From the chapter Database and SQL, some of the important questions are covered in this question paper. The questions are covers from the book back and the previous year questions.

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 2

5 x 2 = 10

Define the following terms:
(i) First Normal Form
(ii) Second Normal Form
(iii) Third Normal Form

What is the need of normalization?

Give a suitable example of a table with sample data and illustrate primary and alternate keys in it.

What do you mean by the union and cartesian product operation in relational algebra?

What is the difference between degree and cardinality of a table? What is the degree and cardinality of the following table?

Eno	Name	salary
101	John Fedrick	45000
102	Raya Mazumdar	50600

Differentiate between the terms degree and cardinality in context of RDBMS.

Generic 4

1 x 4 = 4

Answer the questions (a) and (b) on the basis of the following tables SHOPPE and ACCESSORIES.
TABLE SHOPPE

Id	SName	Area
S001	ABC Computeronics	CP
S002	All Infotech Media	GK II
S003	Tech Shoppe	CP
S004	Geeks Tecno Soft	Nehru Place
S005	Hitech Tech Store	Nehru Place

TABLE ACCESSORIES

No	Name	Price	Id
A01	Mother Board	12000	S01
A02	Hard Disk	5000	S01
A03	Keyboard	500	S02
A04	Mouse	300	S01
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

(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.COUNT (*) FROM SHOPPE GROUP BY AREA;
(iii)SELECT COUNT (DISTINCT AREA) FROM SHOPPE;
(iv)SELECT NAME,PRICE*0.05 DISCOUNT FROM ACCESSORIES WHERESNO IN ('S02','S03');

Fill up / 1 Marks

2 x 1 = 2

Write a query on the SALESPEOPLE table, whose output will exclude all salespeople with a rating >=100, unless they are located in Delhi.

What are DDL and DML?

Generic 6

7 x 6 = 42

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;

10.

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

SNO	NAME	STREAM	FEES	AGE	SEX
1	ARUN KUMAR	COMPUTER	750.00	17	M
2	DIVYA JENEJA	COMPUTER	750.00	18	F
3	KESHAR MEHRA	BIOLOGY	500.00	16	M
4	HARISH SINGH	ENG.DR	350.00	18	M
5	PRACHI	ECONOMICS	300.00	19	F
6	NISHA ARORA	COMPUTER	750.00	15	F
7	DEEPAK KUMAR	ECONOMICS	300.00	16	M
8	SARIKA VASWANI	BIOLOGY	500.00	15	F

(a) List the name of all students, who have taken stream as COMPUTER.
(b) To count the number of female students.
(c) To display the number of students stream wise.
(d) To insert a new row in the STUDENT table
9,'KARISHMA','ECONOMICS',300.18,'F'
(e) To display a report, listing NAME, STREAM,SEX and stipend, where stipend is 20% of fees.
(f) To display all the records in sorted order of name.
(g) Give the output of the following SQL statements based on STUDENT table:
(i) SELECT AVG(FEES) FROM STUDENT WHERE STREAM='COMPUTER';
(ii) SELECT MAX(AGE) FROM STUDENT;
(iii) SELECT COUNT(DISTINCT STREAM) FROM STUDENT;
(iv) SELECT SUM(FEES) FROM STUDENT GROUP BY STREAM;

11.

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';

12.

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;

13.

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

CID	CNAME	GENDER	SID	AREA
1001	R SHARMA	FEMALE	101	NORTH
1002	M R TIWARY	MALE	102	SOUTH
1003	M K KHAN	MALE	103	EAST
1004	A K SINGH	MALE	102	EAST
1005	S SEN	FEMALE	101	WEST
1006	R DUBEY	MALE	104	NORTH
1007	M AGARWAL	FEMALE	104	NORTH
1008	S DAS	FEMALE	103	SOUTH
1009	R K PATIL	MALE	102	NORTH
1010	N KRISHNA MURTY	MALE	102	SOUTH

TABLE: ONLINESHOP

SID	SHOP
101	MY BUY
102	ECO BUY
103	JUST SHOPPING
104	SHOPPING EASY

(a) To display CNAME, AREA of all female customers from CUSTOMER table.
(b) To display the details of all the CUSTOMERS in ascending order of CNAME within SID.
(c) To display the total number of customers for each AREA from CUSTOMER table.
(d) to display CNAME and corres[ponding SHOP from CUSTOMER table and ONLINESHOP table.
(e) SELECT COUNT(*), GENDER FROM CUSTOMER GROUP BY GENDER;
(f) SELECT COUNT(*) FROM ONLINESHOP;
(g) SELECT CNAME FROM CUSTOMER WHERE CNAME LIKE 'L%';
(h) SELECT DISTINCT AREA FROM CUSTOMER;

14.

Write SQL queries for (a) to (f) and write the outputs for (g) parts (i) to (iv) on the basis of tables APPLICANTS and COURSES.
TABLE: APPLICANTS

No	NAME	FEE	GENDER	C_ID	JOINYEAR
1012	Amandeep	30000	M	A01	2012
1102	Avisha	25000	F	A02	2009
1103	Ekant	30000	M	A02	2011
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	Arluna	30000	F	A03	2012
2109	Shakti	35000	M	A04	2011
1101	Kirat	25000	M	A01	2012

TABLE: COURSES

C_ID	COURSES
A01	FASHION DESIGN
A02	NETWORKING
A03	HOTEL MANAGEMENT
A04	EVENT MANAGEMENT
A05	OFFICE MANAGEMENT

(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 the names of all applicants in ascending order of their joinyear.
(d) To display the year and the total number of applicants joined in each year
from the table APPLICANTS>
(e) To display the C_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 the following SQL statements:
(i) SELECT NAME,JOINYEAR FROM APPLICANTS WHERE GENDER='F' AND C_ID='A02';
(ii) SELECT MIN(JOINYEAR) 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 APPLICANTS GROUP BY C_ID HAVING COUNT(*)=2;

15.

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 2

5 x 2 = 10

First Normal Form(1NF)
Any table is in 1NF if the values in the domain of each attribute of the relation are "atomic". We can say, only one value is associated with each attribute and the value is not a set of values or a list of values. It does not have any repeating groups. It does not have any repeating groups. It disallows multi-valued attributes, composite attributes, and their combinations.
Second Normal Form(2NF)
To be in 2NF, a table must be in First Normal Form and no attributes of the table should be functionally dependent on only one part of a concatenated primary key. Thus, 2NF can be violated only when a key is a composite key, or in other words, one that consists of more than one attributes.
Third Normal Form(3NF)
A table is said to be in the 3NF if all the non-key fields of the table are independent of all other non-key fields of the table.

The normalisation is the process of transformation of the relationship among data elements in a record.Normalisation replaces a collection of data in a record structure by another record design which is simpler, more predictable and therefore more manageable.

Primary key It is a column or set of columns which help to identify a row uniquely. e.g. in table student, RollNo of all students are different so, RollNo is a primary key, helps to identify the information of all students uniquely.
Alternate key It is a column or set of columns that can act as a primary key but not selected as a primary key.
Table: Student

AdmNo	RollNo	Name	Marks
1000	1	Amit	85
1200	2	Akash	65
999	3	Babita	95
1011	4	Charu	60

e.g. In table student, RollNo and AdmNo of all students are different. Both of them can be selected as a primary key. Suppose, we have selected roll number as a primary key then AdmNo is called as an alternate key.

Union It operates on two relations and is indicated by represents\(\cup .e.g.R=R1\cup R2\) union operation between two relations R1 and R2. The degree of R is equal to the degree of R1. The cardinality of R is equal or less than the sum of the cardinality of R1 and cardinality of R2.
Cartesian product It operates on two relations and is denoted by X. e.g. the cartesian product of two relations R1 and R2 is represented by.\(R=R1\times R2\) The degree of R is equal to the sum of degrees of R1 and R2. The cardinality of R is the product of cardinality of R1 and cardinality of R2.

Degree: The number of attribute (or) columns in a table is called the degree of the table.
The degree of the given table is:3
Cardinality: The number of rows (or) records in a table is called the cardinality of the table.
The cardinality of the given table is:2.

Generic 4

1 x 4 = 4

(a)(i) SELECT Name, Price
FROM ACCESSORIES
ORDER BY Price;
   (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) SELECT Name,Price, SName
   FROM ACCESSORIES A, SHOPPE S
WHERE A.Id=S.Id;
but this query enable to show the result because A.Id and S.Id are not identical.
(b)(i)

NAME

MotherBoard

Hard Disk

LCD

(ii)

AREA	COUNT(*)
GK II	1
Nehru Place	2
CP	2

(iii)

Count(Distinct area)

(iv) The given query will result in an error as there is no column named SNo in ACCESSORIES table.

Fill up / 1 Marks

2 x 1 = 2

()
SELECT * FROM SALESPEOPLE
WHERE rating<100 or city='Delhi';
SELECT*FROM SALESPEOPLE WHERE NOT rating>=100 OR city ='Delhi';
SELECT*FROM SALESPEOPLE WHERE NOT(rating>=100 AND city<>'Delhi');

()
DDL (Data Definition Language) It is a part of SQL, which provides commands for creation,altering and deleting the tables.Different DDL commands are CREATE, ALTER and DROP.
DML (Data Manipulation language) It is a part of SQL, which provides commands for inserting, deleting and updating the information in a database.Different DML commands are SELECT,UPDATE,DELETE,INSERT.

Generic 6

7 x 6 = 42

(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

10.

(a) SELECT NAME FROM STUDEnT WHERE STREAM ='COMPUTER';
(b) SELECT COUNT(*) FROM STUDENT WHERE SEX='F';
(c) SELECT STREAM, COUNT(*) FROM STUDENT GROUP BY STREAM;
(d) INSERT INTO STUDENT VALUES(9,'KARISHMA','ECONOMICS',300.18,'F');
(e) SELECT NAME, STREAM, SEX, FEES*20/100 'STIPEND' FROM STUDENT;
(f) SELECT * FROM STUENT ORDER BY NAME;
(g) The output is given after excluding the row given in part(f).
(i)

AVG(FEES)

750.00

(ii)

MAX(AGE)

(iii)

COUNT(DISTINCT STREAM)

(iv)

SUM(FEES)

225.00

1000.00

350.00

600.00

11.

(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

12.

(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

13.

(a)SELECT CNAME,AREA FROM CUSTOMER
WHERE GENDER ='FEMALE';
(b) SELECT * FROM CUSTOMER ORDER BY SID, CNAME;
(c) SELECT AREA COUNT(*) FROM CUSTOMER GROUP BY AREA;
(d) SELECT CUSTOMER .CNAME, ONLINESHOP.SHOP
FROM CUSTOMER, ONLINESHOP WHERE CUSTOMER.SID=ONLINESHOP.SID;
(e)

COUNT(*)	GENDER
4	FEMALE
6	MALE

(f)

COUNT(*)

(g) No rows selected.
(h)

DISTINCT AREA

NORTH

SOUTH

EAST

WEST

14.

a) SELECT NAME, FEE, GENDER, JOINYEAR FROM APPLICANTS WHERE JOINYEAR<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 GROUP BY C_ID;
(f) SELECT NAME, COURSE FROM APPLICANTS, COURSES WHERE APPLICANTS.C_ID=COURSES.C_ID;
(g) (i)

NAME	JOINYEAR
Avisha	2009

(ii)

MIN(JOIN YEAR)

2009

(iii)

AVG(FEE)

31666.666

(iv)

SUM(FEE)	C_ID
55000	A01

15.

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