SQL语句练习题

更新时间：2023-11-06 07:09:01 阅读量：教育文库文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

一、对于学生和课程之间的学习关系，有如下的属性：

学生（学号，姓名，性别，出生日期，民族，班级，家庭住址，联系电话）课程（课程ID，课程名称）学习（学号，课程ID，成绩） 1．画出这种关系的E-R图。 2．指出各表的主键字段。

3．在此数据库的基础上，写出SQL语句。（1）查出所有男同学的基本信息

（2）查出学生的学号，姓名，班级，课程名称，成绩（3）查出成绩表中成绩大于平均成绩的信息（4）查出民族为汉族或者回族的学生的所有基本信息（5）查出成绩在70－80分之间的学生姓名，课程名称和成绩二、.设职工社团数据库有三个基本表: 职工（职工号，姓名，年龄，性别）；社会团体（编号，名称，负责人，活动地点）；参加（职工号，编号，参加日期）。其中：

1）职工表的主码为职工号。

2）社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为职工号。

3）参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。试用SQL语句表达下列操作：

l）定义职工表、社会团体表和参加表，并说明其主码和参照关系。 2）建立下列两个视图。

社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；参加人情况（职工号，姓名，社团编号，社团名称，参加日期） 3）查找参加唱歌队或篮球队的职工号和姓名。

4）查找没有参加任何社会团体的职工情况。 5）查找参加了全部社会团体的职工情况。

6）查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。 7）求每个社会团体的参加人数。

8）求参加人数最多的社会团体的名称和参加人数。 9）求参加人数超过100人的社会团体的名称和负责人。

10）把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。

建立一个数据库表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

创建表和输入数据

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='计算机导论');

有四个表(关系）：

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