1、-查詢會(huì)員的select year(GETDATE()-year(c_Birth) asfrom Customers-year 截取年份year(GETDATE()當(dāng)前時(shí)間-查詢籍貫為“湖南”的會(huì)員c_Address from Customers where c_Address like 湖南%select-查詢select and 30在-30歲的會(huì)員* from Customers where year(GETDATE()-year(c_Birth) betn 20-查詢來自“湖南長沙”和“湖南株洲”兩地的會(huì)員信息select * from Customers where c_Addres

2、s =湖南長沙市or c_Address =湖南株洲市-查詢“湖南”省的所有的會(huì)員或者是在歲以下的會(huì)員號(hào)、會(huì)員名稱、籍貫的select 會(huì)員號(hào)=c_ID,會(huì)員名稱=c_TrueName,=c_Gender,籍貫=c_Address,=year(GETDATE()-year(c_Birth) from Customerswhere c_Address like 湖南% and c_Gender = 男or year(GETDATE()- year(c_Birth) 2500 order by g_Price desc,t_ID asc-查詢每一類商品總數(shù)selectt_ID,COUNT(1) f

3、rom Goods group by t_ID-查詢每一個(gè)訂單的總金額，并根據(jù)訂單總額進(jìn)行升序排列select o_Sum from Ordersorder by o_Sum asc-查詢訂單總額大于的訂單信息，并升序排列select * from Orders where o_Sum5000 order by o_Sum asc-查詢訂單表中的單價(jià)和數(shù)量的總和-select d_Price,d_Number,COUNT(1) from OrderDetails group by d_Price,d_Numberselect d_Price,d_Number from OrderDetails

4、 compute sum(d_Price),sum(d_Number)-分頁和：在訂單表中按訂單總額進(jìn)行排序，查詢其中的第-5條select * from (select ROW_NUMBER()over(order by g_id)as 分頁列,g_ID,g_name from Goods)as temp where 分頁列 betn 3 and 5-查詢商品的商品號(hào)、商品名稱和商品種類名稱select Goods.t_ID,g_ID,g_Name,t_Name Goods.t_ID=Types.t_IDfrom Goods,Types where-查詢不低于“三星SGH-C178”價(jià)格的商

5、品號(hào)、商品名稱和商品單價(jià)，查詢的結(jié)果要求按商品單價(jià)升序排列select g_ID,g_Name,g_Price from Goods where g_Price(select g_Price from Goods where g_Name=三星SGH-C178) order by g_Price asc-查詢所有商品類別及其對(duì)應(yīng)商品信息，如果商品類別沒有對(duì)應(yīng)商品也需要顯示其類別信息 -副表right join 主表每一項(xiàng)都要要顯示selectGoods.t_ID,g_ID,g_Name,t_Name,g_Description,g_Sus,g_ProduceDatefrom Goods rig

6、ht joypes on Goods.t_ID=Types.t_ID-查詢所有訂單所訂購的商品信息和訂單日期select Orders.o_ID ,o_Date ,Goods.g_ID,g_Name,g_Price,g_ProduceDate,g_Description from Orders,OrderDetails,GoodswhereOrders.o_ID=OrderDetails.o_ID and Goods.g_ID=OrderDetails.g_ID-對(duì)會(huì)員信息表customers按進(jìn)行降序排列select * from Customers order by (select yea

7、r(GETDATE()- year(c_Birth) )desc-對(duì)會(huì)員信息表按會(huì)員類型進(jìn)行升序排列，類型相同的按進(jìn)行降序排列select * from Customers year(c_Birth) )descorder by c_Type asc,(select year(GETDATE()-統(tǒng)計(jì)表中的男、女會(huì)員的總?cè)藬?shù)selectCOUNT(1) from Customers group by c_Gender-統(tǒng)計(jì)訂單表中每一會(huì)員的訂單總額，并顯示大于平均總額的會(huì)員和訂單總額select SUM(o_sum),c_id from orders1 group by c_ID select

8、 SUM(o_sum)/COUNT(distinct c_id) from orders select SUM(o_sum),c_id from orders group by c_ID havingSUM(o_sum)(select sum(o_sum)/count(distinct c_id)from orders)-統(tǒng)計(jì)訂單表中每一天的訂單總額，并根據(jù)訂單總額進(jìn)行降序排列select o_Date,SUM(o_Sum) from Orders group by o_Date order by SUM(o_Sum)desc使用pute語句統(tǒng)計(jì)產(chǎn)生訂單的天數(shù)和訂單總額select o_Dat

9、e,SUM(o_Sum) from Orders group by o_Date compute count(o_date),sum(sum(o_sum)10.09select*o temp from Goodsselect* from tempcreatetable avggoods(t_id char(2) ,g_avg float)inserto avggoods(t_id,g_avg)select t_id,avg(g_price) from Goodsgroup by t_IDselect * from avggoods-將商品中類別名稱為“家用電器”的商品折扣修改為.8set g_

10、Discount=0.1 where 家用電器= (select t_name fromupdate GoodsTypes where Goods.t_ID=Types.t_ID)update Goods set g_Discount=0.8 where t_ID=(select t_ID from Types where t_name = 家用電器)select * from Goods select * from Types-刪除類別名稱為“家用電器”的商品的基本信息DELETE FROM temp where t_ID=(select t_ID from Types where t_na

11、me = 家用電器)-查詢“三星”的商品以及價(jià)格不高于的商品g_Name like %三星% union select * fromselect * from Goods where Goods where g_Price3000-視圖create view v_id asselect Orders.c_ID from Orders,Customers where Orders.c_ID=Customers.c_IDselect * from v_id-任務(wù)：創(chuàng)建視圖，查詢“熱點(diǎn)”商品的商品號(hào)、商品名稱、類別號(hào)、商品價(jià)格、商品折扣和商品數(shù)量信息。createselect selectview

12、v_sus as* from Goods where g_Sus=熱點(diǎn)* from v_sus-任務(wù)：創(chuàng)建視圖查詢訂單所訂購的商品信息(商品名稱、create view v_Orders as價(jià)格和數(shù)量)和訂單日期select Orders.o_ID,o_Date,g_Name,d_Price,d_Number from Orders,OrderDetails,Goodswhere Orders.o_ID=OrderDetails.o_ID and OrderDetails.g_ID=Goods.g_ID select * from v_Orders-任務(wù)：創(chuàng)建視圖查詢商品的商品號(hào)、商品名稱、

13、類別名稱和商品價(jià)格信息create view v_Goods asselect g_ID,g_Name,Goods.t_ID,g_Price from Goods,Types where Goods.t_ID=Types.t_IDselect * from v_Goods-任務(wù)：創(chuàng)建視圖查詢某一類商品的類別號(hào)和該商品的最高價(jià)格信息create view v_Types as-修改視圖ALTER VIEW v_Types asselect goods.t_ID 類別號(hào),max(g_Price)最高價(jià)格 from Goods,Types Goods.t_IDselect * from v_Type

14、sgroupbycreate view v_Go asselect g_ID,g_Name,Goods.t_ID from Goods,Types Goods.t_ID=Types.t_IDselect * from v_Gowhereexec sp_databases exec sp_tablescreate proc proc_order asselect * from orders exec proc_orderdrop proc proc_idcreate proc proc_goods(id char(6) asselect * from Goods where g_id = ide

15、xec proc_goods 010002select* from Goodscreate asproc proc_id (ID char(2)select* from Goods where t_ID=IDexec proc_id 04select CAST(GETDATE()as CHAR(16)create proc proc_insertorder(c_id char(5),o_sum float,e_id char(10),p_id char(2),o_sendmode varchar(50)asdeclare oid varchar(14),sysdate varchar(16) set sysdate=CAST(GETDATE() as CHAR(16)set oid=SUBSTRING(sysdate,7,4)+SUBSTRING(sysdate,1,2)+SUBSTRING(sysdate,4,2)set oid=oid+CAST(DATEPART(hour,getdate()as CHAR(2)+CAST(DATEPART(MINUTE,getdate()as CHAR(2)+CAST(DATEPART(SECOND,getdate()as CHAR(2)inserto ordersvalues(oid,c_id,GETDATE(),o_sum,e_id