Repository navigation
Expand file tree
/
Copy path02_EMPLOYEE.SQL
More file actions
125 lines (79 loc) · 2.61 KB
/
Copy path02_EMPLOYEE.SQL
File metadata and controls
125 lines (79 loc) · 2.61 KB
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
--DATA SET: `EMPLOYEES`
CREATE TABLE EMPLOYEES(
EMPLOYEE_ID INT PRIMARY KEY,
NAME VARCHAR(100),
DepartmentID INT,
SALARY INT,
AGE INT,
CITY VARCHAR (90),
EXPERIENCE INT,
FOREIGN KEY (DepartmentID) REFERENCES DEPARTMENTS (DepartmentID)
);
INSERT INTO EMPLOYEES
(EMPLOYEE_ID, NAME, DepartmentID, SALARY, AGE, CITY, EXPERIENCE)
VALUES
(1,'RAHUL',1,75000,28,'Bangalore',5),
(2,'PRIYA',2,55000,32,'Chennai',7),
(3,'AMIT',3,65000,30,'Mumbai',6),
(4,'SENHA',1,80000,26,'Hyderabad',4),
(5,'Kiran',4,50000,35,'Bangalore',10),
(6,'Anjali',3,72000,29,'Pune',5),
(7,'Ramesh',1,60000,31,'Chennai',8),
(8,'Neha',2,58000,27,'Mumbai',3),
(9,'Arjun',4,45000,25,'Pune',2),
(10,'Divya',3,90000,38,'Bangalore',12);
--SQL PRACTICE QUESTIONS
--SELECT
--1 DISPLAY ALL COLUMNS FROM THE `EMPLOYEE` TABLE
SELECT * FROM EMPLOYEES;
--2. DISPLAY ONLY EMPLOYEE NAMES AND THEIR SALARIES.
SELECT NAME, SALARY FROM EMPLOYEES;
--DISTINCT
--3. FIND ALL UNIQUE CITIES WHERE EMPLOYEE ARE LOCATED
SELECT DISTINCT CITY FROM EMPLOYEES;
--WHERE
--4.DISPLAY EMPLOYEES WHOSE SALARY IS GREATER THAN 70000.
SELECT NAME FROM EMPLOYEES
WHERE SALARY > 70000;
--5.DISPLAY EMPLOYEES WHO ARE FROM BANGALORE
SELECT NAME FROM EMPLOYEES
WHERE CITY = 'BANGALORE';
--6. DISPLAY ALL EMPLOYEES SORTED BY SALARY IN ASCENDING ORDER.
SELECT SALARY FROM EMPLOYEES
ORDER BY SALARY;
--7. DISPLAY EMPLOYEES SORTED BY SALARY IN DECENDING ORDER
SELECT SALARY FROM EMPLOYEES
ORDER BY SALARY DESC;
--8. DISPLAY EMPLOYEES SORTED BY AGE FROM YOUNGEST TO OLDEST.
SELECT AGE FROM EMPLOYEES
ORDER BY AGE;
--9. FIND EMPLOYEE WHO ARE OLDER THAN 30 AND HAVE MORE THAN 5 YEARS OF EXPERIENCE.
SELECT * FROM EMPLOYEES
WHERE AGE > 30 AND EXPERIENCE > 5;
--OR
--10. FIND EMPLOYEES WHO BELONG TO THE HR OR FINANCE DEPARTMENT.
SELECT e.*
FROM EMPLOYEES e
JOIN DEPARTMENTS d
ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName IN ('HR', 'Finance');
--NOT
--11. DISPLAY EMPLOYEES WHO ARE NOT FROM BANGALORE
SELECT * FROM EMPLOYEES
WHERE CITY != 'BANGALORE';
--BETWEEN
-- 12. FIND EMPLOYEES WHOSE SALARY IS BETWEEN 50000 AND 75000.
SELECT * FROM EMPLOYEES
WHERE SALARY BETWEEN 50000 AND 75000;
--13. FIND EMPLOYEES WHOSE AGE IS BETWEEN 25 AND 30.
SELECT * FROM EMPLOYEES
WHERE AGE BETWEEN 25 AND 30;
--14. FIND EMPLOYEES WHOSE EXPERIENCE IS BETWEEN 3 AND 8 YEARS.
SELECT * FROM EMPLOYEES
WHERE EXPERIENCE BETWEEN 3 AND 8;
--15. FIND EMPLOYEES WHO ARE LOCATED IN BANGALORE , CHENNAI OR PUNE
SELECT * FROM EMPLOYEES
WHERE CITY IN('BANGALORE','CHENNAI','PUNE');
--16. FIND EMPLOYEES WHOSE AGE IS IN THE RANGE OF 25 28 OR35
SELECT * FROM EMPLOYEES
WHERE AGE IN (25,28,35);