mysql经典45题

本次练习主要4个表格，分别为students（学生信息表）、courses（课程信息表）、scores（学生成绩表）、teachers（教师信息表），各表的关系图如下：


image

一、建立表格

1.学生信息表

CREATE TABLE students
(sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5))

2.课程信息表

CREATE TABLE courses
(cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL)

3.学生成绩表

CREATE TABLE scores
(sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10, 1) NOT NULL)

4.教师信息表

CREATE TABLE teachers
(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 STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(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、查询所有选修“计算机导论”课程的“男”同学的成绩表

四、代码

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

select sname,ssex,class
from students;

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

select DISTINCT depart
from teachers;

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

select * from students;

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

select sno,degree
from scores
where degree <=80 and degree >=60;

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

select sno,degree
from scores
where degree in (85,86,88)

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

select * from students
where class = "95031" or ssex = "女";

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

Select * from students
ORDER BY class desc;

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

select * from scores
order by cno,degree desc;

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

select class,count(0)
from students
where class = "95031";

10、查询Score表中的最高分的学生学号和课程号。

select sno,cno
from scores
ORDER BY degree desc
limit 1;

11、查询‘3-105’号课程的平均分。

select cno,avg(degree)
from scores
where cno = "3-105";

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

select cno,avg(degree)
from scores
where cno like "3%"
GROUP BY cno
having count(cno) >= 5;

13、查询最低分大于70，最高分小于90的Sno列。

select sno,degree
from scores
GROUP BY sno
having min(degree)>70 and max(degree) < 90;

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

Select sname,cno,degree
from students s1 join scores s2
on s1.sno = s2.sno;

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

select sno,cname,degree
from scores s2 left join courses c
on s2.cno = c.cno;

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

select sname,cname,degree
from students s1 join scores s2
on s1.sno = s2.sno
join courses c
on s2.cno = c.cno;

17、查询“95033”班所选课程的平均分。

select cname,avg(degree)
from students s1 join scores s2
on s1.sno = s2.sno
join courses c
on s2.cno = c.cno
where class = "95033"
GROUP BY c.cname
ORDER BY c.cname;

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

create table grade(low char(3),upp char(3),rank char(2));
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列。

select sno,cno,rank
from scores s2 left join grade g
on s2.degree >= g.low and s2.degree <=g.upp
ORDER BY Sno;

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

select sno,degree
from scores 
where cno = "3-105"
and degree > (select degree from scores where cno = "3-105" and sno = "109")

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

select *
from scores
group by sno
having count(sno) > 1 and degree <> max(degree);

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

select s1.sno,s1.degree
from scores s1 join scores s2
on s1.degree > s2.degree and s1.cno = s2.cno
where s1.cno = "3-105" and s2.sno = "109";

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

select s1.sno,s1.sname,s1.sbirthday
from students s1 join students s2
on year(s1.sbirthday) = year (s2.sbirthday)
where s2.sno = "108";

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

select sno,degree
from teachers t join courses c
on t.tno = c.tno
join scores s
on c.cno = s.cno
where t.tname = "张旭";

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

select tname
from teachers t join courses c
on t.tno = c.tno
join scores s
on s.cno = c.cno
GROUP BY s.cno
HAVING count(s.sno) >5;

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

select * 
from students
where class in("95033","95031")
order by class;

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

select cno
from scores
group by cno
having max(degree) > 85;

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

select s.sno,s.degree
from teachers t  join courses c
on t.tno = c.tno
join scores s
on c.cno = s.cno
where t.depart = "计算机系";

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

SELECT Tname,Prof
FROM Teachers
WHERE Depart='计算机系' AND Prof NOT IN(
    SELECT DISTINCT Prof
    FROM Teachers
    WHERE Depart='电子工程系');

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

SELECT Cno,Sno,Degree
FROM Scores
WHERE Cno='3-105' AND Degree > ANY(
    SELECT Degree
    FROM Scores
    WHERE Cno='3-245')
ORDER BY Degree DESC;

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

SELECT Cno,Sno,Degree
FROM Scores
WHERE Cno='3-105' AND Degree > ALL(
    SELECT Degree
    FROM Scores
    WHERE Cno='3-245')
ORDER BY Degree DESC;

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

select sname,ssex,sbirthday
from students
union
select tname,tsex,tbirthday
from teachers;

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

select sname,ssex,sbirthday
from
(select sname,ssex,sbirthday
from students
union
select tname,tsex,tbirthday
from teachers) as ts
where ssex = "女";

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

select s1.*
from scores s1  join
(select cno,avg(degree)  ad
from scores
GROUP BY cno) as s2
on s1.cno = s2.cno and s1.degree < s2.ad;

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

select tname,depart
from teachers t join courses c
on t.tno = c.tno

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

select tname,depart
from teachers
where tno not in
(select tno
from courses)

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

select class,count(0) as boycount
from students
where ssex = "男"
GROUP BY class
having boycount >= 2;

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

select *
from students
where sname not like "王%";

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

select sname,(year(now()) - year(sbirthday)) as age
from students;

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

select min(sbirthday),max(sbirthday)
from students;

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

select * from students
ORDER BY class desc,sbirthday asc

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

select t.tname,c.cname
from teachers t INNER join courses c
on t.tno = c.tno
where t.tsex = "男";

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

select sno,cno,degree
from scores
ORDER BY degree desc
limit 1;

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

select sname,ssex
from students
where ssex = (select ssex
from students
where sname = "李军"
)

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

select s1.sname,s1.class,s1.ssex
from students s1 join students s2
on s1.ssex = s2.ssex and s1.class = s2.class
where s2.sname = "李军"

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

select s1.sno,s1.cno,s1.degree
from scores s1 join courses c
on s1.cno = c.cno
join students s2
on s1.sno = s2.sno
where c.cname = "计算机导论" and s2.ssex = "男";
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,332评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,508评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,812评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,607评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,728评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,919评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,071评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,802评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,256评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,576评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,712评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,389评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,032评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,026评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,473评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,606评论 2赞 350

推荐阅读更多精彩内容