下载文档原格式
设有一个学生-课程数据库，包括学生关系Student 、课程关系Course 、选修关系SC ，如下图所示：(18分)根据以上所定义的三张基表写出相应的SQL 语句： （1）统计有学生选修的课程门数。
（2）查询每个学生的姓名及其选修课程的课程名。
（3）求选修2号课程的学生的平均年龄。
（4）求学分为4的每门课程的学生平均成绩。
（5）查询年龄在20以下的学生的姓名、所在系、年龄。
（6）统计每门课程的学生选修人数，超过2人的课程才统计。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
（7）检索姓名以王打头的所有学生的姓名和年龄。
（8）创建一个视图，包括每个系的学生所选的每门课程的平均成绩。
（9）把查询学生表的权限授予用户U1设有一个学生-课程数据库，包括学生关系Student 、课程关系Course 、选修关系SC ，如下图所示：(18分)SC根据以上所定义的三张基表写出相应的SQL语句：（1）统计有学生选修的课程门数。
（2）查询每个学生的姓名及其选修课程的课程名。
答：SELECT 姓名，课程名FROM Student,Course,Sc Where Student.学号=SC.学号AND Course.课程号=SC.课程号.（3）求选修2号课程的学生的平均年龄。
答：SELECT A VG(年龄) FROM Student,SCWHERE Student.学生号=SC.学生号and 课程号=’2’（4）求学分为4的每门课程的学生平均成绩。
答：SELECT Course.课程号,AVG(成绩) FROM Course, SCWHERE Course.课程号=SC.课程号and 学分=4GROUP BY Course.课程号（5）查询年龄在20以下的学生的姓名、所在系、年龄。
答：SELECT 姓名、系别、年龄FROM StudentWHERE 年龄课<20（6）统计每门课程的学生选修人数，超过2人的课程才统计。
练习二SQL Server数据库的管理1、在企业管理器中利用图形界面工具新建一个数据库studb1，设置其参数如下：数据文件的初始大小为2M，最大为6M，增长方式为每次1M；数据文件的名称和路径使用默认值日志文件的初始大小为1M，最大大小无限制，增长方式为递增5%；日志文件的名称和路径使用默认值2、在企业管理器中利用图形界面工具新建一个数据库studb2，设置其参数如下：数据文件的逻辑名为studb2_Data1；物理名称为studb2_Data1.Mdf，路径位于D:\sdata；数据文件的初始大小为1M，最大为8M，增长方式为每次10%；日志文件的逻辑名为studb2_Log1；物理名称为studb2_Log1.ldf，路径位于D:\sdata；日志文件的初始大小为2M且不自动增长；3、studb2数据库创建完成后，对数据库studb2进行如下操作创建新的文件组，名称为SEC创建新的数据文件，逻辑名称为studb2_Data2,物理文件名为studb2_Data2.ndf; 路径位于D:\sdata; 其他参数值采取默认值，并将其加入文件组SEC创建新的日志文件，逻辑名称为studb2_Log2，其余参数均采用默认值4、对数据库studb2，执行如下操作删除第3题中创建的文件组SEC，观察系统的反应。
删除第3题中创建的次数据文件，然后再删除文件组SEC，观察系统的反应。
删除第三题中创建的次日志文件删除主数据文件、主日志文件观察系统的反应删除主文件组，观察系统的反应。
5、在查询分析器中，编写SQL语句创建数据库Studentsdb1，不指定参数，创建完成后去企业管理器查看系统为其制定的各项参数。
6、利用图形界面工具修改数据库Studentsdb1的上述参数7、在查询分析器中，编写SQL语句创建数据库Studentsdb2，参数如下：a)数据文件逻辑名为stu2b)物理文件为stu2.mdf;存放在d盘目录db8、在查询分析器中，编写SQL语句创建数据库Studentsdb3，参数如下：a)数据文件逻辑名为stu3b)物理文件为stu3.mdf; 存放在d盘目录dbc)初始大小为2M，递增方式为1M，最大10M9、在查询分析器中，编写SQL语句创建数据库Studentsdb4，参数如下：a)数据文件逻辑名为stu4，物理文件为stu4.mdf；存放在d盘目录db；初始大小为2000KB，递增方式为10%，最大9000KB;b)日志文件逻辑名为stu4log；物理文件为stu4.ldf；存放在d盘目录db；初始大小1M，递增方式为5%，大小不限制10、在查询分析器中，编写SQL语句创建数据库Studentsdb5，参数如下：a)数据文件逻辑名为stu5，物理文件为stu5.mdf；存放在d盘目录db；初始大小为4000KB，其大小不可自动增长11、在查询分析器，编写SQL语句创建数据库Studentsdb6，要求如下：a)主文件组包括两个数据文件和两个日志文件b)数据文件1的逻辑文件名为stu61;物理文件存放于d:\db目录下，名字为stu61.mdf;初始大小为1MB;最大为5MB；递增方式是每次增加20%c)数据文件2的逻辑文件名为stu62;物理文件存放于D盘的DB目录下，名字为stu62.ndf;初始大小为3MB;且不自动增长d)日志文件的逻辑文件名分别为stu6log1,stu6log2,，名字分别为stu6log1.ldf和stu6log2.ldf; 存放于d:\db目录下;初始大小均为1MB;最大均为5MB；递增方式都是每次增加256KB12、利用企业管理器删除数据库Studentsdb1，Studentsdb2，Studentsdb313、编写SQL语句删除数据库Studentsdb4，Studentsdb5，Studentsdb614、如果希望在上面建立的每个数据库中都包含表test，如何快速建立这些表？。
--以默认方式创建stu001数据库create database stu001--打开stu001数据库use stu001--创建学生表XScreate table xs(学号char(7)not null primary key,姓名char(8)not null,性别bit not null default 1,出生日期smalldatetime not null,班级char(5)default'41021',总学分tinyint default 0,备注text null)--创建课程表KCcreate table kc(课程号char(3)not null primary key,课程名char(20)not null,开课学期tinyint not null default 1 check(开课学期>=1 and开课学期<9 ),学分tinyint not null default 0,学时tinyint not null)--创建学生与课程表xs_kccreate table xs_kc(学号char(7)not null references xs(学号),课程号char(3)not null references kc(课程号),成绩tinyint null default 0 check(成绩>=0 and成绩<=100),学分tinyint null default 0primary key(学号,课程号))--修改学生情况表（XS）的“出生日期”字段，使其数据类型改为datetime，且允许为空use stu001alter table xsalter column出生日期datetime null--删除学生与课程表（XS_KC）中的“学分”字段alter table xs_kc drop constraint DF__xs_kc__学分__0AD2A005alter table XS_kc drop column学分--修改学生情况表（XS），删除“班级”字段，增加一“专业”字段（varchar(20), 默认值为“计算机”）alter table xs drop constraint DF__xs__班级__7E6CC920alter table xs drop column班级alter table xs add专业varchar(20)default'计算机'--向数据库STUxxxx的XS表、KC表和XS_KC表中插入或修改成数据insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注)values('4102101','王林',1,'1983-1-23','计算机',40,NULL)insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注)values('4102102','吴荣华',0,'1984-3-28','计算机',44,NULL)insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注)values('4202103','张强',1,'1981-11-19','电子',null,NULL)insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注)values('4202110','王向荣',1,'1980-12-9','电子',50,NULL)insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注)values('4202221','李丽',0,'1982-7-30','电子',null,NULL)insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注)values('4302101','刘明',1,'1982-10-18','自控',38,NULL)insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注)values('4402130','叶凡',1,'1983-11-18','数学',46,'转专业学习')insert into kc(课程号,课程名,开课学期,学时,学分)values('101','计算机基础',1,48,3)insert into kc(课程号,课程名,开课学期,学时,学分)values('102','C语言',2,80,5) insert into kc(课程号,课程名,开课学期,学时,学分)values('103','数据库',4,64,4) insert into kc(课程号,课程名,开课学期,学时,学分)values('201','离散数学',6,64,4)insert into kc(课程号,课程名,开课学期,学时,学分)values('202','计算机网络',7,64,4)insert into kc(课程号,课程名,开课学期,学时,学分)values('301','软件工程',6,64,4)insert into xs_kc(学号,课程号,成绩)values('4102101','101',80)insert into xs_kc(学号,课程号,成绩)values('4102101','102',89)insert into xs_kc(学号,课程号,成绩)values('4102101','103',78)insert into xs_kc(学号,课程号,成绩)values('4102102','101',57)insert into xs_kc(学号,课程号,成绩)values('4102102','102',67)insert into xs_kc(学号,课程号,成绩)values('4202103','103',90)insert into xs_kc(学号,课程号,成绩)values('4202110','101',85)insert into xs_kc(学号,课程号,成绩)values('4202110','102',91)insert into xs_kc(学号,课程号,成绩)values('4202221','101',69)insert into xs_kc(学号,课程号,成绩)values('4402130','101',78)insert into xs_kc(学号,课程号,成绩)values('4402130','103',74)--（一）简单查询--1.查询XS表中各个同学的所有信息。
SQL 上机作业2报告管理学院电子商务文(本)0902班邬涵卿0902110233SQL 上机作业21.在Student数据库中创建下列数据表：学生情况表233：课程情况表233：学生选课表233：2.按下列要求修改数据表1）修改“学生情况表099”的结构，为其添加“寝室号”、“照片”字段；2）将“性别”移到“身份证号”前面；将“省份”移到“班级”后面；3）修改“课程情况表099”，为其添加“职称”、“上课地点”字段；4）修改“学生选课表099”，添加“成绩等级”字段；5）将“专业”的类型修改为varchar，宽度修改为30，不允许为空；6）将“学生情况表099”的学号修改为不为空、且为主键；7）将“课程情况表099”中的课程号修改为不为空、且为主键；8）将“姓名、身份证号、课程名”修改为不为空。
9）生成3个数据表的脚本文件。
学生情况表233：学生选课表233：课程情况表233：3.用INSERT命令语句，为上面三个数据表录入至少10条记录（输入的学号是从自己学号开始的10个，姓名为自己和后面同学的真实姓名；课程号是自己学号的后三位开始，依次增加；专业、班级为真实信息；课程为自己学过的10门课程；其他数据自定）；并将插入数据的命令保存。
学生情况表233：学生选课表233：课程情况表233：4.按下列要求进行查询1）查询自己第1学期高等数学的成绩，只显示学生的学号、姓名、班级、课程名、选课学期、成绩等字段；2）查询与自己同班同学的第1学期大学英语的成绩，只显示学生的学号、姓名、班级、课程名、选课学期、成绩等字段；3）查询第2学期各个院系英语成绩的平均分、最高分、最低分；*没有第二学期,所以只算第一学期，并且测量系英语只有一人，故只算管理学院*4）查询自己各个学期的选课情况，按“选课学期”升序、“成绩”降序显示学号、姓名、课程名、学时、学分、选课学期、成绩等信息。
5）查询高等数学成绩高于自己的其他同学的相关信息。
班级：学号：姓名：实验3 SQL数据操纵1.实验目的掌握INSERT、UPDA TE、DELETE命令实现对表数据添加、修改与删除等更新操作。
2.背景知识（1）添加操作。
SQL语言中数据添加语句INSERT通常有两种形式，一种是一次添加一条元组，另一种是一次添加一个子查询结果，即一次添加多条元组。
1)添加单条元组。
在SQL语句中添加单条元组的语句格式如下：INSERT INTO <表名>[(<属性列1>[,<属性2>]…)]V ALUES(<常量1>[,<常量2>])功能是将指定的元组添加到指定的关系中，其中属性列的顺序要与常量值的顺序一一对应，常量i的值赋给属性列i（i=1,2,…）。
关系中在INTO子句中没有出现的属性列，新值在这些列上取空值。
注意，若表定义中不许为空的列不能取空值，否则会出错。
有时可以省略属性列表，但常量的列表顺序要求必须与指定关系的实际属性列顺序一致，且新添加的记录必须在每个属性列上均有值。
2）添加多个元组。
在SQL语句中，子查询结果可以一次添加到指定的关系中，添加子查询结果的语句格式如下：INSERT INTO <表名>[(<属性列1>[,<属性2>]…)] <子查询>说明：子查询结果集合中的列数、列序和数据类型必须与<基本表名>所指基本表中相应各项匹配或兼容。
（2）修改操作。
数据库中数据发生变化时，需要对关系进行修改，在SQL语句中修改操作的一般格式为：UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]… [WHERE <条件>]功能是修改指定关系中满足WHERE子句条件的元组。
其中，SET子句给出指定列的修改方式及修改后取值。
若省略WHERE子句则说明要修改关系中所有的元组。
大工21秋《SQL数据库系统及应用》在线作业2试卷总分:100 得分:1001.在SQL的select语句中，如果要过滤结果中的重复行，可以在字段列表前面加上（）。
<-A.->group<-B.->order<-C.->top<-D.->distinct【本题正确参考选择】:D2.在select语句中如果要对得到的结果中某个字段按降序处理,则使用()参数。
<-A.->asc<-B.->desc<-C.->between<-D.->and【本题正确参考选择】:B3.查询学校员工工资信息时,结果按工资降序排列,正确的是()。
<-A.->ORDERBY工资<-B.->ORDERBY工资desc<-C.->ORDERBY工资asc<-D.->ORDERBY工资and【本题正确参考选择】:B4.当使用SQL语句进行分组检索时,为了按照一定的条件对分组后的数据进行筛选,应当()。
<-A.->在join后面使用betweento子句<-B.->在groupby后面使用having子句<-C.->在where后面使用orderby子句<-D.->在join后面使用orderby子句【本题正确参考选择】:B5.在SQL查询语句中,HAVING子句的作用是()。
<-A.->指出排序的范围<-B.->指出查询结果的最大值<-C.->指出分组查询的条件<-D.->指出创建数据表的字段【本题正确参考选择】:C6.以下关于HAVING子句描述错误的是（）。
<-A.->HAVING子句允许与GROUPBY子句同时使用<-B.->使用HAVING子句的同时不能使用WHERE子句<-C.->使用HAVING子句的同时可以使用WHERE子句<-D.->使用HAVING子句的作用是限定分组的条件。
大连理工大学20年秋季学期《SQL数据库系统及应用》
在线作业2附参考答案
试卷总分:100 得分:100
一、单选题 (共 10 道试题,共 50 分)
1.在SQL的select语句中,如果要过滤结果中的重复行,可以在字段列表前面加上( )。
A.group
B.order
C.top
D.distinct
答案:D
2.在select语句中如果要对得到的结果中某个字段按降序处理,则使用( )参数。
A.asc
B.desc
C.between
D.and
答案:B
更多加微boge30619
3.查询学校员工工资信息时,结果按工资降序排列,正确的
是( )。
A.ORDER BY 工资
B.ORDER BY 工资 desc
C.ORDER BY 工资 asc
D.ORDER BY 工资 and
答案:B
4.当使用SQL语句进行分组检索时,为了按照一定的条件对分组后的数据进行筛选,应当( )。
A.在join后面使用between to子句
B.在group by后面使用having子句
C.在where后面使用order by子句
D.在join后面使用order by子句
答案:B
5.在SQL查询语句中,HAVING子句的作用是( )。
A.指出排序的范围
B.指出查询结果的最大值
C.指出分组查询的条件
D.指出创建数据表的字段
答案:C。
SQL数据库上机练习2在D盘根目录下用自己的名字加“练习2”建立一个目录一．（6分）在SQL Server Management Studio中建立满足如下要求的数据库：1．数据库名字为：TwoDB；2．此数据库包含两个数据文件，其中主数据文件的逻辑文件名为：Two_Data1,物理文件名为Two_data1.mdf，初始大小为2MB，存放在新建目录下，自动增长，每次增长1MB，最多增加到4MB。
辅助数据文件的逻辑文件名为：Two_data2，物理文件名为Two_data2.ndf，初始大小为3MB，同主数据文件存放在相同的位置，不自动增长。
3．日志文件包含一个，日志文件的逻辑文件名为：Two_log,物理文件名为Two_log.ldf，初始大小为3MB，同主数据文件存放在相同的位置，自动增长，每次增长30%，最多增加到5MB。
二．（10分）为描述书店对图书的进书情况，创建了三张表：图书表，书店表和书店进书表。
图书表用于表示图书的基本信息，书店表用于描述书店的基本信息，书店进书表描述了书店的图书进书情况。
用语句在TwoDB数据库中创建这三张表，请将语句以文件形式保存下来，文件名为“姓名.sql”，存放在新建的目录下。
可以将后续所有的SQL语句均保存在此文件中，在文件中要注明题号）。
注:括号中为表名和列名的拼音表示，建表时用拼音表示表名和列名即可。
三张表的结构如下：1．图书表（TSB）（记录出版社所印的每种图书的情况，一个图书编号对应一种图书，而不是一本图书）图书ISBN（ISBN）：普通编码定长字符型，4位长，主码书名（SM）：普通编码可变长字符型，30位长，非空类别（LB）：普通编码定长字符型，10位长，允许空，取值范围为{计算机、通讯、数学、外语}印刷数量(YSSL)，小整型，允许空，取值：大于等于1000单价（DJ）：定点小数，小数点后保留2位，小数点前4位，允许空出版日期（CBRQ）：小日期时间型，允许空2．书店（SDB）书店编号（SDBH）：普通编码定长字符型，4位长，主码书店名称（SDDM）：普通编码定长字符型，20位长，非空地址（DZh）：普通编码可变长字符型，30位长，允许空电话（DH):普通编码定长字符型，12位长,取值形式为：“(3位数字)-6位数字”3．书店进书表（JSB）图书ISBN（ISBN）：普通编码定长字符型，4位长，非空书店编号（SDBH）：普通编码定长字符型，4位长，非空进书日期（JSRQ）：小日期时间型，非空,默认值为插入记录时的当前时间进书数量（JSSL）：小整型，允许空，取值：大于等于10其中：（图书ISBN，书店编号，进书日期）为主码图书ISBN为引用图书表的外码，书店编号为引用书店表的外码。
SQL上机作业（2）
一.针对SQL上机作业（1）第一题定义的三个表（book，card，borrow），用SQL语句实现如下查询：（注：藏书量=库存量+借出数）
1．求藏书种数、总册数、最高价、最低价。
select count(category)as category,sum(total)as total,
max(price) as maxpric,min(price)as minpric
from book
group by book.category;
select count(*),sum(total),max(price),min(price)
from book;
2．列出藏书在十本以上的书（书名、作者、出版社、年份）。
select title,author,press,year
from book
where total>10;
3．哪些出版社的总藏书超过100种。
select press ,count(distinct category)
from book
group by press
having count(distinct category)>100;
4．目前已借出多少册书？
select sum(total)-sum(stock)
from book;
5．年份最久远的书。
select title
from book
group by title
having min(year)<=all(select min(year)
from book
group by title);
6．“数据库系统原理教程，王珊编，清华大学出版社，1998年出版”还有几本？
select title ,stock
from book
where title='数据库系统原理教程'and press='清华大学出版社'
and author='王珊' and year='1998';
7．哪一年的图书最多？
select year
from book
group by year
having sum(total)>=all(select sum(total)
from book
group by total);
8．平均每本借书证的借书册数。
select count(*)/count(cno)
from borrow
where return_date is not null;
9．今年未借过书的借书证。
Select cno
From borrow
Where year(borrow_date)<'2009';
10．哪个系的同学平均借书最多？
create view CB
as
select o,department
from card,borrow
where o=o;
select department
from CB
group by department
having count(department)>all(select department
from CB
group by department);
11．最近两年都未被借过的书。
select title
from book
where bno not in(select bno
from borrow
where year(borrow_date)between'2007' and '2009');
12．今年那种书出借最多？
select category ,(sum(total)-sum(stock))
from book
group by category
having (sum(total)-sum(stock))>all(select (sum(total)-sum(stock))
from book
group by category);
二、针对SQL上机作业（1）第二题定义的四个表（S，P，J，SPJ），用SQL语句实现如下查询：
1．求供应工程J1零件的供应商号码SNO；
from SPJ
where jno='j1';
2．求供应工程J1零件P1的供应商号码SNO；
select distinct sno
from SPJ
where jno='j1'and pno='p1';
3．求供应工程J1零件为红色的供应商号码SNO；
方法一：复合条件连接查询
select sno
from SPJ,P
where SPJ.pno=P.pno and SPJ.jno='j1' and P.color='红'; 方法二：嵌套查询
select sno
from SPJ
where jno='j1'and
pno in
(select pno
from P
where color='红');
4．求没有使用天津供应商生产的红色零件的工程号JNO；select distinct jno
from SPJ
where jno not in
(select jno
from SPJ,P,S
where S.sno=SPJ.sno and
P.pno=SPJ.pno and
S.city='天津' and
P.color='红');
5．求至少用了供应商S1所供应的全部零件的工程号JNO。
select distinct jno
from SPJ fst
where not exists
(select *
from SPJ snd
where snd.sno='s1' and
not exists
(select *
from SPJ trd
where trd.jno=fst.jno and
trd.pno=snd.pno));
6. 找出所有供应商的姓名和所在城市。
from S;
7. 找出所有零件的名称、颜色、重量。
select pname,color,weight
from P;
8. 找出使用供应商S1所供应零件的工程号码。
select jno
from SPJ
where sno='s1';
9. 找出工程项目J2使用的各种零件的名称及其数量。
select ,SPJ.qty
from P,SPJ
where P.pno=SPJ.pno and SPJ,jno=‘j2’;
10. 找出上海厂商供应的所有零件号码。
select distinct pno
from SPJ
where sno in (select sno
from S
where city=‘上海’);
11. 找出使用上海产的零件的工程名称。
select jname
from S,SPJ,J
where SPJ.sno=S.sno and SPJ.jno=J.jno and S.city='上海'; 方法二
selcet jname
from J
where jno in (select jno
from SPJ,S
where SPJ.sno=S.sno and S.city=’上海’);
12. 找出没有使用天津产的零件的工程号码。
select jno
from J
where jno not in
(select jno
from SPJ
where SPJ.jno=J.jno and
sno in (select sno
from S
where city='天津'));
13. 把全部红色零件的颜色改为蓝色。
update p
set color='蓝'
where color='红';
14. 由S5供给J4的零件P6改为由S3供应，请作必要的修改。
update spj
set sno='s3'
where sno='s5' and pno='p6' and jno='j4';
15. 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录。
delete
from s
where sno='s2';
delete
from spj
where sno='s2';
16. 请将（S2，J6，P4，200）插入供应情况关系。
insert
into spj(sno,jno,pno,qty)
values('s2','j6','p4',200);。
