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 2

13 x 2 = 26

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 is the need of normalization?

Consider the following supplier table:
TABLE: Supplier

Suppcode	Lastname	Firstname	City
1	Jain	Anuj	Noida
2	Goel	Pooja	Kanpur
3	Sharma	Raman	Delhi
4	Gupta	Satish	Noida

Write a query to find all suppliers, who reside in Noida and also give the resultant table.

Consider the following two tables:
Relation: STUDENTS

Name	RollNo	Class
Saurabh	349	11A
Yogendra	443	11B
Mohan	306	11A
Monika	259	10C

Relation: VIDHYARTHI

Name	RollNo	Class
Shagun	150	10A
Saurabh	349	11A

Perform the STUDENTS U VIDHYARTHI operation on above relation and show the resultant relation.

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

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

What do you understand by selection and projection operation in relational algebra?

10.

Illustrate cartesian product operation between the two tables/relations using a suitable example.

11.

Differentiate between candidate key and primary key in context of RDBMS.

12.

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

13.

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

14.

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

15.

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

16.

Differentiate between SQL commands DROP TABLE and DROP VIEW.

17.

What are DDL and DML?

Generic 6

3 x 6 = 18

18.

Consider the following tables EMPLOYEE and SALGRADE and answer (a) and (b) parts of this question:
TABLE: EMPLOYEE

ECODE	NAME	DESIGN	SGRADE	DOJ	DOB
101	Abdul Ahmed	EXECUTIVE	S03	23-MAR-2003	13-JAN-1980
102	RaviChander	HEAD-IT	S02	13-FEB-2010	22-JUL-1987
103	JohnKen	Receptionist	S03	24-JUN-2009	24-FEB-1983
105	Nazar Ameen	GM	S02	11-AUG-2006	03-MAR-1984
108	Priyam Sen	CEO	S01	29-DEC-2004	19-JAN-1982

TABLE: SALGRADE

SGRADE	SALARY	HRA
S01	56000	18000
S02	32000	12000
S03	24000	8000

(a) Write SQL commands for the following statements:
(i) To display the details of all the EMPLOYEE in descending order of DOJ.
(ii) To display NAME and DESIGN of those EMPLOYEEs, whose SGRADE is either S02 or S03.
(iii) To display the content of all the EMPLOYEEs whose DOJ is in between '09-FEB-2006' and '08-AUG-2009'.
(iv) To add a new row in the EMPLOYEE table with the following:
109,'Harish Roy','HEADIT',"S02','09-SEP-2007','21-APR-1983'.
(b) Give the output of the following SQL queries:
(i) SELECT COUNT(SGRADE), SGRADE FROM EMPLOYEE GROUP BY SGRADE;
(ii) SELECT MIN(DOB), MAX(DOJ) FROM EMPLOYEE;
(iii) SELECT NAME, SALARY FROM EMPLOYEE E, SALGRADE S WHERE E.SGRADE=S.SGRADE AND E.ECODE<103;
(iv) SELECT SGRADE, SALARY+HRA FROM SALGRADE
WHERE SGRADE='S02';

19.

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;

20.

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

13 x 2 = 26

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

To find all suppliers who reside in Noida
\(\sigma \quad city='Noida'(Supplier)\\ \swarrow \quad \quad \quad \downarrow \quad \quad \quad \quad \quad \quad \quad \downarrow \)
Selection Condition Table name
operator
The resultant table from query is:

Super code	Last name	First name	City
1	jain	Anuj	Noida
4	Gupta	Satish	Noida

STUDENTS U VIDHYARTHI

Name	RollNo	Class
Saurabh	349	11A
Yogendra	443	11B
Mohan	306	11A
Monika	259	10C
Shagun	150	10A

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.

A projection is a unary operation written a,\({ \Pi }_{ a1...an }(R)\quad \) where is\(\Pi \) a projection operator a1...an is a set of attribute names and R is a table. The result of such all the attributes in R is restricted to the set. A selection is a unary operator, which select the subset of tuples or rows of a table which satisfied a particular condition. It is written as,\({ \sigma }_{ \alpha }(R)\) where is \({ \sigma }\) a selection operator,\(\alpha \) is a condition and R is a table.

10.

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

11.

The candidate key is a set of attributes that uniquely identifies records in a table. Each table may have one or more candidate keys.
Primary Key: The candidate key that is selected to identify tuples uniquely in a relation is called a primary key. A relation can have only one primary key.

AdmNo	RollNo	Name	Class	Mark
2715	1	Ram	12	90
2716	2	Ajay	11	95
2811	3	Jayesh	12	98
2914	4	Tarun	11	94

In student table, AdmNo and RollNo both can identify records uniquely. So, both are a candidate key. Here, we have created AdmNo as the primary key.

12.

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.

13.

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

14.

(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

15.

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

16.

()
DROP TABLE command deletes the definition of the table as well as the data of table.If the table is dropped you cannot access it.While DROP VIEW command only deletes the definition of view.Dropping a view does not affect the base table.i.e. no loss of data is there in DROP VIEW.
Syntax of DROP View is - DROP VIEW viewname;

17.

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

3 x 6 = 18

18.

(a) (i) SELECT * FROM EMPLOYEE
ORDER BY DOJ DESC;
   (ii) SELECT NAME, DESIGN FROM EMPLOYEE
   WHERE (SGRADE='S02' OR SGRADE='S03');
   (iii) SELECT * FROM EMPLOYEE WHERE DOJ BETWEEN '09-FEB-2006' AND '08-AUG-2009';
   (iv) INSERT INTO EMPLOYEE VALUES(109,'Harish Roy','HEAD-IT','S02';'09- SEP-2007','21-APR-1983');
(b)The output is given after excluding the row given in part(a(iv)).
(i)

COUNT(SGRADE)	SGRADE
2	S03
2	S02
1	S01

(ii)

MIN(DOB)	MAX(DOJ)
13-JAN-1980	12-FEB-2010

(iii)

NAME	SALARY
Abdul Ahmed	24000
Ravi Chander	32000

(iv)

SGRADE	SALARY+HRA
S02	44000

19.

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

20.

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