Tuesday, 10 September 2024

SQL-2



 Consider the tables Admin and Transport given below:

Table: ADMIN

Table: TRANSPORT







CREATE TABLE ADMIN(S_ID VARCHAR(5) PRIMARY KEY,S_NAME VARCHAR(10), ADDRESS VARCHAR(10), S_TYPE VARCHAR(15));






DESC ADMIN;








INSERT INTO ADMIN(S_ID,S_NAME,ADDRESS,S_TYPE)VALUES ('S001','SANDYA','ROHINI','DAY BOARDER');

INSERT INTO ADMIN(S_ID,S_NAME,ADDRESS,S_TYPE)VALUES('S002','VEDANSHI','ROTHAK','DAY SCHOLAR');

INSERT INTO ADMIN(S_ID,S_NAME,ADDRESS,S_TYPE)VALUES('S003','VIBHU','RAJ NAGAR','NULL');

INSERT INTO ADMIN(S_ID,S_NAME,ADDRESS,S_TYPE)VALUES('S004','ATHARVA','RAMPUR','DAY BOARDER');

SELECT * FROM ADMIN;







CREATE TABLE TRANSPORT(S_ID VARCHAR(5) PRIMARY KEY, BUS_NO VARCHAR(6), STOP_NAME VARCHAR(17));

DESC TRANSPORT;






INSERT INTO TRANSPORT(S_ID,BUS_NO,STOP_NAME)VALUES('S002','TSS10','SARAI KALE KHAN');

INSERT INTO TRANSPORT(S_ID,BUS_NO,STOP_NAME)VALUES('S004','TSS12','SAINIK VIHAR');

INSERT INTO TRANSPORT(S_ID,BUS_NO,STOP_NAME)VALUES('S005','TSS10','KAMLA NAGAR');

SELECT * FROM TRANSPORT;







Write SQL queries for the following:

(i)  Display the student name and their stop name from the table Admin and Transport.

Ans.   SELECT S_NAME,STOP_NAME FROM ADMIN,TRANSPORT WHERE ADMIN.S_ID=TRANSPORT.S_ID;






(ii) Display the number of students whose S_TYPE is not known.

Ans. SELECT COUNT(*) FROM ADMIN WHERE S_TYPE IS NULL;







(iii) Display all details of the students whose name starts with 'v',

Ans. SELECT * FROM ADMIN WHERE S_NAME LIKE 'V%';








(iv) Display student id and address in alphabetical order of student name, from the table Admin.

Ans. SELECT S_ID, ADDRESS FROM ADMIN ORDER BY S_NAME;







No comments:

Post a Comment