七、MYSQL数据库练习题（四）

参考来源：

https://www.toutiao.com/i6676783005559685636/?tt_from=weixin&utm_campaign=client_share&wxshare_count=1&timestamp=1624424226&app=news_article&utm_source=weixin&utm_medium=toutiao_ios&use_new_style=1&req_id=2021062312570601013516823124005041&share_token=7165338E-8B04-478C-A881-FBBC77A3D429&group_id=6676783005559685636&wid=1624429497860

一、建表语句

CREATE TABLE students
(sno VARCHAR(3) NOT NULL, 
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL, 
sbirthday DATETIME,
class VARCHAR(5))
CREATE TABLE courses
(cno VARCHAR(5) NOT NULL, 
cname VARCHAR(10) NOT NULL, 
tno VARCHAR(10) NOT NULL)
CREATE TABLE scores 
(sno VARCHAR(3) NOT NULL, 
cno VARCHAR(5) NOT NULL, 
degree NUMERIC(10, 1) NOT NULL) 
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 (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 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、查询所有教师和同学的name、sex和birthday.

select tname,tsex as 'sex',tbirthday from teachers UNION(select sname,ssex,sbirthday from students);

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

select tname,tsex as 'sex',tbirthday from teachers where tsex='女' UNION(select sname,ssex,sbirthday from students where ssex='女');

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

select * from (SELECT scores.*,b.avgdegree from scores LEFT JOIN (select a.cno,AVG(a.degree) as 'avgdegree' from (select courses.cno,scores.sno,scores.degree from courses LEFT JOIN scores on scores.cno =courses.cno)as a GROUP BY a.cno)as b on b.cno=scores.cno 
)as c where c.degree < c.avgdegree;

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

select tname,depart from teachers;

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

select * from teachers LEFT JOIN courses on courses.tno = teachers.tno where courses.tno is  null;

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

select class from students  GROUP BY class HAVING COUNT(ssex) >=2;

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

select * from students where sname REGEXP '^[^王]';

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

select sname,(DATE_FORMAT(NOW(),"%Y")-DATE_FORMAT(sbirthday,"%Y")) as 'age'  from students;

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

select MAX(sbirthday) as 'maxsbirthday',MIN(sbirthday) as 'minsbirthday' from students;

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

select *,(DATE_FORMAT(NOW(),"%Y")-DATE_FORMAT(sbirthday,"%Y")) as 'age'  from students order by class DESC,age DESC;

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

select a.*,cname from courses   join (select * from teachers where tsex = '男')as a on a.tno = courses.tno;

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

SELECT * from scores where degree = (select MAX(degree) from scores);

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

select Sname from students where ssex = (select ssex from students where sname = '李军');

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

select Sname from students where ssex = (select ssex from students where sname = '李军') and class = (select class from students where sname = '李军');

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

select c.* from (select b.*,students.ssex from students  JOIN  (select scores.*,cname from scores JOIN (select * from courses where cname = '计算机导论' ) as a on a.cno = scores.cno )as b on   b.sno =students.sno) as c where c.ssex='男';