3 CS Structured Query Language

Downloaded from www.studiestoday.
com
NCERT Solutions Class 12
Computer Science
Chapter - Structured Query Language

Question 1:
Differentiate between delete and drop table command ?
Аnswer:
DELETE command is used to remove information from a particular row or rows. If used
without any condition, it will delete all row information but not the structure of the table. It
is a DML command. DROP table command is used to remove the entire structure of the
om
table and information. It is a DDL command.
.c
Question 2:
What is the use of wildcard ? ay
Аnswer:
od
The wildcard operators are used with the LIKE operator to search a value similar to a specific
pattern in a column. There are 2 wildcard operators.
st
% – represents 0,1 or many characters

– = represents a single number or character
e
di
Question 3:
tu
Write SQL query to add a column total price with datatype numeric and size 10, 2 in a table
.s
product.
Аnswer:
w
ALTER TABLE product ADD total price number

w
w
Question 4:
While creating table ‘customer’, Rahul a forgot to add column ‘price’. Which command is
used to add new column in the table. Write the command to implement the same.
Аnswer:
ALTER TABLE customer ADD price number(10,2)
Question 5:
Deepika wants to remove all rows from the table BANK. But he needs to maintain the
structure of the table. Which command is used to implement the same ?
Аnswer:
DELETE FROM BANK
Question 6:
Sonal needs to display name of teachers, who have “0” as the third character in their name.
Downloaded from www.studiestoday.com

She wrote the following query.

Select name from teacher where name = “$$0?”; But the query isn’t producing the result.
Identify the problem.
Аnswer:
The wildcards are incorrect. The corrected query is SELECT name FROM teacher WHERE
name
LIKE’ _ _ 0%’
Question 7:
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;
om
3. SELECT TEACHERNAME FROM SCHOOL WHERE EXPERIENCE >12 ORDER BY TEACHER
NAME;
.c
4. SELECT COUNT (*), GENDER FROM ADMIN GROUP BY GENDER;
Table : SCHOOL
ay
od
CODE TEACHER SUBJECT DOJ PERIODS EXPERIENCE

st
1001 RAVI SHANKAR ENGLISH 12/3/2000 24 10

e
di
1009 PRIYARAI PHYSICS 03/09/1998 26 12

tu
1203 LIS ANAND ENGLISH 09/04/2000 27 5

.s
1045 YASHRAJ MATHS 24/8/2000 24 15

w
1123 GANAN PHYSICS 16/7/1999 28 3

w
w
1167 HARISHB CHEMISTRY 19/10/1999 27 5
1215 UMESH PHYSICS 11/05/1998 22 16

TABLE : ADMIN
CODE GENDER DESIGNATION
1001 MALE VICE PRINCIPAL
1009 FEMALE COORDINATOR
1203 FEMALE COORDINATOR
1045 MALE HOD

1123 MALE SENIOR TEACHER
1167 MALE SENIOR TEACHER
1215 MALE HOD
Аnswer:
(i)
VICE PRINCIPAL 01
(ii)
16
om
(iii)
.c
UMESH ay
YASH RAJ
od
st
Short Answer Type Questions-II [3 mark each]

e
di
Question 1:
tu
.s
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based
w
on the tables.
w
w
Table: VEHICLE
CODE VTYPE PERKM
101 VOLVO BUS 160
102 AC DELUXE BUS 150
103 ORDINARY BUS 90
105 SUV 40
104 CAR 20
Note :
1. PERKM is Freight Charges per Kilometer.

2. VTYPE is Vehicle Type.
Table: TRAVEL
No. NAME TDATE KM CODE NOP
101 Janish Kin 2015-11-13 200 101 32
103 Vedika Sahai 2016-04-21 100 103 45
105 Tarun Ram 2016-03-23 350 102 42
102 John Fen 2016-02-13 90 102 40
107 Ahmed Khan 2015-01-10 75 104 2
104 Raveena 2016-05-28 80 105 4
om
106 Kripal Anya 2016-02-06 200 101 25
.c
Note:
ay
 NO is Traveller Number
od
 KM is Kilometer Travelled
 NOP is number of travellers travelled in vehicle.
st
 TDATE is Travel Date

e
di
1. To display NO, NAME, TDATE from the table TRAVEL in descending order of NO.
2. To display the NAME of all the travelers from the table TRAVEL who are travelling by
tu
vehicle with code 101 or 102.

.s
3. To display the NO and NAME of those travelers from the table TRAVEL who travelled
between ‘2015-1231’ and ‘2015-04-01’.
w
4. To display all the details from table TRAVEL for the travelers, who have travelled
w
distance more than 100 KM in ascending order of NOP .

w
5. SELECT COUNT (*), CODE FROM TRAVEL GROUP BY CODE HAVING COUNT(*)>1;
6. SELECT DISTINCT CODE FROM TRAVEL;
7. SELECT A. CODE,NAME, VTYPE
FROM TRAVEL A,VEHICLE B

WHERE A.CODE=B.CODE AND ‘KM<90;
8.SELECT NAME, KM*PERKM
WHERE A.CODE=B. CODE AND A.CODE=’105’;
Аnswer:
1. Select NO, Name, TDATE from TRAVEL order by NO desc

2. Select NAME from TRAVEL, where CODE in (101, 102)

3. Select NO, NAME from TRAVEL where TDATE between ’2015-12-31′ and ‘2015-04-01’.
4. Select * from TRAVEL where KM > 100 order by NOP.
5.
COUNT (*) CODE
2 101
2 102
6.
DISTANCE (CODE)
101
om
103
.c
102
104
ay
od
105
7.
st
CODE NAME VTYPE

e
di
104 Ahmed khan CAR

tu
105 Raveena SUV

.s
8.
w
NAME KM*PERKM
w
Tarun Ram 14000

w
Question 2:
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based
on the tables.
Table :VEHICLE
VCODE VEHICLETYPE PERKM
VOl VOLVO BUS 150
V02 AC DELUXE BUS 125
V03 ORDINARY BUS 80

V0’5 SUV 30
V04 CAR 18
Note:
PERKM is Freight Charges per kilometer.
Table : TRAVEL
CNo CNAME TRAVELDATE KM VCODE NOP
101 K.Niwal 2015-12-13 200 VOl 32
103 Fredrick Sym 2016-03-21 120 V03 45
105 Hitesh Jain 2016-04-23 450 V02 42
om
102 Ravi Anish 2016-01-13 80 V02 40
107 John Malina 2015-02-10 65 V04 2
.c
104 Sahanubhuti 2016-01-28
ay 90 V0 5 4
106 Ramesh Jaya 2016-04-06 100 VOl 25

od
Note:
e st
 Km is Kilometers Travelled
di
 NOP is number of passengers travelled in vehicle.

tu
1.To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO.
.s
2.To display the CNAME of all the customers from the table TRAVEL who are travelling by
vehicle with code V01 or V02.
w
3.To display the CNO and CNAME of those customers from the table TRAVEL who travelled
w
between ‘2015-12-31’ and ‘2015-05-01’.

w
4.To display all the details from table TRAVEL for the customers, who have travel distance
more than 120 KM in ascending order of NOP.
5.SELECT COUNT (*) , VCODE FROM TRAVEL
GROUP BY VCODE HAVING COUNT(*)>1;
6. SELECT DISTINCT VCODE FROM TRAVEL;
7. SELECT A. VCODE, CNAME, VEHICLETYPE
WHERE A.VCODE=B.VCODE AND KM<90;
8. SELECT CNAME, KM*PERKM FROM TRAVEL A,VEHICLE B
WHERE A.VCODE=B . VCODE AND A.VCODE= ‘V05 ‘ ;
Аnswer:
(i) Select CNO, CNAME, TRAVELDATE from TRAVEL order by CNO desc
(ii) Select CNAME from TRAVEL, where VCODE in (‘VOl’, ‘ V02 ‘)
(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.

(v)
COUNT (*) VCODE
2 V01
2 V02
(vi)
DISTANCE (CODE)
V01
om
V03
.c
V02
V04
ay
od
V05
(vii)
e st
di
VCODE CNAME VEHICLETYPE

tu
V04 JOHN MALINI CAR

.s
(viii)
CNAME KM*PERKM
w
Sahanubhuti 30
w
Note: PERKM is neither given in query nor in TABLE so no output is also acceptable.
w
Long Answer Type Questions [ 4 marks each]
Question 1:
Consider the following tables FACULTY and COURSES. Write SQL commands for the
statements (i) to (v) and give outputs for SQL queries (vi) to (vii)
FACULTY
F_ID Fname Lname Hire_date Salary
102 Amit Mishra 12-10-1998 12000

103 Nitin Vyas 24-12-1994 8000
104 Rakshit Soni 18-5-2001 14000
105 Rashmi Malhotra 11-9-2004 11000
106 Sulekha Srivastava 5-6-2006 10000

COURSES
C_ID FJD Cname
C21 102 Grid Computing 40000
C22 106 System Design 16000
om
C23 104 Computer Security 8000
C24 106 Human Biology 15000
.c
C25 102 Computer Network
ay 20000
C26 105 Visual Basic 6000

od
(i) To display details of those Faculties whose salary is greater than 12000.
Аnswer:
st
Select * from faculty

e
where salry > 12000;

di
(ii) To display the details of courses whose fees is in th range of 15000 to 50000(both values
tu
included).
Аnswer:
.s
Select * from Courses

w
where fees between 15000 and 50000;

(iii)To increase the fees of all courses by 500 of “System Design” Course.
w
<strongАnswer:
w
Update courses set fees = fees + 500

where Cname = “System Design”;
(iv)To display details of those courses which are taught by ‘Sulekha’ in descending order of
courses.
Аnswer:
Select * from faculty fac, courses cour
where fac.f_id = cour.f_id and fac.fname = ‘Sulekha’ order by cname desc;
(v)Select COUNT (DISTINCT F_ID) from COURSES;
Аnswer:
4
(vi)Select MIN (Salary) from FACULTY, COURSES where COURSES.F_ID = FACULTY.FJD;
Аnswer:
6000

Question 2:
Consider the following DEPT and WORKER tables. Write SQL queries for (i) to (iv) and find
outputs for SQL queries (v) to (viii):
TABLE : DEPT
DCODE DEPARTMENT CITY
D01 MEDIA DELHI
D02 MARKETING DELHI
D03 INFRASTRUCTURE MUMBAI
D05 FINANCE KOLKATA
D04 HUMAN RESOURCE MUMBAI
om
TABLE : WORKER
.c
WNO NAME . Y; DOJ DOB
ay GENDER DCODE
1001 George K 2013-09-02 1991-09-01 MALE D01

od
1002 Ryma Sen 2012-12-11 1990-12-15 FEMALE D03

st
1003 Mohitesh 2013-02-03 1987-09-04 MALE D05

e
di
1007 Anil Jha 2014-01-17 1984-10-19 MALE D04

tu
1004 Manila Sahai 2012-12-09 1986-11-14 FEMALE DOl

.s
1005 RSAHAY 2013-11-18 1987-03-31 MALE D02

w
1006 Jaya Priya 2014-06-09 1985-06-23 FEMALE DQ5

w
Note : DOJ refers to date of joining and DOB refers to date of birth of workers.
w
(i)To display Wno. Name, Gender from the table WORKER in descending order of Wno. Ans.
Аnswer:
SELECT WNO, Name, Gender FROM Worker
ORDER BY Wno DESC;
(ii)To display the Name of all the FEMALE workers from the table WORKER.
Аnswer:
SELECT Name FROM Worker
WHERE gender = ‘FEMALE’;
(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’.
Аnswer:
SELECT Wno, Name FROM Worker
WHERE DOB BETWEEN ‘1987-01-01’ AND ‘1991-12-01’;

OR
SELECT Wno, Name FROM worker

WHERE DOB > = 1987-01-01′ AND DOB < = ‘1991-12-01’;
OR
WHERE DOB > = ‘1987-01-01’ AND DOB < = ‘1991-12-01’;

(iv)To count and display MALE workers who have joined after ‘1986-01-01’.
Аnswer:
SELECT COUNT (*) FROM Worker
WHERE GENDER = ‘MALE’ AND DOJ > ‘198601-01’;
om
OR
.c
SELECT * FROM Worker ay
WHERE GENDER = ‘MALE’ AND DOJ > ‘198601-01’;
(Any valid query for counting and/or displaying for male workers will be awarded 1 mark)
od
(v) SELECT COUNT (*), DCODE FROM WORKER GROUP BY DCODE HAVING COUNT (*) > 1;
Аnswer:
st
COUNT (*) DCODE

e
di
2 D01
tu
.s
2 D05
(vi)SELECT DISTINCT DEPARTMENT FROM DEPT;
w
Аnswer:
w
Department
w
MEDIA
MARKETING
INFRASTRUCTURE
FINANCE
HUMAN RESOURCE
(viii)SELECT NAME, DEPARTMENT, CITY FROM WORKER W, DEPT D WHERE W DCODE = D.
DCODE AND WNO < 1003;
Аnswer:
NAME DEPARTMENT CITY
George K MEDIA DELHI
Ryma Sen infrastructure MUMBAI

(viii) SELECT MAX (DOJ), MIN (DOB) FROM WORKER;

Аnswer: MAX (DOJ) MIN (DOB)
2014-06-09 1984-10-19
Note : In the output queries, please ignore the order of rows
Question 3:
Consider the following DEPT and EMPLOYEE tables. Write SQL queries for (i) to (iv) and find
outputs for SQL queries (v) to (viii).
TABLE : DEPT
DCODE DEPARTMENT LOCATION
D01 INFRASTRUCTURE DELHI
om
D02 MARKETING DELHI
D03 MEDIA MUMBAI
.c
DOS FINANCE ay KOLKATA
D04 HUMAN RESOURCE MUMBAI

od
TABLE : EMPLOYE
e st
ENO NAME DOJ DOB GENDER DCODE

di
1001 GEORGE K 2013-09-02 1991-09-01 MALE D01

tu
1002 Ryma Sen 2012-12-11 1990-12-15 FEMALE D03

.s
1003 Mohitesh 2013-02-03 1987-09-04 MALE D05

w
w
1007 Anil Jha 2014-01-17 198410-19 MALE D04

w
1004 Manila Sahai 2012-12-09 1986-11-14 FEMALE D01
1005 RSAHAY 2013-11-18 1987-03-31 MALE D02
1006 JAYA Priya 2014-06-09 1985-06-23 FEMALE D05

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.
Аnswer: SELECT Eno, Name, Gender FROM Employee ORDER BY Eno;
(ii)To display the Name of all the MALE employees from the table EMPLOYEE.
Аnswer: SELECT Name FROM EMPLOYEE WHERE
Gender = ‘MALE’;
(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’.
Аnswer:

SELECT Eno, Name FROM Employee

OR
SELECT Eno, Name FROM Employee

WHERE DOB > = ‘1987-01-01′ AND DOB < =’1991-12-01’;
OR
SELECT Eno, Name FROM Employee WHERE DOB > ‘1987-01-01’ AND DOB < ‘199112-01’;
om
OR
.c
WHERE DOB > = ‘1987-01-01’ AND DOB < = ‘1991-12-01’;
ay
OR
od
WHERE DOB > ‘1987-01-01’ AND DOB < ‘199112-01’);

st
(iv)To count and display FEMALE employees who have joined after ‘1986-01-01’;
e
Аnswer: SELECT count (*) FROM Employee

di
WHERE GENDER = ‘FEMALE’ AND DOJ > ‘1986-01-01’;

tu
OR
.s
w
SELECT * FROM Employee

w
WHERE GENDER = ‘FEMALE’ AND DOJ > ‘1986-01-01’;

w
(Any valid query for counting and/or displaying for female employees will be awarded 1
mark)
(v)SELECT COUNT (*), DCODE FROM EMPLOYEE
GROUP BY DCODE HAVING COUNT (*) > 1;
Аnswer:
COUNT DCODE
2 D01
2 D05
(½Mark for correct output)

(vi)SELECT DISTINCT DEPARTMENT FROM DEPT
Аnswer: Department
INFRASTRUCTURE
MARKETING
MEDIA
FINANCE
HUMAN RESOURCE
(vii) SELECT NAME, DEPARTMENT FROM EMPLOYEE E, DEPT D WHERE E. DCODE = D.DCODE
AND ENO <1003;
NAME DEPARTMENT
George K MEDIA
om
Ryma Sen infrastructure
.c
(viii) SELECT MAX (DOJ), MIN (DOB) FROM EMPLOYEE;
Аnswer: ay
MAX (DOJ) MIN (DOB)
2014-06-09 1984-10-19
od
Note : In the output queries, please ignore the order of rows.

Question 4:
st
Write SQL commands for the queries (i) to (iv) and output for (v) & (viii) based on a table
e
COMPANY and CUSTOMER

di
tu
CID NAME CITY PRODUCTNAME

.s
111 SONY DELHI TV

w
222 NOKIA MUMBAI MOBILE

w
w
333 ONIDA DELHI TV
444 SONY MUMBAI MOBILE
555 BLACKBERRY MADRAS MOBILE
666 DELL DELHI LAPTOP
CUSTID NAME PRICE QTY CID
101 ROHAN SHARMA 70,000 20 222
102 DEEPAK KUMAR 50,000 10 666

103 MOHAN KUMAR 30,000 5 111
104 SAHIL BANSAL 35,000 3 333
105 NEHA SONI 25,000 7 444
106 SONAL AGGARWAL 20,000 5 333
107 ARUN SINGH 50,000 15 666
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 customer whose name starts with S?
4. To add one more column total price with decimal(10,2) to the table customer
5. SELECT COUNTO ,CITY FROM COMPANY GROUP BY CITY;
om
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
.c
COMPANY.CID=CUSTOMER.CID AND PRODU CTN AME=”MOBILE”;
ay
Аnswer:
od
1. To display those company name which are having prize less than 30000.
SELECT NAME FROM COMPANY WHERE COMPANY.CID=CUSTOMER. CID AND PRICE <
st
30000
e
2.To display the name of the companies in reverse alphabetical order.

di
SELECT NAME FROM COMPANY

ORDER BY NAME DESC?;
tu
3.To increase the prize by 1000 for those customer whose name starts with “S”
.s
UPDATE CUSTOMER
SET PRICE = PRICE + 1000;
w
WHERE NAME LIKE ‘S%’;

w
4.To add one more column total price with decimal(10,2) to the table customer
w
ALTER TABLE CUSTOMER

ADD TOTALPRICE DECIMAL(10,2);
5.SELECT COUNT(*) ,CITY FROM COMPANY GROUP BY CITY;
3 DELHI
2 MUMBAI
1 MADRAS
6.SELECT MIN(PRICE), MAX(PRICE) FROM
CUSTOMER WHERE QTY> 10;
50000,70000
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”;
MOBILE MUMBAI 70000
MOBILE MUMBAI 25000

Question 5:
Consider the following tables SCHOOL and ADMIN and answer this question :
Table : SCHOOL
CODE TEACHERNAME SUBJECT DOJ PERIODS EXPERIENCE
1001 Ravi Shankar English 12/3/2000 24 10
om
1009 Priya Rai Physics 03/09/1998 26 , 12
1203 Lisa Anand English 09/04/2000 27 5
.c
1045 Yashraj Maths
ay
24/08/2000 24 15
1123 Ganan Physics 16/07/1999 28 3

od
1167 Harish B Chemistry 19/10/1999 27 5

st
1215 Umesh Physics 11/05/1998 22 16

e
di
Table : Admin
tu
Code Gender Designation

.s
w
1001 Male Vice Principal

w
1009 Female Coordinator

w
1203 Female Coordinator
1045 Male HOD
1123 Male Senior Teacher
1167 Male Senior Teacher
1215 Male HOD

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.
Аnswer:
1. To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25.
SELECT TEACHERNAME, PERIODS
FROM SCHOOL WHERE PERIODS >25.
2.To display all the information from the table SCHOOL in descending order of experience.
 SELECT * FROM SCHOOL;
3.To display DESIGNATION without duplicate entries from the table ADMIN.
SELECT DISTINCT DESIGNATION FROM ADMIN;
4.To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL
om
and ADMIN of Male teachers.
SELECT TEACHERNAME.CODE
DESIGNATION FROM SCHOOL.CODE = ADMIN.CODE
.c
WHERE GENDER = MALE; ay
Question 6:
od
Answer the questions (a) and (b) on the basis of the following tables SHOPPE and
ACCESSORIES.
st
Аnswer:
e
di
Id SName Area
tu
S001 ABC computronics CP

.s
w
S002 All Infotech Media GKII

w
S003 Tech Shoppe CP

w
S004 Geeks Tecno Soft Nehru Place
S005 Hitech Tech Store Nehru Place
No Name Price Id
A01 Mother Board 12000 SOI
A02 Hard Disk 5000 SOI

A03 Keyboard 500 S02
A04 Mouse 300 SOI
A05 Mother Board 13000 S02
A06 Keyboard 400 S03
A07 LCD 6000 S04
T08 LCD 5500 S05
T09 Mouse 350 S05
T10 Hard Disk 4500 S03
om
1. To display Name and Price of all the Accessories in ascending order of their Price.
2. To display Id and SName of all Shoppe located in Nehru Place.
3. To display Minimum and Maximum Price of each Name of Accessories.
.c
4. To display Name, Price of all Accessories and their respective SName where they are
ay
available.
od
(b)Write the output of the following SQL commands:
st
1. SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE > =5000;

e
2. SELECT AREA, COUNT(*) FROM SHOPPE GROUP BY AREA;

di
3. SELECT COUNT (DISTINCT AREA) FROM SHOPPE;

4. SELECT NAME, PRICE*0.05 DISCOUNT FROM ACCESSORIES
tu
.s
Аnswer: (a) (i) SELECT Name, Price FROM ACCESSORIES ORDER BY Prices;
w
(ii)SELECT ID, SName FROM SHOPPE WHERE Area=”Nehru Place”;

(iii)SELECT Name, max (Price); min(Price) FROM ACCESSORIES, Group By Name;
w
(iv)SELECT Name, price, Sname FROM ACCESSORIES, SHOPPE WHERE

w
SHOPPE.ID=ACCESSORIES.ID
(b)(i) Name
Mother Board
Hard Disk
LCD
(ii)
Area Count
CP 2
GK II 1
Nehru Place 2

(iii) count(Distinct Area)

3
(iv) Name
Name DISCOUNT
600 600
Hard Disk 250
Key Board 20
Hard Disk 225

Question 7:
Answer the questions (a) to (g) on the basics of the following tables APPLICANTS and
om
COURSB.
.c
1. To display name, fee, gender, join year about the applicants, who have joined before
2010.
ay
2. To display names of applicants, who are paying fee more than 30000.
od
3. To display names of all applicants in ascending order of their join year.
4. To display the year and the total number of applicants joined in each YEAR from the
st
table APPLICANTS.
e
di
Join
No Name Fee Gender C_ID
Year
tu
1012 Amandeep 30000 M A01 2012

.s
w
1102 Avisha 25000 F A02 2009

w
1103 Ekant 30000 M A02 2011

w
1049 Arun 30000 M A03 2009
1025 Amber 40000 M A02 2011
1106 Ela 40000 F A05 2010
1017 Nikita 35000 F A03 2012
1108 Arleena 30000 F A03 2012
2109 Shakti 35000 M A04 2011
1101 Kirat 25000 M A01 2012
Table Courses

C_ID Course
A01 Fashion Design
A02 Networking
A03 Hotel Management
A04 Event Management
A05 Office Management

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 statements: of following SQL statements :
om
(i)SELECT Name, Join year FROM APPLICANTS
WHERE GENDER=’F’ and C_ID=’A02′;
.c
(ii) SELECT MIN (Join year) FROM APPLICANTS
ay
(iii)SELECT AVG (Fee) FROM APPLICANTS WHERE C_ID=’A01′ OR C_ID=’A05′;
(iv)SELECT SUM (Fee), C_ID FROM APPLICANTS
od
GROUP BY C_ID HAVING COUNT(*)=2;
Аnswer: (a) SELECT NAME,FEE,GENDERJOINYEAR FROM APPLICANTS
st
WHERE JOINYEAR<2000;
(b)SELECT NAME FROM APPLICANTS
e
WHERE FEE>30000;
di
(c)SELECT NAME FROM APPLICANTS

tu
ORDERBY JOINYEAR ASC;

(d)SELECT YEAR, COUNTf) FROM APPLICANTS;
.s
w
(e)SELECT C_ID, COUNT(*) FROM APPLICANTS, COURSES

w
w
WHERE APPLICANTS.C_ID=COURSES; C_ID;

(f)SELECT NAME,COURSE FROM
APPLICANTS, COURSES
WHERE APPLICANTS.C_ID=COURSES. C_ID;
(g)(i) Avisha 2009
(ii)2009
(iii)67
(iv)55000 A01
Question 8:
Write SQL queries for (a) to (g) and write the output for the SQL queries mentioned shown
in (hi) to (h4) parts on the basis of table ITEMS and TRADERS :
Table : ITEMS

CODE INAME QTY PRICE COMPANY TCODE
1001 DIGITAL PAD 12i 120 11000 XENITA T01
1006 LED SCREEN 40 70 38000 SANTORA T02
1004 CAR GPS SYSTEM 50 21500 GEOKNOW T01
1003 DIGITAL CAMERA 12X 160 8000 DIGICLICK T02
1005 PEN DRIVE 32 GB 600 1200 STOREHOME T03

Table : TRADERS
TCode TName City
om
T01 MUMBAI
ELECTRONIC SALES BUSY STORE CORP DISP HOUSE
T03 DELHI
INC
T02 CHENNAI
.c

ay
To display the details of all the items in ascending order of item names (i.e., INAME).
 To display item name and price of all those items, whose price is in the range of 10000
od
and 22000 (both values inclusive).

 To display the number of items, which are traded by each trader. The expected output
st
of this query should be:

e
 To display the price, item name and quantity (i.e., qty) of those items which have
di
quantity more than 150.

tu
 To display the names of those traders, who are either from DELHI or from MUMBAI.
 To display the names of the companies and the names of the items in descending
.s
order of company names.

w
 Obtain the outputs, of the following SQL queries based on the data given in tables
ITEMS and TRADERS above.
w
w
(h1)SELECT MAX (PRICE), MIN (PRICE) FROM ITEMS;

(h2) SELECT PRICE*QTY
FROM ITEMS WHERE CODE-1004;
(h3) SELECT DISTINCT TCODE FROM ITEMS;
(h4) SELECT INAME, TNAME
FROM ITEMS I, TRADERS T WHERE I.TCODE=T.TCODE AND
QTY<100;
Аnswer:
(a) SELECT INAME FROM ITEMS ORDER BY INAME ASC;
(b)SELECT INAME, PRICE FROM ITEMS WHERE PRICE => 10000 AND PRICE =< 22000;
(c)SELECT TCODE, COUNT (CODE) FROM
ITEMS GROUP BY TCODE;
(d)SELECT PRICE, INAME, QTY FROM ITEMS
WHERE (QTY> 150);

(e)SELECT TNAME FROM TRADERS

WHERE (CITY = “DELHI”) OR (CITY = “MUMBAI”)
ORDER BY COMPANY DESC;
(g) (h1) 38000
1200
(h2) 1075000
(h3) T01
T02
T03
(h4) LED SCREEN 40 DISPHOUSE INC
CAR GPS SYSTEM ELECTRONICS
SALES
Question 9:
om
Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned shown
in (hi) to (h4) parts on the basis of tables PRODUCTS and SUPPLIERS
Table : PRODUCTS
.c
ay
PID PNAME QTY PRICE COMPANY SUPCODE
od
101 DIGITAL CAMERA 14X 120 12000 RENBIX SOI

st
102 DIGITAL PAD lli 100 22000 DIGI POP S02

e
104 PEN DRIVE 16 GB 500 1100 STOREKING SOI

di
tu
106 LED SCREEN 32 70 28000 DISPEXPERTS S02

.s
105 CAR GPS SYSTEM 60 12000 MOVEON S03

w
Table : SUPPLIERS
w
w
SUPCODE SNAME CITY
SOI GET ALL INC KOLKATA
S03 EASY MARKET DELHI
CORP
S02 DIGI BUSY GROUP CHENNAI

(a)To display the details of all the products in ascending order of product names (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;
2

2
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.
(gl) SELECT DISTINCT SUPCODE FROM PRODUCTS;
(g2) SELEC MAX (PRICE), MIN (PRICE) FROM PRODUCTS
(g3) SELECT PRICE*QTY
FROM PRODUCTS WHERE PID = 104;
(g4) SELECT PNAME, SNAME
FROM PRODUCTS P, SUPPLIERS S WHERE P SUPCODE = S. SUPCODE AND QTY>100;
om
Аnswer:
(a) SELECT * FROM PRODUCTS ORDER. BY PNAME ASC;
.c
(b)SELECT PNAME, PRICE FROM PRODUCTS WHERE ((PRICE => 10000) AND (PRICE = <
15000)); ay
(c)SELECT SUPCODE, COUNT (PID) [Yz] FROM PRODUCTS GROUP BY SUPCODE;
od
(d)SELECT PRICE, PNAME, QTY FROM PRODUCTS WHERE (QTY > 100);
(e)SELECT SNAME FROM SUPPLIERS WHERE ((CITY = “DELHI”) OR (CITY = “CHENNAI”));
st
(f)SELECT COMPANY, PNAME FROM PRODUCTS ORDER BY COMPANY DESC;

(g) SOI
e
(g1) S02
di
S03
tu
(g2) 28000
1100
.s
(g3) 550000
w
(g4) PNAME SNAME

w
DIGITAL CAMERA 14 X GET ALL INC

PENDRIVE 16 GB GET ALL INC
w
Question 10:
Give a suitable example of a table with sample data and illustrate Primary and Alternate
Keys in it. Consider the following tables CARDEN and CUSTOMER and answer (b) and (c)
parts of this question :
Table : CARDEN
Ceode CarName Make Colour Capacity Charges
501 A-Star Suzuki RED 3Q 14
503 Indigo Tata SILVER 3 12
502 Innova Toyota WHITE 7 15

509 SX4 Suzuki SILVER 4 14
510 C Class Mercedes RED 4 35

Table : CUSTOMER
CCode Cname Ceode
1001 Hemant Sahu 501
1002 Raj Lai 509
1003 Feroza Shah 503
1004 Ketan Dhal 502
om
(b)Write SQL commands for the following statements:
1. To display the names of all the silver coloured cars.
.c
2. Tp display names of car, make and capacity of cars in descending order of their sitting
ay
capacity.
3. To display the highest charges at which a vehicle can be hired from CARDEN.
od
4. To display the customer name and the corresponding name of the cars hired by them.
st
(c)Give the output of the following SQL queries:

e
(i)SELECT COUNT(DlST!NCT Make) FROM CARDEN;

di
(ii)SELECT MAX(Charges), MIN (Charges) FROM CARDEN;

SELECT COUNTO, Make FROM CARDEN;
tu
(iv) SELECT CarName FROM CARDEN WHERE Capacity=4;

.s
Аnswer:
(a) Primary Key of CARDEN = Ceode of CARDEN
w
Alternate Key = CarName:

w
Primary key of Customer = Ceode

w
Alternate Key of Customer = Cname

(b) (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
CUSTOMER, CARDEN WHERE CARDEN. Ccode = CUSTOMER. Ccode;
(c) (i) 4
(ii) MAX(Charges) MIN(Charges)
35 12
(iii)5
(iv)SX4
C Class
Question 11:

(a) Give a suitable example of a table with sample data and illustrate Primary agd Candidate
Keys in it. Consider the following tables CABHUB and CUSTOMER and answer (b) and (c)
parts of this question :
Table : CABHUB
Vcode VehicleName Make Colour Capacity Charges
100 Innova Toyota WHITE 7 15
102 SX4 Suzuki BLUE 4 14
104 C Class Mercedes RED 4 35
105 A-Star Suzuki WHITE 3 14
108 Indigo Tata SILVER – 3 12
om
Table : CUSTOMER
.c
Ceode Cname
ay Vcode
1 Hemant Sahu 101

od
2 Raj Lai 108

st
3 Feroza Shah 105

e
di
4 Ketan Dhal 104

tu
(b) Write SQL commands for the following statements:

.s
1. To display the names of all the white coloured vehicles.

w
2. To display name of vehicle name and capacity of vehicles in ascending order of their
w
sitting capacity.
w
3. To display the highest charges at which a vehicle can be hired from CABHUB.
4. To display the customer name and the corresponding name of the vehicle hired by
them.
(c)Give the output of the following SQL queries :
1. SELECT COUNT (DISTINCT Make) FROMCABHUB;

2. SELECT MAX(Charges), MIN(Charges) FROM CABHUB;
3. SELECT COUNT (*) Make FROM CABHUB;
4. SELECT Vehicle FROM CABHUB WHERE Capacity=4;
Аnswer:
(a) Primary Key of CABHUB = Vcode
Alternate key of CABHUB = Vehicle Name.

Primary Key of Customer = Ccode

Alternate Key of CUSTOMER = Cname.
(b) (i) SELECT VehicleName FROM CABHUB WHERE Colour =”WHITE”;
(ii)SELECT VehicleName, Capacity From CABHUB ORDER BY Capacity ASC;
(iii)SELECT MAX(Charges) FROM CABHUB;
(iv)SELECT Cname,VehicleName FROM CABHUB, CUSTOMER WHERE CUSTOMER.
Vcode=CABHUB. Vcode;
(c)4
(ii)Max(Charges) Min(Charges)
35 12
(iii)5
(iv)SX4
C Class
om
Long Answer Type Question – II
.c
Question 1:
ay
Watchid Watch_Name Price Type Qty_Store
od
‘wool High Time 10000 Unisex 100

st
W002 Life Time 15000 Ladies 150

e
di
W003 Wave 20000 Gents 200

tu
W004 HlghFashion 7000 Unisex 250

.s
W005 GoldenTime 25000 Gents 100

w
w
w
Watchid Qly_Sold Quarter
wool 10 1
W003 5 1
W002 20 2
W003 10 2
W001 15 3
W002 20 3
W005 10 3
W003 15 4

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
between 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.watchid!=s.watchid;
8. select watch_name, qty_store, sum(qty_sold), qty_store-sum(qty_sold) “Stock” from
watches w, sales where w. watchid=s. watchid group by s.watchid;
Аnswer:
om
(i) Select*from watches where watch_name like’Time’
(ii)select watchjname, price from watches where price between 5000 and 15000;
(iii)select sum(qty_store) from watches where type like ‘Unisex’;
.c
(iv)select watch name, qty_sold from watches w,sale s where w.watchid=s.watchid and
ay
quarter=l;
(v)
od
st
max(price) min(qty_store)
e
25000 100
di
(vi)
tu
.s
quarter suxn(qty_sold)
w
1 15
w
2 30
w
3 45
4 15
(vii)
Watch_name price type
HighFashion 7000 Unisex

(viii)
Watch_name qty_store qty_sold Stock

HighTime 100 25 75
LifTime 150 40 110
Wave 200 30 170
GoldenTime 100 10 90
om
.c
ay
od
ste
di
tu
.s
w
w
w

3 CS Structured Query Language

Uploaded by

Copyright:

Available Formats

3 CS Structured Query Language

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

3 CS Structured Query Language

Uploaded by

Copyright:

Available Formats

Downloaded from www.studiestoday.

NCERT Solutions Class 12

Chapter - Structured Query Language

% – represents 0,1 or many characters

ALTER TABLE product ADD total price number

Downloaded from www.studiestoday.com

She wrote the following query.

CODE TEACHER SUBJECT DOJ PERIODS EXPERIENCE

1001 RAVI SHANKAR ENGLISH 12/3/2000 24 10

1009 PRIYARAI PHYSICS 03/09/1998 26 12

1203 LIS ANAND ENGLISH 09/04/2000 27 5

1045 YASHRAJ MATHS 24/8/2000 24 15

1123 GANAN PHYSICS 16/7/1999 28 3

1167 HARISHB CHEMISTRY 19/10/1999 27 5

1215 UMESH PHYSICS 11/05/1998 22 16

CODE GENDER DESIGNATION

1001 MALE VICE PRINCIPAL

1009 FEMALE COORDINATOR

1203 FEMALE COORDINATOR

1045 MALE HOD

Downloaded from www.studiestoday.com

1123 MALE SENIOR TEACHER

1167 MALE SENIOR TEACHER

1215 MALE HOD

Short Answer Type Questions-II [3 mark each]

CODE VTYPE PERKM

101 VOLVO BUS 160

102 AC DELUXE BUS 150

103 ORDINARY BUS 90

1. PERKM is Freight Charges per Kilometer.

Downloaded from www.studiestoday.com

2. VTYPE is Vehicle Type.

No. NAME TDATE KM CODE NOP

101 Janish Kin 2015-11-13 200 101 32

103 Vedika Sahai 2016-04-21 100 103 45

105 Tarun Ram 2016-03-23 350 102 42

102 John Fen 2016-02-13 90 102 40

107 Ahmed Khan 2015-01-10 75 104 2

104 Raveena 2016-05-28 80 105 4

 TDATE is Travel Date

vehicle with code 101 or 102.

distance more than 100 KM in ascending order of NOP .

FROM TRAVEL A,VEHICLE B

1. Select NO, Name, TDATE from TRAVEL order by NO desc

Downloaded from www.studiestoday.com

COUNT (*) CODE

CODE NAME VTYPE

104 Ahmed khan CAR

105 Raveena SUV

Tarun Ram 14000

VCODE VEHICLETYPE PERKM

VOl VOLVO BUS 150

V02 AC DELUXE BUS 125

V03 ORDINARY BUS 80

Downloaded from www.studiestoday.com

CNo CNAME TRAVELDATE KM VCODE NOP

101 K.Niwal 2015-12-13 200 VOl 32

103 Fredrick Sym 2016-03-21 120 V03 45

105 Hitesh Jain 2016-04-23 450 V02 42