Querying and SQL Functions

Overview

Learning outcomes

• Students will be able to distinguish single-row functions from aggregate functions and choose the right one for a task.
• Students will be able to use POWER(), ROUND() (including negative places) and MOD() and predict their output.
• Students will be able to apply text functions such as UPPER(), MID(), LEFT(), RIGHT(), INSTR(), LENGTH() and TRIM(), including nested calls.
• Students will be able to extract parts of dates with NOW(), DATE(), MONTH(), MONTHNAME(), YEAR(), DAY() and DAYNAME().
• Students will be able to summarise data with MAX(), MIN(), SUM(), AVG() and COUNT(), and explain how NULLs are handled.
• Students will be able to write queries using GROUP BY, HAVING and ORDER BY in the correct clause order, and explain WHERE vs HAVING.
• Students will be able to compute the degree and cardinality of a Cartesian product and write equi-joins in both WHERE and JOIN ... ON form using table aliases.

Lesson plan

Notes

SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;

+-------+--------------+--------+------------+--------+-------+--------+
| EmpID | Name         | Gender | DOJ        | Salary | Bonus | DeptID |
+-------+--------------+--------+------------+--------+-------+--------+
| E101  | Aarav Sharma | M      | 2015-03-12 |  52350 |  5000 | D01    |
| E102  | Priya Nair   | F      | 2018-07-01 |  61200 |  NULL | D03    |
| E103  | Rohan Gupta  | M      | 2012-11-23 |  45800 |  3500 | D02    |
| E104  | Meera Iyer   | F      | 2020-01-15 |  72500 |  8000 | D03    |
| E105  | Kabir Singh  | M      | 2019-09-30 |  38750 |  NULL | D01    |
| E106  | Ananya Das   | F      | 2016-05-20 |  49500 |  4000 | D02    |
| E107  | Vikram Rao   | M      | 2021-12-05 |  58000 |  6000 | D03    |
| E108  | Sneha Patil  | F      | 2017-02-28 |  41250 |  NULL | D01    |
+-------+--------------+--------+------------+--------+-------+--------+
+--------+----------+-----------+
| DeptID | DeptName | City      |
+--------+----------+-----------+
| D01    | Sales    | Mumbai    |
| D02    | Accounts | Delhi     |
| D03    | IT       | Bengaluru |
| D04    | HR       | Kolkata   |
+--------+----------+-----------+

SELECT Name, UPPER(Name) FROM EMPLOYEE WHERE DeptID = 'D02';
SELECT MAX(Salary) FROM EMPLOYEE WHERE DeptID = 'D02';

+-------------+-------------+
| Name        | UPPER(Name) |
+-------------+-------------+
| Rohan Gupta | ROHAN GUPTA |
| Ananya Das  | ANANYA DAS  |
+-------------+-------------+
+-------------+
| MAX(Salary) |
+-------------+
|       49500 |
+-------------+

SELECT POWER(3,4), POWER(2,-2), POW(10,3);
SELECT ROUND(2912.564,1), ROUND(283.5), ROUND(-7.5);
SELECT MOD(23,5), MOD(-23,5), 23 % 5, MOD(7.5,2);

+------------+-------------+-----------+
| POWER(3,4) | POWER(2,-2) | POW(10,3) |
+------------+-------------+-----------+
|         81 |        0.25 |      1000 |
+------------+-------------+-----------+
+-------------------+--------------+-------------+
| ROUND(2912.564,1) | ROUND(283.5) | ROUND(-7.5) |
+-------------------+--------------+-------------+
|            2912.6 |          284 |          -8 |
+-------------------+--------------+-------------+
+-----------+------------+--------+------------+
| MOD(23,5) | MOD(-23,5) | 23 % 5 | MOD(7.5,2) |
+-----------+------------+--------+------------+
|         3 |         -3 |      3 |        1.5 |
+-----------+------------+--------+------------+

SELECT Name, Salary, ROUND(Salary * 0.12, -2) AS PF,
       MOD(Salary, 1000) AS Odd_Amount
FROM EMPLOYEE;

+--------------+--------+------+------------+
| Name         | Salary | PF   | Odd_Amount |
+--------------+--------+------+------------+
| Aarav Sharma |  52350 | 6300 |        350 |
| Priya Nair   |  61200 | 7300 |        200 |
| Rohan Gupta  |  45800 | 5500 |        800 |
| Meera Iyer   |  72500 | 8700 |        500 |
| Kabir Singh  |  38750 | 4700 |        750 |
| Ananya Das   |  49500 | 5900 |        500 |
| Vikram Rao   |  58000 | 7000 |          0 |
| Sneha Patil  |  41250 | 5000 |        250 |
+--------------+--------+------+------------+

SELECT UPPER('Delhi Public School'), LCASE('MUMBAI'), LENGTH('NEW DELHI');
SELECT Name, UCASE(Name), LENGTH(Name) FROM EMPLOYEE WHERE DeptID = 'D03';

+------------------------------+-----------------+---------------------+
| UPPER('Delhi Public School') | LCASE('MUMBAI') | LENGTH('NEW DELHI') |
+------------------------------+-----------------+---------------------+
| DELHI PUBLIC SCHOOL          | mumbai          |                   9 |
+------------------------------+-----------------+---------------------+
+------------+-------------+--------------+
| Name       | UCASE(Name) | LENGTH(Name) |
+------------+-------------+--------------+
| Priya Nair | PRIYA NAIR  |           10 |
| Meera Iyer | MEERA IYER  |           10 |
| Vikram Rao | VIKRAM RAO  |           10 |
+------------+-------------+--------------+

SELECT MID('INFORMATICS',3,4), SUBSTRING('INFORMATICS',6),
       SUBSTR('INFORMATICS',-4,3), MID('INFORMATICS',0,3);
SELECT LEFT('KOLKATA',3), RIGHT('CHENNAI',4), MID('COMPUTER',4);

+------------------------+----------------------------+----------------------------+------------------------+
| MID('INFORMATICS',3,4) | SUBSTRING('INFORMATICS',6) | SUBSTR('INFORMATICS',-4,3) | MID('INFORMATICS',0,3) |
+------------------------+----------------------------+----------------------------+------------------------+
| FORM                   | MATICS                     | TIC                        |                        |
+------------------------+----------------------------+----------------------------+------------------------+
+-------------------+--------------------+-------------------+
| LEFT('KOLKATA',3) | RIGHT('CHENNAI',4) | MID('COMPUTER',4) |
+-------------------+--------------------+-------------------+
| KOL               | NNAI               | PUTER             |
+-------------------+--------------------+-------------------+

SELECT INSTR('BANANA','NA'), INSTR('BANANA','XY'), INSTR('Informatics','MA');
SELECT Name, INSTR(Name,' ') AS SpaceAt,
       LEFT(Name, INSTR(Name,' ') - 1) AS FirstName
FROM EMPLOYEE WHERE DeptID = 'D01';

+----------------------+----------------------+---------------------------+
| INSTR('BANANA','NA') | INSTR('BANANA','XY') | INSTR('Informatics','MA') |
+----------------------+----------------------+---------------------------+
|                    3 |                    0 |                         6 |
+----------------------+----------------------+---------------------------+
+--------------+---------+-----------+
| Name         | SpaceAt | FirstName |
+--------------+---------+-----------+
| Aarav Sharma |       6 | Aarav     |
| Kabir Singh  |       6 | Kabir     |
| Sneha Patil  |       6 | Sneha     |
+--------------+---------+-----------+

SELECT CONCAT('[', LTRIM('   Pune  '), ']'),
       CONCAT('[', RTRIM('   Pune  '), ']'),
       CONCAT('[', TRIM('   Pune  '), ']');
SELECT LENGTH('  Goa  '), LENGTH(LTRIM('  Goa  ')), LENGTH(TRIM('  Goa  '));

+--------------------------------------+--------------------------------------+-------------------------------------+
| CONCAT('[', LTRIM('   Pune  '), ']') | CONCAT('[', RTRIM('   Pune  '), ']') | CONCAT('[', TRIM('   Pune  '), ']') |
+--------------------------------------+--------------------------------------+-------------------------------------+
| [Pune  ]                             | [   Pune]                            | [Pune]                              |
+--------------------------------------+--------------------------------------+-------------------------------------+
+-------------------+--------------------------+-------------------------+
| LENGTH('  Goa  ') | LENGTH(LTRIM('  Goa  ')) | LENGTH(TRIM('  Goa  ')) |
+-------------------+--------------------------+-------------------------+
|                 7 |                        5 |                       3 |
+-------------------+--------------------------+-------------------------+

SELECT DATE('2025-08-15 10:30:45'), MONTH('2025-08-15'), MONTHNAME('2025-08-15'),
       YEAR('2025-08-15'), DAY('2025-08-15'), DAYNAME('2025-08-15');
SELECT Name, DOJ, YEAR(DOJ), MONTHNAME(DOJ), DAYNAME(DOJ) FROM EMPLOYEE;

+-----------------------------+---------------------+-------------------------+--------------------+-------------------+-----------------------+
| DATE('2025-08-15 10:30:45') | MONTH('2025-08-15') | MONTHNAME('2025-08-15') | YEAR('2025-08-15') | DAY('2025-08-15') | DAYNAME('2025-08-15') |
+-----------------------------+---------------------+-------------------------+--------------------+-------------------+-----------------------+
| 2025-08-15                  |                   8 | August                  |               2025 |                15 | Friday                |
+-----------------------------+---------------------+-------------------------+--------------------+-------------------+-----------------------+
+--------------+------------+-----------+----------------+--------------+
| Name         | DOJ        | YEAR(DOJ) | MONTHNAME(DOJ) | DAYNAME(DOJ) |
+--------------+------------+-----------+----------------+--------------+
| Aarav Sharma | 2015-03-12 |      2015 | March          | Thursday     |
| Priya Nair   | 2018-07-01 |      2018 | July           | Sunday       |
| Rohan Gupta  | 2012-11-23 |      2012 | November       | Friday       |
| Meera Iyer   | 2020-01-15 |      2020 | January        | Wednesday    |
| Kabir Singh  | 2019-09-30 |      2019 | September      | Monday       |
| Ananya Das   | 2016-05-20 |      2016 | May            | Friday       |
| Vikram Rao   | 2021-12-05 |      2021 | December       | Sunday       |
| Sneha Patil  | 2017-02-28 |      2017 | February       | Tuesday      |
+--------------+------------+-----------+----------------+--------------+

SELECT Name, DOJ FROM EMPLOYEE
WHERE MONTH(DOJ) BETWEEN 1 AND 6
ORDER BY Name;
SELECT Name, DAYNAME(DOJ) AS JoinDay FROM EMPLOYEE
WHERE DAYNAME(DOJ) = 'Friday';

+--------------+------------+
| Name         | DOJ        |
+--------------+------------+
| Aarav Sharma | 2015-03-12 |
| Ananya Das   | 2016-05-20 |
| Meera Iyer   | 2020-01-15 |
| Sneha Patil  | 2017-02-28 |
+--------------+------------+
+-------------+---------+
| Name        | JoinDay |
+-------------+---------+
| Rohan Gupta | Friday  |
| Ananya Das  | Friday  |
+-------------+---------+

SELECT MAX(Salary), MIN(Salary), SUM(Salary), AVG(Salary) FROM EMPLOYEE;
SELECT MAX(DOJ), MIN(Name), SUM(Bonus), AVG(Bonus) FROM EMPLOYEE;
SELECT ROUND(AVG(Salary),2) AS Avg_IT FROM EMPLOYEE WHERE DeptID = 'D03';

+-------------+-------------+-------------+-------------+
| MAX(Salary) | MIN(Salary) | SUM(Salary) | AVG(Salary) |
+-------------+-------------+-------------+-------------+
|       72500 |       38750 |      419350 |  52418.7500 |
+-------------+-------------+-------------+-------------+
+------------+--------------+------------+------------+
| MAX(DOJ)   | MIN(Name)    | SUM(Bonus) | AVG(Bonus) |
+------------+--------------+------------+------------+
| 2021-12-05 | Aarav Sharma |      26500 |  5300.0000 |
+------------+--------------+------------+------------+
+----------+
| Avg_IT   |
+----------+
| 63900.00 |
+----------+

SELECT COUNT(*), COUNT(Bonus), COUNT(DISTINCT DeptID) FROM EMPLOYEE;
SELECT AVG(Bonus), SUM(Bonus)/COUNT(*) FROM EMPLOYEE;
SELECT COUNT(*) AS No_Bonus FROM EMPLOYEE WHERE Bonus IS NULL;

+----------+--------------+------------------------+
| COUNT(*) | COUNT(Bonus) | COUNT(DISTINCT DeptID) |
+----------+--------------+------------------------+
|        8 |            5 |                      3 |
+----------+--------------+------------------------+
+------------+---------------------+
| AVG(Bonus) | SUM(Bonus)/COUNT(*) |
+------------+---------------------+
|  5300.0000 |           3312.5000 |
+------------+---------------------+
+----------+
| No_Bonus |
+----------+
|        3 |
+----------+

SELECT Name, Salary FROM EMPLOYEE ORDER BY Salary DESC;
SELECT DeptID, Name, Salary FROM EMPLOYEE ORDER BY DeptID, Salary DESC;

+--------------+--------+
| Name         | Salary |
+--------------+--------+
| Meera Iyer   |  72500 |
| Priya Nair   |  61200 |
| Vikram Rao   |  58000 |
| Aarav Sharma |  52350 |
| Ananya Das   |  49500 |
| Rohan Gupta  |  45800 |
| Sneha Patil  |  41250 |
| Kabir Singh  |  38750 |
+--------------+--------+
+--------+--------------+--------+
| DeptID | Name         | Salary |
+--------+--------------+--------+
| D01    | Aarav Sharma |  52350 |
| D01    | Sneha Patil  |  41250 |
| D01    | Kabir Singh  |  38750 |
| D02    | Ananya Das   |  49500 |
| D02    | Rohan Gupta  |  45800 |
| D03    | Meera Iyer   |  72500 |
| D03    | Priya Nair   |  61200 |
| D03    | Vikram Rao   |  58000 |
+--------+--------------+--------+

SELECT DeptID, COUNT(*), SUM(Salary), ROUND(AVG(Salary),2)
FROM EMPLOYEE
GROUP BY DeptID
ORDER BY DeptID;
SELECT YEAR(DOJ) AS Yr, COUNT(*) FROM EMPLOYEE
WHERE YEAR(DOJ) >= 2018
GROUP BY YEAR(DOJ)
ORDER BY Yr;

+--------+----------+-------------+----------------------+
| DeptID | COUNT(*) | SUM(Salary) | ROUND(AVG(Salary),2) |
+--------+----------+-------------+----------------------+
| D01    |        3 |      132350 |             44116.67 |
| D02    |        2 |       95300 |             47650.00 |
| D03    |        3 |      191700 |             63900.00 |
+--------+----------+-------------+----------------------+
+------+----------+
| Yr   | COUNT(*) |
+------+----------+
| 2018 |        1 |
| 2019 |        1 |
| 2020 |        1 |
| 2021 |        1 |
+------+----------+

SELECT DeptID, AVG(Salary) FROM EMPLOYEE
GROUP BY DeptID
HAVING AVG(Salary) > 50000;
SELECT DeptID, COUNT(*) FROM EMPLOYEE
WHERE Gender = 'F'
GROUP BY DeptID
HAVING COUNT(*) >= 1
ORDER BY DeptID DESC;

+--------+-------------+
| DeptID | AVG(Salary) |
+--------+-------------+
| D03    |  63900.0000 |
+--------+-------------+
+--------+----------+
| DeptID | COUNT(*) |
+--------+----------+
| D03    |        2 |
| D02    |        1 |
| D01    |        1 |
+--------+----------+

SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT;
SELECT E.Name, D.DeptName FROM EMPLOYEE E, DEPARTMENT D
WHERE E.EmpID = 'E101';

+----------+
| COUNT(*) |
+----------+
|       32 |
+----------+
+--------------+----------+
| Name         | DeptName |
+--------------+----------+
| Aarav Sharma | Sales    |
| Aarav Sharma | Accounts |
| Aarav Sharma | IT       |
| Aarav Sharma | HR       |
+--------------+----------+

SELECT E.Name, D.DeptName, D.City
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DeptID = D.DeptID
ORDER BY E.EmpID;
SELECT E.Name, D.DeptName FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DeptID = D.DeptID AND D.City = 'Mumbai'
ORDER BY E.Name;

+--------------+----------+-----------+
| Name         | DeptName | City      |
+--------------+----------+-----------+
| Aarav Sharma | Sales    | Mumbai    |
| Priya Nair   | IT       | Bengaluru |
| Rohan Gupta  | Accounts | Delhi     |
| Meera Iyer   | IT       | Bengaluru |
| Kabir Singh  | Sales    | Mumbai    |
| Ananya Das   | Accounts | Delhi     |
| Vikram Rao   | IT       | Bengaluru |
| Sneha Patil  | Sales    | Mumbai    |
+--------------+----------+-----------+
+--------------+----------+
| Name         | DeptName |
+--------------+----------+
| Aarav Sharma | Sales    |
| Kabir Singh  | Sales    |
| Sneha Patil  | Sales    |
+--------------+----------+

SELECT D.DeptName, COUNT(*) AS Staff, SUM(E.Salary) AS Payroll
FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DeptID = D.DeptID
GROUP BY D.DeptName
ORDER BY Payroll DESC;
SELECT * FROM EMPLOYEE NATURAL JOIN DEPARTMENT WHERE DeptID = 'D02';

+----------+-------+---------+
| DeptName | Staff | Payroll |
+----------+-------+---------+
| IT       |     3 |  191700 |
| Sales    |     3 |  132350 |
| Accounts |     2 |   95300 |
+----------+-------+---------+
+--------+-------+-------------+--------+------------+--------+-------+----------+-------+
| DeptID | EmpID | Name        | Gender | DOJ        | Salary | Bonus | DeptName | City  |
+--------+-------+-------------+--------+------------+--------+-------+----------+-------+
| D02    | E103  | Rohan Gupta | M      | 2012-11-23 |  45800 |  3500 | Accounts | Delhi |
| D02    | E106  | Ananya Das  | F      | 2016-05-20 |  49500 |  4000 | Accounts | Delhi |
+--------+-------+-------------+--------+------------+--------+-------+----------+-------+

SELECT Name FROM EMPLOYEE WHERE DeptID = 'D02'
UNION
SELECT Name FROM EMPLOYEE WHERE Salary > 60000;
SELECT Name FROM EMPLOYEE WHERE DeptID = 'D03'
EXCEPT
SELECT Name FROM EMPLOYEE WHERE Salary > 60000;

+-------------+
| Name        |
+-------------+
| Rohan Gupta |
| Ananya Das  |
| Priya Nair  |
| Meera Iyer  |
+-------------+
+------------+
| Name       |
+------------+
| Vikram Rao |
+------------+

Key terms

Function

A named routine that takes input values, processes them and returns a result.

Single-row function

A function applied to each row separately, giving one result per row; also called a scalar function.

Aggregate function

A multiple-row function that returns one value for a set of rows, such as SUM() or COUNT().

Argument

A value passed to a function inside its brackets, such as 3 and 4 in MID(s,3,4).

Nested function

A function used as an argument of another function; the inner one is evaluated first.

NULL

A marker for a missing or unknown value; it is not zero and not an empty string.

COUNT(*)

Counts all rows in a table or group, including rows that contain NULLs.

Column alias

A temporary heading for an output column, given with AS.

Table alias

A short temporary name for a table, given in the FROM clause and used to qualify columns.

GROUP BY

Clause that collects rows with equal values in given columns into groups for aggregation.

HAVING

Clause that filters groups using conditions that may contain aggregate functions.

ORDER BY

Clause that sorts the final result in ascending (ASC) or descending (DESC) order.

Cartesian product

Combination of every row of one table with every row of another; rows multiply, columns add.

Join

An operation that combines related rows from two tables using a condition on common columns.

Equi-join

A join whose condition tests equality of the common column in both tables.

Natural join

An equi-join on all same-named columns that shows each common column only once.

Qualified column name

A column name written with its table name or alias, such as E.DeptID.

Degree

The number of columns (attributes) in a relation.

Cardinality

The number of rows (tuples) in a relation.

UNION (Enrichment)

Set operation returning rows from either of two query results, without duplicates.

MCQs

Tap an option to check it, or use Show answers above to reveal them all.

1. What is the output of SELECT MID('INFORMATICS',3,4);?

   1. NFOR
   2. FORM
   3. ORMA
   4. FORMA
   Answer

   (b) FORM

   Counting from 1: I(1) N(2) F(3) O(4) R(5) M(6). Four characters from position 3 give FORM.

2. What does SELECT ROUND(15.193,-1); return?

   1. 15.2
   2. 15
   3. 10
   4. 20
   Answer

   (d) 20

   d = -1 rounds to the nearest ten. The units digit is 5, so 15.193 rounds up to 20.

3. Which of the following is a multiple-row (aggregate) function?

   1. LENGTH()
   2. ROUND()
   3. COUNT()
   4. MONTHNAME()
   Answer

   (c) COUNT()

   COUNT() returns one value for a group of rows. The others work on one value per row.

4. What is the output of SELECT MOD(-29,4);?

   1. -1
   2. 1
   3. 3
   4. -3
   Answer

   (a) -1

   29 = 4 x 7 + 1. In MySQL the remainder takes the sign of the dividend, so the result is -1.

5. What is the output of SELECT INSTR('MISSISSIPPI','SS');?

   1. 2
   2. 6
   3. 0
   4. 3
   Answer

   (d) 3

   INSTR returns the first position where SS starts. M(1) I(2) S(3) S(4), so the answer is 3.

6. Table T has 10 rows. Column Phone is NULL in 3 rows. What is COUNT(Phone)?

   1. 10
   2. 3
   3. 13
   4. 7
   Answer

   (d) 7

   COUNT(column) skips NULL values: 10 - 3 = 7. COUNT(*) would give 10.

7. What is the output of SELECT LENGTH(TRIM(' CBSE '));?

   1. 8
   2. 6
   3. 4
   4. 5
   Answer

   (c) 4

   TRIM removes the leading and trailing spaces, leaving CBSE, which has 4 characters.

8. SELECT DAYNAME('2026-01-26'); returns:

   1. Monday
   2. 26
   3. January
   4. 1
   Answer

   (a) Monday

   DAYNAME returns the weekday name as text. 26 January 2026 is a Monday.

9. Which clause is used to filter groups formed by GROUP BY?

   1. WHERE
   2. HAVING
   3. ORDER BY
   4. DISTINCT
   Answer

   (b) HAVING

   HAVING applies conditions to groups and may use aggregate functions; WHERE filters individual rows.

10. Which query will give an error?

    1. SELECT AVG(Salary) FROM EMPLOYEE WHERE DeptID='D01';
    2. SELECT DeptID FROM EMPLOYEE GROUP BY DeptID HAVING COUNT(*)>2;
    3. SELECT DeptID FROM EMPLOYEE WHERE SUM(Salary)>100000;
    4. SELECT MAX(DOJ) FROM EMPLOYEE;
    Answer

    (c) SELECT DeptID FROM EMPLOYEE WHERE SUM(Salary)>100000;

    An aggregate function such as SUM() cannot be used in WHERE. The condition belongs in HAVING with GROUP BY DeptID.

11. What is the output of SELECT RIGHT('BENGALURU',3);?

    1. BEN
    2. LUR
    3. URU
    4. RU
    Answer

    (c) URU

    RIGHT returns the last 3 characters: URU.

12. Relation A has 5 columns and 6 rows; relation B has 3 columns and 4 rows. The Cartesian product A x B has:

    1. 8 columns and 24 rows
    2. 15 columns and 10 rows
    3. 8 columns and 10 rows
    4. 15 columns and 24 rows
    Answer

    (a) 8 columns and 24 rows

    Degrees add (5 + 3 = 8) and cardinalities multiply (6 x 4 = 24).

13. Which function returns the month as a word, such as 'August'?

    1. MONTH()
    2. DAYNAME()
    3. MONTHNAME()
    4. DATE()
    Answer

    (c) MONTHNAME()

    MONTHNAME() returns the full month name. MONTH() returns the number 1 to 12.

14. What is the output of SELECT POWER(2,5);?

    1. 10
    2. 25
    3. 7
    4. 32
    Answer

    (d) 32

    2 raised to the power 5 is 2 x 2 x 2 x 2 x 2 = 32.

15. In an equi-join of EMPLOYEE and DEPARTMENT, why must DeptID be written as E.DeptID or D.DeptID?

    1. To make the query run faster
    2. Because DeptID is a primary key
    3. Because aliases are compulsory in every query
    4. Because both tables have a column named DeptID, so the name is ambiguous
    Answer

    (d) Because both tables have a column named DeptID, so the name is ambiguous

    When a column name exists in both tables, it must be qualified with the table name or alias to remove ambiguity.

16. Which is the correct order of clauses in a SELECT statement?

    1. SELECT, FROM, GROUP BY, WHERE, HAVING, ORDER BY
    2. SELECT, FROM, WHERE, HAVING, GROUP BY, ORDER BY
    3. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
    4. SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING
    Answer

    (c) SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

    WHERE comes before GROUP BY, HAVING follows GROUP BY, and ORDER BY is always last.

17. What is the output of SELECT ROUND(745,-2);?

    1. 800
    2. 700
    3. 750
    4. 745
    Answer

    (b) 700

    Rounding to the nearest hundred: the tens digit is 4 (less than 5), so the answer is 700.

18. AVG(Bonus) is calculated on a column with values 4000, NULL, 6000. The result is:

    1. 5000
    2. 3333.33
    3. NULL
    4. 10000
    Answer

    (a) 5000

    NULL is ignored. (4000 + 6000) / 2 = 5000.

19. What is the output of SELECT SUBSTR('KASHMIR',-3);?

    1. KAS
    2. HMI
    3. MIR
    4. SHM
    Answer

    (c) MIR

    A negative position counts from the right. Starting 3 characters from the end and taking the rest gives MIR.

20. Which statement about single-row functions is TRUE?

    1. They cannot be used in WHERE
    2. They return one result for each row processed
    3. They always ignore NULL values and return a count
    4. They can be used only with GROUP BY
    Answer

    (b) They return one result for each row processed

    Single-row (scalar) functions return a value for every row and can appear in SELECT, WHERE and ORDER BY.

21. What is the output of SELECT UCASE(MID('india gate',7));?

    1. GATE
    2. INDIA
    3. gate
    4. A GATE
    Answer

    (a) GATE

    Position 7 of 'india gate' is g (the space is position 6). MID returns gate, and UCASE converts it to GATE.

22. Which query lists departments having more than 2 employees?

    1. SELECT DeptID FROM EMPLOYEE WHERE COUNT(*) > 2;
    2. SELECT DeptID FROM EMPLOYEE GROUP BY DeptID HAVING COUNT(*) > 2;
    3. SELECT DeptID FROM EMPLOYEE HAVING DeptID > 2;
    4. SELECT DeptID, COUNT(*) > 2 FROM EMPLOYEE;
    Answer

    (b) SELECT DeptID FROM EMPLOYEE GROUP BY DeptID HAVING COUNT(*) > 2;

    Rows must first be grouped by DeptID, then HAVING COUNT(*) > 2 keeps groups with more than two rows.

23. What is the output of SELECT MID('PRACTICES', INSTR('PRACTICES','C'), 3);?

    1. CTI
    2. CES
    3. ACT
    4. PRA
    Answer

    (a) CTI

    The inner INSTR finds the first C at position 4. MID('PRACTICES',4,3) returns CTI.

24. Which keyword sorts the result from highest to lowest?

    1. DESC
    2. ASC
    3. DOWN
    4. REVERSE
    Answer

    (a) DESC

    DESC sorts in descending order. ASC is the default ascending order.

25. (Enrichment) Which set operation returns only the rows that appear in the results of both queries?

    1. UNION
    2. UNION ALL
    3. INTERSECT
    4. MINUS
    Answer

    (c) INTERSECT

    INTERSECT returns the common rows. UNION returns rows from either query; MINUS (EXCEPT in MySQL) returns rows of the first not in the second.

Assertion–Reason

1. Assertion (A): SELECT COUNT(Bonus) FROM EMPLOYEE; may return a smaller number than SELECT COUNT(*) FROM EMPLOYEE;.

   Reason (R): COUNT(column) ignores NULL values, whereas COUNT(*) counts every row.

   Answer

   (A)

   Both are true and the reason directly explains why the counts can differ.

2. Assertion (A): The condition AVG(Marks) > 60 cannot be written in a WHERE clause.

   Reason (R): WHERE is applied to individual rows before any groups are formed.

   Answer

   (A)

   Because WHERE works row by row before grouping, aggregate values do not exist yet; such conditions go in HAVING.

3. Assertion (A): SELECT ROUND(15.193,-1); returns 15.2.

   Reason (R): A negative second argument in ROUND() rounds the number to the left of the decimal point.

   Answer

   (D)

   The assertion is false: the answer is 20. The reason is true: d = -1 rounds to the nearest ten.

4. Assertion (A): INSTR('CBSE','X') returns 0.

   Reason (R): MySQL string positions start from 0.

   Answer

   (C)

   INSTR does return 0 when the substring is missing, but positions in MySQL start from 1, so R is false.

5. Assertion (A): An equi-join of two tables usually returns fewer rows than their Cartesian product.

   Reason (R): The Cartesian product has as many columns as the sum of the columns of both tables.

   Answer

   (B)

   Both statements are true, but the number of columns does not explain why the join has fewer rows. The join keeps only rows where the common column matches.

6. Assertion (A): UPPER() and UCASE() give the same result.

   Reason (R): MySQL provides some functions under more than one name.

   Answer

   (A)

   UCASE is a synonym of UPPER, just as LCASE/LOWER and MID/SUBSTRING/SUBSTR are synonyms.

Short answer

1. 2 marks

   Differentiate between single-row functions and aggregate functions. Give one example of each.

   Answer

   A single-row function works on one value of each row and returns one result per row, for example UPPER(Name).

   An aggregate function works on a group of rows and returns a single result for the group, for example SUM(Salary).

2. 2 marks

   Write the output of:

   1. SELECT ROUND(1567.89,-2);
   2. SELECT MOD(29,4);
   Answer

   1. 1600 (rounded to the nearest hundred)
   2. 1 (29 = 4 x 7 + 1)
3. 2 marks

   Write the output of:

   1. SELECT LEFT('BENGALURU',4);
   2. SELECT LENGTH('IP 065');
   Answer

   1. BENG
   2. 6 (the space is counted)
4. 2 marks

   Differentiate between COUNT(*) and COUNT(column_name) with an example.

   Answer

   COUNT(*) counts all rows, including rows with NULL values. COUNT(column_name) counts only the non-NULL values of that column.

   In EMPLOYEE, COUNT(*) is 8 but COUNT(Bonus) is 5, because Bonus is NULL for three employees.

5. 2 marks

   Differentiate between WHERE and HAVING clauses.

   Answer

   • WHERE filters individual rows before grouping and cannot contain aggregate functions, e.g. WHERE Salary > 50000.
   • HAVING filters groups after GROUP BY and can contain aggregate functions, e.g. HAVING COUNT(*) > 2.
6. 2 marks

   Write the output of:

   1. SELECT MONTHNAME('2023-10-02');
   2. SELECT DAY('2024-02-29');
   Answer

   1. October
   2. 29
7. 2 marks

   What is a Cartesian product? Table STUDENT has 4 columns and 30 rows; table HOUSE has 3 columns and 4 rows. Find the degree and cardinality of STUDENT x HOUSE.

   Answer

   A Cartesian product pairs every row of one table with every row of another.

   • Degree = 4 + 3 = 7
   • Cardinality = 30 x 4 = 120
8. 2 marks

   What is an equi-join? Why are table aliases used in joins?

   Answer

   An equi-join combines two tables and keeps only those rows where the common column has equal values, using = in the join condition.

   Table aliases are short names (such as E for EMPLOYEE) that make queries shorter and are used to qualify columns that exist in both tables.

9. 2 marks

   Write the output of:

   1. SELECT INSTR('BANANA','NA');
   2. SELECT SUBSTRING('INFORMATICS',6);
   Answer

   1. 3
   2. MATICS
10. 2 marks

    Rahul wrote: SELECT Name, SUM(Salary) FROM EMPLOYEE; and got an error. Explain the problem and write a corrected query that shows each department's total salary.

    Answer

    Name has one value per row, but SUM(Salary) produces one value for all rows, so they cannot be shown together without grouping. A plain column with an aggregate must appear in GROUP BY.

    SELECT DeptID, SUM(Salary) FROM EMPLOYEE
    GROUP BY DeptID ORDER BY DeptID;

    Output

    +--------+-------------+
    | DeptID | SUM(Salary) |
    +--------+-------------+
    | D01    |      132350 |
    | D02    |       95300 |
    | D03    |      191700 |
    +--------+-------------+

11. 2 marks

    Name the functions used to (a) remove spaces only from the right side of a string, and (b) find the weekday name of a date.

    Answer

    (a) RTRIM()

    (b) DAYNAME()

12. 2 marks

    Write the output of:

    1. SELECT UCASE(LEFT('delhi',1));
    2. SELECT ROUND(-3.45,1);
    Answer

    1. D
    2. -3.5 (a halfway value is rounded away from zero)
13. 2 marks

    Write a query to display the name of each employee and the year in which they joined, for employees who joined after 2017, sorted by year of joining.

    Answer

    Using the OFFICE database:

    SELECT Name, YEAR(DOJ) AS JoinYear FROM EMPLOYEE
    WHERE YEAR(DOJ) > 2017
    ORDER BY JoinYear;

    Output

    +-------------+----------+
    | Name        | JoinYear |
    +-------------+----------+
    | Priya Nair  |     2018 |
    | Kabir Singh |     2019 |
    | Meera Iyer  |     2020 |
    | Vikram Rao  |     2021 |
    +-------------+----------+

14. 2 marks

    Why does SELECT AVG(Bonus) FROM EMPLOYEE; give 5300.0000 while SELECT SUM(Bonus)/COUNT(*) FROM EMPLOYEE; gives 3312.5000?

    Answer

    AVG() ignores NULLs, so it divides the total 26500 by the 5 non-NULL bonuses. COUNT(*) counts all 8 rows, so the second expression divides 26500 by 8.

Long answer

1. 3 marks

   Consider the EMPLOYEE table of the OFFICE database. Write SQL queries for the following:

   1. Display the name and salary rounded to the nearest thousand.
   2. Display the first name of every employee.
   3. Display names of employees who joined in a month whose name is 'May' or 'March'.
   Answer

   1. ROUND(Salary,-3) rounds to thousands.
   2. The first name is everything before the space.
   3. MONTHNAME() returns text, so compare with words.

   SELECT Name, ROUND(Salary,-3) FROM EMPLOYEE WHERE DeptID = 'D01';
   SELECT LEFT(Name, INSTR(Name,' ') - 1) AS FirstName FROM EMPLOYEE WHERE DeptID = 'D02';
   SELECT Name FROM EMPLOYEE WHERE MONTHNAME(DOJ) IN ('May','March');

   Output

   +--------------+------------------+
   | Name         | ROUND(Salary,-3) |
   +--------------+------------------+
   | Aarav Sharma |            52000 |
   | Kabir Singh  |            39000 |
   | Sneha Patil  |            41000 |
   +--------------+------------------+
   +-----------+
   | FirstName |
   +-----------+
   | Rohan     |
   | Ananya    |
   +-----------+
   +--------------+
   | Name         |
   +--------------+
   | Aarav Sharma |
   | Ananya Das   |
   +--------------+

2. 3 marks

   Write the output of the following queries:

   1. SELECT POWER(4,0.5), MOD(10,0);
   2. SELECT RIGHT(LEFT('INFORMATICS',6),3);
   3. SELECT LENGTH(LTRIM(' Goa ')), LENGTH(RTRIM(' Goa '));
   Answer

   1. POWER(4,0.5) is the square root of 4, which is 2. MOD by zero gives NULL.
   2. LEFT gives INFORM; the last 3 characters of INFORM are ORM.
   3. ' Goa ' has 7 characters. Removing 2 leading spaces leaves 5; removing 2 trailing spaces also leaves 5.

   SELECT POWER(4,0.5), MOD(10,0);
   SELECT RIGHT(LEFT('INFORMATICS',6),3);
   SELECT LENGTH(LTRIM('  Goa  ')), LENGTH(RTRIM('  Goa  '));

   Output

   +--------------+-----------+
   | POWER(4,0.5) | MOD(10,0) |
   +--------------+-----------+
   |            2 |      NULL |
   +--------------+-----------+
   +--------------------------------+
   | RIGHT(LEFT('INFORMATICS',6),3) |
   +--------------------------------+
   | ORM                            |
   +--------------------------------+
   +--------------------------+--------------------------+
   | LENGTH(LTRIM('  Goa  ')) | LENGTH(RTRIM('  Goa  ')) |
   +--------------------------+--------------------------+
   |                        5 |                        5 |
   +--------------------------+--------------------------+

3. 3 marks

   Using the EMPLOYEE table, write queries to:

   1. Display each department's DeptID and the difference between its highest and lowest salary, largest difference first.
   2. Display the number of male and female employees.
   3. Display DeptIDs whose average salary is more than 50000.
   Answer

   Each query needs GROUP BY; the third also needs HAVING because the condition uses an aggregate.

   SELECT DeptID, MAX(Salary) - MIN(Salary) AS Gap FROM EMPLOYEE
   GROUP BY DeptID ORDER BY Gap DESC;
   SELECT Gender, COUNT(*) FROM EMPLOYEE GROUP BY Gender ORDER BY Gender;
   SELECT DeptID FROM EMPLOYEE GROUP BY DeptID HAVING AVG(Salary) > 50000;

   Output

   +--------+-------+
   | DeptID | Gap   |
   +--------+-------+
   | D03    | 14500 |
   | D01    | 13600 |
   | D02    |  3700 |
   +--------+-------+
   +--------+----------+
   | Gender | COUNT(*) |
   +--------+----------+
   | F      |        4 |
   | M      |        4 |
   +--------+----------+
   +--------+
   | DeptID |
   +--------+
   | D03    |
   +--------+

4. 5 marks

   Explain with examples the use of GROUP BY, HAVING and ORDER BY in one query. Describe the order in which MySQL processes a query that contains WHERE, GROUP BY, HAVING and ORDER BY.

   Answer

   GROUP BY forms groups of rows with the same value; HAVING keeps only the groups that satisfy a condition; ORDER BY sorts the final output.

   Processing order: FROM, then WHERE (rows filtered), then GROUP BY (groups formed), then HAVING (groups filtered), then SELECT (output columns computed), and finally ORDER BY (rows sorted).

   In the query below, WHERE keeps employees who joined before 2020, GROUP BY makes one group per department, HAVING keeps departments whose salary bill is over 90000, and ORDER BY sorts by the bill.

   SELECT DeptID, SUM(Salary) AS Bill FROM EMPLOYEE
   WHERE YEAR(DOJ) < 2020
   GROUP BY DeptID
   HAVING SUM(Salary) > 90000
   ORDER BY Bill DESC;

   Output

   +--------+--------+
   | DeptID | Bill   |
   +--------+--------+
   | D01    | 132350 |
   | D02    |  95300 |
   +--------+--------+

5. 5 marks

   Write SQL queries on the OFFICE database (EMPLOYEE and DEPARTMENT) for the following. Use table aliases.

   1. Display employee name, department name and city for all employees, using the WHERE form of equi-join.
   2. Repeat part 1 using JOIN ... ON.
   3. Display the city and number of employees working in each city.
   4. Display names of employees who work in Bengaluru and earn more than 60000.
   5. Display the name of each department and the total bonus paid in it.
   Answer

   Parts 1 and 2 produce the same 8 rows; only part 2's output is shown. In part 5, SUM() skips the NULL bonuses.

   SELECT E.Name, D.DeptName, D.City FROM EMPLOYEE E, DEPARTMENT D
   WHERE E.DeptID = D.DeptID;
   
   SELECT E.Name, D.DeptName, D.City
   FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DeptID = D.DeptID
   ORDER BY E.Name;
   
   SELECT D.City, COUNT(*) FROM EMPLOYEE E, DEPARTMENT D
   WHERE E.DeptID = D.DeptID GROUP BY D.City ORDER BY D.City;
   
   SELECT E.Name FROM EMPLOYEE E, DEPARTMENT D
   WHERE E.DeptID = D.DeptID AND D.City = 'Bengaluru' AND E.Salary > 60000;
   
   SELECT D.DeptName, SUM(E.Bonus) FROM EMPLOYEE E JOIN DEPARTMENT D
   ON E.DeptID = D.DeptID GROUP BY D.DeptName ORDER BY D.DeptName;

   Output

   +--------------+----------+-----------+
   | Name         | DeptName | City      |
   +--------------+----------+-----------+
   | Aarav Sharma | Sales    | Mumbai    |
   | Ananya Das   | Accounts | Delhi     |
   | Kabir Singh  | Sales    | Mumbai    |
   | Meera Iyer   | IT       | Bengaluru |
   | Priya Nair   | IT       | Bengaluru |
   | Rohan Gupta  | Accounts | Delhi     |
   | Sneha Patil  | Sales    | Mumbai    |
   | Vikram Rao   | IT       | Bengaluru |
   +--------------+----------+-----------+
   +-----------+----------+
   | City      | COUNT(*) |
   +-----------+----------+
   | Bengaluru |        3 |
   | Delhi     |        2 |
   | Mumbai    |        3 |
   +-----------+----------+
   +------------+
   | Name       |
   +------------+
   | Priya Nair |
   | Meera Iyer |
   +------------+
   +----------+--------------+
   | DeptName | SUM(E.Bonus) |
   +----------+--------------+
   | Accounts |         7500 |
   | IT       |        14000 |
   | Sales    |         5000 |
   +----------+--------------+

6. 5 marks

   Explain any three text functions and any two date functions of MySQL with syntax and one example each.

   Answer

   Text functions:

   • UPPER(s) / UCASE(s): converts s to capital letters. UPPER('goa') gives GOA.
   • MID(s,p,n) / SUBSTRING() / SUBSTR(): returns n characters of s from position p. MID('COMPUTER',4,3) gives PUT.
   • INSTR(s,sub): returns the position of the first occurrence of sub in s, or 0. INSTR('PRACTICES','C') gives 4.

   Date functions:

   • MONTHNAME(d): returns the month name. MONTHNAME('2025-08-15') gives August.
   • DAYNAME(d): returns the weekday name. DAYNAME('2025-08-15') gives Friday.
7. 3 marks

   A table SALES(BillNo, Item, Qty, Rate, BillDate) stores sales of a school canteen. Write queries to:

   1. Display the total quantity sold for each item.
   2. Display items whose total quantity sold is at least 50.
   3. Display the number of bills raised in each month, showing the month name.
   Answer

   The answers use GROUP BY on a column and on a function of a column.

   SELECT Item, SUM(Qty) FROM SALES GROUP BY Item;
   
   SELECT Item, SUM(Qty) FROM SALES GROUP BY Item HAVING SUM(Qty) >= 50;
   
   SELECT MONTHNAME(BillDate), COUNT(*) FROM SALES
   GROUP BY MONTH(BillDate), MONTHNAME(BillDate)
   ORDER BY MONTH(BillDate);

8. 3 marks

   Differentiate between: (a) ROUND(n) and ROUND(n,-2), (b) LTRIM() and TRIM(), (c) MONTH() and MONTHNAME(). Give one example of each.

   Answer

   (a) ROUND(n) rounds to the nearest whole number: ROUND(1567.89) is 1568. ROUND(n,-2) rounds to the nearest hundred: ROUND(1567.89,-2) is 1600.

   (b) LTRIM() removes only leading spaces: LENGTH(LTRIM(' Goa ')) is 5. TRIM() removes leading and trailing spaces: LENGTH(TRIM(' Goa ')) is 3.

   (c) MONTH() returns a number: MONTH('2025-08-15') is 8. MONTHNAME() returns text: MONTHNAME('2025-08-15') is August.

Case-based

Practicals

CREATE DATABASE SCHOOL;
USE SCHOOL;
CREATE TABLE STUDENT (
  StudentID INT PRIMARY KEY,
  Name      VARCHAR(30) NOT NULL,
  Class     CHAR(3),
  Gender    CHAR(1),
  DOB       DATE,
  Marks     DECIMAL(5,1),
  HouseID   CHAR(2)
);
DESC STUDENT;

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| StudentID | int          | NO   | PRI | NULL    |       |
| Name      | varchar(30)  | NO   |     | NULL    |       |
| Class     | char(3)      | YES  |     | NULL    |       |
| Gender    | char(1)      | YES  |     | NULL    |       |
| DOB       | date         | YES  |     | NULL    |       |
| Marks     | decimal(5,1) | YES  |     | NULL    |       |
| HouseID   | char(2)      | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

INSERT INTO STUDENT VALUES
(1201, 'Ishaan Verma',   'XII', 'M', '2008-04-14', 88.5, 'H1'),
(1202, 'Diya Menon',     'XII', 'F', '2008-09-02', 92.0, 'H2'),
(1203, 'Arjun Reddy',    'XII', 'M', '2007-12-21', 67.0, 'H3'),
(1204, 'Kavya Joshi',    'XII', 'F', '2008-01-30', 79.5, 'H1'),
(1205, 'Mohammed Faiz',  'XII', 'M', '2008-06-11', 54.0, 'H2'),
(1206, 'Tanvi Kulkarni', 'XII', 'F', '2008-09-25', 81.0, 'H3'),
(1207, 'Harpreet Kaur',  'XII', 'F', '2007-11-08', 73.5, 'H1'),
(1208, 'Siddharth Bose', 'XII', 'M', '2008-03-17', 95.5, 'H2'),
(1209, 'Nikhil Yadav',   'XII', 'M', '2008-07-04', 48.0, 'H3'),
(1210, 'Riya Thomas',    'XII', 'F', '2008-02-12', 86.0, 'H1');
SELECT * FROM STUDENT;

+-----------+----------------+-------+--------+------------+-------+---------+
| StudentID | Name           | Class | Gender | DOB        | Marks | HouseID |
+-----------+----------------+-------+--------+------------+-------+---------+
|      1201 | Ishaan Verma   | XII   | M      | 2008-04-14 |  88.5 | H1      |
|      1202 | Diya Menon     | XII   | F      | 2008-09-02 |  92.0 | H2      |
|      1203 | Arjun Reddy    | XII   | M      | 2007-12-21 |  67.0 | H3      |
|      1204 | Kavya Joshi    | XII   | F      | 2008-01-30 |  79.5 | H1      |
|      1205 | Mohammed Faiz  | XII   | M      | 2008-06-11 |  54.0 | H2      |
|      1206 | Tanvi Kulkarni | XII   | F      | 2008-09-25 |  81.0 | H3      |
|      1207 | Harpreet Kaur  | XII   | F      | 2007-11-08 |  73.5 | H1      |
|      1208 | Siddharth Bose | XII   | M      | 2008-03-17 |  95.5 | H2      |
|      1209 | Nikhil Yadav   | XII   | M      | 2008-07-04 |  48.0 | H3      |
|      1210 | Riya Thomas    | XII   | F      | 2008-02-12 |  86.0 | H1      |
+-----------+----------------+-------+--------+------------+-------+---------+

INSERT INTO STUDENT (StudentID, Name, Class, Gender, DOB, Marks, HouseID)
VALUES (1211, 'Aditi Saxena', 'XII', 'F', '2008-05-19', 77.0, 'H2');
DELETE FROM STUDENT WHERE StudentID = 1209;
SELECT StudentID, Name, Marks FROM STUDENT WHERE StudentID >= 1208;

+-----------+----------------+-------+
| StudentID | Name           | Marks |
+-----------+----------------+-------+
|      1208 | Siddharth Bose |  95.5 |
|      1210 | Riya Thomas    |  86.0 |
|      1211 | Aditi Saxena   |  77.0 |
+-----------+----------------+-------+

SELECT StudentID, Name, Marks FROM STUDENT
WHERE Marks > 80;

+-----------+----------------+-------+
| StudentID | Name           | Marks |
+-----------+----------------+-------+
|      1201 | Ishaan Verma   |  88.5 |
|      1202 | Diya Menon     |  92.0 |
|      1206 | Tanvi Kulkarni |  81.0 |
|      1208 | Siddharth Bose |  95.5 |
|      1210 | Riya Thomas    |  86.0 |
+-----------+----------------+-------+

SELECT MIN(Marks), MAX(Marks), SUM(Marks), ROUND(AVG(Marks),2) AS Average
FROM STUDENT;
SELECT Gender, MIN(Marks), MAX(Marks), ROUND(AVG(Marks),1) AS Average
FROM STUDENT
GROUP BY Gender
ORDER BY Gender;

+------------+------------+------------+---------+
| MIN(Marks) | MAX(Marks) | SUM(Marks) | Average |
+------------+------------+------------+---------+
|       54.0 |       95.5 |      794.0 |   79.40 |
+------------+------------+------------+---------+
+--------+------------+------------+---------+
| Gender | MIN(Marks) | MAX(Marks) | Average |
+--------+------------+------------+---------+
| F      |       73.5 |       92.0 |    81.5 |
| M      |       54.0 |       95.5 |    76.3 |
+--------+------------+------------+---------+

CREATE TABLE CUSTOMER (
  CustID   INT PRIMARY KEY,
  CustName VARCHAR(30),
  Country  VARCHAR(20)
);
INSERT INTO CUSTOMER VALUES
(1, 'Anil Mehta',    'India'),
(2, 'Fatima Noor',   'UAE'),
(3, 'Ramesh Pillai', 'India'),
(4, 'Tenzing Sherpa','Nepal'),
(5, 'Sunita Rao',    'India'),
(6, 'Omar Khalid',   'UAE'),
(7, 'Pema Wangchuk', 'Bhutan'),
(8, 'Bikash Thapa',  'Nepal'),
(9, 'Lakshmi Iyer',  'India');
SELECT Country, COUNT(*) AS Customers
FROM CUSTOMER
GROUP BY Country
ORDER BY Customers DESC, Country;
SELECT Country, COUNT(*) AS Customers
FROM CUSTOMER
GROUP BY Country
HAVING COUNT(*) >= 2
ORDER BY Country;

+---------+-----------+
| Country | Customers |
+---------+-----------+
| India   |         4 |
| Nepal   |         2 |
| UAE     |         2 |
| Bhutan  |         1 |
+---------+-----------+
+---------+-----------+
| Country | Customers |
+---------+-----------+
| India   |         4 |
| Nepal   |         2 |
| UAE     |         2 |
+---------+-----------+

SELECT StudentID, Marks FROM STUDENT
ORDER BY Marks DESC;

+-----------+-------+
| StudentID | Marks |
+-----------+-------+
|      1208 |  95.5 |
|      1202 |  92.0 |
|      1201 |  88.5 |
|      1210 |  86.0 |
|      1206 |  81.0 |
|      1204 |  79.5 |
|      1211 |  77.0 |
|      1207 |  73.5 |
|      1203 |  67.0 |
|      1205 |  54.0 |
+-----------+-------+

SELECT UPPER(Name) AS NameCaps,
       LEFT(Name, 3) AS Code,
       LENGTH(Name) AS Len,
       MID(Name, INSTR(Name,' ') + 1) AS Surname
FROM STUDENT
WHERE HouseID = 'H1';
SELECT Name FROM STUDENT WHERE RIGHT(Name, 1) = 'a';

+---------------+------+-----+---------+
| NameCaps      | Code | Len | Surname |
+---------------+------+-----+---------+
| ISHAAN VERMA  | Ish  |  12 | Verma   |
| KAVYA JOSHI   | Kav  |  11 | Joshi   |
| HARPREET KAUR | Har  |  13 | Kaur    |
| RIYA THOMAS   | Riy  |  11 | Thomas  |
+---------------+------+-----+---------+
+--------------+
| Name         |
+--------------+
| Ishaan Verma |
| Aditi Saxena |
+--------------+

SELECT Name, DOB, DAY(DOB) AS D, MONTHNAME(DOB) AS Mon,
       YEAR(DOB) AS Yr, DAYNAME(DOB) AS BornOn
FROM STUDENT
WHERE YEAR(DOB) = 2008 AND MONTH(DOB) <= 4
ORDER BY DOB;
SELECT MONTHNAME(DOB) AS Mon, COUNT(*) FROM STUDENT
GROUP BY MONTH(DOB), MONTHNAME(DOB)
HAVING COUNT(*) > 1;

+----------------+------------+------+----------+------+-----------+
| Name           | DOB        | D    | Mon      | Yr   | BornOn    |
+----------------+------------+------+----------+------+-----------+
| Kavya Joshi    | 2008-01-30 |   30 | January  | 2008 | Wednesday |
| Riya Thomas    | 2008-02-12 |   12 | February | 2008 | Tuesday   |
| Siddharth Bose | 2008-03-17 |   17 | March    | 2008 | Monday    |
| Ishaan Verma   | 2008-04-14 |   14 | April    | 2008 | Monday    |
+----------------+------------+------+----------+------+-----------+
+-----------+----------+
| Mon       | COUNT(*) |
+-----------+----------+
| September |        2 |
+-----------+----------+

CREATE TABLE HOUSE (
  HouseID   CHAR(2) PRIMARY KEY,
  HouseName VARCHAR(15),
  Incharge  VARCHAR(25)
);
INSERT INTO HOUSE VALUES
('H1', 'Shivaji', 'Mr. Deshpande'),
('H2', 'Tagore',  'Ms. Banerjee'),
('H3', 'Raman',   'Mr. Krishnan'),
('H4', 'Kalpana', 'Ms. Chawla');
SELECT S.Name, H.HouseName, H.Incharge
FROM STUDENT S, HOUSE H
WHERE S.HouseID = H.HouseID AND S.Marks >= 85
ORDER BY S.Name;
SELECT H.HouseName, COUNT(*) AS Members, ROUND(AVG(S.Marks),1) AS AvgMarks
FROM STUDENT S JOIN HOUSE H ON S.HouseID = H.HouseID
GROUP BY H.HouseName
HAVING AVG(S.Marks) > 75
ORDER BY AvgMarks DESC;

+----------------+-----------+---------------+
| Name           | HouseName | Incharge      |
+----------------+-----------+---------------+
| Diya Menon     | Tagore    | Ms. Banerjee  |
| Ishaan Verma   | Shivaji   | Mr. Deshpande |
| Riya Thomas    | Shivaji   | Mr. Deshpande |
| Siddharth Bose | Tagore    | Ms. Banerjee  |
+----------------+-----------+---------------+
+-----------+---------+----------+
| HouseName | Members | AvgMarks |
+-----------+---------+----------+
| Shivaji   |       4 |     81.9 |
| Tagore    |       4 |     79.6 |
+-----------+---------+----------+

Common mistakes

• Using an aggregate in WHERE, as in WHERE AVG(Salary) > 50000. Aggregates are not known while rows are being filtered; move the condition to HAVING after GROUP BY.

• Counting string positions from 0. MySQL positions start at 1, so MID('INFORMATICS',3,4) is FORM, not ORMA. Number the letters before answering.

• Treating NULL as 0. COUNT(Bonus), SUM(Bonus) and AVG(Bonus) all skip NULLs, so AVG(Bonus) divides by 5, not 8. Use COUNT(*) to count every row.

• Selecting a non-grouped column with an aggregate, such as SELECT Name, MAX(Salary) FROM EMPLOYEE GROUP BY DeptID. Every plain column in SELECT must appear in GROUP BY.

• Getting negative ROUND() wrong, for example writing ROUND(15.193,-1) as 15.2 or 10. A negative d rounds to tens, hundreds and so on; the answer here is 20.

• Expecting INSTR() to return -1 or NULL when the substring is missing. It returns 0.

• Writing an unqualified common column in a join (SELECT DeptID FROM EMPLOYEE, DEPARTMENT ...). MySQL reports that DeptID is ambiguous; write E.DeptID or D.DeptID.

• Forgetting the join condition, which silently produces a Cartesian product with 8 x 4 = 32 rows instead of 8 matched rows.

• Writing clauses in the wrong order, such as ORDER BY before GROUP BY, or HAVING before GROUP BY. The order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

• Thinking TRIM() removes all spaces. It removes only leading and trailing spaces; the space between first and last names stays.