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.

 
+------------------------+------------------------------+----------+----------+
| 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 |
+----+-------+---------+------------+

11. Delete duplicate row from the table.

12. Display the alternate rows from MySQL table.

13. Delete alternate rows from MySQL table.

14. MySQL update multiple rows in one query.

15. MySQL get nth highest paid and nth lowest paid salary.

16. Display the nth row from MySQL table.

17. Write a MySQL statements for rollback commit and save points.

18. Display details of first 5 highly paid employees using MySQL.