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.

Back to SQL materials

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

DDL, DML

1. Add new table spies to mike db
   • column id, 3 chars only, can't be empty - '007', '006', '013', etc
   • column loc, same as in dept, it should be referable from dept.loc
     • how to do that? HINT: try to alter dept table first, what can we say?
     • fix it - how? HINT: insert all the possible refs first and then alter dept
   • column active, boolean, can't be empty
   • column missions, max 3 digits number - amount of spy's missions
2. Modify table spies
   • add new column special, string, describes special agent abilities
   • add new index spy, built with columns active and missions
3. Insert 4 rows into spies table
   • 2 rows with full syntax
   • 2 rows with short syntax
   • make sure there is at least 1 row with not active spy
4. Update 2 rows with some special abilities
   • 1st row for Paris - big guns
   • 2nd for New York - lockpicks
5. Delete only not active spies
   • discussion (-:
6. Create read-only view called SUMS_REPORT with the result of query from additional exercise no 14
   • Query the view and show also job and grade

DEP                                      NAZ                      SAL JOB            GRADE
---------------------------------------- ----------------- ---------- --------- ----------
* All deps *                             * All employees *      19625                     
10                                       * All employees *       8750                     
10                                       BUSH                    5000 PRESIDENT          5
10                                       ELISON                  1300 CLERK              2
10                                       MERKEL                  2450 MANAGER            4
20                                       * All employees *      10875                     
20                                       CARNEGIE                3000 ANALYST            4
20                                       FORD                    1100 CLERK              1
20                                       PUTIN                   2975 MANAGER            4
20                                       THATCHER                 800 CLERK              1
20                                       TOOSK                   3000 ANALYST            4

11 rows selected.

hierarchyid (mssql)

1. built-in column type in SQLServer
   • supports searching hierarchies with built-in functions
   • related docs
     • in general - https://learn.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver16
     • supported functions(methods) - https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver16
2. Create this table with hierarchy:

   CREATE TABLE Emps (
       EmpID INT PRIMARY KEY,
       EmpName VARCHAR(100),
       ManagerID hierarchyid
   );
   
   
   CREATE UNIQUE INDEX EmpsMgrNc1
   ON Emps(ManagerID);
   GO
   
   INSERT INTO Emps (EmpID, EmpName, ManagerID)
   VALUES (1, 'Luke Falcon', NULL),
          (2, 'Monique Falcon', '/'),
          (3, 'John Falcon', '/1/1/'),
          (4, 'Coraline Falcon', '/1/'),
   	   (5, 'Mike Falcon', '/2/')
   	   ;
   

   1. Find all the levels (=
   2. Get ancestors (first parent)
   3. Get root node
   4. Find all descendants of Coraline (-:
3. HOMEWORK: Make it better, accordingly to below instructions
   • https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-2-creating-and-managing-data-in-a-hierarchical-table?view=sql-server-ver16
4. Homework2: Improve emp table same way (--:

Back to SQL materials