Query Practice · Class 12 · The Practice Tables
MySQLsetup⏱️ 8 min read
The Tables You Will Query
A board paper gives you a table and asks five things about it. This track does the same, except that the same three tables run through every page — so you spend your time on the queries instead of re-reading a new schema each time.
This track assumes the lessons
Everything here is practice for what the Databases & SQL track teaches. If a query below uses something you have not met yet, that lesson is the place to go first — these pages explain the question, not the clause.
1Build them yourself
Type these in once and every question in this track will work on your own machine. Start with the database:
MySQL command line client
mysql> create database practice;
Query OK, 1 row affected (0.02 sec)
mysql> use practice;
Database changed
Then the three tables. dept first, because emp refers to it:
MySQL command line client
mysql> create table dept (
-> deptno int primary key,
-> dname varchar(15),
-> head varchar(25),
-> location varchar(15)
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> insert into dept values
-> (10, 'Sales', 'Rakesh Menon', 'Gangtok'),
-> (20, 'Accounts', 'Sunita Rai', 'Siliguri'),
-> (30, 'Despatch', 'Imran Qureshi', 'Gangtok'),
-> (40, 'Research', 'Nita Bose', 'Kolkata');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
MySQL command line client
mysql> create table emp (
-> empno int primary key,
-> name varchar(25) not null,
-> deptno int,
-> job varchar(15),
-> salary decimal(9,2),
-> bonus decimal(8,2),
-> doj date,
-> city varchar(15)
-> );
Query OK, 0 rows affected (0.15 sec)
mysql> insert into emp values
-> (101, 'Arun Thapa', 10, 'Manager', 58000, 4500, '2015-03-12', 'Gangtok'),
-> (102, 'Bina Sharma', 20, 'Clerk', 26000, NULL, '2019-07-01', 'Siliguri'),
-> (103, 'Chandan Rai', 10, 'Salesman', 31000, 2200, '2018-01-23', 'Singtam'),
-> (104, 'Deepa Nair', 30, 'Manager', 54000, 4000, '2016-11-05', 'Gangtok'),
-> (105, 'Emil Lepcha', 20, 'Analyst', 47000, NULL, '2020-02-17', NULL),
-> (106, 'Farida Khan', 10, 'Salesman', 29500, 1800, '2021-06-30', 'Namchi'),
-> (107, 'Gopal Das', 30, 'Clerk', 24000, 900, '2017-09-14', 'Siliguri'),
-> (108, 'Hema Subba', 20, 'Analyst', 49500, 3100, '2014-04-08', 'Gangtok'),
-> (109, 'Ishan Pradhan', 10, 'Clerk', 25500, NULL, '2022-08-19', 'Singtam'),
-> (110, 'Jyoti Tamang', 30, 'Salesman', 33000, 2500, '2013-12-02', NULL),
-> (111, 'Karan Bhutia', 20, 'Manager', 61000, 5000, '2012-05-25', 'Kolkata'),
-> (112, 'Lata Gurung', NULL, 'Clerk', 27500, 1200, '2023-01-09', 'Namchi');
Query OK, 12 rows affected (0.03 sec)
Records: 12 Duplicates: 0 Warnings: 0
MySQL command line client
mysql> create table item (
-> icode char(4) primary key,
-> iname varchar(20),
-> category varchar(12),
-> price decimal(8,2),
-> qty int,
-> supplier varchar(20),
-> pdate date
-> );
Query OK, 0 rows affected (0.24 sec)
mysql> insert into item values
-> ('I101','Gel Pen', 'Stationery', 15.00, 250, 'Nataraj', '2024-01-15'),
-> ('I102','Notebook', 'Stationery', 45.50, 180, 'Classmate', '2024-02-20'),
-> ('I103','Geometry Box','Stationery', 120.00, 60, 'Camlin', '2023-11-08'),
-> ('I104','Water Bottle','Utility', 250.00, 40, 'Milton', '2024-03-01'),
-> ('I105','School Bag', 'Utility', 750.00, 25, 'Skybags', '2023-09-12'),
-> ('I106','Pencil Box', 'Stationery', 85.00, NULL,'Camlin', '2024-01-30'),
-> ('I107','Lunch Box', 'Utility', 320.00, 35, 'Milton', '2024-02-14'),
-> ('I108','Eraser', 'Stationery', 5.00, 500, 'Nataraj', NULL);
Query OK, 8 rows affected (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0
Three tables, as promised:
MySQL command line client
mysql> show tables;
+--------------------+
| Tables_in_practice |
+--------------------+
| dept |
| emp |
| item |
+--------------------+
3 rows in set (0.00 sec)
2emp — the main table
MySQL command line client
mysql> describe emp;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| name | varchar(25) | NO | | NULL | |
| deptno | int | YES | | NULL | |
| job | varchar(15) | YES | | NULL | |
| salary | decimal(9,2) | YES | | NULL | |
| bonus | decimal(8,2) | YES | | NULL | |
| doj | date | YES | | NULL | |
| city | varchar(15) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
MySQL command line client
mysql> select * from emp;
+-------+---------------+--------+----------+----------+---------+------------+----------+
| empno | name | deptno | job | salary | bonus | doj | city |
+-------+---------------+--------+----------+----------+---------+------------+----------+
| 101 | Arun Thapa | 10 | Manager | 58000.00 | 4500.00 | 2015-03-12 | Gangtok |
| 102 | Bina Sharma | 20 | Clerk | 26000.00 | NULL | 2019-07-01 | Siliguri |
| 103 | Chandan Rai | 10 | Salesman | 31000.00 | 2200.00 | 2018-01-23 | Singtam |
| 104 | Deepa Nair | 30 | Manager | 54000.00 | 4000.00 | 2016-11-05 | Gangtok |
| 105 | Emil Lepcha | 20 | Analyst | 47000.00 | NULL | 2020-02-17 | NULL |
| 106 | Farida Khan | 10 | Salesman | 29500.00 | 1800.00 | 2021-06-30 | Namchi |
| 107 | Gopal Das | 30 | Clerk | 24000.00 | 900.00 | 2017-09-14 | Siliguri |
| 108 | Hema Subba | 20 | Analyst | 49500.00 | 3100.00 | 2014-04-08 | Gangtok |
| 109 | Ishan Pradhan | 10 | Clerk | 25500.00 | NULL | 2022-08-19 | Singtam |
| 110 | Jyoti Tamang | 30 | Salesman | 33000.00 | 2500.00 | 2013-12-02 | NULL |
| 111 | Karan Bhutia | 20 | Manager | 61000.00 | 5000.00 | 2012-05-25 | Kolkata |
| 112 | Lata Gurung | NULL | Clerk | 27500.00 | 1200.00 | 2023-01-09 | Namchi |
+-------+---------------+--------+----------+----------+---------+------------+----------+
12 rows in set (0.00 sec)
3dept — the second table
MySQL command line client
mysql> select * from dept;
+--------+----------+---------------+----------+
| deptno | dname | head | location |
+--------+----------+---------------+----------+
| 10 | Sales | Rakesh Menon | Gangtok |
| 20 | Accounts | Sunita Rai | Siliguri |
| 30 | Despatch | Imran Qureshi | Gangtok |
| 40 | Research | Nita Bose | Kolkata |
+--------+----------+---------------+----------+
4 rows in set (0.00 sec)
deptno is the column the two tables have in common, and every two-table question on this track joins on it.
4item — the unseen table
item is kept back for the full board-style sets at the end, so that you meet it the way you will meet a table in the exam hall: cold.
MySQL command line client
mysql> select * from item;
+-------+--------------+------------+--------+------+-----------+------------+
| icode | iname | category | price | qty | supplier | pdate |
+-------+--------------+------------+--------+------+-----------+------------+
| I101 | Gel Pen | Stationery | 15.00 | 250 | Nataraj | 2024-01-15 |
| I102 | Notebook | Stationery | 45.50 | 180 | Classmate | 2024-02-20 |
| I103 | Geometry Box | Stationery | 120.00 | 60 | Camlin | 2023-11-08 |
| I104 | Water Bottle | Utility | 250.00 | 40 | Milton | 2024-03-01 |
| I105 | School Bag | Utility | 750.00 | 25 | Skybags | 2023-09-12 |
| I106 | Pencil Box | Stationery | 85.00 | NULL | Camlin | 2024-01-30 |
| I107 | Lunch Box | Utility | 320.00 | 35 | Milton | 2024-02-14 |
| I108 | Eraser | Stationery | 5.00 | 500 | Nataraj | NULL |
+-------+--------------+------------+--------+------+-----------+------------+
8 rows in set (0.00 sec)
5Degree and cardinality
Two words a paper asks for by name. Degree is the number of columns; cardinality is the number of rows. You can read both straight off the output above — describe ends with the column count, and a select * ends with the row count.
emp
degree 8
cardinality 12
dept
degree 4
cardinality 4
item
degree 7
cardinality 8
Cardinality counts the cards
Both words start the same way, which is why they get swapped. Cardinality counts the cards — the rows you could deal out. Degree is the design: how wide the table is. Adding an employee changes the cardinality and never the degree.
6Four things planted on purpose
Three employees have no bonus
Bina Sharma, Emil Lepcha and Ishan Pradhan. Every NULL question uses them.
Two have no city
Emil Lepcha and Jyoti Tamang — so count(city) and count(*) disagree.
Lata Gurung has no department
Her deptno is NULL, so she vanishes from every join. That is the point.
Research has nobody in it
Department 40 exists in dept but appears in no employee row.
Quick Check
What is the degree and cardinality of the emp table?
Quick Check
If you insert one more employee, what changes?