SUMMARY: This chapter introduces students to fundamental database concepts and the use of SQL for managing and manipulating databases. KEY TOPICS: relational databases, SQL commands, data types, primary key, foreign key, normalization, DDL, DML, constraints, joins
Which SQL clause is used with GROUP BY for filtering grouped data?
AWHERE
BHAVING
CIF
DFILTER
Check answerHide answer
Correct answer: Option 2 — HAVING
Q51 Mark
Which join returns matching rows from both tables only?
ALEFT JOIN
BRIGHT JOIN
CINNER JOIN
DFULL JOIN
Check answerHide answer
Correct answer: Option 3 — INNER JOIN
Short Answer Questions5 questions
Q63 Marks
Differentiate between WHERE and HAVING clauses in SQL.
Q73 Marks
Differentiate between DELETE and DROP commands in SQL.
Q83 Marks
Write a SQL query to display name and salary of all employees from the table employee.
Q93 Marks
Explain the use of GROUP BY clause with one example.
Q103 Marks
Write a SQL query to find the average salary of employees department-wise.
Long Answer Questions6 questions
Q116 Marks
Write SQL queries to: (a) create a table employee (b) insert 3 rows (c) display employees with salary > 30000 (d) update salary of one employee (e) delete one employee.
Q126 Marks
Discuss any five SQL aggregate functions with examples.
Q136 Marks
Differentiate between INNER JOIN LEFT JOIN and RIGHT JOIN with one example each.
Q146 Marks
Discuss SQL constraints — PRIMARY KEY FOREIGN KEY UNIQUE NOT NULL CHECK DEFAULT — with examples.
Q156 Marks
Write SQL queries to: (a) sort employees by salary descending (b) display employees of HR department (c) count employees per department (d) display top 3 highest-paid employees.
Q166 Marks
Differentiate between primary key and foreign key in tabular form.
Assertion–Reason Questions5 questions
Q171 Mark
Assertion (A): SELECT is a DQL command.
Reason (R): It retrieves 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.
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 modify the structure of the 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: JOIN combines rows from two or more tables.
Statement 2: The join is based on a related column.
Show answerHide answer
Correct answer: Option 1 —
Both statements are true.
Q261 Mark
Statement 1: A NULL value represents missing data.
Statement 2: It is different from zero or empty string.
Show answerHide answer
Correct answer: Option 1 —
Both statements are true.
Case Study / Passage Questions1 question
Q273 Marks
A retail manager has a sales table with columns sale_id product qty price date. He wants SQL queries to (1) compute total revenue (2) find the best-selling product (3) compute monthly revenue (4) list sales above ₹10000.
Which command retrieves data?
ASELECT
BFROM
CWHERE
DCOUNT
Which clause groups rows for aggregation?
AWHERE
BHAVING
CGROUP BY
DORDER BY
Write SQL queries for all four manager requirements.
Show answersHide answers
1. Option 1 — SELECT
2. Option 3 — GROUP BY
3. (1) SELECT SUM(qty * price) AS revenue FROM sales. (2) SELECT product SUM(qty) FROM sales GROUP BY product ORDER BY SUM(qty) DESC LIMIT 1. (3) SELECT MONTH(date) AS m SUM(qty * price) FROM sales GROUP BY m. (4) SELECT * FROM sales WHERE qty * price > 10000. Aggregate functions (SUM AVG MAX) are typically used with GROUP BY. WHERE filters rows; HAVING filters groups.
Table-Based Questions4 questions
Q285 Marks
Match each database key with its definition.
Key
Definition
Primary
?
Candidate
?
Alternate
?
Foreign
?
Composite
?
Q296 Marks
For sample employee table compute each SQL query result.
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
Q305 Marks
Match each SQL clause with its purpose.
Clause
Purpose
SELECT
?
WHERE
?
GROUP BY
?
HAVING
?
ORDER BY
?
LIMIT
?
Q315 Marks
Match each SQL command type with its commands.
Type
Commands
DDL
?
DML
?
DQL
?
DCL
?
TCL
?
Picture-Based Questions3 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 and FULL JOINs with examples.
Show answersHide answers
1. Option 1 — INNER JOIN
2. Option 2 — LEFT
3. INNER JOIN returns only matching rows. LEFT JOIN returns all rows from left + matching from right (NULL for non-matches). RIGHT JOIN is the mirror. FULL JOIN returns all rows from both. JOINs combine rows based on a related column (typically primary-foreign key relationship).
Q333 Marks
Study the sample employee table and answer:
The maximum salary in the table is:
A35000
B40000
C60000
D225000
Which employees are from IT department?
AAnil
BGeeta
CBoth Anil and Geeta
DHari
Write SQL queries to find: (a) employees with salary > 40000 (b) count of employees per department.
Show answersHide answers
1. Option 3 — 60000
2. Option 3 — Both Anil and Geeta
3. The employee table has 5 rows and 4 columns. SELECT * FROM employee WHERE salary > 40000 returns Anil and Geeta. SELECT dept COUNT(*) FROM employee GROUP BY dept gives 3 groups: IT (2) HR (2) Sales (1). SELECT MAX(salary) returns 60000. WHERE filters rows; GROUP BY groups them; aggregate functions summarise.
Q343 Marks
Study the SQL aggregate function results and answer:
Which function returns the total number of rows?
ACOUNT(*)
BSUM(salary)
CAVG(salary)
DMAX(salary)
Which function returns the average?
ASUM
BAVG
CMIN
DMAX
Discuss any five SQL aggregate functions with one example each.
Show answersHide answers
1. Option 1 — COUNT(*)
2. Option 2 — AVG
3. SQL aggregate functions summarise data: COUNT counts rows; SUM totals numeric column; AVG averages it; MAX returns the maximum; MIN returns the minimum. They are typically used with GROUP BY to compute aggregates per group. COUNT(*) includes NULLs while COUNT(column) ignores NULLs.
