-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSQL_15.text
155 lines (113 loc) · 12.1 KB
/
SQL_15.text
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
SQL> SELECT MAX(SAL) FROM EMP;
MAX(SAL)
----------
5000
SQL> SELECT * FROM EMP
2 WHERE SAL=MAX(SAL);
WHERE SAL=MAX(SAL)
*
ERROR at line 2:
ORA-00934: group function is not allowed here
SQL> SELECT ENAME,MAX(SAL)
2 FROM EMP;
SELECT ENAME,MAX(SAL)
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> SELECT MAX(SAL)
2 FROM EMP
3 WHERE DEPTNO IN(10,30);
MAX(SAL)
----------
5000
SQL> SELECT MAX(SAL)
2 FROM EMP
3 WHERE JOB='SALESMAN';
MAX(SAL)
----------
1600
SQL> SELECT SUM(SAL)
2 FROM EMP;
SUM(SAL)
----------
29025
SQL> SELECT SUM(SAL)+ SUM(COMM)
2 FROM EMP;
SUM(SAL)+SUM(COMM)
------------------
31225
SQL> SELECT SUM(SAL),SUM(COMM)
2 FROM EMP;
SUM(SAL) SUM(COMM)
---------- ----------
29025 2200
SQL> SELECT SUM(SAL),MAX(SAL)
2 FROM EMP;
SUM(SAL) MAX(SAL)
---------- ----------
29025 5000
SQL> SELECT AVG(SAL)
2 FROM EMP;
AVG(SAL)
----------
2073.21429
SQL> SELECT AVG(SAL)
2 FROM EMP
3 WHERE JOB IN('SALESMAN','CLERK');
AVG(SAL)
----------
1218.75
SQL> SELECT COUNT(EMPNO)
2 FROM EMP;
COUNT(EMPNO)
------------
14
SQL> SELECT COUNT(MGR)
2 FROM EMP;
COUNT(MGR)
----------
13
SQL> SELECT COUNT(COMM)
2 FROMM EMP;
FROMM EMP
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> SELECT COUNT(COMM)
2 FROM EMP;
COUNT(COMM)
-----------
4
SQL> SELECT COUNT(*)
2 FROM EMP;
COUNT(*)
----------
14
SQL> SELECT COUNT(*)
2 FROM EMP
3 WHERE JOB='SALESMAN';
COUNT(*)
----------
4
SQL> SELECT COUNT(*)
2 FROM EMP
3 WHERE DEPTNO IN(10,30);
COUNT(*)
----------
9
SQL> SELECT MAX(SAL)
2 FROM EMP;
MAX(SAL)
----------
5000
SQL> SELECT * FROM EMP
2 WHERE SAL=( SELECT MAX(SAL) FROM EMP);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
SQL> SELECT * FROM EMP
2 WHERE SAL=(SELECT MIN(SAL) FROM EMP);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
SQL> SPOOL OFF