SQL答案(最新整理)

1、電話郵政編碼地址客戶號訂單號客戶號聯(lián)系人客戶名稱產(chǎn)品號產(chǎn)品名稱規(guī)格說明單價產(chǎn)品客戶訂購日期訂購單訂單號序號產(chǎn)品號數(shù)量圖1 訂單管理數(shù)據(jù)庫產(chǎn)品1. 查詢客戶表中的所有記錄；2. 從訂購單表中查詢客戶號信息（哪些客戶有訂購單）。3. 查詢單價在 x 元以上（含）的產(chǎn)品信息。4. 查詢單價在 x 元以上（不含）的某產(chǎn)品（按產(chǎn)品名稱查詢）信息。5. 查詢單價在 x 元以上（不含）的某兩種產(chǎn)品（按產(chǎn)品名稱查詢）信息。6. 查詢有某年某月訂購單的客戶名稱、聯(lián)系人、電話號碼和訂單號信息。7. 查詢有某產(chǎn)品（按產(chǎn)品名稱查詢）訂貨的客戶的名稱、聯(lián)系人和電話號碼信息。8. 查詢有某產(chǎn)品（按產(chǎn)品名稱查詢）訂購需求的

2、訂購單明細(xì)記錄。9. 查詢所有訂購數(shù)量（即訂購明細(xì)中每個訂購項(xiàng)目的數(shù)量）都在 x 元以上的訂購單的信息。10. 找出和某產(chǎn)品（按產(chǎn)品名稱查詢）同等價位的所有產(chǎn)品信息。11. 查詢單價范圍在 x 元到 y 元范圍內(nèi)的產(chǎn)品信息。12. 從客戶表 中查詢出客戶名稱中有“xx二字的客戶信息（使用 like 運(yùn)算符）。13. 從客戶表 中查詢出客戶名稱中沒有“xx二字的客戶信息（使用 not like 運(yùn)算符）。14. 按產(chǎn)品的單價升序列出全部產(chǎn)品信息。15. 先按產(chǎn)品名稱排序，在按單價排序列出全部產(chǎn)品信息。16. 從產(chǎn)品表中查出共有幾種產(chǎn)品。17. 從訂購明細(xì)表中查詢某產(chǎn)品（按產(chǎn)品名稱）的訂購總數(shù)。1

3、8. 計(jì)算某產(chǎn)品（按產(chǎn)品名稱）所有訂購的總金額。19. 求所有訂購單的平均金額，在查詢結(jié)果中列出訂購單的個數(shù)和平均金額。20. 求每個訂購單訂購的項(xiàng)目數(shù)和總金額。21. 求每個客戶包含了某產(chǎn)品（按產(chǎn)品名稱）訂購的訂單號及其最高金額和最低金額。22. 求至少有兩個訂購項(xiàng)目的訂購單的平均金額。23. 找出尚未最后確定訂購單（即訂購日期為空值的記錄）的有關(guān)客戶信息（客戶的名稱、聯(lián)系人和電話號碼）和訂單號。24. 找出在某年某月某日之后簽定的訂購單的客戶信息（客戶名稱、聯(lián)系人和電話號碼）、訂單號和訂購日期。25. 列出每類產(chǎn)品（相同名稱）具有最高單價的產(chǎn)品信息（產(chǎn)品號，名稱，規(guī)格說明和單價），提示：使

4、用內(nèi)外層互相關(guān)嵌套查詢。26. 確定哪些客戶目前沒有訂購單（使用謂詞 not exists）。27. 確定目前有訂購單的客戶信息（使用謂詞 exists ）。28. 查詢符合條件的某產(chǎn)品（按產(chǎn)品名稱）信息，產(chǎn)品的單價達(dá)到了任意一款某產(chǎn)品（按產(chǎn)品名稱）單價的一半（使用 any 或 some 量詞）。29. 查詢符合條件的某產(chǎn)品（按產(chǎn)品名稱）信息，產(chǎn)品的單價大于任何一款某產(chǎn)品（按產(chǎn)品名稱）的單價（使用 all 量詞）。30. 設(shè)計(jì)如下的連接操作，并分析各自的特點(diǎn)：廣義笛卡兒積、內(nèi)連接、左連接、右連接、全連接。answer：create table customers(customerid varc

5、har(8) primary key, customername varchar(10) not null, contacts varchar(10) not null, address varchar(50),tel varchar(15)create table goods(goodsid varchar(8) primary key, goodsname varchar(20) not null, descrip varchar(200),address varchar(50), price money)create table orders(orderid varchar(10) pr

6、imary key, customerid varchar(8) not null, orderdate datetime default(getdate(),foreign key(customerid) references customers(customerid)create table item(id varchar(20) primary key, orderid varchar(10) not null, goodsid varchar(8) not null, num int not null,foreign key(orderid) references orders(ord

7、erid), foreign key(goodsid) references goods(goodsid),)select * from customersselect distinct(customerid) from orders select * from goods where price50select * from goods where price50 and goodsname!=手機(jī)select * from goods where price50 and goodsname not in(手機(jī),移動硬盤) select c.customername,c.contacts,c

8、.tel,o.orderidfrom customers c,orders owhereo.orderdatebetween2011-9-10:0:0and2011-9-3023:59:59and o.customerid=c.customeridselect customername,contacts,telfrom customerswhere customerid in( select customerid from orderswhere orderid in( select orderid from itemwhere goodsid=( select goodsid from go

9、odswhere goodsname=毛巾)select * from orderswhere orderid in( select orderid from itemwhere goodsid=( select goodsid from goodswhere goodsname=毛巾)select num from itemwhere goodsid in( select goodsid from goods whereprice60)select g1.*from goods g1,goods g2where g1.price=g2.price and g2.goodsname=碗 and

10、 g1.goodsname!=碗select * from goodswhere price between 69 and 300select *from customerswhere customername like %李晶%select *from customerswhere customername not like %李晶%select * from goodsorder by price ascselect * from goodsorder by goodsname desc,price select count(*) from goodsselect goodsid,sum(

11、num) from itemgroup by goodsidselect sum(g.price*i.num) as 總金額from goods g,item iwhere goodsname=移動硬盤and g.goodsid=i.goodsidselect count(*) as 訂單個數(shù),avg(g.price*i.num) as 平均金額from goods g,item iwhere g.goodsid=i.goodsidselect orderid,count(orderid) 項(xiàng)目數(shù) ,sum(g.price*i.num) 總金額from goods g,item iwhere

12、g.goodsid=i.goodsid group by orderidselect i.orderid,max(g.price*i.num) 最高金額 ,min(g.price*i.num) 最低金額from item i,goods g,orders owhere g.goodsname=毛巾 and i.goodsid=g.goodsid and i.orderid=o.orderid group by i.orderidselect i.orderid,avg(g.price*i.num) 平均金額,count(*) from item i,goods g,orders owhere

13、i.goodsid=g.goodsid and i.orderid=o.orderid group by i.orderidhaving count(*)=2select o.orderid,c.customername,c.contacts,c.telfrom orders o,customers cwhere o.customerid=c.customerid and o.orderdate is nullselect o.orderid,c.customername,c.contacts,c.tel,o.orderdate from orders o,customers cwhere o

14、.customerid=c.customerid and o.orderdate=2011-9-22select max(g.price),g.goodsname from goods gwhere g.goodsname in( select g1.goodsname from goods g1where g.goodsname=g1.goodsname)group by g.goodsnameselect out.goodsid,out.goodsname,out.descrip,out.pricefrom goods out where price=(select max(price)

15、from goods inner1 where out.goodsname=inner1.goodsname)select *from customers where not exists (select * from orderswhere orders.customerid=customers.customerid)select goodsname from goods where price =any(select price/2 from goods)select goodsname from goods where price =all(select max(price) from

16、goods)sql 中大概有這么幾種 join： cross joininner join left outer joinright outer join full outer join首先都是基于 cross join(笛卡爾乘積），然后是 inner join，在笛卡爾乘積的結(jié)果集中去掉不符合連接條件的行。left outer join 是在 inner join 的結(jié)果集上加上左表中沒被選上的行，行的右表部分每個字段都用 null 填充。right outer join 是在 inner join 的結(jié)果集上加上右表中沒被選上的行，行的左表部分全用null 填充。sql 之 join 用

17、法完全版一、各種 join 的含義sql 中大概有這么幾種 join： cross joininner join left outer joinright outer join full outer join首先都是基于 cross join(笛卡爾乘積），然后是 inner join，在笛卡爾乘積的結(jié)果集中去掉不符合連接條件的行。left outer join 是在 inner join 的結(jié)果集上加上左表中沒被選上的行，行的右表部分每個字段都用 null 填充。right outer join 是在 inner join 的結(jié)果集上加上右表中沒被選上的行，行的左表部分全用null 填充。o

18、uter 的意思就是沒有關(guān)聯(lián)上的行。二、舊式寫法和標(biāo)準(zhǔn)寫法：1、inner join code as the following:select * from a a, b b where a.categoryid = b.categoryid; equals:select * from a a inner join b b on a.categoryid = b.categoryid; 2、outer join code as the followingselect * from a a full(left/right) outerjoin b bon a on a.categoryid =

19、b.categoryid;equals:select * from a a, b b where a.categoryid *=b.categoryid; select * from a a, b b where a.categoryid =*b.categoryid;三、例子table a have 12( 8+4) entries, 8 entries have valid relation with b table b have 80(77+3) entries , 77 entries have valid relation with a.then the return amount

20、of join is :cross join : 12*80 inner join : 77full outer join : 77+4+3 left outer join: 77 + 4right outrer join: 77 + 3select o.orderid,c.customername,c.contacts,c.tel,o.orderdate from orders o,customers cwhere o.customerid=c.customeridselect o.orderid,c.customername,c.contacts,c.tel,o.orderdate fro

21、m orders o full outer join customers con o.customerid=c.customeridselect o.orderid,c.customername,c.contacts,c.tel,o.orderdate from orders o right outer join customers con o.customerid=c.customeridselect o.orderid,c.customername,c.contacts,c.tel,o.orderdatefrom orders o left outer join customers c on o.customerid=c.customeridselect o.orderid,c.customername,c.contacts,c.tel,o.orderdate from orders o ,custom