Database and SQL

Download CBSE Class 12th Standard CBSE Computer Science question papers, sample papers, important questions, and previous year solved papers in PDF format. Get free study materials, NCERT solutions, and exam preparation resources for Class 12th Standard CBSE Computer Science

Questions:

Generic 6

10 x 6 = 60

Write SQL commands for (a) to (g) and write the outputs for (h) on the basis of table COLLEGE
TABLE: COLLEGE

No	Name	Age	Department	Dateof Join	Basic	Sex
1	Shalaz	45	Biology	13/02/88	10500	M
2	Sammera	54	Biology	10/01/90	9500	F
3	Yagyen	43	Physics	27/02/98	8500	M
4	Pratyush	34	Chemistry	11/01/93	7500	M
5	Aren	51	Mathematics	22/01/91	8500	M
6	Reeta	27	Chemistry	14/02/94	9000	F
7	Urvashi	29	Biology	10/02/93	8500	F
8	Teena	35	Mathematics	02/02/89	10500	F
9	Viren	49	Mathematics	03/01/88	9000	M
10	Prakash	22	Physics	17/02/92	8000	M

(a) To change the Basic salary to 10500 of all those teachers from college who joined the COLLEGE after 01/02/89 and are above the age of 50.
(b) To display Name, Age and Basic of all those from COLLEGE, who belong to Physics and Chemistry department only.
(c) To display all the department names from COLLEGE with no duplication.
(d) To list names of all teachers from COLLEGE with their date of joining in ascending order within their Basic salaries in ascending order.
(e) To display maximum salary amongst the female teachers and also amongst the male teachers from COLLEGE.(Give a single command)
(f) To insert a new row in the table COLLEGE with the following data:
15,"ATIN",27,"Physics",'15/05/02',8500,"M"
(g) To delete a row from table COLLEGE in which NAME is VIREN.
(h) Give the output of the following SQL statements:
(i) SELECT COUNT(Name) FROM COLLEGE;
(ii) SELECT MIN(Basic)FROM COLLEGE WHERE Age>40;

Write SQL commands for (a) to (d) and write the outputs for (e) and (f) on the basis of table EMPLOYEE
TABLE: EMPLOYEE

SNO	NAME	BASIC	DEPARTMENT	DATEOFAPP	AGE	SEX
1	KARAN	8000	PERSONEL	27/03/97	35	M
2	DIVAKAR	9500	COMPUTER	20/01/98	34	M
3	DIVYA	7300	ACCOUNTS	19/02/97	34	F
4	ARUN	8350	PERSONNEL	01/01/95	33	M
5	SABINA	9500	ACCOUNTS	12/01/96	36	F
6	JOHN	7400	FINANCE	24/02/97	36	M
7	ROBERT	8250	PERSONNEL	20/02/97	39	M
8	RUBINA	9450	MAINTENANCE	22/02/98	37	F
9	VIKAS	7500	COMPUTER	13/01/94	41	M
10	MOHAN	9300	MAINTENANCE	19/02/98	37	M

(a) List the names of the employees, who are more than 34 years old sorted by NAME.
(b) Display a report, listing NAME, BASIC, DEPARTMENT AND annual salary. Annual salary equals to BASIC*12.
(c) To count the number of employees, who are either working in PERSONNEL or COMPUTER department.
(d) To insert a new row in the EMPLOYEE table
11,'VIJAY',9300,'FINANCE','13/7/98',35,"M"
(e) Give the output of the following SQL statements based on table EMPLOYEE:
(i) SELECT SUM(BASIC) FROM EMPLOYEE WHERE DEPARTMENT='PERSONNEL';
(ii) SELECT AVG(BASIC) FROM EMPLOYEE WHERE SEX='F';
(iii) SELECT MAX(BASIC) FROM EMPLOYEE WHERE DATEOFAPP>'22/02/97';
(iv) SELECT COUNT (DISTINCT DEPARTMENT) FROM EMPLOYEE;
(f) Assume that there is one more table INCHARGE in the database as shown below:
TABLE: INCHARGE

DEPT	HEAD
PERSONNEL	RAHUL
COMPUTER	SATYAM
ACCOUNTS	NATH
FINANCE	GANESH
MAINTENANCE	JACOB

What will be the output of the following query:
SELECT NAME, HEAD
FROM EMPLOYEE E.INCHARGE.I
WHERE E.DEPARTMENT=I.DEPT;

Study the following tables DOCTOR and SALARY and write SQL commands for the equations (a) to (d) and give outputs for SQL queries (e) and (f)
TABLE: DOCTOR

ID	NAME	DEPT	SEX	EXPERIENCE
101	John	ENT	M	12
104	Smith	ORTHOPEDIC	M	5
107	George	CARDIOLOGY	M	10
114	Lara	SKIN	F	3
109	K George	MEDICINE	F	9
105	Johnson	ORTHHOPEDIC	M	10
117	Lucy	ENT	F	3
111	Bill	MEDICINE	F	12
130	Morphy	ORTHOPEDIC	M	15

TABLE: SALARY

ID	BASIC	ALLOWANCE	CONSULTATION
101	12000	1000	300
104	23000	2300	500
107	32000	4000	500
114	12000	5200	100
109	42000	1700	200
105	18900	1690	300
130	21700	2600	300

(a) Display NAME of all doctors who are in MEDICINE department having more than 10 yrs experience from the table DOCTOR.
(b) Display the average salary of all doctors working in ENT department using the tables DOCTOR and SALARY.SALARY=BASIC+ ALLOWANCE.
(c) Display the minimum ALLOWANCE of female doctors.
(d) Display the highest consultation fee among all male doctors.
(e) SELECT COUNT(*) FROM DOCTOR WHERE SEX='F';
(f) SELECT NAME, DEPT, BASIC FROM DOCTOR, SALARY
WHERE DEPT='ENT' AND DOCTOR.ID=SALARY.ID;

Given the following family relation. Write SQL commands for questions (a) to (f) and output of (g) based on the table FAMILY
TABLE: FAMILY

NO	NAME	FEMALE MEMBERS	MALEMEMBERS	INCOME	OCCUPATION
1	Mishra	3	2	7000	Service
2	Gupta	4	1	50000	Business
3	Khan	6	3	8000	Mixed
4	Chaddha	2	2	25000	Business
5	Yadav	7	2	20000	Mixed
6	Joshi	3	2	14000	Service
7	Mayura	6	3	5000	Farming
8	Rao	5	2	10000	Service

(a) To select all the information of family, whose Occupation is service.
(b) To list the name of family, where female members are more than 3.
(c) To list all names of family with income in ascending order.
(d) To display family's name, male members and occupation of business family.
(e) To count the number of family, whose income is less than 10,000.
(f) To insert a new record in the FAMILY table with the following data
9,"D'SOUZA",2,1,15000,"Service"
(g) Give the output of the following SQL commands
(i) SELECT MIN(DISTINCT Income) FROM FAMILY;
(ii) SELECT MIN(FemaleMembers) FROM FAMILY WHERE Occupation='Mixed';
(iii) SELECT SUM(Income) FROM FAMILY WHERE Occupation='Service';
(iv) SELECT AVG(Income) FROM FAMILY;

Write SQL commands for (a) to(f) and write the outputs for (g) parts(i) to(iii) on the basis of table INTERIORS.
TABLE: INTERIORS

No	ITEMNAME	TYPE	DATEOFSTOCK	PRICE	DISCOUNT
1	Red rose	Double Bed	23/02/02	32000	15
2	Soft touch	Baby cot	20/01/02	9000	10
3	jerry's home	Baby cot	19/02/02	8500	10
4	Rough wood	Office Table	01/01/02	20000	20
5	Comfort zone	Double Bed	12/01/02	15000	20
6	Jerry look	Baby cot	24/02/02	7000	19
7	Lion king	Office Table	20/02/02	16000	20
8	Royal tiger	Sofa	22/02/02	30000	25
9	Park sitting	Sofa	13/12/01	9000	15
10	Dine Paradise	Dining Table	19/02/02	11000	15
11	White Wood	Double Bed	23/02/03	20000	20
12	James 007	Sofa	20/02/03	15000	15
13	Tomlook	Baby cot	21/02/03	7000	10

(a) To show all information about the Sofa from the ITERIORS table.
(b) Tolist the ITEMNAME, which are picked at more than 10000 from the INTERIORS table.
(c) Tolist ITEMNAME and TYPE of those oitems, in which DATEOFSTOCK is before 22/01/02 from the INTERIORS TABLE IN DESCENING ORDER OF itemname.
(d) To display ITEMNAME and DATEOF STOCK of those items, in which the discount percentage is more than 15 from INTERIORS table.
(e) To count the number of items, whose type is Double Bed from INTERIORS table.
(f) To insert a new row in the INTERIORS table with the following data
{14,'True Indian','Office Table','28/03/03',15000,20}
(g) Give the output of following SQL statements:
(i) SELECT COUNT(DISTINCT TYPE) FROM INTERIORS;
(ii) SELECT AVG(DISCOUNT) FROM INTERIORS WHERE TYPE='Babycot';
(iii) SELECT SUM(PRICE) FROM INTERIORS WHERE DATEOFSTOCK<'12/02/02';
OUTPUTS OF THE BELOW MENTIONED QUERIES SHOULD BE BASED ON ORIGINAL DATA GIVEN IN THE TABLES I.E. WITHOUT CONSIDERING THE INSERTION DONE IN (vi) PART OF THIS QUESTION.

Consider the following tables.Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e) to (h)
TABLE: SENDER

SENDER ID	Sender Name	Sender Address	SenderCity
ND01	R Jain	2, ABC Appts	New Delhi
MU02	H sinha	12,Newtown	Mumbai
MU15	SJha	27/A,Park Street	Mumbai
ND50	T Prasad	122-k,SDA	New Delhi

TABLE:RECEIPIENT

RecID	SenderID	RecName	RecAddress	RecCity
KO05	ND01	R Baipayee	5,Central Avenue	Kolkata
ND08	MU02	S Mahajan	116, A Vihar	New Delhi
MU19	ND01	HSingh	2A,Andheri East	Mumbai
MU32	MU15	P k Swamy	B5, C S Terminus	Mumbai
ND48	ND50	S Tripathi	13,B1 D,Mayur Vihar	New Delhi

(a) To display the names of all Sender from Mumbai.
(b) To display the RecID, Sender Name, Sender Address, Rec Name, Rec Address for every Recipient.
(c) To display Recipient details in ascending order of RecName.
(d) To display number of Recipients from each city.
(e) SELECT DISTINCT SenderCity FROM SENDER;
(f) SELECT A.SenderName, B.Recname FROM SENDER A, RECIPIENT B
   WHERE A.Sender ID=B.SenderID AND B>RecCity='Mumbai';
(g) SELECT Recname, RecAddress FROM RECIPIENT
   WHERE RecCity NOT IN('Mumbai','Kolkata');
(h) SELECT RecID, RecNam FROM RECIPIENT
   WHERE SenderID='MU02' OR SenderID='ND50';

Consider the following tables STUDENT and STREAM. Write SQL commands for the statements(a) to (d) and give outputs for SQL queries (e) to (h).
TABLE: STUDENT

SCODE	NAME	AGE	STRCDE	POINTS	GRADE
101	Amit	16	1	6	NULL
102	Arjun	13	3	4	NULL
103	Zaheer	14	2	1	NULL
105	Gagan	15	5	2	NULL
108	Kumar	13	6	8	NULL
109	Rajesh	17	5	8	NULL
110	Naveen	13	3	9	NULL
113	Ajay	16	2	3	NULL
115	Kapil	14	3	2	NULL
120	Gurdeep	15	2	6	NULL

TABLE: STREAM

STRDE	STRNAME
1	SCIENCE+COMP
2	SCIENCE+BIO
3	SCIENCE+ECO
4	COMMERCE+MATHS
5	COMMERCE+SOCIO
6	ARTS+MATHS
7	ARTS+SOCIO

(a) To display the name of streams in alphabetical order from table STREAM.
(b) To display the number of students whose POINTS are more than 5.
(c) To update GRADE to 'A' for all those students, who are getting more than 8 as POINTS.
(d) ARTS+MATHS stream is no more available. Make necesary change in table STREAM.
(e) SELECT SUM(POINTS) FROM STUDENT WHERE AGE>14;
(f) SELECT STRCDE, MAX(POINTS) FROM STUDENT GROUP BY STRCDE HAVING SCODE BETWEEN 105 AND 130;
(g) SELECT AVG(AGE) FROM STUDENT WHERE SCODE IN(102,105,110,120);
(h) SELECT COUNT(STRNAME) FROM STREAM WHERE STRNAME LIKE "SCI%";

Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this questions:
TABLE: STORE

Item No	Item	Scode	Qty	Rate	LastBuy
2005	Sharpener Classic	23	60	8	31-JUN-09
2003	Balls	22	50	25	01-FEB-10
2002	Gel Pen Premium	21	150	12	24-FEB-10
2006	Gel Pen Classic	21	250	20	11-MAR-09
2001	Eraser Small	22	220	6	19-JAN-09
2004	Eraser Big	22	110	8	02-DEC-09
2009	Ball Pen 0.5	21	180	18	03-NOV-09

TABLE: SUPPLIERS

Scode	Sname
21	Premimum Stationers
23	Soft Plastics
22	Tera Supply

(a) Write SQL commands for the following statements:
(i) To display details of all the items in the STORE table in ascending order of LastBuy.
(ii)To display ItemNo and Item name 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;

Consider the following tables CARDEN and CUSTOMER and answer(a) and (b) parts of this questions:
TABLE: CARDEN

Ccode	Car Name	Make	Color	Capacity	Charge
501	A-star	Suzuki	RED	3	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	CCode
1001	Hamant Sahu	501
1002	Raj Lal	509
1003	Feroza Shah	503
1004	Ketan Dhal	502

(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 sitting Capacity of cars in descending order of their sitting Capacity.
(iii) To display the higher Charges at which a vehicle can be hired from CARDEN.
(iv) Todisplay the customer name 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(Carges),MIN (Charges) FROM CARDEN;
(iii) SELECT COUNT(*) Make FROM CARDEN;
(iv) SELECT CarName FROM CARDEN WHERE Capacity=4;

10.

Write SQL queries for (a) to (f) and write the output for the SQL queries mentioned in (g) parts (i) to (iv) on the basis of tables ITEMS and TRADERS.
TABLE: ITEMS

code	IName	Qty	price	company	Tcode
1001	DIGITAL PAD 121	120	11000	XENITA	T01
1006	LED SCREEN 40	70	38000	SANTORA	T02
1004	CAR GPS SYSTEM	50	2150	GEOKNOW	T01
1003	DIGITAL CAMERA 12X	160	8000	DIGICLICK	T02
1005	PEN DRIVE 32 GB	600	1200	STOREHOME	T03

TABLE: TRADERS

Tcode	TName	City
T01	ELECTRONIC SALES	MUMBAI
T03	BUSY STORE CORP	DELHI
T02	DISP HOUSE INC	CHENNAI

(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 nd 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
T03	1
T02	2

(d) To display the Price, item name(ie.name) and quantity(ie.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 the tables ITEMS and TRADERS above.
(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.Code=T.TCode AND Qty<100;

Visit QB365-SLMS - https://www.qb365.in/slms/

Answers Key:

Generic 6

10 x 6 = 60

(a)UPDATE COLLEGE SET Basic=10500
WHERE DateofJoin>'01/02/89' AND Age>50;
(b) SELECT Name, Age, Basic From COLLEGE
WHERE Department='Physics' OR Department='Chemistry';
(c)SELECT DISTINCT Department FROM COLLEGE;
(d) SELECT Name, DateofJoin, Basic FROM COLLEGE ORDER BY Basic, DateofJoin;
(e) SELECT MAX(Basic) FROM COLLEGE GROUP BY SEX;
(f) INSERT INTO COLLEGE VALUES(15,'ATIN',27 'Physics','15/05/02'8500,'M');
(g) DELETE FROM COLLEGE WHERE Name='VIREN';
(h) The output is given after excluding the insertion and deletion of rows performing in above queries.
(i)

COUNT(Name)

(ii)

MIN(Basic)

8500

a) SELECT NAME FROM EMPLOYEE WHERE AGE >34 ORDER BY NAME;
(b) SELECT NAME, BASIC, DEPARTMENT, BASIC*12 "Annual Salary" FROM EMPLOYEE;
(c) SELECT COUNT(*) FROM EMPLOYEE
WHERE DEPARTMENT ='PERSONNEL' OR DEPARTMENT='COMPUTER';
(d) INSERT INTO EMPLOYEE VALUES(11, 'VIJAY',9300,'FINANCE';'13/7/98',35,'M');
(e) The output is given after excluding the row given in part(d)
(i)

SUM(BASIC)

24600

(ii)

AVG(BASIC)

8750

(iii)

MAX(BASIC)

9500

(iv)

COUNT(DISTINCT DEPARTMENT)

(f)

Name	Head
KARAN	RAHUL
DIVAKAR	SATYAM
DIVYA	NATH
ARUN	RAHUL
SABINA	NATH
JOHN	GANESH
ROBERT	RAHUL
RUBINA	JACOB
VIKAS	SATYAM
MOHAN	JACOB

(a) SELECT NAME FROM DOCTOR WHERE EDPT ='MEDICINE' AN EXPERIENCE>10;
(b) SELECT AVG(BASIC+ALLOWANCE) FROM SALARY WHERE SALARY.ID IN(SELECT ID FROM DOCTOR WHERE DEPT='ENT');
(c) SELECT MIN(ALLOWANCE) FROM SALARY WHERE SALARY.ID IN(SELECT ID FROM DOCTORE WHERE SEX='F');
(d) SELECT MAX(CONSULTATION) FROM SALARY .ID IN(SELECT ID FROM DOCTOR WHERE SEX='M');
(e)

COUNT(*)

(f)

NAME	DEPT	BASIC
John	ENT	12000

(a) SELECT * FROM FAMILY WHERE Occupation='Sservice';
(b) SLECT Name FROM FAMILY WHERE FemaleMembers>3;
(c) SELECT Name, Income FROM FAMILY ORDER BY Income;
(d) SELECT N\ame, MaleMembers, Occupation FROM FAMILY WHERE Occupation='Business';
(e) SELECT COUNT(*) FROM FAMILY WHERE Income<10000;
(f) INSERT INTO FAMILY VALUES(9,'D'SOUZA',2,1,15000,'Service');
(g) The output is given after excluding the row given in part(f)
(i)

MIN(DISTINCT Income)

5000

(ii)

MIN(FemaleMembers)

(iii)

SUM(I|ncome)

31000

(iv)

AVG(Income)

17375

(a) SELECT * FROM INTERIORS WHERE TYPE='Sofa';
(b) SELECT ITEMNAME FROM INTERIORS WHERE PRICE>10000;
(c) SELECT ITEMNAME, TYPE FROM INTERIORS WHERE DATAOFSTOCK<'22/01/02' ORDER BY ITEMNAME DESC;
(d) SELECT ITEMNAME, DATEOFSTOCK FROM INTERIORS WHERE DISCOUNT>15;
(e) SELECT COUNT(*) FROM INTERIORS WHERE TYPE='Double Bed';
(f) INSERT INTO INTERIORS VALUES(14,''TrueIndian','Office Table','28/03/03',15000,20);
(g) The output is given after excluding the row given in -part(f)
(i)

COUNT(DISTINCT TYPE)

(ii)

AVG(DISCOUNT)

12.25

(iii)

SUM(PRICE)

53000

(a) SELECT SenderName FROM SENDER WHERE SenderCity='Mumbai';
(b) SELECT RecID, SenderName, SenderAddress,RecName,RecAddress
FROM RECIPIENT, SENDER WHERE RECIPIENT.SenderID=SENDER.SenderID;
(C) SELECT*FROM RECIPIENT ORDER BY RecName;
(d) SELECT COUNT(*) As "No of Recipients",RecCity FROM RECIPIENT GROUP BY RecCity;
(e)

Distinct SenderCity

New Delhi

Mumbai

(f)

Sendername	Recname
R jain	H Singh
S Jha	P K Swamy

(g)

RecName	RecAddress
S Mahajan	116,A Vihar
S Tripathi	13,B1D, Mayur Vihar

(h)

RecID	Recname
ND08	S Mahajan
ND48	S Tripathi

(a) SELECT STRNAME FROM STREAM ORDER BY STRNAME;
(b) SELECT COUNT(*) FROM STUDENT WHERE POINTS>5;
(c) UPDATE STUDENT SET GRADE='A' WHERE POINTS>8;
(d) DELETE FROM STREAM WHERE STRCDE=6;
(e)

SUM(POINTS)

(f)

STRCDE	MAX(POINTS)
2	6
3	9
5	8
6	8

(g)

AVG(AGE)

(h)

COUNT(STRNAME)

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

COUNT(DISTINCT Scode)

(ii)

RATE*Qty

880

(iii)

Item	Sname
Gel Pen Classic	Premium Stationers

(iv)

Max(LastBuy)

24-FEb-2010

(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 CUSTOMER.CNAME, CARDEN.CARNAME FROM CARDEN, CUSTOMER WHERE CARDEN.Ccode=CUSTOMER.Ccode;
(b)(i)

COUNT
(DISTINCT MAKE)

(ii)

MAX(Charges)	MIN(Charges)
35	12

(iii)

MAKE(*)

(iv)

CARNAME

SX4

C-CLASS

10.

(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
FROM ITEMS
ORDER BY Company DESC;
(g) (i)