数据库——练习题答案

1).创建数据库

create database学生选课数据库

2).创建四张表

Create table Student

(

Sno Char(3) not null primary key, #学号

Sname Char(8) not null, #学生姓名

Ssex Char(2) not null, #学生姓名

Sbirthday date, #学生出生年月

Class Char(5) #学生所在班级

);

Create table Course

(

Cno Char(5) not null primary key, #课程号

Cname Varchar(10) not null, #课程名称

Tno Char(3) not null #教工编号

);

Create table Score

(

Sno Char(3) not null references Student(Sno) , #学号

Cno Char(5) not null references Course(Cno), #课程号

Degree Decimal(4,1) #成绩

);

create table Teacher

(

Tno Char(3) not null primary key, #教工编号

Tname Char(4) not null, #教工姓名

Tsex Char(2) not null, #教工性别

Tbirthday date, #教工出生年月

Prof Char(6), #职称

Depart Varchar(10) not null #教工所在部门

);

3)分别给四张表插入数据

insert into Student values('108','曾华','男','1977-09-01','95033');

insert into Student values('105','匡明','男','1975-10-02','95031');

insert into Student values('107','王丽','女','1976-01-23','95033');

insert into Student values('101','李军','男','1976-02-20','95033');

insert into Student values('109','王芳','女','1975-02-10','95031');

insert into Student values('103','陆君','男','1974-06-03','95031');

或者一句话:

insert into Student values('108','曾华','男','1977-09-01','95033'),('105','匡明','男','1975-10-02','95031'),('107','王丽','女','1976-01-23','95033'),('101','李军','男','1976-02-20','95033'),('109','王芳','女','1975-02-10','95031'),('103','陆君','男','1974-06-03','95031');

insert into Course values('3-105','计算机导论','825');

insert into Course values('3-245','操作系统','804');

insert into Course values('6-166','数字电路','856');

insert into Course values('9-888','高等数学','831');

或者一句话

insert into Course values('3-105','计算机导论','825'),('3-245','操作系统','804'),('6-166','数字电路','856'),('9-888','高等数学','831');

insert into Score values('103','3-245','86');

insert into Score values('105','3-245','75');

insert into Score values('109','3-245','68');

insert into Score values('103','3-105','92');

insert into Score values('105','3-105','88');

insert into Score values('109','3-105','76');

insert into Score values('101','3-105','64');

insert into Score values('107','3-105','91');

insert into Score values('108','3-105','78');

insert into Score values('101','6-166','85');

insert into Score values('107','6-166','79');

insert into Score values('108','6-166','81');

或者一句话

insert into Score values('103','3-245','86'),('105','3-245','75'),('109','3-245','68'),('103','3-105','92'),('105','3-105','88'),('109','3-105','76'),('107','3-105','91'),('108','3-105','78'),('101','6-166','85'),('107','6-166','79'),('108','6-166','81');

insert into Teacher values('804','李诚','男','1958-12-02','副教授','计算机系');

insert into Teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');

insert into Teacher values('825','王萍','女','1972-05-05','助教','计算机系');

insert into Teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');

或者一句话

insert into Teacher values('804','李诚','男','1958-12-02','副教授','计算机系'),('856','张旭','男','1969-03-12','讲师','电子工程系'),('825','王萍','女','1972-05-05','助教','计算机系'),('831','刘冰','女','1977-08-14','助教','电子工程系');


题目和答案:

1、查询Student表中的所有记录的Sname、Ssex和Class列。

select Sname,Ssex,Class from Student;

2、 查询教师所有的单位即不重复的Depart列。

select distinct Depart from Teacher;

select depart from teacher group by depart;

3、 查询Student表的所有记录。

select * from Student;

4、 查询Score表中成绩在60到80之间的所有记录。

select * from Score where Degree between 60 and 80;

5、 查询Score表中成绩为85,86或88的记录。

select * from Score where Degree=85 or Degree=86 or Degree=88

select * from Score where Degree in (85,86,88)

6、 查询Student表中“95031”班或性别为“女”的同学记录。

select * from Student where class='95031' or Ssex='女';

7、 以Class降序查询Student表的所有记录。

select * from student order by class desc;

8、 以Cno升序、Degree降序查询Score表的所有记录。

select * from Score order by Cno, Degree desc;

9、 查询“95031”班的学生人数。

select count(Sno) from Student where class='95031';

select count(*) from Student where class='95031';

10、查询Score表中的最高分的学生学号和课程号。(子查询或者排序)

select Sno as '学号',Cno as "课程号" from Score where Degree=(select max(Degree) from Score);

11、 查询每门课的平均成绩。

select avg(Degree) from Score group by Cno;

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select avg(Degree) from Score group by Cno having count(Cno)>=5 and Cno like '3%';

select avg(Degree) from Score group by Cno having count(*)>=5 and Cno like '3%';

select avg(Degree) from Score where Cno like '3%' and Cno in (select Cno from Score group by Cno having count(*)>=5);

(自己)Select cno,count(sno),avg(degree) from score where cno like "3%" group by cno having count(sno)>=5;

13、查询分数大于70,小于90的Sno列。

select Sno from Score where Degree between 70 and 90;(包含70和90)

select sno from score where degree >70 and degree<90;(不包含70和90)

14、查询所有学生的Sname、Cno和Degree列。

select Student.Sname,Score.Cno,Score.Degree from Student,Score where

Student.Sno=Score.Sno;

select sname,cno,degree from student join score on Student.Sno=Score.Sno;

15、查询所有学生的Sno、Cname和Degree列。

select Score.Sno,Course.Cname,Score.Degree from Score,Course where Course.Cno=Score.Cno;

select Sno,Cname,Degree from Score join Course on Course.Cno=Score.Cno;

16、查询所有学生的Sname、Cname和Degree列。

select Student.Sname,Course.Cname,Score.Degree from Student,Course,Score where Student.Sno=Score.Sno and Course.Cno=Score.Cno

select Sname,Cname,Degree from Course join Score on Course.Cno=Score.Cno join Student on Student.Sno=Score.Sno

17、查询“95033”班学生的平均分。

select avg(Degree) from Score,Student where Student.Sno=Score.Sno and class='95033'

select avg(Degree) from Score where Sno in (select Sno from Student where class='95033')

18、 假设使用如下命令建立了一个grade表:

create table grade

(

low int(3),upp int(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');

现查询所有同学的Sno、Cno和rank列

select Score.Sno,Score.Cno,grade.rank from score,grade where degree between low and upp #order by rank;

select Sno,Cno,rank from score join grade on degree between low and upp

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select * from Student,Score where Student.Sno=Score.Sno and Cno='3-105' and Degree>76

select distinct a.Cno,a.Sno,a.Degree from Score a,Score b where a.Cno='3-105' and a.Degree>b.Degree and b.Sno='109' and b.Cno='3-105'

select * from Score a where a.Cno='3-105' and a.degree>(select degree b from Score where Sno='109' and Cno='3-105')

select * from Score where Cno='3-105' and degree>(select degree from Score where Sno='109' and Cno='3-105')

20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

select Sno from Score group by Sno having count(*)>1;

select * from Score where Degree not in(select max(Degree) from Score group by cno) #非课程最高分

select * from Score a where Sno in(select Sno from Score group by Sno having count(*)>1) and

a.Degree not in (select max(Degree) from Score b where b.cno=a.cno) #非课程最高分

select * from Score where Sno in(select Sno from Score group by Sno having count(*)>1) and

Degree not in (select max(Degree) from Score group by sno)#非学号最高分

select * from Score where

Degree not in (select max(Degree) from Score group by sno having count(*)>1) and

Sno in(select Sno from Score group by Sno having count(*)>1)#非学号最高分

21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select * from Score where degree>(select degree from Score where Sno='109' and Cno='3-105')

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

select Sno,Sname,Sbirthday from Student where Sbirthday=(select Sbirthday from Student where Sno='108') #年月日全部匹配

select Sno,Sname,Sbirthday from Student where Year(Sbirthday)=(select Year(Sbirthday) from Student where Sno='108') #只匹配年

23、查询“张旭“教师任课的学生成绩。

select Degree from Score,Course,Teacher where Teacher.Tname='张旭'and Teacher.Tno=Course.Tno and Course.Cno=Score.Cno

select Degree from Score where Cno=(select Cno from Course where Tno=(select Tno from Teacher where Tname='张旭'))

24、查询选修某课程的同学人数多于5人的教师姓名。

select Tname from Teacher where Tno in (select Tno from Course,Score group by Score.Cno having count(Score.Cno)>5)

select Tname from Teacher where tno in(select tno from course where cno in (select cno from score group by cno having count(cno)>5))

25、查询95033班和95031班全体学生的记录。

select * from Student where class in('95033','95031')

26、查询存在有85分以上成绩的课程Cno.

select Cno from Score where Degree in (select Degree from Score group by Cno having Degree>85)

select distinct cno from score where degree>85

27、查询出“计算机系“教师所教课程的成绩表。

select distinct * from Score where Cno in (select Cno from Course where Tno in(select Tno from Teacher where Depart='计算机系'))

select distinct Score.Sno,Score.Cno,Score.Degree from Score,Course,Teacher where Score.Cno=Course.Cno and Course.Tno=Teacher.Tno and Teacher.Depart='计算机系'

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

select Tname,Prof from Teacher where Depart='计算机系' and prof not in (select Prof from Teacher where Depart='电子工程系')

#只查出了计算机系中的记录

select Tname,Prof from Teacher where Prof not in (select Prof from Teacher where Depart='计算机系' and prof in (select Prof from Teacher where Depart='电子工程系'))

#查出了计算机系和电子工程系的记录

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

select Cno,Sno,Degree from Score where Cno='3-105' and Degree>any (select Degree from Score where Cno='3-245')order by Degree desc;

# any任何一个值

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

select Cno,Sno,Degree from Score where Cno='3-105' and Degree>(select max(Degree) from Score where Cno='3-245') order by Degree desc

select Cno,Sno,Degree from Score where Cno='3-105' and Degree>all(select Degree from Score where Cno='3-245') #all所有值

31、查询所有教师和同学的name、sex和birthday.

select Tname as name,Tsex as sex,Tbirthday as birthday from Teacher union select Sname,Ssex,Sbirthday from Student

32、查询所有“女”教师和“女”同学的name、sex和birthday.

select Tname,Tsex,Tbirthday from Teacher where Tsex='女' union select Sname,Ssex,Sbirthday from Student where Ssex='女'

33、查询成绩比该课程平均成绩低的同学的成绩表。

select * from Score a where a.Degree<(select avg(Degree) from Score b where b.Cno=a.Cno)

34、 查询所有任课教师的Tname和Depart.

select Tname,Depart from Teacher

select Tname,Depart from Teacher where Tno in(select Tno from Course where Teacher.Tno=Course.Tno) #理解为有课就有任课

select Tname,Depart from Teacher where exists(select Tno from Course where Teacher.Tno=Course.Tno) #通常用exists里面select后面跟*

select Tname,Depart from Teacher where Tno in(select tno from course where cno in (select cno from score where cno !=0))#理解为有课并有学生选课才是有任课

select Tname,Depart from Teacher where Tno in(select tno from course where cno in (select cno from score where course.cno=score.cno))#理解为有课并有学生选课才是有任课

35、 查询所有未讲课的教师的Tname和Depart.

select Tname,Depart from Teacher where Tno not in(select Tno from Course)

select Tname,Depart from Teacher where not exists(select Tno from Course where Teacher.Tno=Course.Tno) #通常用exists里面select后面跟*

select Tname,Depart from Teacher where Tno not in(select tno from course where cno in (select cno from score where cno !=0))

select Tname,Depart from Teacher where Tno not in(select tno from course where cno in (select cno from score))

36、查询至少有2名男生的班号。

select class from Student where Ssex='男' group by class having count(Student.Ssex)>=2

select class from Student where Ssex='男' group by class having count(*)>=2

37、查询Student表中不姓“王”的同学记录。

select * from Student where Sname not like '王%'

38、查询Student表中每个学生的姓名和年龄。

select Sname as姓名,year(now())-year(Sbirthday) as年龄from Student

#now sysdate取现在的时间,年月日点

39、查询Student表中最大和最小的Sbirthday日期值。

select max(Sbirthday),min(Sbirthday) from Student

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

select * from Student order by class desc,Sbirthday

41、查询“男”教师及其所上的课程。

select Teacher.Tname,Teacher.Tsex,Course.Cname from Teacher,Course where Teacher.Tsex='男' and Teacher.Tno=Course.Tno

select Cname from Course where Tno in (select Tno from Teacher where Tsex='男')

42、查询最高分同学的Sno、Cno和Degree列。

select * from Score where Degree=(select max(Degree) from Score)

43、查询和“李军”同性别的所有同学的Sname.

select Sname from Student where Ssex=(select Ssex from Student where Sname='李军')

44、查询和“李军”同性别并同班的同学Sname.

select Sname from Student where Ssex=(select Ssex from Student where Sname='李军') and class=(select class from Student where Sname='李军')

45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

select Cno from Course where Cname='计算机导论'#fen

select Sno from Student where Ssex='男' #fen

select * from Score

where Cno in (select Cno from Course where Cname='计算机导论') and Sno in (select Sno from Student where Ssex='男')

#满足一个条件可以用‘=’,满足多个条件用‘in’,所以最好都用‘in’

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 206,602评论 6 481
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 88,442评论 2 382
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 152,878评论 0 344
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 55,306评论 1 279
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 64,330评论 5 373
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,071评论 1 285
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,382评论 3 400
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,006评论 0 259
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 43,512评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,965评论 2 325
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,094评论 1 333
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,732评论 4 323
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,283评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,286评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,512评论 1 262
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,536评论 2 354
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,828评论 2 345

推荐阅读更多精彩内容