· 5 years ago · Oct 05, 2020, 05:02 AM
1
2CREATE TABLE IF NOT EXISTS EMPTABLE
3(
4 Empid NUMERIC(6) PRIMARY KEY,
5 Name VARCHAR(10) NOT NULL,
6 City VARCHAR(10),
7 DOB DATE NOT NULL,
8 Sex CHARACTER(1),
9 DOJ DATE NOT NULL,
10 Salary NUMERIC(8, 2),
11 Department VARCHAR(10),
12 CONSTRAINT Name_Check CHECK (ASCII(LEFT(Name, 1)) BETWEEN ASCII('A') AND ASCII('Z')),
13 CONSTRAINT City_Check CHECK (ASCII(LEFT(City, 1)) BETWEEN ASCII('A') AND ASCII('Z')),
14 CONSTRAINT Sex_Check CHECK (Sex in ('M', 'F')),
15 CONSTRAINT Salary_Check CHECK (Salary BETWEEN 15000 AND 50000),
16 CONSTRAINT Department_Check CHECK (Department in ('ACC', 'FIN', 'EDP'))
17);
18
19INSERT INTO EMPTABLE
20VALUES (1001, 'Anish', 'Chennai', STR_TO_DATE('21-apr-1966', '%d-%b-%Y'), 'M', STR_TO_DATE('10-jun-1986', '%d-%b-%Y'),
21 17000, 'ACC'),
22 (1002, 'Anandi', 'Ooty', STR_TO_DATE('11-sep-1975', '%d-%b-%Y'), 'F', STR_TO_DATE('17-jun-1997', '%d-%b-%Y'),
23 15000,
24 'FIN'),
25 (1003, 'Shiva', 'Kambam', STR_TO_DATE('07-feb-1966', '%d-%b-%Y'), 'M', STR_TO_DATE('21-apr-1991', '%d-%b-%Y'),
26 21000, 'EDP'),
27 (1004, 'Deepa', 'Trichi', STR_TO_DATE('14-feb-1967', '%d-%b-%Y'), 'M', STR_TO_DATE('21-apr-1990', '%d-%b-%Y'),
28 15000, 'FIN'),
29 (1005, 'Engela', NULL, STR_TO_DATE('23-mar-1970', '%d-%b-%Y'), 'F', STR_TO_DATE('21-apr-1986', '%d-%b-%Y'),
30 32000, 'EDP'),
31 (1006, 'Krish', 'Trichi', STR_TO_DATE('14-jul-1970', '%d-%b-%Y'), 'F', STR_TO_DATE('21-mar-1989', '%d-%b-%Y'),
32 15000, 'ACC'),
33 (1007, 'Kamal', 'Ooty', STR_TO_DATE('14-jul-1966', '%d-%b-%Y'), 'M', STR_TO_DATE('04-mar-1990', '%d-%b-%Y'),
34 15000,
35 'FIN'),
36 (1008, 'Neela', 'Madurai', STR_TO_DATE('27-jun-1969', '%d-%b-%Y'), 'F', STR_TO_DATE('07-apr-1990', '%d-%b-%Y'),
37 25000, 'EDP'),
38 (1009, 'Bala', 'Coimbatore', STR_TO_DATE('29-oct-1962', '%d-%b-%Y'), 'M', STR_TO_DATE('21-apr-1993', '%d-%b-%Y'),
39 20000, 'ACC'),
40 (1010, 'Rajesh', 'Pollachi', STR_TO_DATE('21-apr-1966', '%d-%b-%Y'), 'M', STR_TO_DATE('12-oct-1993', '%d-%b-%Y'),
41 17000, 'FIN'),
42 (1011, 'Rekha', 'Coimbatore', STR_TO_DATE('21-apr-1970', '%d-%b-%Y'), 'F',
43 STR_TO_DATE('14-dec-1999', '%d-%b-%Y'),
44 20000, 'ACC'),
45 (1012, 'Xavier', 'Madurai', STR_TO_DATE('21-apr-1971', '%d-%b-%Y'), 'M', STR_TO_DATE('12-mar-1997', '%d-%b-%Y'),
46 20000, 'ACC'),
47 (1013, 'Vinotha', 'Trichi', STR_TO_DATE('21-apr-1972', '%d-%b-%Y'), 'F', STR_TO_DATE('21-apr-1994', '%d-%b-%Y'),
48 17000, 'ACC');
49
50# QN 3-01
51SELECT *
52FROM EMPTABLE;
53
54# QN 3-02
55SELECT Empid, Name, City, Salary
56FROM EMPTABLE;
57
58# QN 3-03
59SELECT *
60FROM EMPTABLE
61WHERE Name = 'Bala';
62
63# QN 3-04
64SELECT *
65FROM EMPTABLE
66WHERE Sex = 'M';
67
68# QN 3-05
69SELECT *
70FROM EMPTABLE
71WHERE City = 'Coimbatore';
72
73# QN 3-06
74SELECT *
75FROM EMPTABLE
76WHERE City IN ('Coimbatore', 'Trichi', 'Chennai');
77
78# QN 3-07
79SELECT Name
80FROM EMPTABLE
81WHERE City IS NULL;
82
83# QN 3-08
84SELECT DISTINCT City
85FROM EMPTABLE;
86
87# QN 3-09
88SELECT Empid, Salary * 2
89FROM EMPTABLE;
90
91# QN 3-10
92SELECT Name, City
93FROM EMPTABLE
94WHERE Salary > 50000;
95
96# QN 3-11
97SELECT Empid, Name, Salary * 1.05 AS 'Increased Salary'
98FROM EMPTABLE;
99
100# QN 3-12
101SELECT Empid, Name, Salary * 1.05 AS 'Increased Salary'
102FROM EMPTABLE
103ORDER BY `Increased Salary` DESC;
104
105# QN 3-13
106SELECT *
107FROM EMPTABLE
108ORDER BY Name, Salary DESC;
109
110# QN 3-14
111SELECT COUNT(*)
112FROM EMPTABLE;
113
114# QN 3-15
115SELECT DISTINCT COUNT(Department)
116FROM EMPTABLE;
117
118# QN 3-16
119SELECT COUNT(*)
120FROM EMPTABLE
121WHERE Department = 'FIN';
122
123# QN 3-17
124SELECT COUNT(*)
125FROM EMPTABLE
126WHERE Sex = 'M';
127
128# QN 3-18
129SELECT Name
130FROM EMPTABLE
131WHERE Salary = (SELECT MAX(Salary) FROM EMPTABLE);
132
133# QN 3-19
134SELECT Name
135FROM EMPTABLE
136WHERE Salary = (SELECT MIN(Salary) FROM EMPTABLE);
137
138# QN 3-20
139SELECT Department, SUM(Salary)
140FROM EMPTABLE
141GROUP BY Department;
142
143# QN 3-21
144SELECT Empid
145FROM EMPTABLE
146WHERE Empid != 1002;
147
148# QN 3-22
149SELECT *
150FROM EMPTABLE
151WHERE ASCII(LEFT(Name, 1)) = ASCII('A');
152
153# QN 3-23
154SELECT *
155FROM EMPTABLE
156WHERE DOB < DATE(STR_TO_DATE('01-jan-91', '%d-%b-%y'));
157
158# QN 3-24
159SELECT Name
160FROM EMPTABLE
161WHERE Salary BETWEEN 20000 AND 40000;
162
163# QN 3-25
164SELECT *
165FROM EMPTABLE
166WHERE Salary > 20000;
167
168# QN 3-26
169SELECT *
170FROM EMPTABLE
171WHERE Salary > 20000
172 AND Department = 'ACC';
173
174# QN 3-27
175SELECT *
176FROM EMPTABLE
177WHERE Salary > 20000;
178
179# QN 3-28
180SELECT NAME, SALARY, DEPARTMENT
181FROM EMPTABLE
182WHERE SALARY IN (SELECT MIN(SALARY) FROM emptable GROUP BY DEPARTMENT);
183
184# QN 3-29
185SELECT Department, COUNT(*)
186FROM EMPTABLE
187WHERE Sex = 'M'
188GROUP BY Department;
189
190# QN 3-30
191SELECT DEPARTMENT, MIN(SALARY), MAX(SALARY), SUM(SALARY), AVG(SALARY)
192FROM EMPTABLE
193GROUP BY DEPARTMENT;
194
195#QN 3-31
196SELECT TIMESTAMPDIFF(DAY, DOJ, CURRENT_TIMESTAMP)
197FROM EMPTABLE;
198
199# QN 3-32
200SELECT TIMESTAMPDIFF(YEAR, DOJ, CURRENT_TIMESTAMP)
201FROM EMPTABLE;
202
203# QN 3-33
204SELECT NAME, TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP) AS 'AGE'
205FROM EMPTABLE;
206
207# QN 3-34
208SELECT NAME
209FROM EMPTABLE
210WHERE DOB = (SELECT MIN(DOB) FROM EMPTABLE);
211
212# QN 3-35
213SELECT TIMESTAMPDIFF(YEAR, DOb, CURRENT_TIMESTAMP) AS 'AGE'
214FROM EMPTABLE
215WHERE DOB = (SELECT MIN(DOB) FROM EMPTABLE);
216
217# QN 3-36
218SELECT Name, City
219FROM EMPTABLE
220ORDER BY Name, City;
221
222# QN 3-37
223SELECT Name, DOB
224FROM EMPTABLE
225WHERE MONTH(DOB) = '01';
226
227# QN 3-38
228SELECT Department, COUNT(*)
229FROM EMPTABLE
230GROUP BY Department;
231
232# QN 3-39
233SELECT Sex, AVG(TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP))
234FROM EMPTABLE
235GROUP BY Sex;
236
237# QN 3-40
238SELECT Name, DOB
239FROM EMPTABLE
240WHERE MONTH(DOB) = MONTH(CURDATE());
241
242# QN 3-41
243SELECT AVG(Salary)
244FROM EMPTABLE;
245
246# QN 3-42
247SELECT Department, AVG(Salary)
248FROM EMPTABLE
249GROUP BY Department;
250
251# QN 3-43 Repeated
252SELECT Department, SUM(Salary)
253FROM EMPTABLE
254GROUP BY Department;
255
256# QN 3-44
257SELECT COUNT(*)
258FROM EMPTABLE
259WHERE Salary > 30000;
260
261# QN 3-45
262SELECT MAX(Salary)
263FROM EMPTABLE;
264
265# QN 3-46
266SELECT Department, MAX(Salary)
267FROM EMPTABLE
268GROUP BY Department;
269
270# QN 3-47
271SELECT COUNT(*)
272FROM EMPTABLE
273WHERE City = 'Coimbatore'
274 AND Sex = 'F';
275
276# QN 3-48
277SELECT *
278FROM EMPTABLE
279WHERE MONTH(DOB) NOT IN (3, 10);
280
281# QN 3-49
282SELECT Department, COUNT(*) AS 'Female Employees'
283FROM EMPTABLE
284WHERE Sex = 'F'
285GROUP BY Department;
286
287# QN 3-50
288SELECT Name, City, DOB
289FROM EMPTABLE;
290
291# QN 3-51
292SELECT MAX(Salary) - MIN(Salary) AS Difference
293FROM EMPTABLE;
294
295# QN 3-52
296SELECT Department
297FROM EMPTABLE
298GROUP BY Department
299ORDER BY COUNT(Department) DESC
300LIMIT 1;
301
302# QN 3-53
303SELECT Department
304FROM EMPTABLE
305GROUP BY Department
306ORDER BY COUNT(*)
307LIMIT 1;
308
309# QN 3-54
310SELECT Name
311FROM EMPTABLE
312WHERE DOB = (SELECT DOB FROM EMPTABLE WHERE YEAR(DOB) = 1990 AND Sex = 'M' ORDER BY DOB LIMIT 1);
313
314# QN 3-55
315SELECT Name
316FROM EMPTABLE
317WHERE DOB = (SELECT DOB FROM EMPTABLE WHERE YEAR(DOB) = 1990 AND Sex = 'F' ORDER BY DOB DESC LIMIT 1);
318
319# QN 3-56
320SELECT Name
321FROM EMPTABLE
322WHERE TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP) < 32;
323
324# QN 3-57
325SELECT Name
326FROM EMPTABLE
327WHERE TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP) <
328 (SELECT TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP) FROM EMPTABLE WHERE Empid = 1007);
329
330# QN 3-58
331SELECT YEAR(DOB)
332FROM EMPTABLE
333GROUP BY YEAR(DOB)
334ORDER BY COUNT(DOB) DESC
335LIMIT 1;
336
337# QN 3-59
338SELECT MONTH(DOB)
339FROM EMPTABLE
340GROUP BY MONTH(DOB)
341ORDER BY COUNT(DOB) DESC
342LIMIT 1;
343
344# QN 3-60
345SELECT Name
346FROM EMPTABLE
347WHERE Sex = 'M'
348 AND Salary < (SELECT AVG(Salary) FROM EMPTABLE WHERE Sex = 'F');
349
350# QN 3-61
351SELECT *
352FROM EMPTABLE
353WHERE Salary IN (SELECT Salary FROM EMPTABLE GROUP BY Salary HAVING COUNT(Salary) > 1);
354
355# QN 3-62
356SELECT COUNT (DISTINCT DEPARTMENT)
357FROM EMPTABLE;
358
359# QN 3-63
360SELECT *
361FROM EMPTABLE
362WHERE LENGTH(NAME) <= 5
363
364# QN 3-64
365SELECT Name
366FROM EMPTABLE
367WHERE Name LIKE '%e%';
368
369# QN 3-65
370SELECT *
371FROM EMPTABLE
372WHERE City = 'Trichi'
373 AND Department IN
374 (SELECT Department FROM EMPTABLE WHERE City = 'Trichi' GROUP BY Department HAVING COUNT(Department) = 1);
375
376# QN 3-66
377SELECT *
378FROM EMPTABLE
379ORDER BY City;
380
381# QN 3-67
382SELECT CITY, COUNT(*)
383FROM EMPTABLE
384GROUP BY CITY
385
386# QN 3-68
387SELECT *
388FROM EMPTABLE
389WHERE (City, Salary) IN
390 (SELECT City, Salary FROM EMPTABLE GROUP BY City, Salary HAVING COUNT(Salary) > 1 AND COUNT(City) > 1);
391
392# QN 3-69
393SELECT DISTINCT Department
394FROM EMPTABLE
395WHERE Department NOT IN (SELECT DISTINCT Department FROM EMPTABLE WHERE City = 'Trichi');
396
397# QN 3-70
398# Dunno
399
400# QN 3-71
401SELECT *
402FROM EMPTABLE
403WHERE Sex = 'F'
404 AND Salary IN (SELECT Salary FROM EMPTABLE GROUP BY Salary HAVING COUNT(Salary) > 1);
405
406# QN 3-72
407ALTER TABLE EMPTABLE
408 MODIFY COLUMN Name VARCHAR(15) NOT NULL;
409
410# QN 3-73
411ALTER TABLE EMPTABLE
412 ADD COLUMN Age NUMERIC(3);
413
414# QN 3-74
415UPDATE EMPTABLE
416SET Age = TIMESTAMPDIFF(YEAR, DOB, CURRENT_TIMESTAMP);
417
418# QN 3-75
419ALTER TABLE EMPTABLE
420 CHANGE DOB DateOfBirth DATE NOT NULL;
421
422# QN 3-76
423ALTER TABLE EMPTABLE
424 DROP COLUMN Age;
425
426# QN 3-77
427# NO idea how to do it within the shell
428
429# QN 3-78
430# Table does not exists will result in a error
431
432# QN 3-79
433ALTER TABLE EMPTABLE
434 MODIFY COLUMN DOJ DATE;
435
436# QN 3-80
437SELECT UPPER(Name), UPPER(City)
438FROM EMPTABLE;
439
440# QN 3-81
441SELECT MONTH(DateOfBirth)
442FROM EMPTABLE
443GROUP BY MONTH(DateOfBirth)
444ORDER BY COUNT(DateOfBirth) DESC
445LIMIT 1;
446
447# QN 3-82
448# Repeat of Qn 3-60
449
450# QN 3-83
451SELECT Name
452FROM EMPTABLE
453WHERE Sex = 'F'
454 AND Salary > (SELECT MAX(Salary) FROM EMPTABLE WHERE Sex = 'M');
455
456# QN 3-84
457INSERT INTO EMPTABLE VALUE (1001, 'Anish', 'Chennai', STR_TO_DATE('21-apr-1966', '%d-%b-%Y'), 'M',
458 STR_TO_DATE('10-jun-1986', '%d-%b-%Y'), 17000, 'ACC');
459
460# QN 3-85
461DELETE
462FROM EMPTABLE
463WHERE Name = 'Engela';