Question Bank — Structured Query Language (SQL)

NCERT Class 12 Computer Science · Chapter 9 · CBSE Board Pattern

Class 12 Computer Science Chapter 9 Question Bank CBSE Board Pattern

QUESTION BANK

Section A — MCQs (10 questions, 1 mark each)

  1. Which SQL constraint ensures that all values in a column are distinct?
    (a) NOT NULL (b) UNIQUE (c) DEFAULT (d) PRIMARY KEY

  2. The data type used for storing dates in 'YYYY-MM-DD' format in MySQL is:
    (a) CHAR (b) VARCHAR (c) DATE (d) INT

  3. Assertion (A): CHAR(n) is a fixed-length data type while VARCHAR(n) is variable-length.
    Reason (R): In CHAR(n), unused spaces are padded with spaces on the right.
    Choose the correct option:
    (a) Both A and R are true and R is the correct explanation of A.
    (b) Both A and R are true but R is not the correct explanation of A.
    (c) A is true but R is false.
    (d) A is false but R is true.

  4. Which clause is used to eliminate duplicate rows from the result of a SELECT query?
    (a) WHERE (b) DISTINCT (c) ORDER BY (d) GROUP BY

  5. The statement used to permanently remove a table from the database is:
    (a) DELETE (b) DROP TABLE (c) ALTER TABLE (d) TRUNCATE

  6. Assertion (A): A foreign key in one table refers to the primary key of another table.
    Reason (R): Foreign key ensures referential integrity between relations.
    Choose the correct option:
    (a) Both A and R are true and R is the correct explanation of A.
    (b) Both A and R are true but R is not the correct explanation of A.
    (c) A is true but R is false.
    (d) A is false but R is true.

  7. Which function returns the number of characters in a given string?
    (a) LENGTH() (b) MID() (c) ROUND() (d) NOW()

  8. The clause used to group rows having the same values in a specified column is:
    (a) WHERE (b) ORDER BY (c) GROUP BY (d) HAVING

  9. Which operator is used to check for NULL values in a column?
    (a) = NULL (b) IS NULL (c) LIKE (d) IN

  10. The result of SELECT MOD(21,2); is:
    (a) 0 (b) 1 (c) 10 (d) 21

Section B — Very Short Answer (6 questions, 2 marks each)

  1. Differentiate between CHAR and VARCHAR data types with respect to storage.
  2. What is the purpose of the PRIMARY KEY constraint? Give one example from the chapter.
  3. Write the output of: SELECT UCASE("informatics practices");
  4. State any two differences between DDL and DML.
  5. What is the use of the BETWEEN operator? Give syntax with example.
  6. Write the output of: SELECT LENGTH("Computer Science");

Section C — Short Answer (5 questions, 3 marks each)

  1. Write the SQL statement to create the STUDENT table (as per Table 9.3) with appropriate data types and constraints (RollNumber as PRIMARY KEY, SName as NOT NULL).
  2. Consider the EMPLOYEE table (Table 9.8). Write a query to display EmpNo and Ename of all employees earning salary between 20000 and 50000 (inclusive) in ascending order of salary.
  3. Identify the error in the following statement and rewrite the correct statement:
    ALTER TABLE GUARDIAN MODIFY GPhone CHAR(10) UNIQUE;
    (Assume the table exists.)
  4. Write the output of the following query on the SALE table and explain why:
    SELECT PaymentMode, COUNT(*) FROM SALE GROUP BY PaymentMode HAVING COUNT(*) > 1;
  5. Differentiate between the JOIN and NATURAL JOIN clauses with respect to the resulting table when two relations are combined on a common attribute.

Section D — Long Answer (3 questions, 5 marks each)

  1. Write the complete sequence of SQL statements to:
    - Create the database StudentAttendance.
    - Create the three tables STUDENT, GUARDIAN and ATTENDANCE (with correct data types and constraints as given in Tables 9.3–9.5, including composite primary key and foreign keys).
    - Insert the sample records shown in Tables 9.6 and 9.7.
    (Use proper syntax and termination with semicolon.)

  2. Consider the following query on the EMPLOYEE table:
    SELECT DeptId, AVG(Salary) FROM EMPLOYEE GROUP BY DeptId HAVING AVG(Salary) > 30000 ORDER BY DeptId;
    Perform a dry-run and prepare a trace table showing step-by-step processing of the GROUP BY and HAVING clauses. Show final output.

  3. Explain the working of the three set operations UNION, INTERSECT and MINUS with the help of the DANCE and MUSIC relations (Tables 9.18–9.19). Write one SQL query for each operation using the given relations.

Section E — Case/Source-Based (2 questions, 4 marks each)

Case 1: CARSHOWROOM Database

Consider the four relations INVENTORY, CUSTOMER, SALE and EMPLOYEE (Tables 9.9–9.12).

Sub-questions:
(a) Write a query to display CarId, CarName and the rounded GST (12% of Price) for all cars.
(b) Write a query to find the total commission earned by each employee (EmpID) from the SALE table.
(c) Write a query to display details of all cars whose model name contains the substring “1.2”.
(d) Write a query to display the month name in which the maximum number of cars were sold.

Case 2: SchoolUniform Database

Consider the two relations UNIFORM and COST (Tables 9.25–9.26) with UCode as the common attribute.

Sub-questions:
(a) Write a query to display UCode, UName, Size and Price of all uniforms using an explicit JOIN.
(b) Write a query to display only those records where the uniform colour is “White”.
(c) Write a query using NATURAL JOIN and state how it differs from the JOIN in (a).
(d) Write a query to find the average price of uniforms of each size.

▶ Answer Key Attempt all questions first,
then tap to reveal

Section A

  1. (b)
  2. (c)
  3. (a)
  4. (b)
  5. (b)
  6. (a)
  7. (a)
  8. (c)
  9. (b)
  10. (b)

Section B

  1. CHAR(n) reserves fixed n bytes; VARCHAR(n) allocates only the actual length used (up to n).
  2. Uniquely identifies each row; e.g., RollNumber in STUDENT.
  3. INFORMATICS PRACTICES
  4. DDL defines/modifies schema (CREATE, ALTER); DML manipulates data (INSERT, UPDATE).
  5. Checks inclusive range; column BETWEEN low AND high.
  6. 16

Section C

  1. CREATE TABLE STUDENT (RollNumber INT PRIMARY KEY, SName VARCHAR(20) NOT NULL, SDateofBirth DATE NOT NULL, GUID CHAR(12) FOREIGN KEY REFERENCES GUARDIAN(GUID));
  2. SELECT EmpNo, Ename FROM EMPLOYEE WHERE Salary BETWEEN 20000 AND 50000 ORDER BY Salary;
  3. Error: UNIQUE cannot be added with MODIFY without proper syntax; correct: ALTER TABLE GUARDIAN ADD UNIQUE(GPhone); (after ensuring datatype).
  4. Output shows only Bank Finance and Credit Card (each appears twice). GROUP BY groups rows; HAVING filters groups with count > 1.
  5. JOIN keeps the common column twice; NATURAL JOIN removes the redundant common column.

Section D

  1. (Complete correct indented code block with CREATE DATABASE, three CREATE TABLE statements with constraints, and INSERT statements matching the sample data.)
  2. Trace table shows grouping by DeptId → calculation of AVG → HAVING filter → ORDER BY result.
  3. UNION, INTERSECT and MINUS explained with the DANCE & MUSIC example and corresponding SQL.

Section E

(Correct queries for each sub-part using appropriate clauses/functions from the chapter, with brief explanation of output.)

← Revision Summary All Chapters

All questions are answerable from the NCERT chapter text. Reviewed by GFIS faculty.