-
Notifications
You must be signed in to change notification settings - Fork 0
/
PRACTICA TEMA 7.txt
112 lines (69 loc) · 2.53 KB
/
PRACTICA TEMA 7.txt
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
------------------------------------------------------------------------------------------------------------------------------------------------------------
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
------------------------------------------------------------------------------------------------------------------------------------------------------------
PRACTICA 7:
1. SELECT ENAME, HIREDATE
FROM EMP
WHERE DEPTNO = 30 AND ENAME NOT LIKE 'BLAKE'
GROUP BY ENAME, HIREDATE;
2. SELECT EMPNO, ENAME
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP)
ORDER BY SAL DESC;
3. SELECT E.EMPNO, E.ENAME
FROM EMP E
WHERE E.DEPTNO IN (
SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%T%'
);
4. SELECT E.ENAME, E.DEPTNO, E.JOB
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'DALLAS';
5. SELECT E.ENAME, E.SAL
FROM EMP E
WHERE E.MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING');
6. SELECT E.DEPTNO, E.ENAME, E.JOB
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE D.DNAME = 'SALES';
7. SELECT E.EMPNO, E.ENAME, E.SAL
FROM EMP E
WHERE E.DEPTNO IN (
SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%T%'
AND SAL > (SELECT AVG(SAL) FROM EMP)
);
EJERCICIO EXTRAS:
1. SELECT E.ENAME, E.DEPTNO, E.SAL
FROM EMP E
WHERE (E.DEPTNO, E.SAL) IN (
SELECT DEPTNO, SAL
FROM EMP
WHERE COMM IS NOT NULL
);
2. SELECT E.ENAME, D.DNAME, E.SAL
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE (E.SAL, E.COMM) IN (
SELECT E2.SAL, E2.COMM
FROM EMP E2
JOIN DEPT D2 ON E2.DEPTNO = D2.DEPTNO
WHERE D2.LOC = 'DALLAS'
);
3. SELECT E.ENAME, E.HIREDATE, E.SAL
FROM EMP E
WHERE (E.SAL, E.COMM) = (
SELECT S.SAL, S.COMM
FROM EMP S
WHERE S.ENAME = 'SCOTT'
);
4. SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'CLERK')
ORDER BY SAL DESC;
------------------------------------------------------------------------------------------------------------------------------------------------------------
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
------------------------------------------------------------------------------------------------------------------------------------------------------------