SUMMARY: This chapter focuses on teaching students how to write and execute SQL queries to manage and manipulate databases. KEY TOPICS: SQL syntax, SELECT statement, WHERE clause, JOIN operations, GROUP BY clause, ORDER BY clause, INSERT statement, UPDATE statement, DELETE statement, aggregate functions
Which SQL command is used to retrieve data from a database?
AGET
BSELECT
CFETCH
DRETRIEVE
Check answerHide answer
Correct answer: Option 2 — SELECT
Q21 Mark
Which SQL clause is used to filter records?
AWHERE
BFILTER
CHAVING
DIF
Check answerHide answer
Correct answer: Option 1 — WHERE
Q31 Mark
Which SQL keyword is used to sort the result-set in ascending order by default?
ASORT BY
BGROUP BY
CORDER BY
DLIST BY
Check answerHide answer
Correct answer: Option 3 — ORDER BY
Q41 Mark
The SQL command used to add a new row to a table is:
AADD
BAPPEND
CINSERT
DCREATE
Check answerHide answer
Correct answer: Option 3 — INSERT
Q51 Mark
Which SQL function returns the total number of rows in a column?
ASUM
BCOUNT
CTOTAL
DLEN
Check answerHide answer
Correct answer: Option 2 — COUNT
Short Answer Questions5 questions
Q63 Marks
Differentiate between DDL and DML commands with two examples each.
Q73 Marks
Write a SQL query to display all rows from the table employee.
Q83 Marks
Differentiate between WHERE and HAVING clauses.
Q93 Marks
Write a SQL query to find the total number of employees in each department from a table employee(emp_id name dept salary).
Q103 Marks
Explain the use of DISTINCT keyword in SQL with one example.
Long Answer Questions6 questions
Q116 Marks
Discuss any five SQL aggregate functions with examples on a sample employee table.
Q126 Marks
Differentiate between INNER JOIN LEFT JOIN and RIGHT JOIN with one example each.
Q136 Marks
Write SQL queries to: (a) create a table student (b) insert 3 rows (c) update marks of one student (d) delete a student (e) display students with marks > 80.
Q146 Marks
Explain GROUP BY and HAVING clauses with examples.
Q156 Marks
Discuss constraints in SQL — PRIMARY KEY FOREIGN KEY UNIQUE NOT NULL CHECK DEFAULT — with one example each.
Q166 Marks
Differentiate between DDL and DML SQL commands in tabular form with examples.
Assertion–Reason Questions5 questions
Q171 Mark
Assertion (A): SELECT is a DQL (Data Query Language) command.
Reason (R): It is used to retrieve data from one or more tables.
Show explanationHide explanation
Correct answer: Option 1 —
Both A and R are true, and R is the correct explanation of A.
Q181 Mark
Assertion (A): GROUP BY groups rows with same value in a column.
Reason (R): It is often used with aggregate functions.
Show explanationHide explanation
Correct answer: Option 1 —
Both A and R are true, and R is the correct explanation of A.
Q191 Mark
Assertion (A): COUNT(*) returns the total number of rows including NULLs.
Reason (R): COUNT(column) ignores NULL values in that column.
Show explanationHide explanation
Correct answer: Option 1 —
Both A and R are true, and R is the correct explanation of A.
Q201 Mark
Assertion (A): DELETE removes rows from a table.
Reason (R): DROP removes the entire table including its structure.
Show explanationHide explanation
Correct answer: Option 1 —
Both A and R are true, and R is the correct explanation of A.
Q211 Mark
Assertion (A): The LIKE operator is used for pattern matching.
Reason (R): Wildcard % matches any sequence of characters.
Show explanationHide explanation
Correct answer: Option 1 —
Both A and R are true, and R is the correct explanation of A.
Statement-Based Questions5 questions
Q221 Mark
Statement 1: DDL commands include CREATE ALTER and DROP.
Statement 2: They define and modify the structure of a database.
Show answerHide answer
Correct answer: Option 1 —
Both statements are true.
Q231 Mark
Statement 1: DML commands include INSERT UPDATE and DELETE.
Statement 2: They modify the data within tables.
Show answerHide answer
Correct answer: Option 1 —
Both statements are true.
Q241 Mark
Statement 1: WHERE filters rows before grouping.
Statement 2: HAVING filters groups after aggregation.
Show answerHide answer
Correct answer: Option 1 —
Both statements are true.
Q251 Mark
Statement 1: A NULL value is different from zero or empty string.
Statement 2: It represents missing or unknown data.
Show answerHide answer
Correct answer: Option 1 —
Both statements are true.
Q261 Mark
Statement 1: JOIN combines rows from two or more tables based on a related column.
Statement 2: Different types include INNER LEFT RIGHT and FULL OUTER joins.
Show answerHide answer
Correct answer: Option 1 —
Both statements are true.
Case Study / Passage Questions1 question
Q273 Marks
A company has an employee table with columns emp_id name dept salary. The HR manager wants to write SQL queries to: (1) list all employees in HR (2) find the average salary in each department (3) get the top 3 highest-paid employees (4) count employees per department.
Which SQL command retrieves data from a table?
ASELECT
BGET
CFETCH
DRETRIEVE
Which clause is used to filter individual rows?
AWHERE
BHAVING
CGROUP BY
DORDER BY
Write the SQL queries for all four HR requirements.
Show answersHide answers
1. Option 1 — SELECT
2. Option 1 — WHERE
3. (1) SELECT * FROM employee WHERE dept = 'HR'. (2) SELECT dept AVG(salary) FROM employee GROUP BY dept. (3) SELECT * FROM employee ORDER BY salary DESC LIMIT 3. (4) SELECT dept COUNT(*) FROM employee GROUP BY dept. WHERE filters individual rows; GROUP BY groups rows; HAVING filters groups; ORDER BY sorts; LIMIT restricts the count.
Table-Based Questions4 questions
Q286 Marks
For the sample employee table compute the result of each SQL query.
emp_id
name
dept
salary
1
Anil
IT
50000
2
Bobby
HR
40000
3
Chitra
HR
35000
4
Geeta
IT
60000
5
Hari
Sales
40000
Q295 Marks
Match each SQL clause with its purpose.
Clause
Purpose
SELECT
?
WHERE
?
GROUP BY
?
HAVING
?
ORDER BY
?
LIMIT
?
Q305 Marks
For the sample table evaluate each SQL aggregate query.
Query
Result
COUNT(*)
?
SUM(salary)
?
AVG(salary)
?
MAX(salary)
?
MIN(salary)
?
COUNT(DISTINCT dept)
?
Q315 Marks
Match SQL command type (DDL/DML/DCL/DQL/TCL) for each.
Command
Type
CREATE
?
INSERT
?
SELECT
?
GRANT
?
COMMIT
?
DROP
?
UPDATE
?
Picture-Based Questions2 questions
Q323 Marks
Study the SQL JOIN Venn diagrams and answer:
Which JOIN returns only matching rows from both tables?
AINNER JOIN
BLEFT JOIN
CFULL JOIN
DCROSS JOIN
Which JOIN returns all rows from the left table plus matches from the right?
AINNER
BLEFT
CRIGHT
DFULL
Differentiate between INNER LEFT RIGHT and FULL JOINs with examples.
Show answersHide answers
1. Option 1 — INNER JOIN
2. Option 2 — LEFT
3. INNER JOIN returns only matching rows from both tables (intersection). LEFT JOIN returns all rows from left + matching from right (NULL for non-matching). RIGHT JOIN is the mirror. FULL JOIN returns all rows from both tables. CROSS JOIN returns Cartesian product. JOINs combine rows based on a related column (typically primary key foreign key).
Q333 Marks
Study the SQL execution order diagram and answer:
Which clause is executed FIRST?
ASELECT
BFROM
CWHERE
DORDER BY
Which clause filters AGGREGATED groups?
AWHERE
BHAVING
CGROUP BY
DORDER BY
Discuss the logical execution order of SQL clauses and explain WHERE vs HAVING.
Show answersHide answers
1. Option 2 — FROM
2. Option 2 — HAVING
3. SQL clauses are written in the order SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT but executed in a different logical order: FROM WHERE GROUP BY HAVING SELECT ORDER BY LIMIT. This means WHERE filters individual rows BEFORE grouping while HAVING filters groups AFTER aggregation. Knowing the execution order helps write correct queries.
