DBMS and SQL (OLD) - Test Papers 02

CBSE TEST PAPER-02

Class-12 Computer Science(SQL)

General Instruction: -

Question No. 1 to 6 carry Two marks,
Question No. 7 to 10 carry Six marks.

What is the difference between column constraint and table constraint? Name some database integrity constrains.
Give examples of some DDL commands and some DML commands.
What is the difference between Unique and Primary Key constraint?
Compare DISTINCT and ALL keywords when used with SELECT command.
What is the difference between where and having clause?
How does following constraint work? (iii) Default (iv) Check
What is SQL?
Consider the following tables FACULTY and COURSES. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (vi)
FACULTY
F_ID
Fname
Lname
Hire_date
Salary
102
Amit
Mishra
12-10-1998
12000
103
Nitin
Vyas
24-12-1994
8000
104
Rakshit
Soni
18-5-2001
14000
105
Rashmi
Malhotra
11-9-2004
11000
106
Sulekha
Srivastava
5-6-2006
10000
COURSES
C_ID
F_ID
Cname
Fees
C21
102
Grid Computing
40000
C22
106
System Design
16000
C23
104
Computer Security
8000
C24
106
Human Biology
15000
C25
102
Computer Network
20000
C26
105
Visual Basic
6000
i. To display details of those Faculties whose salary is greater than 12000.
ii. To display the details of courses whose fees is in the range of 15000 to 50000 (both values included).
iii. To increase the fees of all courses by 500 of “System Design” Course.
iv. To display details of those courses which are taught by ‘Sulekha’ in descending order of courses.
v. Select COUNT(DISTINCT F_ID) from COURSES;
vi. Select MIN(Salary) from FACULTY,COURSES where COURSES.F_ID = FACULTY.F_ID;

Write the SQL commands and write outputs for SQL commands given below on basis of table MOV
Table: MOV

No.	Title	Type	Rating	Stars	Qty	Price
1	Gone with the Wind	Drama	G	Gable	4	39.95
2	Friday the 13th	Horror	R	Jason	2	69.95
3	Top Gun	Drama	PG	Cruise	7	49.95
4	Splash	Comedy	PG13	Hanks	3	29.95
5	Independence Day	Drama	R	Turner	3	19.95
6	Risky Business	Comedy	R	Cruise	2	44.95
7	Cocoon	Scifi	PG	Ameche	2	31.95
8	Crocodile Dundee	Comedy	PG13	Harris	2	69.95
9	101 Dalmatians	Comedy	G		3	59.95
10	Tootsie	Comedy	PG	Hoffman	1	29.95

i. Find the total value of the movie cassettes available in the library.
ii. Display a list of all movies with Price over 20 and sorted by Price.
iii. Display all the movies sorted by Qty in decreasing order.
iv. Display a report listing a movie number, current value and replacement value for each movie in the above table. Calculate the replacement value for all movies as QTY * Price * 1.15
v. Count the number of movies where Rating is not “G”.
vi. Increase the price of Comedy type by 10.

Consider the following tables DRESS and MATERIAL. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).
Table: DRESS

DCODE	DESCRIPTION	PRICE	MCODE	LAUNCHDATE
10001	FORMAL SHIRT	1250	M001	12-JAN-08
10020	FROCK	750	M004	09-SEP-07
10012	ONFORMAL SHIRT	1450	M002	06-JUN-08
10019	EVENING GOWN	850	M003	06-JUN-08
10090	TULIP SKIRT	850	M002	31-MAR-07
10023	PENCIL SKIRT	1250	M003	19-DEC-08
10089	SLACKS	850	M003	20-OCT-08
10007	FORMAL PANT	1450	M001	09-MAR-08
10009	INFORMAL PANT	1400	M002	20-OCT-08
10024	BABY TOP	650	M003	07-APR-07

Table: MATERIAL

MCODE	TYPE
M001	TERELENE
M002	COTTON
M004	POLYESTER
M003	SILK

i. To display DCODE and DESCRIPTION of each dress in ascending order of DCODE.
ii. To display the details of all the dresses which have LAUNCHDATE in between 05-DEC-07 AND 20-JUN-08 (inclusive of both the dates).
iii. To display the average PRICE of all the dresses which are made up of material with MCODE as M003.
iv. To display material we highest and lowest price of dresses from DRESS table. (Display MCODE of each dress along with highest and lowest price)
v. SELECT SUM(PRICE) FROM DRESS WHERE MCODE = ‘M001’;
vi. SELECT DESCRIPTION, TYPE FROM DRESS, MATERIAL WHERE DRESS.MCODE=MATERIAL.MCODE AND DRESS.PRICE >= 1250;
vii. SELECT MAX(MCODE) FROM MATERIAL;
viii. SELECT COUNT(DISTINCT PRICE) FROM DRESS;

CBSE TEST PAPER-02
Class-12 Computer Science(SQL)
[Answers]

The difference between column constraint and table constraint is that column constraint applies only to individual columns, whereas table constraints apply to groups of one or more columns.
Following are the few of database integrity constrains:Unique constraint
- Primary Key constraint
- Default constraint
- Check constraint
DDL Commands
- CREATE
- ALTER
- DROP
DML Commands
- INSERT INTO
- DELETE
- UPDATE
Unique: This constraint ensures that no two rows have the same value in the specified columns. For eg , CREATE TABLE employee (ecode integer NOT NULL UNIQUE, ename char(20),Sex char(2) );
Primary Key: Primary key does not allow NULL value and Duplicate data in the column which is declared as Primary Key.
For eg , CREATE TABLE employee (ecode integer NOT NULL PRIMARY KEY, ename char(20),Sexchar(2) );
DISTINCT keyword is used to restrict the duplicate rows from the results of a SELECT statement. ALL keyword retains the duplicate rows, by default ALL keyword is use by SELECT statement.

WHERE CLAUSE	HAVING CLAUSE
Places conditions on individual rows.	Places conditions on groups.
Cannot include aggregate function.	Can include aggregate function.
For eg. SELECT * FROM student WHERE Rno >=10;	For eg. SELECT AVG(marks) FROM student GROUP BY grade HAVING grade = ‘B1’;

Default: When a user does not enter a value for the column, automatically the defined default value is inserted in field. A column can have only one default value.
For eg , CREATE TABLE employee (ecode integer NOT NULL PRIMARY KEY, ename char(20), Sexchar(2), Grade char(2) DEFAULT = ‘E1’ );
Check: This constraint limits values that can inserted into a column of table.
For eg , CREATE TABLE employee (ecode integer NOT NULL PRIMARY KEY, ename char(20),Sex char(2) , Grade char(2) DEFAULT = ‘E1’, Gross decimal CHECK (gross > 2000 );
SQL stands for Structured Query Language. It is a unified, non-procedural language used for creating, accessing, handling and managing data in relational databases.
i. SELECT * FROM FACULTY WHERE SALARY > 12000
ii. SELECT * FROM COURSES WHERE FEES BETWEEN 15000 AND 50000
iii. UPDATE COURSES SET FEES = FEES + 500 WHERE CNAME = “System Design”
iv. SELECT * FROM FACULTY FAC,COURSES COUR WHERE FAC.F_ID = COUR.F_ID AND FAC.FNAME = 'Sulekha' ORDER BY CNAME DESC
v. COUNT(DISTINCT F_ID)
4
vi. MIN(SALARY)
6000
i. SELECT COUNT(TITLE) FROM MOV;
ii. SELECT * FROM MOV WHERE PRICE>20 ORDER BY PRICE;
iii. SELECT * FROM MOV ORDER BY QTY DESC;
iv. SELECT NO,PRICE AS 'CURRENT VALUE',(QTY*PRICE*1.15) AS 'REPLACEMENT VALUE' FROM MOV;
v. SELECT COUNT(TITLE) FROM MOV WHERE RATING<>'G';
vi. UPDATE MOV SET PRICE=PRICE+10 WHERE TYPE=’Comedy’;
i) SELECT DCODE,DESCRIPTION FROM DRESS ORDER BY DCODE;
ii) SELECT * FROM DRESS WHERE LAUNCHDATE BETWEEN '05-DEC-07' AND '20-JUN-08';
iii) SELECT AVG(PRICE) FROM DRESS WHERE MCODE='M003';
iv) SELECT B.MCODE,TYPE,MAX(PRICE) AS "HIGHEST",MIN(PRICE) AS "LOWEST" FROM DRESS A, MATERIAL B WHERE A.MCODE=B.MCODE GROUP BY TYPE;
v) $\frac{S U M (P R I C E)}{2700}$
vi)
DESCRIPTION
TYPE
FORMAL SHIRT
TERELENE
INFORMAL SHIRT
COTTON
PENCIL SKIRT
SILK
FORMAL PANT
TERELENE
INFORMAL PANT
COTTON
vii) $\frac{M A X (M C O D E)}{M 004}$
viii) $\frac{C O U N T (D I S T I N C T P R I C E)}{6}$

F_ID	Fname	Lname	Hire_date	Salary
102	Amit	Mishra	12-10-1998	12000
103	Nitin	Vyas	24-12-1994	8000
104	Rakshit	Soni	18-5-2001	14000
105	Rashmi	Malhotra	11-9-2004	11000
106	Sulekha	Srivastava	5-6-2006	10000

C_ID	F_ID	Cname	Fees
C21	102	Grid Computing	40000
C22	106	System Design	16000
C23	104	Computer Security	8000
C24	106	Human Biology	15000
C25	102	Computer Network	20000
C26	105	Visual Basic	6000