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)
-- 插入數(shù)據(jù)
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' ,'計(jì)算機(jī)導(dǎo)論',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系統(tǒng)' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'數(shù)據(jù)電路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等數(shù)學(xué)' ,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','副教授','計(jì)算機(jī)系');
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','助教','計(jì)算機(jī)系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (831,'劉冰','女','1977-08-14','助教','電子工程系');
-- 作業(yè)題目
-- 1语盈、 查詢 Student 表中的所有記錄的 Sname绝编、Ssex 和 Class 列。
SELECT sname,ssex,class from student;
-- 2淹禾、 查詢教師所有的單位即不重復(fù)的 Depart 列搔驼。
SELECT DISTINCT depart from teacher;
-- 3捆憎、 查詢 Student 表的所有記錄书闸。
SELECT * from student;
-- 4啡捶、 查詢 Score 表中成績(jī)?cè)?60 到 80 之間的所有記錄。
SELECT * from score where degree BETWEEN 60 and 80;
-- 5两嘴、 查詢 Score 表中成績(jī)?yōu)?85丛楚，86 或 88 的記錄。
SELECT * from score where degree in('85','86','88');
-- 6憔辫、 查詢 Student 表中“95031”班或性別為“女”的同學(xué)記錄趣些。
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”班的學(xué)生人數(shù)。
select count(sno) from student where class='95031';
-- 10杠园、查詢 Score 表中的最高分的學(xué)生學(xué)號(hào)和課程號(hào)顾瞪。
SELECT sno,cno from score WHERE degree=(SELECT max(degree) from score)
-- 11、查詢?3-105?號(hào)課程的平均分。
-- SELECT degree from score where cno='3-105'
SELECT avg(degree) from score where cno='3-105'
-- 12陈醒、查詢 Score 表中至少有 5 名學(xué)生選修的并以 3 開頭的課程的平均分?jǐn)?shù)惕橙。
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、查詢所有學(xué)生的 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、查詢所有學(xué)生的 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、查詢所有學(xué)生的 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'))
