create DATABASE System;
use system;
-- 建表
CREATE TABLE STUDENT(
SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL
,SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))
CREATE TABLE COURSE(
CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)
CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL,
TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL,
PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL)
-- 插入数据
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 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);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
-- 作业题目
-- 1、 查询 Student 表中的所有记录的 Sname、Ssex 和 Class 列。
SELECT sname,ssex,class from student;
-- 2、 查询教师所有的单位即不重复的 Depart 列。
SELECT DISTINCT depart from teacher;
-- 3、 查询 Student 表的所有记录。
SELECT * from student;
-- 4、 查询 Score 表中成绩在 60 到 80 之间的所有记录。
SELECT * from score where degree BETWEEN 60 and 80;
-- 5、 查询 Score 表中成绩为 85，86 或 88 的记录。
SELECT * from score where degree in('85','86','88');
-- 6、 查询 Student 表中“95031”班或性别为“女”的同学记录。
select * from student where class in('95031') or ssex in('女')
-- 7、 以 Class 降序查询 Student 表的所有记录。
SELECT * from student ORDER BY class desc
-- 8、 以 Cno 升序、Degree 降序查询 Score 表的所有记录。
select * from score ORDER BY cno,degree desc
-- 9、 查询“95031”班的学生人数。
select count(sno) from student where class='95031';
-- 10、查询 Score 表中的最高分的学生学号和课程号。
SELECT sno,cno from score WHERE degree=(SELECT max(degree) from score)
-- 11、查询„3-105‟号课程的平均分。
-- SELECT degree from score where cno='3-105'
SELECT avg(degree) from score where cno='3-105'
-- 12、查询 Score 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数。
select cno,avg(degree) from score group by cno HAVING cno like '3%' and count(*)>=5;
-- 13、查询最低分大于 70，最高分小于 90 的 Sno 列。
SELECT sno,min(degree),max(degree) FROM score GROUP BY sno HAVING MIN(Degree)>70 AND MAX(Degree)<90
-- 14、查询所有学生的 Sname、Cno 和 Degree 列。
SELECT A.sname,B.cno,B.degree from student A INNER JOIN score B on A.sno=B.sno INNER JOIN course C on B.cno=C.cno
-- 15、查询所有学生的 Sno、Cname 和 Degree 列。
SELECT B.sno,C.cname,B.degree from student A INNER JOIN score B on A.sno=B.sno INNER JOIN course C on B.cno=C.cno
-- 16、查询所有学生的 Sname、Cname 和 Degree 列。
SELECT A.sname,C.cname,B.degree from student A INNER JOIN score B on A.sno=B.sno INNER JOIN course C on B.cno=C.cno
-- 17、查询“95033”班所选课程的平均分。
SELECT avg(degree) from score where sno in(SELECT sno from student where class in('95033'))
