SQL Exercises
SQL Exercises
IMPORTANT NOTE for trainers only: Please DO NOT change these materials, especially the EXERCISES, without direct permission from: GRZEGORZ PRUSZCZYNSKI or LUKASZ SOKOLOWSKI.
Copyright Notice
Copyright © 2004-2023 by NobleProg Limited All rights reserved.
This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise.
1. List employees whose Daily salary is more than 100.
+-------+------------+ | ename | Daily sal | +-------+------------+ | BUSH | 166.666667 | +-------+------------+
2. *Select employees with the longest name. (subqueries)
+----------+ | ename | +----------+ | THATCHER | | CARNEGIE | +----------+
3. List the emps in the asc order of Designations of those joined after the second half of 1981.
+----------+-----------+------------+ | ename | job | hiredate | +----------+-----------+------------+ | TOOSK | ANALYST | 1981-12-03 | | CARNEGIE | ANALYST | 1982-12-09 | | BUFFETT | CLERK | 1981-12-03 | | FORD | CLERK | 1983-01-12 | | ELISON | CLERK | 1982-01-23 | | BUSH | PRESIDENT | 1981-11-17 | | CHIRACK | SALESMAN | 1981-09-28 | | GATES | SALESMAN | 1981-09-08 | +----------+-----------+------------+
4. List the emps whose Annual salary is ranging from 22000 and 45000.
+----------+----------+ | ename | Annual | +----------+----------+ | BLAIR | 34200.00 | | MERKEL | 29400.00 | | PUTIN | 35700.00 | | TOOSK | 36000.00 | | CARNEGIE | 36000.00 | +----------+----------+
5. List the emps who joined in the month of which second character is 'a'.
+--------+----------+ | ename | hiredate | +--------+----------+ | BLAIR | May | | FORD | January | | ELISON | January | +--------+----------+
6. List the emps whose Sal is four digit number ending with Zero.
+----------+---------+ | ename | sal | +----------+---------+ | BUSH | 5000.00 | | BLAIR | 2850.00 | | MERKEL | 2450.00 | | CHIRACK | 1250.00 | | BAROSSO | 1600.00 | | GATES | 1500.00 | | WALTON | 1250.00 | | TOOSK | 3000.00 | | CARNEGIE | 3000.00 | | FORD | 1100.00 | | ELISON | 1300.00 | +----------+---------+
7. List all the emps who joined before or after 1981.
+----------+------------+ | ename | HIREDATE | +----------+------------+ | THATCHER | 1980-12-17 | | CARNEGIE | 1982-12-09 | | FORD | 1983-01-12 | | ELISON | 1982-01-23 | +----------+------------+
8. List the emps who joined in any year but not in Dec.
+---------+------------+ | ename | hiredate | +---------+------------+ | BUSH | 1981-11-17 | | BLAIR | 1981-05-01 | | MERKEL | 1981-06-09 | | PUTIN | 1981-04-02 | | CHIRACK | 1981-09-28 | | BAROSSO | 1981-02-20 | | GATES | 1981-09-08 | | WALTON | 1981-02-22 | | FORD | 1983-01-12 | | ELISON | 1982-01-23 | +---------+------------+
9. Display the location of TOOSK
+---------+------------+ | ename | loc | +---------+------------+ | TOOSK | LONDON | +---------+------------+
10. *List the EMPS who are not working under the president AND HAVE EMPLOYEES.
+------+----------+---------+------+ | id | ename | job | mgr | +------+----------+---------+------+ | 7902 | TOOSK | ANALYST | 7566 | | 7788 | CARNEGIE | ANALYST | 7566 | +------+----------+---------+------+
11. List those Emps whose Salary is odd value (unpaired)
+-------+---------+ | ename | sal | +-------+---------+ | PUTIN | 2975.00 | +-------+---------+
12. **List THE Name of department where highest no. of employees are working
+------+----------+---------+------------+ | No | Name | City | No of emps | +------+----------+---------+------------+ | 30 | SALES | PARIS | 6 | +------+----------+---------+------------+
13. *Produce below report (sum)
+------+--------------------+------------+ | DEP | NAZ | SAL | +------+--------------------+------------+ | 10 | * All employees * | 8750.00 | | 10 | BUSH | 5000.00 | | 10 | ELISON | 1300.00 | | 10 | MERKEL | 2450.00 | | 20 | * All employees * | 10875.00 | | 20 | CARNEGIE | 3000.00 | | 20 | FORD | 1100.00 | | 20 | PUTIN | 2975.00 | | 20 | THATCHER | 800.00 | | 20 | TOOSK | 3000.00 | +------+--------------------+------------+
14. **Yet another report (sum of sums)
+--------------------+-------------------+------------+ | DEP | NAZ | SAL | +--------------------+-------------------+------------+ | * Both 10 and 20 * | * All employees * | 19625.00 | | 10 | * All employees * | 8750.00 | | 10 | BUSH | 5000.00 | | 10 | ELISON | 1300.00 | | 10 | MERKEL | 2450.00 | | 20 | * All employees * | 10875.00 | | 20 | CARNEGIE | 3000.00 | | 20 | FORD | 1100.00 | | 20 | PUTIN | 2975.00 | | 20 | THATCHER | 800.00 | | 20 | TOOSK | 3000.00 | +--------------------+-------------------+------------+
