You know **what** a relational database is — now let's actually talk to one! This chapter answers the BIG questions: **How do you write SQL queries? What data types does MySQL support? How do you filter, sort, and calculate on the fly?** Master this chapter and you'll be writing real SQL like a pro! 🚀
> [!TIP]
> **How to use these notes:** This chapter is **command-heavy** — type out every query yourself instead of just reading it! Board Exam Tips appear throughout — don't skip them! Focus especially on **Sections 10.5.13 to 10.5.19 (WHERE clause conditions)** and **10.7 (Aggregate Functions)** — examiner favourites every year!
---
## 10.1 🏁 Introduction
In the previous chapter, we learnt about the **Relational Model** and **MySQL** as a DBMS. Now it's time to actually **write SQL** — the language used to create databases, insert data, and most importantly, **query (retrieve) data** based on conditions.
```mermaid
graph LR
DB["🗄️ Database Created"]
DATA["📥 Data Inserted"]
QUERY["🔍 SQL Query Written"]
RESULT["📊 Result Set Returned"]
DB --> DATA --> QUERY --> RESULT
style QUERY fill:#2196F3,color:#fff
style RESULT fill:#4CAF50,color:#fff
```
> Think of SQL as a conversation: you **ask** the database a precise question ("show me employees earning more than ₹50,000"), and it **answers** with exactly the rows that match — nothing more, nothing less.
---
## 10.2 🧩 Some MySQL SQL Elements
Before writing queries, you need to know the basic **building blocks** of SQL — the raw materials every statement is made of.
### 10.2.1 Literals 🔢
A **Literal** is a fixed, constant value written directly into an SQL statement — exactly as it appears, with no calculation needed.
::: grid
::: card 🔢 | Numeric Literal | A plain number, written without quotes | 25, 99.5, -10
::: card 📝 | String Literal | Text enclosed in single quotes | 'Computer Science', 'Priya'
::: card 📅 | Date Literal | A date written as a quoted string in a fixed format | '2026-06-27'
::: card ✅ | Boolean Literal | TRUE or FALSE values | TRUE, FALSE
:::
> [!NOTE]
> **Quotes Matter! 🧠**
> Numbers are written **without** quotes (`25`), but text and dates are always written **inside single quotes** (`'Priya'`, `'2026-06-27'`). Forgetting quotes around text is one of the most common SQL syntax errors!
---
### 10.2.2 Data Types 📦
Every column in a MySQL table must be assigned a **data type**, which decides what kind of value it can hold and how much storage it uses.
**Common MySQL Data Types:**
| Category | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| **Numeric** | `INT` | Whole numbers | 1, 100, -50 |
| **Numeric** | `DECIMAL(m,d)` | Exact decimal numbers (m = total digits, d = digits after decimal) | `DECIMAL(6,2)` → 1234.56 |
| **Numeric** | `FLOAT` | Approximate decimal numbers | 3.14159 |
| **String** | `CHAR(n)` | Fixed-length string of exactly n characters | `CHAR(5)` → 'India' |
| **String** | `VARCHAR(n)` | Variable-length string, up to n characters | `VARCHAR(30)` → 'Priya Sharma' |
| **String** | `TEXT` | Large blocks of text | Long descriptions, comments |
| **Date/Time** | `DATE` | Stores date only | '2026-06-27' |
| **Date/Time** | `TIME` | Stores time only | '14:30:00' |
| **Date/Time** | `DATETIME` | Stores date and time together | '2026-06-27 14:30:00' |
| **Boolean** | `BOOLEAN` | TRUE or FALSE (stored internally as TINYINT) | TRUE, FALSE |
::: grid
::: card ⚖️ | CHAR vs VARCHAR | CHAR always uses fixed space; VARCHAR uses only as much space as needed | CHAR(10) for 'Hi' still uses 10 chars; VARCHAR(10) uses only 2
::: card 💰 | DECIMAL vs FLOAT | DECIMAL is exact (use for money!); FLOAT is approximate (rounding errors possible) | Always use DECIMAL for salary/price fields
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between CHAR and VARCHAR." — **2-mark** question asked frequently!
> Answer: **CHAR(n)** is a **fixed-length** string — it always reserves *n* characters of storage, padding with spaces if the actual value is shorter. **VARCHAR(n)** is a **variable-length** string — it uses only as much storage as the actual data needs, up to a maximum of *n* characters.
---
### 10.2.3 Null Values ❓
A **NULL** value represents **missing, unknown, or inapplicable data** — it is NOT the same as zero, blank space, or an empty string!
```mermaid
graph LR
A["0 (Zero)\nA known numeric value"]
B["'' (Empty String)\nA known, empty text value"]
C["NULL\nValue is unknown / not entered\nat all!"]
style A fill:#4CAF50,color:#fff
style B fill:#2196F3,color:#fff
style C fill:#F44336,color:#fff
```
| Concept | Meaning | Example |
| :--- | :--- | :--- |
| **0** | A real, known numeric value | Marks scored = 0 |
| **'' (empty string)** | A real, known but empty text value | Middle name field left empty on purpose |
| **NULL** | Value is missing/unknown — nothing was stored at all | Phone number not provided yet |
> [!WARNING]
> **Common Mistake**
> You **cannot** test for NULL using the `=` operator! Writing `WHERE Marks = NULL` will **never** return any rows, even if NULL values exist. You must always use `IS NULL` or `IS NOT NULL` instead (covered in Section 10.5.19).
---
### 10.2.4 Comments 💬
**Comments** are notes written inside SQL code that MySQL **ignores during execution** — used purely to explain code to humans reading it.
**Comment Styles in MySQL:**
| Style | Syntax | Example |
| :--- | :--- | :--- |
| **Single-line** | `-- comment text` | `-- This query fetches all employees` |
| **Single-line (alt)** | `# comment text` | `# Filter by department` |
| **Multi-line** | `/* comment text */` | `/* This is a multi-line comment */` |
```sql
-- query.sql
-- Fetch all employees from the Sales department
SELECT * FROM Employee
WHERE Dept = 'Sales'; # inline comment also works
```
> [!NOTE]
> **Comments Cost Nothing! 🧠**
> MySQL completely skips comments during execution — they exist purely for documentation. Good SQL scripts always include comments explaining *why* a query does something, especially for complex conditions!
---
## 10.3 📐 SQL Command Syntax
Every SQL statement follows a consistent structural pattern. Understanding this general shape makes learning each new command far easier.
**General SQL Statement Anatomy:**
```
COMMAND column_list FROM table_name [WHERE condition] [ORDER BY column];
↑ ↑ ↑ ↑ ↑
keyword what to select which table filter rows sort results
```
::: grid
::: card 🔤 | Case Sensitivity | SQL keywords are NOT case-sensitive (SELECT = select); but data values ARE case-sensitive depending on the table's collation | SELECT and select work identically
::: card 🔚 | Statement Terminator | Every SQL statement must end with a semicolon | SELECT * FROM Employee;
::: card 🔲 | Identifiers | Table and column names should avoid spaces and special characters | Use Emp_Name not Emp Name
::: card 📏 | Whitespace | Extra spaces, tabs, and line breaks are ignored by SQL | You can format long queries across multiple lines for readability
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "Is SQL case-sensitive?" — **1-mark** question.
> Answer: SQL **keywords** (SELECT, WHERE, FROM) are **not case-sensitive**. However, **data values** stored in string columns may be case-sensitive depending on the database's collation settings (MySQL is usually case-insensitive by default for string comparisons).
---
## 10.4 🗄️ Sample Database
To practice queries throughout this chapter, we'll use one consistent sample table — **Employee** — exactly as most CBSE textbooks do.
**Table: Employee**
| EmpId | Name | Dept | Salary | DOJ | City |
| :--- | :--- | :--- | :--- | :--- | :--- |
| 101 | Priya Sharma | Sales | 45000.00 | 2022-03-15 | Delhi |
| 102 | Arjun Kumar | IT | 62000.00 | 2021-07-01 | Mumbai |
| 103 | Sita Roy | HR | 38000.00 | 2023-01-20 | Delhi |
| 104 | Rohan Das | IT | 71000.00 | 2020-11-05 | Bangalore |
| 105 | Anjali Gupta | Sales | NULL | 2023-06-10 | Mumbai |
| 106 | Vikram Singh | HR | 41000.00 | 2022-09-25 | Delhi |
**Table Creation Command Used:**
```sql
-- employee_table.sql
CREATE TABLE Employee (
EmpId INT PRIMARY KEY,
Name VARCHAR(30),
Dept VARCHAR(20),
Salary DECIMAL(10,2),
DOJ DATE,
City VARCHAR(20)
);
```
> [!TIP]
> **Keep This Table Handy! 🎵**
> Every example query in this chapter uses this exact **Employee** table. Try running each query yourself against this data — predicting the output *before* running it is the best way to build real SQL intuition!
---
## 10.5 🔍 Making Simple Queries
This is the heart of the chapter — learning to write `SELECT` statements that retrieve exactly the data you want, formatted exactly how you want it.
### 10.5.1 Creating and Accessing Database 🏗️
Before querying any table, you must first **create** (or select) the database that contains it.
```sql
-- setup.sql
CREATE DATABASE CompanyDB; -- Create a new database
USE CompanyDB; -- Switch to / access that database
SHOW DATABASES; -- List all databases on the server
SHOW TABLES; -- List all tables in the current database
```
| Command | Purpose |
| :--- | :--- |
| `CREATE DATABASE db_name;` | Creates a new, empty database |
| `USE db_name;` | Selects a database to work within for the current session |
| `SHOW DATABASES;` | Lists all databases available on the server |
| `SHOW TABLES;` | Lists all tables inside the currently selected database |
| `DROP DATABASE db_name;` | Permanently deletes a database and everything inside it |
> [!WARNING]
> **Common Mistake**
> Forgetting `USE database_name;` before running queries causes the error **"No database selected"**. Always switch to the correct database first, every new session!
---
### 10.5.2 The SELECT Command 🔍
**SELECT** is the most-used SQL command — it retrieves data from one or more tables, optionally filtered, sorted, and calculated.
**Basic SELECT Syntax:**
```sql
SELECT column1, column2, ...
FROM table_name;
```
```mermaid
graph LR
SELECT["SELECT\ncolumns to display"]
FROM["FROM\nwhich table"]
WHERE["WHERE\n(optional)\nfilter condition"]
ORDER["ORDER BY\n(optional)\nsort order"]
SELECT --> FROM --> WHERE --> ORDER
style SELECT fill:#2196F3,color:#fff
```
> [!IMPORTANT]
> **Board Exam Tip**
> "Write the general syntax of the SELECT statement." — **1-mark** question.
> Answer: `SELECT column_name(s) FROM table_name [WHERE condition] [ORDER BY column_name];` — the WHERE and ORDER BY clauses are optional.
---
### 10.5.3 Selecting all Columns ⭐
To display **every** column of a table, use the asterisk (`*`) wildcard instead of listing each column name.
```sql
-- select_all.sql
SELECT * FROM Employee;
```
**Output:** All 6 columns (EmpId, Name, Dept, Salary, DOJ, City) for every row are displayed.
::: grid
::: card ⭐ | Asterisk (*) | Means "all columns", in the order they exist in the table | Quick way to preview entire table contents
::: card 📋 | Named Columns | Explicitly listing columns is better practice for real applications | SELECT Name, Salary FROM Employee;
:::
> [!NOTE]
> **`*` is great for exploring, risky for production! 🧠**
> While `SELECT *` is handy for quickly checking table contents, real applications should always **name specific columns** — this avoids fetching unnecessary data and prevents bugs if the table structure changes later (e.g., a new column gets added).
---
### 10.5.4 Reordering Columns in Query Results 🔄
The order of columns in your **SELECT** clause decides the order they appear in the output — and it doesn't have to match the table's actual structure!
```sql
-- reorder_columns.sql
SELECT Name, Salary, EmpId FROM Employee;
```
**Output (columns appear in the order requested, NOT table order):**
| Name | Salary | EmpId |
| :--- | :--- | :--- |
| Priya Sharma | 45000.00 | 101 |
| Arjun Kumar | 62000.00 | 102 |
> [!TIP]
> **You're in Control of the Layout! 🎵**
> SQL doesn't force you to follow the table's original column order. Want Salary displayed before EmpId? Just write it that way in your SELECT list — the database happily rearranges the output for you!
---
### 10.5.5 Eliminating Redundant Data — DISTINCT Keyword 🧹
The **DISTINCT** keyword removes duplicate rows from the result, showing each unique value (or combination of values) only once.
```sql
-- distinct_demo.sql
SELECT DISTINCT Dept FROM Employee;
```
**Output (each department listed only once, even though multiple employees share a department):**
| Dept |
| :--- |
| Sales |
| IT |
| HR |
```mermaid
graph LR
RAW["Without DISTINCT:\nSales, IT, HR, IT, Sales, HR"]
CLEAN["With DISTINCT:\nSales, IT, HR"]
RAW -->|"removes duplicates"| CLEAN
style RAW fill:#F44336,color:#fff
style CLEAN fill:#4CAF50,color:#fff
```
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the use of the DISTINCT keyword?" — **1-mark** question.
> Answer: `DISTINCT` eliminates duplicate rows from the query result, ensuring each unique value (or row combination) appears only once in the output.
---
### 10.5.6 Selecting from all the Rows — ALL Keyword 📋
The **ALL** keyword is the exact opposite of DISTINCT — it explicitly tells MySQL to return **every** matching row, including duplicates. Since this is the *default* behaviour of SELECT, ALL is rarely typed explicitly but good to know conceptually.
```sql
-- all_keyword_demo.sql
SELECT ALL Dept FROM Employee;
```
**Output:** All 6 department values, including repeats (Sales, IT, HR, IT, Sales, HR).
| Keyword | Behaviour | Default? |
| :--- | :--- | :--- |
| **ALL** | Returns every row, including duplicates | ✅ Yes — this is what SELECT does automatically |
| **DISTINCT** | Removes duplicate rows from the result | ❌ No — must be explicitly written |
> [!NOTE]
> **ALL is Implicit! 🧠**
> Writing `SELECT ALL Dept` produces the exact same result as `SELECT Dept` — because returning all rows (with duplicates) is SQL's default behaviour. ALL exists mainly for symmetry with DISTINCT, and is almost never typed in practice.
---
### 10.5.7 Viewing Structure of Table 🏗️
The **DESCRIBE** (or `DESC`) command shows a table's structure — column names, data types, constraints — without displaying any actual data.
```sql
-- describe_table.sql
DESCRIBE Employee;
-- or simply:
DESC Employee;
```
**Output:**
| Field | Type | Null | Key | Default | Extra |
| :--- | :--- | :--- | :--- | :--- | :--- |
| EmpId | int | NO | PRI | NULL | |
| Name | varchar(30) | YES | | NULL | |
| Dept | varchar(20) | YES | | NULL | |
| Salary | decimal(10,2) | YES | | NULL | |
| DOJ | date | YES | | NULL | |
| City | varchar(20) | YES | | NULL | |
> [!IMPORTANT]
> **Board Exam Tip**
> "Which command is used to view the structure of a table without seeing its data?" — **1-mark** question.
> Answer: **`DESCRIBE table_name;`** (or its short form `DESC table_name;`) displays the table's column names, data types, and key constraints.
---
### 10.5.8 How to Perform Simple Calculations ➕
SQL can act like a calculator — you can run arithmetic expressions directly inside a SELECT statement, even **without referencing any table**.
```sql
-- simple_calc.sql
SELECT 25 + 17; -- Output: 42
SELECT 100 / 4; -- Output: 25.0000
SELECT 2 * 8 - 3; -- Output: 13
```
| Operator | Meaning | Example | Result |
| :--- | :--- | :--- | :--- |
| `+` | Addition | `SELECT 5 + 3;` | 8 |
| `-` | Subtraction | `SELECT 10 - 4;` | 6 |
| `*` | Multiplication | `SELECT 6 * 7;` | 42 |
| `/` | Division | `SELECT 20 / 4;` | 5.0000 |
| `%` or `MOD` | Modulus (remainder) | `SELECT 17 % 5;` | 2 |
> [!NOTE]
> **SQL Without FROM? 🧠**
> Notice these queries have no `FROM` clause! MySQL allows SELECT to compute plain expressions directly, without needing any table — useful for quick calculator-style queries.
---
### 10.5.9 Scalar Expressions with Selected Fields 🧮
You can also combine arithmetic with **actual column values** to calculate new results on the fly — these are called **scalar expressions**.
```sql
-- scalar_expression.sql
SELECT Name, Salary, Salary * 12 FROM Employee;
```
**Output:**
| Name | Salary | Salary * 12 |
| :--- | :--- | :--- |
| Priya Sharma | 45000.00 | 540000.00 |
| Arjun Kumar | 62000.00 | 744000.00 |
> A **scalar expression** is any calculation that produces a **single value per row** — here, converting monthly Salary into an Annual figure, calculated fresh for every row.
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to display the Name and Annual Salary (Salary × 12) of all employees." — Common **2-mark** practical question!
> Answer: `SELECT Name, Salary * 12 FROM Employee;`
---
### 10.5.10 Using Column Aliases 🏷️
A **Column Alias** gives a calculated or existing column a temporary, more readable name in the output — using the `AS` keyword.
```sql
-- column_alias.sql
SELECT Name, Salary * 12 AS AnnualSalary
FROM Employee;
```
**Output:**
| Name | AnnualSalary |
| :--- | :--- |
| Priya Sharma | 540000.00 |
| Arjun Kumar | 744000.00 |
::: grid
::: card 🏷️ | AS Keyword | Explicitly renames a column in the output | Salary AS MonthlyPay
::: card 🔡 | AS is Optional | A space alone also works as a shorthand alias | Salary MonthlyPay (same result)
::: card 📏 | Multi-word Aliases | Use quotes if the alias contains a space | Salary AS 'Monthly Pay'
:::
> [!NOTE]
> **Aliases are Temporary! 🧠**
> A column alias only exists for the **duration of that query's output** — it does NOT rename the actual column in the table. Run the query again without the alias, and the original column name reappears.
---
### 10.5.11 Handling Nulls 🕳️
When a column contains NULL and you perform calculations on it, the result is usually **NULL too** — because "unknown plus anything" is still unknown!
```sql
-- handling_nulls.sql
SELECT Name, Salary, Salary * 12 FROM Employee
WHERE Name = 'Anjali Gupta';
```
**Output:**
| Name | Salary | Salary * 12 |
| :--- | :--- | :--- |
| Anjali Gupta | NULL | NULL |
**Using IFNULL() to Substitute a Default Value:**
```sql
-- ifnull_demo.sql
SELECT Name, IFNULL(Salary, 0) AS Salary FROM Employee;
```
| Function | Purpose | Example |
| :--- | :--- | :--- |
| `IFNULL(col, default)` | Replaces NULL with a specified default value | `IFNULL(Salary, 0)` |
| `COALESCE(col1, col2, ...)` | Returns the first non-NULL value from a list | `COALESCE(Salary, Bonus, 0)` |
> [!WARNING]
> **Common Mistake**
> Many students assume NULL behaves like 0 in arithmetic. It does NOT! `NULL + 100` results in `NULL`, not `100`. Always use `IFNULL()` or `COALESCE()` if you need a calculation to treat missing data as a specific default value.
---
### 10.5.12 Putting Text in the Query Output 📝
You can insert **literal text** directly into your query results, mixed alongside actual column data — useful for making output more readable.
```sql
-- text_in_output.sql
SELECT Name, ' earns Rs.', Salary, ' per month' FROM Employee;
```
**Output (conceptually, each row reads like a sentence):**
| Name | (literal) | Salary | (literal) |
| :--- | :--- | :--- | :--- |
| Priya Sharma | earns Rs. | 45000.00 | per month |
> [!TIP]
> **String Literals = Extra Columns! 🎵**
> Every quoted string literal you add to a SELECT list is treated as its own extra "column" in the output — repeated identically for every row. Combine this with `CONCAT()` (covered under String Functions) for cleaner, sentence-like single-column output!
---
### 10.5.13 Selecting Specific Rows — WHERE Clause 🎯
The **WHERE clause** filters rows, returning only those that satisfy a specified condition — this is how you go from "show everything" to "show exactly what I need."
```sql
-- where_demo.sql
SELECT Name, Dept, Salary FROM Employee
WHERE Dept = 'IT';
```
**Output:**
| Name | Dept | Salary |
| :--- | :--- | :--- |
| Arjun Kumar | IT | 62000.00 |
| Rohan Das | IT | 71000.00 |
```mermaid
graph LR
ALL["📊 All 6 Rows"]
WHERE["🔍 WHERE Dept = 'IT'"]
FILTERED["✅ Only Matching Rows\n(Arjun, Rohan)"]
ALL --> WHERE --> FILTERED
style WHERE fill:#FF9800,color:#fff
style FILTERED fill:#4CAF50,color:#fff
```
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the purpose of the WHERE clause?" — **1-mark** question, asked constantly!
> Answer: The **WHERE clause** filters records, returning only the rows that satisfy a specified Boolean condition.
---
### 10.5.14 Relational Operators ⚖️
**Relational Operators** compare values in a WHERE condition, deciding which rows match.
| Operator | Meaning | Example |
| :--- | :--- | :--- |
| `=` | Equal to | `WHERE Dept = 'HR'` |
| `!=` or `<>` | Not equal to | `WHERE Dept != 'HR'` |
| `>` | Greater than | `WHERE Salary > 50000` |
| `<` | Less than | `WHERE Salary < 50000` |
| `>=` | Greater than or equal to | `WHERE Salary >= 45000` |
| `<=` | Less than or equal to | `WHERE Salary <= 45000` |
```sql
-- relational_operators.sql
SELECT Name, Salary FROM Employee
WHERE Salary > 50000;
```
**Output:**
| Name | Salary |
| :--- | :--- |
| Arjun Kumar | 62000.00 |
| Rohan Das | 71000.00 |
---
### 10.5.15 Logical Operators 🔗
**Logical Operators** combine multiple conditions into one WHERE clause, letting you build more precise filters.
| Operator | Meaning | Example |
| :--- | :--- | :--- |
| `AND` | Both conditions must be true | `WHERE Dept = 'IT' AND Salary > 60000` |
| `OR` | At least one condition must be true | `WHERE Dept = 'HR' OR Dept = 'Sales'` |
| `NOT` | Reverses (negates) a condition | `WHERE NOT Dept = 'IT'` |
```sql
-- logical_operators.sql
SELECT Name, Dept, Salary FROM Employee
WHERE Dept = 'IT' AND Salary > 60000;
```
**Output:**
| Name | Dept | Salary |
| :--- | :--- | :--- |
| Arjun Kumar | IT | 62000.00 |
| Rohan Das | IT | 71000.00 |
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between AND and OR logical operators." — **2-mark** question.
> Answer: **AND** returns a row only if **all** combined conditions are true. **OR** returns a row if **at least one** of the combined conditions is true.
---
### 10.5.16 Condition Based on a Range — BETWEEN 📏
The **BETWEEN** operator checks whether a value falls within a specified range (inclusive of both endpoints).
```sql
-- between_demo.sql
SELECT Name, Salary FROM Employee
WHERE Salary BETWEEN 40000 AND 65000;
```
**Output (all employees with Salary from 40000 to 65000, inclusive):**
| Name | Salary |
| :--- | :--- |
| Priya Sharma | 45000.00 |
| Arjun Kumar | 62000.00 |
| Vikram Singh | 41000.00 |
> [!NOTE]
> **BETWEEN is Inclusive! 🧠**
> `Salary BETWEEN 40000 AND 65000` is exactly equivalent to writing `Salary >= 40000 AND Salary <= 65000` — both endpoint values ARE included in the results!
---
### 10.5.17 Condition Based on a List — IN 📋
The **IN** operator checks whether a value matches **any** value in a given list — a clean shorthand for multiple OR conditions.
```sql
-- in_demo.sql
SELECT Name, Dept FROM Employee
WHERE Dept IN ('HR', 'Sales');
```
**Output:**
| Name | Dept |
| :--- | :--- |
| Priya Sharma | Sales |
| Sita Roy | HR |
| Anjali Gupta | Sales |
| Vikram Singh | HR |
> This single query replaces the longer: `WHERE Dept = 'HR' OR Dept = 'Sales'`
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the use of the IN operator? Rewrite using OR." — **2-mark** question.
> Answer: `IN` checks if a value matches any value within a specified list. `WHERE Dept IN ('HR','Sales')` is equivalent to `WHERE Dept = 'HR' OR Dept = 'Sales'`.
---
### 10.5.18 Condition Based on Pattern Matches — LIKE 🔎
The **LIKE** operator searches for a specified **pattern** within a text column, using wildcard characters.
| Wildcard | Meaning | Example |
| :--- | :--- | :--- |
| `%` | Matches any sequence of characters (zero or more) | `'A%'` → matches "Arjun", "Anjali" |
| `_` | Matches exactly one character | `'_ohan'` → matches "Rohan" |
```sql
-- like_demo.sql
SELECT Name FROM Employee
WHERE Name LIKE 'A%';
```
**Output:**
| Name |
| :--- |
| Arjun Kumar |
| Anjali Gupta |
::: grid
::: card 🔤 | Starts With | Pattern % at the end | 'A%' starts with "A"
::: card 🔚 | Ends With | Pattern % at the beginning | '%a' ends with "a"
::: card 🔍 | Contains | Pattern % on both sides | '%an%' contains "an" anywhere
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to display names of employees whose name ends with 'a'." — Common **2-mark** practical question!
> Answer: `SELECT Name FROM Employee WHERE Name LIKE '%a';`
---
### 10.5.19 Searching for NULL — IS NULL 🕳️
Since NULL cannot be tested with `=`, MySQL provides the dedicated **IS NULL** and **IS NOT NULL** operators.
```sql
-- is_null_demo.sql
SELECT Name FROM Employee
WHERE Salary IS NULL;
```
**Output:**
| Name |
| :--- |
| Anjali Gupta |
```sql
-- is_not_null_demo.sql
SELECT Name FROM Employee
WHERE Salary IS NOT NULL;
```
| Operator | Use |
| :--- | :--- |
| `IS NULL` | Finds rows where the column has no value stored |
| `IS NOT NULL` | Finds rows where the column DOES have a stored value |
> [!WARNING]
> **Common Mistake**
> `WHERE Salary = NULL` will **always return zero rows** — even when NULL values exist! NULL represents "unknown," so it can never be considered "equal" to anything, including itself. Always use `IS NULL` instead.
---
### 10.5.20 Operator Precedence 🥇
When multiple operators appear in one expression, MySQL evaluates them in a fixed **order of precedence** — just like the BODMAS/PEMDAS rule in mathematics.
**Simplified Precedence Order (Highest to Lowest):**
```mermaid
graph TD
P1["1️⃣ Parentheses ()"]
P2["2️⃣ Arithmetic: * / percent then + -"]
P3["3️⃣ Comparison: = != less-than greater-than"]
P4["4️⃣ NOT"]
P5["5️⃣ AND"]
P6["6️⃣ OR"]
P1 --> P2 --> P3 --> P4 --> P5 --> P6
style P1 fill:#F44336,color:#fff
style P6 fill:#2196F3,color:#fff
```
```sql
-- precedence_demo.sql
SELECT Name FROM Employee
WHERE Dept = 'IT' OR Dept = 'HR' AND Salary > 40000;
```
Here, `AND` is evaluated **before** `OR` — so this means: *"IT employees (any salary) OR (HR employees with Salary > 40000)"*, NOT "(IT or HR) with Salary > 40000."
> [!TIP]
> **When in Doubt, Use Parentheses! 🎵**
> Relying on memorised precedence rules is risky in exams and in real code. Always use `()` to make your intended logic crystal clear:
> `WHERE Dept = 'IT' OR (Dept = 'HR' AND Salary > 40000)`
---
### 10.5.21 Sorting Results — ORDER BY Clause 📶
The **ORDER BY** clause sorts the final result set by one or more columns, in ascending or descending order.
```sql
-- order_by_demo.sql
SELECT Name, Salary FROM Employee
ORDER BY Salary DESC;
```
**Output (sorted from highest to lowest salary; NULL appears first in MySQL's default sort):**
| Name | Salary |
| :--- | :--- |
| Anjali Gupta | NULL |
| Rohan Das | 71000.00 |
| Arjun Kumar | 62000.00 |
| Priya Sharma | 45000.00 |
| Vikram Singh | 41000.00 |
| Sita Roy | 38000.00 |
| Keyword | Meaning | Default? |
| :--- | :--- | :--- |
| `ASC` | Ascending order (smallest/earliest first) | ✅ Yes — default if not specified |
| `DESC` | Descending order (largest/latest first) | ❌ No — must be written explicitly |
**Sorting by Multiple Columns:**
```sql
SELECT Name, Dept, Salary FROM Employee
ORDER BY Dept ASC, Salary DESC;
```
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to display employee names and departments sorted alphabetically by department." — Common **2-mark** practical question!
> Answer: `SELECT Name, Dept FROM Employee ORDER BY Dept ASC;` (ASC can be omitted since it's the default)
---
## 10.6 ⚙️ MySQL Functions
MySQL provides built-in **functions** to transform, calculate, and format data directly within a query — no application code needed!
### 10.6.1 String Functions 🔤
| Function | Purpose | Example | Result |
| :--- | :--- | :--- | :--- |
| `UPPER(str)` | Converts text to uppercase | `UPPER('priya')` | 'PRIYA' |
| `LOWER(str)` | Converts text to lowercase | `LOWER('PRIYA')` | 'priya' |
| `LENGTH(str)` | Returns the number of characters | `LENGTH('Priya')` | 5 |
| `CONCAT(s1, s2, ...)` | Joins multiple strings together | `CONCAT('Hello, ', Name)` | 'Hello, Priya' |
| `SUBSTRING(str, pos, len)` | Extracts part of a string | `SUBSTRING('Computer', 1, 4)` | 'Comp' |
| `TRIM(str)` | Removes leading/trailing spaces | `TRIM(' Hi ')` | 'Hi' |
```sql
-- string_functions.sql
SELECT CONCAT(Name, ' works in ', Dept) AS Summary
FROM Employee;
```
> [!NOTE]
> **CONCAT Fixes the "Text in Output" Problem! 🧠**
> Remember Section 10.5.12, where adding plain text created multiple separate "columns"? `CONCAT()` solves this elegantly by merging everything into **one clean, readable column**!
---
### 10.6.2 Numeric Functions 🔢
| Function | Purpose | Example | Result |
| :--- | :--- | :--- | :--- |
| `ROUND(num, d)` | Rounds a number to d decimal places | `ROUND(45.678, 2)` | 45.68 |
| `MOD(m, n)` | Returns remainder of m divided by n | `MOD(17, 5)` | 2 |
| `POWER(m, n)` | Raises m to the power n | `POWER(2, 3)` | 8 |
| `ABS(num)` | Returns the absolute (positive) value | `ABS(-15)` | 15 |
| `SQRT(num)` | Returns the square root | `SQRT(64)` | 8 |
| `TRUNCATE(num, d)` | Cuts off decimals without rounding | `TRUNCATE(45.678, 1)` | 45.6 |
```sql
-- numeric_functions.sql
SELECT Name, ROUND(Salary, 0) AS RoundedSalary
FROM Employee;
```
---
### 10.6.3 Date and Time Functions 📅
| Function | Purpose | Example |
| :--- | :--- | :--- |
| `NOW()` | Returns current date and time | `SELECT NOW();` |
| `CURDATE()` | Returns current date only | `SELECT CURDATE();` |
| `CURTIME()` | Returns current time only | `SELECT CURTIME();` |
| `YEAR(date)` | Extracts the year from a date | `YEAR(DOJ)` |
| `MONTH(date)` | Extracts the month from a date | `MONTH(DOJ)` |
| `DATEDIFF(d1, d2)` | Returns number of days between two dates | `DATEDIFF(NOW(), DOJ)` |
```sql
-- date_functions.sql
SELECT Name, YEAR(DOJ) AS JoinYear
FROM Employee;
```
**Output:**
| Name | JoinYear |
| :--- | :--- |
| Priya Sharma | 2022 |
| Arjun Kumar | 2021 |
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to display the names and joining year of all employees." — Common **2-mark** practical question!
> Answer: `SELECT Name, YEAR(DOJ) AS JoinYear FROM Employee;`
---
## 10.7 📊 Aggregate Functions
**Aggregate Functions** perform a calculation across **multiple rows** and return a single summary value — used heavily for reports and statistics.
```mermaid
graph TD
ROWS["📊 Multiple Rows\n(45000, 62000, 38000, 71000, NULL, 41000)"]
AGG["📐 Aggregate Function\n(e.g. SUM)"]
RESULT["1️⃣ Single Value\n257000"]
ROWS --> AGG --> RESULT
style AGG fill:#9C27B0,color:#fff
style RESULT fill:#4CAF50,color:#fff
```
**The Five Core Aggregate Functions:**
| Function | Purpose | Example | Result (on Salary column) |
| :--- | :--- | :--- | :--- |
| `COUNT()` | Counts the number of rows/values | `COUNT(Salary)` | 5 (NULL excluded) |
| `SUM()` | Adds up all values | `SUM(Salary)` | 257000.00 |
| `AVG()` | Calculates the average value | `AVG(Salary)` | 51400.00 |
| `MAX()` | Finds the largest value | `MAX(Salary)` | 71000.00 |
| `MIN()` | Finds the smallest value | `MIN(Salary)` | 38000.00 |
```sql
-- aggregate_functions.sql
SELECT COUNT(*) AS TotalEmployees,
AVG(Salary) AS AverageSalary,
MAX(Salary) AS HighestSalary
FROM Employee;
```
**Output:**
| TotalEmployees | AverageSalary | HighestSalary |
| :--- | :--- | :--- |
| 6 | 51400.00 | 71000.00 |
::: grid
::: card ⚠️ | COUNT(*) vs COUNT(column) | COUNT(*) counts ALL rows, including NULLs; COUNT(column) skips NULL values in that column | COUNT(*) = 6, but COUNT(Salary) = 5
::: card 🧮 | Aggregates Ignore NULL | SUM, AVG, MAX, MIN all silently skip NULL values during calculation | Anjali Gupta's NULL salary doesn't break AVG()
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between COUNT(*) and COUNT(column_name)." — **2-mark** question asked very often!
> Answer: **COUNT(\*)** counts the **total number of rows** in the result, regardless of NULL values. **COUNT(column_name)** counts only the rows where that **specific column has a non-NULL value**.
---
## ⚠️ Common Errors and Misconceptions
| Misconception | Correct Fact |
| :--- | :--- |
| ❌ `WHERE Salary = NULL` finds NULL rows | ✅ Must use `WHERE Salary IS NULL` — `=` never matches NULL |
| ❌ DISTINCT and ALL do different things by default | ✅ ALL is the default behaviour; DISTINCT must be explicitly added to remove duplicates |
| ❌ Column aliases permanently rename the column | ✅ Aliases only apply to that query's output — the actual table is unchanged |
| ❌ NULL behaves like 0 in calculations | ✅ Any arithmetic involving NULL produces NULL, not 0 |
| ❌ AND and OR have equal precedence | ✅ AND is evaluated before OR unless parentheses say otherwise |
| ❌ COUNT(*) and COUNT(column) always give the same result | ✅ COUNT(column) excludes NULLs in that column; COUNT(*) counts every row |
---
## 🔑 Quick Revision — Exam Ready!
**SQL Elements & Syntax — One-Line Summary:**
- **Literal** → Fixed constant value written directly in a query
- **CHAR vs VARCHAR** → CHAR is fixed-length; VARCHAR is variable-length
- **NULL** → Represents missing/unknown data; never equals anything, even itself
- **Comments** → `--`, `#` (single-line) or `/* */` (multi-line); ignored during execution
**WHERE Clause Conditions — Quick Table:**
| Need | Use |
| :--- | :--- |
| Exact match | `=` |
| Range of values | `BETWEEN ... AND ...` |
| Match from a list | `IN (...)` |
| Pattern match | `LIKE` with `%` and `_` |
| Missing value | `IS NULL` / `IS NOT NULL` |
**Functions — Quick Table:**
| Category | Examples |
| :--- | :--- |
| String | UPPER, LOWER, LENGTH, CONCAT, SUBSTRING, TRIM |
| Numeric | ROUND, MOD, POWER, ABS, SQRT |
| Date/Time | NOW, CURDATE, YEAR, MONTH, DATEDIFF |
| Aggregate | COUNT, SUM, AVG, MAX, MIN |
---
## 🎯 Sample Board Exam Questions
### Q1: Very Short Answer [1 mark each]
a) Which keyword is used to remove duplicate rows from a query result?
**→ DISTINCT**
b) Which command is used to view a table's structure?
**→ DESCRIBE (or DESC)**
c) Name the wildcard character used in LIKE to match any number of characters.
**→ % (percent sign)**
d) What does the AVG() function calculate?
**→ The average of all non-NULL values in the specified column**
e) Which operator is used to check for missing values?
**→ IS NULL**
---
### Q2: Short Answer [2 marks]
**Q: Differentiate between BETWEEN and IN operators with examples.**
**BETWEEN** checks if a value falls within a continuous range (inclusive): `WHERE Salary BETWEEN 40000 AND 65000`.
**IN** checks if a value matches any one value from a specific list of discrete values: `WHERE Dept IN ('HR', 'Sales')`.
---
### Q3: Short Answer [2 marks]
**Q: Write SQL queries for the following based on the Employee table:**
a) Display names of employees working in the 'IT' department.
b) Display all employees sorted by Salary in descending order.
**Answers:**
a) `SELECT Name FROM Employee WHERE Dept = 'IT';`
b) `SELECT * FROM Employee ORDER BY Salary DESC;`
---
### Q4: Output Based Question [3 marks]
**Q: Based on the Employee table given, what is the output of the following query?**
```sql
SELECT COUNT(*) FROM Employee
WHERE Salary > 40000;
```
**→ Output: 4 — Priya (45000), Arjun (62000), Rohan (71000), and Vikram (41000) all satisfy Salary > 40000. Sita (38000) and Anjali (NULL) are excluded.**
---
### Q5: Output Based Question [2 marks]
**Q: What will be the output of the following?**
```sql
SELECT Name FROM Employee
WHERE Name LIKE '_o%';
```
**→ Output: "Rohan Das" — the pattern `_o%` matches names where the second character is 'o', regardless of what comes before or after.**
---
## ✏️ Practice Problems
1. Write a query to display Name and Salary of employees who joined after the year 2021.
2. Write a query to display the highest and lowest salary in the Employee table using aggregate functions.
3. Write a query to display employee names in uppercase along with their department.
4. Explain the difference between `WHERE Dept = 'IT' OR Dept = 'HR'` and `WHERE Dept IN ('IT', 'HR')`. Do they return the same result?
5. Write a query to count how many employees do NOT have a salary recorded (NULL).
6. What will `SELECT 10 % 3;` return? Explain the MOD operator's behaviour.
7. Write a query to display all employee names sorted alphabetically, with department names in uppercase.
8. Differentiate between `COUNT(Salary)` and `COUNT(*)` using the sample Employee table — what values would each return?
9. Write a query to display employee names whose City starts with 'D'.
10. Explain why `SELECT * FROM Employee WHERE Salary = NULL;` returns no rows, even though Anjali Gupta has a NULL salary.
Back to List
Calculating...