sql考试题及答案.pdf

用sql语句完成下面题目: 1. 创建数据库db_Student，在db_Student中创建四张表： 学生表tb_Student,字段:SID(编号),SName(姓名),Sage(年龄),SSex(性别) 教师表tb_Teacher,字段:TID(编号),TName(姓名) 课程表tb_Course,字段:CID(编号),CName(名称),TID(教师编号) 成绩表tb_Score,字段:SID(编号),CID(课程编号),Score(成绩) 要求:分析添加约束,如:主键约束，外键约束等(15分) 2. 给表添加一些数据。(10分) 3. 完成下列查询:(30分) 1 查询姓李的老师有多少位。 2 查询c#课程的平均分。 3 查询sql最高分的学生编号，学生姓名及分数。 4 查询sql课程超平均分的学生姓名及分数。 5 查询没有考sql的学生编号及姓名。 6 查询没有学王老师课程的学生编号及姓名。 7 查询课程表的第5到第10条记录，条件为编号不连续。写两种方 法。 8 查询每个学生的编号，姓名，课程名称，成绩，老师信息。 9 查询c#考试成绩前三名的学生，包含并列的行。(可能查询的结果 超过三行) 10 查询c#课程比sql课程的考试分数高的学生编号及姓名。 4. 写存储过程完成下列问题，并写出存储过程执行语句：(15分) 1 根据学生编号查询学生的各科成绩及所教课程的老师 2 根据教师编号，查询所教课程(教师编号，姓名，课程编号，课程 名)。 3 写一种分页的存储过程。 5. 将第3 题的查询两题写成视图，然后执行查询视图。(10 分) 6. 创建存储过程，在存储过程里创建事务，执行删除课程表的行 时，同时删除成绩表的相应行。(10分) 7. 创建触发器，实现删除教师表的行时，同时删除课程表的相应 行。(首先删除相应表的约束)(10分) -答案- - -第一题 create database db_Student on primary( name=db_Student.mdf, filename=D:db_Student.mdf, size=10mb, maxsize=unlimited, filegrowth=10% ) log on( name=db_Student.ldf, filename=D:db_Student.ldf, size=2mb, maxsize=unlimited, filegrowth=10% ) go use db_Student -学生表 if exists(select * from sysobjects where name=tb_Student) drop table tb_Student go create table tb_Student( SID int primary key, SName varchar(10), Sage int, SSex bit ) -教师表 if exists(select * from sysobjects where name=tb_Teacher) drop table tb_Teacher go create table tb_Teacher( TID int primary key, TName varchar(10) ) -课程表 if exists(select * from sysobjects where name=tb_Course) drop table tb_Course go create table tb_Course( CID int primary key, CName varchar(20), TID int foreign key references tb_Teacher(TID) ) -成绩表 if exists(select * from sysobjects where name=tb_Score) drop table tb_Score go create table tb_Score( SID int foreign key references tb_Student(SID), CID int foreign key references tb_Course(CID), Score float ) -第题 insert into tb_Student values(1,张山,18,0) insert into tb_Student values(2,李四,16,1) insert into tb_Student values(3,王五,21,1) insert into tb_Student values(4,傻笑,20,0) select * from tb_Student insert into tb_Teacher values(1,于老师) insert into tb_Teacher values(2,於老师) insert into tb_Teacher values(3,雷老师) select * from tb_Teacher insert into tb_Course values(1,VF课程,1) insert into tb_Course values(2,VB课程,2) insert into tb_Course values(3,C#课程,3) insert into tb_Course values(4,SQL课程,3) select * from tb_Course insert into tb_Score values(1,1,80) insert into tb_Score values(1,2,70) insert into tb_Score values(1,3,70) insert into tb_Score values(1,4,70) insert into tb_Score values(2,1,90) insert into tb_Score values(2,2,50) insert into tb_Score values(2,3,80) insert into tb_Score values(2,4,50) insert into tb_Score values(3,1,50) insert into tb_Score values(3,2,50) insert into tb_Score values(3,3,90) insert into tb_Score values(3,4,90) insert into tb_Score values(4,1,50) insert into tb_Score values(4,2,50) insert into tb_Score values(4,3,100) insert into tb_Score values(4,4,90) select * from tb_Score -第题 - 查询姓李的老师有多少位 select COUNT(*) from tb_Teacher where TName like 李% - 查询c#课程的平均分 select AVG(SCore) C#课程平均分 from tb_Score where CID=(select CID from tb_Course where CName=c#课程) - 查询sql最高分的学生编号，学生姓名及分数。 select a.SID,a.SName,b.SCore from tb_Student a join (select SID,SCore from tb_Score where SCore= (select MAX(SCore) from tb_Score where CID=(select CID from tb_Course where CName=SQL课程) (这里可直接 用 order by 查询) and CID=(select CID from tb_Course where CName=SQL课程) b on a.SID=b.SID - 查询sql课程超平均分的学生姓名及分数。 select a.Sname,b.Score from tb_Student a join (select SID,SCore from tb_Score where CID=(select CID from tb_Course where CName=SQL课程) and SCore (select AVG(SCore) sql平均分 from tb_Score where CID=(select CID from tb_Course where CName=SQL课 程) b on a.SID=b.SID - 查询没有考sql的学生编号及姓名 select SID,SName from tb_Student where SID not in (select SID from tb_Score where CID=(select CID from tb_Course where CName=SQL课程) - 查询没有学(考)雷老师课程的学生编号及姓名。 select * from tb_Teacher select * from tb_Score select * from tb_Course select * from tb_Student select SID,SName from tb_Student where SID not in (select SID from tb_Score where CID in (select CID from tb_Course where TID= (select TID from tb_Teacher where TName=雷老师) - 查询课程表的第到第条记录，条件为编号不连续。写两种 方法 select top 6 * from tb_Course where (TID not in (select top 4 TID from tb_Course ORDER BY TID) ORDER BY TID select top 6 * from tb_Course where (TID (select MAX(TID) from (select top 4 TID from tb_Course ORDER BY TID) as T) ORDER BY TID - 查询每个学生的编号，姓名，课程名称，成绩，老师信 息。 select b.SID,b.SName,c.CName,a.Score,d.TName from tb_Score a ,tb_Student b,tb_Course c,tb_Teacher d where a.SID=b.SID and a.CID=c.CID and c.TID=d.TID - 查询c#考试成绩前三名的学生，包含并列的行。(可能查 询的结果超过三行) select * from tb_Student where SID in (select top 3 with ties SID from tb_Score where CID=(select CID from tb_Course where CName=c#课程) order by Score desc) -这里with ties 表示可以包含并列的行 - 查询c#课程比sql课程的考试分数高的学生编号及姓名。 select SID,Sname from tb_Student where SID in (select a.SID from (select * from tb_Score where CID=(select CID from tb_Course where CName=c#课程) a join (select * from tb_Score where CID=(select CID from tb_Course where CName=SQL课程) b on a.SID=b.SID where a.Scoreb.Score) -第题 -1 if exists(select * from sysobjects where name=prc_SelectInfo) drop proc prc_SelectInfo go create proc prc_SelectInfo ( SID int ) as select a.Sname,b.Score,c.Cname,d.TName from (select * from tb_Student where SID=SID) a join (select * from tb_Score where SID=SID) b on a.SID=b.SID join tb_Course c on b.CID=c.CID join tb_Teacher d on d.TID=c.TID go exec prc_SelectInfo 1 -2 if exists(select * from sysobjects where name=prc_TInfo) drop proc prc_TInfo go create proc prc_TInfo ( TID int ) as select a.TID,a.Tname,b.CID,b.CName from (select * from tb_Teacher where TID=TID) a join tb_Course b on a.TID=b.TID go exec prc_TInfo 1 -3 if exists(select * from sysobjects where name=prc_Page) drop proc prc_Page go create proc prc_Page ( Page int, -每页数据大小 index int -第几页 ) as select top(Page) * from tb_Course where (CID not in (select top (Page*(index-1) CID from tb_Course ORDER BY CID) ORDER BY CID go select * from tb_Course exec prc_Page 3,2 -第题 if exists(select * from sysobjects where name=V_Vew1) drop view V_Vew1 go create view V_Vew1 as select b.SID,b.SName,c.CName,a.Score,d.TName from tb_Score a ,tb_Student b,tb_Course c,tb_Teacher d where a.SID=b.SID and a.CID=c.CID and c.TID=d.TID go select * from V_Vew1 if exists(select * from sysobjects where name=V_Vew2) drop view V_Vew2 go create view V_Vew2 as select * from tb_Student where SID in (select top 3 SID from tb_Score where CID=(select CID from tb_Course where CName=c#课程) order by Score desc) go select * from V_Vew2 -第题 if exists(select * from sysobjects where name=prc_tran) drop proc prc_tran go create proc prc_tran (