SQL Exercises
Jump to navigation
Jump to search
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 | +--------------------+-------------------+------------+
DDL, DML
- 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
- Modify table spies
- add new column special, string, describes special agent abilities
- add new index spy, built with columns active and missions
- 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
- Update 2 rows with some special abilities
- 1st row for Paris - big guns
- 2nd for New York - lockpicks
- Delete only not active spies
- discussion (-:
- 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)
- built-in column type in SQLServer
- supports searching hierarchies with built-in functions
- 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/') ;
- Find all the levels (=
- Get ancestors (first parent)
- HOMEWORK: Make it better, accordingly to below instructions
