1、物料表、物料生效通知表的数据库操作题

表1 S1#(item_code,item_name, creater,create_tine, i tem_ver, ca_id; 物料表(物料号，物料名称，创建人，创建时间、物料版本，通知单号) (key: item_code + ca_id)表2 S2#(ca_id, ca_descri, ca_valid_time); 物料生效通知单表(通知单号，通知单说明，生效时间) (key: ca_id)

问题1、写出liuzz创建的所有物料信息

SELECT*FROMS1#，S#2 WHERE S1#.ca_id = S2#.ca_id AND creater = 'liuzz';

问题2，查询通知单编号为CA001所生效的物料，物料创建人以及物料的版本信息

SELECTS1#.item_code, S1#.creater, S1#.item_ver FROM S1#, S2# WHERE S1#.ca_id = S2#.ca_id AND S2#.ca_id = 'CA001';

问题3，更新物料编号为S001的创建人为LIUQQ

UPDATE S1# SET creater ='LIUQQ'WHERE item_code ='S001';

问题4，统计出在2014/05/04后通知单生效的所有物料个数（要求去掉重复物料）

SELECTCOUNT(DISTINCTS1#.item_code) FROM S1#, S2# WHERE S1#.ca_id = S2#.ca_id AND S2#.ca_valid_time > '2014/05/04;

2、库存、出库关系的数据库题

1）分别写出AAA的建表语句；

答案：

CREATETABLEAAA (Mc种类VARCHAR(50)NOTNULL,S1库存总量INTNOTNULL);

2）用一条SQL语句求出A出库的数量是多少？

答案：

SELECTsum( S1出库数量 )AS出库总量FROMBBBWHEREMc种类 ='A';

3）用一条SQL语句求出A,B各剩下多少库存？

答案：

SELECTAAA.Mc种类, S1库存总量 -COALESCE(SUM(BBB.S1出库数量),0)AS剩余库存量FROMAAALEFTJOINBBBONAAA.Mc种类 = BBB.Mc种类WHEREAAA.Mc种类IN('A','B')GROUPBYAAA.Mc种类, S1库存总量;

3、学生表、班级信息表、学生班级表的数据库操作

1）select*fromstudentwherescore >80orscorein(60,61,62);2）selectclass.c_name,count(*),avg(score)fromstudent,class,student_classwherestudent.s_id = student_class.s_idandstudent_class.c_id = class.c_idandstudent.gender ='女'groupbyc_id；

4、stock_rating表操作

参考答案：

SELECTstockId, rating, ratingDateFROMstock_ratingWHEREstockIdIN('600036','002146')ANDratingDate = (SELECTMAX(ratingDate)FROMstock_ratingWHEREstockIdIN('600036','002146'))

5、客户表、商品表、商家表、购买记录表的数据操作

I.客户表customer(CId,Cname,CDate,Ctype)--CId 客户编号,Cname 客户名称,Cdate创建日期,Ctype客户类别

II.商品表good(GId,Gname,SId)--GId商品编号Gname 商品名称,SId商家编号

III.商家表seller (SId,Sname)--SId 商家编号,Sname 商家名称

IV.购买记录表order(CId, GId,num)--CId 客户编号,GId 货物编号,num购买件数

现需按照如下要求编写出对应的SQL语句：

1. 查询买过「xx商家」（Sname=’xx商家’）商品的用户信息

2. 查询购买两种及以上商品，且每种类别购买数都少于100件的客户姓名、客户编号，和客户的平均购买件数

3. 查询同时购买01,02商品且购买" 01"商品比" 02 "商品件数多的用户信息和购买的件数

参考答案：

1、

SELECTc.CId, c.CnameFROMcustomer cINNERJOINorderoONc.CId = o.CIdINNERJOINgood gONo.GId = g.GIdINNERJOINseller sONg.SId = s.SIdWHEREs.Sname ='xx商家';

2、

SELECTc.CId, c.Cname,AVG(o.num)ASaverage_purchasesFROMcustomer cINNERJOINorderoONc.CId = o.CIdGROUPBYc.CId, c.CnameHAVINGCOUNT(DISTINCTo.GId) >=2ANDMAX(o.num) <100;

3、

SELECTc.CId, c.Cname, o1.numASnum_01, o2.numASnum_02FROMcustomer cINNERJOINordero1ONc.CId = o1.CIdANDo1.GId ='01'INNERJOINordero2ONc.CId = o2.CIdANDo2.GId ='02'WHEREo1.num > o2.num;

6、学生表、课程表、学生课程关联表的数据操作

参考答案：

1、查询每个学生所选择的课程SELECT

答案：

student.sname, course.cname FROM student,stu_course,coursewherestudent.sid = stu_course.sid and stu_course.cid = course.cid;

2、选择了所有课程的学生

答案：

SELECTs.sid, s.snameFROMstudent sWHERENOTEXISTS(SELECTc.cidFROMcourse cWHERENOTEXISTS(SELECTsc.sidFROMstu_course scWHEREsc.sid = s.sidANDsc.cid = c.cid));

7、base_user表、account表的数据操作

有如下两张数据表：

表一：base_user

表二：account

请用一条sql查询语句，查询出以下结果：

参考答案：

select*frombase_user t1leftjoinaccountast2ont1.id = t2.id;

8、管理培训信息系统的表数据操作

为管理培训信息，建立3个表

1、S(Sno,SName,Dept,Age)Sno,SNameDept,Age分别代表学号，学员姓名，所属单位，学员年龄

2、C(Cno,CName)Cno,CName分别代表课程编号，课程名称

3、SC(Sno,Cno,Score)Sno,Cno,G分别代表学号，所选的课程编号，学习成绩

问题：

(1)查询选修课程名称为”科研管理”的学员学号和姓名?

(2)查询没选修课程编号为”C5”的学员姓名和所属单位?

(3)列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

(4)查询选修了课程的学员人数

(5)查询选修课程超过5门的学员学号和所属单位?

参考答案：

1）selectS.Sno,S.SNamefromC,S,SCwhereS.Sno=SC.SnoandSC.Cno=C.CnoandC.CName="科研管理";2）selectS.SName,S.DeptfromS,SCwhereS.Sno=SC.SnoandSC.Sno <>"C5";3）selectS.SNname,avg(SC.Score)fromS, SCwhereS.Sno=SC.SnoandSC.Score<60groupbyS.SNamehavingcount(distinctSC.Cno)>=2;4）select学员人数=count(distinctSno)fromSC;5）selectSno,DeptfromSwhereSnoin(selectSnofromSCgroupbySnohavingcount(*)>5);

9、学生成绩信息表的数据操作

1、按分数倒序排列显示出姓名、科目、分数

答案：

Select姓名,科目,分数from表Aorderby分数desc

2、查询出语文分数大于70分的人数

答案：

Selectcount(1)from表Awhere科目=’语文’and分数>70

3、查询出数学最高分的姓名、分数

答案：

Select姓名,分数from表Awhere科目=’数学’orderby分数desclimit1

4、查询出所有科目评价为良好的男生姓名、科目、分数

答案：

Select姓名,科目,分数from表Awhere性别=’男’and分数>=71and分数<=85

5、一条查询中分别输出男女生的数学最低分

答案：

Selectmin(分数),性别fromAwhere科目=’数学’groupby性别

6、一条查询中输出语文和数学分数前两名的姓名、科目、分数

Select姓名,科目,分数fromAwhere科目=’语文’orderby分数desclimit2UnionallSelect姓名,科目,分数fromAwhere科目=’数学’orderby分数desclimit2

7、将所有男生的性别修改成01

update表Aset性别=01where性别=“男”

8、根据分数上下限设置表A的是否及格列（优秀、良好、及格都表示及格）以及评价列

UpdateA ainnerjoinB bona.分数>=b.分数下限anda.分数<=b.分数上限set评价

10、学生成绩表操作

MySQL数据库中有一个学生成绩表student ,一共有name, score两个字段，分别表示学生的姓名和分数。请查询出分数排名前60%的学生名单及其对应的分数。

参考答案：

select*from(SELECTA.*, @row_num:=@row_num+1ASROW_NUMFROM(selectname,scorefromstudent1orderbyscoredesc) A, (SELECT@row_num:=0) B) CwhereC.ROW_NUM<=(@row_num*0.6)

学无止境，行以致远。