SQL_Class_scripts/SQLQuery5.sql at main · Gvicho/SQL_Class_scripts · GitHub

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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
use bd0_2022053_2223;

SELECT * from emp;
SELECT * from department;

/* Subquerys*/

/* 1) */
SELECT * FROM emp
WHERE Sal = (SELECT min(Sal) FROM emp);


/* 2) */
SELECT * FROM emp
WHERE Job = (SELECT Job FROM emp WHERE Name = 'SMITH');

/* 3) */


SELECT * FROM emp
WHERE Sal > (SELECT min(Sal) FROM emp WHERE depno = 30);

/* or */

SELECT * FROM emp
WHERE Sal > ANY (SELECT DISTINCT Sal
				FROM emp
				WHERE depno = 30);


/* 4) */

SELECT * FROM emp
WHERE Sal > (SELECT max(Sal) FROM emp WHERE depno = 20);

/* or */

SELECT * FROM emp
WHERE Sal > ALL (SELECT DISTINCT Sal
				FROM emp
				WHERE depno = 20);


/* 5) */
SELECT *
FROM emp e1
WHERE Sal > (SELECT AVG(Sal)
			FROM emp e2
			Where e2.depno = e1.depno);
/* !! e1 e2 saxelebis darqmeva aris korelacia !! */

/* 6) */
SELECT *
FROM emp
WHERE empno = ANY (SELECT Manager
				FROM emp );
/* OR */

SELECT *
FROM emp
WHERE empno IN (SELECT Manager
				FROM emp );


/* EXTRA */

/* a) */

SELECT *
FROM emp e1 INNER JOIN department d1
ON e1.depno = d1.dept_no
WHERE e1.Hiredate = (SELECT MIN(e2.Hiredate) FROM emp e2);


/* b) */

SELECT *
FROM emp e1 INNER JOIN department d1
ON e1.depno = d1.dept_no
WHERE e1.Name LIKE 'J%'
AND d1.Dname LIKE 'S%';

/*OR*/

SELECT TABLE1.depno,TABLE1.Name
FROM
		(SELECT e1.Name,e1.depno FROM emp e1 WHERE	e1.Name LIKE 'J%')AS TABLE1
INNER JOIN
		(SELECT d1.dept_no,d1.Dname FROM department d1 WHERE d1.Dname LIKE 'S%')AS TABLE2
ON TABLE1.depno = TABLE2.dept_no;


/* c) */

SELECT *
FROM emp e1
WHERE LEN(e1.Name) = (SELECT MIN(LEN(e2.Name))
			FROM emp e2
			Where e2.Job = e1.Job);

/* d) */

SELECT e1.depno,e1.Name,e1.Hiredate,d1.Dname
FROM emp e1 INNER JOIN department d1
ON e1.depno = d1.dept_no
WHERE e1.Hiredate = (SELECT MAX(e2.Hiredate)
			FROM emp e2
			Where e2.depno = e1.depno);

/* e) */


SELECT *
FROM emp e1
WHERE e1.Hiredate < (SELECT e2.Hiredate
			FROM emp e2
			Where e2.Name = 'FORD')
AND e1.Comm IS NULL
ORDER BY e1.Sal ASC;


/* f) */

select e1.Name , e1.Job , e1.depno from emp e1
where e1.depno = 20
and e1.Job in (select distinct e2.Job
           from emp e2 inner join department d
            on e2.depno = d.dept_no
			where d.Dname = 'SALES')
order by e1.Name DESC

/* g) */

SELECT *
FROM emp e1
WHERE e1.Sal > (SELECT e2.Sal
			FROM emp e2
			Where e2.Name = 'FORD')
OR e1.Job = (SELECT e2.Job
			FROM emp e2
			Where e2.Name = 'SMITH')
ORDER BY e1.Name DESC;


/* h)  */

SELECT *
FROM emp e1 INNER JOIN department d1
ON e1.depno = d1.dept_no
WHERE (d1.Loc = 'New York'
OR d1.Loc = 'Chicago')
AND e1.Comm IS NOT NULL
ORDER BY e1.Sal DESC;