SQL Queries & Joins Notes & Questions | Class 12 Computer Science | Drishti OS

What you'll learn

Writing SELECT queries with filtering, sorting, and grouping
Using aggregate functions (COUNT, SUM, AVG, MIN, MAX)
The difference between INNER, LEFT, RIGHT, and FULL JOINs
Writing subqueries and nested SELECT statements
Using DISTINCT to eliminate duplicate results

Key concepts

Basic SELECT Syntax

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC|DESC;

Clauses and Their Roles

Clause	Purpose	Example
SELECT	Choose which columns to return	`SELECT name, age`
FROM	Specify the source table	`FROM students`
WHERE	Filter rows before grouping	`WHERE age > 16`
GROUP BY	Group rows that share a column value	`GROUP BY class`
HAVING	Filter groups after aggregation	`HAVING COUNT(*) > 5`
ORDER BY	Sort results (ASC default, DESC optional)	`ORDER BY marks DESC`
DISTINCT	Remove duplicate rows from output	`SELECT DISTINCT city`

WHERE vs HAVING: WHERE filters individual rows; HAVING filters aggregated groups.

Aggregate Functions

Function	Returns
COUNT(*)	Number of rows
SUM(col)	Total of numeric column
AVG(col)	Mean value
MIN(col)	Smallest value
MAX(col)	Largest value

Example:

SELECT class, COUNT(*) AS total_students, AVG(marks) AS avg_marks
FROM students
GROUP BY class
HAVING AVG(marks) > 60
ORDER BY avg_marks DESC;

JOINs Explained

A JOIN combines rows from two tables based on a related column.

JOIN Type	Returns
INNER JOIN	Only rows with matching values in both tables
LEFT JOIN	All rows from the left table; NULL for non-matches on the right
RIGHT JOIN	All rows from the right table; NULL for non-matches on the left
FULL JOIN	All rows from both tables; NULL where no match exists

Sample Tables:

Students

student_id	name	dept_id
1	Anika	10
2	Rohan	20
3	Priya	NULL

Departments

dept_id	dept_name
10	Science
20	Commerce
30	Arts

INNER JOIN (only matched rows):

SELECT name, dept_name
FROM students
INNER JOIN departments ON students.dept_id = departments.dept_id;
-- Returns: Anika/Science, Rohan/Commerce

LEFT JOIN (all students, even without a department):

SELECT name, dept_name
FROM students
LEFT JOIN departments ON students.dept_id = departments.dept_id;
-- Returns: Anika/Science, Rohan/Commerce, Priya/NULL

RIGHT JOIN (all departments, even without students):

-- Returns: Anika/Science, Rohan/Commerce, NULL/Arts

FULL JOIN (everyone, matched or not):

-- Returns: Anika/Science, Rohan/Commerce, Priya/NULL, NULL/Arts

Subqueries

A subquery is a SELECT statement nested inside another query.

-- Find students who scored above the class average
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

Subqueries can appear in SELECT, FROM, or WHERE clauses.

DISTINCT

SELECT DISTINCT city FROM students;
-- Returns each city only once, even if many students share it

Quick check

Write a query to find the name and marks of the top 3 students from a table called results, sorted by marks in descending order.
What is the difference between WHERE and HAVING? Give one example where HAVING is necessary and WHERE cannot be used instead.
A teachers table and a courses table share a teacher_id column. Write an INNER JOIN query to display each teacher's name alongside their course name.
Using the students table with columns (id, name, class, marks), write a query to display the average marks per class, but only for classes where the average exceeds 70.
What does a LEFT JOIN return that an INNER JOIN does not? Describe a real-world scenario where a LEFT JOIN is the correct choice.

SQL Queries & Joins