MySQL Queries — Study Notes

1. Introduction

While the revision tour built the structure of databases and tables, the real power of SQL lies in retrieving data in exactly the form needed. MySQL Queries is the chapter where SELECT statements are developed into sophisticated tools for filtering, sorting, grouping, and combining data. Whether we need the names of students scoring above 80, the average marks per city, or the details of employees whose names begin with 'A', every requirement translates into a SELECT statement built from a small set of clauses.

A SELECT statement is assembled from several optional clauses. The WHERE clause filters rows before any grouping takes place, ORDER BY sorts the result, GROUP BY clusters rows that share the same value in a column, and HAVING filters the groups that were formed. Joins bring data from two or more tables together, and subqueries allow a query to use the result of another query as a value. Each clause appears in a strict order that MySQL follows when executing the query.

This chapter teaches the complete construction of SELECT queries, clause by clause. We begin with the operators used inside conditions, move on to sorting and grouping, then to joins and subqueries. A large portion of the theory paper and the practical examination asks for queries of exactly these types, so every example here should be typed and executed on a real MySQL server until the syntax becomes second nature.

2. The WHERE Clause and Operators

The WHERE clause filters rows using a condition. The operators available fall into several families.

2.1 Relational Operators

SELECT * FROM student WHERE marks > 80;
SELECT * FROM student WHERE roll = 102;
SELECT * FROM student WHERE marks BETWEEN 70 AND 90;

• =, >, <, >=, <=, <> compare values.
• BETWEEN a AND b includes both endpoints a and b.

2.2 Logical Operators

SELECT * FROM student WHERE city = 'Delhi' AND marks > 85;
SELECT * FROM student WHERE city = 'Mumbai' OR city = 'Kolkata';
SELECT * FROM student WHERE NOT city = 'Delhi';

• AND requires both conditions to be true.
• OR requires at least one condition to be true.
• NOT reverses a condition.

2.3 The IN Operator

IN is a shorthand for multiple OR conditions:

SELECT * FROM student WHERE city IN ('Delhi', 'Mumbai', 'Pune');
SELECT * FROM student WHERE city NOT IN ('Delhi', 'Mumbai');

2.4 The LIKE Operator

LIKE matches a pattern using two wildcard characters: % matches any sequence of characters and _ matches exactly one character.

SELECT * FROM student WHERE name LIKE 'A%';
SELECT * FROM student WHERE name LIKE '%an%';
SELECT * FROM student WHERE name LIKE 'R___';
SELECT * FROM student WHERE name NOT LIKE 'A%';

• 'A%' matches names starting with A.
• '%an%' matches names containing 'an' anywhere.
• 'R___' matches names starting with R and having exactly three more characters.

2.5 IS NULL and IS NOT NULL

SELECT * FROM student WHERE email IS NULL;
SELECT * FROM student WHERE email IS NOT NULL;

IS NULL tests for missing values, because = NULL always evaluates as unknown.

3. ORDER BY

ORDER BY sorts the result rows in ascending or descending order:

SELECT name, marks FROM student ORDER BY marks ASC;
SELECT name, marks FROM student ORDER BY marks DESC;
SELECT name, marks FROM student ORDER BY marks DESC, roll ASC;

• The default order is ascending, so ASC may be omitted.
• Sorting can be on multiple columns, applied left to right.
• ORDER BY can reference a column position as well: ORDER BY 2 sorts by the second selected column.

4. Aggregate Functions

Aggregate functions summarise a whole column into a single value:

SELECT COUNT(*) FROM student;
SELECT MAX(marks), MIN(marks) FROM student;
SELECT SUM(marks), AVG(marks) FROM student;

• COUNT(*) counts the number of rows.
• COUNT(col) counts only non-NULL values of that column.
• SUM(col), AVG(col), MAX(col), MIN(col) compute the total, average, largest, and smallest values.
• Aggregate functions ignore NULL values except for COUNT(*).

5. GROUP BY

GROUP BY collects all rows having the same value in a column into a single group, so that an aggregate can be computed for each group:

SELECT city, COUNT(*) FROM student GROUP BY city;
SELECT city, AVG(marks) FROM student GROUP BY city;

The first query gives the number of students in each city, and the second gives the average marks per city. Any column that appears in the SELECT list alongside an aggregate should also appear in the GROUP BY clause.

6. HAVING

HAVING filters the groups created by GROUP BY, in the same way WHERE filters individual rows:

SELECT city, AVG(marks) FROM student GROUP BY city HAVING AVG(marks) > 80;
SELECT city, COUNT(*) FROM student GROUP BY city HAVING COUNT(*) >= 2;

The key distinction is that WHERE cannot use aggregate functions, while HAVING can. This is one of the most commonly tested points in the exam.

7. The Complete SELECT Statement Order

The clauses of a SELECT statement must follow a fixed order:

SELECT column_list
FROM table
WHERE condition
GROUP BY columns
HAVING condition
ORDER BY columns
LIMIT n;

MySQL evaluates the query conceptually in the order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Therefore WHERE runs before grouping and HAVING runs after grouping.

SELECT city, COUNT(*) AS total
FROM student
WHERE marks > 50
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY total DESC;

8. Joins

A join combines rows from two or more tables based on a related column.

8.1 Inner Join

An inner join returns only rows that have matching values in both tables:

SELECT student.roll, student.name, fee.amount
FROM student
INNER JOIN fee ON student.roll = fee.roll;

8.2 Left and Right Join

A left join returns all rows of the left table and matching rows of the right table; unmatched right-side columns appear as NULL. A right join is the mirror image:

SELECT student.roll, student.name, fee.amount
FROM student
LEFT JOIN fee ON student.roll = fee.roll;

The ON clause specifies the join condition, and table names prefixed to columns remove ambiguity when both tables share a column name.

9. Subqueries

A subquery is a SELECT statement nested inside another query. It can appear in the WHERE clause as a value:

SELECT name FROM student WHERE roll IN (SELECT roll FROM fee WHERE amount > 4000);
SELECT name, marks FROM student
WHERE marks > (SELECT AVG(marks) FROM student);

The inner query executes first and its result is used by the outer query. The operator IN is used when the subquery returns many values, while a scalar subquery returning a single value can be compared directly with relational operators.

10. Aliases

Aliases give a temporary name to a column or table:

SELECT name AS student_name, marks AS score FROM student;
SELECT s.name, f.amount FROM student AS s JOIN fee AS f ON s.roll = f.roll;

The AS keyword is optional, so SELECT name student_name also works, but using AS improves readability.

Quick Revision Tables

Table 1: LIKE Wildcard Patterns

Table 2: WHERE versus HAVING

Mind Map

Important Diagrams (SVG)

Diagram 1: Execution Order of SELECT Clauses

Diagram 2: Inner Join between student and fee

Common Mistakes

1. Using = NULL in a condition; the correct test is IS NULL, since = NULL never evaluates to true.
2. Using aggregate functions in the WHERE clause, which MySQL rejects; such conditions belong in HAVING.
3. Placing HAVING without a GROUP BY, which is syntactically allowed but usually a logical error.
4. Forgetting the single quotes around string values in conditions, e.g. writing city = Delhi.
5. Misusing LIKE: LIKE 'A%' matches strings starting with A, but LIKE '%A%' matches strings containing A anywhere.
6. Writing ORDER BY before WHERE, which is invalid because WHERE must follow FROM.
7. Omitting the ON clause in a join, producing a cartesian product of all row combinations.
8. Selecting a non-aggregated column in a GROUP BY query that is not part of the GROUP BY clause, which gives unpredictable results.
9. Confusing COUNT(*) with COUNT(col): COUNT(*) includes rows with NULLs, while COUNT(col) ignores them.

Exam Tips

1. Practise writing queries for common patterns: top marks, per-city averages, names with patterns, and grouped counts.
2. Memorise the full clause order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
3. Know by heart that BETWEEN includes both endpoints, while LIKE with '%' and '_' has specific meanings.
4. Be able to write both an inner join and a left join for the same two tables and explain the difference in results.
5. Practise subquery questions where the inner query returns a single value versus many values.
6. Remember that aggregate functions ignore NULL values, so AVG is computed over non-null entries only.
7. Write table.column names in joins whenever both tables share a column, to avoid ambiguity errors.

Conclusion

MySQL Queries turns the basic SELECT into a complete data-retrieval tool. The WHERE clause filters individual rows using relational, logical, IN, LIKE, and IS NULL operators; ORDER BY sorts the output; aggregate functions such as COUNT, SUM, AVG, MAX, and MIN summarise columns; and GROUP BY with HAVING computes and filters per-group statistics. Joins combine related tables, and subqueries let one query feed another. Because every clause occupies a fixed position in the statement, learning the order of execution is as important as learning the syntax. Together with the functions covered in the next chapter, these queries form the heart of the SQL portion of the Class 12 examination.