SQL题

创建数据库 exec
CREATE DATABASE exec;

创建学生表
CREATE TABLE student(
sno VARCHAR(3),
sname VARCHAR(10) NOT NULL,
ssex VARCHAR(3),
sbrithday DATETIME,
class VARCHAR(5) NOT NULL,
PRIMARY KEY(sno))
ENGINE=INNODB DEFAULT CHARSET=utf8;

创建课程表
CREATE TABLE course(
cno VARCHAR(10),
cname VARCHAR(30) NOT NULL,
tno VARCHAR(10) NOT NULL,
PRIMARY KEY(cno))
ENGINE=INNODB DEFAULT CHARSET=utf8;

创建成绩表
CREATE TABLE grade(
sno VARCHAR(3),
cno VARCHAR(5),
degree TINYINT NOT NULL,
PRIMARY KEY(sno, cno))
ENGINE=INNODB DEFAULT CHARSET=utf8;

创建教师表
CREATE TABLE teacher(
tno VARCHAR(5),
tname VARCHAR(20) NOT NULL,
tsex VARCHAR(2),
tbrithday DATETIME,
prof VARCHAR(20) NOT NULL,
depart VARCHAR(30) NOT NULL,
PRIMARY KEY(tno))
ENGINE=INNODB DEFAULT CHARSET=utf8;

创建等级表
CREATE TABLE rank(
down TINYINT NOT NULL,
up TINYINT NOT NULL,
rank VARCHAR(2),
PRIMARY KEY(rank))
ENGINE=INNODB DEFAULT CHARSET=utf8;

插入学生信息
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 GRADE(SNO,CNO,DEGREE)VALUES ('103','3-245',86);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

插入等级信息
INSERT INTO rank(down,up,rank) VALUES(90,100,'A');
INSERT INTO rank(down,up,rank) VALUES(80,89,'B');
INSERT INTO rank(down,up,rank) VALUES(70,79,'C');
INSERT INTO rank(down,up,rank) VALUES(60,69,'D');
INSERT INTO rank(down,up,rank) VALUES(0,59,'E');

插入课程信息
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');

1、 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname, ssex, class FROM student;

2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart FROM teacher;

3、 查询Student表的所有记录。
SELECT * FROM student;

4、 查询Grade表中成绩在60到80之间的所有记录。
SELECT * FROM grade WHERE degree BETWEEN 60 AND 80;

5、 查询Grade表中成绩为85，86或88的记录。
SELECT * FROM grade WHERE degree IN (85, 86, 88);

6、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student WHERE class='95031' AND ssex='女';

7、 以Class降序查询Student表的所有记录。
SELECT * FROM student ORDER BY class DESC;

8、 以Cno升序、Degree降序查询Grade表的所有记录。
SELECT * FROM grade ORDER BY cno, degree DESC;

9、 查询“95031”班的学生人数。
SELECT COUNT(class) FROM student WHERE class='95031';

10、查询Grade表中的最高分的学生学号和课程号。
SELECT sno, cno FROM grade ORDER BY degree LIMIT 1;

11、查询‘3-105’号课程的平均分。
SELECT AVG(degree) FROM grade WHERE cno='3-105';

12、查询Grade表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(degree), COUNT(*) AS c FROM grade WHERE cno LIKE '3%' GROUP BY cno HAVING c>=5;

13、查询最低分大于70，最高分小于90的Sno列。
SELECT sno FROM grade GROUP BY sno HAVING MIN(degree)>70 AND MAX(degree)<90;

14、查询所有学生的Sname、Cno和Degree列。
SELECT student.sname, grade.cno, grade.degree FROM student JOIN grade ON student.sno=grade.sno;

15、查询所有学生的Sno、Cname和Degree列。
SELECT student.sno,course.cname,grade.degree FROM student JOIN grade ON student.sno=grade.sno JOIN course ON grade.cno=course.cno;

16、查询所有学生的Sname、Cname和Degree列。
SELECT student.sname, course.cname, grade.degree FROM student JOIN grade ON student.sno=grade.sno JOIN course ON grade.cno=course.cno;

17、查询“95033”班所选课程的平均分。
SELECT AVG(degree) FROM grade JOIN student ON grade.sno=student.sno WHERE class='95033';

18、查询选修课成绩为A等的学生信息
SELECT * FROM student JOIN grade ON student.sno=grade.sno GROUP BY degree HAVING MIN(degree)>=90 AND MAX(degree)<=100;

19、在grade表中查询选修“3-105”课程的成绩高于选修“3-105”并且学号为“109”的所有同学的记录。
SELECT * FROM student JOIN grade ON student.sno=grade.sno WHERE grade.degree>(SELECT degree FROM grade WHERE grade.sno='109' AND grade.cno='3-105') AND grade.cno='3-105';