Databases & SQL · Class 12 · Query Practice
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),
-> foreign key (deptno) references dept(deptno)
-> );
Query OK, 0 rows affected (0.17 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.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
The last line of emp is the one to notice. The foreign key tells MySQL that a deptno in emp must be a department that really exists in dept — which is exactly why dept had to be created first. With it in place, a row pointing at a department that does not exist is refused rather than quietly stored:
MySQL command line client
mysql> insert into emp values (113, 'Test Row', 99, 'Clerk', 20000, NULL, '2024-01-01', 'Gangtok');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`))
Informatics Practices students: you may leave that line out
The foreign key is on the Computer Science syllabus, not the IP one. Drop it from your
create table if you like — nothing else in this chapter changes. Every page from here on asks you to read these tables with queries, and a query returns exactly the same answer whether the constraint is there or not. It is written in because emp and dept genuinely are related, and saying so in the schema is what makes “create dept first” mean something.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 | MUL | 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)
PRI on empno is the primary key, and the MUL now sitting against deptno is the foreign key’s index — multiple rows are allowed to share a value there, which is right: a department has many employees.
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?