SQL课后题.docx

1、SQL课后题学生选课教师关系表1） 打开“SQL server Management Studio”，单击在 “新建查询”按钮；在查询窗口中，输入如下命令。create table Student ( StudentNo int, sname nvarchar(20), age int, sex BIT NOT NULL DEFAULT 1)INSERT INTO Student VALUES(990015, 王芳,21, 0 ),(990016, 王柳,20, 1 ),(990017, 张笑,19, 0 ),(990018, 李明,20, 1 ),(990019, 学友,21, 1 ),(9

2、90020, 马晓,20, 0 )create table Course( CourseNo int, coursename nvarchar(20), TeacherNo int ) INSERT INTO Course VALUES(1234, 高数, 15 ),(1122, 大物, 16 ),(1017, 英语, 17 ),(1019, 地概, 18 )create table Teacher ( TeacherNo int, tname nvarchar(20), title nvarchar(20) )INSERT INTO Teacher VALUES(15, 娜娜, 高数 ),(

3、16, 王二, 大物 ),(17, 小刚, 英语 ),(18, 老大, 地概 )create table SeleCourse ( StudentNo int, CourseNo int, score int)INSERT INTO SeleCourse VALUES(990015, 1234, 88 ),(990016, 1122, 90 ),(990016, 1234, 90 ),(990017, 1122, 79 ),(990018, 1017, 76 ),(990019, 1019, 83 ),(990019, 1234, null ),(990020, 1019, 88 )2） 在“

4、SQL编辑器工具栏中，单击”执行“按钮，表创建成功后，在结果窗口会出现“命令已成功完成。”的信息提示；向表中读入数据，运行成功后，出现“x行受影响”的信息提示；3） 在窗口中输入以下SQL查询命令并执行：3.2题：1.检索年龄小于21岁的女学生的学号和姓名SELECT StudentNo,sname FROM StudentWHERE Age1order by 2 DESC,15.检索学号比王琳同学大，年龄比她小的学生姓名SELECT sname/*学号比王琳同学大，年龄比她小的学生姓名*/FROM Student WHERE StudentNoALL(SELECT StudentNo fro

5、m Student where sname=王琳)andage=(select avg(age) from Student where sex=0)9. 求年龄大于等于所有女同学年龄的男生姓名和年龄SELECT sname,age /*年龄大于等于所有女同学年龄的男生姓名和年龄*/FROM Student WHERE sex=1 and age=all(select age from Student where sex=0)3.12题：1. 往关系Course中插一个课程元组(0008,VC+,06)insert into Course values (0008,VC+,06)2. 检索所授每

6、门课程的平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在表COMP中create table FACULTY(TNAME char(8) not null)insert into FACULTY(TNAME)select tnamefrom Teacherwhere TeacherNo in(select TeacherNo from Course where CourseNo in (select CourseNo from SeleCourse group by CourseNohaving AVG(SCORE)80)3. 在SC中删除无成绩的选课元组delete from

7、SeleCourse where score is null4. 把选修LIU老师课程的女同学选课元组全部删去delete from SeleCourse where StudentNo in(select StudentNo from Student where sex=0) and CourseNo in(select CourseNo from Course ,Teacher where Course .TeacherNo =Teacher .TeacherNo and tname =王二)5. 把高数课不及格的成绩全部改为60update SeleCourse set score =

8、60where score 60 and CourseNo in(select CourseNo from Course where coursename =高数)6. 把低于所有课程总平均成绩的女学生成绩提高5%update SeleCourse set score = score *1.05where StudentNo in (select StudentNo from Student where sex =0) and score 70;update SeleCourse set score = score *1.05where CourseNo=1234 and score 70 t

9、hen 1.04 else 1.05 end where CourseNo =1234 7. 在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%update SeleCourse set score = score *1.05where score 40 and SEX =M3.假设每个职工只在一个公司工作，检索工资超过3600元的男职工工号和姓名select EMP.E#,ENAMEfrom EMP,WORKSwhere EMP.E# =WORKS .E# and SEX =M and SALARY 36004.假设每个职工可在多个公司工作，检索至少在编号A1和A5公司兼职的职工工号和

10、姓名drop table EMPcreate table EMP(E# char(8) not null,ENAME char(8) not null,AGE int,SEX char(1),ECITY char(8)primary key(E#)INSERT INTO EMP VALUES(1001, 王名,50, M ,山东),(1002, 小火,46, F ,北京),(1003, 大雷,33, M ,上海),(1004, 刘二,35, M ,山东),(1005, 王琪琪,40, F ,黑龙江),(1006, 刘梅,35, F ,陕西),(1007, 韩雪,29, F ,甘肃)drop t

11、able COMPcreate table COMP(C# char(8) not null,CNAME char(8) not null,CITY char(8)primary key(C#)INSERT INTO COMP VALUES(A1,信息公司,上海),(A5,建设公司,深圳),(B3,联华公司,北京)drop table WORKScreate table WORKS(E# char(8) not null,C# char(8) not null,SALARY int,primary key(E#,C#),foreign key(E#) references EMP,foreig

12、n key(C#) references COMP )INSERT INTO WORKS VALUES(1001, A1,15000),(1002, A1,4600),(1002, A5,4600),(1002, B3,4000),(1003, A5,4000),(1004, A1,3500),(1005, A1,4000),(1005, A5,3000),(1006, A5,3500),(1007, A1,3500)检索：select EMP.E#,EMP.ENAMEfrom EMP,WORKS as a,WORKS as bwhere EMP .E# =a .E# and a.E# =b.

13、E# and a.C#=A1 and b.C#=A55.检索在“建设公司”工作，工资超过1000元的男性职工的工号和姓名select EMP.E#,EMP.ENAMEfrom EMP,WORKS ,COMP where EMP .E# =WORKS .E# and WORKS .C# =COMP .C# and CNAME =建设公司 and SALARY 1000 and SEX =M6. 假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和公资总数，显示（E#，NUM，SUM_SALARY），分别表示工号，公司数目和工资总数select E#,COUNT(C#) as NUM,COU

14、NT(SALARY) as SUM_SALARYfrom WORKSgroup by E#7.工号为1005的职工在多个公司工作，试检索至少在1005职工兼职的所有公司工作的职工工号select x.E#from WORKS xwhere not exists(select * from WORKS y where E#=1004 and not exists (select * from WORKS z where z.E#=x.E# and z.C#=y.C#);8.检索信息公司中低于本公司平均工资的职工工号和姓名select EMP.E#,EMP.ENAMEfrom EMP,WORKS,

15、COMP where EMP .E# =WORKS .E# and WORKS.C# =COMP .C# and CNAME =信息公司and SALARY =40)10.在EMP表和WORKS表中删除年龄大于60岁的职工的有关元组delete from WORKS where E# in (select E# from EMP where AGE =40);delete from EMP where AGE 40;3.15题：对3.2题中的数据库中基本表SC建立一个视图1）打开“SQL Server Management Studio”窗口。2）单击：“标准”工具栏上的“新建查询”按钮，打开

16、“查询编辑器”窗口。在窗口内直接输入以下语句，按要求创建视图。在数据库中，基于SC表创建一个名为” S_SCORE”的视图，要求该视图中包含列“课程号”、“课程名”、“开课学期”和“学时”、并且限定视图中返回的行中只包括第3学期及以后开课的课程信息。 CREATE VIEW S_SCORE(StudentNo,CourseNo_NUM,AVG_score) AS SELECT StudentNo,COUNT(CourseNo),AVG(score) FROM SeleCourse GROUP BY StudentNo;3）单击“SQL编辑器”工具栏上的“分析”按钮，检查输入的T-SQL语句是否有语法错误。4）查询与分析1. select * from S_SCORE允许查询：select StudentNo,COUNT(CourseNo)as CourseNo_NUM,AVG(score) as AVG_scorefrom SeleCourse group by StudentNo2. select StudentNo,CourseNo_NUMfrom S_SCOREwhere AVG_score80允许查询：select StudentNo,COUNT(CourseNo)as CourseNo_NUMfrom SeleCourse