数据库实验

–查询products表中P_price在800以上的商品详细信息
–select *
–from products
–where P_price>800

–查询products表中p_quantity在20和50之间的商品编号，商品名称和商品数量
–select P_no,P_name,P_quantity
–from products
–where P_quantity between 20 and 50

–查询orders表中各会员购买商品的总量，并以汉字列标题形式输出会员帐号，商品总额，请写出SQL代码
–select O_quantity 商品总量,M_account as 会员账号,P_price*O_quantity as 商品总额
–from orders,products
–where orders.P_no=products.P_no

–查询members表中家庭地址为“湖南”的会员详细信息，请写出SQL代码
–select *
–from members
–where M_address like ‘湖南%’

–查询members表中年龄大于30且性别为“男”的会员详细信息
–select *
–from members
–where (YEAR(GETDATE())-YEAR(M_birth))>30 and M_sex =‘男’

–查询orders表各商品销售总量前3名的商品编号和销售总量，请写出SQL代码
–select top 3 P_no 商品编号,sum(O_quantity) 销售总量
–from orders
–group by P_no•
–order by sum(O_quantity) desc;

–查询orders表中购买过商品的会员帐号，要求去掉重复行
–select distinct M_account
–from orders;

–查询orders表已确认、已支付和已配送的订单详细信息，请写出SQL代码
–select *
–from orders
–where O_confirm_state=1 and O_pay_state=1 and O_send_state=1;

–查询2005年6月6日前，所有商品的订购总量，要求输出商品号和订购总量，请写出SQL代码
–select P_no 商品号,SUM(O_quantity) 订单总量
–from orders
–where O_date<‘2005-06-06’
–group by P_no

–查询所有会员的平均月薪，最高月薪和最低月薪之和，请写出SQL代码
–select AVG(M_salary)+MAX(M_salary)+MIN(M_salary)
–from members

–查询购买了商品号为“0910810004”的会员号和姓名，并以汉字标题显示，请写出SQL代码
–select M_name 姓名,members.M_account 会员号
–from members
–join orders
–on members.M_account=orders.M_account
–where P_no=‘0910810004’

–查询购买了商品名称为“爱国者MP3”的会员号、姓名和商品价格，请写出SQL代码
–select members.M_account 会员号,M_name 姓名,P_price
–from members
–join orders
–on members.M_account=orders.M_account
–join products
–on orders.P_no=products.P_no and P_name=‘爱国者MP3’;

–查询比“张自梁”月薪高的而和他不是同一地址的会员姓名和年龄，请写出SQL代码
–select A.M_name,A.M_birth
–from members A
–join members B
–on A.M_account<>B.M_account
–and B.M_name=‘张自梁’
–and A.M_salary>B.M_salary
–and A.M_address<>B.M_address

–使用exists查询购买了“0910810004”商品的会员号和姓名，请写出SQL代码
–select M_account,M_name
–from members
–where exists(select *
–from orders
–where members.M_account=orders.M_account
–and P_no=‘0910810004’)

–使用in查询与“刘法治”购买至少同一种商品的会员号和商品号，请写出SQL代码
–select distinct A.M_account,A.P_no
–from orders A
–where P_no in(select P_no
–from orders B
–where A.M_account<>B.M_account
–and B.M_account in(select M_account from members
–where B.M_account=members.M_account and M_name=‘刘法治’));

–在members表中创建地址为“湖南株洲”的会员的视图V_addr，请写出SQL代码
–create view V_addr
–as select *
–from members
–where M_address=‘湖南株洲’;

–在orders表中创建购买了商品号为“0910810004”商品的视图V_buy，请写出SQL代码
–create view V_buy
–as select *
–from products
–where P_no=‘09108100004’;

–在members和orders表上创建“湖南株洲”的会员购买了商品号为“0910810004”商品的视图V_addr_buy
–create view V_addr_buy
–as select members.*
–from members
–join orders
–on members.M_account=orders.M_account and P_no=‘0910810004’ and members.M_account=‘湖南株洲’;

–在视图V_addr上查询性别为“男”的会员信息，请写出SQL代码
–go
–select *
–from V_addr
–where M_sex=‘男’;

–在视图V_addr中增加一条记录（内容如下），并查看members表中记录的改变情况。记录内容如下：（T-SQL）
–insert into V_addr values(‘fengxk’,‘冯向克’,‘1978-06-28’,‘男’,‘北京市’,5000.0,‘fxk0628’)
–select *
–from members;

–将视图V_addr中会员号为“liuzc518”的会员的密码修改为“liuzc0518”，并查看members中记录的改变情况，请写出SQL代码
–update V_addr set M_password=‘liuzc0518’
–where M_password=‘liuzc518’
–select *
–from members
–where M_account=‘liuzc0518’

–在V_addr中删除会员号为“fengxk”的记录，并查看members中记录的改变情况，请写出SQL代码
delete from V_addr where M_account=‘fengxk’
select *
from members
where M_account=‘fengxk’