SQL语句测试

Student(S#,Sname,Sage,Ssex) 学生表       S#：学号；Name：学生姓名；Age：学生年龄；Sex：学生性别
Course(C#,Cname,T#) 课程表                    C#,课程编号；Name：课程名字；T#：教师编号
SC(S#,C#,score) 成绩表                             S#：学号；C#,课程编号；Score：成绩

Teacher(T#,Tname) 教师表                        T#：教师编号； Name：教师名字

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

2、查询没有学全所有课的同学的学号、姓名；
3、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
4、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
5、查询各科成绩前三名的学生

一、select SC1.`S#` from SC SC1 JOIN SC SC2 ON SC1.`S#`=SC2.`S#` WHERE SC1.`C#`='1' AND SC2.`C#`='2' AND SC1.Score > SC2.Score

一、select Student.`S#` from (select `s#`,score from SC where `C#`='1') Student,(select `S#`,score from SC where `C#`='2') Course WHERE Student.`S#`=Course.`S#`

二、select Student.`S#`,Student.`Name` from Student,SC where Student.`S#`=SC.`S#`  group by Student.`S#`,Student.`Name`  having count(`C#`)  < (select count(*) from Course)

三、select `C#` as '课程ID',MAX(Score) as '最高分',MIN(Score) as '最低分' from SC group by `C#`

四、select distinct SC.`S#`,Student.`Name` from SC JOIN Student ON SC.`S#`=Student.`S#` where SC.`S#` not in (select `S#` from sc where `C#` not in  (select `C#` from sc where `S#`='2')) AND SC.`S#`<>2 group by SC.`S#` having count(*) = (select count(*) from sc where `S#`='2');


五、SELECT a.`S#` '学生ID',a.`C#` '课程ID',a.Score '分数' FROM sc a WHERE (SELECT COUNT(`C#`) FROM sc WHERE `C#`=a.`C#` AND a.Score<Score)<3 ORDER BY `C#` ASC, Score DESC

/*
Navicat MySQL Data Transfer

Source Server         : windows_mysql
Source Server Version : 50547
Source Host           : 127.0.0.1:3306
Source Database       : sql

Target Server Type    : MYSQL
Target Server Version : 50547
File Encoding         : 65001

Date: 2016-06-28 21:24:49
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `C#` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `T#` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`C#`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '软件工程', '1');
INSERT INTO `course` VALUES ('2', '项目优化', '2');
INSERT INTO `course` VALUES ('3', '二次开发', '3');
INSERT INTO `course` VALUES ('4', '框架实战', '4');
INSERT INTO `course` VALUES ('5', 'PHP从入门到放弃', '5');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `S#` int(11) DEFAULT NULL,
  `C#` int(11) DEFAULT NULL,
  `Score` int(255) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '50');
INSERT INTO `sc` VALUES ('2', '1', '123');
INSERT INTO `sc` VALUES ('1', '2', '26');
INSERT INTO `sc` VALUES ('2', '2', '5');
INSERT INTO `sc` VALUES ('5', '3', '23');
INSERT INTO `sc` VALUES ('6', '3', '34');
INSERT INTO `sc` VALUES ('7', '4', '56');
INSERT INTO `sc` VALUES ('2', '4', '78');
INSERT INTO `sc` VALUES ('1', '3', '56');
INSERT INTO `sc` VALUES ('1', '4', '123');
INSERT INTO `sc` VALUES ('2', '3', '12');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `S#` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `Age` int(11) DEFAULT NULL,
  `Sex` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`S#`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '王浩', '10', '男');
INSERT INTO `student` VALUES ('2', '付兴', '11', '女');
INSERT INTO `student` VALUES ('3', '刘丹', '11', '男');
INSERT INTO `student` VALUES ('4', '赵鑫', '11', '女');
INSERT INTO `student` VALUES ('5', '高玉', '11', '男');
INSERT INTO `student` VALUES ('6', '李敏', '11', '女');
INSERT INTO `student` VALUES ('7', '马军', '11', '男');
INSERT INTO `student` VALUES ('8', '江文', '11', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `T#` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`T#`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '赵伟');
INSERT INTO `teacher` VALUES ('2', '张恩');
INSERT INTO `teacher` VALUES ('3', '李朋');
INSERT INTO `teacher` VALUES ('4', '张鹏');
INSERT INTO `teacher` VALUES ('5', '李明');