MySQL练习题（四）

「已注销」

于 2024-08-03 23:26:59 发布

阅读量806

点赞数 20

分类专栏：数据库专题文章标签： mysql 数据库

本文链接：https://blog.csdn.net/SHI940637335/article/details/140898879

版权

数据库专题专栏收录该内容

11 篇文章 0 订阅

订阅专栏

环境搭建（MySQL:8.0.25）

学生表（Student）

CREATE TABLE Student (
    Sno VARCHAR(20) NOT NULL,
    Sname VARCHAR(20) NOT NULL,
    Ssex VARCHAR(20) NOT NULL,
    Sbirthday DATETIME,
    Class VARCHAR(20),
    PRIMARY KEY (Sno)
);

INSERT INTO Student (Sno, Sname, Ssex, Sbirthday, Class) 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');

课程表（Course）

CREATE TABLE Course (
    Cno VARCHAR(20) NOT NULL,
    Cname VARCHAR(20) NOT NULL,
    Tno VARCHAR(20) NOT NULL,
    PRIMARY KEY (Cno)
);

INSERT INTO Course (Cno, Cname, Tno) VALUES 
('3-105', '计算机导论', '825'),
('3-245', '操作系统', '804'),
('6-166', '数字电路', '856'),
('9-888', '高等数学', '831');

成绩表（Score）表：

CREATE TABLE Score (
    Sno VARCHAR(20) NOT NULL,
    Cno VARCHAR(20) NOT NULL,
    Degree DECIMAL(4,1),
    PRIMARY KEY (Sno, Cno)
);

INSERT INTO Score (Sno, Cno, Degree) 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),
('101', '3-105', 64),
('107', '3-105', 91),
('108', '3-105', 78),
('101', '6-166', 85),
('107', '6-166', 79),
('108', '6-166', 81);

教师信息表（Teacher）

CREATE TABLE Teacher (
    Tno VARCHAR(20) NOT NULL,
    Tname VARCHAR(20) NOT NULL,
    Tsex VARCHAR(20) NOT NULL,
    Tbirthday DATETIME,
    Prof VARCHAR(20),
    Depart VARCHAR(20) NOT NULL,
    PRIMARY KEY (Tno)
);

INSERT INTO Teacher (Tno, Tname, Tsex, Tbirthday, Prof, Depart) VALUES
('804', '李诚', '男', '1958-12-02', '副教授', '计算机系'),
('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系'),
('825', '王萍', '女', '1972-05-05', '助教', '计算机系'),
('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

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

select s.Sname, s.Ssex, s.Class
from Student as s;

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

select distinct Depart
from Teacher;

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

select s.Sno, s.Sname, s.Ssex, s.Sbirthday, s.Class
from Student as s;

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

select Degree
from Score s
where s.Degree >= 60
  and s.Degree <= 80;

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

select s.Sno,s.Degree
from Score s
where s.Degree = 85
   or s.Degree = 86
   or s.Degree = 88;

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

select s.*
from Student s
where s.Class = '95031'
   or s.Ssex = '女';

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

select s.Sno, s.Sname, s.Ssex, s.Sbirthday, s.Class
from Student as s
order by s.Class desc;

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

select s.Sno, s.Cno, s.Degree
from Score s
order by s.Cno asc, s.Degree desc;

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

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

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

select s.Sno, s.Cno
from Score s
where s.Degree >= all (select s.Degree from Score s);

select s.Sno, s.Cno
from Score s
order by s.Degree desc
limit 0,1;

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

select s.Cno, avg(s.Degree) as avg_degree
from Score s
group by s.Cno;

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

select s.Cno, avg(s.Degree) as avg_degree
from Score s
where s.Cno like '3%'
group by s.Cno
having count(s.Sno) >= 5;

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

select s.Sno
from Score s
where s.Degree > 70
  and s.Degree < 90;

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

select st.Sname, sc.Cno, sc.Degree
from Student st
         left join Score sc on st.Sno = sc.Sno;

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

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

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

select st.Sname, cr.Cname, sc.Degree
from Student st
         left join Score sc on st.Sno = sc.Sno
         left join Course cr on cr.Cno = sc.Cno;

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

SELECT AVG(sc.Degree) AS avg_score
FROM Student st
         left JOIN
     Score sc ON st.Sno = sc.Sno
WHERE st.Class = '95033';

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

CREATE TABLE grade
(
    low    INT(3),
    upp    INT(3),
    `rank` CHAR(1)
);

INSERT INTO grade (low, upp, `rank`)
VALUES (90, 100, 'A');
INSERT INTO grade (low, upp, `rank`)
VALUES (80, 89, 'B');
INSERT INTO grade (low, upp, `rank`)
VALUES (70, 79, 'C');
INSERT INTO grade (low, upp, `rank`)
VALUES (60, 69, 'D');
INSERT INTO grade (low, upp, `rank`)
VALUES (0, 59, 'E');

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

SELECT sc.Sno, sc.Cno, gr.rank
FROM Score sc
         JOIN grade gr ON sc.Degree BETWEEN gr.low AND gr.upp;

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

SELECT *
FROM Score
WHERE Cno = '3-105'
  AND Degree > (SELECT Degree
                FROM Score
                WHERE Sno = '109'
                  AND Cno = '3-105');

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

SELECT s1.*
FROM Score s1
         JOIN (SELECT Sno, MAX(Degree) AS MaxDegree
               FROM Score
               GROUP BY Sno
               HAVING COUNT(Cno) > 1) s2 ON s1.Sno = s2.Sno AND s1.Degree < s2.MaxDegree;

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 st2.Sno, st2.Sname, st2.Sbirthday
from Student st1
         inner join Student st2 on st1.Sbirthday = st2.Sbirthday
where st1.Sno = '108';

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

SELECT sc.Sno, sc.Cno, sc.Degree
FROM Teacher tr
    LEFT JOIN Course cr ON cr.Tno = tr.Tno
    LEFT JOIN Score sc ON sc.Cno = cr.Cno
WHERE tr.Tname = '张旭';

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

SELECT DISTINCT t.Tname
FROM Teacher t
         JOIN Course c ON t.Tno = c.Tno
         JOIN (SELECT Cno
               FROM Score
               GROUP BY Cno
               HAVING COUNT(Sno) > 5) sc ON c.Cno = sc.Cno;

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

SELECT DISTINCT Cno
FROM Score
WHERE Degree > 85;

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

SELECT tr.Tname, cr.Cname, sc.Sno, sc.Degree
FROM Teacher tr
         LEFT JOIN Course cr ON tr.Tno = cr.Tno
         LEFT JOIN Score sc ON sc.Cno = cr.Cno
WHERE tr.Depart = '计算机系';

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

SELECT Tname, Prof
FROM Teacher
WHERE Depart IN ('计算机系', '电子工程系');

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

SELECT sc1.Cno,
       sc1.Sno,
       sc1.Degree
FROM Score sc1
         JOIN
     Score sc2 ON sc1.Sno = sc2.Sno
WHERE sc1.Cno = '3-105'
  AND sc2.Cno = '3-245'
  AND sc1.Degree >= sc2.Degree
order by sc1.Degree desc;

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

SELECT sc1.Cno,
       sc1.Sno,
       sc1.Degree
FROM Score sc1
         JOIN
     Score sc2 ON sc1.Sno = sc2.Sno
WHERE sc1.Cno = '3-105'
  AND sc2.Cno = '3-245'
  AND sc1.Degree > sc2.Degree;

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

SELECT Sname     AS name,
       Ssex      AS sex,
       Sbirthday AS birthday
FROM Student

UNION

SELECT Tname     AS name,
       Tsex      AS sex,
       Tbirthday AS birthday
FROM Teacher;

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

SELECT Sname     AS name,
       Ssex      AS sex,
       Sbirthday AS birthday
FROM Student
WHERE Ssex = '女'

UNION

SELECT Tname     AS name,
       Tsex      AS sex,
       Tbirthday AS birthday
FROM Teacher
WHERE Tsex = '女';

注意：这样的回答会生成一个笛卡尔积，这样每一个女学生都会和每一个女教师进行匹配，导致结果集里有大量不必要的重复组合。

select st.Sname, st.Ssex, st.Sbirthday, tr.Tname, tr.Tsex, tr.Tbirthday
from Student st,
     Teacher tr
group by st.Sname, st.Ssex, st.Sbirthday, tr.Tname, tr.Tsex, tr.Tbirthday
having st.Ssex = '女'
   and tr.Tsex = '女';

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

SELECT sc.Sno,
       sc.Cno,
       sc.Degree
FROM Score sc
         JOIN
     (SELECT Cno,
             AVG(Degree) AS avg_degree
      FROM Score
      GROUP BY Cno) avg_sc
     ON
         sc.Cno = avg_sc.Cno
WHERE sc.Degree < avg_sc.avg_degree;

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

SELECT DISTINCT t.Tname,
                t.Depart
FROM Teacher t
         INNER JOIN Course c ON t.Tno = c.Tno;

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

SELECT t.Tname,
       t.Depart
FROM Teacher t
         LEFT JOIN Course c ON t.Tno = c.Tno
WHERE c.Tno IS NULL;

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

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

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

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

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

SELECT Sname,
       TIMESTAMPDIFF(YEAR, Sbirthday, CURDATE()) AS age
FROM Student;

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

SELECT 
    MAX(Sbirthday) AS max_day, 
    MIN(Sbirthday) AS min_day
FROM 
    Student;

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

select *
from Student
order by Sbirthday asc, Class desc;

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

select t.Tname, t.Tsex, t.Depart, c.Cname
from Teacher t
join Course c on t.Tno = c.Tno
where t.Tsex = '男';

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

select sc.Sno, sc.Cno, sc.Degree
from Score sc
order by sc.Degree desc
limit 0,1;

select sc.Sno, sc.Cno, sc.Degree
from Score sc
where sc.Degree = (select max(Degree) from Score);

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

select st2.Sname
from Student st1
         inner join Student st2 on st1.Ssex = st2.Ssex
where st1.Sname = '李军' and st2.Sname != '李军';

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

select st2.Sname
from Student st1
inner join Student st2 on st1.Ssex = st2.Ssex and st1.Class = st2.Class
where st1.Sname = '李军' and st2.Sname != '李军';

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

select st.Sname, st.Ssex, sc.Degree
from Student st
         left join Score sc on st.Sno = sc.Sno
         left join Course cr on cr.Cno = sc.Cno
where cr.Cname = '计算机导论'
  and st.Ssex = '男';