學(xué)習(xí)數(shù)據(jù)庫必須掌握的54條SQL查詢語句

1、學(xué)習(xí)數(shù)據(jù)庫必須掌握的54條SQL查詢語句1 -1、查找員工的編號(hào)、姓名、部門和出生日期，如果出生日期為空值，顯示日期不詳,并按部門排序輸出,日期格式為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、查找與喻自強(qiáng)在同一個(gè)單位的員工姓名、性別、部門和職稱 7 select emp_no,emp_name,dept,title 8 from employee 9

2、where emp_name<>'喻自強(qiáng)' and dept in 10 (select dept from employee 11 where emp_name='喻自強(qiáng)') 12 13 -3、按部門進(jìn)行匯總，統(tǒng)計(jì)每個(gè)部門的總工資 14 select dept,sum(salary) 15 from employee 16 group by dept 17 18 -4、查找商品名稱為14寸顯示器商品的銷售情況，顯示該商品的編號(hào)、銷售數(shù)量、單價(jià)和金額 19 select d_id,qty,unit_price,unit_price*qty

3、totprice 20 from sale_item a,product b 21 where d_id=d_id and prod_name='14寸顯示器' 22 23 -5、在銷售明細(xì)表中按產(chǎn)品編號(hào)進(jìn)行匯總，統(tǒng)計(jì)每種產(chǎn)品的銷售數(shù)量和金額 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函數(shù)按客戶編號(hào)統(tǒng)計(jì)每個(gè)客戶1996年的訂單總金額 29 select cust_i

4、d,sum(tot_amt) totprice 30 from sales 31 where convert(char(4),order_date,120)='1996' 32 group by cust_id 33 34 -7、查找有銷售記錄的客戶編號(hào)、名稱和訂單總額 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

5、年中有銷售記錄的客戶編號(hào)、名稱和訂單總額 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

6、 tot_amt= 50 (select max(tot_amt) 51 from sales) 52 53 -10、查找至少有3次銷售的業(yè)務(wù)員名單和銷售日期 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、用存在量詞查找沒有訂貨記錄

7、的客戶名稱 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、使用左外連接查找每個(gè)客戶的客戶編號(hào)、名稱、訂貨日期、訂單金額訂貨日期不要顯示時(shí)間，日期格式為yyyy-mm-dd按客戶編號(hào)排序，同一客戶再按訂單降序排序輸出 72 select a.cust_id,cust_name,convert(char(10),order_date,120),tot_amt 73 from customer

8、 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的銷售情況，要求顯示相應(yīng)的銷售員的姓名、性別，銷售日期、銷售數(shù)量和金額，其中性別用男、女表示 77 select emp_name 姓名, 性別= case a.sex when 'm' then '男' 78 when 'f' then '女' 79 else '未' 80 end, 81 銷售日期= is

9、null(convert(char(10),c.order_date,120),'日期不詳'), 82 qty 數(shù)量, qty*unit_price as 金額 83 from employee a, sales b, sale_item c,product d 84 where d_name='16M DRAM' and d_id=d_id and 85 a.emp_no=b.sale_id and b.order_no=c.order_no 86 87 -14、查找每個(gè)人的銷售記錄，要求顯示銷售員的編號(hào)、姓名、性別、產(chǎn)品名稱、數(shù)

10、量、單價(jià)、金額和銷售日期 88 select emp_no 編號(hào),emp_name 姓名, 性別= case a.sex when 'm' then '男' 89 when 'f' then '女' 90 else '未' 91 end, 92 prod_name 產(chǎn)品名稱,銷售日期= isnull(convert(char(10),c.order_date,120),'日期不詳'), 93 qty 數(shù)量, qty*unit_price as 金額 94 from employee a left o

11、uter join sales b on a.emp_no=b.sale_id , sale_item c,product d 95 where d_id=d_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_sum101 from (select cust_id, sum(tot_amt) as cust_sum102 from sales103 group b

12、y cust_id ) b104 where b.cust_sum = 105 ( select max(cust_sum)106 from (select cust_id, sum(tot_amt) as cust_sum107 from sales108 group by cust_id ) c )109 ) d110 where a.cust_id=d.cust_id 111 112 -16、查找銷售總額少于1000元的銷售員編號(hào)、姓名和銷售額113 select emp_no,emp_name,d.sale_sum114 from employee a,115 (select sale

13、_id,sale_sum116 from (select sale_id, sum(tot_amt) as sale_sum117 from sales118 group by sale_id ) b119 where b.sale_sum <1000 120 ) d121 where a.emp_no=d.sale_id 122 123 -17、查找至少銷售了3種商品的客戶編號(hào)、客戶名稱、商品編號(hào)、商品名稱、數(shù)量和金額124 select a.cust_id,cust_name,d_id,prod_name,d.qty,d.qty*d.unit_price125 from c

14、ustomer a, product b, sales c, sale_item d126 where a.cust_id=c.cust_id and d_id=d_id and 127 c.order_no=d.order_no and a.cust_id in (128 select cust_id129 from (select cust_id,count(distinct prod_id) prodid130 from (select cust_id,prod_id131 from sales e,sale_item f132 where e.order_no=f.

15、order_no) g133 group by cust_id134 having count(distinct prod_id)>=3) h )135 136 -18、查找至少與世界技術(shù)開發(fā)公司銷售相同的客戶編號(hào)、名稱和商品編號(hào)、商品名稱、數(shù)量和金額137 select a.cust_id,cust_name,d_id,prod_name,qty,qty*unit_price138 from customer a, product b, sales c, sale_item d139 where a.cust_id=c.cust_id and d_id=d

16、_id and 140 c.order_no=d.order_no and not exists141 (select f.*142 from customer x ,sales e, sale_item f143 where cust_name='世界技術(shù)開發(fā)公司' and x.cust_id=e.cust_id and144 e.order_no=f.order_no and not exists145 ( select g.*146 from sale_item g, sales h147 where d_id = d_id and g.order_n

17、o=h.order_no and148 h.cust_id=a.cust_id)149 )150 151 19、查找表中所有姓劉的職工的工號(hào)，部門，薪水152 select emp_no,emp_name,dept,salary153 from employee154 where emp_name like '劉%'155 156 20、查找所有定單金額高于2000的所有客戶編號(hào)157 select cust_id158 from sales159 where tot_amt>2000160 161 21、統(tǒng)計(jì)表中員工的薪水在4000-6000之間的人數(shù)162 selec

18、t count(*)as 人數(shù)163 from employee164 where salary between 4000 and 6000165 166 22、查詢表中的同一部門的職工的平均工資，但只查詢住址是上海市的員工167 select avg(salary) avg_sal,dept 168 from employee 169 where addr like '上海市%'170 group by dept171 172 23、將表中住址為"上海市"的員工住址改為"北京市"173 update employee 174 set a

19、ddr like '北京市'175 where addr like '上海市'176 177 24、查找業(yè)務(wù)部或會(huì)計(jì)部的女員工的基本信息。178 select emp_no,emp_name,dept179 from employee 180 where sex='F'and dept in ('業(yè)務(wù)','會(huì)計(jì)')181 182 25、顯示每種產(chǎn)品的銷售金額總和，并依銷售金額由大到小輸出。183 select prod_id ,sum(qty*unit_price)184 from sale_item 185 gr

20、oup by prod_id186 order by sum(qty*unit_price) desc187 188 26、選取編號(hào)界于'C0001'和'C0004'的客戶編號(hào)、客戶名稱、客戶地址。189 select CUST_ID,cust_name,addr190 from customer 191 where cust_id between 'C0001' AND 'C0004'192 193 27、計(jì)算出一共銷售了幾種產(chǎn)品。194 select count(distinct prod_id) as '共銷售產(chǎn)品數(shù)

21、'195 from sale_item 196 197 28、將業(yè)務(wù)部員工的薪水上調(diào)3%。198 update employee199 set salary=salary*1.03200 where dept='業(yè)務(wù)'201 202 29、由employee表中查找出薪水最低的員工信息。203 select *204 from employee205 where salary=206 (select min(salary )207 from employee )208 209 30、使用join查詢客戶姓名為"客戶丙"所購貨物的"客戶名稱&

22、quot;,"定單金額","定貨日期","電話號(hào)碼"210 select a.cust_id,b.tot_amt,b.order_date,a.tel_no211 from customer a join sales b212 on a.cust_id=b.cust_id and cust_name like '客戶丙'213 214 31、由sales表中查找出訂單金額大于"E0013業(yè)務(wù)員在1996/10/15這天所接每一張訂單的金額"的所有訂單。215 select *216 from sal

23、es217 where tot_amt>all218 (select tot_amt 219 from sales 220 where sale_id='E0013'and order_date='1996/10/15')221 order by tot_amt222 223 32、計(jì)算'P0001'產(chǎn)品的平均銷售單價(jià)224 select avg(unit_price)225 from sale_item226 where prod_id='P0001'227 228 33、找出公司女員工所接的定單229 select sa

24、le_id,tot_amt230 from sales231 where sale_id in 232 (select sale_id from employee233 where sex='F')234 235 34、找出同一天進(jìn)入公司服務(wù)的員工236 select a.emp_no,a.emp_name,a.date_hired237 from employee a238 join employee b239 on (a.emp_no!=b.emp_no and a.date_hired=b.date_hired)240 order by a.date_hired241 2

25、42 35、找出目前業(yè)績超過232000元的員工編號(hào)和姓名。243 select emp_no,emp_name244 from employee 245 where emp_no in246 (select sale_id247 from sales 248 group by sale_id249 having sum(tot_amt)<232000)250 251 36、查詢出employee表中所有女職工的平均工資和住址在上海市的所有女職工的平均工資252 select avg(salary)253 from employee254 where sex like 'f

26、9;255 union256 select avg(salary)257 from employee258 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、 找出目前銷售業(yè)績超過10000元的業(yè)務(wù)員編號(hào)及銷售業(yè)績，并按銷售業(yè)績從大到小排序。267 Sele

27、ct sale_id ,sum(tot_amt)268 from sales 269 group by sale_id 270 having sum(tot_amt)>10000271 order by sum(tot_amt) desc272 273 39、 找出公司男業(yè)務(wù)員所接且訂單金額超過2000元的訂單號(hào)及訂單金額。 274 Select order_no,tot_amt275 From sales ,employee276 Where sale_id=emp_no and sex='M' and tot_amt>2000277 278 40、 查詢sale

28、s表中訂單金額最高的訂單號(hào)及訂單金額。279 Select order_no,tot_amt from sales 280 where tot_amt=(select max(tot_amt) from sales)281 282 41、 查詢?cè)诿繌堄唵沃杏嗁徑痤~超過4000元的客戶名及其地址。283 Select cust_name,addr from customer a,sales b 284 where a.cust_id=b.cust_id and tot_amt>4000285 286 42、 求出每位客戶的總訂購金額，顯示出客戶號(hào)及總訂購金額，并按總訂購金額降序排列。287

29、 Select cust_id,sum(tot_amt) from sales288 Group by cust_id 289 Order by sum(tot_amt) desc290 291 43、 求每位客戶訂購的每種產(chǎn)品的總數(shù)量及平均單價(jià)，并按客戶號(hào)，產(chǎn)品號(hào)從小到大排列。292 Select cust_id,prod_id,sum(qty),sum(qty*unit_price)/sum(qty)293 From sales a, sale_item b294 Where a.order_no=b.order_no295 Group by cust_id,prod_id296 Orde

30、r by cust_id,prod_id297 298 44、 查詢訂購了三種以上產(chǎn)品的訂單號(hào)。299 Select order_no 300 from sale_item301 Group by order_no302 Having count(*)>3303 304 45、 查詢訂購的產(chǎn)品至少包含了訂單3號(hào)中所訂購產(chǎn)品的訂單。305 Select distinct order_no306 From sale_item a307 Where order_no<>'3'and not exists ( 308 Select * from sale_item b

31、 where order_no ='3' and not exists 309 (select * from sale_item c where c.order_no=a.order_no and d_id=d_id)310 311 46、 在sales表中查找出訂單金額大于"E0013業(yè)務(wù)員在1996/11/10這天所接每一張訂單的金額"的所有訂單，并顯示承接這些訂單的業(yè)務(wù)員和該訂單的金額。312 Select sale_id,tot_amt from sales313 where tot_amt>all(select tot_a

32、mt 314 from sales 315 where sale_id='E0013' and order_date='1996-11-10') 316 317 47、 查詢末承接業(yè)務(wù)的員工的信息。318 Select *319 From employee a320 Where not exists 321 (select * from sales b where a.emp_no=b.sale_id)322 323 48、 查詢來自上海市的客戶的姓名，電話、訂單號(hào)及訂單金額。324 Select cust_name,tel_no,order_no,tot_am

33、t325 From customer a ,sales b326 Where a.cust_id=b.cust_id and addr='上海市'327 328 49、 查詢每位業(yè)務(wù)員各個(gè)月的業(yè)績，并按業(yè)務(wù)員編號(hào)、月份降序排序。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) desc333 334 50、 求每種產(chǎn)品的總銷售數(shù)量及總銷售金額，要求顯示出產(chǎn)品編號(hào)、產(chǎn)品名稱，總數(shù)量及總金額，并按產(chǎn)品號(hào)從小到大排列。 3