🔍 Simple Queries in SQL

This is the chapter where you start writing SQL queries against a relational database. It covers the basic elements of MySQL SQL (literals, data types, NULL values and comments), SQL command syntax, and SELECT queries that filter rows with WHERE, sort results, and calculate values with MySQL functions and aggregate functions.

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.

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.

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:

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!

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

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:

-- query.sql
-- Fetch all employees from the Sales department
SELECT * FROM Employee
WHERE Dept = 'Sales';  # inline comment also works

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

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

Table Creation Command Used:

-- 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)
);

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.

-- 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

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:

SELECT column1, column2, ...
FROM table_name;

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

10.5.3 Selecting all Columns ⭐

To display every column of a table, use the asterisk (*) wildcard instead of listing each column name.

-- select_all.sql
SELECT * FROM Employee;

Output: All 6 columns (EmpId, Name, Dept, Salary, DOJ, City) for every row are displayed.

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!

-- reorder_columns.sql
SELECT Name, Salary, EmpId FROM Employee;

Output (columns appear in the order requested, NOT table order):

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.

-- distinct_demo.sql
SELECT DISTINCT Dept FROM Employee;

Output (each department listed only once, even though multiple employees share a department):

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

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.

-- all_keyword_demo.sql
SELECT ALL Dept FROM Employee;

Output: All 6 department values, including repeats (Sales, IT, HR, IT, Sales, HR).

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.

-- describe_table.sql
DESCRIBE Employee;
-- or simply:
DESC Employee;

Output:

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.

-- simple_calc.sql
SELECT 25 + 17;        -- Output: 42
SELECT 100 / 4;        -- Output: 25.0000
SELECT 2 * 8 - 3;      -- Output: 13

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.

-- scalar_expression.sql
SELECT Name, Salary, Salary * 12 FROM Employee;

Output:

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.

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.

-- column_alias.sql
SELECT Name, Salary * 12 AS AnnualSalary
FROM Employee;

Output:

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!

-- handling_nulls.sql
SELECT Name, Salary, Salary * 12 FROM Employee
WHERE Name = 'Anjali Gupta';

Output:

Using IFNULL() to Substitute a Default Value:

-- ifnull_demo.sql
SELECT Name, IFNULL(Salary, 0) AS Salary FROM Employee;

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.

-- text_in_output.sql
SELECT Name, ' earns Rs.', Salary, ' per month' FROM Employee;

Output (conceptually, each row reads like a sentence):

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."

-- where_demo.sql
SELECT Name, Dept, Salary FROM Employee
WHERE Dept = 'IT';

Output:

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

10.5.14 Relational Operators ⚖️

Relational Operators compare values in a WHERE condition, deciding which rows match.

-- relational_operators.sql
SELECT Name, Salary FROM Employee
WHERE Salary > 50000;

Output:

10.5.15 Logical Operators 🔗

Logical Operators combine multiple conditions into one WHERE clause, letting you build more precise filters.

-- logical_operators.sql
SELECT Name, Dept, Salary FROM Employee
WHERE Dept = 'IT' AND Salary > 60000;

Output:

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).

-- between_demo.sql
SELECT Name, Salary FROM Employee
WHERE Salary BETWEEN 40000 AND 65000;

Output (all employees with Salary from 40000 to 65000, inclusive):

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.

-- in_demo.sql
SELECT Name, Dept FROM Employee
WHERE Dept IN ('HR', 'Sales');

Output:

This single query replaces the longer: 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.

-- like_demo.sql
SELECT Name FROM Employee
WHERE Name LIKE 'A%';

Output:

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.

-- is_null_demo.sql
SELECT Name FROM Employee
WHERE Salary IS NULL;

Output:

-- is_not_null_demo.sql
SELECT Name FROM Employee
WHERE Salary IS NOT NULL;

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):

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

-- 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."

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.

-- 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):

Sorting by Multiple Columns:

SELECT Name, Dept, Salary FROM Employee
ORDER BY Dept ASC, Salary DESC;

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 🔤

-- string_functions.sql
SELECT CONCAT(Name, ' works in ', Dept) AS Summary
FROM Employee;

10.6.2 Numeric Functions 🔢

-- numeric_functions.sql
SELECT Name, ROUND(Salary, 0) AS RoundedSalary
FROM Employee;

10.6.3 Date and Time Functions 📅

-- date_functions.sql
SELECT Name, YEAR(DOJ) AS JoinYear
FROM Employee;

Output:

10.7 📊 Aggregate Functions

Aggregate Functions perform a calculation across multiple rows and return a single summary value — used heavily for reports and statistics.

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:

-- aggregate_functions.sql
SELECT COUNT(*) AS TotalEmployees,
       AVG(Salary) AS AverageSalary,
       MAX(Salary) AS HighestSalary
FROM Employee;

Output:

⚠️ Common Errors and Misconceptions

🎯 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?

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?

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.