经典数据库sql练习

经典练习：
四张表：学生表 、老师表、课程表、成绩表

学生表 ：

CREATE TABLE `student` (
  `student_number` int(10) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
  `student_name` varchar(50) NOT NULL COMMENT '学生姓名',
  `student_age` int(10) NOT NULL COMMENT '学生年龄',
  `student_sex` varchar(10) NOT NULL COMMENT '学生性别',
  PRIMARY KEY (`student_number`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

老师表：

CREATE TABLE `teacher` (
  `teacher_number` int(10) NOT NULL COMMENT '教师编号',
  `teacher_name` varchar(10) NOT NULL COMMENT '教师姓名',
  PRIMARY KEY (`teacher_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

课程表：

CREATE TABLE `course` (
  `course_number` int(10) NOT NULL COMMENT '课程编号',
  `course_name` varchar(10) NOT NULL COMMENT '课程名称',
  `teacher_number` varchar(10) NOT NULL COMMENT '教师编号',
  PRIMARY KEY (`course_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

成绩表：

CREATE TABLE `student_score` (
  `student_number` int(10) NOT NULL COMMENT '学生学号',
  `course_number` int(10) NOT NULL COMMENT '课程编号',
  `score` int(10) NOT NULL COMMENT '成绩',
  PRIMARY KEY (`student_number`,`course_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

里面数据自己添加！

sql练习：

1、查询“1”课程比“2”课程成绩高的所有学生的学号;

select a.student_number from (select student_number,score from student_score where course_number='1') a,(select student_number,score from student_score where course_number='2') b where a.score>b.score and a.student_number=b.student_number;

2、查询平均成绩大于60分的同学的学号和平均成绩;

SELECT student_number,AVG(score) FROM student_score  GROUP BY student_number HAVING AVG(score) > 60

3、查询所有同学的学号、姓名、选课数、总成绩;

SELECT a.student_number,a.student_name,count(c.course_number),sum(score) FROM student a LEFT OUTER JOIN student_score c ON a.student_number=c.student_number GROUP BY a.student_number

4、查询姓“李”的老师的个数;

SELECT COUNT(t.teacher_name)  from teacher t where teacher_name LIKE '李%'

5、查询没学过 “张三” 老师课的同学的学号、姓名

SELECT
    a.student_number,
    a.student_name
FROM
    student a
WHERE
    a.student_number NOT IN(
        SELECT DISTINCT
            (b.student_number)
        FROM
            student_score b,
            course c,
            teacher d
        WHERE
            b.course_number = c.course_number
        AND d.teacher_number = c.teacher_number
        AND d.teacher_name = '张三'
    )

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;