sql练习题

来源：互联网发布：邻家女孩在淘宝有店吗编辑：程序博客网时间：2024/05/12 18:45

有2张表，为父子关系表：

1 Employee 表
id Name Age
1 张三 31
2 李四 28
3 王五 40
...

2 Wages 表
-------------------------------------------------------------
Emp_id | 工资项目 | 工资金额 |
-------------------------------------------------------------
1 | 基本工资 | 1.00 |
-------------------------------------------------------------
1 | 补助 | 2.00 |
-------------------------------------------------------------
1 | 津贴 | 3.00 |
-------------------------------------------------------------
2 | 基本工资 | 4.00 |
-------------------------------------------------------------
2 | 补助 | 5.00 |
-------------------------------------------------------------
2 | 津贴 | 6.00 |
-------------------------------------------------------------
3 | 基本工资 | 7.00 |
-------------------------------------------------------------
3 | 补助 | 8.00 |
-------------------------------------------------------------
3 | 津贴 | 9.00 |
-------------------------------------------------------------
.........

其中， Employee 表的 id 对应于 Wages 表的 Emp_id 是1对多的关系。

不用存储过程，能否直接用查询检索出下面的结果？

id Name Age 基本工资补助津贴合计
1 张三 31 1 2 3 6
2 李四 28 4 5 6 15
3 王五 40 7 8 9 24

--------------------------------------------------------------------------------------------------------------------------
SELECT ID, NAME, age, t1.基本工资,t2.补助,t3.津贴,t4.合计
FROM employee,
(SELECT emp_id, 工资金额 AS 基本工资
FROM wages
WHERE 工资项目 = '基本工资') t1,
(SELECT emp_id, 工资金额 AS 补助
FROM wages
WHERE 工资项目 = '补助') t2,
(SELECT emp_id, 工资金额 AS 津贴
FROM wages
WHERE 工资项目 = '津贴') t3,
(SELECT emp_id, SUM (工资金额) AS 合计
FROM wages
GROUP BY emp_id) t4
WHERE ID = t1.emp_id AND ID = t2.emp_id AND ID = t3.emp_id AND ID = t4.emp_id

建立一个数据库表student,数据表computer,字段名name,number,sex,SQL2000,flash,net ,其中SQL2000,flash,net设置为浮点型 float.
1、输出所有男生的成绩

use student
　 select yuyan as SQL数据库 ,flash as 网络动画,net as 计算机网络

from computer
where sex='男'

2、输出所有SQL成绩在90以上的女生的成绩

use student
select SQL2000 as SQL数据库 from computer
where sex='女'and SQL2000>=90

3、输出某一科目不合格所有的男生的成绩

use student
select yuyan as SQL数据库 ,flash as 网络动画,net as 计算机网络　　　　
　　　from computer
where sex='男'and SQL2000<60 or flash<60 or net<60

4、计算并显示每位同学各科的总分和平均分，并按总分从高到低排序

use student
select SQL2000+flash+net as 总分,(SQL2000+flash+net/3)as 平均分　　
　　from computer
order by SQL2000+flash+net desc

5、输出所有计算机网络成绩在70-79之间的同学

use student
select * from computer
where flash between 70 and 79

6、输出所有姓“陈”和姓“李”的男生

use student
select * from computer
where sex='男'and left(name,1) in ('李', '陈')

　或者 use student
select * from computer
where sex='男' (and name like '李__'or name like '陈__')

7、输出所有学号为偶数的同学成绩

use student
select num as 学号,SQL2000 as SQL数据库 ,flash as 网络动画,net as 　　
　计算机网络 from computer
where num%2=0

8、输出Flash成绩最好的5位同学

use student
select top 5 * from computer
order by flash desc

9、更新同学的成绩，把计算机网络成绩在55-59之间的同学该科的成绩调整为60分

use student
update computer
set net=60
where net between 55 and 59

10、删除平均分最低的3位同学

　　use student
　　select top 3 *,(SQL2000+flash+net)/3 as 平均分 from computer
　　order by (SQL2000+flash+net)/3
　　delete from computer
　　where number in(033001,033003,033011)

11、统计成绩表中平均分为90以上（含90分）人数

use student
select count(*) from computer
where (SQL2000+flash+net)/3>=90

12、用SQL命令向成绩表添加一个新字段——C语言

　　use student
　　alter table computer
　　add c语言 float

问题描述：
已知关系模式：
s (sno,sname）学生关系。

sno 为学号，

sname 为姓名

c (cno,cname,cteacher) 课程关系。

cno 为课程号，

cname 为课程名，

cteacher 为任课教师
sc(sno,cno,scgrade) 选课关系。

scgrade 为成绩

要求实现如下5个处理：
1．找出没有选修过“李明”老师讲授课程的所有学生姓名
2．列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
3．列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
4．列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
5．列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

1. 找出没有选修过“李明”老师讲授课程的所有学生姓名

--实现代码：
select sname from s
where not exists(
select * from sc,c
where sc.cno=c.cno
and c.cteacher='李明'
and sc.sno=s.sno)

2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

--实现代码：
select s.sno,s.sname,avg_scgrade=avg(sc.scgrade)
from s,sc,(
select sno
from sc
where scgrade<60
group by sno
having count(distinct cno)>=2
)a where s.sno=a.sno and sc.sno=a.sno
group by s.sno,s.sname

3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

--实现代码：
select s.sno,s.sname
from s,(
select sc.sno
from sc,c
where sc.cno=c.cno
and c.cname in('1','2')
group by sno
having count(distinct cno)=2
)sc where s.sno=sc.sno

4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

--实现代码：
select s.sno,s.sname
from s,sc sc1,sc sc2
where sc1.cno='1'
and sc2.sno='2'
and sc1.cno=s.cno
and sc1.scgrade>sc2.scgrade

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

--实现代码：
select sc1.sno,[1号课成绩]=sc1.scgrade,[2号课成绩]=sc2.scgrade
from sc sc1,sc sc2
where sc1.cno='1'
and sc2.cno='2'
and sc1.sno=sc2.sno
and sc1.scgrade>sc2.scgrade

有四个表(关系）：
　　product(maker,model,type)
　　maker:产品制造商，model:型号，type:产品分类(pc,printer,laptop))

　　pc(model,speed,ram,hd,cd,price)
　　laptop(model,speed,ram,hd,screen,price)
　　printer(model,color,price)
　　四个表中主键都是model,
　　求一SQL命令，能查出所有产品中价格(price)最高的产品。

　　select model
　　from product
　　where model in (select model
　　from (
　　select pc.model model,
　　pc.price price
　　from produce,
　　pc
　　where pc.model=product.model

　　union

　　select laptop.model model,
　　laptop.price price
　　from produce,
　　laptop
　　where laptop.model=product.model

　　union

　　select printer.model model,
　　printer.price price
　　from produce,
　　printer
　　where printer.model=product.model
　)
　　where price in (select max(price)
　　from (
　　select pc.model model,
　　pc.price price
　　from produce,
　　pc
　　where pc.model=product.model

　　union

　　select laptop.model model,
　　laptop.price price
　　from produce,
　　laptop
　　where laptop.model=product.model

　　union

　　select printer.model model,
　　printer.price price
　　from produce,
　　printer
　　where printer.model=product.model
　)
　)

　)

创建表和输入数据

CREATE TABLE STUDENT
　(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))
Go
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)
go
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)
go
CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL)

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,1977-09-01,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,1975-10-02,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,1976-01-23,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,1976-02-20,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,1975-02-10,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,1974-06-03,95031);
GO
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825)
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
GO
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
GO
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

练习题目

　　1、查询Student表中的所有记录的Sname、Ssex和Class列。
　　2、查询教师所有的单位即不重复的Depart列。
　　3、查询Student表的所有记录。
　　4、查询Score表中成绩在60到80之间的所有记录。
　　5、查询Score表中成绩为85，86或88的记录。
　　6、查询Student表中“95031”班或性别为“女”的同学记录。
　　7、以Class降序查询Student表的所有记录。
　　8、以Cno升序、Degree降序查询Score表的所有记录。
　　9、查询“95031”班的学生人数。
　　10、查询Score表中的最高分的学生学号和课程号。
　　11、查询‘3-105’号课程的平均分。
　　12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
　　13、查询最低分大于70，最高分小于90的Sno列。
　　14、查询所有学生的Sname、Cno和Degree列。
　　15、查询所有学生的Sno、Cname和Degree列。
　　16、查询所有学生的Sname、Cname和Degree列。
　　17、查询“95033”班所选课程的平均分。
　　18、假设使用如下命令建立了一个grade表：
　　　　create table grade(low number(3,0),upp number(3),rank char(1));
　　　　insert into grade values(90,100,’A’);
　　　　insert into grade values(80,89,’B’);
　　　　insert into grade values(70,79,’C’);
　　　　insert into grade values(60,69,’D’);
　　　　insert into grade values(0,59,’E’);
　　　　commit;
　　　　现查询所有同学的Sno、Cno和rank列。
　　19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
　　20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
　　21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
　　22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
　　23、查询“张旭“教师任课的学生成绩。
　　24、查询选修某课程的同学人数多于5人的教师姓名。
　　25、查询95033班和95031班全体学生的记录。
　　26、查询存在有85分以上成绩的课程Cno.
　　27、查询出“计算机系“教师所教课程的成绩表。
　　28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
　　29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按　　
　　　　Degree从高到低次序排序。
　　30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
　　31、查询所有教师和同学的name、sex和birthday.
　　32、查询所有“女”教师和“女”同学的name、sex和birthday.
　　33、查询成绩比该课程平均成绩低的同学的成绩表。
　　34、查询所有任课教师的Tname和Depart.
　　35、查询所有未讲课的教师的Tname和Depart.
　　36、查询至少有2名男生的班号。
　　37、查询Student表中不姓“王”的同学记录。
　　38、查询Student表中每个学生的姓名和年龄。
　　39、查询Student表中最大和最小的Sbirthday日期值。
　　40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
　　41、查询“男”教师及其所上的课程。
　　42、查询最高分同学的Sno、Cno和Degree列。
　　43、查询和“李军”同性别的所有同学的Sname.
　　44、查询和“李军”同性别并同班的同学Sname.
　　45、查询所有选修“计算机导论”课程的“男”同学的成绩表

SQL语句练习题参考答案
　　1、 select Sname,Ssex,Class from Student;
　　2、 select distinct depart from teacher;
　　3、 select Sno as '学号',Sname as '姓名',Ssex as '性别',Sbirthday as'出生日期',Class as'班号'from student;
　　　　或select Sno as 学号,Sname as 姓名,Ssex as 性别,Sbirthday as 出生日期,Class as 班号 from student;
　　4、 select * from score where degree between 60 and 80;或select * from score where degree>=60 and degree<=80;
　　5、 select * from score where degree in (85,86,88);
　　6、 select * from student where class='95031'or Ssex='女';
　　7、 select * from student order by class desc;
　　8、 select * from score order by cno asc ,degree desc;或select * from score order by cno ,degree desc;
　　9、 select count(*) as CNT from student where class='95031';
　　10、select Sno as '学号',cno as '课程号', degree as '最高分' from scorewhere degree=(select max(degree) from 　　　
　　　　score)
　　11、select avg(degree)as 课程平均分 from score where cno='3-105';
　　12、select cno,avg(degree) from score where cno like'3%'group by cno having count(*) >5;
　　13、select Sno from score group by Sno having min(degree)>70 and max(degree)<90;
　　14、select student.Sname,score.Cno,score.degree from student,score where student.Sno=score.Sno;
　　15、select x.Sno,y.Cname,x.degree from score x,course y where x.Cno=y.Cno;
　　16、select x.Sname,y.Cname,z.degree from student x,course y,score z where x.Sno=z.Sno and z.Cno=y.Cno;
　　17、select y.Cno,avg(y.degree) from student x,score y where x.Sno=y.Sno and x.class='95033'group by y.cno;
　　18、select Sno,Cno,rank from score,grade where degree between low and upp order by rank;
　　19、select x.Cno,x.Sno,x.degree from score x,score ywhere x.cno='3-105' and x.degree>y.degree and　　　
　　　　y.sno='109'and y.cno='3-105';
　　20、
　　　　1,查询成绩非本科最高 select * from score b where degree <(select max(degree) from score a where 　　　　
　　　　　a.cno=b.cno);
　　　　2,查询成绩非本科最高并且选2门以上的学生的成绩:
　　21、select x.cno,x.Sno,x.degree from score x,score y where x.degree>y.degree and y.sno='109'and y.cno='3-105';
　　　　select cno,sno,degree from score where degree >(select degree from score where sno='109' and cno='3-105')
　　22、select sno,sname,sbirthday from student where to_char(sbirthday,'yyyy')=(select to_char(sbirthday,'yyyy') 　　　
　　　　from student where sno='108');
　　23、select cno,sno,degree from score where cno=(select x.cno from course x,teacher y where x.tno=y.tno and
　　　　y.tname='张旭');
　　24、select tname from teacher where tno in(select x.tno from course x,score y where x.cno=y.cno group by x.tno
　　　　having count(x.tno)>5);
　　25、select * from student where class in('95033','95031');
　　26、select distinct cno from score where degree in (select degree from score where degree>85);
　　27、select * from score where cno in(select x.cno from course x,teacher y where y.tno=x.tno and y.depart='计算
　　　　机系');
　　28、select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where
　　　　depart='电子工程系');
　　29、select * from score where cno='3-105' and degree>any (select degree from score where cno='3-245')order by
　　　　degree desc;
　　30、select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');
　　31、select tname,tsex,tbirthday from teacher
　　　　union select sname,ssex,sbirthday from student;
　　32、select tname,tsex,tbirthday from teacher where tsex='女'
　　　　union select sname,ssex,sbirthday from student where ssex='女';
　　33、select * from score a where degree<(select avg(degree)
　　　　from score b where a.cno=b.cno);
　　34、select tname,depart from teacher a where exists
　　　　(select * from course b where a.tno=b.tno);
　　35、select tname,depart from teacher a where not exists
　　　　(select * from course b where a.tno=b.tno);
　　36、select class from student where ssex='男'group by class having count(*)>=2;
　　37、select * from student where sname not like'王_';
　　38、select sname as 姓名,(to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')) as 年龄 from student
　　39、select sname,sbirthday as 最大 from student where sbirthday =(select min (sbirthday) from student)
　　　　union select sname,sbirthday as 最小 from student where sbirthday =(select max(sbirthday) from student)
　　40、select class,sname,sbirthday from student order by class desc,sbirthday;
　　41、select x.tname,y.cname from teacher x,course y where x.tno=y.tno and x.tsex='男';
　　42、select * from score where degree=(select max(degree)from score);
　　43、select sname from student where ssex=(select ssex from student where sname='李军');
　　44、select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class 　　
　　　　from student where sname='李军');
　　45、select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course
　　　　where cname='计算机导论');

1.设有如下4个关系模式：
　　书店（书店编号，书店名，地址）
　　图书（书号，书名，定价）
　　图书馆（馆号，馆名，城市，电话）
　　图书发行（馆号，书号，书店号，数量）
　　设各关系模式中的数据满足下列问题，请回答：
　　　（1）用SQL语句检索已发行的图书中最贵的书名和定价。
　　　（2）写出下列SQL语句所表达的中文意思。
　　select 馆名
　　from 图书馆
　　where 馆号 in
　　（select 馆号
　　　from 图书发行
　　　where 书号 in
　　（select 书号
　　　from 图书
　　　where 书名=‘数据库系统基础’））；
　　2.关于教学数据库的关系式如下：
　　　S（S#，SNAME，AGE，SEX）
　　　SC（S#，C#，CRADE）
　　　C（C#，CNAME，TEACHER）
　　其中，S表示学生，它的各属性依次为学号、姓名、年龄、性别；SC表示成绩，它的各属性依次为学号，课程号和分数；C表示课程，它的各属性依次为课程号、课程名和任课老师。
　　问题：检索学号为10001学生所学课程的课程名与任课教师。

　　1：select "书名","定价" from "图书馆"
　　　 where "定价" = (select top 1 "定价" from "图书馆" desc as '定价') //就是要查出定价最高的价格，可以对　　　　　　　　
　　　定价排序，取到，这个你可以自己实践下，我忘了命令了，本机上没装SQL

　　2：就是要你查出藏有发行“书名=‘数据库系统基础’”的图书馆馆名

　　3：select CNAME,TEACHER from c where C# =
　　　(select C# from SC where S# = '10001')

题目1
问题描述：
为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

要求实现如下5个处理：
1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
5. 查询选修了课程的学员人数
6. 查询选修课程超过5门的学员学号和所属单位

1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
--实现代码：
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM C,SC
WHERE C.[C#]=SC.[C#]
AND CN=N'税收基础')

2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
--实现代码：
SELECT S.SN,S.SD FROM S,SC
WHERE S.[S#]=SC.[S#]
AND SC.[C#]='C2'

3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
--实现代码：
SELECT SN,SD FROM S
WHERE [S#] NOT IN(
SELECT [S#] FROM SC
WHERE [C#]='C5')

4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
--实现代码：
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
RIGHT JOIN C ON SC.[C#]=C.[C#]
GROUP BY [S#]
HAVING COUNT(*)=COUNT(DISTINCT [S#]))

5. 查询选修了课程的学员人数
--实现代码：
SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC

6. 查询选修课程超过5门的学员学号和所属单位
--实现代码：
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(DISTINCT [C#])>5)

Trackback: http://tb.blog.csdn.NET/TrackBack.aspx?PostId=384967

[点击此处收藏本文] 发表于 2005年05月31日 17:08:00

YZLUJI 发表于2005-06-06 4:04 PM IP: 61.186.252.*
SQL练习一：第四题答案是不是有问题？是否应改为
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(*)=(SELECT COUNT(*) FROM C))

boywdj@hotmail.com 发表于2005-07-15 11:03 AM IP: 61.186.252.*
select * from S where S# in(
select S# from SC
group by S#
having count( distinct C#)= (select count(*) from C))
多一个distinct是否更好呢。比如,可能一些没有及格人或者其他情况考了2次,嘿嘿。

shenjane 发表于2006-02-07 9:58 AM IP: 210.22.152.*
第四题好像有错误：

SELECT SN,SD FROM S
WHERE [S#] IN (SELECT [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(*)= (SELECT COUNT(DISTINCT C#) FROM C))

题目2

问题描述：
已知关系模式：
S (SNO,SNAME）学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

要求实现如下5个处理：
1．找出没有选修过“李明”老师讲授课程的所有学生姓名
2．列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
3．列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
4．列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
5．列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

1. 找出没有选修过“李明”老师讲授课程的所有学生姓名
--实现代码：
SELECT SNAME FROM S
WHERE NOT EXISTS(
SELECT * FROM SC,C
WHERE SC.CNO=C.CNO
AND C.CTEACHER='李明'
AND SC.SNO=S.SNO)

2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
--实现代码：
SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,(
SELECT SNO
FROM SC
WHERE SCGRADE<60
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)>=2
)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME

3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
--实现代码：
SELECT S.SNO,S.SNAME
FROM S,(
SELECT SC.SNO
FROM SC,C
WHERE SC.CNO=C.CNO
AND C.CNAME IN('1','2')
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)=2
)SC WHERE S.SNO=SC.SNO

4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
--实现代码：
SELECT S.SNO,S.SNAME
FROM S,SC SC1,SC SC2
WHERE SC1.CNO='1'
AND SC2.SNO='2'
AND SC1.CNO=S.CNO
AND SC1.SCGRADE>SC2.SCGRADE

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
--实现代码：
SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE
FROM SC SC1,SC SC2
WHERE SC1.CNO='1'
AND SC2.CNO='2'
AND SC1.SNO=SC2.SNO
AND SC1.SCGRADE>SC2.SCGRADE

Trackback: http://tb.blog.csdn.Net/TrackBack.aspx?PostId=384993

[点击此处收藏本文] 发表于 2005年05月31日 17:31:00

十年等待发表于2005-06-06 12:04 PM IP: 61.186.252.*
你好：

1．找出没有选修过“李明”老师讲授课程的所有学生姓名

SELECT SNAME FROM S
WHERE NOT EXISTS(
SELECT * FROM SC,C <=这里是否要加上S
WHERE SC.CNO=C.CNO
AND CNAME='李明' <=应该是CTEACHER = '李明' 吧
AND SC.SNO=S.SNO)

十年等待发表于2005-06-06 12:47 PM IP: 61.186.252.*
列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
SELECT S.SNO,S.SNAME
FROM S,(
SELECT SC1.SNO
FROM SC SC1,C C1,SC SC2,C C2
WHERE SC1.CNO=C1.CNO AND C1.NAME='1'
AND SC2.CNO=C2.CNO AND C2.NAME='2'
<=这里好像牛头不对马嘴吧？？
<=是AND SC2.CNO=C2.CNO AND SC2.SNO='2'才对吧
AND SC1.SCGRADE>SC2.SCGRADE
)SC WHERE S.SNO=SC.SNO

而且“1”号课，就我的理解是course的ID,也就是CNO,怎么会是CNAME,而且这里你还写成了C1.NAME，这种态度要不得阿

我是新手，一般都是上网查资料的，还好以前学了一点点，不然对于那些一点都不会的人，不是被楼主害死了，要么不说，要么就要有认真的态度，直言所至，请楼主谅解

ghb 发表于2005-11-14 12:04 PM IP: 61.236.10.*
/*
问题描述：
已知关系模式：
S (SNO,SNAME）学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

--create table s(sno varchar(10),sname varchar(20))
--create table c(cno varchar(10),cname varchar(20),cteacher varchar(20))
--create table sc(sno varchar(10),cno varchar(20),scgrade integer)

insert into s
select '1','ghb'
union all select '2','tw'
union all select '3','wkp'

insert into c
select '1','语文','李明'
union all select '2','数学','王了'
union all select '3','英语','其它'

insert into sc
select '1','2',50
union all select '1','3',52
union all select '2','1',80
union all select '2','2',90
union all select '2','3',59
union all select '3','1',100
union all select '3','2',59
union all select '3','3',70

--delete from sc where sno = '1' and cno = '1'
select *
from s
select *
from c
select *
from sc
-- 1．找出没有选修过“李明”老师讲授课程的所有学生姓名
select sname
from s
where s.sno not in (select sc.sno from sc,c where sc.cno = c.cno and c.cteacher = '李明')

SELECT SNAME FROM S
WHERE NOT EXISTS(
SELECT * FROM SC,C
WHERE SC.CNO=C.CNO
AND C.CTEACHER='李明'
AND SC.SNO=S.SNO)

-- 2．列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
select s.sname,avg(sc.scgrade) as avgsc
from s,sc
where s.sno = sc.sno and s.sno in (select sno from sc where scgrade < 60 group by sno having count(sno) >= 2)
group by s.sname

select s.SNAME,avg(sc.SCGRADE) from S,sc where sc.SCGRADE<60 and s.sno=sc.sno group by s.SNAME having count(sc.SCGRADE)>=2

SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,(
SELECT SNO
FROM SC
WHERE SCGRADE<60
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)>=2
)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME

-- 3．列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
select tem.sname
from
(
select s.sname
from s
where s.sno in(select sno from sc where cno = '1')
union all
select s.sname
from s
where s.sno in(select sno from sc where cno = '2')
)tem
group by tem.sname
having count(tem.sname) > 1

select s.sname from s,sc where s.sno=sc.sno and sc.cno=1 and sc.sno in (select b.sno from sc b where b.sno=sc.sno and b.cno=2)

select s.sname from s,sc where s.sno=sc.sno and sc.cno=1 and exists (select b.sno from sc b where b.sno=sc.sno and b.cno=2)

SELECT S.SNO,S.SNAME
FROM 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

-- 4．列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
select sc.sno
from sc
where cno = '1' and scgrade > (select scgrade from sc where sno = '2' and cno = '1') and sno <> '2'

SELECT Sc.SNO FROM SC where sc.cno = '1' and exists (select * from sc b where b.sno = sc.sno and sc.scgrade > b.scgrade and b.sno = '2')

select s.SNO from s,sc where s.sno=sc.sno and sc.cno=1 and sc.SCGRADE>(select b.SCGRADE from sc b where b.sno=sc.sno and b.cno=2)

SELECT S.SNO,S.SNAME
FROM S,SC SC1,SC SC2
WHERE SC1.CNO='1'
AND SC2.SNO='2'
AND SC1.CNO=S.CNO
AND SC1.SCGRADE>SC2.SCGRADE

select sc1.sno
from sc sc1,sc sc2
where sc1.cno = '1' and sc1.sno <> '2' and sc2.cno = '1' and sc2.sno = '2' and sc1.scgrade > sc2.scgrade and sc1.cno = sc2.cno

-- 5．列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
select sc1.sno,sc1.scgrade,sc2.scgrade
from sc sc1,sc sc2
where sc1.sno = sc2.sno and sc1.cno = '1' and sc2.cno = '2' and sc1.scgrade > sc2.scgrade

select a.SNO,a.SCGRADE from sc a where (a.CNO=1 or a.CNO=2)
and a.sno in
(select s.SNO from s,sc where s.sno=sc.sno and sc.cno=1 and sc.SCGRADE>(select b.SCGRADE from sc b where b.sno=sc.sno and b.cno=2))

SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE
FROM SC SC1,SC SC2
WHERE SC1.CNO='1'
AND SC2.CNO='2'
AND SC1.SNO=SC2.SNO
AND SC1.SCGRADE>SC2.SCGRADE

drop table s
drop table c
drop table sc

shenjane 发表于2006-02-07 3:13 PM IP: 210.22.152.*
第四、第五题都有一些错误
SELECT S.SN,SC1.SCGRADE AS 课程1,SC2.SCGRADE as 课程2 FROM S,SC SC1,SC SC2 WHERE
S.SNO = SC1.SNO AND SC1.CNO='0001'AND SC2.CNO='0002'
AND SC1.SNO=SC2.SNO AND SC1.SCGRADE<SC2.SCGRADE

题目3

问题描述：
本题用到下面三个关系表：
CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级
BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下15个处理：
1．写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。
2．找出借书超过5本的读者,输出借书卡号及所借图书册数。
3．查询借阅了"水浒"一书的读者，输出姓名及班级。
4．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
5．查询书名包括"网络"关键词的图书，输出书号、书名、作者。
6．查询现有图书中价格最高的图书，输出书名及作者。
7．查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。
8．将"C01"班同学所借图书的还期都延长一周。
9．从BOOKS表中删除当前无人借阅的图书记录。
10．如果经常按书名查询图书信息，请建立合适的索引。
11．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。
12．建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。
13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。
15．对CARD表做如下修改：
a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。
b. 为该表增加1列NAME（系名），可变长，最大20个字符。

1. 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束
--实现代码：
CREATE TABLE BORROW(
CNO int FOREIGN KEY REFERENCES CARD(CNO),
BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
RDATE datetime,
PRIMARY KEY(CNO,BNO))

2. 找出借书超过5本的读者,输出借书卡号及所借图书册数
--实现代码：
SELECT CNO,借图书册数=COUNT(*)
FROM BORROW
GROUP BY CNO
HAVING COUNT(*)>5

3. 查询借阅了"水浒"一书的读者，输出姓名及班级
--实现代码：
SELECT * FROM CARD c
WHERE EXISTS(
SELECT * FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
AND b.BNAME=N'水浒'
AND a.CNO=c.CNO)

4. 查询过期未还图书，输出借阅者（卡号）、书号及还书日期
--实现代码：
SELECT * FROM BORROW
WHERE RDATE<GETDATE()

5. 查询书名包括"网络"关键词的图书，输出书号、书名、作者
--实现代码：
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE BNAME LIKE N'%网络%'

6. 查询现有图书中价格最高的图书，输出书名及作者
--实现代码：
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE PRICE=(
SELECT MAX(PRICE) FROM BOOKS)

7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出
--实现代码：
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法'
AND NOT EXISTS(
SELECT * FROM BORROW aa,BOOKS bb
WHERE aa.BNO=bb.BNO
AND bb.BNAME=N'计算方法习题集'
AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC

8. 将"C01"班同学所借图书的还期都延长一周
--实现代码：
UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
FROM CARD a,BORROW b
WHERE a.CNO=b.CNO
AND a.CLASS=N'C01'

9. 从BOOKS表中删除当前无人借阅的图书记录
--实现代码：
DELETE A FROM BOOKS a
WHERE NOT EXISTS(
SELECT * FROM BORROW
WHERE BNO=a.BNO)

10. 如果经常按书名查询图书信息，请建立合适的索引
--实现代码：
CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

11. 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）
--实现代码：
CREATE TRIGGER TR_SAVE ON BORROW
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
INSERT BORROW_SAVE SELECT i.*
FROM INSERTED i,BOOKS b
WHERE i.BNO=b.BNO
AND b.BNAME=N'数据库技术及应用'

12. 建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）
--实现代码：
CREATE VIEW V_VIEW
AS
SELECT a.NAME,b.BNAME
FROM BORROW ab,CARD a,BOOKS b
WHERE ab.CNO=a.CNO
AND ab.BNO=b.BNO
AND a.CLASS=N'力01'

13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出
--实现代码：
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
AND b.BNAME IN(N'计算方法',N'组合数学')
GROUP BY a.CNO
HAVING COUNT(*)=2
ORDER BY a.CNO DESC

14. 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句
--实现代码：
ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)

15.1 将NAME最大列宽增加到10个字符（假定原为6个字符）
--实现代码：
ALTER TABLE CARD ALTER COLUMN NAME varchar(10)

15.2 为该表增加1列NAME（系名），可变长，最大20个字符
--实现代码：
ALTER TABLE CARD ADD 系名 varchar(20)

0 0