SQL经典练习题47道（简单）

HHHBan

已于 2023-09-09 20:03:25 修改

阅读量439

点赞数 2

分类专栏： SQL练习文章标签： mysql sql 数据库

于 2020-11-18 08:17:51 首次发布

本文链接：https://blog.csdn.net/hhhban/article/details/109763430

版权

SQL练习专栏收录该内容

2 篇文章 0 订阅

订阅专栏

基于MySQL语法

1. 四张表结构

学生表
students(sno,sname,ssex,sbirthday,class)：学生编号,学生姓名,学生性别,出生年月,学生班级
课程表
course(cno,cname,tno)：课程编号, 课程名称, 教师编号
成绩表
score(sno,cno,degree)：学生编号,课程编号,学生分数
教师表
teacher(tno,tname,tsex,tbirthday,prof,depart)：教师编号,教师姓名,教师性别,出生年月,职称,部门编号

2.建表语句

-- 学生表
CREATE TABLE student (
    sno VARCHAR(3) NOT NULL,
    sname VARCHAR(4) NOT NULL,
    ssex VARCHAR(2) NOT NULL,
    sbirthday DATETIME,
    class VARCHAR(5)
);

-- 课程表
CREATE TABLE course (
    cno VARCHAR(5) NOT NULL,
    cname VARCHAR(10) NOT NULL,
    tno VARCHAR(10) NOT NULL
);

-- 成绩表
CREATE TABLE score (
    sno VARCHAR(3) NOT NULL,
    cno VARCHAR(5) NOT NULL,
    degree NUMERIC(10, 1) NOT NULL
);

-- 教师表
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),
       (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(cno, cname, tno)
VALUES ('3-105', '计算机导论', 825),
       ('3-245', '操作系统', 804),
       ('6-166', '数据电路', 856),
       ('9-888', '高等数学', 100);

-- 成绩表测试数据
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-106', 79),
       (108, '6-166', 81);

-- 教师表测试数据
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', '助教', '电子工程系');

3.题目及答案（仅供参考）

-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname, ssex, class
FROM student;

-- 2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart
FROM teacher;

-- 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 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(*)
FROM student
WHERE class = '95031';

-- 10、查询Score表中的最高分的学生学号和课程号。
SELECT sno, cno
FROM score
ORDER BY degree DESC
LIMIT 1;

-- 11、查询‘3-105’号课程的平均分（四舍五入保留2位）。
SELECT ROUND(AVG(degree), 2)
FROM score
WHERE cno = '3-105';

-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数（四舍五入保留2位）。
SELECT AVG(degree)
FROM score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(*) > 5;

-- 13、查询最低分大于70，最高分小于90的Sno列。
SELECT sno
FROM score
GROUP BY sno
HAVING MIN(degree) > 70
   AND MAX(degree) < 90;

-- 14、查询所有学生的Sname、Cno和Degree列。
SELECT sname, cno, degree
FROM student st
     JOIN score sc ON st.sno = sc.sno;

-- 15、查询所有学生的Sno、Cname和Degree列。
SELECT sno, cname, degree
FROM score sc
     JOIN course co ON sc.cno = co.cno;

-- 16、查询所有学生的Sname、Cname和Degree列。
SELECT sname, cname, degree
FROM student st
     JOIN score sc ON st.sno = sc.sno
     JOIN course co ON sc.cno = co.cno;

-- 17、查询“95033”班所选课程的平均分（四舍五入保留3位）。
SELECT ROUND(AVG(degree), 3)
FROM student st
     JOIN score sc ON st.sno = sc.sno
WHERE st.class = '95033';

-- 18、假设使用如下命令建立了一个grade表：
CREATE TABLE grade (
    low int,
    upp int,
    rating char(1)
);
INSERT INTO grade
VALUES (90, 100, 'A'),
       (80, 89, 'B'),
       (70, 79, 'C'),
       (60, 69, 'D'),
       (0, 59, 'E');

-- 现查询所有同学的Sno、Cno和rating列。
SELECT sno, cno, degree, rating
FROM score
     LEFT JOIN grade ON degree BETWEEN low AND 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 *
FROM score
WHERE degree != (SELECT MAX(degree) FROM score)
  AND sno IN (SELECT sno FROM score GROUP BY sno HAVING COUNT(*) > 1);

SELECT sc1.*
FROM score sc1
     JOIN (SELECT sno, MAX(degree) AS degree FROM score GROUP BY sno HAVING COUNT(*) > 1) sc2 ON sc1.sno = sc2.sno
WHERE sc1.degree != sc2.degree;

-- 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 YEAR(sbirthday) = (SELECT YEAR(sbirthday) FROM student WHERE sno = '108');

-- 23、查询“张旭“教师任课的学生成绩。
SELECT degree
FROM teacher te
     JOIN score sc
     JOIN course co ON te.tno = co.tno AND sc.cno = co.cno
WHERE te.tname = '张旭';

-- 24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT te.*
FROM course co
     JOIN teacher te ON co.tno = te.tno
WHERE cno IN (SELECT cno FROM score GROUP BY cno HAVING COUNT(*) > 5);

-- 25、查询95033班和95031班全体学生的记录。
SELECT *
FROM student
WHERE class IN ('95033', '95031');

-- 26、查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT cno
FROM score
WHERE degree > 85;

-- 27、查询出“计算机系“教师所教课程的成绩表。
SELECT sc.*, te.tname, te.depart, co.cname
FROM teacher te
     JOIN score sc
     JOIN course co ON te.tno = co.tno AND sc.cno = co.cno
WHERE depart = '计算机系';

-- 28、查询“计算机系”与“电子工程系“教师的Tname和Prof。
SELECT tname, prof
FROM teacher
WHERE depart IN ('计算机系', '电子工程系');

-- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree降序。
SELECT sc1.*
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.*
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 = '女';

-- 33、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT *
FROM score sc1
     JOIN (SELECT cno, AVG(degree) AS avg FROM score GROUP BY cno) sc2 ON sc1.cno = sc2.cno
WHERE sc1.degree < avg;

-- 34、查询所有任课教师的Tname和Depart.
SELECT tname, depart
FROM teacher te
     JOIN course co ON te.tno = co.tno;

-- 35  查询所有未讲课的教师的Tname和Depart.
SELECT tname, depart
FROM teacher te
     LEFT JOIN course co ON te.tno = co.tno
WHERE cname IS NULL;

-- 36、查询至少有2名男生的班号。
SELECT class
FROM student
WHERE ssex = '男'
GROUP BY class
HAVING COUNT(*) > 1;

-- 37、查询Student表中不姓“王”的同学记录。
SELECT *
FROM student
WHERE sname NOT LIKE '王%';

-- 38、查询Student表中每个学生的姓名和年龄
SELECT sname, YEAR(NOW()) - YEAR(sbirthday)
FROM student;

-- 39、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(sbirthday), MIN(sbirthday)
FROM student;

-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT *
FROM student
ORDER BY class DESC, sbirthday DESC;

-- 41、查询“男”教师及其所上的课程。
SELECT tname, tsex, cname
FROM teacher te
     JOIN course co ON te.tno = co.tno
WHERE tsex = '男';

-- 42、查询最高分同学的Sno、Cno和Degree列。
SELECT *
FROM score
ORDER BY degree DESC
LIMIT 1;

-- 43、查询和“李军”同性别的所有同学的Sname.
SELECT *
FROM student
WHERE ssex = (SELECT ssex FROM student WHERE sname = '李军')
  AND sname != '李军';

-- 44、查询和“李军”同性别并同班的同学Sname.
SELECT *
FROM student
WHERE (ssex, class) = (SELECT ssex, class FROM student WHERE sname = '李军')
  AND sname != '李军';

-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT sc.*
FROM student st
     JOIN score sc
     JOIN course co ON st.sno = sc.sno AND sc.cno = co.cno
WHERE co.cname = '计算机导论'
  AND st.ssex = '男';

-- 46、查询王萍老师的学生中，与王萍老师相同性别的学生姓名
SELECT *
FROM student
WHERE ssex = (SELECT tsex FROM teacher WHERE tname = '王萍');

-- 47、查询出计算机系男老师的学生男女各多少人
SELECT st.ssex, COUNT(*)
FROM teacher te
     JOIN course co
     JOIN score sc
     JOIN student st ON te.tno = co.tno AND co.cno = sc.cno AND sc.sno = st.sno
WHERE te.depart = '计算机系'
  AND te.tsex = '男'
GROUP BY st.ssex;