SQL Exercises: Difference between revisions
Jump to navigation
Jump to search
Lsokolowski1 (talk | contribs) |
Lsokolowski1 (talk | contribs) |
||
| (11 intermediate revisions by the same user not shown) | |||
| Line 224: | Line 224: | ||
=== hierarchyid (''mssql'') === | === hierarchyid (''mssql'') === | ||
# '''built-in''' column '''type''' in ''SQLServer'' | # '''built-in''' column '''type''' in ''SQLServer'' | ||
#* supports searching hierarchies with built-in functions | #* supports '''searching''' hierarchies with '''built-in functions''' | ||
# Create this table with hierarchy | #* related docs | ||
#** in '''general''' - <small>https://learn.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver16</small> | |||
#** supported '''functions'''(methods) - <small>https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver16</small> | |||
# Create this table with hierarchy:<syntaxhighlight lang="sql"> | |||
CREATE TABLE Emps ( | CREATE TABLE Emps ( | ||
EmpID INT PRIMARY KEY, | EmpID INT PRIMARY KEY, | ||
| Line 245: | Line 248: | ||
; | ; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
# HOMEWORK: Make it better, accordingly to below instructions | ## Find '''all''' the levels (= | ||
## Get '''ancestors''' (first parent) | |||
## Get '''root''' node | |||
## Find all '''descendants''' of Coraline (-: | |||
# '''HOMEWORK''': Make it '''better''', accordingly to below instructions | |||
#* <small>https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-2-creating-and-managing-data-in-a-hierarchical-table?view=sql-server-ver16</small> | #* <small>https://learn.microsoft.com/en-us/sql/relational-databases/tables/lesson-2-creating-and-managing-data-in-a-hierarchical-table?view=sql-server-ver16</small> | ||
# '''Homework2''': Improve '''emp''' table same way (--: | |||
<!-- | <!-- | ||
CREATE TABLE spies ( | CREATE TABLE spies ( | ||
| Line 329: | Line 336: | ||
order by 1, 2 | order by 1, 2 | ||
; | ; | ||
SELECT | |||
EmpID, EmpName | |||
, ManagerID.GetLevel() AS Level | |||
, ManagerID.GetAncestor(1).ToString() AS [1stParent] | |||
FROM Emps | |||
ORDER BY ManagerID; | |||
SELECT ManagerID.ToString() AS Text_HierNode, * | |||
FROM emps | |||
WHERE ManagerID = hierarchyid::GetRoot(); | |||
DECLARE @Manager hierarchyid | |||
SELECT @Manager = ManagerID FROM emps | |||
WHERE EmpID = 2; | |||
SELECT * FROM emps | |||
WHERE ManagerID.IsDescendantOf(@Manager) = 1; | |||
--> | --> | ||
<!-- 121 --> | <!-- 121 --> | ||
Latest revision as of 07:00, 9 September 2024
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
- related docs
- 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)
- Get root node
- Find all descendants of Coraline (-:
- HOMEWORK: Make it better, accordingly to below instructions
- Homework2: Improve emp table same way (--:
