数据库zj练习题.doc

数据库练习题：p65、1、（1）create database webshop2on (name=webshop2_dat,filename=f:datawebshop2_dat.mdf,size=5mb,filegrowth=1mb)log on(name=webshop2_log,filename=f:datawebshop2_log.ldf,size=2mb,filegrowth=10)（3）1、use webshop2alter database webshop2add log file (name=webshop2_log1,filename=f:datawebshop2_log1.ldf)2、alter database webshop2modify file (name=webshop2_dat,maxsize=500mb)3、alter database webshop2modify file (name=webshop2_log,maxsize=300mb) -教材P107课堂实践11、SELECT * FROM Customers2、SELECT c_ID 用户名,c_Name 真实姓名,c_TrueName 真实姓名,c_Password 密码FROM Customers3、SELECT c_ID ,c_Name ,c_TrueName,year(Getdate()-year(c_Birth) c_age ,c_Password FROM Customers4、SELECT c_ID 用户名,c_Name 真实姓名,c_TrueName 真实姓名,year(Getdate()-year(c_Birth) 年龄,c_Password 密码FROM Customers-教材P113课堂实践21、SELECT * FROM CustomersWHERE c_Type=VIp2、SELECT c_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 用户类型FROM CustomersWHERE c_Type=VIp AND c_Gender=男 3、SELECT c_Name 姓名,c_Gender 性别,c_Birth 出生年月,c_Address 籍贯,c_Phone 联系电话,c_Email 邮箱FROM CustomersWHERE c_Type=VIp AND c_Gender=男 AND LEFT (c_Address,2)=湖南4、SELECT * FROM CustomersWHERE c_Email LIKE %_163.%5、SELECT TOP 10 PERCENT * FROM Customers6、SELECT c_Name 姓名,c_Gender 性别,c_Birth 出生年月,c_Address 籍贯FROM CustomersWHERE c_TrueName LIKE 刘%-教材P119课堂实践31、-对会员信息表Customers表按年龄进行按降序排列SELECT * FROM CustomersORDER BY year(Getdate()-year(c_Birth) DESC2、-对会员信息表Customers表按会员类型c_Type进行升序排列，类型相同的按年龄排列SELECT * FROM CustomersORDER BY c_Type,year(Getdate()-year(c_Birth) DESC3、-统计Orders表中男、女会员的总人数SELECT c_Gender, COUNT(c_Gender) 总人数FROM CustomersGROUP BY c_Gender4、-统计Orders表中每一个会员的订单总额，并显示大于平均总额的会员编号和订单总额SELECT c_ID 会员编号 , sum(o_sum)订单总额 FROM OrdersGROUP BY c_IDHAVING sum(o_sum) (select AVG(o_sum) FROM Orders)5、-统计Orders表中每一天的订单总额，按降序排列SELECT o_Date 日期, sum(o_sum) 订单总额 FROM OrdersGROUP BY o_DateORDER BY sum(o_sum) DESC5、-使用COMPUTE语句统计产生订单的天数和订单总额SELECT o_Date,o_sumFROM Ordersorder by o_dateCOMPUTE count(o_date),SUM (o_sum) by o_date -教材P125课堂实践4（1）查询每笔订单的基本信息（订单号、订单日期、总金额）以及付款方式（名称），和处理该订单的员工名称。1、select o_id,o_date,o_sum,p_mode,e_namefrom ordersjoin paymentson orders.p_id=payments.p_idjoin employeeson employees.e_id=orders.e_id（2）使用WHERE语句来实现下面的查询操作。需要了解不低于“三星SGH-C178”价格的商品号、商品名称和商品单价，查询后的结果要求按商品单价升序排列。SELECT G2.g_ID 商品号,G2.g_Name 商品名称,G2.g_Price 价格FROM Goods G1,Goods G2where G1.g_Name=三星SGH-C178 AND G1.g_Price25003、update vw_hotgoodsset 折扣=0.75where 名称=飞利浦2924、create view vw_tnamegoodsasselect g_id as 商品号,g_name as 商品名称,t_name as 类别名称,g_price as 商品价格from goodsjoin typeson goods.t_id=types.t_idwith check option5、select * from vw_tnamegoodswhere 类别名称=通信产品 and 商品名称like %三星%6、alter view vw_hotgoodsasselect g_id as 商品号,g_name as 名称,g_price as 价格,g_number as 数量from goods7、drop view vw_hotgoods drop view vw_tnamegoods P94、create database webshop2on (name=webshop2_dat,filename=d:datawebshop2_dat.mdf,size=10mb,maxsize=500mb,filegrowth=1mb)log on(name=webshop2_log,filename=d:datawebshop2_log.ldf,size=1mb,maxsize=200mb,filegrowth=10)alter table employeesadd constraint pk_e_id primary key (e_id) alter table paymentsadd constraint pk_p_id primary key (p_id)create database orderso_id char(14) not null primary key,e_id char(10) not null references employees (e_id),p_id char (2) not null references payments (p_id)alter table paymentsadd unique (p_mode)use webshopalter table customers alter column c_gender char(2) not nullalter table customers add constraint def_typ default 普通用户 forc_typealter table customers add constraint def_gender default 男 forc_genderalter table customers add constraint ch_gender check (c_gender= 男 or c_gender=女)alter table customers add constraint ch_cardid check (c_car