DBMS Board Questions

  1. In SQL, the aggregate function which will display the cardinality of the table is __________. [1 MARK] [2024]

    (a) sum()
(b) count(*)
(c) avg()
(d) sum(*)
    (b) count(*)
  2. Which of the following is not a DDL command in SQL ? [1 MARK] [2024]

    (a) DROP
(b) CREATE
(c) UPDATE
(d) ALTER
    (c) UPDATE
  3. The SELECT statement when combined with ______________ clause, returns records without repetition. [1 MARK] [2024]

    (a) DISTINCT
(b) DECREASE
(c) UNIQUE
(d) NULL
    (a) DISTINCT
  4. Define cartesian product with respect to RDBMS. [1 MARK] [2024]
    Cartesian Product operation combines rows/tuples from two tables/relations. It results in all the pairs of rows from both the tables. It is denoted by 'X'.
  5. Give any two features of SQL. [1 MARK] [2024]
    (i) It is used to retrieve and view specific data from a table in a database.
    (ii) It is case insensitive.
  6. Write the SQL commands to perform the following tasks : [2 MARKS] [2024]

    (i) View the list of tables in the database Exam.

    (ii) View the structure of the table Term1.
    (i) SHOW TABLES;
    (ii) DESC/DESCRIBE Term1
  7. Ms. Veda created a table named sports in the MySQL database containing columns Game_id, P_age and G_name. After creating the table she realized that the attribute category has to be added. Help her to write a command to add the category column. Thereafter write the command to insert the following record in the table. [2 MARKS] [2024]

    Game_id : G42
    P_age : Above 18
    G_name : Chess
    Category : Senior
    (i)
ALTER TABLE SPORTS
ADD CATEGORY VARCHAR(10);

OR

ALTER TABLE SPORTS
ADD COLUMN CATEGORY VARCHAR(10);

OR

ALTER TABLE SPORTS
ADD CATEGORY CHAR(10);

OR

ALTER TABLE SPORTS
ADD COLUMN CATEGORY CHAR(10);

(ii)
INSERT INTO SPORTS
VALUES("G42","Above18","Chess","Senior");

OR

INSERT INTO SPORTS(Game_id,P_Age,G_name,Category)
VALUES("G42","Above18","Chess","Senior");
  8. Consider the table ORDERS given below and write the output of the SQL queries that follow: [3 MARKS] [2024]
    ORDNO ITEM QTY RATE ORDATE
    1001 RICE 23 120 2023-09-10
    1002 PULSES 13 120 2023-10-18
    1003 RICE 25 110 2023-11-17
    1004 WHEAT 28 65 2023-12-25
    1005 PULSES 16 110 2024-01-15
    1006 WHEAT 27 55 2024-04-15
    1007 WHEAT 25 60 2024-04-30


    (i) SELECT ITEM, SUM(QTY) FROM ORDERS GROUP BY ITEM;

    (ii)SELECT ITEM, QTY FROM ORDERS WHERE ORDATE BETWEEN ‘2023-11-01’ AND ‘2023-12-31’;

    (iii) SELECT ORDNO, ORDATE FROM ORDERS WHERE ITEM = ‘WHEAT’ AND RATE>=60;
    (i)
    ITEM SUM(QTY)
    RICE 48
    PULSES 29
    WHEAT 80

    (ii)
    ITEM QTY
    RICE 25
    WHEAT 28

    (iii)
    ORDNO ORDATE
    1004 2023-12-25
    1007 2024-04-30
  9. Consider the table Projects given below: [3 MARKS] [2024]

    P_id Pname Language Startdate Enddate
    P001 School Management System Python 2023-01-12 2023-04-03
    P002 Hotel Management System C++ 2023-12-01 2023-02-02
    P003 Blood Bank Python 2023-02-11 2023-03-02
    P004 Payroll Management System Python 2023-03-12 2023-06-02

    Based on the given table, write SQL queries for the following:

    (i) Add the constraint, primary key to column P_id in the existing table Projects.

    (ii) To change the language to python of the project whose ID is P002.

    (iii) To delete the table Projects from MySQL database along with its data.
    (i) ALTER TABLE Projects
    ADD PRIMARY KEY (P_id);

    (ii) UPDATE Projects
    SET LANGUAGE= "Python"
    WHERE P_id = "P002";

    (iii) DROP TABLE Projects;
  10. Consider the tables Admin and Transport given below : [4 MARKS] [2024]

    Table : Admin
    S_id S_name Address S_type
    S001 Sandhya Rohini Day Boarder
    S002 Vedanshi Rohtak Day Scholar
    S003 Vibhu Raj Nagar NULL
    S004 Atharva Rampur Day Boarder

    Table : Transport
    S_id Bus_no Stop_name
    S002 TSS10 Sarai Kale Khan
    S004 TSS12 Sainik Vihar
    S005 TSS10 Kamla Nagar

    Write SQL queries for the following :

    (i) Display the name and their stop name from the tables Admin and Transport.

    (ii) Display the number of students whose S_type is not known.

    (iii) Display all details of the students whose name starts with ‘V’.

    (iv) Display student id and address in alphabetical order of Student name, from the table Admin.
    (i) SELECT S_name, Stop_name FROM Admin, Transport
    WHERE Admin.S_id = Transport.S_id;

    (ii) SELECT COUNT(*) FROM Admin
    WHERE S_type IS NULL;

    (iii) SELECT * FROM Admin
    WHERE S_name LIKE 'V%';

    (iv) SELECT S_id, Address FROM Admin
    ORDERBY S_name;
  11. ___________ is number of tuples in a relation. [ 1 MARK ] [ 2023 ]

    a)  Attribute	
b)  Degree           
c)  Domain	          
d)  Cardinality
    d) Cardinality
  12. __________ clause is used with SELECT statement to display data in a sorted form with respect to a specified column. [ 1 MARK ] [ 2023 ]

    a)  WHERE	
b)  ORDER BY         
c)  HAVING	          
d)  DISTINCT
    b) ORDER BY
  13. fetchall( ) method fetches all rows in a result set and returns a ____________ [ 1 MARK ] [ 2023 ]

    a)  Tuple of lists		
b)  List of tuples         
c)  List of strings	            
d)  Tuple of strings
    b) List of tuples
  14. Write the command to view all databases. [ 1 MARK ] [ 2023 ]
    SHOW DATABASES;
  15. Consider the following tables - LOAN and BORROWER : [ 1 MARK ] [ 2023 ]

    Table : LOAN
    LOAN_NO B_NAME AMOUNT
    L_170 DELHI 3000
    L_230 KANPUR 4000

    Table : BORROWER
    CUST_NAME LOAN_NO
    JOHN L_171
    KRISH L_230
    RAVYA L_170

    How many rows and columns will there be in the natural join of these two tables ?
    Rows : 2
    Columns : 4
  16. Explain the concept of “Alternate Key” in a Relational Database Management System with an appropriate example. [ 2 MARKS ] [ 2023 ]

    Alternate Keys are all the Candidate Keys of a RDBMS table, which have not been used as a Primary Key.

    Example:
    RegNo AadhaarNo Name
    123456 123456789012 Abraham Sen
    123458 123456789123 Umeed Singh

    In this example, any one of the RegNo and AadhaarNo can be used as a Primary Key. If RegNo is used as the Primary Key then AadhaarNo is the Alternate Key.

  17. Differentiate between CHAR and VARCHAR data types in SQL with appropriate example. [ 2 MARKS ][ 2023 ]

    CHAR data type is used to store strings of fixed length
    VARCHAR data type is used to store strings of variable-length.

    Eg, to store ‘India’,
    VARCHAR(20) occupies only 5 bytes
    CHAR(20) occupies 20 bytes.

  18. Name any two DDL and any two DML commands. [ 2 MARKS ][ 2023 ]

    DDL – CREATE, ALTER, DROP
    DML – INSERT, UPDATE, DELETE, SELECT

  19. Write the output of the queries (i) to (iv) based on the table, WORKER given below : [ 2 MARKS ][ 2023 ]

    Table : WORKER
    W_ID F_NAME L_NAME CITY STATE
    102 SAHIL KHAN KANPUR UTTAR PRADESH
    104 SAMEER PARIKH ROOP NAGAR PUNJAB
    105 MARY JONES DELHI DELHI
    106 MAHIR SHARMA SONIPAT HARYANA
    107 ATHARVA BHARDWAJ DELHI DELHI
    108 VEDA SHARMA KANPUR UTTAR PRADESH

    (i) SELECT F_NAME, CITY FROM WORKER ORDER BY STATE DESC;

    (ii) SELECT DISTINCT (CITY) FROM WORKER;

    (iii) SELECT F_NAME, STATE FROM WORKER WHERE L_NAME LIKE ‘_HA%’;

    (iv) SELECT CITY, COUNT(*) FROM WORKER GROUP BY CITY;
    (i)
    F_NAME CITY
    SAHIL KANPUR
    VEDA KANPUR
    SAMEER ROOP NAGAR
    MAHIR SONIPAT
    MARY DELHI
    ATHARVA DELHI

    (ii)
    City
    KANPUR
    ROOP NAGAR
    DELHI
    SONIPAT

    (iii)
    F_NAME STATE
    SAHIL UTTAR PRADESH
    MAHIR HARYANA
    ATHARVA DELHI
    VEDA UTTAR PRADESH

    (iv)
    CITY COUNT (*)
    KANPUR 2
    ROOP NAGAR 1
    DELHI 2
    SONIPAT 1
  20. Write the outputs of the SQL queries (i) to (iv) based on the relations COMPUTER and SALES given below : [ 2 MARKS ] [ 2023 ]

    Table : COMPUTER
    PROD_ID PROD_NAME PRICE COMPANY TYPE
    P001 MOUSE 200 LOGITECH INPUT
    P002 LASER PRINTER 4000 CANON OUTPUT
    P003 KEYBOARD 500 LOGITECH INPUT
    P004 JOYSTICK 1000 IBALL INPUT
    P005 SPEAKER 1200 CREATIVE OUTPUT
    P006 DESKJET PRINTER 4300 CANON OUTPUT

    Table : SALES
    PROD_ID QTY_SOLD QUARTER
    P002 4 1
    P003 2 2
    P001 3 2
    P004 2 1

    (i) SELECT MIN(PRICE), MAX(PRICE) FROM COMPUTER;

    (ii) SELECT COMPANY, COUNT(*) FROM COMPUTER GROUP BY COMPANY HAVING COUNT(COMPANY) > 1;

    (iii) SELECT PROD_NAME, QTY_SOLD FROM COMPUTER C, SALES S WHERE C.PROD_ID = S.PROD_ID AND TYPE = ‘INPUT’;

    (iv) SELECT PROD_NAME, COMPANY, QUARTER FROM COMPUTER C, SALES S WHERE C.PROD_ID = S.PROD_ID;
    (i)
    MIN(PRICE) MAX(PRICE)
    200 4300

    (ii)
    COMPANY COUNT(*)
    LOGITECH 2
    CANON 2

    (iii)
    PROD_NAME QTY_SOLD
    MOUSE 3
    KEYBOARD 2
    JOYSTICK 2

    (iv)
    PROD_NAME COMPANY QUARTER
    MOUSE LOGITECH 2
    LASER PRINTER CANON 1
    KEYBOARD LOGITECH 2
    JOYSTICK IBALL 1
  21. The school has asked their estate manager Mr. Rahul to maintain the data of all the labs in a table LAB. Rahul has created a table and entered data of 5 labs. [ 4 MARKS ] [ 2023 ]

    LABNO LAB_NAME INCHARGE CAPACITY FLOOR
    L001 CHEMISTRY Daisy 20 I
    L002 BIOLOGY Venky 20 II
    L003 MATH Preeti 15 I
    L004 LANGUAGE Daisy 36 III
    L005 COMPUTER Mary Kom 37 II

    Based on the data given above answer the following questions :

    (i) Identify the columns which can be considered as Candidate keys.

    (ii) Write the degree and cardinality of the table.

    (iii) Write the statements to :
    (a) Insert a new row with appropriate data.
    (b) Increase the capacity of all the labs by 10 students which are on ‘I’ Floor.

    OR (Option for part (iii) only)

    (iii) Write the statements to :
    (a) Add a constraint PRIMARY KEY to the column LABNO in the table
    (b) Delete the table LAB.
    (i) Candidate keys: LABNO and LAB_NAME

    (ii) Degree = 5
    Cardinality = 5

    (iii) (a) INSERT INTO LAB VALUES('L006','PHYSICS','RAVI',25,'II');
    (b) UPDATE LAB SET CAPACITY=CAPACITY+10 WHERE FLOOR='I';

    OR

    (iii) (a) ALTER TABLE LAB ADD PRIMARY KEY (LABNO);
    (b) DROP TABLE LAB;