MySQL Join Tables Exercise

Write a mysql statement to find the name, birth, department name, department block from the given tables.

 
+----+--------------+------------+------------+   
| id | name         | dept_id    | birth      |
+----+--------------+------------+------------+
|  1 | Maria Gloria | 2          | 1994-03-12 |
|  2 | John Smith   | 1          | 1993-02-07 |
|  3 | Gal Rao      | 4          | 1992-09-11 |
|  4 | Jakey Smith  | 2          | 1990-08-31 |
|  5 | Rama Saho    | 1          | 1994-12-09 |
|  6 | Maria Gaga   | 4          | 1993-10-09 |
+----+--------------+------------+------------+

+---------+--------------------------+------------+
| dept_id | dept_name                | dept_block |
+---------+--------------------------+------------+
|       1 | Computer Science         | B-Block    |
|       2 | Information Technology   | C-Block    |
|       3 | Mechanical               | A-Block    |
|       4 | Electronic Communication | D-Block    |
+---------+--------------------------+------------+

Solution

MySQL Join is used to retrieve data from self or more tables from a single statement based on the values of common column between tables.

The following statement retrieves data from both students and departments tables.

mysql> SELECT name, birth, dept_name, dept_block
    -> FROM students
    -> JOIN departments using(dept_id);

Output of the above statement -

+--------------+------------+--------------------------+------------+
| name         | birth      | dept_name                | dept_block |
+--------------+------------+--------------------------+------------+
| Maria Gloria | 1994-03-12 | Information Technology   | C-Block    |
| John Smith   | 1993-02-07 | Computer Science         | B-Block    |
| Gal Rao      | 1992-09-11 | Electronic Communication | D-Block    |
| Jakey Smith  | 1990-08-31 | Information Technology   | C-Block    |
| Rama Saho    | 1994-12-09 | Computer Science         | B-Block    |
| Maria Gaga   | 1993-10-09 | Electronic Communication | D-Block    |
+--------------+------------+--------------------------+------------+