CREATE DATABASE /*!32312 IF NOT EXISTS*/`school-db` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `school-db`;
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2020 */
/*==============================================================*/
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS scores;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS teachers;
/*==============================================================*/
/* Table: courses */
/*==============================================================*/
CREATE TABLE courses
(
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL,
PRIMARY KEY (cno)
);
/*==============================================================*/
/* Table: scores */
/*==============================================================*/
CREATE TABLE scores
(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno)
);
/*==============================================================*/
/* Table: students */
/*==============================================================*/
CREATE TABLE students
(
sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5),
PRIMARY KEY (sno)
);
/*==============================================================*/
/* Table: teachers */
/*==============================================================*/
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),
PRIMARY KEY (tno)
);
ALTER TABLE courses ADD CONSTRAINT FK_Reference_3 FOREIGN KEY (tno)
REFERENCES teachers (tno) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE scores ADD CONSTRAINT FK_Reference_1 FOREIGN KEY (sno)
REFERENCES students (sno) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE scores ADD CONSTRAINT FK_Reference_2 FOREIGN KEY (cno)
REFERENCES courses (cno) ON DELETE RESTRICT ON UPDATE RESTRICT;
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
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 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','助教','电子工程系');
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 ('6-106' ,'概率论' ,831);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);
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);
2. 表数据
2.1 courses表
2.2 scores表
2.3 students表
2.4 teachers表
3. 基础题(30)
3.1、查询students表的所有记录
SELECT * FROM students;
3.2、查询students表中的所有记录的sname、ssex和class列
SELECT sname,ssex,class FROM students;
3.3、查询teacher表所有的单位即不重复的depart列
SELECT DISTINCT depart FROM teachers;
3.4、查询scores表中成绩在60到80之间的所有记录
SELECT * FROM scores WHERE degree BETWEEN 60 AND 80;
3.5、查询scores表中成绩为85,86或88的记录
SELECT * FROM scores WHERE degree IN(85,86,88);
3.6、查询students表中“95031”班或性别为“女”的同学记录
SELECT * FROM students WHERE class='95031' OR ssex='女';
3.7、以班级class降序查询students表的所有记录
SELECT * FROM students ORDER BY class DESC;
3.8、以cno升序、degree降序查询scores表的所有记录
SELECT * FROM scores ORDER BY cno,degree DESC;
3.9、查询“95031”班的学生人数
SELECT COUNT(*) FROM students WHERE class='95031';
3.10、查询每个班的学生人数
SELECT class,COUNT(*) FROM students GROUP BY class;
3.11、查询scores表中的最高分的学生学号和课程号
(1)
SELECT sno,cno FROM scores ORDER BY degree DESC LIMIT 1;
(2)
SELECT sno,cno FROM scores WHERE degree=(
SELECT MAX(degree) FROM scores WHERE degree);
3.12、查询‘3-105’号课程的平均分
SELECT AVG(degree) FROM scores WHERE cno='3-105';
3.13、查询最高分
SELECT MAX(degree) FROM scores WHERE degree;
3.14、查询最低分
SELECT MIN(degree) FROM scores WHERE degree;
3.15、查询最低分大于70,最高分小于90的sno列
SELECT sno FROM scores
GROUP BY sno
HAVING MIN(degree)>70 AND MAX(degree)<90;
3.16、查询95033班和95031班全体学生的记录
SELECT * FROM students WHERE class='95033' OR class='95031';
3.17、查询存在有85分以上成绩的课程cno
SELECT cno,degree FROM scores WHERE degree>85;
3.18、查询所有教师和同学的NAME、sex和birthday
SELECT tname,tsex,tbirthday FROM teachers
UNION
SELECT sname,ssex,sbirthday FROM students;
3.19、查询所有“女”教师和“女”同学的NAME、sex和birthday
SELECT tname AS NAME,tsex AS sex,tbirthday AS birthday FROM teachers WHERE tsex='女'
UNION
SELECT sname AS NAME,ssex AS sex,sbirthday AS birthday FROM students WHERE ssex='女';
3.20、查询所有任课教师的tname和depart
SELECT tname,depart FROM teachers WHERE tno IN(
SELECT tno FROM courses);
3.21、查询所有未讲课的教师的tname和depart
SELECT tname,depart FROM teachers WHERE tno NOT IN(
SELECT tno FROM courses);
3.22、查询至少有2名男生的班号
SELECT class,COUNT(*) FROM students WHERE ssex='男'
GROUP BY class HAVING COUNT(*)>=2;
3.23、查询students表中姓“王”的同学记录
SELECT * FROM students WHERE sname LIKE '王%';
3.24、查询students表中不姓“王”的同学记录
SELECT * FROM students WHERE sname NOT LIKE '王%';
3.25、查询students表同名的同学记录
SELECT DISTINCT sname FROM students WHERE sname IN
(SELECT sname FROM students GROUP BY sname
HAVING COUNT(sname)>1);
3.26、查询students表中每个学生的姓名和年龄
SELECT sname,YEAR(NOW())-YEAR(sbirthday) AS sage FROM students;
3.27、查询students表中最大和最小的sbirthday日期值
SELECT MIN(sbirthday),MAX(sbirthday) FROM students;
3.28、以班号和年龄从大到小的顺序查询student表中的全部记录
SELECT * FROM students ORDER BY class DESC,sbirthday ASC;
3.29、查询“男”教师及其所上的课程
SELECT teachers.tname,courses.cname
FROM teachers INNER JOIN courses
ON(teachers.tno=courses.tno)
WHERE teachers.tsex='男';
3.30、查询课程对应的老师姓名、职称、所属系
SELECT
c.cno,c.cname,t.tno,t.tname,t.prof,t.depart
FROM(SELECT * FROM teachers) t
LEFT JOIN(SELECT * FROM courses) c
ON c.tno=t.tno;
3.31、查询每门课最高分同学的sno、cno和degree列
(1)
SELECT * FROM scores WHERE degree IN
(
SELECT MAX(degree) FROM scores GROUP BY cno
HAVING MAX(degree)
);
(2)
SELECT s.sno,s.cno,s.degree
FROM
(SELECT sno,cno,degree FROM scores) s
RIGHT JOIN
(SELECT cno,MAX(degree) dmax FROM scores
GROUP BY cno) d
ON s.cno=d.cno AND s.degree=d.dmax;
4. 提高题(20)
4.1、查询scores表中至少有5名学生选修的并以3开头的课程的平均分数
SELECT cno,AVG(degree)
FROM scores
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(sno) >= 5;
4.2、查询所有学生的sname、cno和degree列
(1)
SELECT sname,cno,degree
FROM students INNER JOIN scores
ON(students.sno=scores.sno)
ORDER BY sname;
(2)
SELECT
stu.sname,sc.cno,sc.degree
FROM
(SELECT sno,sname FROM students) stu
LEFT JOIN
(SELECT sno,cno,degree FROM scores) sc
ON stu.sno = sc.sno
ORDER BY stu.sname;
4.3、查询所有学生的sno、cname和degree列
SELECT sno,cname,degree
FROM scores
INNER JOIN courses ON scores.cno = courses.cno
ORDER BY sno;
4.4、查询所有学生的sname、cname和degree列
SELECT sname,cname,degree
FROM students
INNER JOIN scores ON students.sno=scores.sno
INNER JOIN courses ON scores.cno=courses.cno
ORDER BY sname;
4.5、创建等级grade表,现查询所有同学的sno、cno和rank级别列
CREATE TABLE grade(low TINYINT,upp TINYINT,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');
SELECT sno,cno,degree,rank
FROM scores INNER JOIN grade
ON scores.degree>=grade.low AND scores.degree<=grade.upp
ORDER BY sno;
4.6、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
SELECT s1.sno,s1.degree
FROM scores s1 LEFT JOIN scores s2
ON s1.cno=s2.cno AND s1.degree>s2.degree
WHERE s1.cno='3-105' AND s2.sno='109'
ORDER BY s1.sno;
4.7、查询scores中选学一门以上课程的同学中分数为非最高分成绩的记录
SELECT sno,degree FROM scores
GROUP BY sno
HAVING COUNT(cno)>1 AND degree!=MAX(degree);
4.8、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
SELECT s1.sno,s1.degree
FROM scores AS s1 INNER JOIN scores AS s2
ON(s1.cno=s2.cno AND s1.degree>s2.degree)
WHERE s1.cno='3-105' AND s2.sno='109'
ORDER BY s1.sno;
4.9、查询和学号为108的同学同年出生的所有学生的sno、sname和sbirthday列
SELECT s1.sno,s1.sname,s1.sbirthday
FROM students s1 INNER JOIN students s2
ON YEAR(s1.sbirthday)=YEAR(s2.sbirthday)
WHERE s2.sno='108';
4.10、查询“95033”班所选课程的平均分*
SELECT cname,AVG(degree),ROUND(AVG(degree),2)
FROM students
INNER JOIN scores ON students.sno=scores.sno
INNER JOIN courses ON scores.cno=courses.cno
WHERE class='95033'
GROUP BY courses.cno
ORDER BY cname;
4.11、查询“张旭“教师任课的学生成绩*
SELECT sno,degree
FROM scores INNER JOIN courses
ON(scores.cno=courses.cno) INNER JOIN teachers
ON(courses.tno=teachers.tno)
WHERE teachers.tname='张旭';
4.12、查询选修某课程的同学人数多于5人的教师姓名
SELECT DISTINCT tname
FROM scores INNER JOIN courses
ON(scores.cno=courses.cno) INNER JOIN teachers
ON(courses.tno=teachers.tno)
WHERE courses.cno IN(SELECT cno FROM scores GROUP BY(cno) HAVING COUNT(sno)>5);
4.13、查询出“计算机系“教师所教课程的成绩表
SELECT tname,cname,sname,degree
FROM teachers INNER JOIN courses
ON(teachers.tno=courses.tno) INNER JOIN scores
ON(courses.cno=scores.cno) INNER JOIN students
ON(scores.sno=students.sno)
WHERE teachers.depart='计算机系'
ORDER BY tname,cname,degree DESC;
4.14、查询“计算机系”与“电子工程系“不同职称的教师的tname和prof
SELECT tname,prof
FROM teachers
WHERE depart='计算机系' AND prof NOT IN(
SELECT DISTINCT prof
FROM teachers
WHERE depart='电子工程系');