mysql练习题3

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之间的所有记录

SELECT * from SCORE
where degree>60 and degree<80;

2.查询 score 表中成绩为85，86或88的记录

select * from SCORE
where degree in (85,86,88);

3.以 cno 升序、degree降序查询 score 表的所有记录

select * from SCORE
order by cno asc,degree desc;

4.查询“95031”班的学生人数。

select count(*)
from STUDENT
where class=95031;

5.查询Score表中的最高分的学生学号和课程号

select sno,cno
from SCORE
where degree >= all(
select degree from score)

6.查询‘3-105’号课程的平均分。

select avg(degree) from SCORE
where cno='3-105';

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

select cno,avg(degree)
from SCORE
where cno like '3%'
GROUP BY cno
having count(sno)>=5;

8.查询最低分大于70，最高分小于90的Sno列。

select sno
from SCORE
GROUP BY sno
having min(degree)>70 and max(degree)<90;

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

select sname,cno,degree
from student left join score on STUDENT.sno=score.sno;

10.c查询所有学生的Sno、Cname和Degree列。

select STUDENT.sno,cname,degree
from STUDENT left join score on STUDENT.sno=score.SNO
left join course on course.cno=score.cno;

11.查询所有学生的Sname、Cname和Degree列。

select STUDENT.sno,cname,degree
from STUDENT left join score on STUDENT.sno=score.SNO
left join course on course.cno=score.cno;

12.查询“95033”班所选课程的平均分。

select avg(degree)
from SCORE
where sno in (
select sno
from STUDENT
where class=95033);

select sno,cno,rank
from SCORE left join grade on degree BETWEEN low and upp;

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

select * from score 
where cno = '3-105' and degree >(
select degree from score 
where sno = 109 and cno = '3-105');

****15.查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

-- 小于无论什么课程的最大值（最大值只有一个）
select * from SCORE
where sno in (
select sno 
from SCORE
where degree != (select max(degree) from score)
GROUP BY sno HAVING count(cno) >1)

--针对每个学生最大分数
select *
from score a
-- 将sno分组内连接求每组不等于最大值的，针对于学生
join (select sno,max(degree) max_degree from score GROUP BY sno) b
on a.sno=b.sno and a.DEGREE!=b.max_degree;

-- 针对于每科课程最大分数
SELECT * 
from score a
join (select cno,max(degree) as max_degree from score GROUP BY cno) b
on a.cno=b.cno and a.degree != b.max_degree;

16.查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

SELECT sno,sname,sbirthday
from STUDENT
where YEAR(sbirthday)=(
select YEAR(sbirthday)
from STUDENT
where sno=108) and sno!=108;

17.查询“张旭“教师任课的学生成绩。

select * from SCORE
where cno in (
select cno from course
where tno in (select tno from teacher
where tname='张旭'));

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

select tname from teacher 
where tno in(select tno from course
where cno in (
select cno from score 
GROUP BY cno
HAVING count(sno)>5));

19.查询95033班和95031班全体学生的记录。

select * from STUDENT
where class in ('95033','95031');

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

select cno from SCORE
where cno in (select cno from SCORE where degree>85)
GROUP BY cno;

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

select * from SCORE
where cno in(
select cno from COURSE
where tno in (
select tno from teacher 
where depart='计算机系'));

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

select tname name,tsex sex,tbirthday birthday
from TEACHER
UNION //集合函数，将教师和学生拼接起来
select sname name,ssex sex,sbirthday birthday
from student

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

--只要11对应，无论什么属性
select tname name,tsex sex,tbirthday birthday
from teacher where tsex = '女'
UNION
select sname name,ssex sex,sbirthday birthday
from student where ssex = '女';

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

SELECT * from SCORE
where degree<(
select avg(degree) from SCORE)

25.查询所有任课教师的Tname和Depart。

-- 有可能老师没任课
select tname,depart
from TEACHER
where tno in(
select distinct(tno) from COURSE);

26.查询至少有2名男生的班号。

select class from STUDENT
where ssex = '男'
GROUP BY class 
having count(*)>1;

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

select * from STUDENT
where sname not like '王%';

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

select sname,YEAR(NOW())-YEAR(sbirthday) age
from STUDENT