《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 sy0begin raiserror (余额不足,16,1)rollbackendENDIF BZ=1BEGINUPDATE 储户SET 存款额=存款额+MONEYWHERE 账号=ZHEND第二组：学校图书馆借书信息管理系统建立三个表：学生信息表：student字段名称数据类型说明stuIDchar(10)学生编号，主键stuNameVarchar(10)学生名称majorVarchar(50)专业图书表：book字段名称数据类
11、型说明BIDchar(10)图书编号，主键titlechar(50)书名authorchar(20)作者借书信息表：borrow字段名称数据类型说明borrowIDchar(10)借书编号，主键stuIDchar(10)学生编号，外键BIDchar(10)图书编号，外键T_timedatetime借书日期B_timedatetime还书日期请编写SQL语句完成以下的功能：1) 查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期；参考查询结果如下图所示：2) 查询所有借过图书的学生编号、学生名称、专业；参考查询结果
12、如下图所示：3) 查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；参考查询结果如下图所示：4) 查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所示：答案：USE masterGO/*$建库$*/-检验数据库是否存在，如果为真，删除此数据库-IF exists(SELECT * FROM sysdatabases WHERE name=BOOK) DROP DATABASE BOOKGOCREATE DATABASE BOOKGO-建数据表-USE BOOKGOCREATE TABLE student -学生信息表( stuID CHAR(10) p
13、rimary key, -学生编号 stuName CHAR(10) NOT NULL , -学生名称 major CHAR(50) NOT NULL -专业)GOCREATE TABLE book -图书表( BID CHAR(10) primary key, -图书编号 title CHAR(50) NOT NULL, -书名 author CHAR(20) NOT NULL, -作者)GOCREATE TABLE borrow -借书表( borrowID CHAR(10) primary key, -借书编号stuID CHAR(10) foreign key(stuID) references student(stuID), -学生编号BID CHAR(10) foreign key(BID) references book(BID),-图书编号 T_time datetime NOT NULL, -借出日期 B_time datetime -归还日期)GO-学生信息表中插入数据-INSERT INTO student(stuID,stuName,major)VALUES(
