eFKJDApJ

1show databases;
2use 19pw33;
3
4CREATE TABLE IF NOT EXISTS Employee
5(
6    ENO        BIGINT,
7    FNAME      VARCHAR(50) NOT NULL,
8    LNAME      VARCHAR(50) NOT NULL,
9    DESIG      VARCHAR(50),
10    MGR        BIGINT,
11    DOB        DATE,
12    HDATE      DATE,
13    SALARY     NUMERIC(8, 2),
14    COMM NUMERIC(8, 2),
15    PRIMARY KEY (ENO),
16    FOREIGN KEY (MGR) REFERENCES Employee (ENO),
17    CONSTRAINT DOB_Check CHECK ( YEAR(DOB) <= 1980 ),
18    CONSTRAINT Salary_Check CHECK ( SALARY BETWEEN 20000 AND 100000 )
19);
20
21
22INSERT INTO Employee
23VALUES (101, 'Janson', 'Hass', 'President', NULL, STR_TO_DATE('12/09/1973', '%d/%m/%Y'),
24        STR_TO_DATE('12/01/2003', '%d/%m/%Y'), 87500.00, NULL),
25       (110, 'James', 'Thompson', 'Manager', 101, STR_TO_DATE('06/01/1973', '%d/%m/%Y'),
26        STR_TO_DATE('25/02/2008', '%d/%m/%Y'), 52950.00, NULL),
27       (115, 'Celia', 'Geyer', 'Manager', 101, STR_TO_DATE('07/09/1972', '%d/%m/%Y'),
28        STR_TO_DATE('17/09/2007', '%d/%m/%Y'), 46850.00, 22530.00),
29       (117, 'Robert', 'Stern', 'Manager', 101, STR_TO_DATE('04/02/1972', '%d/%m/%Y'),
30        STR_TO_DATE('15/03/2006', '%d/%m/%Y'), 49900.00, 20555.00),
31       (135, 'Linda', 'Pulaski', 'Salesman', 110, STR_TO_DATE('29/08/1968', '%d/%m/%Y'),
32        STR_TO_DATE('19/09/2007', '%d/%m/%Y'), 21250.00, 14500.00),
33       (142, 'David', 'Henderson', 'Salesman', 110, STR_TO_DATE('02/02/1970', '%d/%m/%Y'),
34        STR_TO_DATE('23/03/2006', '%d/%m/%Y'), 21700.00, 12400.00),
35       (167, 'Martin', 'Spenser', 'Salesman', 110, STR_TO_DATE('09/02/1971', '%d/%m/%Y'),
36        STR_TO_DATE('28/07/2005', '%d/%m/%Y'), 27000.00, 16600.00),
37       (172, 'Smith', 'Nicholls', 'Clerk', 110, STR_TO_DATE('12/03/1965', '%d/%m/%Y'),
38        STR_TO_DATE('26/04/2003', '%d/%m/%Y'), 21850.00, NULL),
39       (154, 'Larry', 'Adamson', 'Salesman', 110, STR_TO_DATE('02/02/1974', '%d/%m/%Y'),
40        STR_TO_DATE('18/02/2004', '%d/%m/%Y'), 22250.00, 17000.00),
41       (179, 'Mathews', 'Pinka', 'Analyst Manager', 117, STR_TO_DATE('08/01/1975', '%d/%m/%Y'),
42        STR_TO_DATE('23/04/2008', '%d/%m/%Y'), 35000.00, NULL),
43       (183, 'Sally', 'Brown', 'Clerk', 179, STR_TO_DATE('29/08/1968', '%d/%m/%Y'),
44        STR_TO_DATE('14/07/2008', '%d/%m/%Y'), 21250.00, NULL),
45       (190, 'Christine', 'Jones', 'Analyst Manager', 117, STR_TO_DATE('13/09/1968', '%d/%m/%Y'),
46        STR_TO_DATE('05/09/2005', '%d/%m/%Y'), 34000.00, NULL),
47       (199, 'Michael', 'Parker', 'Clerk', 190, STR_TO_DATE('07/02/1971', '%d/%m/%Y'),
48        STR_TO_DATE('17/03/2004', '%d/%m/%Y'), 21600.00, NULL),
49       (200, 'John', 'Mehta', 'Clerk', 115, STR_TO_DATE('02/03/1975', '%d/%m/%Y'),
50        STR_TO_DATE('12/02/2006', '%d/%m/%Y'), 21350.00, NULL),
51       (137, 'Bruce', 'Wong', 'Salesman', 110, STR_TO_DATE('12/12/1976', '%d/%m/%Y'),
52        STR_TO_DATE('18/10/2005', '%d/%m/%Y'), 22250.00, 13000.00);
53
54SELECT * FROM Employee;
55DROP TABLE Employee;
56
57# 1
58SELECT * FROM Employee WHERE TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP) < (SELECT TIMESTAMPDIFF(year, DOB, current_timestamp) FROM Employee WHERE ENO = 167);
59
60# 2
61SELECT FNAME, LNAME FROM Employee WHERE TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP);
62
63# 3
64SELECT * FROM Employee WHERE LNAME REGEXP 'er';
65
66# 4
67SELECT CONCAT(FNAME, ' ', LNAME) AS NAME FROM Employee WHERE FNAME REGEXP '^[MJ]';
68
69# 5
70SELECT FNAME, LNAME, SALARY FROM Employee WHERE DESIG IN ('Analyst Manager', 'Manager');
71
72# 6
73SELECT * FROM Employee WHERE TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP) = (SELECT DISTINCT MAX(TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP)) FROM Employee);
74
75# 7
76SELECT * FROM Employee WHERE DESIG NOT IN ('Manager', 'Analyst Manager');
77
78# 8
79SELECT * FROM Employee WHERE SALARY > 35000 AND YEAR(DOB) BETWEEN 1966 AND 1976;
80
81# 9
82SELECT ENO, FNAME, LNAME, DESIG, SALARY FROM Employee WHERE SALARY >= 35000 AND DESIG IN ('Manager', 'Analyst Manager');
83
84# 10
85SELECT ENO, FNAME, LNAME, DESIG, SALARY FROM Employee WHERE Commission >= 15000 AND DESIG IN ('Salesman', 'Manager');
86
87# 11
88SELECT DESIG, COUNT(*) AS COUNT FROM Employee GROUP BY DESIG;
89
90# 12
91SELECT ENO, LNAME, SALARY FROM Employee WHERE LNAME IN ('Thompson', 'Spenser', 'Geyer', 'Brown', 'Jones');
92
93# 13
94SELECT YEAR(HDATE), COUNT(*) AS Count FROM Employee GROUP BY YEAR(HDATE);
95
96# 14
97SELECT ENO, LNAME, SALARY, TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP) AS Age FROM Employee WHERE TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP) BETWEEN 30 AND 40;
98
99# 15
100SELECT FNAME FROM Employee WHERE MGR IN (SELECT ENO FROM Employee WHERE FNAME IN ('James', 'Robert', 'Celia'));
101
102# 16
103SELECT CONCAT(FNAME, ', ', LNAME, ', ', DESIG) FROM Employee;
104
105# 17
106SELECT MIN(TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP)) AS AGE FROM Employee WHERE COMM IS NOT NULL;
107
108# 18
109SELECT CONCAT('Dear, ', FNAME, ' you were born on ', DATE_FORMAT(DOB, '%d, %W-%M-%Y')) AS Description FROM Employee;
110
111# 19
112SELECT * FROM Employee WHERE (DESIG, SALARY) IN (SELECT DESIG, MAX(SALARY) AS SALARY FROM Employee GROUP BY DESIG);
113
114# 20
115SELECT ENO, FNAME, SALARY FROM Employee WHERE MONTHNAME(DOB) IN ('January', 'February', 'August');
116
117# 21
118SELECT FNAME, SALARY, COALESCE(COMM, 0) AS COMMISSION, SALARY + COALESCE(COMM, 0) AS REMUNATION FROM Employee;
119
120# 22
121SELECT FNAME, SALARY, COALESCE(COMM, 0) AS COMMISSION, SALARY + COALESCE(COMM, 0) AS REMUNATION FROM Employee;
122
123# 23
124SELECT FNAME, DESIG FROM Employee WHERE (DESIG, TIMESTAMPDIFF(YEAR, HDATE, CURRENT_TIMESTAMP)) IN (SELECT DESIG, MAX(TIMESTAMPDIFF(YEAR, HDATE, CURRENT_TIMESTAMP)) AS NAME FROM Employee GROUP BY DESIG);
125
126# 24
127SELECT * FROM Employee WHERE DESIG = 'Manager' AND SALARY > 40000 AND YEAR(DOB) < 1973;
128
129# 25
130SELECT * FROM Employee WHERE COMM IS NULL AND SALARY BETWEEN 25000 AND 35000;
131
132# 26
133SELECT * FROM Employee WHERE MONTH(DOB) = MONTH(HDATE);
134
135# 27
136SELECT ENO, FNAME, DATE_FORMAT(HDATE, '%M %d,%Y') AS HIRE_DATE FROM Employee;
137
138# 28
139SELECT ENO, CONCAT(FNAME, ' ', LNAME) AS NAME, DESIG, HDATE, TIMESTAMPDIFF(YEAR, HDATE, CURRENT_TIMESTAMP()) AS EXPERIENCE FROM Employee WHERE ENO IN (SELECT DISTINCT MGR FROM Employee);
140
141# 29
142SELECT ENO, CONCAT(FNAME, ' ', LNAME) AS NAME, SALARY FROM Employee WHERE TIMESTAMPDIFF(YEAR, HDATE, CURRENT_TIMESTAMP()) > 5 AND DESIG IN ('Manager', 'Analyst Manager') AND COMM IS NULL ORDER BY ENO;
143
144# 30
145SELECT * FROM Employee WHERE YEAR(HDATE) < (SELECT YEAR(HDATE) FROM Employee WHERE FNAME = 'Martin');
146
147# 31
148SELECT FNAME FROM Employee WHERE SALARY = (SELECT SALARY FROM Employee WHERE FNAME = 'Larry') AND FNAME != 'Larry';
149
150# 32
151SELECT CONCAT(FNAME, ' ', LNAME) AS NAME FROM Employee WHERE DESIG = (SELECT DESIG FROM Employee WHERE FNAME = 'James') AND FNAME != 'James' OR SALARY > (SELECT SALARY FROM Employee WHERE FNAME = 'Celia');
152
153# 33
154SELECT * FROM Employee WHERE SALARY > (SELECT AVG(SALARY) FROM Employee);
155
156# 34
157SELECT * FROM Employee WHERE DESIG = 'Salesman' ORDER BY YEAR(HDATE) DESC LIMIT 1;
158
159# 35
160SELECT * FROM Employee WHERE YEAR(HDATE) < (SELECT YEAR(HDATE) FROM Employee WHERE MGR = 110 ORDER BY YEAR(HDATE) DESC LIMIT 1);
161
162# 36
163SELECT * FROM Employee ORDER BY TIMESTAMPDIFF(YEAR, DOB, 1991) DESC LIMIT 1;
164
165# 37
166SELECT CONCAT(FNAME, ' ', LNAME) AS NAME FROM Employee WHERE SALARY >= (SELECT (MAX(SALARY) + MIN(SALARY))/2 FROM Employee);
167
168# 38
169SELECT CONCAT(FNAME, ' ', LNAME) AS NAME FROM Employee ORDER BY (SALARY + COALESCE(COMM, 0)) LIMIT 5;
170
171# 39
172SELECT CONCAT(FNAME, ' ', LNAME) AS NAME, SALARY, COMM FROM Employee ORDER BY (SALARY + COALESCE(COMM, 0)) DESC LIMIT 1;
173
174# 40
175SELECT CONCAT(FNAME, ' ', LNAME) AS NAME FROM Employee WHERE DAY(HDATE) IN (SELECT DAY(HDATE) FROM Employee GROUP BY DAY(HDATE) HAVING COUNT(*) > 1);
176
177# 41
178SELECT ENO FROM Employee WHERE MGR IS NULL;
179
180# 42
181SELECT ENO FROM Employee WHERE ENO = (SELECT MGR FROM Employee GROUP BY MGR ORDER BY COUNT(*) DESC LIMIT 1);
182
183# 43
184SELECT * FROM Employee WHERE (DESIG, HDATE) IN (SELECT DESIG, MAX(HDATE) FROM Employee GROUP BY DESIG ORDER BY MAX(HDATE));
185
186# 44
187SELECT FNAME, DESIG FROM Employee WHERE COMM IS NOT NULL AND SALARY = (SELECT MAX(SALARY) FROM Employee WHERE COMM IS NOT NULL);
188
189# 45
190SELECT * FROM Employee WHERE SALARY IN (SELECT SALARY FROM Employee GROUP BY SALARY HAVING COUNT(*) > 1);
191
192# 46
193SELECT AVG(SALARY), DESIG FROM Employee WHERE DESIG != 'President' GROUP BY DESIG ORDER BY AVG(SALARY) DESC LIMIT 1;
194
195# 47
196SELECT * FROM Employee WHERE ENO NOT IN (SELECT DISTINCT MGR FROM Employee WHERE MGR IS NOT NULL);
197
198# 48
199SELECT SUM((SALARY + COALESCE(COMM, 0))) AS REMUNERATION FROM Employee WHERE MGR = 110;
200
201# 49
202UPDATE Employee SET COMM = COMM + (0.1 * SALARY) WHERE MONTH(DOB) = MONTH(CURRENT_TIMESTAMP);
203
204# 50
205DELETE FROM Employee WHERE SALARY > 40000 AND DESIG NOT IN ('President', 'Manager');