SQL文テスト

5490 ワード

Student(S#,Sname,Sage,Ssex)学生表S#:学号;Name:学生の名前;Age:学生の年齢;Sex:学生の性別
Course(C#,Cname,T#)カリキュラムC#,カリキュラム番号;Name:コース名;T#:教師番号
SC(S#,C#,score)成績表S#:学号;C#、カリキュラム番号;スコア:成績
Teacher(T#,Tname)教師表T#:教師番号;Name:教師の名前
1、「001」課程が「002」課程より成績が高いすべての学生の学号を照会する.
2、すべての授業を勉強していない学生の学号、名前を調べる.3、各科の成績が最も高い点と最も低い点を検索する:以下の形式で表示する:課程ID、最も高い点、最低点4、検索と「1002」号の学生が学ぶ課程は完全に同じ他の学生の学号と名前である;5、各科の成績上位3名の学生を調べる
一、select SC 1.`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グループ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/* 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', ' ');