sqlServer分析题

《sqlServer分析题》由会员分享，可在线阅读，更多相关《sqlServer分析题（50页珍藏版）》请在装配图网上搜索。

1、第一组：问题描述：已知关系模式：S (SNO,SNAME） 学生关系。SNO 为学号，SNAME 为姓名C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩1. 找出没有选修过“李明”老师讲授课程的所有学生姓名（4分）2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩（4分）3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名（4分）二、定义一个十进制转换成2进制的标量函数。（4分）三、活期存款中，“储户”通过“存取款单”和“储蓄

2、所”发生联系。假定储户包括：账号，姓名，电话，地址，存款额；“储蓄所”包括：储蓄所编号，名称，电话，地址（假定一个储户可以在不同得储蓄所存取款）1、写出设计以上表格的语句（4分）2、创建一个触发器TR1完成下面内容：当向“存取款单”表中插入数据时，如果存取标志则应该更改储户表让存款额加上存取金额，如果存取标志0则应该更改储户表让存款额减去存取金额,如果余额不足显示余额不足错误。（5分）答案：准备数据create table s(sno int,sName varchar(100)create table c(cno int,cName varchar(100),CTEACHER varchar

3、(100)create table sc(sno int,cno int,scGrade int)insert into svalues(1,lfm1)insert into svalues(2,lfm2)insert into svalues(3,lfm3)insert into svalues(4,lfm4)insert into svalues(5,lfm5)insert into svalues(6,lfm6)insert into cvalues(1,ch1,李明)insert into cvalues(2,ch2,王刚)insert into cvalues(3,ch3,ll)in

4、sert into cvalues(4,ch4,ff)insert into cvalues(5,ch5,ffd)insert into cvalues(6,ch6,se)insert into cvalues(7,ch7,s)insert into scvalues(1,2,80)insert into scvalues(2,2,50)insert into scvalues(1,1,60)insert into scvalues(1,3,90)insert into scvalues(3,2,55)insert into scvalues(1,6,77)insert into scvalu

5、es(3,4,80)insert into scvalues(4,2,70)1. 找出没有选修过“李明”老师讲授课程的所有学生姓名-实现代码：SELECT SNAME FROM SWHERE NOT EXISTS( SELECT * FROM SC,C WHERE SC.CNO=C.CNO AND CNAME=李明 AND SC.SNO=S.SNO) select sname from swhere sno not in(select s.sno from sinner join scon sc.sno=s.snoinner join con o=owhere cteacher=李明)2. 列

6、出有二门以上（含两门）不及格课程的学生姓名及其平均成绩-实现代码：SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)FROM S,SC,( SELECT SNO FROM SC WHERE SCGRADE=2)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNOGROUP BY S.SNO,S.SNAMEselect s.sno,avg(scGrade) from s,scwhere s.sno in(select sc.sno from scinner join con o=owhere sc.scGrade=2) and s

7、.sno=sc.snogroup by s.sno3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名-实现代码：SELECT S.SNO,S.SNAMEFROM S,( SELECT SC.SNO FROM SC,C WHERE SC.CNO=C.CNO AND C.CNO IN(1,2) GROUP BY SNO HAVING COUNT(DISTINCT c.CNO)=2)SC WHERE S.SNO=SC.SNO select S.SNO,S.SNAME from sc,swhere cno=1 and sc.sno in(select sno from sc where c

8、no=2) and s.sno=sc.sno二、create function convert2( num int)returns varchar(100)asbegindeclare re varchar(100)set re=while num0select re=substring(01,num%2+1,1)+re,num=num/2return reendselect dbo.convert2(8)三、1、CREATE TABLE 储户(账号 INT PRIMARY KEY ,姓名 CHAR(10),电话 INT ,地址 CHAR(10),存款额MONEY)GOCREATE TABLE

9、 储蓄所(储蓄所编号 INT PRIMARY KEY,名称 CHAR(10),电话 INT,地址 CHAR(10)GOCREATE TABLE 存取款单(账号 INT NOT NULL,储蓄所编号 INT NOT NULL,存取日期 DATETIME NOT NULL,存取标志 INT NOT NULL,存取金额 MONEY)GO2、CREATE TRIGGER TR1ON 存取款单FOR INSERTASDECLARE BZ INT,MONEY MONEY,ZH INTSELECT BZ=存取标志,MONEY=存取金额,ZH=账号FROM INSERTEDIF BZ=0 BEGINUPDAT

10、E 储户SET 存款额=存款额-MONEYWHERE 账号=ZHdeclare sy moneyselect sy=存款额 from 储户WHERE 账号=ZHif sy2007-12-15 and T_time2*(select count(*) from ProWage where Wage=PWage)update ProWage set total=total+AWage,Wage=Wage+AWageelsebreakendprint一共加薪：+convert(varchar,total)+元print加薪后的程序员工资列表：select * from ProWage-调用存储过程1

11、-exec Sum_wage PWage=2000,AWage=100,total=0exec Sum_wage PWage=2200,AWage=100,total=0exec Sum_wage PWage=3000,AWage=100,total=0exec Sum_wage PWage=4000,AWage=100,total=0exec Sum_wage PWage=5000,AWage=100,total=0exec Sum_wage PWage=6000,AWage=100,total=0-2、创建存储过程2-if exists (select * from sysobjects

12、where name=Avg_wage)drop procedure Avg_wageGOcreate procedure Avg_wage PWage int,AWage int,total intas while (1=1)beginif (select Avg(Wage) from ProWage)=PWage)update ProWage set total=total+AWage,Wage=Wage+AWageelsebreakendprint一共加薪：+convert(varchar,total)+元print加薪后的程序员工资列表：select * from ProWage-调用

13、存储过程-exec Avg_wage PWage=3000,AWage=200,total=0exec Avg_wage PWage=4500,AWage=200,total=0第四组：学生成绩信息三个表，结构如下： 学生表：Member字段名称数据类型说明MIDChar(10)学生号，主键MNameChar(50)姓名课程表：字段名称数据类型说明FIDChar(10)课程，主键FNameChar(50)课程名成绩表：Score字段名称数据类型说明SIDint自动编号，主键，成绩记录号FIDChar(10)课程号，外键MIDChar(10)学生号，外键Scoreint成绩请编写T-SQL语句来

14、实现如下功能：1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表：姓名语文数学英语历史张萨78 678976王强 89 67 84 96李三70 87 92 56李四 8078 97662) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。3) 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。4) 创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。答案：USE masterGO/*$建库$*/-检验数据库是否存在，如果为真，删除此数据库-IF exists(SELECT * FROM sysdatabases WHERE na

15、me=Student) DROP DATABASE StudentGOCREATE DATABASE StudentGO-建数据表-USE StudentGOCREATE TABLE Member -学生表( MID char(10) primary key, -学生号 MName CHAR(50) NOT NULL -姓名)GOCREATE TABLE F -课程表( FID char(10) primary key, -课程号FName CHAR(50) NOT NULL -课程名)GOCREATE TABLE score -学生成绩表( SID int identity(1,1) pri

16、mary key, -成绩记录号 FID char(10) foreign key(FID) references F(FID) , -课程号 MID char(10) foreign key(MID) references Member(MID) , -学生号 Score int NOT NULL -成绩)GO-课程表中插入数据-INSERT INTO F(FID,FName)VALUES(F001,语文)INSERT INTO F(FID,FName)VALUES(F002,数学)INSERT INTO F(FID,FName)VALUES(F003,英语)INSERT INTO F(FI

17、D,FName)VALUES(F004,历史)-学生表中插入数据-INSERT INTO Member(MID,MName)VALUES(M001,张萨)INSERT INTO Member(MID,MName)VALUES(M002,王强)INSERT INTO Member(MID,MName)VALUES(M003,李三)INSERT INTO Member(MID,MName)VALUES(M004,李四)INSERT INTO Member(MID,MName)VALUES(M005,阳阳)INSERT INTO Member(MID,MName)VALUES(M006,虎子)INS

18、ERT INTO Member(MID,MName)VALUES(M007,夏雪)INSERT INTO Member(MID,MName)VALUES(M008,璐璐)INSERT INTO Member(MID,MName)VALUES(M009,珊珊)INSERT INTO Member(MID,MName)VALUES(M010,香奈儿)-成绩表中插入数据-INSERT INTO Score(FID,MID,Score)VALUES(F001,M001,78)INSERT INTO Score(FID,MID,Score)VALUES(F002,M001,67)INSERT INTO

19、Score(FID,MID,Score)VALUES(F003,M001,89)INSERT INTO Score(FID,MID,Score)VALUES(F004,M001,76)INSERT INTO Score(FID,MID,Score)VALUES(F001,M002,89)INSERT INTO Score(FID,MID,Score)VALUES(F002,M002,67)INSERT INTO Score(FID,MID,Score)VALUES(F003,M002,84)INSERT INTO Score(FID,MID,Score)VALUES(F004,M002,96)

20、INSERT INTO Score(FID,MID,Score)VALUES(F001,M003,70)INSERT INTO Score(FID,MID,Score)VALUES(F002,M003,87)INSERT INTO Score(FID,MID,Score)VALUES(F003,M003,92)INSERT INTO Score(FID,MID,Score)VALUES(F004,M003,56)INSERT INTO Score(FID,MID,Score)VALUES(F001,M004,80)INSERT INTO Score(FID,MID,Score)VALUES(F

21、002,M004,78)INSERT INTO Score(FID,MID,Score)VALUES(F003,M004,97)INSERT INTO Score(FID,MID,Score)VALUES(F004,M004,66)INSERT INTO Score(FID,MID,Score)VALUES(F001,M006,88)INSERT INTO Score(FID,MID,Score)VALUES(F002,M006,55)INSERT INTO Score(FID,MID,Score)VALUES(F003,M006,86)INSERT INTO Score(FID,MID,Sc

22、ore)VALUES(F004,M006,79)INSERT INTO Score(FID,MID,Score)VALUES(F002,M007,77)INSERT INTO Score(FID,MID,Score)VALUES(F003,M008,65)INSERT INTO Score(FID,MID,Score)VALUES(F004,M007,48)INSERT INTO Score(FID,MID,Score)VALUES(F004,M009,75)INSERT INTO Score(FID,MID,Score)VALUES(F002,M009,88)- 1)查询各个学生语文、数学、

23、英语、历史课程成绩-SELECT Member.MName AS 姓名,英语 = SUM(CASE F.FName WHEN 语文 THEN Score.Score END), 数学 = SUM(CASE F.FName WHEN 数学 THEN Score.Score END), 语文 = SUM(CASE F.FName WHEN 英语 THEN Score.Score END), 历史 = SUM(CASE F.FName WHEN 历史 THEN Score.Score END) FROM Score, Member,F WHERE F.FID = Score.FID AND Memb

24、er.MID =Score.MID GROUP BY Member.MName - 2)查询四门课中成绩低于70分的学生及相对应课程名和成绩-select 姓名=(select MName from Member where MID=Score.MID),课程名=(select FName from F where FID=Score.FID),成绩=Score from Score where Score70- 3)统计各个学生四课程的平均分，且按平均分数由高到底排序-select 姓名=(select MName from Member where MID=Score.MID),平均分=A

25、vg(Score) from Score group by MID order by 平均分 desc- 4)创建存储过程-if exists (select * from sysobjects where name=P_stu)drop procedure P_stuGOcreate procedure P_stunum intAsprint参加+convert(varchar(5),num)+门课考试的学生姓名及学号：select 姓名=(select MName from Member where MID=Score.MID),学号=MID from Score group by MID

26、 having count(*)=num -调用存储过程-exec P_stu num=2第五组：设教学数据库中有三个基本表：学生表S（SNO，SNAME，AGE，SEX）选课表SC（SNO，CNO，GRADE）课程表C（CNO，CNAME，TEACHER）1.试写出下列查询语句的关系代数表达式：检索全是女同学选修的课程的课程号。2.写出上面第39题的SQL查询语句形式。3.试写出下列插入操作的SQL语句：把SC表中每门课程的平均成绩插到另一个已存在的表SC_C（CNO，CNAME，AVG_GRADE）中。4.试写出下列删除操作的SQL语句：从SC表中把Wu老师的女学生选课元组删去。5.试用S

27、QL的断言来表达下列约束：规定女同学选修LIU老师的课程成绩都应该在70分以上。答案：1.解：CNO（SC）CNO（SEX=M（SSC）2.解： SELECT CNOFROM SCWHERE CNO NOT IN(SELECT CNO FROM S，SC WHERE S.SNO=SC.SNO AND SEX=M)；3.解：INSERT INTO SC_C（CNO，CNAME，AVG_GRADE）SELECT C.CNO，CNAME，AVG（GRADE）FROM SC，CWHERE SC.CNO=C.CNOGROUP BY C.CNO，CNAME；4.解：DELETE FROM SCWHERE

28、SNO IN (SELECT SNO FROM S WHERE SEX=F)AND CNO IN (SELECT CNO FROM C WHERE TEACHER=Wu)；5.解：CREATE ASSERTION ASSE8 CHECK(70 ALL(SELECT GRADE FROM S, SC，C WHERE S.SNO=SC.SNO AND SC.CNO=C.CNOAND SEX=M AND TEACHER=LIU)；第六组：设工厂里有一个记录职工每天日产量的关系模式：R（职工编号，日期，日产量，车间编号，车间主任）。如果规定：每个职工每天只有一个日产量；每个职工只能隶属于一个车间；每个

29、车间只有一个车间主任。试回答下列问题： 根据上述规定，写出模式R的基本FD和关键码； 说明R不是2NF的理由，并把R分解成2NF模式集； 进而再分解成3NF模式集，并说明理由。答案：解： 基本的FD有3个：（职工编号，日期） 日产量 职工编号 车间编号 车间编号 车间主任 R的关键码为（职工编号，日期）。 R中有两个这样的FD：（职工编号，日期）（车间编号，车间主任） 职工编号 （车间编号，车间主任）可见前一个FD是局部依赖，所以R不是2NF模式。 R应分解成R1（职工编号，车间编号，车间主任） R2（职工编号，日期，日产量） 此处，R1和R2都是2NF模式。 R2已是3NF模式。在R1中，存

30、在两个FD：职工编号 车间编号车间编号 车间主任因此，“职工编号 车间主任”是一个传递依赖，R1不是3NF模式。 R1应分解成R11（职工编号，车间编号） R12（车间编号，车间主任）这样，= R11，R12，R2 是一个3NF模式集。第七组：某学员为公安局的车辆信息管理系统设计了数据库的ER图，对车辆、车主、驾驶员、制造商、保险公司、警察、车辆违章和车辆事故等信息进行了管理，其ER图如下所示。1NNPMMNN制造商车辆保险公司车主驾驶员警察交通管理大队被盗拥有管理保险2事故制造违章保险111M11NNNNP该ER图有7个实体类型，其结构如下：制造商（制造商编号，名称，地址）交通管理大队（大队

31、编号，地址，区域，电话）警察（警号，姓名，性别，出生年月）车主（身份证号，姓名，地址，电话）车辆（车辆牌号，型号，发动机号，座位数，登记日期）驾驶员（驾驶证号，姓名，性别，地址，准驾车型，发证日期）保险公司（保险公司编号，名称，地址，电话）该ER图有8个联系类型，其中5个是1:N联系，1个是M:N联系，2个是M:N:P联系。其联系的属性如下：违章（违章编号，时间，地点，违章行为，处理结果）事故（事故编号，时间，地点，事故简要，处理结果）被盗（被盗编号，被盗时间，被盗地点，找回时间，找回地点）试将上述ER图转换成关系模型： 写出关系模式集，并用下划线和波浪线标识出每个模式的主键和外键。 在上述数

32、据库中，写出主键和外键的总数这两个数字。答案：解： 根据ER图和转换规则，7个实体类型转换成7个关系模式，1个M:N联系转换成1个关系模式，2个M:N:P联系转换成2个关系模式。因此，该ER图可转换成10个关系模式，如下：制造商（制造商编号，名称，地址）交通管理大队（大队编号，地址，区域，电话）警察（警号，姓名，性别，出生年月，大队编号）车主（身份证号，姓名，地址，电话）车辆（车辆牌号，型号，发动机号，座位数，登记号，制造商编号，保险公司编号，车主身份证号）驾驶员（驾驶证号，姓名，性别，地址，准驾车型，发证日期，保险公司编号）保险公司（保险公司编号，名称，地址，电话）违章（违章编号，时间，地点

33、，违章行为，处理结果，车辆牌号，驾驶证号，警号）事故（事故编号，时间，地点，事故简要，处理结果，车辆牌号，驾驶证号，警号）被盗（被盗编号，被盗时间，被盗地点，找回时间，找回地点，车主身份证号，车辆牌号） 在上述数据库中，主键的总数为10个，外键的总数为13个。第八组：有一个学生课程数据库，数据库中包括三个表：学生表由学号、姓名、性别、年龄、所在系五个属性组成， 可记为：学生表(学号，姓名，性别，年龄，所在系) 学号为关键字。课程表由课程号、课程名、先修课号、学分四个属性组成， 可记为：课程表(课程号，课程名，先修课号，学分) 课程表为关键字。学生选课表由学号、课程号、成绩三个属性组成， 可记为

34、： 成绩表(学号，课程号，成绩) (学号、课程号)为关键字。编写用SQL语言实现下列功能的sql语句代码：1、建立一个学生表，其中学号属性不能为空，并且其值是唯一的。2、查询全体学生的详细记录。3、查询年龄在20至23岁之间的学生的姓名、系别、和年龄。4、计算1号课程的学生平均成绩。5、将计算机科学系全体学生的成绩置零。答案：1、建立一个学生表。CREATE TABLE 学生表 (学号 CHAR(5) NOT NULL UNIQUE, 姓名CHAR(20), 性别 CHAR(2), 年龄 INT, 所在系 CHAR(15)2、查询全体学生的详细记录SELECT * FROM 学生表3、查询年龄

35、在20至23岁之间的学生的姓名、系别、和年龄SELECT 姓名, 所在系, 年龄FROM 学生表WHERE 年龄 BETWEEN 20 AND 234、计算1号课程的学生平均成绩SELECT AVG(成绩) FROM 成绩表WHERE 课程号=15、将计算机科学系全体学生的成绩置零UPDATE 成绩表 SET 成绩=0 WHERE 学号 in( SELECT 学号 FROM 学生表 WHERE Sdept =计算机科学系)第九组：现有关系数据库如下： 数据库名：学生成绩数据库学生表(班级编号，学号，姓名，性别，民族，身份证号，出生日期) 课程表(课程号，课程名) 成绩表(ID,学号，课号，分数

36、) 用SQL语言实现下列功能的sql语句代码：1在学生成绩数据库的学生表中查询年龄为20岁或22岁的学生。2在学生成绩数据库中查询每个学生的班级编号、学号、姓名、平均分，结果按平均分降序排列，均分相同者按班级排列。3编写一个自定义函数，根据学生表中的出生日期列，计算年龄。4创建一个视图教学成绩表视图显示学生的学号、姓名、课程名、分数。5编写一个存储过程，输入学号，从教学成绩表视图显示该学生的姓名、课程名、分数。6把学生表、成绩表通过学号建立约束关系。7把学生表、成绩表通过学号建立约束关系。8在学生表中插入记录：班级编号 学号 姓名 性别 民族 身份证号 出生日期050201 050201001

37、 王莉欣 女 汉 53010219790625224 1979-06-25050202 050202001 张晶 男 NULL 01020319801224121 NULL把张晶的民族改为“汉”、出生日期改为“1980-12-24”答案1在学生成绩数据库的学生表中查询年龄为20岁或22岁的学生use 教学成绩管理数据库select 姓名, 性别, 年龄=datediff(year,出生日期,getdate() from 学生表where (datediff(year,出生日期,getdate()=20) or (datediff(year,出生日期,getdate()=22)2在学生成绩数据库

38、中查询每个学生的班级编号、学号、姓名、平均分，结果按平均分降序排列，均分相同者按班级排列use 教学成绩管理数据库select 班级编号,a.学号,a.姓名, avg(分数) 平均分from 学生表 as a join成绩表 as b on a.学号 = b.学号group by 班级编号, a.学号,a.姓名order by avg(分数) desc, 班级编号 asc3编写一个自定义函数，根据学生表中的出生日期列，计算年龄。CREATE FUNCTION dbo.计算年龄(vardate datetime,Curdate datetime) RETURNS tinyint AS BEGIN

39、 return datediff(yyyy, vardate, Curdate)END4创建一个视图“教学成绩表视图” 显示学生的学号、姓名、课程名、分数。CREATE VIEW 教学成绩表视图ASSELECT 学号, 姓名, 课程名, 分数FROM 学生表, 成绩表WHERE 学生表.学号=成绩表.学号5编写一个存储过程，输入学号，从“教学成绩表视图” 显示该学生的姓名、课程名、分数。CREATE PROCEDURE 成绩1 xh char (6)asselect 姓名,课程名称,分数 from 教学成绩表视图where 学号=xh6把学生表、成绩表通过“学号”建立约束关系。alter ta

40、ble 学生表 add constraint 约束1 foreign key (学号) references 成绩表 (学号)7创建一个触发器，当修改学生表中的姓名时，显示“学生姓名已被修改”。Create Trigger 触发器7 On 学生表For updataAsPrint 学生姓名已被修改Go8insert 学生表 values(050201,050201001,王莉欣,女,汉,53010219790625224,1979-06-25)insert 学生表 values (050201,050201001, 张晶, 男, null, null, null)update 学生表 set 民族=汉, 身份证号=01