準備工作

CREATE DATABASE STUDENTS;

CREATE TABLE STUDENT(
SNO VARCHAR(32) PRIMARY KEY NOT NULL,
SNAME VARCHAR(32) NOT NULL,
SSEX VARCHAR(32) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(20)
);
CREATE TABLE COURSE(
CNO VARCHAR(20) PRIMARY KEY NOT NULL,
CNAME VARCHAR(20) NOT NULL,
TNO VARCHAR(20),
FOREIGN KEY(TNO) REFERENCES TEACHER(TNO)
);
CREATE TABLE SCORE(
SNO VARCHAR(20) NOT NULL,
CNO VARCHAR(20) NOT NULL,
DEGREE DECIMAL(4,1),
FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),
FOREIGN KEY(CNO) REFERENCES COURSE(CNO)
);
CREATE TABLE TEACHER(
TNO VARCHAR(20) PRIMARY KEY NOT NULL,
TNAME VARCHAR(20) NOT NULL,
TSEX VARCHAR(20) NOT NULL,
TBIRTHDAY DATETIME,
PROF VARCHAR(20),
DEPART VARCHAR(20)
);
INSERT INTO STUDENT VALUE('108','曾華','男','1977-09-01','95033');
INSERT INTO STUDENT VALUE('105','匡明','男','1975-10-02','95031');
INSERT INTO STUDENT VALUE('107','王麗','女','1976-01-23','95033');
INSERT INTO STUDENT VALUE('101','李軍','男','1976-02-20','95033');
INSERT INTO STUDENT VALUE('109','王芳','女','1975-02-10','95031');
INSERT INTO STUDENT VALUE('103','陸軍','男','1974-06-03','95031');

INSERT INTO COURSE VALUE('3-105','計算機導論','825');
INSERT INTO COURSE VALUE('3-245','操作系統','804');
INSERT INTO COURSE VALUE('6-166','數字電路','856');
INSERT INTO COURSE VALUE('9-888','高等數學','831');

INSERT INTO SCORE VALUE('103','3-245','86');
INSERT INTO SCORE VALUE('105','3-245','75');
INSERT INTO SCORE VALUE('109','3-245','68');
INSERT INTO SCORE VALUE('103','3-105','92');
INSERT INTO SCORE VALUE('105','3-105','88');
INSERT INTO SCORE VALUE('109','3-105','76');
INSERT INTO SCORE VALUE('101','3-105','64');
INSERT INTO SCORE VALUE('107','3-105','91');
INSERT INTO SCORE VALUE('108','3-105','78');
INSERT INTO SCORE VALUE('101','6-166','85');
INSERT INTO SCORE VALUE('107','6-166','79');
INSERT INTO SCORE VALUE('108','6-166','81');

INSERT INTO TEACHER VALUE('804','李斌','男','1958-12-02','副教授','計算機系');
INSERT INTO TEACHER VALUE('856','張旭','男','1969-03-12','講師','電子工程系');
INSERT INTO TEACHER VALUE('825','王萍','女','1972-05-05','助教','計算機系');

INSERT INTO TEACHER VALUE('831','劉冰','女','1977-08-14','助教','電子工程系');

#1、查詢Student表中的所有記錄的Sname、Ssex和Class列。

SELECT SNAME,SSEX,CLASS FROM STUDENT;

#2、查詢教師所有的單位即不重復的Depart列。

SELECT DISTINCT PROF FROM TEACHER;

#3、查詢Student表的所有記錄。

SELECT * FROM STUDENT;

#4、查詢Score表中成績在60到80之間的所有記錄。
SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 80;

SELECT * FROM SCORE WHERE DEGREE>=60 AND DEGREE<=80;

#5、查詢Score表中成績為85，86或88的記錄。
SELECT * FROM SCORE WHERE DEGREE IN(85,86,88);
SELECT * FROM SCORE WHERE DEGREE=85 OR DEGREE=86 OR DEGREE=88;

SELECT * FROM SCORE WHERE DEGREE=85 || DEGREE=86 || DEGREE=88;

#6、查詢Student表中“95031”班或性別為“女”的同學記錄。
SELECT * FROM STUDENT WHERE CLASS='95031' OR SSEX='女';

SELECT * FROM STUDENT WHERE CLASS='95031' || SSEX='女';

#7、以Class降序查詢Student表的所有記錄。

SELECT * FROM STUDENT ORDER BY CLASS DESC;

#8、以Cno升序、Degree降序查詢Score表的所有記錄。

SELECT * FROM SCORE ORDER BY CNO ASC,DEGREE DESC;

#9、查詢“95031”班的學生人數。

SELECT COUNT(*) 95031班學生人數 FROM STUDENT WHERE CLASS='95031';

#10、查詢Score表中的最高分的學生學號和課程號。（子查詢或者排序）

SELECT SNO,CNO FROM SCORE WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);

#11、查詢每門課的平均成績。

SELECT AVG(DEGREE),CNO FROM SCORE GROUP BY CNO;

#12、查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數。

SELECT AVG(DEGREE) FROM SCORE GROUP BY CNO HAVING COUNT(CNO)>5 AND CNO LIKE '3%';

#13、查詢分數大于70，小于90的Sno列。(注意此處是大于70，小于90，所以不能使用between...and...因為between 70 and 90是大于等于70小于等于90)
#SELECT SNO FROM SCORE WHERE DEGREE BETWEEN 70 AND 90;
SELECT SNO FROM SCORE WHERE DEGREE>70 AND DEGREE<90;

SELECT SNO FROM SCORE WHERE DEGREE>70 && DEGREE<90;?

#14、查詢所有學生的Sname、Cno和Degree列。
SELECT STU.SNAME,S.CNO,S.DEGREE FROM STUDENT STU,SCORE S WHERE STU.SNO=S.SNO;

SELECT STU.SNAME,S.CNO,S.DEGREE FROM STUDENT STU INNER JOIN SCORE S ON STU.SNO=S.SNO;

#15、查詢所有學生的Sno、Cname和Degree列。
SELECT S.SNO,C.CNAME,S.DEGREE FROM SCORE S,COURSE C WHERE S.CNO=C.CNO;

SELECT S.SNO,C.CNAME,S.DEGREE FROM SCORE S INNER JOIN COURSE C ON S.CNO=C.CNO;

#16、查詢所有學生的Sname、Cname和Degree列。
SELECT STU.SNAME,C.CNAME,S.DEGREE FROM STUDENT STU,COURSE C,SCORE S WHERE STU.SNO=S.SNO AND S.CNO=C.CNO;

SELECT STU.SNAME,C.CNAME,S.DEGREE FROM STUDENT STU INNER JOIN SCORE S ON STU.SNO=S.SNO INNER JOIN COURSE C ON S.CNO=C.CNO;

#17、查詢“95033”班學生的平均分。
SELECT AVG(DEGREE) FROM STUDENT STU,SCORE S WHERE STU.SNO=S.SNO AND STU.CLASS='95033';

SELECT AVG(DEGREE) FROM STUDENT STU INNER JOIN SCORE S ON STU.SNO=S.SNO AND STU.CLASS='95033';

#18、假設使用如下命令建立了一個grade表：
CREATE TABLE grade(low INT(3),upp INT(3),rank CHAR(1))
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'C');
INSERT INTO grade VALUES(60,69,'D');

INSERT INTO grade VALUES(0,59,'E');

#現查詢所有同學的Sno、Cno和rank列。
SELECT SNO,CNO,RANK FROM SCORE,GRADE WHERE SCORE.DEGREE BETWEEN GRADE.LOW AND GRADE.UPP;

SELECT sno,cno,rank FROM score JOIN grade ON score.Degree BETWEEN grade.low AND grade.upp

#19、查詢選修“3-105”課程的成績高于“109”號同學成績的所有同學的記錄。
SELECT *?
FROM STUDENT S,(#查詢成績表課程是3-105并且成績大于109號同學，并返回符合條件的所有數據
SELECT *?
FROM SCORE?
WHERE CNO='3-105' AND DEGREE>( ?#查詢成績表學號是109課程是3-105的成績并返回該學生的成績
SELECT DEGREE?
FROM SCORE?
WHERE SNO='109' AND CNO='3-105'
)
) NEWTABLE #將返回的數據作為另一張表，并給表取一張別名
WHERE S.SNO=NEWTABLE.SNO;#查詢學生表和‘NEWTABLE’表相等學號的信息

#代碼相同

SELECT * FROM STUDENT S,(SELECT * FROM SCORE WHERE CNO='3-105' AND DEGREE>(SELECT DEGREE FROM SCORE WHERE SNO='109' AND CNO='3-105')) NEWTABLE WHERE S.SNO=NEWTABLE.SNO;

#20、查詢score中選學多門課程的同學中分數為非最高分成績的記錄。

SELECT * FROM SCORE S WHERE S.SNO IN(SELECT SNO FROM SCORE GROUP BY SNO HAVING COUNT(SNO)>1) AND S.DEGREE<(SELECT MAX(DEGREE) FROM SCORE SS WHERE S.CNO=SS.CNO);

#21、查詢成績高于學號為“109”、課程號為“3-105”的成績的所有記錄。

SELECT * FROM SCORE WHERE CNO='3-105' AND DEGREE>(SELECT DEGREE FROM SCORE WHERE SNO='109' AND CNO='3-105');

#22、查詢和學號為107的同學同年出生的所有學生的Sno、Sname和Sbirthday列。

SELECT SNO,SNAME,SBIRTHDAY FROM STUDENT WHERE YEAR(SBIRTHDAY)=(SELECT YEAR(SBIRTHDAY) FROM STUDENT WHERE SNO='107');

#23、查詢“張旭“教師任課的學生成績。
SELECT DEGREE FROM SCORE S WHERE S.CNO IN(SELECT CNO FROM COURSE C,TEACHER T WHERE C.TNO=T.TNO AND T.TNAME='張旭');

SELECT DEGREE FROM SCORE S INNER JOIN COURSE C ON S.CNO=C.CNO INNER JOIN TEACHER T ON T.TNO=C.TNO AND T.TNAME='張旭';

#24、查詢選修某課程的同學人數多于5人的教師姓名。

SELECT T.TNAME FROM TEACHER T WHERE T.TNO=(SELECT C.TNO FROM COURSE C WHERE C.CNO=(SELECT S.CNO FROM SCORE S GROUP BY S.CNO HAVING COUNT(SNO)>5));

#25、查詢95033班和95031班全體學生的記錄。
SELECT * FROM STUDENT WHERE CLASS IN('95033','95031');
SELECT * FROM STUDENT WHERE CLASS='95031' OR CLASS='95033';

SELECT * FROM STUDENT WHERE CLASS='95031' || CLASS='95033';

#26、查詢存在有85分以上成績的課程Cno

SELECT DISTINCT CNO FROM SCORE S WHERE S.DEGREE>'85'

#27、查詢出“計算機系“副教授所教課程的成績表。

SELECT S.DEGREE FROM SCORE S WHERE S.CNO=(SELECT C.CNO FROM COURSE C WHERE C.TNO=(SELECT T.TNO FROM TEACHER T WHERE T.DEPART='計算機系' AND T.PROF='副教授'));

#28、查詢“計算機系”與“電子工程系“不同職稱的教師的Tname和Prof。

SELECT Tname,Prof FROM teacher WHERE prof NOT IN (SELECT prof FROM teacher WHERE depart = '計算機系'AND prof IN (SELECT prof FROM teacher WHERE depart ='電子工程系'));

#29、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學的Cno、Sno和Degree,并按Degree從高到低次序排序。

SELECT S.CNO,S.SNO,S.DEGREE FROM SCORE S WHERE S.CNO='3-105' AND S.DEGREE>(SELECT MAX(S.DEGREE) FROM SCORE S WHERE S.CNO='3-245') ORDER BY S.DEGREE;

#30、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學的Cno、Sno和Degree.

SELECT S.CNO,S.SNO,S.DEGREE FROM SCORE S WHERE S.CNO='3-105' AND S.DEGREE>(SELECT MAX(DEGREE) FROM SCORE WHERE CNO='3-245');

#31、查詢所有教師和同學的name、sex和birthday

SELECT S.SNAME 姓名,S.SSEX 性別,S.SBIRTHDAY 生日 FROM STUDENT S UNION SELECT T.TNAME,T.TSEX,T.TBIRTHDAY FROM TEACHER T;

#32、查詢所有“女”教師和“女”同學的name、sex和birthday.

SELECT S.SNAME 姓名,S.SSEX 性別,S.SBIRTHDAY 生日 FROM STUDENT S WHERE S.SSEX='女' UNION SELECT T.TNAME,T.TSEX,T.TBIRTHDAY FROM TEACHER T WHERE T.TSEX='女';

#33、查詢成績比該課程平均成績低的同學的成績表。

SELECT CNO,DEGREE FROM SCORE S WHERE DEGREE<(SELECT AVG(DEGREE) FROM SCORE SS WHERE S.CNO=SS.CNO);

#34、查詢所有任課教師的Tname和Depart.

SELECT T.TNAME,T.DEPART FROM TEACHER T WHERE T.TNO IN (SELECT C.TNO FROM COURSE C WHERE C.CNO IN (SELECT CNO FROM SCORE GROUP BY CNO));

#35 、查詢所有未講課的教師的Tname和Depart.

SELECT TNAME,DEPART FROM TEACHER WHERE TNO NOT IN(SELECT C.TNO FROM COURSE C WHERE C.CNO IN (SELECT S.CNO FROM SCORE S GROUP BY S.CNO));

#36、查詢至少有2名男生的班號。

SELECT CLASS FROM STUDENT WHERE SSEX='男' GROUP BY CLASS HAVING COUNT(*)>=2;

#37、查詢Student表中不姓“王”的同學記錄。

SELECT * FROM STUDENT WHERE SNAME NOT LIKE '王%';

#38、查詢Student表中每個學生的姓名和年齡。

SELECT SNAME,YEAR(GETDATE())-YEAR(SBIRTHDAY) AS '年齡' FROM STUDENT;

#39、查詢Student表中最大和最小的Sbirthday日期值。

SELECT MAX(SBIRTHDAY),MIN(SBIRTHDAY) FROM STUDENT;

#40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。

SELECT * FROM STUDENT ORDER BY CLASS DESC,SBIRTHDAY ASC;

#41、查詢“男”教師及其所上的課程。

SELECT DISTINCT TNAME,COURSE.CNAME FROM TEACHER INNER JOIN COURSE ON TEACHER.TNO=COURSE.TNO AND TEACHER.TSEX='男';

#42、查詢最高分同學的Sno、Cno和Degree列。
SELECT SNO,CNO,DEGREE FROM SCORE WHERE DEGREE IN(SELECT MAX(DEGREE) FROM SCORE);

SELECT SNO,CNO,DEGREE FROM SCORE WHERE DEGREE =(SELECT MAX(DEGREE) FROM SCORE);

#43、查詢和“李軍”同性別的所有同學的Sname.
SELECT SNAME FROM STUDENT WHERE SSEX IN (SELECT SSEX FROM STUDENT WHERE SNAME='李軍');

SELECT SNAME FROM STUDENT WHERE SSEX = (SELECT SSEX FROM STUDENT WHERE SNAME='李軍');

#44、查詢和“李軍”同性別并同班的同學Sname.
SELECT SNAME FROM STUDENT WHERE SSEX IN (SELECT SSEX FROM STUDENT WHERE SNAME='李軍') AND CLASS IN (SELECT CLASS FROM STUDENT WHERE SNAME='李軍') AND SNAME NOT IN('李軍');

SELECT SNAME FROM STUDENT WHERE SSEX = (SELECT SSEX FROM STUDENT WHERE SNAME='李軍') AND CLASS = (SELECT CLASS FROM STUDENT WHERE SNAME='李軍') AND SNAME <> ('李軍');

#45、查詢所有選修“計算機導論”課程的“男”同學的成績表。
SELECT STU.SNAME,STU.SSEX,S.DEGREE
FROM STUDENT STU
INNER JOIN SCORE S ON STU.SNO=S.SNO
INNER JOIN COURSE C ON S.CNO=C.CNO
AND C.CNAME='計算機導論' AND STU.SSEX='男';

微信公眾號，感謝關注。持續更新....