数据库

一. 3个表关联查询学生和课程信息(鸿亚面试)

学生表：

CREATE TABLE `STUDENT` (
  `SNO` int(255) DEFAULT NULL COMMENT '学号',
  `SNAME` varchar(255) DEFAULT NULL,
  `SAGE` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `STUDENT` VALUES ('1', '张三', '18');
INSERT INTO `STUDENT` VALUES ('2', '李四', '18');
INSERT INTO `STUDENT` VALUES ('3', '王五', '25');
INSERT INTO `STUDENT` VALUES ('4', '马六', '25');
INSERT INTO `STUDENT` VALUES ('5', '真七', '29');

课程表：

CREATE TABLE `COURSE` (
  `CNAME` varchar(255) DEFAULT NULL,
  `CNO` int(255) DEFAULT NULL COMMENT '课程编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `COURSE` VALUES ('数学', '1');
INSERT INTO `COURSE` VALUES ('语文', '2');
INSERT INTO `COURSE` VALUES ('英语', '3');

成绩表：

CREATE TABLE `SC` (
  `SNO` int(11) NOT NULL COMMENT '学号',
  `CNO` int(11) DEFAULT NULL COMMENT '课程编号',
  `SCORE` int(11) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `SC` VALUES ('1', '1', '85');
INSERT INTO `SC` VALUES ('1', '2', '80');
INSERT INTO `SC` VALUES ('2', '1', '76');
INSERT INTO `SC` VALUES ('2', '2', '76');

问题：

1)查询“001”课程比“002”课程成绩高的所有学生的学号； --这个是徐总答案，有点看不懂
select sno from sc t1 where score > (select score from sc t2 where t1.sno = t2.sno and t2.cno = '002') and t1.cno = '001'


1)查询“001”课程比“002”课程成绩高的所有学生的学号； 
SELECT s1.sno FROM sc s1 JOIN sc s2 ON s1.sno=s2.sno WHERE s1.cno='001' AND s2.cno='002' AND s1.score>s2.score


2) 查询平均成绩大于60分的同学的学号和平均成绩
SELECT sno,avg(score) s FROM sc GROUP BY sno HAVING  s>60


3) 查询所有同学的学号、姓名、选课数、总成绩；
SELECT stu.sno,stu.sname,count(sc.sno),sum(sc.score) FROM stu LEFT JOIN sc ON stu.sno=sc.sno GROUP BY stu.sno


4) 查询没有学全所有课程的学生姓名，学号；
SELECT stu.sno,stu.sname,count(sc.cno) c FROM stu LEFT JOIN sc ON stu.sno=sc.sno GROUP BY stu.sno HAVING c<(SELECT COUNT(cname) FROM course cc)