数据库zj练习题.docVIP

数据库练习题： p65、 1、（1）create database webshop2 on (name=webshop2_dat,filename=f:\data\webshop2_dat.mdf, size=5mb,filegrowth=1mb) log on (name=webshop2_log,filename=f:\data\webshop2_log.ldf, size=2mb,filegrowth=10) （3）1、use webshop2 alter database webshop2 add log file (name=webshop2_log1,filename=f:\data\webshop2_log1.ldf) 2、alter database webshop2 modify file (name=webshop2_dat,maxsize=500mb) 3、alter database webshop2 modify file (name=webshop2_log,maxsize=300mb) --教材P107课堂实践1 1、SELECT * FROM Customers 2、SELECT c_ID 用户名,c_Name 真实姓名,c_TrueName 真实姓名,c_Password 密码 FROM Customers 3、SELECT c_ID ,c_Name ,c_TrueName,year(Getdate())-year(c_Birth) c_age ,c_Password FROM Customers 4、SELECT c_ID 用户名,c_Name 真实姓名,c_TrueName 真实姓名,year(Getdate())-year(c_Birth) 年龄,c_Password 密码 FROM Customers --教材P113课堂实践2 1、SELECT * FROM Customers WHERE c_Type=VIp 2、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 Customers WHERE c_Type=VIp AND c_Gender=男 3、SELECT c_Name 姓名,c_Gender 性别,c_Birth 出生年月,c_Address 籍贯,c_Phone 联系电话,c_Email 邮箱 FROM Customers WHERE c_Type=VIp AND c_Gender=男 AND LEFT (c_Address,2)=湖南 4、SELECT * FROM Customers WHERE c_Email LIKE %_@163.% 5、SELECT TOP 10 PERCENT * FROM Customers 6、SELECT c_Name 姓名,c_Gender 性别,c_Birth 出生年月,c_Address 籍贯 FROM Customers WHERE c_TrueName LIKE 刘% --教材P119课堂实践3 1、--对会员信息表Customers表按年龄进行按降序排列 SELECT * FROM Customers ORDER BY year(Getdate())-year(c_Birth) DESC 2、--对会员信息表Customers表按会员类型c_Type进行升序排列，类型相同的按年龄排列 SELECT * FROM Customers ORDER BY c_Type,year(Getdate())-year(c_Birth) DESC 3、--统计Orders表中男、女会员的总人数 SELECT c_Gender, COUNT(c_Gender) 总人数 FROM Customers GROUP BY c_Gender 4、--统计Orders表中每一个会员的订单总额，并显示大于平均总额的会员编号和订单总额 SELECT c_ID 会员编号 , sum(o_sum)订单总额 FROM Orders GROUP BY c_ID HAVING sum(o_sum) (select AVG(o_sum) FROM Orders) 5、--统计Orders表中每一天的订单总额，按降序排列 SELECT o_Date 日期, sum