SQL Exercises: Difference between revisions
Jump to navigation
Jump to search
Lsokolowski1 (talk | contribs) m (→SQL Exercises) |
Lsokolowski1 (talk | contribs) mNo edit summary |
||
| Line 183: | Line 183: | ||
+--------------------+-------------------+------------+ | +--------------------+-------------------+------------+ | ||
<br> | |||
== 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 spie'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 | |||
# 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 | |||
<!-- | |||
CREATE TABLE spies ( | |||
ID VARCHAR(3) NOT NULL, | |||
LOC VARCHAR(13) NOT NULL, | |||
ACTIVE NUMBER(1) DEFAULT 0 NOT NULL, | |||
MISSIONS NUMBER(3), | |||
CONSTRAINT SPIES_PRIMARY_KEY PRIMARY KEY (LOC) | |||
); | |||
-- DROP TABLE SPIES; | |||
-- CREATE UNIQUE INDEX SpyLoc ON dept(loc); | |||
ALTER TABLE dept | |||
ADD CONSTRAINT DEPT_FOREIGN_KEY FOREIGN KEY (loc) REFERENCES spies(loc) -- ON DELETE CASCADE | |||
; | |||
ALTER TABLE dept | |||
DROP CONSTRAINT DEPT_FOREIGN_KEY | |||
; | |||
ALTER TABLE dept | |||
ADD CONSTRAINT DEPT_FOREIGN_KEY FOREIGN KEY (loc) REFERENCES spies(loc) ON DELETE CASCADE | |||
; | |||
ALTER TABLE spies | |||
ADD (special VARCHAR(20)) | |||
; | |||
CREATE INDEX spies_spy ON spies(active, missions); | |||
INSERT INTO spies(ID, LOC, ACTIVE) | |||
VALUES ('004', 'NEW YORK', 1); | |||
INSERT INTO spies(ID, LOC, ACTIVE) | |||
VALUES ('003', 'LONDON', 1); | |||
INSERT INTO spies(ID, LOC, ACTIVE) | |||
VALUES ('002', 'PARIS', 0); | |||
INSERT INTO spies(ID, LOC, ACTIVE) | |||
VALUES ('006', 'BERLIN', 1); | |||
INSERT INTO dept(DEPTNO, DNAME, LOC) | |||
VALUES (50, 'IT', 'PARIS'); | |||
INSERT INTO dept(DEPTNO, DNAME, LOC) | |||
VALUES (40, 'OPERATIONS', 'BERLIN'); | |||
UPDATE spies | |||
SET special = 'big guns' | |||
WHERE loc = 'PARIS'; | |||
DELETE FROM spies | |||
-- select * from spies | |||
WHERE loc = 'PARIS' | |||
; | |||
DELETE FROM dept | |||
-- select * from dept | |||
WHERE loc = 'PARIS' -- and deptno = 50 | |||
; | |||
CREATE VIEW SUMS_REPORT | |||
AS select to_char(deptno) DEP, ename NAZ, sal SAL | |||
from emp | |||
where deptno in (10,20) | |||
union | |||
select to_char(deptno) DEP, '* All employees *' NAZ, sum(sal) SAL | |||
from emp | |||
where deptno in (10,20) | |||
group by deptno | |||
union | |||
select '* All deps *' DEP, '* All employees *' NAZ, sum(sal) SAL | |||
from emp | |||
where deptno in (10,20) | |||
order by DEP, NAZ WITH READ ONLY; | |||
select sr.*, e.job, s.grade | |||
from sums_report sr left join emp e on sr.naz = e.ename | |||
left join salgrade s on e.sal between s.losal and s.hisal | |||
order by 1, 2 | |||
; | |||
--> | |||
<!-- 121 --> | <!-- 121 --> | ||
<br> | <br> | ||
[[SQL_Programming|Back to SQL materials]] | [[SQL_Programming|Back to SQL materials]] | ||
<br><br> | <br><br> | ||
Revision as of 23:39, 11 October 2023
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 spie'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
- 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
