DATABASE CONCEPTS and SQL-ANSWER KEY

WORKSHEET
CLASS-XII
1.(i) String and dates are not real numbers that we calculate so Sum or Avg
functions are not valid for them
ii)It is because count(*) will include NULL values but count(C1) will not include
NULL values

2 i) distinct
ii)Degree:5 and Cardinality:2

3. The primary Key is a key that helps in uniquely identifying the tuple of the
database whereas the Foreign Key is a key that is used to identify the
relationship between the tables through the primary key of one table that is the
primary key one table acts as a foreign key to another table.

4. i)select * from tablename;

(ii)mXn rows

5. c)8,15

6. b)distinct

7.b)ALTER TABLE student DROP gender

8.ALTER TABLE SWEETS ADD PRIMARY KEY(SID);

INSERT INTO SWEETS VALUES(999,”KAJU KATLI”,40,2);

9.a) SHOW TABLES;

(b) SELECT NAME FROM TEACHER WHERE NAME LIKE "_ _O%"

10. (i) ALTER TABLE TEACHERS ADD SALARY INT;

(ii) Categories the DDL and DML Commands from the following:
DROP-DDL, ALTER-DDL, SELECT-DML, DELETE-DML, INSERT-DML, UPDATE-DML

11. ORDER BY DESC

12.(i) IS NULL OR IS NOT NULL

(ii)EXISTS

13. i)SHOW DATABASES; ii)USE DATABASENAME;

14.FALSE

15.b. Key

16. d. Select emp_id where emp_id = 109

17. (c) alter

18.char stores only fixed-length character string data types whereas varchar
stores variable-length string where an upper limit of length is specified.
19.DDL:DATA DEFINITION LANGUAGE (CREATE,ALTER),DML:DATA
MANIPULATION LANGUAGE(INSERT,SELECT)

20. (C) Foreign Key

21. (A) DISTINCT

22.c) There can be only one Primary Key in a Table

23.c)ALTER TABLE EMPLOYEE DROP COLUMN DESIGNATION’

24.b)count(),group by

25. c)order by

26.SELECT NAME FROM TEACHER WHERE NAME LIKE ’%i’;

27. If a table has more than one candidate key, one of them will become the
primary key and rest of all are called alternate keys.

28. In a database table, we can add rules to a column known as constraints.

These rules control the data that can be stored in a column. For example, if a
column has NOT NULL constraint, it means the column cannot store NULL values.

29.(C) alter

30. (B) DROP DATABASE

31.(C) Foreign Key

32.(A) DISTINCT

33. (D) count(*)

34. ORDER BY clause is used to sort the result set either in ascending or
descending order and GROUP BY the statement is used to group the rows that
have the same value and it is used with aggregate functions like SUM(), AVG(),
MAX() etc.,
select rno,name from student order by rno;
select rno,name,class from student group by class ;

35. INSERT, UPDATE-DML ALTER, DROP-DDL

36.c) DISTINCT

37. b) DISTINCT

38.d) SHOW TABLES

39. d) DELETE FROM student;

40. A MySQL table, sales have 10 rows.

The following queries were executed on the sales table.
SELECT COUNT(*) FROM sales;
COUNT(*)
10

SELECT COUNT(discount) FROM sales;

COUNT(discount)
8

It is because count(*) will include null values but count(discount) will not count
null values

SECTION B

1.Cartesian Product or Cross join

2.a)degree:7 and cardinality:10

b)12

3.
(i)
DISTINCT SPORTS
SOCCER
TENNIS
CRICKET
ATHLETICS
SNOOKER

(ii)
SPORTS MAX(salary)
SOCCER 50000
TENNIS 20000
CRICKET 15000
ATHLETICS 12000

(iii)
pname sports salary
VIRAT CRICKET 15000
NEERAJ ATHLETICS 12000
SANIA TENNIS 5000

4.a) update fashion set price =price+price*0.05 where product=”KAJAL”;

b) select product,price from fashion where qty>20 and product like ‘%s%’;
c) delete from fashion where price >1200;
5.i)select itemname from lab natural join trader where itemname like “C%” and
city=”Chennai”;
(ii) select itemname from lab where qty>3 order by dataofpurchase;
iii)update lab set itemname=”web”where itemname like ’%a’;
iv)select itemname,qty,tradername from lab natural join trader where
dateofpurchase>’01/01/1998’;

6.(a)
MAX(EXP)
16

(b)
TEACHER
UMESH
YASHRAJ
C)
SUM(PERIODS)
51

(d)
SUBJECT
PHYSICS

7(a)CUSTOMER:
DEGREE:3 Cardinality:5
Transactions:
Degree:5
Cardinality:5

(b) Customer:
Primary Key:CNO
Transactions:
Primary Key:TNO

(c) CNO,CNAME

d)ALTER COMMAND

8. CREATE DATABASE KNOWLEDGE;

USE KNOWLEDGE;
CREATE TABLE STUDENT(ADMNO INT PRIMARY KEY,NAME CHAR(20) NOT
NULL,CLASS INT,SEC CHAR(2),RNO INT,ADDRESS VARCHAR(30));

9.a)
NAME
JATIN MANHAS
JAGDEEP

b)
ADM AGE
1 17
6 18

c)
MAX(FEES) NAME
800 JAGDEEP

d)
NAME AGE
MANISH 17

10.A)DEGREE:3 CARDINALITY:4
b)EMPID

11.CREATE DATABASE SCHOOL;

USE SCHOOL;
CREATE TABLE LABS(LABID INT PRIMARY KEY,LABNAME VARCHAR(20),
LABATTENTANT VARCHAR(20));

b.ADMNO(FOREIGN KEY OF THE TABLE BOOKS)

12.a)SELECT NAME,PRICE FROM ACCESSORIES ORDER BY PRICE;

b)SELECT ID,SNAME FROM SHOPS;
c)SELECT NAME FROM ACCESSORIES WHERE PRICE <1000;
d)SELECT MAX(PRICE),MIN(PRICE) FROM ACCESSORIES GROUP BY NAME;

13.i)
COUNT(DISTINCT
NUMBER)
2
4

ii)
MAX(SCHEDULEDATE) MIN(SCHEDULEDATE)
19-MAR-2004 12-DEC-2003

iii)
SUM(PRIZEMONEY)
59000

iv)
GCODE GAMENAME NUMBER PRIZEMONEY SCHEDULEMONEY
108 LAWN TENNIS 4 25000 19-MAR-2004
14.CREATE TABLE INVENTORY MATERIALID INT PRIMARY KEY,MATERIAL
VARCHAR(50) NOT NULL CATEGORY CHAR DEFAULT(‘E’),DATEPURCHASE DATE;

15.i)Before entering the value of the city in table 2,that value should be insert
into table 1.As the city values is referenced as foreign key in table 2

ii)
i)

PINCODE
100001
ii)
PINCODE

16.a)
TNAME CITY SALARY
MEENAKSHI DELHI 78000
DEEPTI CHANDIGARH 82000
MANIPRABHA CHENNAI 69000
SUNAINA MUMBAI 90000
RICHA MUMBAI 95000
ANANMIKA DELHI 80000

b)
TNAME CITY
DEEPTI CHANDIGARH
MANIPRABHA CHENNAI

c)
TNAME HIREDATE CNAME STARTDATE
ANAMIKA 1994-12-24 DCA 2018-07-15

MEENAKSHI 2002-12-25 DDTP 2018-09-15

d)
COUNT(TNAME)
2
2
1
1

17.a)
SUM(PRICE)
273

b)
COUNT(DISTINCT ITEM_BRAND)
 6
c)
ITEM_NAME
EGGS
d)
ITEM_ID ITEM_NAME ITEM_BRAND GST PRICE UNIT
1011 TEA TATA CAT8 370 KG

18.i)SELECT * FROM ITEM WHERE PRICE BETWEEN 40 AND 95;

ii)SELECT CUSTOMERNAME,CITY,ITEMNAME,PRICE FROM CUSTOMER NATURAL
JOIN ITEM ;
iii)UPDATE ITEM SET PRICE=PROCE+50;
iv)SELECT CUSTOMERNAME FROM CUSTOMER WHERE CITY LIKE ‘N%’;

19.i)
CNAME
AMRITA
KRISHNA
ii)
NAME AGE

AMRITA 35

AMRIT 28

MANJU 33

VIRAT 35

iii)
COUNT(DISTINCT SPORTS)
4

iv)
MIN(AGE)
23
v)
SUM(PAY)
3650

vi)
CNAME AGE
SHYAM 37
KRISHNA 36
VIRAT 35
AMRIT 28
20.i)SELECT BNAME ,AUNAME ,PRICE FROM BOOKS WHERE TYPE=”COMPUTER”;

ii)UPDATE BOOKS SET PRICE=PRICE+50 WHERE TYPE=”COMPUTER”;

iii)SELECT * FROM BOOKS ORDER BY PRICE ;

iv)SELECT BID,BNAME,QTY_ISSUED FROM BOOKS NATURAL JOIN ISSUED

WHERE QTY_ISSUED IS NOT NULL;

a)
ENAME JOB
SMITH CLERK
ANYA SALESMAN
SETH SALESMAN
MAHADEV MANAGER
MOMIN SALESMAN
BINA MANAGER
b)
ENAME
ANYA
BINA
c)
MIN(SALARY)
800

d)
ENAME SALARY+500
SMITH 1300
ANYA 2100
SETH 1750
MAHADEV 348
MOMIN 1750
BINA 3350

22i).UPDATE SUPERVISOR SET NAME=”KASHYAP” WHERE SID=”A11”;

ii)BETWEEN
iii)DEGREE:3 CARDINALITY:4
iv)DEGREE:4 CARDINALITY:4

23.
a)i)
DEPARTMENT AVG(SALARY)
COMPUTER SC 16000
HISTORY 27000
MATHEMATICS 25000
b)
MAX(DATE_OF_JOIN) MIN(DATE_OF_JOIN)
2021-09-05 2017-03-24

c)
 NAME SALARY T.DEPARTMENT PLACE
CLAIRE 30000 MATHEMATICS HYDERABAD
SAMIRA 40000 HISTORY CHENNAI
RAKESH 25000 MATHEMATICS HYDERABAD
SHYAM 30000 HISTORY CHENNAI
JACK 21000 COMPUTER SC DELHI

d)
NAME PLACE
CLAIRE HYDERABAD
SAMIRA CHENNAI
KATE HYDERABAD

b)DEGREE:7 CARDINALITY:8
24.a)
PASSENGER SOURCE MODEL PRICE
ORAM BAN 320 7500
ALI MUM 767 6000

b)FLIGHTID

25.Write the output of the queries (a) to (d) based on the table COURSE given
below: RELATION:COURSE
NO NAME SUBJECT FEES AGE GENDER
1 KARTHIK MATHS 1000 17 M
2 RAKESH COMPUTER 1200 16 M
3 GEETHA NULL 900 18 F
4 JANSI MATHS 1200 17 F
5 PANKAJ COMPUTER NULL 18 M
6 SRINIVAS ENGLISH 1100 17 M
7 DEEPIKA NULL 750 16 F
8 SWATI BIOLOGY 1250 18 F
a)
NAME FEES
RAKESH 1200
JANSI NULL
SRINIVAS 1100
SWATI 1250

b)
DISTINCT SUBJECT
MATHS
COMPUTER
ENGLISH
BIOLOGY

c)SELECT AGE,COUNT(*),MAX(FEES) FROM COURSE GROUP BY AGE HAVING

COUNT(AGE)>2;
AGE COUNT(*) MAX(FEES)
17 3 1200
16 2 1200
18 3 1250

d)SELECT AVG(FEES) FROM COURSE WHERE SUBJECT=’COMPUTER’ OR

SUBJECT=’BIOLOGY’;
AVG(FEES)
2

26. (i)
BRAND_NAME FLAVOUR
LAYS TOMATO
UNCLE CHIPS SPICY
HALDIRAM TOMATO

(ii)
BRAND_NAME FLAVOUR PRICE QUANTITY
HALDIRAM TOMATO 25 30

(iii) Select BRAND_NAME from CHIPS where price > 15 and QUANTITY < 15;
BRAND_NAME
LAYS

(iv)
count( distinct (BRAND_NAME))
3

(v)
PRICE PRICE*1.5
10 15.0

(vi) Select distinct (BRAND_NAME) from CHIPS order by BRAND_NAME desc;

DISTINCT BRANDN_NAME
UNCLE CHIPS
LAYS
HALDIRAM
27.i)SELECT BNAME ,AUNAME ,PRICE FROM BOOKS WHERE TYPE=”COMPUTER”;
ii)UPDATE BOOKS SET PRICE=PRICE+50 WHERE TYPE=”COMPUTER”;
iii)SELECT * FROM BOOKS ORDER BY PRICE ;
iv)SELECT BID,BNAME,QTY_ISSUED FROM BOOKS NATURAL JOIN ISSUED
WHERE QTY_ISSUED IS NOT NULL
v)SHOW TABLES;
28.(i) STOCKID
(ii) IDEGREE:8 CARDINALITY:3
(iii) (a) INSERT INTO STOCK VALUES(201, ’18-OCT-2022’, ’NECKPHONE’, ’BOAT’,
500);
(b) UPDATE STOCK SET PRICE=PRICE-0.05 WHERE DOPURCHASE BETWEEN
“2020-01-01” AND “2020-12-31”;
(iv) a)DELETE FROM STOCK WHERE DOPURCHASE <’’2015-01-01”;
(b) ALTER TABLE STOCK ADD STATUS CHAR;
29.
EMPCODE ENAME DEPTID SALARY DNAME
1001 TOM 10 10000 PHYSICS
1003 SID 10 9000 PHYSICS

30.I)5 II)6
31.CREATE TABLE SUBJECT (ROLL INT FOREIGN KEY REFERENCES
STUDENT(ROLL),SUBCODE INT NOT NULL,SUBJECT VARCHAR(20) NOT NULL);

32.
AVG(SALARY)
15000

33. a)SELECT ID, FEE FROM Coaching WHERE FEE IS NOT NULL;

b)ALTER TABLE COACHING DROP NAME;