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.

  1. What is the difference between column constraint and table constraint? Name some database integrity constrains.
  2. Give examples of some DDL commands and some DML commands.
  3. What is the difference between Unique and Primary Key constraint?
  4. Compare DISTINCT and ALL keywords when used with SELECT command.
  5. What is the difference between where and having clause?
  6. How does following constraint work? (iii) Default (iv) Check
  7. What is SQL?
  8. 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;
  9. 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.
  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]


  1. 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
  2. DDL Commands
    - CREATE
    - ALTER
    - DROP
    DML Commands
    - INSERT INTO
    - DELETE
    - UPDATE
  3. 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) );
  4. 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.
  5.  

    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’;

  6. 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 );

  7. SQL stands for Structured Query Language. It is a unified, non-procedural language used for creating, accessing, handling and managing data in relational databases.

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

  9. 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’;

  10. 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) SUM(PRICE)2700
    vi) 

    DESCRIPTION

    TYPE

    FORMAL SHIRT

    TERELENE

    INFORMAL SHIRT

    COTTON

    PENCIL SKIRT

    SILK

    FORMAL PANT

    TERELENE

    INFORMAL PANT

    COTTON

    vii) MAX(MCODE)M004
    viii)