MySQL进阶（一）SQL基础练习

本文适合哪些人群

致力于将SQL作为自己的技术特长的人
想在日常工作中和面试中突出自己SQL能力的人
SQL建表语句

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', '助教', '电子工程系');

SQL实战

-- 1、查询Score表中的最高分的学生学号和课程号。
select
  sno,
  CNO
from SCORE
where DEGREE = (
  select max(DEGREE)
  from SCORE
);
-- 2、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG( DEGREE),CNO
FROM score
WHERE CNO LIKE '3%'
GROUP BY CNO
HAVING COUNT(*) >5;

-- 3、查询所有学生的Sname、Cname和Degree列。
SELECT
  A.SNAME,
  B.CNAME,
  C.DEGREE
FROM STUDENT A
  JOIN (COURSE B, SCORE C)
    ON A.SNO = C.SNO AND B.CNO = C.CNO;

SELECT a.SNAME,b.CNAME,c.DEGREE FROM 
student a LEFT JOIN course b LEFT JOIN score c
WHERE a.SNO=c.SNO AND b.CNO=c.CNO;

-- 4、查询“95033”班所选课程的平均分。
SELECT AVG( DEGREE)
FROM score
WHERE sno in（select sno from  student WHERE class='95033'）

-- 5、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * FROM score
WHERE CNO="3-105" AND DEGREE > ALL
(SELECT DEGREE FROM score WHERE SNO='109')

-- 6、查询教师所有的单位即不重复的Depart列。
select distinct DEPART
from TEACHER;

-- 7、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录
SELECT * FROM score
WHERE DEGREE < (
SELECT MAX(DEGREE) FROM score
)
GROUP BY SNO
HAVING COUNT(*) >1;

-- 8、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT SNO,SNAME,SBIRTHDAY
FROM student
WHERE year(SBIRTHDAY)=(
SELECT year(SBIRTHDAY) 
   FROM student 
   WHERE SNO='108'
);

-- 9、查询“张旭“教师任课的学生成绩。
SELECT * FROM score
WHERE CNO=
(SELECT CNO FROM course,teacher
  WHERE course.TNO=teacher.TNO AND teacher.TNAME='张旭'
);

-- 10、查询存在有85分以上成绩的课程Cno
SELECT CNO FROM score
GROUP BY CNO 
HAVING MAX(DEGREE) >85;

-- 11、查询出“计算机系“教师所教课程的成绩表。
SELECT * FROM score
WHERE CNO IN(SELECT CNO FROM teacher,course
WHERE DEPART='计算机系' and course.TNO=teacher.TNO);
)

-- 12、查询所有教师和同学的name、sex和birthday.
select
  TNAME     name,
  TSEX      sex,
  TBIRTHDAY birthday
from TEACHER
union
select
  sname     name,
  SSEX      sex,
  SBIRTHDAY birthday
from STUDENT;

-- 13、查询所有“女”教师和“女”同学的name、sex和birthday.
select
  TNAME     name,
  TSEX      sex,
  TBIRTHDAY birthday
from TEACHER
where TSEX = '女'
union
select
  sname     name,
  SSEX      sex,
  SBIRTHDAY birthday
from STUDENT
where SSEX = '女';

-- 14、查询所有任课教师的Tname和Depart.
SELECT TNAME,Depart
FROM teacher a
WHERE  EXISTS (
SELECT * FROM teacher,course b
    WHERE a.TNO=b.TNO
)

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

-- 16、查询Student表中每个学生的姓名和年龄。
select
  SNAME,
  (year(now()) - year(SBIRTHDAY)) age
from STUDENT;

-- 17、查询Student表中最大和最小的Sbirthday日期值。
select MAX(SBIRTHDAY) FROM student
UNION 
SELECT MIN(SBIRTHDAY) FROM student;

-- 18、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * 
FROM student
ORDER BY CLASS desc,year(NOW())-year(SBIRTHDAY) DESC;

-- 19、查询最高分同学的Sno、Cno和Degree列。
SELECT Sno,Cno,DEGREE
FROM score WHERE DEGREE=(SELECT MAX(DEGREE) FROM score);

-- 20、查询和“李军”同性别并同班的同学Sname.
SELECT Sname 
FROM student 
WHERE (SSEX,CLASS) =(SELECT SSEX,CLASS FROM student

                        WHERE SNAME='李军');

-- 21、查询所有选修“计算机导论”课程的“男”同学的成绩表
select * from score,student where score.sno=student.sno and SSEX='男' 
and CNO=(SELECT CNO from  course where CNAME='计算机导论');

-- 22、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof
SELECT tname,PROF
FROM teacher
WHERE DEPART='计算机系' AND prof not in(
  SELECT PROF
   FROM teacher
  WHERE DEPART='电子工程系'
)

-- 23、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT Cno,SNO,DEGREE
FROM score
WHERE CNO='3-105' AND DEGREE >ANY (
  SELECT DEGREE
   FROM score
   WHERE CNO='3-245'
)
ORDER BY DEGREE DESC;

-- 24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT TNAME
FROM teacher
WHERE TNO=(
   SELECT TNO
   FROM course
   WHERE CNO=(
     SELECT CNO
     FROM score
     GROUP BY CNO 
     HAVING COUNT(SNO) >5)
)
)

-- 25、查询成绩比该课程平均成绩低的同学的成绩表(自关联)。
SELECT A.*
FROM SCORE A
WHERE DEGREE < (SELECT AVG(DEGREE)
                FROM SCORE B
                WHERE A.CNO = B.CNO);