ST. LAWRENCE PUBLIC SR. SEC.
SCHOOL
SESSION : 2021-22
NAME : RIDDHI RASTOGI
CLASS : XII A
STREAM : SCIENCE
TOPIC : SQL FILE CONTAINING PREVIOUS YEAR SQL
QUESTIONS.
CERTIFICATE
This is to certify that RIDDHI RASTOGI of
CLASS:XII , SESSION: 2021-22 has prepared
the PRACTICAL FILE as per the prescribed
practical syllabus of Term-II COMPUTER
SCIENCE(083) CLASS-XII under my
supervision.
I am completely satisfied by the performance.
PRINCIPAL’S SIGNATURE :
SUBJECT TEACHER’S SIGNATURE:
ACKNOWLEDGEMENT
I WOULD LIKE TO EXPRESS MY DEEP
SENSE OF GRATITUDE TO ALL THOSE
WHO HAVE BEEN INSTRUMENTAL IN THE
PREPARATION OF THIS PROJECT FILE.
I AM THANKFUL TO MY COMPUTER
SCIENCE TEACHER “MS. DIVYETA
MAM” WHO GAVE ME THE GOLDEN
OPPURTUNITY TO PREPARE THIS SQL
PROJECT FILE.
I AM ALSO THANKFUL TO MY PARENTS
AND FRIENDS FOR THEIR CONSTANT
SUPPORT AND HELP.
Q1: Write SQL commands for the following queries (i) to (iv) based on the
relations STUDENT and POSTING:
TABLE: STUDENT
TABLE: POSTING
1) To show all information about teacher of History Department.
Select * from student where Department=’History’ ;
2) To list the names of female teachers who are in mathematics department.
ANS. Select Name from student where Gender =’F’ and Department
=’Mathematics’;
3) To display teacher’s name, salary, age for male teachers only.
ANS. Select Name, Age, Salary from student where Gender=’M’ ;
4)To display name, bonus for each teacher where bonus is 10% of salary.
ANS. Select Name, salary*0.1 as Bonus from student;
Q2)ANSWER THE FOLLOWING QUESTIONS ON THE BASIS OF
GIVEN RELATIONS:
TABLE: CABHUB
TABLE : CUSTOMER
1)To display the names of all white colored vehicles.
ANS. Select VehicleName from CABHUB where color =’white’ ;
2)To display name of vehicle,make and capacity of vehicles in ascending
order of their seating capacity.
ANS. Select VehicleName, make, capacity from cabhub order by capacity;
3)To display the highest charges at which a vehicle can be hired from
cabhub.
ANS. Select MAX(charges) from cabhub;
4) To display customer name and the corresponding name of vehicle hired by
them.
ANS. Select Cname,VehicleName from Customer,cabhub where
Customer.Vcode=cabhub.Vcode;
Q3) Consider the following tables STORE and SUPPLIERS. Write SQL
commands for given statements.
TABLE : store
TABLE : suppliers
1) To display details of all the items in store table.
ANS. Select * from store;
2) To display ItemNo and item name of those items from store table
whose rate is more than 15.
ANS. Select ItemNo, Item from store where Rate>15;
3) To display details of those items whose suppliers code is 22 or
Quantity in store is more than 110 from the table store.
ANS. Select * from store where Scode= ‘22’ or Qty>110 ;
4) To display rate of items for each Supplier.
ANS. Select Rate from store group by Scode;
Q4) CONSIDER THE FOLLOWING TABLES:COMPANY AND
MODEL. WRITE SQL COMMANDS FOR GIVEN QUERIES.
TABLE : Company
TABLE: MODEL
1)To display the details of all models in the model table.
ANS. Select * from Model ;
2)To display the details of those models manufactured in 2011 and whose
Cost is below 2000.
ANS. Select * from Model where year(DateOfManufacture)=’2011’ and
Cost<2000;
3)To decrease the cost all models in model table by 15%.
ANS. Select Model set Cost= Cost-Cost*0.15 ;
4) To display company name and company head office where head office
name ends with ‘a’.
ANS. Select CompName, CompHO from Company where CompHO like ‘%a’ ;
Q5) Write SQL qureries for (i) to (iv) and find outputs for SQL queries (v) to
(viii), which are based on the tables TRANSPORT and TRIP :
TABLE : Transport
TABLE : Trip
1)To display NO, Name, Tdate from the table Trip in descending order of NO.
ANS. Select NO, Name, Tdate, from Trip order by NO;
2)To display Name of the drivers from table Trip who are travelling by
transport vehicle with code 101 or 103;
ANS. Select Name from Trip where Tcode=101 or Tcode=103;
3) To display NO and Name of the drivers from Trip who traveled between
‘2016-02-10’ and ‘2016-04-10’.
ANS. Select NO, Name from Trip where Tdate between ‘2016-02-10’ and
‘2016-04-10’ ;
4) To display all details from table Trip in which distance travelled is more
than 100KM in ascending order of NOP;
ANS. Select NO, Name, Tdate, KM, Tcode where KM>100 order by NOP;
Q6) CONSIDER THE GIVEN TABLES PRODUCTS AND CLIENTS. WRITE SQL
COMMANDS FOR GIVEN STATEMENTS.
TABLE : PRODUCT
TABLE : CLIENT
1)To display the details of those clients whose cit is Delhi;
ANS. Select * from CLIENT where City= ‘Delhi’;
2)To display details of Products whose price is in the range of 50 to
100.
ANS. Select * from PRODUCT where Price between50 and 100;
3)To display the ClientName, City from table CLIENT, and
ProductName and Price from table PRODUCT, with their corresponding
matching P_ID.
ANS. Select ClientName, City, ProductName, Price from
CLIENT,PRODUCT where PRODUCT.P_ID= CLIENT.P_ID;
4)To increase the Price of all products by 10.
ANS. Update Product set Price= Price+10;
Q7) CONSIDER THE FOLLOWING TABLES SCHOOL AND ADMIN
AND GIVE OUTPUT OF GIVEN QUERIES.
TABLE : School
TABLE : Admin
1)Select max(Experience) from School;
ANS.
2)Select Teacher from School where Experience>12 order by Teacher.
ANS.
3)Select * from School where Experience between 10 and 20;
ANS.
4)Select * from Admin where Designation= ‘Coordinator;
ANS.
5)Select * from School where Subject= ‘English’ or Subject= ‘Maths’;
ANS.
Q8) WRITE SQL COMMAND FOR THE QUERIES BASED ON TABLES
WATCHES AND SALE GIVEN BELOW.
TABLE : Watches
TABLE : Sale
1)To display all the details of those watches whose name ends with
‘Time’.
ANS. Select * from Watches where like= ‘%Time’ ;
2)To display watch’s name and price of those watches which have
price range in between 5000-15000;
ANS. Select WatchName,Price from Watches where Price between
5000 and 15000;
3)To display total quantity of unisex type watches.
ANS. Select Sum(Qty) from Watches where Type=’Unisex’ ;
4) Give output:
Select Max(Price),Min(Quarter) from Watches;
ANS.
Q9) ANSWER THE QUESTIONS ON THE BASIS OF THE FOLLOWING
TABLES SHOP AND ACCESSORIES.
TABLE : SHOP
TABLE : ACCESSORIES
1)To display Name and Price of all the accessories an ascending order.
ANS. Select Name, Price from ACCESSORIES order by Price;
2)To display Id and Sname of all shops located in Nehru Place.
ANS. Select Id, Sname from SHOP where Area= ‘Nehru Place’;
3)To display minimum and maximum price of each name of
ACCESSORIES.
ANS. Select Name,max(Price),min(Price) from ACCESSORIES group by
Name;
4)Write output of the given query.
Select count(Distinct Area) from SHOP;
Q10) WRITE SQL QUERIES FOR THE FOLLOWING QUESTIONS BASED ON
TABLES APPLICANTS AND COURSES:
TABLE : APPLICANTS
TABLE : COURSES
1)To display details of students whose course is BCA.
ANS. Select * from APPLICANTS where Course=’BCA’ ;
2)To display name and phone number from APPLICANTS in ascending
order.
ANS. Select Name, Phone from APPLICANTS order by Id;
3)Write output:
Select max(Id) from APPLICANTS;
ANS.
4)Write output.
Select Sum(Fee) from COURSES;
Q11) CONSIDER DATABASES Resort AND OwnedBy. ANSWER THE GIVEN
QUESTIONS.
TABLE: Resort
TABLE: OwnedBy
1)To display Rcode and Place of all 5 Star resorts in alphabetical order
of the place from table Resort.
ANS. Select Rcode, Place from Resort where Type= ‘5 Star’ order by
Place;
2) To display the maximum and minimum rent for each type of resort
from table Resort.
ANS. Select max(Rent),min(Rent) from Resort group by Type;
3). Give output.
Select Place, Owner from Resort where Type= ‘2 Star’ order by Rent;
ANS.
4) Give output.
Select Place, Owner from OwnedBy where Place like “%A”;
ANS.
THANK YOU !
