Scribd
Upload
91 views6 pages

SQL Question Paper - 2

Uploaded by

vyash2214
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
91 views6 pages

SQL Question Paper - 2

Uploaded by

vyash2214
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

Swamy’s School

Madanandapuram, Chennai 600125


Questions for practice

1. Consider the following tables SCHOOL and ADMIN and answer this question:
Give the output the following SQL queries:

1. Select Designation Count (*) From Admin Group By Designation Having Count (*) <2;
2. SELECT max (EXPERIENCE) FROM SCHOOL;
3. SELECT TEACHER FROM SCHOOL WHERE EXPERIENCE >12 ORDER BY TEACHER;
4. SELECT COUNT (*), GENDER FROM ADMIN GROUP BY GENDER;
2. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based
on the tables TRANSPORT and TRIP

• PERKS is Freight Charages per kilometer • NOP is number of travellers travelled in


• TTYPE is Transport Vehicle Type vehicle
• NO is Driver Number • TDATE is Trip Date
• KM is Kilometer travelled.
a) To display NO, NAME, TDATE from the table TRIP in descending order of NO.
© Swamy’s Group of Schools, Porur, Chennai 1|Page
b) To display the NAME of the drivers from the table TRIP who are traveling by transport vehicle
with code 101 or 103.
c) To display the NO and NAME of those drivers from the table TRIP who travelled between ‘2015-
02-10’ and ‘2015-04-01’.
d) To display all the details from table TRIP in which the distance travelled is more than 100 KM in
ascending order of NOP
e) SELECT COUNT (*), TCODE From TRIP GROUP BY TCODE HAVNING Count (*) > 1;
f) SELECT DISTINCT TCODE from TRIP;
g) SELECT A.TCODE, NAME, TTYPE FROM TRIP A, TRANSPORT B WHERE A. TCODE = B. TCODE AND
KM < 90;
h) SELECT NAME, KM *PERKM FROM TRIP A, TRANSPORT B
WHERE A. TCODE = B. TCODE AND A. TCODE = 105′;
3. Write SQL commands for the queries (i) to (iv) and output for (v) & (viii) based on a table
COMPANY and CUSTOMER.

1. To display those company name which are having prize less than 30000.
2. To display the name of the companies in reverse alphabetical order.
3. To increase the prize by 1000 for those customers whose name starts with “S”?
4. To add one more column totalprice with decimal] 10,2) to the table customer
5. SELECT COUNT(*) , CITY FROM COMPANY GROUP BY CITY;
6. SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY>10;
7. SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE “%r%;
8. SELECT PRODUCTNAME,CITY, PRICE FROM COMPANY, CUSTOMER WHERE
COMPANY. CID=CUSTOMER.CID AND PRODUCTNAME=”MOBILE”;
4. Consider the following tables SCHOOL and ADMIN and answer this question:

© Swamy’s Group of Schools, Porur, Chennai 2|Page


Write SQL statements for the following:
1. To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25.
2. To display all the information from the table SCHOOL in descending order of experience.
3. To display DESIGNATION without duplicate entries from the table ADMIN.
4. To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and
ADMIN of Male teachers.
5. Write SQL commands for the queries (i) to (iv) and output for (v) to (viii) based on the tables
Watches’ and Sale given below.

1. TO DISPLAY ALL THE DETAILS OF THOSE WATCHES WHOSE NAME ENDS WITH ‘TIME’
2. TO DISPLAY WATCH’S NAME AND PRICE OF THOSE WATCHES WHICH HAVE PRICE RANGE IN
BE-TWEEN 5000-15000.
3. TO DISPLAY TOTAL QUANTITY IN STORE OF UNISEX TYPE WATCHES.
4. TO DISPLAY WATCH NAME AND THEIR QUANTITY SOLD IN FIRST QUARTER;
5. SELECT MAX (PRICE), MIN(QTY_STORE) FROM WATCHES;
6. SELECT QUARTER, SUM(QTY SOLD) FROM SALE GROUP BY QUARTER;
7. SELECT WATCH_NAME, PRICE, TYPE FROM WATCHES W, SALE S WHERE W.
WAT£H1D!=S.WATCHID; (viii) SELECT WATCH_NAME, QTYSTORE, SUM (QTY_SOLD),
QTY_STORESUM (QTYSOLD) “STOCK” FROM WATCHES W, SALE S WHERE W. WATCHID =
S.WATCHID GROUP BY S.WATCHID;

© Swamy’s Group of Schools, Porur, Chennai 3|Page


6. Answer the questions (a) and (b) based on the following tables SHOP and ACCESSORIES.

(a) Write the SQL queries:


1. To display Name and Price of all the Accessories in ascending order of their Price.
2. To display Id and SName of all Shop located in Nehru Place.
3. To display Minimum and Maximum Price of each Name of Accessories.
4. To display Name, Price of all Accessories and their respective SName where they are
available.
(b) Write the output of the following SQL
1. SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE> =5000;
2. SELECT AREA, COUNT(*) FROM SHOPPE GROUP BY AREA;
3. SELECT COUNT (DISTINCT AREA) FROM SHOPPE;
4. SELECT NAME, PRICE*0.05 DISCOUNT FROM ACCESSORIES WHERE SNO IN (‘S02‘,S03‘);
7. Write SQL queries for:

1. To display name, fee, gender, joinyear about the applicants, who have joined before 2010.
2. To display names of applicants, who are playing fee more than 30000.
3. To display names of all applicants in ascending order of their joinyear.
4. To display the year and the total number of applicants joined in each YEAR from the table
APPLICANTS.
5. To display the C_ID (i.e., CourselD) and the number of applicants registered in the course
from the APPLICANTS and table.
6. To display the applicant’s name with their respective course’s name from the tables
APPLICANTS and COURSES.
7. Give the output of following SQL statements:
o SELECT Name, Joinyear FROM APPLICANTS WHERE GENDER=’F’ and C_ID=’A02′;
o SELECT MIN (Joinyear) FROM APPLICANTS WHERE Gender=’m’;
o SELECT AVG (Fee) FROM APPLICANTS WHERE C_ID=’A0T OR C_ID=’A05′;
o SELECT SUM- (Fee), C_ID FROM C_ ID GROUP BY C_ID HAVING COUNT(*)=2;

© Swamy’s Group of Schools, Porur, Chennai 4|Page


8. Write SQL queries for (1) to (7) and write the output for the SQL queries on the basis of table
ITEMS and TRADERS:

1. To display the details of all the items in ascending order of item names (i.e., INAME).
2. To display item name and price of all those items, whose price is in the range of 10000 and
22000 (both values inclusive).
3. To display the number of items, which are traded by each trader. The expected output of this
query should be:
4. To display the price, item name and quantity (i.e., qty) of those items which have quantity more
than 150.
5. To display the names of those traders, who are either from DELHI or from MUMBAI.
6. To display the names of the companies and the names of the items in descending order of
company names.
7. Obtain the outputs of the following SQL queries based on the data given in tables ITEMS and
TRADERS above.
o SELECT MAX (PRICE), MIN (PRICE) FROM ITEMS;
o SELECT PRICE*QTY FROM ITEMS WHERE CODE-1004;
o SELECT DISTINCT TCODE FROM ITEMS;
o SELECT INAME, TNAME FROM ITEMS I, TRADERS T WHERE I.TCODE=T.TCODE AND QTY< 100;
9. Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned shown in
(g) parts on the basis of tables PRODUCTS and SUPPLIERS

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).

© Swamy’s Group of Schools, Porur, Chennai 5|Page


c) To display the number of products, which are supplied by each supplier. i.e., the expected
output should be;
S01 2
S02 2
S03 1
d) To display the price, product name 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 above.
a. SELECT DISTINCT SUPCODE FROM PRODUCTS;
b. SELEC MAX (PRICE), MIN (PRICE) FROM PRODUCTS;
c. SELECT PRICE*QTY FROM PRODUCTS WHERE PID = 104;
d. SELECT PNAME, SNAME FROM PRODUCTS P, SUPPLIERS S
WHERE E SUPCODE = S. SUPCODE AND QTY>100;
10. Consider the following tables CARDEN and CUSTOMER and answer (b) and (c) parts of this
question:

1. Write SQL commands for the following statements:


o To display the names of all the silver-coloured cars.
o To display names of car, make and capacity of cars in descending order of their sitting
capacity.
o To display the highest charges at which a vehicle can be hired from CARDEN.
o To display the customer name and the corresponding name of the cars hired by them.
2. Give the output of the following SQL queries:
o SELECT COUNT(DISTINCT Make) FROM CARDEN;
o SELECT MAX(Charges), MIN (Charges) FROM CARDEN;
o SELECT COUNTS), Make FROM CARDEN;

© Swamy’s Group of Schools, Porur, Chennai 6|Page

You might also like