SQL堂上作业四
等值连接
所有值相等的，都会被进行一次连接
SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
输出如下
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- -------------------------------------------------- ------------- ------------- -----------
100 King 90 90 1700
101 Kochhar 90 90 1700
102 De Haan 90 90 1700
103 Hunold 60 60 1400
104 Ernst 60 60 1400
105 Austin 60 60 1400
106 Pataballa 60 60 1400
107 Lorentz 60 60 1400
108 Greenberg 100 100 1700
109 Faviet 100 100 1700
110 Chen 100 100 1700
111 Sciarra 100 100 1700
112 Urman 100 100 1700
113 Popp 100 100 1700
114 Raphaely 30 30 1700
115 Khoo 30 30 1700
116 Baida 30 30 1700
117 Tobias 30 30 1700
118 Himuro 30 30 1700
119 Colmenares 30 30 1700
120 Weiss 50 50 1500
121 Fripp 50 50 1500
122 Kaufling 50 50 1500
123 Vollman 50 50 1500
124 Mourgos 50 50 1500
125 Nayer 50 50 1500
126 Mikkilineni 50 50 1500
127 Landry 50 50 1500
128 Markle 50 50 1500
129 Bissot 50 50 1500
130 Atkinson 50 50 1500
131 Marlow 50 50 1500
132 Olson 50 50 1500
133 Mallin 50 50 1500
134 Rogers 50 50 1500
135 Gee 50 50 1500
136 Philtanker 50 50 1500
137 Ladwig 50 50 1500
138 Stiles 50 50 1500
139 Seo 50 50 1500
140 Patel 50 50 1500
141 Rajs 50 50 1500
142 Davies 50 50 1500
143 Matos 50 50 1500
144 Vargas 50 50 1500
145 Russell 80 80 2500
146 Partners 80 80 2500
147 Errazuriz 80 80 2500
148 Cambrault 80 80 2500
149 Zlotkey 80 80 2500
150 Tucker 80 80 2500
151 Bernstein 80 80 2500
152 Hall 80 80 2500
153 Olsen 80 80 2500
154 Cambrault 80 80 2500
155 Tuvault 80 80 2500
156 King 80 80 2500
157 Sully 80 80 2500
158 McEwen 80 80 2500
159 Smith 80 80 2500
160 Doran 80 80 2500
161 Sewall 80 80 2500
162 Vishney 80 80 2500
163 Greene 80 80 2500
164 Marvins 80 80 2500
165 Lee 80 80 2500
166 Ande 80 80 2500
167 Banda 80 80 2500
168 Ozer 80 80 2500
169 Bloom 80 80 2500
170 Fox 80 80 2500
171 Smith 80 80 2500
172 Bates 80 80 2500
173 Kumar 80 80 2500
174 Abel 80 80 2500
175 Hutton 80 80 2500
176 Taylor 80 80 2500
177 Livingston 80 80 2500
179 Johnson 80 80 2500
180 Taylor 50 50 1500
181 Fleaur 50 50 1500
182 Sullivan 50 50 1500
183 Geoni 50 50 1500
184 Sarchand 50 50 1500
185 Bull 50 50 1500
186 Dellinger 50 50 1500
187 Cabrio 50 50 1500
188 Chung 50 50 1500
189 Dilly 50 50 1500
190 Gates 50 50 1500
191 Perkins 50 50 1500
192 Bell 50 50 1500
193 Everett 50 50 1500
194 McCain 50 50 1500
195 Jones 50 50 1500
196 Walsh 50 50 1500
197 Feeney 50 50 1500
198 OConnell 50 50 1500
199 Grant 50 50 1500
200 Whalen 10 10 1700
201 Hartstein 20 20 1800
202 Fay 20 20 1800
203 Mavris 40 40 2400
204 Baer 70 70 2700
205 Higgins 110 110 1700
206 Gietz 110 110 1700
已选择106行。
表的别名
在上文中，表名太长了，打起来太复杂了，我们可以给表命名一个别名
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
输出与上文的输出一致
多个条件
我们可以在WHERE后面添加多个条件
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
这段语句的意思是：输出表employees，但是会给employees中的每个人的工资打一个分
输出如下：
LAST_NAME SALARY GR
-------------------------------------------------- ---------- --
Olson 2100 A
Markle 2200 A
Philtanker 2200 A
Landry 2400 A
Gee 2400 A
Colmenares 2500 A
Marlow 2500 A
Patel 2500 A
Vargas 2500 A
Sullivan 2500 A
Perkins 2500 A
Himuro 2600 A
Matos 2600 A
OConnell 2600 A
Grant 2600 A
Mikkilineni 2700 A
Seo 2700 A
Tobias 2800 A
Atkinson 2800 A
Geoni 2800 A
Jones 2800 A
Baida 2900 A
Rogers 2900 A
Gates 2900 A
Cabrio 3000 B
Feeney 3000 B
Khoo 3100 B
Davies 3100 B
Fleaur 3100 B
Walsh 3100 B
Nayer 3200 B
Stiles 3200 B
Taylor 3200 B
McCain 3200 B
Bissot 3300 B
Mallin 3300 B
Dellinger 3400 B
Rajs 3500 B
Ladwig 3600 B
Dilly 3600 B
Chung 3800 B
Everett 3900 B
Bell 4000 B
Bull 4100 B
Lorentz 4200 B
Sarchand 4200 B
Whalen 4400 B
Austin 4800 B
Pataballa 4800 B
Mourgos 5800 B
Ernst 6000 C
Fay 6000 C
Kumar 6100 C
Banda 6200 C
Johnson 6200 C
Ande 6400 C
Vollman 6500 C
Mavris 6500 C
Lee 6800 C
Popp 6900 C
Tuvault 7000 C
Sewall 7000 C
Grant 7000 C
Marvins 7200 C
Bates 7300 C
Smith 7400 C
Cambrault 7500 C
Doran 7500 C
Sciarra 7700 C
Urman 7800 C
Kaufling 7900 C
Weiss 8000 C
Olsen 8000 C
Smith 8000 C
Chen 8200 C
Fripp 8200 C
Gietz 8300 C
Livingston 8400 C
Taylor 8600 C
Hutton 8800 C
Hunold 9000 C
Faviet 9000 C
Hall 9000 C
McEwen 9000 C
Sully 9500 C
Greene 9500 C
Bernstein 9500 C
Fox 9600 C
Baer 10000 D
Bloom 10000 D
King 10000 D
Tucker 10000 D
Vishney 10500 D
Zlotkey 10500 D
Cambrault 11000 D
Raphaely 11000 D
Abel 11000 D
Ozer 11500 D
Higgins 12000 D
Greenberg 12000 D
Errazuriz 12000 D
Hartstein 13000 D
Partners 13500 D
Russell 14000 D
De Haan 17000 E
Kochhar 17000 E
King 24000 E
已选择107行。
溢出的情况
在上文的BETWEEN AND语句中，需要保证e.salary在j中一直能够找到与之对应的值域，否则整行都不会输出出来
为了让数据尽可能地可以输出出来，我们可以用(+)来避免
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;
输出如下
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------------------------------------- ------------- ------------------------------------------------------------
King 90 Executive
Kochhar 90 Executive
De Haan 90 Executive
Hunold 60 IT
Ernst 60 IT
Austin 60 IT
Pataballa 60 IT
Lorentz 60 IT
Greenberg 100 Finance
Faviet 100 Finance
Chen 100 Finance
Sciarra 100 Finance
Urman 100 Finance
Popp 100 Finance
Raphaely 30 Purchasing
Khoo 30 Purchasing
Baida 30 Purchasing
Tobias 30 Purchasing
Himuro 30 Purchasing
Colmenares 30 Purchasing
Weiss 50 Shipping
Fripp 50 Shipping
Kaufling 50 Shipping
Vollman 50 Shipping
Mourgos 50 Shipping
Nayer 50 Shipping
Mikkilineni 50 Shipping
Landry 50 Shipping
Markle 50 Shipping
Bissot 50 Shipping
Atkinson 50 Shipping
Marlow 50 Shipping
Olson 50 Shipping
Mallin 50 Shipping
Rogers 50 Shipping
Gee 50 Shipping
Philtanker 50 Shipping
Ladwig 50 Shipping
Stiles 50 Shipping
Seo 50 Shipping
Patel 50 Shipping
Rajs 50 Shipping
Davies 50 Shipping
Matos 50 Shipping
Vargas 50 Shipping
Russell 80 Sales
Partners 80 Sales
Errazuriz 80 Sales
Cambrault 80 Sales
Zlotkey 80 Sales
Tucker 80 Sales
Bernstein 80 Sales
Hall 80 Sales
Olsen 80 Sales
Cambrault 80 Sales
Tuvault 80 Sales
King 80 Sales
Sully 80 Sales
McEwen 80 Sales
Smith 80 Sales
Doran 80 Sales
Sewall 80 Sales
Vishney 80 Sales
Greene 80 Sales
Marvins 80 Sales
Lee 80 Sales
Ande 80 Sales
Banda 80 Sales
Ozer 80 Sales
Bloom 80 Sales
Fox 80 Sales
Smith 80 Sales
Bates 80 Sales
Kumar 80 Sales
Abel 80 Sales
Hutton 80 Sales
Taylor 80 Sales
Livingston 80 Sales
Johnson 80 Sales
Taylor 50 Shipping
Fleaur 50 Shipping
Sullivan 50 Shipping
Geoni 50 Shipping
Sarchand 50 Shipping
Bull 50 Shipping
Dellinger 50 Shipping
Cabrio 50 Shipping
Chung 50 Shipping
Dilly 50 Shipping
Gates 50 Shipping
Perkins 50 Shipping
Bell 50 Shipping
Everett 50 Shipping
McCain 50 Shipping
Jones 50 Shipping
Walsh 50 Shipping
Feeney 50 Shipping
OConnell 50 Shipping
Grant 50 Shipping
Whalen 10 Administration
Hartstein 20 Marketing
Fay 20 Marketing
Mavris 40 Human Resources
Baer 70 Public Relations
Higgins 110 Accounting
Gietz 110 Accounting
NOC
Manufacturing
Government Sales
IT Support
Benefits
Shareholder Services
Retail Sales
Control And Credit
Recruiting
Operations
Treasury
Payroll
Corporate Tax
Construction
Contracting
IT Helpdesk
已选择122行。
将表与自身链接
SELECT worker.last_name || ' works for '
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
输出如下：
WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME
--------------------------------------------------------------------------------------------------------------------------
Hartstein works for King
Zlotkey works for King
Cambrault works for King
Errazuriz works for King
Partners works for King
Russell works for King
Mourgos works for King
Vollman works for King
Kaufling works for King
Fripp works for King
Weiss works for King
Raphaely works for King
De Haan works for King
Kochhar works for King
Higgins works for Kochhar
Baer works for Kochhar
Mavris works for Kochhar
Whalen works for Kochhar
Greenberg works for Kochhar
Hunold works for De Haan
Lorentz works for Hunold
Pataballa works for Hunold
Austin works for Hunold
Ernst works for Hunold
Popp works for Greenberg
Urman works for Greenberg
Sciarra works for Greenberg
Chen works for Greenberg
Faviet works for Greenberg
Colmenares works for Raphaely
Himuro works for Raphaely
Tobias works for Raphaely
Baida works for Raphaely
Khoo works for Raphaely
Geoni works for Weiss
Sullivan works for Weiss
Fleaur works for Weiss
Taylor works for Weiss
Markle works for Weiss
Landry works for Weiss
Mikkilineni works for Weiss
Nayer works for Weiss
Cabrio works for Fripp
Dellinger works for Fripp
Bull works for Fripp
Sarchand works for Fripp
Olson works for Fripp
Marlow works for Fripp
Atkinson works for Fripp
Bissot works for Fripp
Perkins works for Kaufling
Gates works for Kaufling
Dilly works for Kaufling
Chung works for Kaufling
Philtanker works for Kaufling
Gee works for Kaufling
Rogers works for Kaufling
Mallin works for Kaufling
Jones works for Vollman
McCain works for Vollman
Everett works for Vollman
Bell works for Vollman
Patel works for Vollman
Seo works for Vollman
Stiles works for Vollman
Ladwig works for Vollman
Grant works for Mourgos
OConnell works for Mourgos
Feeney works for Mourgos
Walsh works for Mourgos
Vargas works for Mourgos
Matos works for Mourgos
Davies works for Mourgos
Rajs works for Mourgos
Tuvault works for Russell
Cambrault works for Russell
Olsen works for Russell
Hall works for Russell
Bernstein works for Russell
Tucker works for Russell
Sewall works for Partners
Doran works for Partners
Smith works for Partners
McEwen works for Partners
Sully works for Partners
King works for Partners
Banda works for Errazuriz
Ande works for Errazuriz
Lee works for Errazuriz
Marvins works for Errazuriz
Greene works for Errazuriz
Vishney works for Errazuriz
Kumar works for Cambrault
Bates works for Cambrault
Smith works for Cambrault
Fox works for Cambrault
Bloom works for Cambrault
Ozer works for Cambrault
Johnson works for Zlotkey
Grant works for Zlotkey
Livingston works for Zlotkey
Taylor works for Zlotkey
Hutton works for Zlotkey
Abel works for Zlotkey
Fay works for Hartstein
Gietz works for Higgins
已选择106行。
