MySQL Queries — Study Notes

1. Introduction

Writing queries is the heart of working with a database. A query is a question asked to the database, written in SQL, and the database responds with the requested data. The most important query in MySQL is SELECT, which retrieves data from tables. Along with SELECT, the WHERE clause filters rows, aggregate functions summarise data, and operators compare values. Together these form the query toolkit that Class 11 students must master.

This chapter builds directly on the previous chapter's table creation. We assume a table named student exists with columns roll, name, marks, and city. We will learn how to retrieve all columns or selected columns, how to filter records with WHERE and its operators (=, >, <, >=, <=, <>, LIKE, IN, BETWEEN), how to sort results with ORDER BY, and how to compute summaries using aggregate functions like COUNT, SUM, AVG, MAX, and MIN along with GROUP BY. We will also use aliases, DISTINCT, and logical operators to make queries more powerful and readable.

Every query question in the examination can be answered by following a simple recipe: identify the table, choose the columns, apply the condition, and decide the ordering. Mastery of this pattern will make even complex questions straightforward.

2. The SELECT Statement

The basic SELECT statement retrieves data from a table. Use * to select all columns.

SELECT * FROM student;

This displays all rows and all columns of the student table. To select specific columns, list them after SELECT:

SELECT name, marks FROM student;

To select a column only once even if it repeats, use DISTINCT:

SELECT DISTINCT city FROM student;

This returns each city only once, removing duplicates.

3. Aliases

An alias gives a temporary name to a column or table, making output more readable. The AS keyword is used for this purpose.

SELECT name AS student_name, marks AS obtained_marks FROM student;

The output columns will be headed student_name and obtained_marks instead of name and marks.

4. Filtering with WHERE

The WHERE clause filters rows based on a condition.

SELECT name, marks FROM student WHERE marks > 80;

This returns the name and marks of only those students whose marks are greater than 80.

4.1 Relational Operators

The relational operators used in WHERE are = (equal to), <> or != (not equal to), >, <, >=, <=.

SELECT * FROM student WHERE city = 'Delhi';
SELECT * FROM student WHERE marks >= 90;

4.2 Logical Operators

• AND: Both conditions must be true.
• OR: At least one condition must be true.
• NOT: Reverses the condition.

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

4.3 BETWEEN

BETWEEN selects values in a given inclusive range.

SELECT name, marks FROM student WHERE marks BETWEEN 70 AND 90;

This includes marks 70 and 90 as well as everything between them.

4.4 IN

IN matches a value against a list of values, which is shorter than many OR conditions.

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

4.5 LIKE and Wildcards

LIKE is used for pattern matching with two wildcards: % matches any number of characters, and _ matches exactly one character.

SELECT * FROM student WHERE name LIKE 'A%';
SELECT * FROM student WHERE name LIKE '_a%';

• 'A%' matches names starting with 'A'.
• '_a%' matches names whose second character is 'a'.

5. Sorting with ORDER BY

ORDER BY sorts the result in ascending order by default. Use DESC for descending order.

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

Multiple sort keys can be given, separated by commas. Sorting can also be done on the basis of column aliases.

6. Aggregate Functions

Aggregate functions summarise a whole column into a single value.

• COUNT(*): Counts the number of rows.
• COUNT(column): Counts non-NULL values in a column.
• SUM(column): Total of a numeric column.
• AVG(column): Average of a numeric column.
• MAX(column): Maximum value.
• MIN(column): Minimum value.

SELECT COUNT(*) FROM student;
SELECT AVG(marks) FROM student;
SELECT MAX(marks), MIN(marks) FROM student;
SELECT SUM(marks) FROM student WHERE city = 'Delhi';

7. Grouping with GROUP BY

GROUP BY groups rows that have the same value in the given column, so that aggregate functions can be applied per group.

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

The first query shows the average marks of students in each city, and the second shows how many students live in each city.

HAVING Clause

To filter groups, HAVING is used (whereas WHERE filters individual rows before grouping).

SELECT city, AVG(marks) FROM student GROUP BY city HAVING AVG(marks) > 80;

8. Modifying Data

• INSERT INTO: Adds new rows.

INSERT INTO student (roll, name, marks, city) VALUES (5, 'Divya', 91, 'Pune');

• UPDATE: Modifies existing rows.

UPDATE student SET marks = 95 WHERE name = 'Aarav';

• DELETE: Removes rows.

DELETE FROM student WHERE roll = 3;

Quick Revision Tables

Table 1: SELECT Clause Summary

Table 2: Operators in WHERE

Mind Map

Important Diagrams (SVG)

Diagram 1: SQL Query Execution Order

Diagram 2: WHERE Clause with Operators

Common Mistakes

1. Writing WHERE after ORDER BY; the correct order is WHERE then GROUP BY then ORDER BY.
2. Using HAVING instead of WHERE for row-level conditions; HAVING is only for filtered groups.
3. Forgetting quotes around string values in conditions, e.g., writing city = Delhi instead of city = 'Delhi'.
4. Misunderstanding % and _; % matches any number of characters while _ matches exactly one.
5. Assuming BETWEEN excludes the end values; it is inclusive.
6. Using AVG or SUM on non-numeric columns, which produces an error.
7. Forgetting that COUNT(*) counts rows including NULLs, while COUNT(column) counts only non-NULL values.
8. Writing UPDATE or DELETE without a WHERE clause, which affects all rows.

Exam Tips

1. Learn the query execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
2. Practise writing queries for each of these patterns: select columns, filter, sort, count, average, group.
3. Memorise the wildcards: % for any number of characters and _ for a single character.
4. Remember BETWEEN is inclusive on both ends.
5. Always quote string values in WHERE conditions.
6. Be careful with COUNT(*) versus COUNT(column) when NULLs may be present.

Conclusion

The SELECT query is the fundamental tool for extracting information from a relational database. By combining column selection with the WHERE clause and its operators, we can precisely choose the rows we need. Aggregate functions summarise columns, GROUP BY analyses data category by category, and ORDER BY arranges results meaningfully. Inserting, updating, and deleting data keeps the database current. The structured recipe of FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY, once memorised, answers almost every query question in the examination. These SQL skills form the bridge between stored data and the information that supports real decisions.

Worked Example: Building a Query Step by Step

Consider the question: list the names and marks of students from Delhi who scored more than 75, arranged from highest to lowest. We follow the standard recipe. The table is student, so the FROM clause names it. The columns we need are name and marks, so they appear after SELECT. Two conditions must hold together: the city must be Delhi and the marks must be greater than 75, so we join them with AND inside the WHERE clause. Finally, since the highest marks must come first, we add ORDER BY marks DESC. The complete query is SELECT name, marks FROM student WHERE city equal to 'Delhi' AND marks greater than 75 ORDER BY marks DESC. Reading the English question, translating it clause by clause, and then assembling the query is the reliable method that works for almost every question in the examination.

A second example shows the power of grouping. To find the average marks of students in each city, we write SELECT city, AVG(marks) FROM student GROUP BY city. MySQL first separates the rows by the distinct values of the city column, and then computes AVG(marks) within each group separately, so every city appears exactly once with its own average. If we also want to keep only those cities whose average exceeds 80, we append HAVING AVG(marks) greater than 80. It is essential to use HAVING here rather than WHERE, because WHERE runs before grouping and cannot see the group averages, whereas HAVING filters the groups after they have been formed. Understanding this distinction between row-level and group-level filtering is the key to many marks, and it also explains why a column that is not grouped or aggregated cannot appear freely in the SELECT list of a grouped query.