Important Questions for Class 12 Computer Science (C++) – Structured Query Language

 

Previous Years Examination Questions
2 Marks Questions

Question 1:
Explain the concept UNION between two tables, with the help of appropriate example. Delhi 2014
Answer:
The UNION operator is used to combine the result-set of two or more tables, without returning any duplicate rows,
e.g.

6 Marks Questions

Question 2:
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.All India 2017

(i) To display all details from the table MEMBER in descending order of ISSUEDATE.
(ii) To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD.
(iii) To display the DTYPE and number of DVDs in each DTYPE from the table DVD.
(iv) To display all NAME and ISSUEDATE of those members from the table MEMBER who have DVDs issued
(i.e., ISSUEDATE) in the year 2017.

(v) SELECT MIN (ISSUEDATE) FROM MEMBER;
(vi) SELECT DISTINCT DTYPE FROM DVD;
(vii) SELECT D.DCODE, NAME, DTITLE '
FROM DVD D, MEMBER M WHERE D.DC0DE=M.DCODE;
(viii) SELECT DTITLE FROM DVD
WHERE DTYPE NOT IN ("Folk”, "Classical”);

Answer:

(i) SELECT * FROM MEMBER ORDER BY ISSUEDATE DESC;
(ii) SELECT DCODE, DTITLE FROM DVD WHERE DTYPE = "Folk";
(iii) SELECT DTYPE, COUNT (*) FROM DVD GROUP BY DTYPE;
(iv) SELECT NAME, ISSUEDATE FROM MEMBER WHERE ISSUEDATE LIKE ‘2017%’;

 

Question 3:
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii),
which are based on the tables. All India 2016 

NOTE

• KM is Kilometres travelled
• NOP is number of passengers travelled in vehicle.

(i) To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO.
(ii) To display the CNAME of all the customers from the table TRAVEL who are traveling by vehicle with code V01 or V02.
(iii) To display the CNO and CNAME of those customers from the table TRAVEL who travelled between ‘2015-12-31’ and ‘2015-05-01’.
(iv) To display all the details from table TRAVEL for the customers, who have travel distance more than 120 KM in ascending order of NOP.

(v) SELECT COUNT(*), VCODE FROM TRAVEL 
GROUP BY VCODE HAVING C0UNT(*)>1;
(vi) SELECT DISTINCT VCODE FROM TRAVEL;
(vii) SELECT VCODE,CNAME,VEHICLETYPE 
FROM TRAVEL A, VEHICLE B
WHERE A.VC0DE=B.VCODE AND KM<90;
(viii) SELECT CNAME, KM*PERKM
FROM TRAVEL A, VEHICLE B
WHERE A.VC0DE=B.VCODE AND A.VC0DE='V05';

Answer:

(i) SELECT CNO, CNAME, TRAVELDATE FROM TRAVEL ORDER BY CNO DESC;
(ii) SELECT CNAME FROM TRAVEL WHERE VCODE = "VO1" OR VC0DE="VO2";
(iii) SELECT CNO, CNAME FROM TRAVEL WHERE TRAVELDATE BETWEEN '2015-12-31' AND ‘2015-05-01 ‘;
(iv) SELECT * FROM TRAVEL WHERE KM>120  ORDER BY NOP

 

Question 4:
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables. Delhi 2016 
NOTE
• PERKM is Freight Charges per kilometre • VTYPE is Vehicle Type 

NOTE
• NO is Traveller Number
• KM is Kilometre travelled
• NOP is number of travellers travelled in vehicle
• TDATE is Travel Date

(i) To display NO, NAME, TDATE from the table TRAVEL in descending order of NO.
(ii) To display the NAME of all the travellers from the table TRAVEL who are travelling by vehicle with code 101 or 102. ‘
(iii) To display the NO and NAME of those travellers from the table TRAVEL who travelled between ’2015-12-31’ and ‘2015-04-01’.
(iv) To display all the details from table TRAVEL for the travellers, who have travelled distance more than 100 KM in ascending order of NOP.

(v) SELECT COUNT(*), CODE FROM TRAVEL 
GROUP BY CODE HAVING C0UNT(*) >1;
(vi) SELECT DISTINCT CODE FROM TRAVEL;
(vii) SELECT CODE,NAME,VTYPE 
FROM TRAVEL A, VEHICLE B 
WHERE A.C0DE=B.C0DE AND KM<90;
(viii) SELECT NAME,KM*PERKM
FROM TRAVEL A, VEHICLE B
WHERE A.C0DE=B.C0DE AND A.C0DE='105' ;

Answer:

(i) SELECT NO, NAME, TDATE FROM TRAVEL ORDER BY NO DESC;
(ii) SELECT NAME FROM TRAVEL WHERE CODE = 101 OR CODE = 102;
(iii) SELECT NO. NAME FROM TRAVEL WHERE TDATE BETWEEN '2015-12-31' AND '2015-04-01' ;
(iv) SELECT * FROM TRAVEL WHERE KM > 100 ORDER BY NOP;

Question 5:
Consider the following DEPT and WORKER tables.
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii): Delhi 2015 

NOTE
DOJ refers to Date of Joining and DOB refers to Date of Birth of workers.
(i) To display WNO, NAME,, GENDER from the table WORKER in descending order of WNO.
(ii) To display the NAME of all the FEMALE workers from the table WORKER.
(iii) To display the WNO and NAME of those workers from the table WORKER, who are born between ‘1987-01-01’ and ‘1991-12-01’.
(iv) To count and display MALE workers who have joined after ‘1986-01-01’.

(v) SELECT COUNT(*), DCODE FROM WORKER 
GROUP BY DCODE HAVING C0UNT(*)>1;
(vi) SELECT DISTINCT DEPARTMENT FROM DEPT;
(vii) SELECT NAME, DEPARTMENT, CITY FROM WORKER W, DEPT D 
WHERE W.DC0DE=D.DCODE AND WNO<1003;
(viii) SELECT MAX (DOJ), MIN(DOB) FROM WORKER;

Answer:

(i) SELECT WNO, NAME, GENDER FROM WORKER ORDER BY WNO DESC; 
(ii) SELECT NAME FROM WORKER WHERE GENDER = "FEMALE"; 
(iii) SELECT WNO, NAME FROM WORKER WHERE DOB BETWEEN '1987-01-01' AND '1991-12-01'; 
(iv) SELECT COUNT(*) FROM WORKER WHERE GENDER = "MALE" AND DOJ > '1986-01-01';

 

Question 6: Consider the following DEPT and EMPLOYEE tables.
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii). All India 2015  

NOTE DOJ refers to Date of Joining and DOB refers to Date of Birth of employees.
(i) To display ENO, NAME, GENDER from the table EMPLOYEE in ascending order of ENO.
(ii) To display the NAME of all the MALE employees from the table EMPLOYEE.
(iii) To display the ENO and NAME of those employees from the table EMPLOYEE who are born between ‘1987-01-01’ and ‘1991-12-01’.
(iv) To count and display FEMALE employees who have joined after ‘1986-01-01’.

(v) SELECT COUNT (*), DC0DE FROM EMPLOYEE 
GROUP BY DCODE HAVING C0UNT(*)>1;
(vi) SELECT DISTINCT DEPARTMENT FROM DEPT;
(vii) SELECT NAME, DEPARTMENT FROM EMPLOYEE E.DEPT D 
WHERE E.DCODE = D.DCODE AND ENO<1003;
(viii) SELECT MAX(DOJ),MIN(DOB)FROM EMPLOYEE;

Answer:

(i) SELECT ENO, NAME, GENDER FROM EMPLOYEE ORDER BY ENO; 
(ii) SELECT NAME FROM EMPLOYEE WHERE GENDER = 'MALE'; 
(iii) SELECT ENO, NAME FROM EMPLOYEE WHERE DOB BETWEEN '1987-01-01' AND '1991-12-01';
(iv) SELECT COUNT!*) FROM EMPLOYEE WHERE GENDER = 'FEMALE' AND DOJ >'1986-01-01' ;

Question 7:
Consider the following tables SCHOOL and ADMIN  and answer (a) and (b) parts of this question : All India 2014 c 

(a) Write SQL statements for the following:
(i) To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25.
(ii) To display all the information from the table SCHOOL in descending order of experience.
(iii) To display DESIGNATION without duplicate entries from the table ADMIN.
(iv) To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and ADMIN of Male teachers.
(b) Give the output of the following SQL queries :

(i) SELECT DESIGNATION, COUNT (*) FROM ADMIN GROUP BY DESIGNATION HAVING COUNT (*)<2;
(ii) SELECT MAX (EXPERIENCE) FROM SCHOOL;
(iii) SELECT TEACHERNAME FROM SCHOOL WHERE EXPERIENCE > 12 ORDER BY TEACHERNAME;
(iv) SELECT COUNT (*), GENDER FROM ADMIN GROUP BY GENDER;

Answer:

(a) (i) SELECT TEACHERNAME, PERIODS 
FROM SCHOOL WHERE PERI0DS>25; 
(ii) SELECT *FROM SCHOOL ORDER BY EXPERIENCE DESC; 
(iii) SELECT DISTINCT DESIGNATION FROM ADMIN; 
(iv) SELECT TEACHERNAME, CODE, DESIGNATION FROM SCHOOL S, 
ADMIN A WHERE S.CODE = A.CODE AND  GENDER = "MALE"; 

Question 8:
Answer the questions (a) and (b) on the basis of the following tables STORE and ITEM. Delhi 2014

(a) Write the SQL queries
(i) to (iv): (i) To display IName and Price of all the Items in ascending order of their Price.
(ii) To display SNo and SName of all Store located in CP.
(iii) To display Minimum and Maximum Price of each IName from the table ITEM.
(iv) To display IName, Price of all items and their respective SName where they are available.
(b) Write the output of the following SQL commands (i) to (iv):

(i) SELECT DISTINCT IName FROM ITEM
WHERE Price >=5000;
(ii) SELECT Area, COUNT(*)
FROM STORE GROUP BY Area;
(iii) SELECT COUNT(DISTINCT Area) FROM STORE;
(iv) SELECT IName, Price * 0.05 DISCOUNT FROM ITEM 
WHERE SNo IN (S02, S03);

Answer:

(a)(i) SELECT IName, Price FROM ITEM ORDER BY Price; 
(ii) SELECT SNo, SName FROM STORE WHERE Area = 'CP’; 
(iii) SELECT IName, MIN(Price)"Minimum Price", MAX(Price)"Maximum Price" FROM ITEM GROUP BY IName; 
(iv) SELECT IName, Price, SName FROM ITEM I, STORE S WHERE I.SNo = S.SNo;

 

Question 9:
Answer the questions (a) and (b) on the basis of the following tables
SHOPPE and ACCESSORIES. All India 2014

(a) Write the SQL queries:
(i) To display Name and Price of all the accessories in ascending order of their Price.
(ii) To display Id and SName of all Shoppe located in Nehru Place.
(iii) To display Minimum and Maximum Price of each Name of accessories.
(iv) To display Name, Price of all accessories and their respective SName where they are available.
(b) Write the output of the following SQL commands:

(i) SELECT DISTINCT Name FROM ACCESSORIES WHERE Price>=5000;
(ii) SELECT Area, C0UNT(*) FROM SHOPPE GROUP BY Area;
(iii) SELECT C0UNT(DISTINCT Area) FROM SHOPPE:
(iv) SELECT Name, Price*0.05 DISCOUNT FROM ACCESSORIES WHERE SNo IN (S02.S03);

Answer:

(a)(i) SELECT Name, Price FROM ACCESSORIES ORDER BY Price ASC;
(ii) SELECT ID, SName FROM SHOPPE WHERE Area = ‘Nehru Place’;
(iii) SELECT MIN(Price)”Minimum Price”, MAX(Price)”Maximum Price”, Name FROM ACCESSORIES GROUP BY Name;
(iv) The query for this statement cannot be done because relation column, i.e. foreign key is not present. 

Question 10:
Write SQL queries for (a) to (f) and write the outputs for the SQL queries
mentioned shown in (i) to (iv) parts on the basis of tables PRODUCTS and SUPPLIERS. All India 2013  
(a) To display the details of all the products in ascending order of product names (i.e. PNAME).
(b) To display product name and price of all those products, whose price is in the range of 10000 and 15000 (both values inclusive).
(c) To display the number of products which are supplied by each supplier, i.e. the expected output should be

501 2
502 2
503 1

(d) To display the price, product name (i.e. PNAME) and quantity (i.e. QTY) of those products which have quantity more than 100.
(e) To display the names of those suppliers, who are either from DELHI or from CHENNAI.
(f) To display the name of the companies and the name of the products in descending order of company names.
(g) Obtain the outputs of the following SQL queries based on the data given in tables PRODUCTS and SUPPLIERS:

(i) SELECT DISTINCT SUPCODE FROM PRODUCTS:
(ii) SELECT MAX(PRICE), MIN(PRICE) FROM PRODUCTS;
(iii) SELECT PRICE * QTY AMOUNT FROM PRODUCTS WHERE PID = 104;
(iv) SELECT PNAME, SNAME FROM PRODUCTS P, SUPPLIERS S 
WHERE P.SUPCODE - S.SUPCODE AND QTY>100;

Answer:

(a) SELECT * FROM PRODUCTS ORDER BY PNAME; 
(b) SELECT PNAME, PRICE FROM PRODUCTS WHERE PRICE BETWEEN 10000 AND 15000; 
(c) SELECT SUPCODE, COUNT(*) FROM PRODUCTS GROUP BY SUPCODE; 
(d) SELECT PRICE, PNAME, QTY FROM PRODUCTS WHERE QTY > 100; 
(e) SELECT SNAME FROM SUPPLIERS WHERE CITY = 'DELHI' OR CITY = 'CHENNAI' ; 
(f) SELECT COMPANY, PNAME  FROM PRODUCTS ORDER BY COMPANY DESC;

Question 11:
Write SQL queries for (a) to (f) and write the outputs for the SQL queries
mentioned shown in (i) to (iv) parts on the basis of tables ITEMS and TRADERS. Delhi 2013 
(a) To display the details of all the items in ascending order of item names (i.e. INAME).
(b) To display item name and price of all those items, whose price is in the range of 10000 and 22000 (both values inclusive). (c) To display the number of items, which are traded by each trader. The expected output of this query should be

T01 2
T02 2
T03 1

(d) To display the price, item name (i.e. INAME) and quantity (i.e. QTY) of those items which have quantity more than 150.
(e) To display the names of those traders, who are either from DELHI or from MUMBAI.
(f) To display the name of the companies and the name of the items in descending order of company names.
(g) Obtain the outputs of the following SQL queries based on the data given in tables ITEMS and TRADERS:

(i) SELECT MAX (PRICE), MIN( PRICE) FROM ITEMS;
(ii) SELECT PRICE * QTY AMOUNT FROM ITEMS WHERE CODE = 1004;
(iii) SELECT DISTINCT TCODE FROM ITEMS;
(iv) SELECT INAME, TNAME FROM ITEMS I, TRADERS T 
WHERE I.TCODE = T.TCODE AND QTY<100;

Answer:

(a) SELECT * FROM ITEMS ORDER BY INAME;
(b) SELECT INAME, PRICE FROM ITEMS WHERE PRICE BETWEEN 10000 AND 22000;
(c) SELECT TCODE, COUNT(*) FROM ITEMS GROUP BY TCODE;
(d) SELECT PRICE, INAME, QTY FROM ITEMS WHERE QTY >150;
(e) SELECT TNAME FROM TRADERS WHERE CITY
= ‘MUMBAI’ OR CITY= 'DELHI' ; (f) SELECT COMPANY, INAME

 

Question 12:
Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned shown in (i) to (iv) parts on the basis of tables APPLICANTS and COURSES. Delhi (C) 2013 
(a) To display name, fee, gender, joinyear about the applicants, who have joined before 2010.
(b) To display the names of applicants, who are paying fee more than 30000.
(c) To display names of all applicants in ascending order of their joinyear.
(d) To display the year and the total number of applicants joined in each YEARfrom the table APPLICANTS.
(e) To display the CJD (i.e. Course ID) and the number of applicants registered in the course from the APPLICANTS table.
(f) To display the applicant’s name with their respective course’s name from the tables APPLICANTS and COURSES.
(g) Give the output of following SQL statements:

(i) SELECT NAME, JO I NY EAR FROM APPLICANTS WHERE GENDER-'F’ and C_ID=’A02';
(ii) SELECT MINIJOINYEAR) FROM APPLICANTS WHERE Gender='M';
(iii) SELECT AVG (FEE) FROM APPLICANTS WHERE C_ID='A01’ OR C_ID='A05’;
(iv) SELECT SUM(FEE), C_ID FROM APPLICATIONS GROUP BY C_ID HAVING C0UNT(*)=2;

Answer:

(a) SELECT NAME, FEE, GENDER, JOINYEAR FROM APPLICANTS WHERE J0INYEAR<2010; 
(b) SELECT NAME FROM APPLICANTS WHERE FEE >30000; 
(c) SELECT NAME FROM APPLICANTS ORDER BY JOINYEAR; 
(d) SELECT JOINYEAR, COUNT(*) FROM APPLICANTS GROUP BY JOINYEAR; 
(e) SELECT C_ID, COUNT(*) FROM APPLICANTS ORDER BY C_ID; 
(f) SELECT NAME, COURSE FROM APPLICANTS, COURSES WHERE APPLICANTS.C_ID=COURSES.C_ID;

Question 13:
Consider the following tables CABHUB and CUSTOMER and answer (a) and (b) parts of this question: Delhi 2012

(a) Write SQL commands for the following statements:
(i) To display the names of all the white colored vehicles.
(ii) To display name of vehicle, make and capacity of vehicles in ascending order of their  setting Capacity.
(iii) To display the highest charges at which a vehicle can be hired from CABHUB.
(iv) To display the customer names and the corresponding name of the vehicle hired by them.
(b) Give the output of the following SQL queries :

(i) SELECT COUNT (DISTINCT Make) FROM CABHUB;
(ii) SELECT MAX(Charges), MIN (Charges) FROM CABHUB;
(iii) SELECT COUNT(*), Make FROM CABHUB;
(iv) SELECT VehicleName FROM CABHUB WHERE Capacity = 4;

Answer:

(a) (i) SELECT VehicleName 
FROM CABHUB 
WHERE Color = 'WHITE';
(ii) SELECT VehicleName, Make, 
Capacity FROM CABHUB 
ORDER BY Capacity;
(iii) SELECT MAX(Charges)
FROM CABHUB;
(iv) SELECT CName, VehicleName 
FROM CABHUB C1, CUSTOMER 
C2 WHERE C1.Vcode = C2.Vcode;

(iii) This query will execute but COUNT (*) will give result one row and Make will give more than one row so both are not compatible together. But on removing Make from select clause it will give following result.

Question 14:
Consider the following tables CUSTOMER and ONLINESHOP.
Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii). Delhi (C) 2012

(i) To display cname, area of fill female customers from CUSTOMER table.
(ii) To display the details of all the customers in ascending order of CNAME within SID.
(iii) To display the total number of customers for each area from CUSTOMER table.
(iv) To display cname and corresponding shop from CUSTOMER table and ONLINESHOP table.

(v) SELECT COUNT(DATE), GENDER FROM CUSTOMER GROUP BY GENDER;
(vi) SELECT C0UNT(*) FROM ONLINESHOP;
(vii) SELECT CNAME FROM CUSTOMER WHERE CNAME LIKE "L%";
(viii) SELECT DISTINCT AREA FROM CUSTOMER;

Answer:

(i) SELECT CNAME, AREA FROM CUSTOMER WHERE GENDER = 'FEMALE';
(ii) SELECT * FROM CUSTOMER ORDER BY SID, CNAME;
(iii) SELECT COUNT(*) FROM CUSTOMER GROUP BY AREA;
(iv) SELECT CNAME, SHOP FROM CUSTOMER C, ONLINESHOP O WHERE C.SID = O.SID;

Question 15:
Consider the following tables CARDEN and CUSTOMER and answer (a) and (b) parts of this question: All India 2012 
(a) Write SQL commands for the following statements:
(i) To display the name of all the SILVER colored cars.
(ii) To display name of car, make and capacity of cars in descending order of their sitting capacity.
(iii) To display the highest Charges at which a vehicle can be hired from CARDEN.
(iv) To display the customer names and the corresponding name of the cars hired by them,
(b) Give the output of the following SQL queries:

(i) SELECT COUNT (DISTINCT Make) FROM CARDEN:
(ii) SELECT MAX(Charges), MIN (Charges) FROM CARDEN;
(iii) SELECT C0UNT(*), Make FROM CARDEN;
(iv) SELECT CarName FROM CARDEN WHERE Capacity = 4;

Answer:

(a) (i) SELECT CarName FROM CARDEN WHERE Color = ’SILVER’;
(ii) SELECT CarName, Make, Capacity FROM CARDEN ORDER BY Capacity DESC;
(iii) SELECT MAX(Charges) FROM CARDEN;
(iv) SELECT Cname, CarName FROM CARDEN C1, CUSTOMER C2 WHERE C1.Ccode = C2.Ccode:

Question 16:
Consider the following tables EMPLOYEE and SALGRADE and answer (a) and (b) parts of this question: All India 2011 
(a) Write SQL commands for the following statements:
(i) To display the details of all the EMPLOYEE in descending order of DOJ.
(ii) To display name and desig of those EMPLOYEE, whose sgrade is either S02 or S03.
(iii) To display the content of all the EMPLOYEE table, whose DOJ is in between ‘09-FEB-2006’ and ‘08-AUG-2009’.
(iv) To add a new row in the EMPLOYEE table with the following data: 109, ‘Harish Roy’, ‘HEAD-IT, ‘S02’, ‘09-SEP-2007’, ‘21-APR-1983’.
(b) Give the output of the following SQL queries:

(i) SELECT C0UNT(SGRADE), SGRADE FROM EMPLOYEE GROUP BY SGRADE;
(ii) SELECT MIN (DOB), MAX (DOJ) FROM EMPLOYEE;
(iii) SELECT NAME, SALARY FROM EMPLOYEE E, SALGRADE S 
WHERE E.SGRADE = S.SGRADE AND E.EC0DE<103;
(iv) SELECT SGRADE, SALARY+HRA FROM SALGRADE WHERE SGRADE = ‘S02';

Answer:

(a) (i) SELECT * FROM EMPLOYEE ORDER BY DOJ DESC;
(ii) SELECT. NAME, DESIG FROM EMPLOYEE WHERE SGRADE='SO2' OR SGRADE ='SO3’;
(iii) SELECT * FROM EMPLOYEE WHERE DOJ BETWEEN '09-FEB-2006’ AND '08-AUG-2009';
(iv) INSERT INTO EMPLOYEE VALUES 
(109, 'HarishRoy', 'HEAD-IT', ’ SO2', '09-SEP-2007', '21-APR-1983');

 

Question 17:
Consider the following tables WORKER and PAYLEVEL and answer (a) and (b) parts of this question: Delhi 2011  
(a) Write SQL commands for the following statements:
(i) To display the details of all WORKER in descending order of DOB.
(ii) To display name and desig of those WORKER, whose plevel is either P001 or P002.
(iii) To display the content of all the WORKER table, whose DOB is in between ‘19-JAN-1984’ and T8-JAN-1987’.
(iv) To add a new row with the following: 19, ‘Daya Kishore’, ‘Operator’, ‘P003’, ‘19-JUN-2008’, ‘11-JUL-1984’.
(b) Give the output of the following SQL queries:

(i) SELECT COUNTCPLEVEL(). PLEVEL FROM WORKER GROUP BY PLEVEL:
(ii) SELECT MAX (DOB), MIN(DOJ) FROM WORKER;
(iii) SELECT NAME, PAY FROM WORKER W, PAYLEVEL P 
WHERE W.PLEVEL= P.PLEVEL AND W.EC0DE<13;
(iv) SELECT PLEVEL, PAY+ALLOWANCE FROM PAYLEVEL WHERE PLEVEL = ‘POO3’:

Answer:

(a) (i) SELECT *
FROM WORKER
ORDER BY DOB DESC;
(ii) SELECT NAME, DESIG 
FROM WORKER 
WHERE PLEVEL = 'Poo1'
OR PLEVEL = *Poo2';
(iii) SELECT *
FROM WORKER
WHERE DOB BETWEEN ’ 19-JAN-1984' 
AND '18-JAN-1987';
(iv) INSERT INTO WORKER VALUES (19, FR0M ST0CK GR0UP BY Dcode:
'Daya Ki shore', 'Operator', 'P003' 
(b) (i) COUNT (DISTINCT Dcode) '19:JUN-2008', '11-JUL-1984');

Question 18:
Consider the following tables STORE and SUPPLIERS and answer  (a) and (b) parts of this question: Delhi 2010

(a) Write SQL commands for the following statements:
(i) To display details of sill the items in the STORE table in ascending order of LastBuy.
(ii) To display ItemNo and Item of those items from STORE table whose Rate is more than Rs. 15.
(iii) To display the details of those items whose supplier code (Scode) is 22 or quantity in store (Qty) is more than 110 from the table STORE.
(iv) To display minimum Rate of items for each supplier individually as per Scode from the table STORE.
(b) Give the output of the following SQL queries:

(i) SELECT COUNT(DISTINCT Scode) FROM STORE;
(ii) SELECT Rate * Qty FROM STORE WHERE ItemNo = 2004;
(iii) SELECT Item, Sname FROM STORE S, SUPPLIERS P 
WHERE S.Scode = P.Scode AND ItemNo = 2006;
(iv) SELECT MAX(LastBuy) FROM STORE;

Answer:

(a) (i) SELECT *
FROM STORE ORDER BY LastBuy;
(ii) SELECT ItemNo, Item 
FROM STORE WHERE Rate>15;
(iii) SELECT * FROM STORE
WHERE Scode = 22 OR Qty>110;
(iv) SELECT MIN (Rate) FROM STORE GROUP BY Scode;

Question 19:
Consider the following tables STOCK and DEALERS and  answer (a) and (b) parts of this question: All India 2010

(a) Write SQL commands for the following statements:
(i) To display details of all the items in the STOCK table in ascending order of StockDate.
(ii) To display ItemNo and ItemName of those items from STOCK table whose UnitPrice is more than Rs. 10.
(iii) To display the details of those items whose dealer code (Dcode) is 102 or quantity in stock (Qty) is more than 100 from the table STOCK.
(iv) To display maximum UnitPrice of items for each dealer individually as per Dcode from the table STOCK.
(b) Give the output of the following SQL queries:

(i) SELECT COUNT(DISTINCT Dcode) FROM STOCK;
(ii) SELECT Qty * UnitPrice FROM STOCK WHERE ItemNo = 5006;
(iii) SELECT ItemName. Dname FROM STOCK S, DEALERS D 
WHERE S.Dcode = D.Dcode AND ItemNo = 5004;
(iv) SELECT MIN(StockDate) FROM STOCK;

Answer:

(a)(i) SELECT *
FROM STOCK ORDER BY StockDate; 
(ii) SELECT ItemNo, ItemName
FROM STOCK WHERE UnitPrice>10; 
(iii) SELECT * FROM STOCK
Where Dcode = 102 OR Qty>100
(iv) SELECT MAX (UNITEPRICE) FROM ST0CK GROUP BY Dcode

Question 20:
Consider the following tables GARMENT and FABRIC.
Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii). Delhi 2009
 
(i) To display GCODE and DESCRIPTION of each GARMENT in descending order of GCODE.
(ii) To display the details of all the GARMENT, which have READYDATE in between 08-DEC-07 and 16-JUN-08 (inclusive of both the dates).
(iii) To display the average PRICE of all the GARMENT, which are made up of fabric with FCODE as F03.
(iv) To display fabric wise highest and lowest price of GARMENT from GARMENT table. (Display FCODE of each GARMENT alongwith highest and lowest Price).

(v) SELECT SUM(PRICE) FROM GARMENT WHERE FCODE = 'FO1'; . .
(vi) SELECT DESCRIPTION, TYPE FROM GARMENT, FABRIC
WHERE GARMENT.FCODE = FABRIC.FCODE AND GARMENT.PRICE >=1260;
(vii) SELECT MAX(FCODE) FROM FABRIC;
(viii) SELECT COUNT(DISTINCT PRICE) FROM GARMENT;

Answer:

(i) SELECT GCODE, DESCRIPTION FROM GARMENT ORDER BY GCODE DESC; 
(ii) SELECT * FROM GARMENT WHERE READYDATE BETWEEN '08-DEC-07' AND '16-JUN-08' ; ' 
(iii) SELECT AVG(PRICE) FROM GARMENT WHERE FCODE = ’F03’; 
(iv) SELECT FCODE, MAX(PRICE), MIN(PRICE) FROM GARMENT GROUP BY FCODE;

 

Question 21:
Consider the following tables DRESS and MATERIAL.
Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii). All India 2009

(i) To display DCODE and DESCRIPTION of each dress in ascending order of DCODE.
(ii) To display the details of all the dresses which have LAUNCHDATE in between 05-DEC-07 and 20- JUN-08 (inclusive of both the dates).
(iii) To display the average PRICE of all the dresses which are made up of material with MCODE as M003.
(iv) To display materialwise highest and lowest price of dresses from DRESS table, (display MCODE of each dress alongwith highest and lowest price).

(v) SELECT SUM(PRICE) FROM DRESS WHERE MCODE = *M001';
(vi) SELECT DESCRIPTION, TYPE FROM DRESS, MATERIAL
WHERE DRESS.DCODE = MATERIAL.MCODE AND DRESS.PRICE >= 1250;
(vii) SELECT MAX (MCODE) FROM MATERIAL;
(viii) SELECT COUNT(DISTINCT PRICE) FROM DRESS;

Answer:

(i) SELECT DCODE, DESCRIPTION FROM DRESS ORDER BY DCODE;
(ii) SELECT * FROM DRESS WHERE LAUNCHDATE BETWEEN '05 - DEC - 07' AND ’20-JUN-08’;
(iii) SELECT AVG(PRICE) FROM DRESS WHERE MCODE ='M003’;
(iv) SELECT MCODE. MAX(PRICE), MIN(PRICE) FROM DRESS GROUP BY MCODE;

 

Question 22: Consider the following tables STUDENT and STREAM.
Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii). Delhi (C) 2009 
(i) To display the name of streams in alphabetical order from table STREAM.
(ii) To display the number of students whose POINTS are more than 5.
(iii) To update GRADE to ‘A’ for all those students who are getting more than 8 as POINTS.
(iv) ARTS+MATHS stream is no more available. Make necessary change in table fjTREAM.

(v) SELECT SUM(POINTS) FROM STUDENT WHERE AGE >14; .
(vi) SELECT STRCDE, MAX(POINTS) FROM STUDENT
GROUP BY STRCDE HAVING SCODE BETWEEN 105 AND 130;
(vii) SELECT AVG(AGE) FROM STUDENT WHERE SCODE IN (102,105, 110, 120);
(viii) SELECT COUNT(STRNAME) FROM STREAM WHERE STRNAME LIKE "SCI%";

Answer:

(i) SELECT STRNAME
FROM STEAM
ORDER BY STRNAME;
(ii) SELECT COUNT(*)
FROM STUDENT
WHERE P0INTS>5;
(iii) UPDATE STUDENT
SET GRADE = 'A'
WHERE P0INTS>8;
(iv) DELETE FROM STREAM WHERE STRNAME = "ARTS + MATHS";