MySQL Exercises

1. Write a mysql statement to find the concatenated first_name, last_name where the age of the employee is greater than 30.


Suppose the employee table is -
 
+------------------------+------------------------------+----------+----------+
| first_name             | last_name                    | age      | dept     |
+------------------------+------------------------------+----------+----------+
| Mesa                   | Loop                         |  30      |  Acct    |
| Smith                  | Oak                          |  27      |  Devl    | 
| John                   | Jorz                         |  37      |  QA      | 
| Hary                   | Gaga                         |  32      |  QA      | 
+------------------------+------------------------------+----------+----------+
2. Write a mysql statement to get user, current date and mysql version.
3. Write a mysql statement to get item id, item, price of the most expensive item.
4. Write a mysql statement to create a new user and set a password and privileges for an existing database.
5. Write a mysql statement to select data of only CS and IT departments.

Suppose the table is -
 
+----+--------------+------------+-----+
| id | name         | department | age |
+----+--------------+------------+-----+
|  1 | Maria Gloria | CS         |  22 |
|  2 | John Smith   | IT         |  23 |
|  3 | Gal Rao      | CS         |  22 |
|  4 | Jakey Smith  | EC         |  24 |
|  5 | Rama Saho    | IT         |  22 |
|  6 | Maria Gaga   | EC         |  23 |
+----+--------------+------------+-----+
6. Write a mysql statement to select data of all departments in descending order by age.

Suppose the table is -
 
+----+--------------+------------+-----+
| id | name         | department | age |
+----+--------------+------------+-----+
|  1 | Maria Gloria | CS         |  22 |
|  2 | John Smith   | IT         |  23 |
|  3 | Gal Rao      | CS         |  22 |
|  4 | Jakey Smith  | EC         |  24 |
|  5 | Rama Saho    | IT         |  22 |
|  6 | Maria Gaga   | EC         |  23 |
+----+--------------+------------+-----+
7. Write a mysql statement to determine the age of each of the students.

Suppose the table is -
 
+----+--------------+------------+------------+
| id | name         | department | birth      |
+----+--------------+------------+------------+
|  1 | Maria Gloria | CS         | 1994-03-12 |
|  2 | John Smith   | IT         | 1993-02-07 |
|  3 | Gal Rao      | CS         | 1992-09-11 |
|  4 | Jakey Smith  | EC         | 1990-08-31 |
|  5 | Rama Saho    | IT         | 1994-12-09 |
|  6 | Maria Gaga   | EC         | 1993-10-09 |
+----+--------------+------------+------------+
8. Write a mysql statement to retrieve name beginning with 'm'.

Suppose the table is -
 
+----+--------------+------------+------------+
| id | name         | department | birth      |
+----+--------------+------------+------------+
|  1 | Maria Gloria | CS         | 1994-03-12 |
|  2 | John Smith   | IT         | 1993-02-07 |
|  3 | Gal Rao      | CS         | 1992-09-11 |
|  4 | Jakey Smith  | EC         | 1990-08-31 |
|  5 | Rama Saho    | IT         | 1994-12-09 |
|  6 | Maria Gaga   | EC         | 1993-10-09 |
+----+--------------+------------+------------+
9. 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    |
+---------+--------------------------+------------+
10. Write a mysql statement to get name of students containing exactly four characters.

Suppose the student table is -
 
+----+-------+---------+------------+
| id | name  | dept_id | birth      |
+----+-------+---------+------------+
|  1 | Maria | 2       | 1994-03-12 |
|  2 | John  | 1       | 1993-02-07 |
|  3 | Gal   | 4       | 1992-09-11 |
|  4 | Jakey | 2       | 1990-08-31 |
|  5 | Rama  | 1       | 1994-12-09 |
|  6 | Maria | 4       | 1993-10-09 |
+----+-------+---------+------------+

Read more articles


General Knowledge



Learn Popular Language