These papers have been created with the sole purpose of helping the students with their preparations. We hope that these sample papers will help you prepare better for your exams and help you fine-tune your strategies for the board exams.

In this question paper, questions are covered from the chapter Database and SQL and questions are prepared as per NCERT guidelines. Questions are covered from NCERT solutions and NCERT Exemplar.

The latest sample papers have been designed as per the latest blueprints, syllabus and examination trends. Sample papers should be practiced in examination condition at home or school and also show it to your teachers for checking or compare with the answers provided.

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

7 x 2 = 14

Observe the following table carefully and write the names of the most appropriate columns, which can be considered as (i) candidate keys and (ii) primary key.
Table: Product

CID	CNAME	AMOUNT	COUNTRY	ITEM
101	ALLE	100000	JMEKA	SHOES
111	BEN	20000	FRANCE	HELMET
110	RIKI	25000	AMERICA	BAG
011	BRETT LEE	105000	AUSTRALIA	BAT

Consider the following relations
Teach (Name, Address, Course)
Give an expression in the relational algebra for each of the following:
(i) Print all the information about teachers who are teaching the 'DBMS' course.
(ii) Print the names and addresses of those teachers who teach 'computer'.
(iii) List all the teachers who live in Mumbai.

What do you understand by normalization?

What do you understand by primary key? Give a suitable example of the primary key from a table containing some meaningful data.

What is primary and alternate key in a database? Give a suitable example to explain each.

Explain the concept of cartesian product between two tables, with the help of an appropriate example.

Explain the concept of candidate keys with the help of an appropriate example.

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

3 x 1 = 3

Create a table named PROGRAMMERS with the following structure:

P_Name	20 Characters
DOJ	Date
SAL	NUMBER

i) Display the name of the programmer, which has the highest salary.
ii) Update the salary of all programmer by 2000 whose name start with letter 'R'.

10.

Give the SQL statement to create a table STUDENT with Roll Number, Name, Age and Marks.

11.

What are DDL and DML?

Generic 6

4 x 6 = 24

12.

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;

13.

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;

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

7 x 2 = 14

(i) Candidate Keys CID, ITEM
(ii) Primary key CID

\((i){ \sigma }_{ Course="DBMS" }(Teach)\\ (ii){ \Pi }_{ Name,Address }({ \sigma }_{ Course="Computer" })(Teach)\\ (iii){ \sigma }_{ Address="Mumbai" }(Teach)\)

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.

A primary key is a set of one or more attributes that can uniquely identify tuples within the relation.
e.g. Table: Item

ItemNo	Name	State	City
11	Pastry	10	Ajmer
12	Pizza	15	Delhi
13	Cake	5	Puna
14	Burger	35	Delhi

The attribute ItemNo is a primary key in ITEM table as it contains a unique value for each tuple in a relation.

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.

The cartesian product of two relations R and S is the relation, which is a concatenation of every tuple of relation R with every tuple of relation S.
e.g. consider the following two tables GABS1 and GABS2
Table: GABS1

RollNo	Name
1	ABC
2	GABS

Table: GABS2

Marks	SRollNo	Age
90	1	19
92	3	17

The cartesian product of both tables is as follows:
\(GABS1\times GABS2\)

RollNo	Name	Marks	SRollNo	Age
1	ABC	90	1	19
1	ABC	92	3	17
2	GABS	90	1	19
2	GABS	92	3	17

Candidate Key: A candidate key is a set of one or more fields that identifies each record uniquely in a table. There can be multiple candidate keys in one table. Each candidate key can work as primary key.
e.g.

INo	IName	Qty
101	CD	25
102	Pen	50
103	Pencil	60
104	Eraser	10

In the Item table, INo and IName can be treated as the candidate keys.

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

3 x 1 = 3

()
i)Select P_Name MAX(SAL) FROM PROGRAMMERS;
ii)UPDATE PROGRAMMERS SET SAL=SAL+2000 WHERE P_NAME LIKE "R%';

10.

()
CREATE TABLE STUDENT( ROLL Number NUMBER(5) Primary key, Name CHAR(20), Age NUMBER(2), MARKS NUMBER(4));

11.

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

4 x 6 = 24

12.

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

13.

(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

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)