SQL (Structured Query Language)

Syllabus tag: Paper-II, Section-B, Topic IV.3 (part 1) — SQL

Definition

SQL (Structured Query Language) is the standard declarative language for defining, manipulating, querying, and controlling access to relational databases.

SQL Sub-languages

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE — defines/modifies schema
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE — manipulates data
• DCL (Data Control Language): GRANT, REVOKE — controls access permissions
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT — manages transaction boundaries
• CSS angle: FPSC 2016 Q5(C) directly asked to differentiate DML vs DDL — always give one example command per category, not just the category name.

SELECT Statement — Full Clause Order

SELECT [DISTINCT] column_list
FROM table1
[JOIN table2 ON condition]
[WHERE row_condition]
[GROUP BY column_list]
[HAVING group_condition]
[ORDER BY column_list [ASC|DESC]];

• WHERE filters rows before grouping; HAVING filters groups after GROUP BY — a classic exam distinction.

Search Conditions in WHERE (FPSC 2016, 2023)

• Comparison: =, <>, <, >, <=, >=
• Compound comparison: combine conditions with AND / OR / NOT
• Range: BETWEEN x AND y / NOT BETWEEN
• Set membership: IN (v1, v2, ...) / NOT IN
• Pattern match: LIKE / NOT LIKE with wildcards % (any sequence) and _ (single character), e.g. WHERE name LIKE 'A%'
• NULL test: IS NULL / IS NOT NULL

Joins (FPSC 2020, Q6(a))

SELECT e.Name, d.DeptName
FROM Employee e
LEFT JOIN Department d ON e.DeptId = d.DeptId;

Subqueries (FPSC 2016, 2025)

• A subquery (subselect) is a complete SELECT nested inside another SQL statement; its result feeds the outer query.

• Single-row subquery: returns exactly one row/value — used with =, <, > etc.

  SELECT Name FROM Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee);

• Multi-row subquery: returns multiple rows — used with IN, ANY, ALL, EXISTS

  SELECT Name FROM Employee WHERE DeptId IN (SELECT DeptId FROM Department WHERE Location='Lahore');

• Using = with a subquery that returns more than one row causes a runtime error — a classic MCQ trap.

Set Operators

• UNION (removes duplicates), UNION ALL (keeps duplicates), INTERSECT, MINUS/EXCEPT — operands must have matching degree and compatible domains.

GRANT / REVOKE (DCL)

GRANT SELECT, INSERT ON Employee TO 'clerk_user';
REVOKE INSERT ON Employee FROM 'clerk_user';

MCQ Trap

• WHERE cannot reference an aggregate function (WHERE COUNT(*) > 5 is invalid) — use HAVING instead.
• DELETE (DML, logged, rollback-able, row-by-row) vs TRUNCATE (DDL, minimally logged, resets identity, cannot be rolled back in most RDBMS) vs DROP (removes the table structure entirely).

CSS Exam Angle

• SQL is the single highest-yield DB subtopic: tested almost every year (2016, 2020, 2023, 2024, 2025) via joins, subqueries, search conditions, and short-notes on DML/DDL.
• FPSC often asks you to write a query against a given schema (e.g., HR schema: EMPLOYEES, DEPARTMENTS) — practice reading an ER/table schema quickly and mapping it to a query rather than memorizing fixed queries.

Likely Questions

1. Differentiate DDL, DML, DCL, and TCL with one example command each.
2. Explain inner, left, right, and full joins with an example each.
3. Differentiate single-row and multi-row subqueries; write a sample query for each.
4. Explain the LIKE/NOT LIKE pattern-match search condition with wildcard examples.
5. Write a SQL query involving multiple tables with a JOIN, and discuss a potential performance pitfall.

---

📘 Enrichment — Practice Schema with Outputs

Practise every query against this one schema. In the exam, write the expected output table next to your query — it proves you understand the query, not just the syntax.

Department

Employee

DDL + DML Warm-up

CREATE TABLE Department (
    DeptId   INT PRIMARY KEY,
    DeptName VARCHAR(30) NOT NULL,
    Location VARCHAR(30)
);
CREATE TABLE Employee (
    EmpId     INT PRIMARY KEY,
    Name      VARCHAR(50) NOT NULL,
    Salary    DECIMAL(10,2) CHECK (Salary > 0),
    DeptId    INT REFERENCES Department(DeptId),
    ManagerId INT REFERENCES Employee(EmpId)
);
ALTER TABLE Employee ADD Email VARCHAR(80);            -- DDL
INSERT INTO Employee VALUES (6,'Kamran',80000,20,3,NULL); -- DML
UPDATE Employee SET Salary = Salary * 1.10 WHERE DeptId = 20;
DELETE FROM Employee WHERE EmpId = 6;

Graded Query Set (easy → hard)

Outputs below assume the original tables above (i.e., before the warm-up UPDATE/INSERT/DELETE).

Q1 — Pattern match: names whose second letter is 'a':

SELECT Name FROM Employee WHERE Name LIKE '_a%';

Output: Zara (Z-a-ra). Hina → 'i' second, Bilal → 'i' second, so excluded.

Q2 — Range + set membership:

SELECT Name FROM Employee
WHERE Salary BETWEEN 70000 AND 120000 AND DeptId IN (10, 20, 30);

Output: Bilal, Hina, Usman (BETWEEN is inclusive of both ends).

Q3 — NULL test: SELECT Name FROM Employee WHERE DeptId IS NULL; → Zara. (Writing = NULL returns nothing — classic trap.)

Q4 — Aggregates:

SELECT COUNT(*), COUNT(DeptId), AVG(Salary), MAX(Salary) FROM Employee;

Output: 5, 4, 98000, 150000 — COUNT(*) counts rows; COUNT(column) ignores NULLs.

Q5 — GROUP BY + HAVING:

SELECT DeptId, COUNT(*) AS Staff, AVG(Salary) AS AvgSal
FROM Employee
WHERE DeptId IS NOT NULL
GROUP BY DeptId
HAVING COUNT(*) > 1;

Before HAVING the groups are 10 → (2, 120000), 20 → (1, 70000), 30 → (1, 120000). After HAVING: 10 | 2 | 120000.

Q6 — Single-row subquery: above-average earners.

SELECT Name FROM Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee);

Average = 490000 ÷ 5 = 98000 → Ayesha, Usman.

Q7 — Second-highest salary (favourite interview/exam question):

SELECT MAX(Salary) FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Output: 120000.

Q8 — Multi-row subquery with IN: employees working in Lahore.

SELECT Name FROM Employee
WHERE DeptId IN (SELECT DeptId FROM Department WHERE Location = 'Lahore');

Inner query returns {20, 40} → Output: Hina.

Q9 — Correlated subquery: employees earning more than their own department's average.

SELECT e.Name FROM Employee e
WHERE e.Salary > (SELECT AVG(x.Salary) FROM Employee x WHERE x.DeptId = e.DeptId);

The inner query re-runs for every outer row. CS avg = 120000 → Ayesha qualifies; HR and Finance have one employee each (salary = average) → no. Output: Ayesha.

Q10 — EXISTS / NOT EXISTS: departments with no employees.

SELECT d.DeptName FROM Department d
WHERE NOT EXISTS (SELECT 1 FROM Employee e WHERE e.DeptId = d.DeptId);

Output: Legal.

Q11 — Self-join: each employee with their manager's name.

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employee e LEFT JOIN Employee m ON e.ManagerId = m.EmpId;

Output: Ayesha–NULL, Bilal–Ayesha, Hina–Ayesha, Usman–Ayesha, Zara–Hina.

Join Results Side by Side (FPSC 2020 favourite)

Employee e JOIN Department d ON e.DeptId = d.DeptId

flowchart LR
    A["INNER = only overlap"] --- B["LEFT = all Employee + overlap"]
    B --- C["RIGHT = all Department + overlap"]
    C --- D["FULL = everything from both"]

Views

CREATE VIEW PublicStaff AS
SELECT EmpId, Name, DeptId FROM Employee;      -- hides Salary
GRANT SELECT ON PublicStaff TO receptionist;

• A view is a stored query (virtual table). Uses: security (hide columns/rows), simplicity (hide complex joins), logical data independence.
• A materialized view physically stores the result and is refreshed periodically — used in reporting/dashboards.
• A view is generally updatable only if it is based on one table, has no aggregates, DISTINCT or GROUP BY, and includes the key.

Query-Writing Pitfalls & Optimizations (FPSC 2024 Q)

• SELECT * fetches unneeded columns → list columns explicitly.
• A function on an indexed column (WHERE YEAR(JoinDate) = 2024) prevents index use → rewrite as a range.
• LIKE '%abc' (leading wildcard) cannot use a B+-tree index.
• Missing join condition → accidental Cartesian product.
• NOT IN with a subquery that can return NULL returns no rows → prefer NOT EXISTS.
• Correlated subqueries can run once per row → rewrite as a JOIN + GROUP BY where possible.
• Index the columns used in WHERE, JOIN and ORDER BY; check the plan with EXPLAIN.

---

✍️ Model Answers — Likely Questions

Q1. Differentiate DDL, DML, DCL and TCL with one example each.

Key difference DDL vs DML: DDL changes the schema and is usually auto-committed; DML changes the data and can be rolled back.

Q2. Explain inner, left, right and full joins with examples.

Define each in one line, then show the Join Results Side by Side table above using the Employee/Department data (4, 5, 5, 6 rows). Add the Venn-style diagram and one SQL statement. Conclude: inner joins answer "matched only"; outer joins answer "including the unmatched" — e.g., LEFT JOIN finds employees not yet assigned to any department.

Q3. Differentiate single-row and multi-row subqueries with sample queries.

• Single-row: inner query returns one value; used with =, <, >, <=, >=, <>. Example: Q6 (above-average earners) → Ayesha, Usman.
• Multi-row: returns many values; used with IN, ANY, ALL, EXISTS. Example: Q8 (Lahore departments) → Hina.
• Correlated: inner query references the outer row and re-executes per row (Q9).
• Trap: WHERE DeptId = (SELECT DeptId FROM Department WHERE Location='Lahore') fails because two rows (20, 40) come back — use IN.

Q4. Explain LIKE / NOT LIKE with wildcard examples.

• % = zero or more characters; _ = exactly one character.
• LIKE 'A%' → starts with A (Ayesha). LIKE '%a' → ends with a (Ayesha, Hina, Zara). LIKE '_a%' → second letter a (Zara). LIKE '____' → exactly four letters (Hina, Zara).
• NOT LIKE 'A%' → everyone except Ayesha.
• ESCAPE lets you search for a literal % or _, e.g. LIKE '50\%' ESCAPE '\'.
• Note: a leading % prevents index use — a performance pitfall.

Q5. Write a multi-table JOIN query and discuss a performance pitfall.

Query: names and department names of employees in Lahore earning above 60,000.

SELECT e.Name, d.DeptName FROM Employee e JOIN Department d ON e.DeptId = d.DeptId WHERE d.Location = 'Lahore' AND e.Salary > 60000; → Hina, HR.

Pitfalls: forgetting the ON condition (Cartesian product of 20 rows here, millions in real tables); no index on Employee.DeptId (the optimizer falls back to a full scan / nested-loop); applying functions to indexed columns; SELECT *.

Optimizations: index FK columns, filter early (the optimizer pushes the Location selection before the join), select only needed columns, inspect the plan with EXPLAIN.

---

⚡ 60-Second Revision

• Clause execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• WHERE filters rows, HAVING filters groups; aggregates never in WHERE.
• COUNT(*) counts NULL rows, COUNT(col) does not; = NULL is always unknown → use IS NULL.
• DELETE (DML, rollback) · TRUNCATE (DDL, fast, no WHERE) · DROP (removes table).
• UNION removes duplicates, UNION ALL keeps them.

📜 Past-Paper Questions & MCQs (CSS 2016–2026)

2016 · Paper-II · Q5(a) · 8 marks

Q. A nested sub-query is a complete SELECT statement embedded within another SELECT. Differentiate among the nested sub-query operators IN, ANY and ALL.

Ans.

• IN: true if the value equals any value in the sub-query's result list. WHERE branchNo IN (SELECT branchNo FROM Branch WHERE city = 'Lahore'). (NOT IN is the opposite.)
• ANY (SOME): the comparison is true if it holds for at least one value returned. salary > ANY (subquery) means greater than the smallest value. = ANY is the same as IN.
• ALL: the comparison is true only if it holds for every value returned. salary > ALL (subquery) means greater than the largest value.

SELECT name FROM Staff
WHERE salary > ALL (SELECT salary FROM Staff WHERE branchNo = 'B3');

• If the sub-query returns no rows: ALL is true and ANY is false.

2016 · Paper-II · Q5(b) · 6 marks

Q. Explain how the pattern-match search condition (LIKE / NOT LIKE) can be used in the SELECT statement.

Ans.

• LIKE tests a character column against a pattern. % matches any sequence of zero or more characters, and _ matches exactly one character.

SELECT * FROM Staff WHERE name LIKE 'A%';        -- starts with A
SELECT * FROM Staff WHERE name LIKE '%khan';     -- ends with khan
SELECT * FROM Staff WHERE name LIKE '_a%';       -- second letter is a
SELECT * FROM Staff WHERE name NOT LIKE '%son';  -- does not end with son

• ESCAPE allows a literal % or _ in the pattern. LIKE is case-sensitive in some systems and case-insensitive in others.

2016 · Paper-II · Q5(c) · 6 marks

Q. Differentiate between DML and DDL of SQL.

Ans.

2020 · Paper-II · Q6(a) · 8 marks

Q. What is the purpose of a join in SQL? Explain inner, left, right and full join with the help of examples.

Ans.

• A join combines rows of two or more tables on a related column (usually a foreign key matching a primary key), so related data stored in separate tables can be retrieved together.
• Example tables: Employee(id, name, dept_id) and Department(dept_id, dname).
• INNER JOIN: only rows with a match in both tables.
• LEFT (OUTER) JOIN: all rows of the left table, with NULLs where the right side has no match (employees without a department).
• RIGHT (OUTER) JOIN: all rows of the right table, with NULLs on the left (departments with no employees).
• FULL (OUTER) JOIN: all rows of both tables, with NULLs where there is no match.

SELECT e.name, d.dname
FROM Employee e LEFT JOIN Department d ON e.dept_id = d.dept_id;

2023 · Paper-II · Q6(a) · 12 marks

Q. Explain the following SELECT search conditions with examples: compound comparison, range (BETWEEN / NOT BETWEEN), set membership (IN / NOT IN) and pattern match (LIKE / NOT LIKE). (Schema: Staff(staffNo, name, position, salary, branchNo).)

Ans.

• 1. Compound comparison: conditions combined with AND, OR and NOT (NOT first, then AND, then OR; use brackets).

SELECT * FROM Staff WHERE position = 'Manager' AND (salary > 50000 OR branchNo = 'B3');

• 2. Range: BETWEEN low AND high (inclusive at both ends).

SELECT * FROM Staff WHERE salary BETWEEN 30000 AND 60000; NOT BETWEEN selects values outside the range.

• 3. Set membership: the value must (or must not) be in a list.

SELECT * FROM Staff WHERE position IN ('Manager', 'Supervisor'); NOT IN excludes the list.

• 4. Pattern match: LIKE with % and _.

SELECT * FROM Staff WHERE name LIKE 'S%'; NOT LIKE excludes matches.

2024 · Paper-II · Q6(b) · 7 marks

Q. Write a SQL query involving multiple tables and JOIN operations. Discuss the potential pitfalls and optimisations related to complex SQL queries.

Ans.

SELECT s.name, c.title, e.grade
FROM Student s
JOIN Enrollment e ON s.id = e.student_id
JOIN Course c     ON c.code = e.course_code
WHERE c.credit_hours >= 3
ORDER BY s.name;

• Pitfalls:
  • a missing join condition creates a Cartesian product (huge, wrong result)
  • NULLs behave differently in joins and comparisons
  • duplicates from one-to-many joins, and ambiguous column names
  • a filter on the outer table in the WHERE clause turns a LEFT JOIN into an INNER JOIN
  • SELECT * and unneeded columns waste I/O
• Optimisations:
  • indexes on join and filter columns
  • filter early and select only needed columns
  • avoid functions on indexed columns in WHERE
  • read the execution plan (EXPLAIN) and keep statistics up to date
  • rewrite correlated sub-queries as joins, and use views or materialised views for repeated work

2025 · Paper-II · Q6(a) · 10 marks

Q. Using the HR schema (EMPLOYEES, DEPARTMENTS, JOBS, JOB_HISTORY, …): (i) display the length of the first name and of the last name for employees whose last name contains the character 'b' after the 3rd position; (ii) display job title, department name, employee last name and start date for all jobs from 1992 to 1998; (iii) differentiate between left, right and full outer joins using a Venn diagram.

Ans.

-- (i)
SELECT LENGTH(first_name) AS fname_len, LENGTH(last_name) AS lname_len
FROM employees
WHERE INSTR(last_name, 'b', 4) > 0;          -- 'b' found at position 4 or later

-- (ii)
SELECT j.job_title, d.department_name, e.last_name, jh.start_date
FROM job_history jh
JOIN employees e   ON jh.employee_id   = e.employee_id
JOIN jobs j        ON jh.job_id        = j.job_id
JOIN departments d ON jh.department_id = d.department_id
WHERE jh.start_date BETWEEN DATE '1992-01-01' AND DATE '1998-12-31';

• (iii) Outer joins (A = left table, B = right table):

Left outer join : all of A  + the part of B that overlaps A   ( ◖A◗ ∩ B  with all of A )
Right outer join: all of B  + the part of A that overlaps B
Full outer join : all of A and all of B (A ∪ B), with NULLs where there is no match
Inner join      : only the overlap (A ∩ B)

• The unmatched side is filled with NULLs in each case.

2025 · Paper-II · Q6(b)(i) · 5 marks

Q. Differentiate between a single-row sub-query and a multi-row sub-query and write a sample query.

Ans.

• Single-row sub-query: returns one row (one value) and is used with single-row operators =, <, >, <=, >=, <>.

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

• Multi-row sub-query: returns several rows and needs multi-row operators IN, ANY, ALL.

SELECT name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Lahore');

• Using = with a sub-query that returns more than one row causes an error.

MCQs

2024 P-II · Q13. DDL is used to:

• (A) Represent the database structure
• (B) Define and manage the structure of a database ✅
• (C) Deal with the manipulation of data stored in the database
• (D) None of these
• Answer: (B).
• Why: the Data Definition Language (CREATE, ALTER, DROP) defines and changes the schema. Manipulating the stored data is the job of the DML (SELECT, INSERT, UPDATE, DELETE), which is option (C).

2026 P-II · Q8. The Data Definition Language (DDL) describes:

• (A) How data are structured in the database ✅
• (B) The DBMS what is required; the techniques used to process data
• (C) How data must be structured to produce the user's view
• (D) None of these
• Answer: (A).
• Why: DDL specifies the schema, meaning the tables, attributes and constraints. Option (C) describes an external view (the view definition language), and (B) describes processing, which is the DML's job.