Informatics Practices Class XII - SQL
Practical File (With Queries & Outputs)
Table: FLIGHT
Display Flight flying between Varanasi and Nepal
SQL Query:
SELECT * FROM FLIGHT WHERE ORIGIN='VARANASI' AND
DESTINATION='NEPAL';
Expected Output:
It will display flights 1005 and 1265.
Display the different Origin of Flights
SQL Query:
SELECT DISTINCT ORIGIN FROM FLIGHT;
Expected Output:
It will display unique origins: VARANASI, DELHI, MUMBAI.
Display list of flights with first 3 letters of origin, in descending order of Rate
SQL Query:
SELECT LEFT(ORIGIN,3), * FROM FLIGHT ORDER BY RATE DESC;
Expected Output:
It will display flights sorted by rate with first 3 letters of origin.
Display flight details of the flight whose flightdate belongs to 2007
SQL Query:
SELECT * FROM FLIGHT WHERE YEAR(FLIGHTDATE)=2007;
Expected Output:
No record found (since no 2007 data in table).
Show average rate of flights starting from 'Delhi'
SQL Query:
SELECT AVG(RATE) FROM FLIGHT WHERE ORIGIN='DELHI';
Expected Output:
Output: 5000 (average of 5500 and 4500).
List the flights with their Origin, destination and seats for flightno more than
4000
SQL Query:
SELECT ORIGIN, DESTINATION, SEATS FROM FLIGHT WHERE
FLIGHT_NO<4000;
Expected Output:
It will display flights: 1005, 1265, 2345, 2785.
Table: STUDENT
Select all the information of student whose subject is commerce
SQL Query:
SELECT * FROM STUDENT WHERE SUBJECT='COMMERCE';
Expected Output:
It will show AMIT and SONIA.
Show the information of students whose names are POONAM, PANKAJ or
PRIYA
SQL Query:
SELECT * FROM STUDENT WHERE NAME IN
('POONAM','PANKAJ','PRIYA');
Expected Output:
It will show details of these three students.
List the information of students having marks as null
SQL Query:
SELECT * FROM STUDENT WHERE MARKS IS NULL;
Expected Output:
It will display PRIYA.
Delete all records those marks are between 30 and 50
SQL Query:
DELETE FROM STUDENT WHERE MARKS BETWEEN 30 AND 50;
Expected Output:
It will delete AMIT and PANKAJ.
Show all information whose name ending with 'a'
SQL Query:
SELECT * FROM STUDENT WHERE NAME LIKE '%a';
Expected Output:
It will show PRIYA, POONAM, SONIA.
Increase the marks of students by 20% and show the records
SQL Query:
SELECT ROLLNO, NAME, MARKS*1.2 AS UPDATED_MARKS, GRADE,
FEES, SUBJECT FROM STUDENT;
Expected Output:
It will show updated marks with 20% increase.
Table: SALES
Display employee name and bonus after rounding off to zero decimal places
SQL Query:
SELECT SNAME, ROUND(BONUS,0) FROM SALES;
Expected Output:
It will show rounded bonus values.
Display the position of occurrence of string 'ee' in employee names
SQL Query:
SELECT INSTR(SNAME,'ee') FROM SALES;
Expected Output:
It will return numeric position of 'ee'.
Display the four characters from employee name starting from second
character
SQL Query:
SELECT SUBSTRING(SNAME,2,4) FROM SALES;
Expected Output:
It will display 4 characters from 2nd character.
Count the total no of employees who are not getting any bonus
SQL Query:
SELECT COUNT(*) FROM SALES WHERE BONUS IS NULL;
Expected Output:
It will show total employees with NULL bonus.
Table: MEMBERS
Display members no, name and salary of members whose name second
character is 'a'
SQL Query:
SELECT MNO, MNAME, SALARY FROM MEMBERS WHERE MNAME LIKE
'_a%';
Expected Output:
It will return Kavita Sharma, Anand Rathi.
Display members no, name and salary of members whose name’s last character
is 'i'
SQL Query:
SELECT MNO, MNAME, SALARY FROM MEMBERS WHERE MNAME LIKE
'%i';
Expected Output:
It will return Anand Rathi, Sumit Vats.
Display members no, name and comm of members whose name contain 't'
SQL Query:
SELECT MNO, MNAME, COMM FROM MEMBERS WHERE MNAME LIKE
'%t%';
Expected Output:
It will return Kavita Sharma, Tushar Tiwari, Sumit Vats.
Display all the members no and name whose commission is not NULL
SQL Query:
SELECT MNO, MNAME FROM MEMBERS WHERE COMM IS NOT NULL;
Expected Output:
It will return Tushar Tiwari, Sumit Vats.
Display members no and name whose salary is not in between 15000 and 20000
SQL Query:
SELECT MNO, MNAME FROM MEMBERS WHERE SALARY NOT
BETWEEN 15000 AND 20000;
Expected Output:
It will display Kavita Sharma, Tushar Tiwari, Sumit Vats.
Table: DOCTORS & PATIENTS
Display the patno, patname and corresponding docname for each patient
SQL Query:
SELECT [Link], [Link], [Link] FROM PATIENTS P,
DOCTORS D WHERE [Link]=[Link];
Expected Output:
It will show all patients with their doctors.
Display the list of all patients whose opd_days are MWF
SQL Query:
SELECT * FROM PATIENTS P, DOCTORS D WHERE [Link]=[Link]
AND D.OPD_DAYS='MWF';
Expected Output:
It will show OHIT, MOHIT with doctors [Link], [Link].
Show docid, docname, patname along with its department for 'ENT'
SQL Query:
SELECT [Link], [Link], [Link], [Link] FROM
DOCTORS D, PATIENTS P WHERE [Link]=[Link] AND
[Link]='ENT';
Expected Output:
It will show patients Neeraj, Ragini with doctor M. Pandey.
List first day of ortho patients
SQL Query:
SELECT [Link], D.OPD_DAYS FROM PATIENTS P, DOCTORS D
WHERE [Link]=[Link] AND [Link]='ORTHO';
Expected Output:
It will show OHIT with OPD MWF.
Show details of paed doctors with its patients
SQL Query:
SELECT [Link], [Link], [Link] FROM DOCTORS D, PATIENTS
P WHERE [Link]=[Link] AND [Link]='PAED';
Expected Output:
It will show doctor G.P. Gupta with patient MOHIT.