Structured Query Language (SQL): CBSE Class 12 Computer Science

What Structured Query Language (SQL) is about

Weightage: This chapter is part of Unit 3, Database Management, which carries 20 of the 70 theory marks together with Database Concepts and Interface of Python with an SQL Database.Source: CBSE Computer Science (083) syllabus 2026-27, distribution of marks, Unit 3 Database Management

Key concepts

DDL, DML, data types and constraints

NCERT section NCERT 9.3, 9.4

DDL (Data Definition Language) commands define or change the structure: CREATE, ALTER, DROP. DML (Data Manipulation Language) commands work on the data: INSERT, UPDATE, DELETE, SELECT. Data types: char(n) fixed length (padded to n characters), varchar(n) variable length up to n (uses only what it needs), int whole numbers, float decimals, date in 'YYYY-MM-DD' format. Constraints are rules on a column: NOT NULL (a value is compulsory), UNIQUE (no two rows share a value; NULLs are allowed), PRIMARY KEY (unique and not NULL; one per table). Text and date values are written in single quotes; numbers are not.

Worked example

Write the command to create EMPLOYEE with EmpID as primary key, Name compulsory and the other columns as in the table.

Try it first, then show the solutionSolution

1. Pick types: codes of fixed length → char; names and cities → varchar; salary → int; DOJ → date.

2. CREATE TABLE EMPLOYEE (
     EmpID CHAR(3) PRIMARY KEY,
     Name VARCHAR(20) NOT NULL,
     DeptID CHAR(2),
     Salary INT,
     City VARCHAR(15),
     DOJ DATE
   );

Answer: The CREATE TABLE statement above (it is a DDL command).

Database and table commands; ALTER TABLE

NCERT section NCERT 9.4, 9.5

CREATE DATABASE Office;
SHOW DATABASES;
USE Office;
SHOW TABLES;
DESCRIBE EMPLOYEE;      -- or DESC EMPLOYEE: shows columns, types, keys
DROP TABLE EMPLOYEE;    -- removes the table and its data
DROP DATABASE Office;   -- removes the whole database

ALTER TABLE changes the structure of an existing table:

ALTER TABLE EMPLOYEE ADD Email VARCHAR(30);     -- add a column
ALTER TABLE EMPLOYEE DROP Email;                -- remove a column (DROP COLUMN Email also works)
ALTER TABLE DEPT DROP PRIMARY KEY;              -- remove the primary key
ALTER TABLE DEPT ADD PRIMARY KEY (DeptID);      -- add a primary key

A new column is filled with NULL in existing rows. DROP removes structure and data; DELETE removes only rows.

Worked example

EMPLOYEE has degree 6 and cardinality 6. Write commands to add a column Phone of 10 characters and then remove the column City. What is the degree now, and what does the new column hold for existing rows?

Try it first, then show the solutionSolution

1. ALTER TABLE EMPLOYEE ADD Phone CHAR(10); raises the degree to 7.
2. ALTER TABLE EMPLOYEE DROP City; lowers it to 6.
3. Existing rows get NULL in Phone; cardinality stays 6.

Answer: Degree 6 again (7 then 6); Phone is NULL in all existing rows.

INSERT, UPDATE and DELETE

NCERT section NCERT 9.6, 9.7

INSERT INTO DEPT VALUES ('D5', 'Admin', 'Pune');             -- values in column order
INSERT INTO EMPLOYEE (EmpID, Name, DeptID) VALUES ('E07', 'Neha Jain', 'D4');   -- named columns; the rest become NULL
UPDATE EMPLOYEE SET Salary = Salary + 5000 WHERE DeptID = 'D3';
DELETE FROM EMPLOYEE WHERE City = 'Mumbai';

Without WHERE, UPDATE changes every row and DELETE removes every row (the empty table remains). UPDATE can set several columns: SET Salary = 50000, City = 'Pune'.

Worked example

On the original EMPLOYEE table, run UPDATE EMPLOYEE SET Salary = Salary + 5000 WHERE DeptID = 'D3'; then DELETE FROM EMPLOYEE WHERE City = 'Mumbai';. Which employees remain, and what are the D3 salaries?

Try it first, then show the solutionSolution

1. D3 employees are Joy (45000) and Farah (45000); both become 50000.
2. Mumbai rows are Ravi (E02) and Farah (E06); both are deleted.
3. Remaining: E01 Asha, E03 Meena, E04 Joy (50000), E05 Kiran.

Answer: E01, E03, E04, E05 remain (cardinality 4); Joy's salary is 50000 (Farah's row is deleted).

SELECT with WHERE, operators and aliases

NCERT section NCERT 9.7.1-9.7.4

SELECT column_list FROM table WHERE condition; * selects all columns. Operators: mathematical + - * / %; relational = <> (or !=) < > <= >=; logical AND OR NOT. An alias renames a column or table in the output: SELECT Name, Salary * 12 AS AnnualSalary FROM EMPLOYEE; (AS is optional; use quotes if the alias has a space). DISTINCT removes duplicate rows from the result: SELECT DISTINCT City FROM EMPLOYEE;.

Worked example

Write the output of SELECT Name, Salary * 12 AS AnnualSalary FROM EMPLOYEE WHERE DeptID = 'D1';

Try it first, then show the solutionSolution

1. D1 rows: Asha Rao (52000) and Meena Iyer (61000).
2. 52000 × 12 = 624000; 61000 × 12 = 732000; the column is headed AnnualSalary.

Answer:

Name	AnnualSalary
Asha Rao	624000
Meena Iyer	732000

IN, BETWEEN, LIKE and NULL

NCERT section NCERT 9.7.5-9.7.8

IN (list) matches any value in the list: City IN ('Delhi', 'Pune'). BETWEEN a AND b is inclusive at both ends: Salary BETWEEN 40000 AND 52000 includes 52000. LIKE matches patterns: % stands for any number of characters (including none) and _ for exactly one character; '_a%' means the second letter is a. NULL means unknown: it is never equal to anything, not even NULL, so Salary = NULL finds nothing; use IS NULL or IS NOT NULL. Any arithmetic with NULL gives NULL.

Worked example

Give the outputs of (a) SELECT Name FROM EMPLOYEE WHERE Name LIKE '_a%'; (b) SELECT Name, Salary FROM EMPLOYEE WHERE Salary BETWEEN 40000 AND 52000; (c) SELECT Name FROM EMPLOYEE WHERE Salary IS NULL;

Try it first, then show the solutionSolution

1. (a) Second letter 'a': Ravi Kumar, Farah Khan (Asha has 's' second; Kiran has 'i').
2. (b) 40000 to 52000 inclusive: Asha 52000, Joy 45000, Farah 45000. Kiran's NULL is not between anything.
3. (c) Only Kiran Shah has NULL salary.

Answer: (a) Ravi Kumar, Farah Khan (b) Asha Rao 52000, Joy Das 45000, Farah Khan 45000 (c) Kiran Shah

ORDER BY

NCERT section NCERT 9.7.6

ORDER BY column [ASC | DESC] sorts the result; ASC (ascending) is the default. Several columns break ties: ORDER BY Salary DESC, Name sorts by salary high to low and, for equal salaries, by name A to Z. In MySQL, NULL comes first in ascending order and last in descending order. ORDER BY comes after WHERE (and after GROUP BY / HAVING) in the statement.

Worked example

Write the output of SELECT Name, Salary FROM EMPLOYEE WHERE Salary >= 45000 ORDER BY Salary DESC, Name;

Try it first, then show the solutionSolution

1. Rows with Salary ≥ 45000: Asha 52000, Meena 61000, Joy 45000, Farah 45000 (Kiran's NULL fails the test).
2. Descending salary: 61000, 52000, then the two 45000s.
3. Tie broken by name: Farah Khan before Joy Das.

Answer:

Name	Salary
Meena Iyer	61000
Asha Rao	52000
Farah Khan	45000
Joy Das	45000

Aggregate functions

NCERT section NCERT 9.8 (aggregate functions only)

Aggregate functions work on a whole column (or group) and return one value: MAX(), MIN(), SUM(), AVG(), COUNT(). All of them ignore NULL, except COUNT(*), which counts rows. COUNT(column) counts non-NULL values, and COUNT(DISTINCT column) counts different non-NULL values. MySQL shows AVG with decimals, e.g. 48200.0000. An aggregate cannot be used in WHERE; use HAVING for conditions on groups.

Worked example

Give the output of SELECT COUNT(*), COUNT(Salary), SUM(Salary), AVG(Salary), MAX(Salary), MIN(Salary) FROM EMPLOYEE;

Try it first, then show the solutionSolution

1. COUNT(*) counts all 6 rows; COUNT(Salary) skips Kiran's NULL: 5.
2. SUM = 52000 + 38000 + 61000 + 45000 + 45000 = 241000.
3. AVG = 241000 / 5 = 48200 (the NULL is not counted as 0).
4. MAX 61000, MIN 38000.

Answer: 6 | 5 | 241000 | 48200 (MySQL shows 48200.0000) | 61000 | 38000

GROUP BY and HAVING

NCERT section NCERT 9.9

GROUP BY column forms one group per distinct value and lets aggregates work per group: SELECT DeptID, COUNT(*) FROM EMPLOYEE GROUP BY DeptID;. HAVING filters groups after they are formed and may use aggregates: HAVING SUM(Salary) > 90000. WHERE filters rows before grouping and cannot use aggregates. Order of clauses: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY .... Columns in SELECT should be either grouped columns or aggregates.

Worked example

Give the outputs of (a) SELECT DeptID, SUM(Salary) FROM EMPLOYEE GROUP BY DeptID HAVING SUM(Salary) > 90000; (b) SELECT City, COUNT(*) FROM EMPLOYEE GROUP BY City HAVING COUNT(*) > 1;

Try it first, then show the solutionSolution

1. (a) Sums: D1 52000 + 61000 = 113000; D2 38000 (NULL ignored); D3 90000. Only D1 exceeds 90000 (90000 is not > 90000).
2. (b) Counts: Delhi 2, Mumbai 2, Chennai 1, Pune 1. Delhi and Mumbai have more than one.

Answer: (a)

DeptID	SUM(Salary)
D1	113000

(b)

City	COUNT(*)
Delhi	2
Mumbai	2

Joins: cartesian product, equi-join and natural join

NCERT section NCERT 9.10 (joins), 9.11

The cartesian product SELECT * FROM EMPLOYEE, DEPT; pairs every row of the first table with every row of the second: degree = 6 + 3 = 9, cardinality = 6 × 4 = 24. Most of those pairs are meaningless. An equi-join keeps only pairs where the common column is equal: WHERE EMPLOYEE.DeptID = DEPT.DeptID; the common column appears twice in SELECT *, and a column name present in both tables must be qualified (EMPLOYEE.DeptID) or given via table aliases (E.DeptID). A natural join SELECT * FROM EMPLOYEE NATURAL JOIN DEPT; joins on all columns with the same name automatically and shows the common column only once.

Worked example

(a) Degree and cardinality of EMPLOYEE × DEPT? (b) Output of SELECT E.Name, D.DName FROM EMPLOYEE E, DEPT D WHERE E.DeptID = D.DeptID AND D.DName = 'IT'; (c) How many columns does SELECT * FROM EMPLOYEE NATURAL JOIN DEPT; show?

Try it first, then show the solutionSolution

1. (a) Degree 6 + 3 = 9; cardinality 6 × 4 = 24.
2. (b) IT is D3; D3 employees are Joy Das and Farah Khan.
3. (c) 6 + 3 - 1 = 8, because DeptID appears once.

Answer: (a) 9 and 24 (b) Joy Das IT; Farah Khan IT (c) 8 columns

Structured Query Language (SQL) in real life

Each concept, met in an everyday situation. Read the situation, answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Topics in this chapter

• SELECT with WHERE and OperatorsComing soon
• Aggregate Functions, GROUP BY and HAVING
• Joins: Cartesian Product, Equi-Join and Natural JoinComing soon
• DDL, DML, Data Types, Constraints and Table Commands (CREATE, ALTER, DROP)
• INSERT, UPDATE and DELETE Commands
• Joins: Cartesian Product, Equi-Join and Natural Join
• LIKE, IS NULL and ORDER BY
• SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN

Structured Query Language (SQL): syntax and key facts

Clause order

SELECT [DISTINCT] cols FROM tables WHERE row-condition GROUP BY cols HAVING group-condition ORDER BY cols [ASC|DESC];

WHERE before grouping (no aggregates); HAVING after grouping (aggregates allowed).

DDL vs DML

DDL: CREATE, ALTER, DROP. DML: INSERT, UPDATE, DELETE, SELECT.

DROP TABLE removes the table; DELETE FROM removes rows only.

ALTER TABLE

ADD col type; DROP col; ADD PRIMARY KEY (col); DROP PRIMARY KEY

New columns are NULL in existing rows.

LIKE wildcards

% = any number of characters (even none); _ = exactly one character

'A%' starts with A; '%a' ends with a; '_a%' has a as the second letter; '%an%' contains an.

NULL rules

Test with IS NULL / IS NOT NULL; aggregates ignore NULL except COUNT(*); NULL in arithmetic gives NULL

AVG divides by the count of non-NULL values.

Cartesian product

degree = d1 + d2; cardinality = c1 × c2

Equi-join adds WHERE t1.col = t2.col; natural join shows the common column once.

Constraints

NOT NULL: value required. UNIQUE: no duplicates (NULLs allowed). PRIMARY KEY: unique + not NULL, one per table.

char(n) fixed length; varchar(n) variable length.

How the board asks Structured Query Language (SQL)

The question patterns to expect, and the method that works for each.

Tricks to remember Structured Query Language (SQL)

Short memory aids. Say each one aloud once, then use it on a question the same day.

• "Some Friendly Wizards Go Home Often"

  SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

  Use it when: Clause order in every query.

• CAD for DDL

  Create, Alter, Drop change the Design.

  Use it when: Classifying DDL and DML.

• COUNT star counts stars too

  COUNT(*) counts every row, even those full of NULLs; every other aggregate skips NULL.

  Use it when: COUNT and AVG output questions.

• Cartesian: add the columns, multiply the rows

  Degree adds, cardinality multiplies.

  Use it when: Cartesian product questions.

• BETWEEN hugs both ends

  BETWEEN 40000 AND 52000 includes 40000 and 52000.

  Use it when: Range queries.

• NULL is not a value

  Nothing equals NULL, so ask 'IS it NULL?', never '= NULL'.

  Use it when: NULL conditions.

Structured Query Language (SQL): board-level solved questions

Ordered from board level to HOTS. Cover the solution, write your own, then compare step by step.

Case-study practice (Section E style)

Read the situation, then answer every part. In the board paper this is a 4-mark question split into 1 + 1 + 2 marks.

Where the marks are

How a typical answer is split into step marks. Show every step: a right final answer alone rarely earns full marks.

Where students lose marks in Structured Query Language (SQL)

The slips examiners see most often, and what to write instead.

• ✗Mistake: Using an aggregate in WHERE

  WHERE runs before rows are grouped, so aggregates are not available there.

  Wrong working

  SELECT DeptID FROM EMPLOYEE WHERE COUNT(*) > 1 GROUP BY DeptID;

  Right working

  SELECT DeptID FROM EMPLOYEE GROUP BY DeptID HAVING COUNT(*) > 1;

  ✓Do this: Conditions on SUM, COUNT, AVG, MAX or MIN go in HAVING.

  Usually costs: 1 mark

• ✗Mistake: Writing = NULL instead of IS NULL

  NULL is unknown, so a comparison with = is never true.

  Wrong working

  SELECT Name FROM EMPLOYEE WHERE Salary = NULL;

  Right working

  SELECT Name FROM EMPLOYEE WHERE Salary IS NULL;

  ✓Do this: Always IS NULL / IS NOT NULL.

  Usually costs: 1 mark

• ✗Mistake: Counting NULL in AVG or COUNT(column)

  Aggregates skip NULL; only COUNT(*) counts every row.

  Wrong working

  AVG(Salary) = 241000 / 6 = 40166.67

  Right working

  AVG(Salary) = 241000 / 5 = 48200

  ✓Do this: Cross out NULL rows before computing any aggregate except COUNT(*).

  Usually costs: 1 mark in an output question

• ✗Mistake: Leaving out quotes around text or dates, or quoting numbers carelessly

  City = Delhi is read as a column name; dates need 'YYYY-MM-DD' in quotes.

  Wrong working

  WHERE City = Delhi AND DOJ > 2020-01-01

  Right working

  WHERE City = 'Delhi' AND DOJ > '2020-01-01'

  ✓Do this: Quote every text and date literal; leave numbers unquoted.

  Usually costs: Half to 1 mark

• ✗Mistake: Ambiguous column in a join

  DeptID exists in both tables, so SELECT DeptID ... FROM EMPLOYEE, DEPT is ambiguous.

  Wrong working

  SELECT Name, DeptID, DName FROM EMPLOYEE, DEPT WHERE DeptID = DeptID;

  Right working

  SELECT Name, E.DeptID, DName FROM EMPLOYEE E, DEPT D WHERE E.DeptID = D.DeptID;

  ✓Do this: Qualify common columns with the table name or an alias.

  Usually costs: 1 mark

• ✗Mistake: Confusing DELETE and DROP

  DELETE removes rows (DML); DROP TABLE removes the table itself (DDL).

  ✓Do this: To empty a table keep its structure: DELETE FROM T; to remove the table: DROP TABLE T; to remove a column: ALTER TABLE T DROP col.

  Usually costs: 1 mark

NCERT exercise guide: Structured Query Language (SQL)

What each exercise in the NCERT chapter tests, how to approach it, and a question of the same kind solved step by step. Questions here are written in our own words.

Practice questions on Structured Query Language (SQL)

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQBoard level

   Which of these is a DML command? (a) CREATE (b) ALTER (c) UPDATE (d) DROP

   Hint

   It changes data, not structure.

   Answer

   (c) UPDATE

2. Q2 · 1 mark · Very short answerBoard level

   Write the output of SELECT Name FROM EMPLOYEE WHERE City IN ('Delhi', 'Pune') AND Salary IS NOT NULL;

   Hint

   Kiran is in Pune but has NULL salary.

   Answer

   Asha Rao, Joy Das

3. Q3 · 1 mark · Very short answerBoard level

   Write the command to add a primary key on EmpID if EMPLOYEE was created without one, and the command to show the table's structure.

   Hint

   ALTER and DESCRIBE.

   Answer

   ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EmpID); DESCRIBE EMPLOYEE;

4. Q4 · 2 marks · Short answerHarder board level

   Write the output of SELECT DName, COUNT(*) FROM EMPLOYEE E, DEPT D WHERE E.DeptID = D.DeptID GROUP BY DName ORDER BY DName;

   Hint

   HR has no employees, so it does not appear.

   Answer

   DName	COUNT(*)
   Finance	2
   IT	2
   Sales	2

5. Q5 · 1 mark · Assertion-reasonHOTS / competency

   Assertion (A): SELECT * FROM EMPLOYEE NATURAL JOIN DEPT; displays DeptID only once.
   Reason (R): A natural join joins on columns with the same name and removes the duplicate column.

   Hint

   Compare with an equi-join.

   Answer

   (a) Both A and R are true and R is the correct explanation of A.

6. Q6 · 2 marks · Short answerHarder board level

   Write queries to (i) display names and annual salary (Salary × 12) as 'Annual' of employees of D2 and D3 (ii) display the total salary of each city in descending order of total.

   Hint

   Alias; GROUP BY with ORDER BY on the aggregate.

   Answer

   (i) SELECT Name, Salary * 12 AS Annual FROM EMPLOYEE WHERE DeptID IN ('D2', 'D3');
   (ii) SELECT City, SUM(Salary) FROM EMPLOYEE GROUP BY City ORDER BY SUM(Salary) DESC;

7. Q7 · 2 marks · Short answerHOTS / competency

   Write the output of SELECT Name, Salary FROM EMPLOYEE ORDER BY Salary; and explain where Kiran Shah appears.

   Hint

   MySQL puts NULL first in ascending order.

   Answer

   Kiran Shah NULL; Ravi Kumar 38000; Joy Das 45000; Farah Khan 45000; Asha Rao 52000; Meena Iyer 61000. NULL sorts first in ascending order in MySQL (the two 45000 rows may appear in either order).

8. Q8 · 2 marks · Short answerBoard level

   Differentiate between char(n) and varchar(n) with an example of when to use each.

   Hint

   Fixed vs variable.

   Answer

   char(n) stores exactly n characters (padding shorter values), good for fixed-length codes such as EmpID 'E01' or PIN codes. varchar(n) stores only the characters entered up to n, good for names and addresses whose lengths vary.

9. Q9 · 2 marks · Short answerHarder board level

   The department D1 moves to Gurugram, and all its employees get a 5% raise. Write the two statements.

   Hint

   Two tables are updated.

   Answer

   UPDATE DEPT SET Location = 'Gurugram' WHERE DeptID = 'D1';
   UPDATE EMPLOYEE SET Salary = Salary * 1.05 WHERE DeptID = 'D1';

10. Q10 · 3 marks · Short answerHOTS / competency

    Write a query to display, for each department name, the highest salary, only where that highest salary is at least 45000, using an equi-join. Give its output.

    Hint

    GROUP BY DName with HAVING MAX(Salary) >= 45000.

    Answer

    SELECT DName, MAX(Salary) FROM EMPLOYEE E, DEPT D WHERE E.DeptID = D.DeptID GROUP BY DName HAVING MAX(Salary) >= 45000;
    Output: IT 45000; Sales 61000 (Finance's maximum is 38000, so it is left out; group order may differ).

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.Is ALTER a DDL or DML command?Show answer

   DDL.

2. 2.What is the difference between char(10) and varchar(10)?Show answer

   char(10) always uses 10 characters (padded); varchar(10) uses only as many as stored, up to 10.

3. 3.Which wildcard in LIKE matches exactly one character?Show answer

   _ (underscore).

4. 4.What does COUNT(Salary) return for EMPLOYEE?Show answer

   5 (Kiran's NULL is skipped).

5. 5.Which clause filters groups?Show answer

   HAVING.

6. 6.What is the cardinality of the cartesian product of a 6-row and a 4-row table?Show answer

   24.

7. 7.Write the command to remove the primary key of DEPT.Show answer

   ALTER TABLE DEPT DROP PRIMARY KEY;

8. 8.How does a natural join differ from an equi-join in its output?Show answer

   A natural join shows the common column once; an equi-join with SELECT * shows it twice.

9. 9.Which command shows the structure of a table?Show answer

   DESCRIBE table_name; (or DESC).

Quick revision checklist

The night before the exam: can you do each of these without looking?

• ☐DDL: CREATE, ALTER, DROP. DML: INSERT, UPDATE, DELETE, SELECT.
• ☐char(n) fixed; varchar(n) variable; int; float; date 'YYYY-MM-DD'.
• ☐NOT NULL, UNIQUE (NULL allowed), PRIMARY KEY (unique + not NULL).
• ☐ALTER TABLE: ADD col; DROP col; ADD PRIMARY KEY (col); DROP PRIMARY KEY.
• ☐UPDATE/DELETE without WHERE affect all rows.
• ☐IN for lists; BETWEEN inclusive; LIKE with % and _; IS NULL.
• ☐ORDER BY default ASC; extra columns break ties.
• ☐Aggregates ignore NULL except COUNT(*); MySQL shows AVG with 4 decimals.
• ☐WHERE before GROUP BY; HAVING after; ORDER BY last.
• ☐Cartesian: degree adds, cardinality multiplies; equi-join on =; natural join shows the common column once.

Structured Query Language (SQL): frequently asked questions