下载文档原格式
SQL练习题及答案1（合集五篇）第一篇：SQL练习题及答案1SQL练习题：商品销售数据库商品销售数据库Article(商品号 char(4)，商品名char(16)，单价 Numeric(8,2)，库存量 int)Customer(顾客号char(4)，顾客名 char(8)，性别 char(2)，年龄 int)OrderItem(顾客号 char(4)，商品号 char(4)，数量 int, 日期date)1.用SQL建立三个表，须指出该表的实体完整性和参照完整性，对性别和年龄指出用户自定义的约束条件。
（性别分成男女，年龄从10到100）。
顾客表的数据用插入语句输入数据，其它两表可用任意方式输入数据。
create table OrderItem(顾客号char(4),商品号char(4),日期datetime,数量 smallint，primary key(顾客号,商品号,日期)，foreign key(商品号)references Article(商品号), foreign key(顾客号)references Custommer(顾客号));2.检索定购商品号为…0001‟的顾客号和顾客名。
select distinct 顾客号,顾客名from OrderItem where 商品号='0001'3.检索定购商品号为…0001‟或…0002‟的顾客号。
select distinct 顾客号 from OrderItem where 商品号='0001' or 商品号='0002';4.检索至少定购商品号为…0001‟和…0002‟的顾客号。
select 顾客号 from OrderItem where 商品号='0001' and 顾客号 in(select 顾客号 from OrderItem where 商品号='0002');5.检索至少定购商品号为…0001‟和…0002‟的顾客号。
一、设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（ Score）以及教师信息表（ Teacher）。
四个表的结构分别如表 1-1 的表（一） ~表（四）所示，数据如表1-2 的表（一） ~表（四）所示。
用 SQL语句创建四个表并完成相关题目。
表 1-1 数据库的表结构表（一） Student属性名数据类可否为含义型空Sno Char(3) 否学号（主键）Sname Char(8) 否学生姓名Ssex Char(2) 否学生性别Sbirthday datetime 可学生出生年月Class Char(5) 可学生所在班级表（二） Course属性名数据类型可否为含义空Cno Char(5) 否课程号（主键）Cname Varchar(10) 否课程名称Tno Char(3) 否教师编号（外键）表（三） Score属性数据类型可否为含义名空Sno Char(3) 否学号（外键）Cno Char(5) 否课程号（外键）Degree Decimal(4,1) 可成绩主码： Sno+ Cno表（四） Teacher属性名数据类型可否为含义空Tno Char(3) 否教师编号（主键）Tname Char(4) 否教师姓名Tsex Char(2) 否教师性别Tbirthday datetime 可教师出生年月Prof Char(6) 可职称Depart Varchar(10) 否教师所在部门表 1-2 数据库中的数据表（一） StudentSno Sname Ssex Sbirthday class108 曾华男1977-09- 9503301105 匡明男1975-10- 9503102107 王丽女1976-01- 9503323101 李军男1976-02- 9503320109 王芳女1975-02- 9503110103 陆君男1974-06- 9503103表（二） CourseCno Cname Tno3-105 计算机导论8253-245 操作系统8046-166 数字电路8569-888 高等数学831表（三） ScoreSno Cno Degree103 3-245 86105 3-245 75109 3-245 68103 3-105 92105 3-105 88109 3-105 76101 3-105 64107 3-105 91108 3-105 78101 6-166 85107 6-166 79108 6-166 81表（四） TeacherTno Tname Tsex Tbirthday Prof Depart804 李诚男1958-12- 副教计算机系02 授856 张旭男1969-03- 讲师电子工程12 系825 王萍女1972-05- 助教计算机系05831 刘冰女1977-08- 助教电子工程14 系-- 1、查询 Student 表中的所有记录的Sname、Ssex和 Class列。
实验训练5：存储过程与函数的构建与使用一、存储过程与函数的概念存储过程和函数都是数据库中的可执行代码，可以被多次调用和重复使用。
存储过程是一组预定义的SQL语句集合，可以在数据库中定义和存储。
而函数是一个独立的代码块，它接收输入参数并返回一个值。
二、存储过程的构建与使用1. 创建存储过程在MySQL中，创建存储过程需要使用CREATE PROCEDURE语句。
例如：CREATE PROCEDURE myproc()BEGINSELECT * FROM mytable;END;这个例子创建了一个名为myproc的存储过程，它会查询mytable表中的所有数据。
2. 调用存储过程使用CALL语句可以调用已经创建好的存储过程。
例如：CALL myproc();这个语句会执行myproc存储过程中定义的SQL语句。
3. 存储过程参数我们可以给存储过程添加参数来使其更加灵活。
例如：CREATE PROCEDURE myproc(IN p1 INT, IN p2 VARCHAR(50)) BEGINSELECT * FROM mytable WHERE column1 = p1 AND column2 = p2;END;这个例子创建了一个带有两个输入参数p1和p2的存储过程，它会查询mytable表中column1等于p1并且column2等于p2的数据。
4. 存储过程变量除了参数之外，存储过程还可以使用变量来存储中间结果。
例如：CREATE PROCEDURE myproc(IN p1 INT)BEGINDECLARE v1 INT;SET v1 = p1 * 2;SELECT * FROM mytable WHERE column1 = v1;END;这个例子创建了一个带有一个输入参数p1和一个变量v1的存储过程，它会将p1乘以2并将结果存储在v1变量中，然后查询mytable表中column1等于v1的数据。
实验三用T-SQL编辑修改数据库数据一、实验目的熟练掌握在查询分析器中执行INSERT，UPDATE，DELETE语句来实现数据的添加、修改、删除等操作。
二、操作示例1．使用INSERT语句向表中添加数据。
1）向mydatabase数据库的kcxxb表添加下列新记录（插入所有列）：use mydatabasegoinsert kcxxb values (‘046110’，‘多媒体应用技术’，70，4)select * from kcxxb --显示结果2）向xsxxb表添加记录（插入部分列）：use mydatabasegoinsert xsxxb (xh,xm,csrq,address)values('04651020','黄小烽','1983-7-6','40-203')select * from xsxxb --查询显示结果3）创建一个与xsxxb表结构一样的空表aa，并把xsxxb表中性别（xb）列为“女”的记录添加到aa表中。
并查看aa表中的信息。
use mydatabasegoselect * into aa from xsxxb where 1=2 --1=2是永远为假的条件。
insert aa select * from xsxxb where xb= '女'select * from aa --查询显示结果2．UPDATE语句修改数据1)将xscjb表中的成绩（grade）减少3分。
USE mydatabasegoUPDATE xscjb SET grade=grade-32)将kcxxb表中课程名(kcm)为“哲学”的课时(ks)改为54。
Use mydatabaseGoUpdate kcxxb set ks=72 where kcm= '哲学' 3．DELETE语句删除记录1）删除aa表中出生日期（csrq）在1985年以后的记录。
数据库实验总汇二、使用SQL Server 工具在管理数据库系统和运行SQL查询的两个主要工具是企业管理器和查询分析器三、实验目的1．熟悉数据库的交互式SQL工具。
2．熟悉通过SQL对数据库进行操作。
3．完成上机练习。
四、实验内容(打※为选做题)Part A1．使用CREATE语句创建数据库2. 使用CREATE语句创建基本表3．更改基本表的定义，增加列，修改列的数据类型。
4．创建表的索引，取消表的索引1、数据库的建立使用SQL 语句建立关系数据库schooldemo2、基本表的建立在数据库schooldemo下，使用SQL 语句建立关系数据库表：人员表PERSON（Pno，Pname, Page）,房间表ROOM（Rno,Rname,Rarea）,表PR(Pno,Rno,Date).其中，表PERSON ：Pno（CHAR（8），主码，非空，唯一），Pname（CHAR（20），非空），Page（INT）；表ROOM：Rno（CHAR（8），主码，非空，唯一），Rname（CHAR（20）），Rarea（FLOAT （10））；表PR：Pno（CHAR（8），非空，唯一），Rno（CHAR（8），非空，唯一），Date （DATETIME）；（三个表之间有联系）3、更改基本表的定义，增加列，删除列，修改列的数据类型。
更改表PERSON，增加属性Ptype（类型是CHAR ，长度为10），把表ROOM中的属性Rname的数据类型改为长度为40。
4、创建表的索引，取消表的索引为ROOM表创建按Rno降序排列的索引创建PERSON表按Pname升序排列的唯一性索引取消PERSON表Pname升序索引5、删除表ROOM实验过程(1.a)1、数据库的建立使用SQL 语句建立关系数据库schooldemocreate database schooldemoon(name=schooldemo,filename='e:\123\school_data.mdf',size=5mb,maxsize=15mb,filegrowth=10%)log on(name=schooldemo_log,filename='e:\123\schooldemo.ldf',size=5mb,maxsize=15mb,filegrowth=4mb)2、基本表的建立在数据库schooldemo下，使用SQL 语句建立关系数据库表：人员表PERSON（Pno，Pname, Page）,房间表ROOM（Rno,Rname,Rarea）, 表PR(Pno,Rno,Date).其中，表PERSON ：Pno（CHAR（8），主码，非空，唯一），Pname（CHAR（20），非空），Page（INT）；表ROOM：Rno（CHAR（8），主码，非空，唯一），Rname（CHAR（20）），Rarea（FLOAT（10））；表PR：Pno（CHAR（8），非空，唯一），Rno（CHAR（8），非空，唯一），Date（DATETIME）；（三个表之间有联系）create table person(pno char (8)primary key,pname char (20),page int);create table room(rno char (8) primary key,rname char(20),rarea float(10));create table pr(pno char (8),rno char (8),date datetime,primary key(pno,rno),foreign key(pno) references person(pno),foreign key(rno) references room(rno));3、更改基本表的定义，增加列，删除列，修改列的数据类型。
1 集美大学计算机工程学院实验报告 课程名称：数据库系统教程 班级： 实验成绩： 指导教师 姓名 实验项目名称： 学号： 上机实践日期： 实验项目编号：实验五 组号： 上机实践时间： 学时
一、目的 二、实验内容 (1) 根据以下三张图配合自已的成绩查看，完成集美大学学生成绩管理的数据库设计，包括E-R（用ERWIN或VISIO等CASE工具绘制）及数据库表设计（针对SQL SERVER），根据业务的估计频度及业务特点完成数据库物理设计，完成安全性设计（文字叙述即可）. (2) 按图书管理系统课件tushu.ppt完成图书馆图书管理系统的概念模型设计（用ERWIN或VISIO或POWERDESIGNER等绘制），完成数据库表设计。试进行物理设计。 (3) 针对图书管理系统的业务完成数据库的界面设计（提高）.
三、运行结果 (1)①设计图：
②sql数据库设计：
2
CREATE TABLE course ( ctpye tinyint NULL, cprieod char(18) NULL, ctime char(18) NULL, cname char(18) NULL, cno tinyint NOT NULL, tno char(18) NULL ) go ALTER TABLE course ADD PRIMARY KEY NONCLUSTERED (cno) go
CREATE TABLE ctype ( publicbase char(18) NULL, special char(18) NULL, specialbase char(18) NULL ) go
CREATE TABLE examsort ( computer char(18) NULL, speak char(18) NULL, unlook char(18) NULL, look char(18) NULL ) go
第1章数据库系统概述二．1．答：该学校的教学管理E-R模型有以下实体：系、教师、学生、项目、课程。
各实体属性如下：系（系编号，系名，系主任）教师（教师编号，教师姓名，职称）学生（学号，姓名，性别，班号）项目（项目编号，名称，负责人）课程（课程编号，课程名，学分）各实体之间的联系如下：教师担任课程的1：n“任课”联系教师参加项目的n：m“参加”联系学生选修课程的n：m“选修”联系系、教师和学生之间的所属关系的1：m：n“领导”联系对应的E-R模型如图1.1所示。
图1.1 E-R图2．答：计算结果如下：R∪S R∩S R-S R╳T3．答：计算结果如下：R SσA=C (R S) A B C a b c A R.B S.B C a b d a b b c A R.B S.B C c b c a b b d a b e a c b d c b b c c b b c de acbbddebd4. 答：（1）它是2NF 。
因为R 的候选关键字为课程名，而“课程名→教师名”，“教师名→课程名”不成立，教师名→教师地址，所以课程名 t 教师地址，即存在非主属性教师地址对候选关键字课程名的传递函数依赖，因此R 不是3NF 。
又因为不存在非主属性对候选关键字的部分函数依赖，所以R 是2NF 。
（2）存在删除操作异常。
当删除某门课程时会删除不该删除的教师的有关信息。
（3）分解为高一级范式如下：R 1课程名 教师名 R2 W1 任我行 教师名 教师地址 W2 张三丰 任我行 D1 W3 郭靖 张三丰 D1 W4张三丰郭靖D2分解后，若删除课程数据时，仅对关系R1操作，教师地址信息在关系R2中仍然保留，不会丢失教师方面的信息。
5.答: 令X=D ，X(0)=D 。
在F 中找出左边是D 子集的函数依赖，其结果是D →HG ，所以X(1)=X(0)HG=DGH ，显然有X(1)≠X(0)。
在F 中找出左边是DGH 子集的函数依赖，未找到，则X(2)=DGH 。
实验使用SQL语句进行多表查询●目标✓完成本实验，将能够：使用SQL语句对数据表进行连接查询和子查询操作，掌握连接查询语句和子查询的使用方法。
●实验预估时间：60 min练习使用SQL 语句进行连接查询在本练习中，将使用SQL语句完成对数据表的单表的连接查询操作。
实验步骤：1)启动SQL Server20052)登录数据库服务器3)完成数据库和数据表的构建工作：打开并运行在服务器上共享的数据库构建文件Initialsize.sql文件，具体方法为：a)将服务器上共享的Initialsize.sql文件复制到本地磁盘b)双击本地磁盘中的Initialsize.sql文件，并在弹出的“连接服务器”对话框中点击“连接”按钮。
c)按键盘上的“F5”键运行Initialsize.sql文件中的数据库和数据表的SQL构建语句。
4)查询所有学生的详细信息与选课信息。
5)分别使用连接查询和子查询的方式检索所有选修了2号课程的学生的姓名。
6)分别使用连接查询和子查询的方式检索所有与“刘琳”在一个系学习的学生的姓名。
注：数据SQL语句格式：数据查询：SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …FROM <表名或视图名>[，<表名或视图名> ] …[ WHERE <条件表达式> ][ORDER BY <列名> [ASC|DESC] [,<列名> [ASC|DESC]…] ]子查询：SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …FROM <表名或视图名>[，<表名或视图名> ] …WHERE <列名> IN(SELECT <列名>FROM <表名>[ WHERE <条件表达式> ])。
一在数据库 school 中建立student , sc, course 表。
学生表、课程表、选课表属于数据库School ，其各自的数据结构如下：学生Student (Sno,Sname,Ssex,Sage,Sdept)课程表course(Cno,Cname,Cpno,Ccredit)学生选课SC(Sno,Cno,Grade)二设定主码1 Student表的主码：sno2 Course表的主码：cno3 Sc表的主码：sno，cno1写出使用 Create Table 语句创建表 student , sc, course 的SQL语句23 删除student表中的元组4在数据库school中删除关系student5在student表添加属性sbirthdate 类型datetimeDelete1 删除所有JSJ 系的男生delete from Student where Sdept=’JSJ’ and Ssex=’男’;2 删除“数据库原理”的课的选课纪录delete from SC where Cno in (select Cno fromCourse where Cname=’数据库原理’);Update1 修改0001 学生的系科为: JSJ2 把陈小明的年龄加1岁，性别改为女。
2 修改李文庆的1001课程的成绩为93 分3 把“数据库原理”课的成绩减去1分Select 查询语句一单表1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。
2查询姓名中第2个字为“明”字的学生学号、性别。
3查询 1001课程没有成绩的学生学号、课程号4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名，结果按系排列5按10分制查询学生的sno,cno,10分制成绩（1-10分为1 ，11-20分为2 ，30-39分为3，。
90-100为10）6查询 student 表中的学生共分布在那几个系中。
SQL练习题库表结构Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表Teacher(T#,Tname) 教师表试题：1、查询“0001”课程比“C002”课程成绩高的所有学生的学号；Select s# from scWhere c#='0001' and score>any(select score from sc c#='0002')2、查询平均成绩大于60分的同学的学号和平均成绩；select s#,avg(score) from SCgroup by s#having avg(score)>603、查询所有同学的学号、姓名、选课数、总成绩；select student.s#,student.sname,count(sc.c#)as 选课数,sum(score)总成绩from student,scwhere student.s#=sc.s#group by student.s#,student.sname4、查询姓“张”的老师的个数；select count(*)人数from teacherwhere tname like'张%'5、查询没学过“叶平”老师课的同学的学号、姓名；select student.s#,student.sname from student,course,teacher,scwhere student.s#=sc.s# and course.t#=teacher.t# and teacher.t# not in(select t# from teacher where tname='张丽芬') group by student.s#,student.sname6、查询学过“0001”并且也学过编号“0002”课程的同学的学号、姓名；select sc.s#,sname from sc,studentwhere sc.c# = '0001' and student.s# = sc.s# and sc.s# in (select s# from sc where sc.c# = '0002')--并（两表值）select student.s#,student.sname from student,scwhere student.s#=sc.s# and sc.c#='0001'unionselect student.s#,student.sname from student,scwhere student.s#=sc.s# and sc.c#='0002'--交（有相同值）select student.s#,student.sname from student,scwhere student.s#=sc.s# and sc.c#='0001'intersectselect student.s#,student.sname from student,scwhere student.s#=sc.s# and sc.c#='0002'--差（不同值）select student.s#,student.sname from student,scwhere student.s#=sc.s# and sc.c#='0001'exceptselect student.s#,student.sname from student,scwhere student.s#=sc.s# and sc.c#='0002'7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；select student.s#,student.sname from student,course,teacher,scwhere student.s#=sc.s# and course.t#=teacher.t# and teacher.t# in(select t# from teacher where tname='张丽芬')group by student.s#,student.sname8、查询课程编号“001”的成绩比课程编号“002”课程低的所有同学的学号、姓名；select student.s#,sname,score from student,scwhere student.s#=sc.s# and score in(( select score from sc where c#='0001' )<all( select score from sc where c#='0002' )) 9、查询所有课程成绩小于60分的同学的学号、姓名；select student.s#,sname from studentwhere s# in(select s# from sc where score<60)10、查询没有学全所有课的同学的学号、姓名；select student.s#,student.sname from student,scwhere student.s#=sc.s#group by student.s#,student.snamehaving count(c#)<(select count(c#) from course)11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；select student.s#,student.sname from student,scwhere student.s#=sc.s# and c#=any(select c# from scwhere s#='1001')group by student.s#,student.sname12、查询至少学过学号为“0001”同学所有一门课的其他同学学号和姓名；Select sc.s#,sname from sc inner join student on sc.s#=student.s#Where c# in (select c# from sc where s#=’0001’)13、把“SC”表中“赵雁南”老师教的课的成绩都更改为此课程的平均成绩；update scset score=(select avg(sc.score) from sc,teacher,coursewhere sc.c#=course.c# and course.t#=teacher.t# and teacher.tname='赵雁南')where sc.c#=(select c# from course,teacher where course.t#=teacher.t# and tname='赵雁南' )14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；select student.s#,sname from student,scwhere student.s#=sc.s# and sc.c#=all(select c# from sc where s#='1005') and student.s#<>'1005'15、删除学习“朱玉文”老师课的SC表记录；delete from scwhere c# in(select c# from sc where c# in (select c# from course,teacher where teacher.t#=course.t# and teacher.tname='朱玉文' ) )16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“0002”课程的同学学号、号课的平均成绩；insert into scvaluesselect s# from sc where s# not in (select s# from sc where c#='0002'select avg(score) as 平均成绩from sc where c#='0002'17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分select sc.s#,ame,sc.score,avg(score)as 平均成绩from sc inner join course on sc.c#=course.c#where sc.c# in(select c# from course where cname=any(select cname from course where cname in('计算机基础','Oracle','软件工程')))group by sc.s#,ame,sc.scoreorder by avg(score) descSELECT S# as 学生ID,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='0001') AS 计算机基础,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='0002') AS Oracle,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='0011') AS 软件工程,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩FROM sc AS tGROUP BY S#ORDER BY avg(t.score) desc18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分select c# 课程ID,max(score) 最高分,min(score) 最低分from scgroup by c#19、按各科平均成绩从低到高和及格率的百分数从高到低顺序select c#, avg(score) from scwhere score>60group by c#20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（），马克思（），OO&UML （），数据库（）21、查询不同老师所教不同课程平均分从高到低显示select course.t#,avg(sc.score) as 平均分from sc,coursewhere sc.c#=course.c#group by course.t#order by avg(sc.score) desc22、查询如下课程成绩第3 名到第6 名的学生成绩单：企业管理，马克思，UML，数据库[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩select student.s#,student.sname,sc.score,ame from student inner join sc on student.s#=sc.s#inner join course on sc.c#=course.c#where ame in('oracle','电路分析','计算机基础')order by ame,sc.score desc23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]select sc.c#,ame,count(*)人数from sc,coursewhere course.c#=sc.c#group by sc.c#,ame24、查询学生平均成绩及其名次select sc.s#,avg(score) from scgroup by sc.s#order by avg(score) desc25、查询各科成绩前三名的记录考虑成绩并列情况select s#,c# ,score from scwhere score in (select distinct top 3 score from scgroup by c#,score)order by score desc26、查询每门课程被选修的学生数select c# as 课程号,count(c#)as 选修人数from scgroup by c#order by count(c#) desc27、查询出只选修了一门课程的全部学生的学号和姓名select sc.s#,student.sname from sc inner join student on student.s#=sc.s#group by sc.s#,student.snamehaving count(c#)=128、查询男生、女生人数select ssex, count(*) as 总人数from studentgroup by ssex29、查询姓“张”的学生名单select sname from studentwhere sname like '张%'30、查询同名同性学生名单，并统计同名人数select ssex,count(*) from studentgroup by ssex31、同年出生的学生名单(注：Student表中Sage列的类型是datetime)32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列select c#,avg(score)平均成绩from scgroup by c#order by avg(score) asc33、查询平均成绩大于的所有学生的学号、姓名和平均成绩select top 1 student.s#,student.sname,avg(score)平均成绩from student inner join sc on student.s#=sc.s#group by student.s#,student.snameorder by avg(score) desc34、查询课程名称为“数据库”，且分数低于80的学生姓名和分数select student.sname,sc.score from sc,student,coursewhere student.s#=sc.s# and sc.c#=course.c# and ame='计算机基础' and sc.score<80group by student.sname,sc.score35、查询所有学生的选课情况；select student.s#,sname,c# from student left join sc on student.s#=sc.s#36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；select student.sname,ame,sc.score from student,sc,coursewhere student.s#=sc.s# and sc.c#=course.c# and sc.score>70order by score desc37、查询不及格的课程，并按课程号从大到小排列select c#,score from scwhere score<60order by c#38、查询课程编号为0001且课程成绩在70分以上的学生的学号和姓名；select student.s#,sname from sc,studentwhere c#='0001' and score>70 and student.s#=sc.s#39、求选了课程的学生人数select count(s#)人数from scwhere sc.score<>040、查询选修“oracle”课程的学生中，成绩最高的学生姓名及其成绩select top 1 student.sname,max(sc.score)成绩最高from student inner join sc on student.s#=sc.s# inner join course on sc.c#=course.c#where ame='oracle'group by student.snameorder by max(sc.score) desc41、查询各个课程及相应的选修人数select ame,count(sc.c#) as 选修人数from sc inner join course on sc.c#=course.c#group by ameorder by count(sc.c#) desc,ame asc42、查询不同课程成绩相同的学生的学号、课程号、学生成绩43、查询每门功成绩最好的前两名SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 2 score FROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#44、统计每门课程的学生选修人数（超过人的课程才统计）。
实验5sql语句练习——图书馆数据库实验5 sql语句练习——图书馆数据库实验目的（1）了解SQL Server数据库的逻辑结构和物理结构；（2）了解表的结构特点；（3）了解SQL Server的基本数据类型；（4）了解空值概念；（5）学会在企业管理器中创建数据库和表；（6）学会使用T-SQL语句创建数据库和表。
（7）学会使用T-SQL语句更新数据。
（7）学会使用T-SQL语句创建多种查询。
实验准备首先要明确，能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。
其次创建数据库必须要确定数据库名、数据库大小（最初的大小、最大的大小、是否允许增长及增长方式）和存储数据库的文件。
然后，确定数据库包含哪些表，以及所包含的各表的结构，还要了解SQL Server的常用数据类型，以创建数据库的表。
此外还要了解两种常用的创建数据库、表的方法，即在企业管理器中创建和使用T-SQL的CREATE DATABASE 语句。
实验内容假设有5本书设有一图书馆数据库，其中包括3个表，即图书表、读者表和借阅表。
三个表的结构如图：图书表结构列名说明数据类型约束说明书号图书唯一的编号定长字符串，长度为10主键书名图书的名称定长字符串，长度为50空值作者图书的编著者名定长字符串，长度为30空值出版社图书的出版社定长字符串，长度为30空值单价出版社确定的图书的单价浮点型，Float空值假设有10位读者借阅表结构Create database Lab05（2）用Sql语句创建上述3个表create table book(bookId char(10)primary key,bookName varchar(50),bookWriter varchar(30),bookPublish varchar(30),bookPrice float)create table reader(readerId char(10) primary key,readerName varchar(8)not null,readerSex char(2)not null,readerOfficeTel char(8),readerDepartment varchar(30))create table 借阅表(readerId char(10),bookId char(10),checkOutTime char(8),checkInTime char(8),primary key(readerId,bookId),foreign key (readerId) references reader(readerId),foreign key (bookId) references book(bookId),)（3）基于图书馆数据库的3个表，用sql语言完成一下操作：1）为图书表增加一列“ISBN”，数据类型为CHAR(10)alter table book add ISBN char(10)2）为刚添加的ISBN列增加缺省值约束，约束名为ISBNDEF，缺省值为‘7111085949’ALTER TABLE book ADD CONSTRAINT ISBNDEF DEFAULT ('7111085949') FOR ISBN 3）删除图书表中ISBN列增加的缺省值约束alter table book drop ISBNDEF4）删除图书表中新增的ISBN列ALTER TABLE book DROP COLUMN ISBN5）查询全体图书的图书号、书名、作者、出版社和单价select bookId,bookName,bookWriter,bookPublish,bookPricefrom book6）查询全体图书的信息，其中单价打8折，并设置该列的别名为‘打折价’select bookId,bookName,bookWriter,bookPublish,(bookPrice*0.8) as打折价from book7）显示所有借阅者的读者号，并去掉重复行select distinct readerIdfrom 借阅表8）查询所有单价在20—30元之间的图书信息select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBNfrom bookwhere bookPrice between 20 and 309）查询机械工业出版社、科学出版社、人民邮电出版社的图书信息select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBNfrom bookwhere bookPublish in('机械工业出版社' , '科学出版社','人民邮电出版社') 10）查询既不是机械工业出版社、人民邮电出版社、也不是科学出版社出版的图书信息select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBNfrom bookwhere bookPublish not in('机械工业出版社' , '科学出版社','人民邮电出版社') 11）查询姓名的第二个字符是’建’，并且只有2个字的读者的读者号及姓名select readerId,readerNamefrom readerwhere readerName like '_建'12）查询姓名不是以‘王’、‘张’或‘李’开头的所有读者的读者号及姓名【方式一】查询出来的结果有问题！select readerId,readerNamefrom readerwhere readerName not in ('王%','张%','李%')【方式二】select readerId,readerNamefrom readerwhere readerName not in(select readerNamefrom readerwhere readerName like'王%'or readerName like'张%'or readerName like'李%' )13）查询无归还日期的借阅信息select book.bookId,book.bookName,reader.readerId,reader.readerName,借阅表.checkOutTime as 借书时间,借阅表.checkInTime as 还书时间from 借阅表,book,readerwhere 借阅表.bookId = book.bookIdand 借阅表.readerId = reader.readerIdand 借阅表.checkInTime is null14）查询机械工业出版社图书的平均价格、最高价、最低价select avg(bookPrice)as 平均价格,max(bookPrice) as 最高价 ,min(bookPrice) as 最低价from bookwhere bookPublish = '机械工业出版社图书'15）查询读者的基本信息及借阅情况select reader.readerId,reader.readerName ,借阅表.bookId,book.bookName,book.bookPublishfrom reader , 借阅表 ,bookwhere reader.readerId = 借阅表.readerIdand book.bookId = 借阅表.bookIdand 借阅表.readerId ='1000000007'16）查询至少借阅过1本机械工业出版社出版的图书的读者的读者号、姓名、书名及借阅本数，并按借阅本书多少降序排列select r.Rno,Rname,count(borrow.Bno) 借阅册数from borrow,b,rwhere b.bno=borrow.bno and press='机械工业出版社' and borrow.rno=r.rnogroup by r.rno,Rnameorder by count(borrow.Bno) desc17）查询与‘王小平’的办公电话相同的读者的姓名/* 使用“自连接方式”求解 */select b.readerName,b.readerId,a.readerOfficeTelfrom reader a, reader bwhere a.readerName like '王小平'and a.readerOfficeTel = b.readerOfficeTel/*18）查询所有单价小于平均单价的图书的书号、书名及出版社select bookId,bookName,bookPublish,bookPricefrom bookwhere bookPrice <(select avg(bookPrice) as averagePricefrom book)19）查询‘科学出版社’的图书单价比‘机械工业出版社’最高单价还高的图书书名及单价select bookId,bookName,bookPublish,bookPricefrom bookwhere bookPublish like '科学出版社' and bookPrice >(select max(bookPrice)from bookwhere bookPublish = '机械工业出版社')20）查询‘科学出版社’的图书中单价比‘机械工业出版社’最低单价高的图书书名及单价select bookId,bookName,bookPublish,bookPricefrom bookwhere bookPrice <(select max(bookPrice)from bookwhere bookPublish = '机械工业出版社')and bookPublish like '科学出版社'21）创建机械工业出版社图书的视图CREATE VIEW 机械工业出版社ViewASSELECT bookId,bookName,bookPriceFROM bookWHERE bookPublish = '机械工业出版社'22）创建一个借阅统计视图，名为CountView，包含读者的读者号和总借阅本数CREATE VIEW CountViewASSELECT readerId as 读者号,count(*) as 总借阅本数FROM 借阅表GROUP BY readerId23）创建一个借阅统计视图，名为CountView10,包含借阅总本数打于2的读者号和总借阅本数CREATE VIEW 借阅统计视图ViewASSELECT readerId as 读者号,count(*) as 总借阅本数FROM 借阅表GROUP BY readerIdHAVING COUNT(*) >2。
