Student(S#,Sname,Sage,Ssex)学生表Course(C#,Cname,T#)课程表SC(S#,C#,score)成绩表Teacher(T#,Tname)教师表
建表语句
1CREATETABLEstudent2(3s#INT,4snamenvarchar(32),5sageINT,6ssexnvarchar(8)7)89CREATETABLEcourse10(11c#INT,12cnamenvarchar(32),13t#INT14)1516CREATETABLEsc17(18s#INT,19c#INT,20scoreINT21)2223CREATETABLEteacher24(25t#INT,26tnamenvarchar(16)27)插入测试数据语句
1insertintoStudentselect1,N'刘一',18,N'男'unionall2select2,N'钱二',19,N'女'unionall3select3,N'张三',17,N'男'unionall4select4,N'李四',18,N'女'unionall5select5,N'王五',17,N'男'unionall6select6,N'赵六',19,N'女'78insertintoTeacherselect1,N'叶平'unionall9select2,N'贺高'unionall10select3,N'杨艳'unionall11select4,N'周磊'1213insertintoCourseselect1,N'语文',1unionall14select2,N'数学',2unionall15select3,N'英语',3unionall16select4,N'物理',41718insertintoSC19select1,1,56unionall20select1,2,78unionall21select1,3,67unionall22select1,4,58unionall23select2,1,79unionall24select2,2,81unionall25select2,3,92unionall26select2,4,68unionall27select3,1,91unionall28select3,2,47unionall29select3,3,88unionall30select3,4,56unionall31select4,2,88unionall32select4,3,90unionall33select4,4,93unionall34select5,1,46unionall35select5,3,78unionall36select5,4,53unionall37select6,1,35unionall38select6,2,68unionall39select6,4,71问题
本题用到下面三个关系表：
CARD借书卡。CNO卡号，NAME姓名，CLASS班级
BOOKS图书。BNO书号，BNAME书名,AUTHOR作者，PRICE单价，QUANTITY库存册数
BORROW借书记录。CNO借书卡号，BNO书号，RDATE还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下15个处理：
S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号、学员姓名、所属单位、学员年龄
C(C#,CN)C#,CN分别代表课程编号、课程名称
SC(S#,C#,G)S#,C#,G分别代表学号、所选修的课程编号、学习成绩
要求实现如下5个处理：
1--1.使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名2--实现代码：3SELECTSN,SDFROMS4WHERE[S#]IN(5SELECT[S#]FROMC,SC6WHEREC.[C#]=SC.[C#]7ANDCN=N'税收基础')8910--2.使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位11--实现代码：12SELECTS.SN,S.SDFROMS,SC13WHERES.[S#]=SC.[S#]14ANDSC.[C#]='C2'1516--3.使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位17--实现代码：18SELECTSN,SDFROMS19WHERE[S#]NOTIN(20SELECT[S#]FROMSC21WHERE[C#]='C5')2223--4.使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位24--实现代码：25SELECTSN,SDFROMS26WHERE[S#]IN(27SELECT[S#]FROMSC28RIGHTJOINCONSC.[C#]=C.[C#]29GROUPBY[S#]30HAVINGCOUNT(*)=COUNT(DISTINCT[S#]))3132--5.查询选修了课程的学员人数33--实现代码：34SELECT学员人数=COUNT(DISTINCT[S#])FROMSC3536--6.查询选修课程超过5门的学员学号和所属单位37--实现代码：38SELECTSN,SDFROMS39WHERE[S#]IN(40SELECT[S#]FROMSC41GROUPBY[S#]42HAVINGCOUNT(DISTINCT[C#])>5)