-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL_Join_and_Subquery.txt
221 lines (160 loc) · 10.1 KB
/
SQL_Join_and_Subquery.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
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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
JOINTS:
we can fetch records from more than 1 table using join.While executing join there should be a common column between both tables.There are diff types of joins:
Inner Join
Outer left Join
Outer rigth Join
Self Join
Cross Join
Full Join
create table dept
(
deptno number(5) primary key,
dname varchar2(20),
loc varchar2(20)
);
insert into dept values(10,'Testing','Mumbai');
insert into dept values(20,'Dev','Pune');
insert into dept values(50,'HR','Pune');
create table employee
(
empno number(5) primary key,
ename varchar(20),
salary float,
deptno number(5)
);
insert into employee values(1,'ram',10000,10);
insert into employee values(5,'krishna',20000,20);
insert into employee values(2,'ganesh',20000,40);
------------------------------------------------------------------------------------------------------------------------
Inner Joint:
select e.ename,e.deptno,d.dname from employee e,dept d where e.deptno=d.deptno;
OR
SELECT e.ename,e.deptno,d.dname FROM employee e INNER JOIN dept d ON e.deptno=d.deptno;
ENAME DEPTNO DNAME
-------------------- ---------- --------------------
ram 10 Testing
krishna 20 Dev
employee name and department name was printed if the department no is common.
---------------------------------------------------------------------------------------------------------------------------
Left OUTER Joint:
select e.ename,e.deptno,d.dname from employee e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
ENAME DEPTNO DNAME
-------------------- ---------- --------------------
ram 10 Testing
krishna 20 Dev
ganesh 40
OR
SELECT * FROM dept d LEFT OUTER JOIN employee e ON d.deptno=e.deptno;
DEPTNO DNAME LOC EMPID EMPNAME EMPSALARY CITY DEPTNO
---------- -------------------- -------------------- ---------- -------------------- ---------- -------------------- ----------
10 Design Mumbai 101 Ramesh 16295.433 Mumbai 10
20 Production Chakkan 102 Amit 20000 Chennai 20
10 Design Mumbai 103 Suresh 7320.5 Pune 10
30 Education 105 Ganesh 12000 Mumbai 30
50 Quality Control Pune
all rows from left side table and only matching record from rigth table.
-------------------------------------------------------------------------------------------------------------------------------
RIGHT OUT JOINT:
select e.ename,e.deptno,d.dname from employee e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
ENAME DEPTNO DNAME
-------------------- ---------- --------------------
ram 10 Testing
krishna 20 Dev
HR
OR
SELECT * FROM dept d RIGHT OUTER JOIN employee e ON d.deptno=e.deptno;
DEPTNO DNAME LOC EMPID EMPNAME EMPSALARY CITY DEPTNO
---------- -------------------- -------------------- ---------- -------------------- ---------- -------------------- ----------
10 Design Mumbai 101 Ramesh 16295.433 Mumbai 10
20 Production Chakkan 102 Amit 20000 Chennai 20
10 Design Mumbai 103 Suresh 7320.5 Pune 10
30 Education 105 Ganesh 12000 Mumbai 30
-------------------------------------------------------------------------------------------------------------------------------
FULL JOINT:
select e.ename,e.deptno,d.dname from employee e FULL JOIN dept d ON e.deptno=d.deptno;
ENAME DEPTNO DNAME
-------------------- ---------- --------------------
ram 10 Testing
krishna 20 Dev
ganesh 40
HR
OR
SELECT * FROM dept d FULL JOIN employee e ON d.deptno=e.deptno;
DEPTNO DNAME LOC EMPID EMPNAME EMPSALARY CITY DEPTNO
---------- -------------------- -------------------- ---------- -------------------- ---------- -------------------- ----------
10 Design Mumbai 101 Ramesh 16295.433 Mumbai 10
20 Production Chakkan 102 Amit 20000 Chennai 20
10 Design Mumbai 103 Suresh 7320.5 Pune 10
30 Education 105 Ganesh 12000 Mumbai 30
50 Quality Control Pune
------------------------------------------------------------------------------------------------------------------------------------
CROSS JOINT:
select * from employee,dept;
EMPNO ENAME SALARY DEPTNO DEPTNO DNAME LOC
---------- -------------------- ---------- ---------- ---------- -------------------- --------------------
1 ram 10000 10 10 Testing Mumbai
1 ram 10000 10 20 Dev Pune
1 ram 10000 10 50 HR Pune
5 krishna 20000 20 10 Testing Mumbai
5 krishna 20000 20 20 Dev Pune
5 krishna 20000 20 50 HR Pune
2 ganesh 20000 40 10 Testing Mumbai
2 ganesh 20000 40 20 Dev Pune
2 ganesh 20000 40 50 HR Pune
-----------------------------------------------------------------------------------------------------------------------------------------
SELF JOINT:
select * from employee a,employee b where a.empid=101 and a.deptno=b.deptno;
EMPID EMPNAME EMPSALARY CITY DEPTNO EMPID EMPNAME EMPSALARY CITY DEPTNO
---------- -------------------- ---------- -------------------- ---------- ---------- -------------------- ---------- -------------------- ----------
101 Ramesh 16295.433 Mumbai 10 101 Ramesh 16295.433 Mumbai 10
101 Ramesh 16295.433 Mumbai 10 103 Suresh 7320.5 Pune 10
select b.empname from employee a,employee b where a.empid=101 and a.deptno=b.deptno;
EMPNAME
--------------------
Ramesh
Suresh
----------------------------------------------------------------------------------------------------------------------------------------------
Subquery:
select salary from employees where salary>(select avg(salary) from employees);
SALARY
----------
24000
17000
17000
9000
12000
9000
8200
7700
7800
6900
11000
For subquery returning 1 value;
select first_name,department_id from employees where department_id=(select department_id from departments where department_name='IT');
FIRST_NAME DEPARTMENT_ID
-------------------- -------------
Alexander 60
Bruce 60
David 60
Valli 60
Diana 60
For subquer returning 2 values:
select first_name,department_id from employees where department_id in(select department_id from departments where department_id in(20,60));
FIRST_NAME DEPARTMENT_ID
-------------------- -------------
David 60
Bruce 60
Pat 20
Michael 20
Alexander 60
Diana 60
Valli 60
----------------------------------------------------------------------------------------------------------------------------------------------
select * from employees where salary>=(select max(salary) from employees);
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-87 AD_PRES 24000 90
select * from employees where salary>=all(select salary from employees);
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-87 AD_PRES 24000 90