54條sql學習

--1、查找員工的編号、姓名、部門和出生日期，如果出生日期為空值，顯示日期不詳,并按部門排序輸出,日期格式為yyyy-mm-dd。
  2 select emp_no,emp_name,dept,isnull(convert(char(10),birthday,120),'日期不詳') birthday
  3 from employee
  4 order by dept
  5 
  6 --2、查找與喻自強在同一個機關的員工姓名、性别、部門和職稱
  7 select emp_no,emp_name,dept,title
  8 from employee
  9 where emp_name<>'喻自強' and dept in
 10 (select dept from employee
 11 where emp_name='喻自強')
 12 
 13 --3、按部門進行彙總，統計每個部門的總工資
 14 select dept,sum(salary)
 15 from employee
 16 group by dept
 17 
 18 --4、查找商品名稱為14寸顯示器商品的銷售情況，顯示該商品的編号、銷售數量、單價和金額
 19 select a.prod_id,qty,unit_price,unit_price*qty totprice
 20 from sale_item a,product b
 21 where a.prod_id=b.prod_id and prod_name='14寸顯示器'
 22 
 23 --5、在銷售明細表中按産品編号進行彙總，統計每種産品的銷售數量和金額
 24 select prod_id,sum(qty) totqty,sum(qty*unit_price) totprice
 25 from sale_item
 26 group by prod_id
 27 
 28 --6、使用convert函數按客戶編号統計每個客戶1996年的訂單總金額
 29 select cust_id,sum(tot_amt) totprice
 30 from sales
 31 where convert(char(4),order_date,120)='1996'
 32 group by cust_id
 33 
 34 --7、查找有銷售記錄的客戶編号、名稱和訂單總額
 35 select a.cust_id,cust_name,sum(tot_amt) totprice
 36 from customer a,sales b
 37 where a.cust_id=b.cust_id
 38 group by a.cust_id,cust_name
 39 
 40 --8、查找在1997年中有銷售記錄的客戶編号、名稱和訂單總額
 41 select a.cust_id,cust_name,sum(tot_amt) totprice
 42 from customer a,sales b
 43 where a.cust_id=b.cust_id and convert(char(4),order_date,120)='1997'
 44 group by a.cust_id,cust_name
 45 
 46 --9、查找一次銷售最大的銷售記錄
 47 select order_no,cust_id,sale_id,tot_amt
 48 from sales
 49 where tot_amt=
 50 (select max(tot_amt)
 51 from sales)
 52 
 53 --10、查找至少有3次銷售的業務員名單和銷售日期
 54 select emp_name,order_date
 55 from employee a,sales b 
 56 where emp_no=sale_id and a.emp_no in
 57 (select sale_id
 58 from sales
 59 group by sale_id
 60 having count(*)>=3)
 61 order by emp_name
 62 
 63 --11、用存在量詞查找沒有訂貨記錄的客戶名稱
 64 select cust_name
 65 from customer a
 66 where not exists
 67 (select *
 68 from sales b
 69 where a.cust_id=b.cust_id)
 70 
 71 --12、使用左外連接配接查找每個客戶的客戶編号、名稱、訂貨日期、訂單金額訂貨日期不要顯示時間，日期格式為yyyy-mm-dd按客戶編号排序，同一客戶再按訂單降序排序輸出
 72 select a.cust_id,cust_name,convert(char(10),order_date,120),tot_amt
 73 from customer a left outer join sales b on a.cust_id=b.cust_id
 74 order by a.cust_id,tot_amt desc
 75 
 76 --13、查找16M DRAM的銷售情況，要求顯示相應的銷售員的姓名、性别，銷售日期、銷售數量和金額，其中性别用男、女表示
 77 select emp_name 姓名, 性别= case a.sex when 'm' then '男'
 78 when 'f' then '女' 
 79 else '未'
 80 end,
 81 銷售日期= isnull(convert(char(10),c.order_date,120),'日期不詳'),
 82 qty 數量, qty*unit_price as 金額
 83 from employee a, sales b, sale_item c,product d
 84 where d.prod_name='16M DRAM' and d.prod_id=c.prod_id and
 85 a.emp_no=b.sale_id and b.order_no=c.order_no
 86 
 87 --14、查找每個人的銷售記錄，要求顯示銷售員的編号、姓名、性别、産品名稱、數量、單價、金額和銷售日期
 88 select emp_no 編号,emp_name 姓名, 性别= case a.sex when 'm' then '男'
 89 when 'f' then '女' 
 90 else '未'
 91 end,
 92 prod_name 産品名稱,銷售日期= isnull(convert(char(10),c.order_date,120),'日期不詳'),
 93 qty 數量, qty*unit_price as 金額
 94 from employee a left outer join sales b on a.emp_no=b.sale_id , sale_item c,product d
 95 where d.prod_id=c.prod_id and b.order_no=c.order_no
 96 
 97 --15、查找銷售金額最大的客戶名稱和總貨款
 98 select cust_name,d.cust_sum
 99 from customer a,
100 (select cust_id,cust_sum
101 from (select cust_id, sum(tot_amt) as cust_sum
102 from sales
103 group by cust_id ) b
104 where b.cust_sum = 
105 ( select max(cust_sum)
106 from (select cust_id, sum(tot_amt) as cust_sum
107 from sales
108 group by cust_id ) c )
109 ) d
110 where a.cust_id=d.cust_id 
111 
112 --16、查找銷售總額少于1000元的銷售員編号、姓名和銷售額
113 select emp_no,emp_name,d.sale_sum
114 from employee a,
115 (select sale_id,sale_sum
116 from (select sale_id, sum(tot_amt) as sale_sum
117 from sales
118 group by sale_id ) b
119 where b.sale_sum <1000 
120 ) d
121 where a.emp_no=d.sale_id 
122 
123 --17、查找至少銷售了3種商品的客戶編号、客戶名稱、商品編号、商品名稱、數量和金額
124 select a.cust_id,cust_name,b.prod_id,prod_name,d.qty,d.qty*d.unit_price
125 from customer a, product b, sales c, sale_item d
126 where a.cust_id=c.cust_id and d.prod_id=b.prod_id and 
127 c.order_no=d.order_no and a.cust_id in (
128 select cust_id
129 from (select cust_id,count(distinct prod_id) prodid
130 from (select cust_id,prod_id
131 from sales e,sale_item f
132 where e.order_no=f.order_no) g
133 group by cust_id
134 having count(distinct prod_id)>=3) h )
135 
136 --18、查找至少與世界技術開發公司銷售相同的客戶編号、名稱和商品編号、商品名稱、數量和金額
137 select a.cust_id,cust_name,d.prod_id,prod_name,qty,qty*unit_price
138 from customer a, product b, sales c, sale_item d
139 where a.cust_id=c.cust_id and d.prod_id=b.prod_id and 
140 c.order_no=d.order_no and not exists
141 (select f.*
142 from customer x ,sales e, sale_item f
143 where cust_name='世界技術開發公司' and x.cust_id=e.cust_id and
144 e.order_no=f.order_no and not exists
145 ( select g.*
146 from sale_item g, sales h
147 where g.prod_id = f.prod_id and g.order_no=h.order_no and
148 h.cust_id=a.cust_id)
149 )
150 
151 19、查找表中所有姓劉的職工的工号，部門，薪水
152 select emp_no,emp_name,dept,salary
153 from employee
154 where emp_name like '劉%'
155 
156 20、查找所有定單金額高于2000的所有客戶編号
157 select cust_id
158 from sales
159 where tot_amt>2000
160 
161 21、統計表中員工的薪水在4000-6000之間的人數
162 select count(*)as 人數
163 from employee
164 where salary between 4000 and 6000
165 
166 22、查詢表中的同一部門的職工的平均工資，但隻查詢＂住址＂是＂上海市＂的員工
167 select avg(salary) avg_sal,dept 
168 from employee 
169 where addr like '上海市%'
170 group by dept
171 
172 23、将表中住址為"上海市"的員工住址改為"北京市"
173 update employee 
174 set addr like '北京市'
175 where addr like '上海市'
176 
177 24、查找業務部或會計部的女員工的基本資訊。
178 select emp_no,emp_name,dept
179 from employee 
180 where sex='F'and dept in ('業務','會計')
181 
182 25、顯示每種産品的銷售金額總和，并依銷售金額由大到小輸出。
183 select prod_id ,sum(qty*unit_price)
184 from sale_item 
185 group by prod_id
186 order by sum(qty*unit_price) desc
187 
188 26、選取編号界于'C0001'和'C0004'的客戶編号、客戶名稱、客戶位址。
189 select CUST_ID,cust_name,addr
190 from customer 
191 where cust_id between 'C0001' AND 'C0004'
192 
193 27、計算出一共銷售了幾種産品。
194 select count(distinct prod_id) as '共銷售産品數'
195 from sale_item 
196 
197 28、将業務部員工的薪水上調3%。
198 update employee
199 set salary=salary*1.03
200 where dept='業務'
201 
202 29、由employee表中查找出薪水最低的員工資訊。
203 select *
204 from employee
205 where salary=
206 (select min(salary )
207 from employee )
208 
209 30、使用join查詢客戶姓名為"客戶丙"所購貨物的"客戶名稱","定單金額","定貨日期","電話号碼"
210 select a.cust_id,b.tot_amt,b.order_date,a.tel_no
211 from customer a join sales b
212 on a.cust_id=b.cust_id and cust_name like '客戶丙'
213 
214 31、由sales表中查找出訂單金額大于"E0013業務員在1996/10/15這天所接每一張訂單的金額"的所有訂單。
215 select *
216 from sales
217 where tot_amt>all
218 (select tot_amt 
219 from sales 
220 where sale_id='E0013'and order_date='1996/10/15')
221 order by tot_amt
222 
223 32、計算'P0001'産品的平均銷售單價
224 select avg(unit_price)
225 from sale_item
226 where prod_id='P0001'
227 
228 33、找出公司女員工所接的定單
229 select sale_id,tot_amt
230 from sales
231 where sale_id in 
232 (select sale_id from employee
233 where sex='F')
234 
235 34、找出同一天進入公司服務的員工
236 select a.emp_no,a.emp_name,a.date_hired
237 from employee a
238 join employee b
239 on (a.emp_no!=b.emp_no and a.date_hired=b.date_hired)
240 order by a.date_hired
241 
242 35、找出目前業績超過232000元的員工編号和姓名。
243 select emp_no,emp_name
244 from employee 
245 where emp_no in
246 (select sale_id
247 from sales 
248 group by sale_id
249 having sum(tot_amt)<232000)
250 
251 36、查詢出employee表中所有女職工的平均工資和住址在＂上海市＂的所有女職工的平均工資
252 select avg(salary)
253 from employee
254 where sex like 'f'
255 union
256 select avg(salary)
257 from employee
258 where sex like 'f' and addr like '上海市%'
259 
260 37、在employee表中查詢薪水超過員工平均薪水的員工資訊。
261 Select * 
262 from employee 
263 where salary>( select avg(salary) 
264 from employee)
265 
266 38、 找出目前銷售業績超過10000元的業務員編号及銷售業績，并按銷售業績從大到小排序。
267 Select sale_id ,sum(tot_amt)
268 from sales 
269 group by sale_id 
270 having sum(tot_amt)>10000
271 order by sum(tot_amt) desc
272 
273 39、 找出公司男業務員所接且訂單金額超過2000元的訂單号及訂單金額。 
274 Select order_no,tot_amt
275 From sales ,employee
276 Where sale_id=emp_no and sex='M' and tot_amt>2000
277 
278 40、 查詢sales表中訂單金額最高的訂單号及訂單金額。
279 Select order_no,tot_amt from sales 
280 where tot_amt=(select max(tot_amt) from sales)
281 
282 41、 查詢在每張訂單中訂購金額超過4000元的客戶名及其位址。
283 Select cust_name,addr from customer a,sales b 
284 where a.cust_id=b.cust_id and tot_amt>4000
285 
286 42、 求出每位客戶的總訂購金額，顯示出客戶号及總訂購金額，并按總訂購金額降序排列。
287 Select cust_id,sum(tot_amt) from sales
288 Group by cust_id 
289 Order by sum(tot_amt) desc
290 
291 43、 求每位客戶訂購的每種産品的總數量及平均單價，并按客戶号，産品号從小到大排列。
292 Select cust_id,prod_id,sum(qty),sum(qty*unit_price)/sum(qty)
293 From sales a, sale_item b
294 Where a.order_no=b.order_no
295 Group by cust_id,prod_id
296 Order by cust_id,prod_id
297 
298 44、 查詢訂購了三種以上産品的訂單号。
299 Select order_no 
300 from sale_item
301 Group by order_no
302 Having count(*)>3
303 
304 45、 查詢訂購的産品至少包含了訂單3号中所訂購産品的訂單。
305 Select distinct order_no
306 From sale_item a
307 Where order_no<>'3'and not exists ( 
308 Select * from sale_item b where order_no ='3' and not exists 
309 (select * from sale_item c where c.order_no=a.order_no and c.prod_id=b.prod_id))
310 
311 46、 在sales表中查找出訂單金額大于"E0013業務員在1996/11/10這天所接每一張訂單的金額"的所有訂單，并顯示承接這些訂單的業務員和該訂單的金額。
312 Select sale_id,tot_amt from sales
313 where tot_amt>all(select tot_amt 
314 from sales 
315 where sale_id='E0013' and order_date='1996-11-10') 
316 
317 47、 查詢末承接業務的員工的資訊。
318 Select *
319 From employee a
320 Where not exists 
321 (select * from sales b where a.emp_no=b.sale_id)
322 
323 48、 查詢來自上海市的客戶的姓名，電話、訂單号及訂單金額。
324 Select cust_name,tel_no,order_no,tot_amt
325 From customer a ,sales b
326 Where a.cust_id=b.cust_id and addr='上海市'
327 
328 49、 查詢每位業務員各個月的業績，并按業務員編号、月份降序排序。
329 Select sale_id,month(order_date), sum(tot_amt) 
330 from sales 
331 group by sale_id,month(order_date)
332 order by sale_id,month(order_date) desc
333 
334 50、 求每種産品的總銷售數量及總銷售金額，要求顯示出産品編号、産品名稱，總數量及總金額，并按産品号從小到大排列。 
335 Select a.prod_id,prod_name,sum(qty),sum(qty*unit_price)
336 From sale_item a,product b
337 Where a.prod_id=b.prod_id 
338 Group by a.prod_id,prod_name
339 Order by a.prod_id
340 51、 查詢總訂購金額超過'C0002'客戶的總訂購金額的客戶号，客戶名及其住址。
341 Select cust_id, cust_name,addr
342 From customer
343 Where cust_id in (select cust_id from sales 
344 Group by cust_id
345 Having sum(tot_amt)>
346 (Select sum(tot_amt) from sales where cust_id='C0002'))
347 
348 52、 查詢業績最好的的業務員号、業務員名及其總銷售金額。
349 select emp_no,emp_name,sum(tot_amt)
350 from employee a,sales b
351 where a.emp_no=b.sale_id
352 group by emp_no,emp_name
353 having sum(tot_amt)=
354 (select max(totamt)
355 from (select sale_id,sum(tot_amt) totamt
356 from sales
357 group by sale_id) c)
358 
359 53、 查詢每位客戶所訂購的每種産品的詳細清單，要求顯示出客戶号，客戶名，産品号，産品名，數量及單價。
360 select a.cust_id, cust_name,c.prod_id,prod_name,qty, unit_price
361 from customer a,sales b, sale_item c ,product d
362 where a.cust_id=b.cust_id and b.order_no=c.order_no and c.prod_id=d.prod_id
363 
364 54、 求各部門的平均薪水，要求按平均薪水從小到大排序。
365 select dept,avg(salary) 
366 from employee
367 group by dept 
368 order by avg(salary)