-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSQL_18.text
152 lines (125 loc) · 16.3 KB
/
SQL_18.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
SQL> SELECT DISTINCT SAL
2 FROM EMP;
SAL
----------
2450
5000
1300
1250
2850
2975
1100
3000
800
1600
1500
950
12 rows selected.
SQL> SELECT DISTINCT JOB
2 FROM EMP;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
SQL> SELECT DISTINCT DEPTNO
2 FROM EMP;
DEPTNO
----------
30
20
10
SQL> SELECT DISTINCT COMM
2 FROM EMP;
COMM
----------
1400
500
300
0
SQL> SELECT DISTINCT SAL,JOB
2 FROM EMP;
SAL JOB
---------- ---------
800 CLERK
2850 MANAGER
1500 SALESMAN
2975 MANAGER
2450 MANAGER
1300 CLERK
1250 SALESMAN
1100 CLERK
3000 ANALYST
950 CLERK
1600 SALESMAN
5000 PRESIDENT
12 rows selected.
SQL> SELECT DISTINCT SAL,JOB,DEPTNO
2 FROM EMP;
SAL JOB DEPTNO
---------- --------- ----------
950 CLERK 30
800 CLERK 20
2850 MANAGER 30
1100 CLERK 20
2975 MANAGER 20
2450 MANAGER 10
1600 SALESMAN 30
1250 SALESMAN 30
5000 PRESIDENT 10
1500 SALESMAN 30
3000 ANALYST 20
1300 CLERK 10
12 rows selected.
SQL> SELECT DISTINCT *
2 FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7839 KING PRESIDENT 17-NOV-81 5000 10
14 rows selected.
SQL> SELECT * FROM BONUS
2 ;
no rows selected
SQL> SELECT DISTINCT * FROM BONUS;
no rows selected
SQL> SELECT SAL
2 FROM(SELECT SAL
3 FROM(SELECT DISTINCT SAL FROM EMP
4 ORDER BY SAL DESC)
5 WHERE ROWNUM<=3;
WHERE ROWNUM<=3
*
ERROR at line 5:
ORA-00907: missing right parenthesis
SQL> SELECT SAL
2 FROM(SELECT SAL
3 FROM(SELECT DISTINCT SAL FROM EMP
4 ORDER BY SAL DESC)
5 WHERE ROWNUM<=3
6 SELECT SAL
7 FROM(SELECT SAL
8 FROM(SELECT DISTINCT SAL FROM EMP
9 ORDER BY SAL DESC)
10 WHERE ROWNUM<=3
11 ORDER BY SAL)
12 WHERE ROWNUM=1;
SELECT SAL
*
ERROR at line 6:
ORA-00907: missing right parenthesis
SQL> SPOOL OFF;