MySql 数据库综合练习03
提示:部分数据不满足查询要求,即结果为null
数据库综合练习03
- MySql 数据库综合练习03
- 一、数据库创建代码
- 二、题目
- 1、查询Score表中成绩在60到80之间的所有记录
- 2、查询 score 表中成绩为85,86或88的记录
- 3、以 cno 升序、degree降序查询 score 表的所有记录
- 4、查询“95031”班的学生人数。
- 5、查询Score表中的最高分的学生学号和课程号
- 6、查询‘3-105’号课程的平均分。
- 7、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
- 8、查询最低分大于70,最高分小于90的Sno列。
- 9、查询所有学生的Sname、Cno和Degree列。
- 10、查询所有学生的Sno、Cname和Degree列。
- 11、查询所有学生的Sname、Cname和Degree列。
- 12、查询“95033”班所选课程的平均分。
- 13、查询所有同学的Sno、Cno和rank列。 Rank为等级排序
- 14、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
- 15、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录
- 16、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
- 17、查询“张旭“教师任课的学生成绩。
- 18、查询选修某课程的同学人数多于5人的教师姓名。
- 19、查询95033班和95031班全体学生的记录。
- 20、查询存在有85分以上成绩的课程Cno。
- 21、查询出“计算机系“教师所教课程的成绩表。
- 22、查询所有教师和同学的name、sex和birthday。
- 23、查询所有“女”教师和“女”同学的name、sex和birthday。
- 24、查询成绩比该课程平均成绩低的同学的成绩表。
- 25、查询所有任课教师的Tname和Depart。
- 26、查询至少有2名男生的班号。
- 27、查询Student表中不姓“王”的同学记录。
- 28、查询Student表中每个学生的姓名和年龄。
提示:以下均为内容为 题目 + sql查询语句 + 查询结果图
from ===> where ===> group by ===> having ===> select ===> order by ===> limit
一、数据库创建代码
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);
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);
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);
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 (101, '6-166', 85);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (107, '6-106', 79);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (108, '3-105', 78);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (108, '6-166', 81);
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、查询Score表中成绩在60到80之间的所有记录
-- 1、查询Score表中成绩在60到80之间的所有记录
SELECT *
FROM score
WHERE score.DEGREE BETWEEN 60 AND 80
2、查询 score 表中成绩为85,86或88的记录
-- 2、查询 score 表中成绩为85,86或88的记录
SELECT *
FROM score
WHERE score.DEGREE IN (85,86,88);
3、以 cno 升序、degree降序查询 score 表的所有记录
-- 3、以 cno 升序、degree降序查询 score 表的所有记录
SELECT *
FROM score
ORDER BY score.SNO ASC,score.DEGREE DESC
4、查询“95031”班的学生人数。
-- 4、查询“95031”班的学生人数。
SELECT COUNT(*)
FROM student
WHERE student.CLASS = '95031'
5、查询Score表中的最高分的学生学号和课程号
-- 5、查询Score表中的最高分的学生学号和课程号
SELECT score.DEGREE,score.SNO,score.CNO
FROM score
ORDER BY score.DEGREE DESC
LIMIT 1 OFFSET 0;
6、查询‘3-105’号课程的平均分。
-- 6、查询‘3-105’号课程的平均分。
SELECT AVG(score.DEGREE)
FROM score
WHERE score.CNO ='3-105'
7、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
-- 7、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(score.DEGREE)
FROM score
WHERE score.CNO LIKE '3%'
HAVING COUNT(*) >=5
8、查询最低分大于70,最高分小于90的Sno列。
-- 8、查询最低分大于70,最高分小于90的Sno列。
SELECT score.SNO
FROM score
GROUP BY score.SNO
HAVING MIN(score.DEGREE) > 70 AND MAX(score.DEGREE) <90
9、查询所有学生的Sname、Cno和Degree列。
-- 9、查询所有学生的Sname、Cno和Degree列。
SELECT S.SNAME,C.CNO,C.DEGREE
FROM student S
JOIN score C ON (S.sno = C.SNO)
ORDER BY S.SNAME ASC
10、查询所有学生的Sno、Cname和Degree列。
-- 10、查询所有学生的Sno、Cname和Degree列。
SELECT S.SNO,CR.CNAME,C.DEGREE
FROM student S
JOIN score C ON (S.SNO = C.SNO )
JOIN course CR ON (CR.cno = C.CNO)
ORDER BY S.SNO
11、查询所有学生的Sname、Cname和Degree列。
-- 11、查询所有学生的Sname、Cname和Degree列。
SELECT S.SNAME,CR.CNAME,C.DEGREE
FROM student S
JOIN score C ON (S.SNO = C.SNO )
JOIN course CR ON (CR.cno = C.CNO)
12、查询“95033”班所选课程的平均分。
-- 12、查询“95033”班所选课程的平均分。
SELECT AVG(C.DEGREE)
FROM score C
JOIN student S ON (C.SNO = S.SNO)
WHERE S.CLASS ='95033'
13、查询所有同学的Sno、Cno和rank列。 Rank为等级排序
首先创建一个rank等级表
CREATE TABLE grade (
low NUMERIC (3, 0),
upp NUMERIC (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');
-- 13、查询所有同学的Sno、Cno和rank列。 Rank为等级排序
SELECT SS.SNO,S.CNO,G.rank
FROM score S
JOIN grade G ON(S.DEGREE BETWEEN G.low AND G.upp)
JOIN student SS ON (S.SNO = SS.SNO)
ORDER BY G.rank ASC
方式②
-- 13、查询所有同学的Sno、Cno和rank列。 Rank为等级排序
SELECT SC.SNO,SC.CNO,(CASE
WHEN SC.DEGREE BETWEEN 90 AND 100 THEN 'A'
WHEN SC.DEGREE BETWEEN 80 AND 90 THEN 'B'
WHEN SC.DEGREE BETWEEN 70 AND 80 THEN 'C'
WHEN SC.DEGREE BETWEEN 60 AND 70 THEN 'D'
ELSE
'不及格'
END) AS rank
FROM score SC
ORDER BY rank ASC
14、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
-- 14、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT *
FROM score SC
JOIN student S ON (SC.SNO = S.SNO)
WHERE SC.CNO ='3-105' AND S.SNO <> '109' AND SC.DEGREE > (SELECT DEGREE FROM score WHERE score.SNO = '109' AND score.CNO ='3-105')
15、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录
-- 15、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录
SELECT COUNT(S.DEGREE),S.DEGREE
FROM score S
WHERE S.DEGREE < (SELECT MAX(score.DEGREE) FROM score WHERE score.SNO = S.SNO)
GROUP BY S.SNO,S.DEGREE
HAVING COUNT(DEGREE) >=1
16、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
-- 16、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT S.SNO ,S.SNAME,S.SBIRTHDAY
FROM student S
WHERE YEAR(S.SBIRTHDAY) = (SELECT YEAR(student.SBIRTHDAY) FROM student WHERE student.SNO = '108') AND S.SNO <> '108'
17、查询“张旭“教师任课的学生成绩。
-- 17、查询“张旭“教师任课的学生成绩。
SELECT S.*
FROM score S
JOIN course C ON (S.CNO = C.CNO )
JOIN teacher T ON (T.TNO = C.TNO)
WHERE T.TNAME = '张旭'
18、查询选修某课程的同学人数多于5人的教师姓名。
-- 18、查询选修某课程的同学人数多于5人的教师姓名。
SELECT T.TNAME
FROM score S
JOIN course C ON (S.CNO = C.CNO )
JOIN teacher T ON (T.TNO = C.TNO)
GROUP BY S.CNO,T.TNAME
HAVING COUNT(S.SNO) > 5
19、查询95033班和95031班全体学生的记录。
-- 19、查询95033班和95031班全体学生的记录。
SELECT S.*
FROM student S
WHERE S.CLASS IN ('95033','95031')
20、查询存在有85分以上成绩的课程Cno。
-- 20、查询存在有85分以上成绩的课程Cno。
SELECT score.CNO
FROM score
WHERE score.DEGREE > 85
GROUP BY score.CNO
21、查询出“计算机系“教师所教课程的成绩表。
-- 21、查询出“计算机系“教师所教课程的成绩表。
SELECT S.DEGREE,C.CNAME,T.TNAME
FROM score S
JOIN course C ON (S.CNO = C.CNO)
JOIN teacher T ON (T.TNO = C.TNO)
WHERE T.DEPART = '计算机系';
22、查询所有教师和同学的name、sex和birthday。
-- 22、查询所有教师和同学的name、sex和birthday。
SELECT S.SNAME name,S.SSEX sex,S.SBIRTHDAY birthday
FROM student S
UNION
SELECT T.TNAME ,T.TSEX ,T.TBIRTHDAY
FROM teacher T ;
23、查询所有“女”教师和“女”同学的name、sex和birthday。
-- 23、查询所有“女”教师和“女”同学的name、sex和birthday。
SELECT S.SNAME NAME, S.SSEX SEX ,S.SBIRTHDAY BIRTHDAY
FROM student S
WHERE S.SSEX = '女'
UNION
SELECT T.TNAME , T.TSEX ,T.TBIRTHDAY
FROM teacher T
WHERE T.TSEX = '女'
24、查询成绩比该课程平均成绩低的同学的成绩表。
-- 24、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT S.DEGREE,S.SNO
FROM score S
WHERE S.DEGREE < (SELECT AVG(DEGREE) FROM score WHERE score.CNO = S.CNO)
25、查询所有任课教师的Tname和Depart。
-- 25、查询所有任课教师的Tname和Depart。
SELECT T.TNAME ,T.DEPART
FROM teacher T
INNER JOIN course C ON (T.TNO = C.TNO)
26、查询至少有2名男生的班号。
-- 26、查询至少有2名男生的班号。
SELECT student.CLASS
FROM student
WHERE student.SSEX= '男'
GROUP BY student.CLASS
HAVING COUNT(student.SNO) >= 2
27、查询Student表中不姓“王”的同学记录。
-- 27、查询Student表中不姓“王”的同学记录。
SELECT student.*
FROM student
WHERE student.SNAME NOT LIKE '王%'
28、查询Student表中每个学生的姓名和年龄。
-- 28、查询Student表中每个学生的姓名和年龄。
SELECT student.SNAME,(TIMESTAMPDIFF(YEAR,student.SBIRTHDAY,CURRENT_DATE)) AS '年龄'
FROM student