数据库系统原理与应用(sql server 2005)课后实训题sql语句

《数据库系统原理与应用(sql server 2005)课后实训题sql语句》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、数据库系统原理与应用(SQLServer2005)课后实训题SQL语句--实训4-1--1SELECTg_Name,t_ID,g_Price*g_NumberFROMGoods--2SELECTg_Name,g_ID,g_Price*g_NumberFROMGoods--3SELECTg_Name商品名称,g_ID商品编号,g_Price*g_Number总价值FROMGoods--4SELECT*FROMGoodsWHEREg_status='热点'--5SELECTg_Name商品名称,g_ID商品编号,g_Price商品价格,t_ID商品类别号FROM

2、GoodsWHEREt_ID='01'ANDg_Price>2500--6SELECTc_CardID,c_Name,c_Gender,c_Address,year(getdate())-year(c_Birth)c_ageFROMCustomersWHEREc_Addresslike'湖南%'ANDc_Gender='男'ANDyear(getdate())-year(c_Birth)<=30--7SELECTc_Name,c_Address,year(getdate())-year(c_Birth)N1FROMCustomersWHEREyear(get

3、date())-year(c_Birth)>=20andyear(getdate())-year(c_Birth)<=25--8SELECTc_Name,c_Address,year(getdate())-year(c_Birth)N1FROMCustomersWHEREyear(getdate())-year(c_Birth)<=20ORyear(getdate())-year(c_Birth)>=25--9SELECT*FROMCustomersWHEREc_Addresslike'湖南%'--10SELECT*FROMCustomersWHEREnot

4、c_Addresslike'湖南%'--11SELECT*FROMGoodsWHEREg_Namelike'三星%'--12SELECTc_Name,c_TrueName,c_Mobile,c_EmailFROMCustomersWHEREc_TrueNamelike'黄_'--13SELECT*FROMGoodsWHEREg_Image=null--14SELECTDISTINCTOrders.c_IDFROMCustomersjoinordersonCustomers.c_ID=Orders.c_ID--15SELECTTOP8*FROMGoods--1

5、6SELECTg_ID,g_Name,g_PriceFROMGoodsWHEREt_ID='01'orderbyg_Pricedesc--17SELECTg_ID,g_Name,g_Price,t_IDFROMGoodsWHEREg_Price>2500orderbyt_IDasc,g_Pricedesc--18SELECTt_ID,g_NumberFROMGoodsORDERBYt_IDCOMPUTESUM(g_NUMBER)BYt_ID--19SELECTo_ID,o_SumFROMordersorderbyo_Sumasc--20SELECTo_ID,

6、o_SumFROMorderswhereo_Sum>5000orderbyo_Sumasc--21SELECT*FROM(SELECTo_Sum,ROW_NUMBER()OVER(ORDERBYo_Sumasc)AS行号FROMOrders)AStempwhere行号between3and5--4-2.1selectg_id,g_name,t_idFROMgoods--4-2.2selectgoods.g_name,goods.g_price,goods.g_number,orders.o_datefromorderdetailsjoingoodsonord

7、erdetails.g_id=goods.g_idjoinordersonorderdetails.o_id=orders.o_id--4-2.3selectg2.g_id,g2.g_name,g2.g_pricefromgoodsg1joingoodsg2ong1.g_name='三星SGH-C178'andg1.g_price<=g2.g_priceorderbyg2.g_price--4-2.4select*fromtypesleftouterjoingoodson.types.t_id=goods.t_id--4-2.5select*fromgood

8、sleftouterjoinorderdetails