-
Notifications
You must be signed in to change notification settings - Fork 1
/
SQL coding
122 lines (105 loc) · 4.72 KB
/
SQL coding
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
drop table EMPLOYEES;
drop table JOB_HISTORY;
drop table DEPARTMENTS;
drop table LOCATIONS;
drop table JOBS;
------------------------------------------
--DDL statement for table 'HR' database--
--------------------------------------------
CREATE TABLE EMPLOYEES (
EMP_ID CHAR(9) NOT NULL,
F_NAME VARCHAR(15) NOT NULL,
L_NAME VARCHAR(15) NOT NULL,
SSN CHAR(9),
B_DATE DATE,
SEX CHAR,
ADDRESS VARCHAR(30),
JOB_ID CHAR(9),
SALARY DECIMAL(10,2),
MANAGER_ID CHAR(9),
DEP_ID CHAR(9) NOT NULL,
PRIMARY KEY (EMP_ID));
CREATE TABLE JOB_HISTORY (
EMPL_ID CHAR(9) NOT NULL,
START_DATE DATE,
JOBS_ID CHAR(9) NOT NULL,
DEPT_ID CHAR(9),
PRIMARY KEY (EMPL_ID,JOBS_ID));
CREATE TABLE JOBS (
JOB_IDENT CHAR(9) NOT NULL,
JOB_TITLE VARCHAR(15) ,
MIN_SALARY DECIMAL(10,2),
MAX_SALARY DECIMAL(10,2),
PRIMARY KEY (JOB_IDENT));
CREATE TABLE DEPARTMENTS (
DEPT_ID_DEP CHAR(9) NOT NULL,
DEP_NAME VARCHAR(15) ,
MANAGER_ID CHAR(9),
LOC_ID CHAR(9),
PRIMARY KEY (DEPT_ID_DEP));
CREATE TABLE LOCATIONS (
LOCT_ID CHAR(9) NOT NULL,
DEP_ID_LOC CHAR(9) NOT NULL,
PRIMARY KEY (LOCT_ID,DEP_ID_LOC));
------------------------------------------
-- Query 1: Retrieve all employees whose address is in Elgin,IL
SELECT F_NAME , L_NAME
FROM EMPLOYEES
WHERE ADDRESS LIKE '%Elgin,IL%' ;
------------------------------------------
-- Query 2: Retrieve all employees who were born during the 1970's.
SELECT F_NAME , L_NAME
FROM EMPLOYEES
WHERE B_DATE LIKE '197%' ;
------------------------------------------
-- Query 3: Retrieve all employees in department 5 whose salary is between 60000 and 70000 .
SELECT * FROM EMPLOYEES
WHERE (SALARY BETWEEN 60000 AND 70000) AND DEP_ID = 5 ;
------------------------------------------
-- Query 4A: Retrieve a list of employees ordered by department ID.
select F_NAME, L_NAME, DEP_ID
from EMPLOYEES
order by DEP_ID;
------------------------------------------
-- Query 4B: Retrieve a list of employees ordered in descending order by department ID and within each department ordered alphabetically in descending order by last name.
select F_NAME, L_NAME, DEP_ID
from EMPLOYEES
order by DEP_ID desc, L_NAME desc;
------------------------------------------
-- Query 5A: For each department ID retrieve the number of employees in the department.
select DEP_ID, COUNT(*)
from EMPLOYEES
group by DEP_ID;
------------------------------------------
-- Query 5B: For each department retrieve the number of employees in the department, and the average employees salary in the department.
select DEP_ID, COUNT(*), AVG(SALARY)
from EMPLOYEES
group by DEP_ID;
------------------------------------------
-- Query 5C: Label the computed columns in the result set of Query 5B as NUM_EMPLOYEES and AVG_SALARY.
select DEP_ID, COUNT(*) AS "NUM_EMPLOYEES", AVG(SALARY) AS "AVG_SALARY"
from EMPLOYEES
group by DEP_ID;
--Query5D--
------------------------------------------
-- Query 5D: In Query 5C order the result set by Average Salary.
select DEP_ID, COUNT(*) AS "NUM_EMPLOYEES", AVG(SALARY) AS "AVG_SALARY"
from EMPLOYEES
group by DEP_ID
order by AVG_SALARY;
------------------------------------------
-- Query 5E: In Query 5D limit the result to departments with fewer than 4 employees.
SELECT DEP_ID, COUNT(*) AS "NUM_EMPLOYEES", AVG(SALARY) AS "AVG_SALARY"
FROM EMPLOYEES
GROUP BY DEP_ID
HAVING COUNT(*) < 4
ORDER BY AVG_SALARY;
SELECT DEP_ID, NUM_EMPLOYEES, AVG_SALARY FROM
( SELECT DEP_ID, COUNT(*) AS NUM_EMPLOYEES, AVG(SALARY) AS AVG_SALARY FROM
order by AVG_SALARY;
------------------------------------------
-- Query 6: Similar to 4B but instead of department ID use department name. Retrieve a list of employees ordered by department name, and within each department ordered alphabetically in descending order by last name.
SELECT D.DEP_NAME , E.F_NAME, E.L_NAME
FROM EMPLOYEES as E, DEPARTMENTS AS D
WHERE E.DEP_ID = D.DEPT_ID_DEP
ORDER BY D.DEP_NAME, E.L_NAME DESC ;