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
COURSESF_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
i. To display details of those Faculties whose salary is greater than 12000.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
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
i. Find the total value of the movie cassettes available in the library.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
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: DRESSDCODE
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)
6000i. 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)
vi)DESCRIPTION
TYPE
FORMAL SHIRT
TERELENE
INFORMAL SHIRT
COTTON
PENCIL SKIRT
SILK
FORMAL PANT
TERELENE
INFORMAL PANT
COTTON
vii)
viii)