数据库zj练习题

数据库练习题：p65、1、（1）createdatabasewebshop2on(name=webshop2_dat,filename='f:\data\webshop2_dat.mdf',size=5mb,filegrowth=1mb)logon(name=webshop2_log,filename='f:\data\webshop2_log.ldf',size=2mb,filegrowth=10)（3）1、usewebshop2alterdatabasewebshop2addlogfile(name=webshop2_log1,filename='f:\data\webshop2_log1.ldf')2、alterdatabasewebshop2modifyfile(name=webshop2_dat,maxsize=500mb)3、alterdatabasewebshop2modifyfile(name=webshop2_log,maxsize=300mb)--教材P107课堂实践11、SELECT*FROMCustomers2、SELECTc_ID用户名,c_Name真实姓名,c_TrueName真实姓名,c_Password密码FROMCustomers3、SELECTc_ID,c_Name,c_TrueName,year(Getdate())-year(c_Birth)c_age,c_PasswordFROMCustomers4、SELECTc_ID用户名,c_Name真实姓名,c_TrueName真实姓名,year(Getdate())-year(c_Birth)年龄,c_Password密码FROMCustomers--教材P113课堂实践21、SELECT*FROMCustomersWHEREc_Type='VIp'2、SELECTc_ID编号,c_Name姓名,c_TrueName真实姓名,c_Gender性别,c_Birth出生年月,c_CardID身份证号,c_Address籍贯,c_Postcode邮编,c_Mobile手机,c_Phone联系电话,c_Email邮箱,c_Password密码,c_SafeCode安全码,c_Question提示问题,c_Answer提示答案,c_Type用户类型FROMCustomersWHEREc_Type='VIp'ANDc_Gender='男'3、SELECTc_Name姓名,c_Gender性别,c_Birth出生年月,c_Address籍贯,c_Phone联系电话,c_Email邮箱FROMCustomersWHEREc_Type='VIp'ANDc_Gender='男'ANDLEFT(c_Address,2)='湖南'4、SELECT*FROMCustomersWHEREc_EmailLIKE'%_@163.%'5、SELECTTOP10PERCENT*FROMCustomers6、SELECTc_Name姓名,c_Gender性别,c_Birth出生年月,c_Address籍贯FROMCustomersWHEREc_TrueNameLIKE'刘%'--教材P119课堂实践31、--对会员信息表Customers表按年龄进行按降序排列SELECT*FROMCustomersORDERBYyear(Getdate())-year(c_Birth)DESC2、--对会员信息表Customers表按会员类型c_Type进行升序排列，类型相同的按年龄排列SELECT*FROMCustomersORDERBYc_Type,year(Getdate())-year(c_Birth)DESC3、--统计Orders表中男、女会员的总人数SELECTc_Gender,COUNT(c_Gender)总人数FROMCustomersGROUPBYc_Gender4、--统计Orders表中每一个会员的订单总额，并显示大于平均总额的会员编号和订单总额SELECTc_ID会员编号,sum(o_sum)订单总额FROMOrdersGROUPBYc_IDHAVINGsum(o_sum)(selectAVG(o_sum)FROMOrders)5、--统计Orders表中每一天的订单总额，按降序排列SELECTo_Date日期,sum(o_sum)订单总额FROMOrdersGROUPBYo_DateORDERBYsum(o_sum)DESC5、--使用COMPUTE语句统计产生订单的天数和订单总额SELECTo_Date,o_sumFROMOrdersorderbyo_dateCOMPUTEcount(o_date),SUM(o_sum)byo_date--教材P125课堂实践4（1）查询每笔订单的基本信息（订单号、订单日期、总金额）以及付款方式（名称），和处理该订单的员工名称。1、selecto_id,o_date,o_sum,p_mode,e_namefromordersjoinpaymentsonorders.p_id=payments.p_idjoinemployeesonemployees.e_id=orders.e_id（2）使用WHERE语句来实现下面的查询操作。需要了解不低于“三星SGH-C178”价格的商品号、商品名称和商品单价，查询后的结果要求按商品单价升序排列。SELECTG2.g_ID商品号,G2.g_Name商品名称,G2.g_Price价格FROMGoodsG1,GoodsG2whereG1.g_Name='三星SGH-C178'ANDG1.g_Price=G2.g_PriceORDERBYG2.g_Price（3）实现订单表（Orders）和订单详情表（OrderDetails）的左外连接。select*fromordersoleftjoinorderdetailsodono.o_id=od.o_id（4）实现订单表（Orders）和订单详情表（OrderDetails）的右外连接。select*fromordersorightjoinorderdetailsodono.o_id=od.o_id（5）实现订单表（Orders）和订单详情表（OrderDetails）的完整外部连接。select*fromordersofulljoinorderdetailsodono.o_id=od.o_idP133:1、usewebshopselecto_id,o_date,c_id,o_sumfromorderswhereo_idin(selecto_idfromorderswheree_idin(selecte_idfromemployeeswheree_name='张小路'))2、selecto_id,g_id,d_price,d_numberfromorderdetailswhereo_idin(selecto_idfromorderswheree_idin(selecte_idfromemployeeswheree_name='张小路'))3、select*fromorderdetailsselect*intood_tempfromorderdetailsselect*fromod_temp4、insertintosale(d_avg,d_sum)selectavg(d_price),sum(d_number)fromorderdetailsselect*fromsale5、updateordersseto_status=truewhereo_idin(selecto_idfromorderswheree_idin(selecte_idfromemployeeswheree_name='张小路'))P1541、usewebshopcreateviewvw_hotgoodsasselectg_idas商品号,g_nameas名称,g_priceas价格,g_discountas折扣,g_numberas数量fromgoodswhereg_status='热点'2、select*fromvw_hotgoodswhere价格25003、updatevw_hotgoodsset折扣='0.75'where名称='飞利浦292'4、createviewvw_tnamegoodsasselectg_idas商品号,g_nameas商品名称,t_nameas类别名称,g_priceas商品价格fromgoodsjointypesongoods.t_id=types.t_idwithcheckoption5、select*fromvw_tnamegoodswhere类别名称='通信产品'and商品名称like'%三星%'6、alterviewvw_hotgoodsasselectg_idas商品号,g_nameas名称,g_priceas价格,g_numberas数量fromgoods7、dropviewvw_hotgoodsdropviewvw_tnamegoodsP94、createdatabasewebshop2on(name=webshop2_dat,filename='d:\data\webshop2_dat.mdf',size=10mb,maxsize=500mb,filegrowth=1mb)logon(name=webshop2_log,filename='d:\data\webshop2_log.ldf',size=1mb,maxsize=200mb,filegrowth=10)altertableemployeesaddconstraintpk_e_idprimarykey(e_id)altertablepaymentsaddconstraintpk_p_idprimarykey(p_id)createdatabaseorderso_idchar(14)notnullprimarykey,e_idchar(10)notnullreferencesemployees(e_id),p_idchar(2)notnullreferencespayments(p_id)altertablepaymentsaddunique(p_mode)usewebshopaltertablecustomersaltercolumnc_genderchar(2)notnullaltertablecustomersaddconstraintdef_typdefault'普通用户'forc_typealtertablecustomersaddconstraintdef_genderdefault'男'forc_genderaltertablecustomersaddconstraintch_gendercheck(c_gender='男'orc_gender='女')altertablecustomersaddconstraintch_cardidcheck(c_cardidlike'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'orc_cardidlike'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')altertablecustomersaddconstraintch_emailcheck(c_emaillike'%_@%.%')