数据库sql查询语句练习4_习题_结果图书_习题

第三章综合练习1.创建一个数据库，数据库名字edu，数据文件逻辑名字edu_data，初始大小10M，最大为1024M，增长比例为每次1M;日志文件逻辑名字为edu_log,初始大小5M，最大为100M,按10%比例增长，这些文件都存储到E盘根目录下，并且物理文件名与逻辑文件名全部相同。

CREATE DATABASE eduON PRIMARY( NAME = N'educ_data', FILENAME = N'e:\educ_data.mdf' , SIZE = 10240KB , MAXSIZE = 1024M , FILEGROWTH = 1M)LOG ON( NAME = N'educ_log', FILENAME = N'e:\educ_log.ldf' , SIZE = 5M , MAXSIZE = 100M , FILEGROWTH = 10% )2.在edu数据库里创建一个架构，架构名字为自己名字的汉语拼音。

create schema zhangsan; --自己名字的汉语拼音3.在创建的架构中，创建如下表：表1 student表（学生信息表）表3 student_course表（学生选课成绩表）表4 teacher表（教师信息表）表6 department表（院系息表）创建表的时候必须建立主、外键关系。

create table zhangsan.department(dno char(6) primary key,dname char(8) not null,dhome varchar(40),dzipcode char(6),dtel varchar(40))create table zhangsan.student(sno char(8) not null primary key,sname char(8) not null,sex char(2),[native] char(20),birthday datetime,pno char(4),dno char(6),classno char(4),entime datetime,home varchar(40),tel varchar(40)foreign key(dno) references department(dno) )create table zhangsan.course(cno char(10) primary key,cname char(20) not null,cpno char(10),experiment tinyint,lecture tinyint,semester tinyint,credit tinyint,foreign key (cpno) references course(cno))create table zhangsan.student_course(sno char(8),cno char(10),score tinyint,primary key(sno,cno),foreign key(sno) references student(sno), foreign key(cno) references course(cno))create table zhangsan.teacher(tno char(8) primary key,tname char(8) not null,sex char(2),birthday datetime,dno char(6),pno tinyint,home varchar(40),zipcode char(6),tel varchar(40),email varchar(40),foreign key(dno) references department(dno))create table zhangsan.teacher_course(tcid smallint primary key,tno char(8),classno char(4),cno char(10) not null,semester char(6),schoolyear char(10),classtime varchar(40),classroom varchar(40),weektime tinyint,foreign key(tno) references teacher(tno),foreign key(cno) references course(cno))4.建立、删除索引（1）在student表的sname列建立唯一降序索引；create unique index IX_STUDENT_SNAME on zhangsan.student(sname desc)（2）在course表的credit列上建立升序索引；create index IX_COURSE_CREDIT on zhangsan.course(credit)（3）在student_course表的sno列上建立聚集索引。

sql查询语句练习（解析版）BY DD表情况Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表Teacher(T#,Tname) 教师表create table Student(S# varchar2(20),Sname varchar2(10),Sage int,Ssex varchar2(2)) ;create table Course(C# varchar2(20),Cname varchar2(10),score varchar2(4)) ;create table SC(S# varchar2(20),C# varchar2(20),score varchar2(4)) ;create table Teacher(T# varchar2(20),Tname varchar2(10)) ;insert into Student(S#,Sname,Sage,Ssex) values('1001','李五','15','男');insert into Student(S#,Sname,Sage,Ssex) values('1002','张三','16','女');insert into Student(S#,Sname,Sage,Ssex) values('1003','李四','15','女');insert into Student(S#,Sname,Sage,Ssex) values('1004','陈二','14','男');insert into Student(S#,Sname,Sage,Ssex) values('1005','小四','15','男');问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号；select a.S# from (select s#,score from SC where C#='001') a,(select s#,scorefrom SC where C#='002') bwhere a.score>b.score and a.s#=b.s#;解析：(select s#,score from SC where C#='001') a//从SC中查询C#=001的学生学号和分数，并定义为a表。

先创建下面三个表：（book表）（borrow表）（reader表）1) 找出姓李的读者姓名(NAME)和所在单位(COMPANY)。

2) 列出图书库中所有藏书的书名(BOOK_NAME)及出版单位(OUTPUT)。

3) 查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE)，结果按单价降序排序。

4) 查找价格介于10元和20元之间的图书种类(SORT)，结果按出版单位(OU TPUT)和单价(PRICE)升序排序。

5) 查找书名以”计算机”开头的所有图书和作者(WRITER)。

6) 检索同时借阅了总编号(BOOK_ID)为和两本书的借书证号(READER_ID)。

##7)* 查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。

8)* 找出李某所借所有图书的书名及借书日期(BORROW_DATE)。

9)* 无重复地查询2006年10月以后借书的读者借书证号(READER_ID)、姓名和单位。

##10)* 找出借阅了<FoxPro大全>一书的借书证号。

11) 找出与”赵正义”在同一天借书的读者姓名、所在单位及借书日期。

12) 查询2006年7月以后没有借书的读者借书证号、姓名及单位。

#13) 求”科学出版社”图书的最高单价、最低单价、平均单价。

##14)* 求”信息系”当前借阅图书的读者人次数。

#15) 求出各个出版社图书的最高价格、最低价格和总册数。

#16) 分别找出各单位当前借阅图书的读者人数及所在单位。

17)* 找出当前至少借阅了2本图书(大于等于2本)的读者姓名及其所在单位。

18) 分别找出借书人次数多于1人次的单位及人次数。

19) 找出藏书中各个出版单位的名称、每个出版社的书籍的总册数(每种可能有多册)、书的价值总额。

20) 查询经济系是否还清所有图书。

如果已经还清，显示该系所有读者的姓名、所在单位和职称。

附录：建表语句创建图书管理库的图书、读者和借阅三个基本表的表结构：创建BOOK:（图书表）CREATE TABLE BOOK (BOOK_ID int,SORT VARCHAR(10),BOOK_NAME VARCHAR(50),WRITER VARCHAR(10),OUTPUT VARCHAR(50),PRICE int);创建READER:（读者表）CREATE TABLE READER (READER_ID int,COMPANY VARCHAR(10),NAME VARCHAR(10),SEX VARCHAR(2),GRADE VARCHAR(10),ADDR VARCHAR(50));创建BORROW:（借阅表）CREATE TABLE BORROW (READER_ID int,BOOK_ID int,BORROW_DATE datetime)插入数据:BOOK表:insert into BOOK values(,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into BOOK values(,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into BOOK values(,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into BOOK values(,'TP5/10','计算机基础','李伟','高等教育出版社',18.00);insert into BOOK values(,'TP3/12','FoxBASE','张三','电子工业出版社',23.60);insert into BOOK values(,'TS7/21','高等数学','刘明','高等教育出版社',20.00);insert into BOOK values(,'TR9/12','线性代数','孙业','北京大学出版社',20.80);insert into BOOK values(,'TR7/90','大学英语','胡玲','清华大学出版社',12.50);insert into BOOK values(,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into BOOK values(,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into BOOK values(,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into BOOK values(,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);insert into BOOK values(,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);insert into BOOK values(,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);insert into BOOK values(,'TP4/15','计算机网络','黄力钧','高等教育出版社',2 1.80);READER表:insert into reader values(111,'信息系','王维利','女','教授','1号楼424'); insert into reader values(112,'财会系','李立','男','副教授','2号楼316 ');insert into reader values(113,'经济系','张三','男','讲师','3号楼105'); insert into reader values(114,'信息系','周华发','男','讲师','1号楼316'); insert into reader values(115,'信息系','赵正义','男','工程师','1号楼224 ');insert into reader values(116,'信息系','李明','男','副教授','1号楼318 ');insert into reader values(117,'计算机系','李小峰','男','助教','1号楼214 ');insert into reader values(118,'计算机系','许鹏飞','男','助工','1号楼216 ');insert into reader values(119,'计算机系','刘大龙','男','教授','1号楼318 ');insert into reader values(120,'国际贸易','李雪','男','副教授','4号楼506');insert into reader values(121,'国际贸易','李爽','女','讲师','4号楼510 ');insert into reader values(122,'国际贸易','王纯','女','讲师','4号楼512 ');insert into reader values(123,'财会系','沈小霞','女','助教','2号楼202'); insert into reader values(124,'财会系','朱海','男','讲师','2号楼210'); insert into reader values(125,'财会系','马英明','男','副教授','2号楼212 ');BORROW表:insert into borrow values(112,,'3-19-2006');insert into borrow values(125,,'2-12-2006');insert into borrow values(111,,'8-21-2006');insert into borrow values(112,,'3-14-2006');insert into borrow values(114,,'10-21-2006');insert into borrow values(120,,'11-2-2006');insert into borrow values(120,,'10-18-2006');insert into borrow values(119,,'11-12-2006');insert into borrow values(112,,'10-23-2006');insert into borrow values(115,,'8-21-2006');insert into borrow values(118,,'9-10-2006');现有关系数据库如下：数据库名：图书借阅管理系统读者表(读者编号 char(6)，姓名，性别，年龄，单位，身份证号，职称)图书表(图书编号char(6)，图书名称，出版社，作者)借阅表(读者编号，图书编号，借阅时间)用SQL语言实现下列功能的sql语句代码。

数据库sql查询语句练习4_习题_结果（单世民）图书_习题现有图书管理数据库的三个关系模式：图书（总编号, 分类号, 书名, 作者, 出版单位, 单价）读者（借书证号, 单位, 姓名, 性别, 职称, 地址）借阅（借书证号, 总编号, 借书⽇期）具体数据为：读者：根据以上描述，请完成：DDL1.写出创建上述表的语句命令：create table图书(总编号varchar(7)primary key,分类号varchar(8),书名varchar(18),作者varchar(8),出版单位varchar(18),单价float)create table读者(借书证号varchar(4)primary key,单位varchar(7),姓名varchar(8),性别varchar(2),职称varchar(8),地址varchar(18))create table借阅(借书证号varchar(3),总编号varchar(6),借书⽇期date,primary key(借书证号,总编号,借书⽇期))DML2.给出插⼊上述数据的insert语句命令：insert into图书values('445501','TP3/12','数据库导论','王强','科学出版社', insert into图书values('445502','TP3/12','数据库导论','王强','科学出版社', insert into图书values('445503','TP3/12','数据库导论','王强','科学出版社', insert into图书values('332211','TP5/10','计算机基础','李伟','⾼等教育出版社', insert into图书values('112266','TP3/12','FoxBASE','张三','电⼦⼯业出版社', insert into图书values('665544','TS7/21','⾼等数学','刘明','⾼等教育出版社', insert into图书values('114455','TR9/12','线性代数','孙业','北京⼤学出版社', insert into图书values('113388','TR7/90','⼤学英语','胡玲','清华⼤学出版社', insert into图书values('446601','TP4/13','数据库基础','马凌云','⼈民邮电出版社',insert into图书values('446602','TP4/13','数据库基础','马凌云','⼈民邮电出版社',insert into图书values('446603','TP4/13','数据库基础','马凌云','⼈民邮电出版社',insert into图书values('449901','TP4/14','FoxPro⼤全','周虹','科学出版社', insert into图书values('449902','TP4/14','FoxPro⼤全','周虹','科学出版社', insert into图书values('118801','TP4/15','计算机⽹络','黄⼒钧','⾼等教育出版社',insert into图书values('118802','TP4/15','计算机⽹络','黄⼒钧','⾼等教育出版社',insert into读者values('111','信息系','王维利','⼥','教授','1号楼')insert into读者values('112','财会系','李⽴','男','副教授','2号楼')insert into读者values('113','经济系','张三','男','讲师','3号楼')insert into读者values('114','信息系','周华发','男','讲师','1号楼')insert into读者values('115','信息系','赵正义','男','⼯程师','1号楼')insert into读者values('116','信息系','李明','男','副教授','1号楼')insert into读者values('117','计算机系','李⼩峰','男','助教','1号楼')insert into读者values('118','计算机系','许鹏飞','男','教授','1号楼')insert into读者values('119','计算机系','刘⼤龙','男','副教授','4号楼') insert into读者values('120','国际贸易','李雪','男','副教授','4号楼') insert into读者values('121','国际贸易','李爽','⼥','讲师','4号楼') insert into读者values('122','国际贸易','王纯','⼥','讲师','4号楼') insert into读者values('123','财会系','沈⼩霞','⼥','助教','2号楼') insert into读者values('124','财会系','朱海','男','讲师','2号楼')insert into读者values('125','财会系','马英明','男','副教授','2号楼')insert into借阅values('112','445501','1997-3-19')insert into借阅values('125','332211','1997-2-12')insert into借阅values('111','445503','1997-8-21')insert into借阅values('112','112266','1997-3-14')insert into借阅values('114','665544','1997-10-21')insert into借阅values('120','114455','1997-11-2')insert into借阅values('120','118801','1997-10-18')insert into借阅values('119','446603','1997-12-12')insert into借阅values('112','449901','1997-10-23')insert into借阅values('115','449902','1997-8-21')insert into借阅values('118','118801','1997-9-10')单表查询3.找出姓李的读者姓名和所在单位命令：select姓名,单位from读者where姓名like'李%'结果：4.列出图书库中所有藏书的书名以及出版单位命令：select distinct书名,出版单位from图书结果：5.查找出⾼等教育出版社的所有图书及单价，结果按单价降序排列命令：select distinct书名,单价from图书where出版单位='⾼等教育出版社' order by单价desc结果：6.查找出价格位于10元和20元之间的图书种类，结果按出版单位和单价升序排序命令：select*from图书where单价between 10 and 20 order by出版单位,单价结果：7.找出书名以“计算机”打头的所有图书和作者命令：select distinct书名,作者from图书where书名like'计算机%'结果：8.检索同时接借阅了总编号为112266和449901两本书的借书证号命令：select借书证号from借阅where总编号='112266'intersect select借书证号from借阅where总编号='449901'结果：9.求科学出版社图书的最⾼单价、最低单价和平均单价命令：select MAX(单价)最⾼单价,MIN(单价)最低单价,AVG(单价)平均单价from 图书where出版单位='科学出版社'结果：聚合查询10.找出藏书中各个出版社的册数、价值总额命令：select出版单位,COUNT(*)册数,SUM(单价)价值总额from图书group by 出版单位结果：11.求出各个出版社图书的最⾼价格、最低价格和册数命令：select出版单位,COUNT(*)册数,max(单价)最⾼价格,MIN(单价)最低价格from图书group by出版单位结果：多表查询12.查找所有借了书的读者的姓名以及所在单位命令：select distinct姓名,单位from读者join借阅on读者.借书证号=借阅.借书证号结果：13.找出李某所借图书的所有图书的书名及借书⽇期命令：select姓名,书名,借书⽇期from读者join借阅on读者.借书证号=借阅.借书证号join图书on借阅.总编号=图书.总编号where 姓名like'李%'结果：14.查询1997年10⽉以后借书的读者借书证号、姓名和单位命令：select distinct读者.借书证号,姓名,单位from借阅join读者on借阅.借书证号=读者.借书证号whereDATEDIFF(MONTH,'1977-10-1',借书⽇期)>=0结果：15.找出借阅了FoxPro⼤全⼀书的借书证号命令：select借书证号from借阅join图书on借阅.总编号=图书.总编号where 书名='FoxPro⼤全'结果：16.分别找出借书⼈次超过1⼈次的单位及⼈次数命令：select单位,COUNT(*)⼈次数from借阅join读者on借阅.借书证号=读者.借书证号group by单位having COUNT(*)>1结果：⼦查询17.找出与赵正义在同⼀天借书的读者姓名、所在单位以及借书⽇期命令：select姓名,单位,借书⽇期from读者join借阅on读者.借书证号=借阅.借书证号where借书⽇期=(select借书⽇期from借阅join读者on借阅.借书证号=读者.借书证号where姓名='赵正义')结果：18.查询1997年7⽉以后没有借书的读者借书证号、姓名以及单位命令：select借书证号,姓名,单位from读者except select借阅.借书证号,姓名,单位from借阅join读者on借阅.借书证号=读者.借书证号where DATEDIFF(DAY,'1997-7-1',借书⽇期)>=0结果：19.求信息系当前借阅图书的读者⼈次数命令：select COUNT(*)⼈次数from借阅join读者on借阅.借书证号=读者.借书证号where单位='信息系'结果：20.找出当前⾄少借阅了2本书的读者及所在单位命令：select姓名,单位from借阅join读者on借阅.借书证号=读者.借书证号group by读者.姓名,单位having COUNT(*)>=2结果：21.查询经济系是否还清所有图书。

36.设教学用的四个基本表（S,C,T,SC）（2）查询年龄大于23岁的女同学的学号和姓名select 姓名,学号from swhere 性别='女' and 年龄>23（3）查询至少选修了刘老师所讲授课程中的一门课程的女同学姓名select s.姓名from s,scwhere s.性别='女'and s.学号=sc.学号and sc.课程编号in(select c.课程编号from c,twhere c.教师编号=t.教师编号and t.姓名='刘%')（4）查询至少选修了2门课程的学生学号select sc.学号from scgroup by 学号having count(课程编号)>2（5）查询全部学生都选修的课程号与课程名select c.课程编号,c.课程名称from c,scwhere sc.课程编号=c.课程编号and sc.学号=(select distinct s.学号from s)(6)计算机系每个教师讲授的课程号select t.教师编号,课程编号from c,twhere t.所在系='计算机系（7）查询没有选修过任何一门课程的学生的学号select s.学号from swhere s.学号not in(select distinct sc.学号from sc)（10）统计个系教师的人数select count (教师编号)from tgroup by 所在系（11）统计出教师人数超过10人的系的名称select t.所在系from tgroup by 所在系having count(教师编号)>10（12）在选课表SC中查询成绩为NULL的学生的学号和课程号select 学号课程编号from scwhere 成绩='NULL'（13）姓王的同学的年龄、姓名、选课名称、成绩select 年龄,姓名,课程名称,成绩from s,c,scwhere s.学号=sc.学号and c.课程编号=sc.课程编号and s.姓名='王%'（14）查询年龄大于女同学平均年龄的男同学姓名和年龄select 姓名,年龄from swhere 性别='男' and 年龄>(select avg(年龄)from swhere 性别='女')37.在数据库{USER、ORDER}中，用户需要查询“所有于2009年5月25日下订单的女顾客姓名”。

SQL数据库查询练习题及答案（四⼗五道题）题⽬：设有⼀数据库，包括四个表：学⽣表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。

四个表的结构分别如表1-1的表（⼀）~表（四）所⽰，数据如表1-2的表（⼀）~表（四）所⽰。

⽤SQL语句创建四个表并完成相关题⽬。

表1-1数据库的表结构表（⼀）Student (学⽣表)属性名数据类型可否为空含义Sno varchar (20)否学号（主码）Sname varchar (20)否学⽣姓名Ssex varchar (20)否学⽣性别Sbirthday datetime可学⽣出⽣年⽉Class varchar (20)可学⽣所在班级表（⼆）Course（课程表）属性名数据类型可否为空含义Cno varchar (20)否课程号（主码）Cname varchar (20)否课程名称Tno varchar (20)否教⼯编号（外码）表（三）Score(成绩表)属性名数据类型可否为空含义Sno varchar (20)否学号（外码）Cno varchar (20)否课程号（外码）Degree Decimal(4,1)可成绩主码：表（四）Teacher(教师表)属性名数据类型可否为空含义Tno varchar (20)否教⼯编号（主码）Tname varchar (20)否教⼯姓名Tsex varchar (20)否教⼯性别Tbirthday datetime可教⼯出⽣年⽉Tbirthday datetime可教⼯出⽣年⽉Prof varchar (20)可职称Depart varchar (20)否教⼯所在部门表1-2数据库中的数据表（⼀）StudentSno Sname Ssex Sbirthday class 108曾华男1977-09-0195033 105匡明男1975-10-0295031 107王丽⼥1976-01-2395033 101李军男1976-02-2095033 109王芳⼥1975-02-1095031 103陆君男1974-06-0395031表（⼆）CourseCno Cname Tno3-105计算机导论8253-245操作系统8046-166数字电路8569-888⾼等数学831表（三）ScoreSno Cno Degree1033-245861053-245751093-245681033-105921053-105881093-105761013-105641073-105911073-105911083-105781016-166851076-166791086-16681表（四）TeacherTno Tname Tsex Tbirthday Prof Depart 804李诚男1958-12-02副教授计算机系856张旭男1969-03-12讲师电⼦⼯程系825王萍⼥1972-05-05助教计算机系831刘冰⼥1977-08-14助教电⼦⼯程系查询问题：1、查询Student表中的所有记录的Sname、Ssex和Class列。

数据库中SQL查询语句习题含答案查询问题：设教学数据库Education 有三个关系：学生关系S （SNO ，SNAME ，AGE ，SEX ，SDEPT ）；学习关系SC （SNO ，CNO ，GRADE ）；课程关系C （CNO ，CNAME ，CDEPT ，TNAME ）（1）检索计算机系的全体学生的学号，姓名和性别；（2）检索学习课程号为C2的学生学号与姓名；（3）检索选修课程名为“DS ”的学生学号与姓名；（4）检索选修课程号为C2或C4的学生学号；（5）检索至少选修课程号为C2和C4的学生学号；（6）检索不学C2课的学生姓名和年龄；（7）检索学习全部课程的学生姓名；（8）查询所学课程包含学生S3所学课程的学生学号。

（1）检索计算机系的全体学生的学号，姓名和性别； SELECT Sno ，Sname ，Sex FROM SWHERE Sdept =’CS ’；（2）检索学习课程号为C2的学生学号与姓名；（3）检索选修课程名为“DS ”的学生学号与姓名本查询涉及到学号、姓名和课程名三个属性，分别存放在S 和C 表中，但S 和C 表没有直接联系，必须通过SC 表建立它们二者的联系。

C → SC → S 基本思路：（1）首先在C 表中找出“DS ”课程的课程号Cno ；（2）然后在SC 表中找出Cno 等于第一步给出的Cno 集合中的某个元素Cno ；（3）最后在S 关系中选出Sno 等于第二步中Sno 集合中某个元素的元组，取出Sno 和Sname 送入结果表列。

SELECT Sno ，Sname FROM SWHERE Sno IN （SELECT Sno FROM SCWHERE Cno IN （SELECT Cno FROM CWHERE Cname=‘DS ’））；（4）检索选修课程号为C2或C4的学生学号； SELECT Sno FROM SCWHERE Cno=‘C2’ OR Cno=‘C4’；（5）检索至少选修课程号为C2和C4的学生学号； SELECT SnoFROM SC X ，SC YWHERE X.Sno=Y.Sno AND/doc/d33e82944b7302768e9951e79b89680202d86b33.html o=‘C2’ AND /doc/d33e82944b7302768e9951e79b89680202d86b33.html o=‘C4’；（6）检索不学C2课的学生姓名和年龄；（7）检索学习全部课程的学生姓名；在表S 中找学生，要求这个学生学了全部课程。

SQL语句练习及参考答案编辑整理：尊敬的读者朋友们：这里是精品文档编辑中心，本文档内容是由我和我的同事精心编辑整理后发布的，发布之前我们对文中内容进行仔细校对，但是难免会有疏漏的地方，但是任然希望（SQL语句练习及参考答案）的内容能够给您的工作和学习带来便利。

同时也真诚的希望收到您的建议和反馈，这将是我们进步的源泉，前进的动力。

本文可编辑可修改，如果觉得对您有帮助请收藏以便随时查阅，最后祝您生活愉快业绩进步，以下为SQL语句练习及参考答案的全部内容。

SQL 语句练习1。

设学生选课数据库有关系S （sno ，sname ，age ，sex ）、SC （sno ，cno ，grade ）和C （cno,cname,teacher ），分别表示学生、选课和课程，sno 代表学号，sname 代表学生姓名,age 代表年龄，sex 代表性别，grade 代表成绩，cno 代表课程号，teacher 代表任课教师。

试完成表示下列查询。

(1）检索年龄大于21的男学生学号(sno ）和姓名(sname)。

（2）建立性别只能为“男”、“女"的约束。

(3）创建一个视图v1，该视图用来查询学生的选课情况,要求包含：学生姓名(sname)，课程名（cname),任课教师teacher 和成绩grade 。

（4）检索选修课程号为k1和k5的学生学号（sno ）. (5）检索全部学生都选修的课程的课程号（cno)和课程名（cname ）。

(6)删除所有男同学的选课记录。

1。

（1）select sno ，snae from s where sex=’男' and age 〉21（2）alter table s add constraint c1 check sex in （‘男’,’女'）（3)create view v1 as select sname ，cname,teacher,grade from s ，sc ，c where s 。

西安财经学院信息学院姓名龚雨学号1305170125班级计本 1301《数据库原理》实验报告年级2013 级指导教师张志刚实验名称数据库对象的建立与数据更新查询实验室实验日期一、实验目的及要求1、掌握数据库对象的设计及建立；2、针对给定的实际问题，完成数据库对象的设计，包括各表的结构（属性名、类型、约束等）及表之间的关系，在选定的 DBMS上建立数据库表，用 SQL命令和可视化环境（可选）分别建立数据库表，体会两种方式的特点；3、熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用；加深理解表的定义对数据更新的作用；4、对已经建立的数据库进行修改、删除、插入等操作；5、掌握关系数据库的各种查询方法，理解其含义，包括嵌套查询、连接查询等；6、对关系数据库进行查询，包括嵌套查询、连接查询；二、实验环境硬件平台： PC；软件平台： Windows 2000 / MICROSOFT SQLSERVER；三、实验内容对象的设计与建立用 SQL命令和可视化工具（可选）完成以下操作：1.创建学生 - 课程数据库 :create database xueshengkecheng12．创建学生关系表S，其中 Sno 是主码学号姓名性别年龄所在系Sno Sname Ssex Sage Sdeptcreate table S(sno varchar(30) primary key,sname varchar(30),ssex varchar(30),sage varchar(30),sdept varchar( 30));3．创建课程关系表C，其中 Cno是主码， Cpno是外码：课程号课程名先行课学分Cno Cname Cpno ccreditcreate table C( cno varchar(50) primary key,cname varchar(50),cpno varchar (50),ccreditvarchar(50),foreign key (cpno) references C(cno));4．创建课程关系表SC，其中 (Sno,Cno) 是主码， Sno 和 Cno 分别是外码：学号课程号成绩Sno Cno Gradecreate table SC(sno varchar( 30),cno varchar(50),grate varchar(30),primary key (sno,cno),foreign key(sno)references S(sno),foreign key (cno) references C(cno));5．在表 S 上增加“出生日期”属性列；alter table S add birth varchar(30);6．删除表 S 的“年龄”属性列；alter table S drop column sage;7．在表 S 上，按“ Sno”属性列的 unique 方式建立索引；create unique index Ssno on S(sno);8．删除表 SC；drop table SC cascade;数据更新：对已建好的各表输入适当的数据并练习数据的删除和修改，注意检测键码约束、外码约束、非空约束、唯一约束、取值约束等各种约束在输入数据及数据的删除、修改时的作用，结合课堂教学体会其含义。

查询语句1、查询“001”课程比“002”课程成绩高的所有学生的学号；select a.S# from (select s#,score from SC where C#='001') as a,(selects#,score from SC where C#='002') as bwhere a.score>b.score and a.s#=b.s#2、查询平均成绩大于60分的同学的学号和平均成绩；select S#,avg(score)from scgroup by S# having avg(score) >603、查询所有同学的学号、姓名、选课数、总成绩；select Student.S#,Student.Sname,count(SC.C#),sum(score)from Student left join SC on Student.S#=SC.S#group by Student.S#,Sname4、查询姓“李”的老师的个数；select count(distinct(Tname))from Teacherwhere Tname like '李%'5、查询没学过“张三”老师课的同学的学号、姓名；select Student.S#,Student.Snamefrom Studentwhere S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# andTeacher.Tname='张三')6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S#and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# andSC_2.C#='002')7、查询学过“张三”老师所教的所有课的同学的学号、姓名；select S#,Snamefrom Studentwhere S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C#and Teacher.T#=Course.T# and Teacher.Tname='张三' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher whereTeacher.T#=Course.T# and Tname='张三'))8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；Select S#,Snamefrom (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') as score2from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2<score9、查询所有课程成绩小于60分的同学的学号、姓名；select S#,Snamefrom Studentwhere S# not in (select Student.S# from Student,SC where S.S#=SC.S# andscore>60)10、查询没有学全所有课的同学的学号、姓名；select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course)11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；select S#,Snamefrom Student,SCwhere Student.S#=SC.S# and C# in (select C# from SC where S#='1001')12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；select distinct SC.S#,Snamefrom Student,SCwhere Student.S#=SC.S# and C# in (select C# from SC whereS#='001')13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；update SC set score=(select avg(SC_2.score)from SC SC_2where SC_2.C#=SC.C# )from Course,Teacherwhere Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='张三'14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；select S# from SC where C# in (select C# from SC whereS#='1002')group by S# having count(*)=(select count(*) from SC whereS#='1002')15、删除学习“张三”老师课的SC表记录；Delect SCfrom course ,Teacherwhere Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='张三'16、向SC表中插入一些记录，这些记录要求符合以下条件：学号是没有上过编号“002”课程的同学学号课程号是002成绩是002号课的平均成绩；Insert into SCvalues（select S# from Student where S# not in (Select S# from SC where C#='002'),'002',(Select avg(score)from SC where C#='002')）17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,数据库,企业管理,英语,有效课程数,有效平均分select S# as 学生ID,(select score from SC where SC.S#=t.S# and C#='004') as 数据库，(select score from SC where SC.S#=t.S# and C#='001') as 企业管理,(select score from SC where SC.S#=t.S# and C#='006') as 英语,count(*) as 有效课程数, avg(t.score) as 平均成绩from SC as tgroup by S#order by avg(t.score)18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分select L.C# As 课程ID,L.score as 最高分,R.score as 最低分from SC L ,SC as Rwhere L.C# = R.C# and L.score = (select max(IL.score)from SC as IL,Student as IMwhere L.C# = IL.C# and IM.S#=IL.S# group by IL.C#)and R.Score = (SELECT MIN(IR.score) from SC as IRwhere R.C# = IR.C# group by IR.C# )19、按各科平均成绩从低到高和及格率的百分数从高到低顺序SELECT t.C# as 课程号,max(ame) as课程名,isnull(AVG(score),0) as 平均成绩,100 * SUM(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) as 及格百分数from SC T,Coursewhere t.C#=course.C#group by t.C#order by 100 * SUM(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) DESC20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分,100 * SUM(CASE WHEN C# = '001' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END)AS 企业管理及格百分数,SUM(CASE WHEN C# = '002' THEN scoreELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分,100 * SUM(CASE WHEN C# = '002' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END)AS 马克思及格百分数,SUM(CASE WHEN C# = '003' THEN scoreELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分,100 * SUM(CASE WHEN C# = '003' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END)AS UML及格百分数,SUM(CASE WHEN C# = '004' THEN scoreELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分,100 * SUM(CASE WHEN C# = '004' ANDscore >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END)AS 数据库及格百分数FROM SC21、查询不同老师所教不同课程平均分从高到低显示SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(ame) AS 课程名称,AVG(Score) AS 平均成绩FROM SC AS T,Course AS C ,Teacher AS Zwhere T.C#=C.C# and C.T#=Z.T#GROUP BY C.C#ORDER BY AVG(Score) DESC22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩SELECT DISTINCT top 3SC.S# As 学生学号,Student.Sname AS 学生姓名 ,T1.score AS 企业管理,T2.score AS 马克思,T3.score AS UML,T4.score AS 数据库,ISNULL(T1.score,0) + ISNULL(T2.score,0) +ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分FROM Student,SC LEFT JOIN SC AS T1ON SC.S# = T1.S# AND T1.C# = '001'LEFT JOIN SCAS T2ON SC.S# = T2.S# AND T2.C# = '002'LEFT JOIN SCAS T3ON SC.S# = T3.S# AND T3.C# = '003'LEFT JOIN SCAS T4ON SC.S# = T4.S# AND T4.C# = '004'WHERE student.S#=SC.S# andISNULL(T1.score,0) + ISNULL(T2.score,0) +ISNULL(T3.score,0) + ISNULL(T4.score,0)NOT IN(SELECTDISTINCTTOP 15 WITHTIESISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +ISNULL(T4.score,0)FROM scLEFT JOIN scAS T1ON sc.S# = T1.S# AND T1.C# = 'k1'LEFT JOIN scAS T2ON sc.S# = T2.S# AND T2.C# = 'k2'LEFT JOIN scAS T3ON sc.S# = T3.S# AND T3.C# = 'k3'LEFT JOIN scAS T4ON sc.S# = T4.S# AND T4.C# = 'k4'ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0)+ ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC)23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]SELECT SC.C# as 课程ID, Cname as 课程名称,SUM(CASE WHEN score BETWEEN 85 AND100 THEN 1 ELSE 0 END) AS [100 - 85] ,SUM(CASE WHEN score BETWEEN 70 AND85 THEN 1 ELSE 0 END) AS [85 - 70] ,SUM(CASE WHEN score BETWEEN 60 AND70 THEN 1 ELSE 0 END) AS [70 - 60] ,SUM(CASE WHEN score < 60 THEN 1ELSE 0 END) AS [60 -]FROM SC,Coursewhere SC.C#=Course.C#GROUP BY SC.C#,Cname24、查询学生平均成绩及其名次25、查询各科成绩前三名的记录:(不考虑成绩并列情况)26、查询每门课程被选修的学生数select c#,count(S#) from sc group by C#27、查询出只选修了一门课程的全部学生的学号和姓名select SC.S#,Student.Sname,count(C#) AS 选课数from SC ,Studentwhere SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=128、查询男生、女生人数Select count(Ssex) as 男生人数 fromStudent group by Ssex having Ssex='男' Select count(Ssex) as 女生人数 fromStudent group by Ssex having Ssex='女'29、查询姓“张”的学生名单SELECT Sname FROM Student WHERE Sname like '张%'30、查询同名同姓学生名单，并统计同名人数select Sname,count(*) from Student group by Sname having count(*)>131、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)select Snamefrom studentwhere year(Sage)='1981'32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩select Sname,SC.S# ,avg(score)from Student,SCwhere Student.S#=SC.S# group by SC.S#,Sname having avg(score)>8534、查询课程名称为“数据库”，且分数低于60的学生姓名和分数Select Sname,isnull(score,0)from Student,SC,Coursewhere SC.S#=Student.S# and SC.C#=Course.C# and ame='数据库'and score <6035、查询所有学生的选课情况；SELECT SC.S#,SC.C#,Sname,CnameFROM SC,Student,Coursewhere SC.S#=Student.S# and SC.C#=Course.C#36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；SELECT distinct student.S#,student.Sname,SC.C#,SC.scoreFROM student,ScWHERE SC.score>=70 AND SC.S#=student.S#37、查询不及格的课程，并按课程号从大到小排列select c# from sc where scor e <60 order by C#38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；select SC.S#,Student.Snamefrom SC,StudentwhereSC.S#=Student.S# and Score>80 and C#='003'39、求选了课程的学生人数select count(*) from sc40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩select Student.Sname,scorefrom Student,SC,Course C,Teacherwhere Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T#and Teacher.Tname='张三' andSC.score=(select max(score) from SC where C#=C.C# )41、查询各个课程及相应的选修人数select count(*) from sc group by C#42、查询不同课程成绩相同的学生的学号、课程号、学生成绩43、查询每门功成绩最好的前两名SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 2 score FROM SC WHERE t1.C#= C# ORDER BYscore DESC ) ORDER BY t1.C#44、统计每门课程的学生选修人数（超过10人的课程才统计）。