[轉] 經典SQL練習題

標籤：

原題目來自qaz13177_58_CSDN部落格 http://blog.csdn.net/qaz13177_58_/article/details/5575711/#sql

只是更新個人答案供參考

 

表結構：
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL, 
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL, 
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))
go
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL, 
CNAME VARCHAR(10) NOT NULL, 
TNO VARCHAR(10) NOT NULL)
go
CREATE TABLE SCORE 
(SNO VARCHAR(3) NOT NULL, 
CNO VARCHAR(5) NOT NULL, 
DEGREE NUMERIC(10, 1) NOT NULL) 
go
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);
GO
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);
GO
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);
GO
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列。
2、 查詢教師所有的單位即不重複的Depart列。
3、 查詢Student表的所有記錄。
4、 查詢Score表中成績在60到80之間的所有記錄。
5、 查詢Score表中成績為85，86或88的記錄。
6、 查詢Student表中“95031”班或性別為“女”的同學記錄。
7、 以Class降序查詢Student表的所有記錄。
8、 以Cno升序、Degree降序查詢Score表的所有記錄。
9、 查詢“95031”班的學生人數。
10、查詢Score表中的最高分的學生學號和課程號。
11、查詢‘3-105’號課程的平均分。
12、查詢Score表中至少有5名學生選修的並以3開頭的課程的平均分數。
13、查詢最低分大於70，最高分小於90的Sno列。
14、查詢所有學生的Sname、Cno和Degree列。
15、查詢所有學生的Sno、Cname和Degree列。
16、查詢所有學生的Sname、Cname和Degree列。
17、查詢“95033”班所選課程的平均分。
18、假設使用如下命令建立了一個grade表：
create table grade(low   number(3,0),upp   number(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’);
commit;
現查詢所有同學的Sno、Cno和rank列。
19、查詢選修“3-105”課程的成績高於“109”號同學成績的所有同學的記錄。
20、查詢score中選學一門以上課程的同學中分數為非最高分成績的記錄。
21、查詢成績高於學號為“109”、課程號為“3-105”的成績的所有記錄。
22、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。
23、查詢“張旭“教師任課的學產生績。
24、查詢選修某課程的同學人數多於5人的教師姓名。
25、查詢95033班和95031班全體學生的記錄。
26、查詢存在有85分以上成績的課程Cno.
27、查詢出“電腦系“教師所教課程的成績表。
28、查詢“電腦系”與“電子工程系“不同職稱的教師的Tname和Prof。
29、查詢選修編號為“3-105“課程且成績至少高於選修編號為“3-245”的同學的Cno、Sno和Degree,並按Degree從高到低次序排序。
30、查詢選修編號為“3-105”且成績高於選修編號為“3-245”課程的同學的Cno、Sno和Degree.
31、查詢所有教師和同學的name、sex和birthday.
32、查詢所有“女”教師和“女”同學的name、sex和birthday.
33、查詢成績比該課程平均成績低的同學的成績表。
34、查詢所有任課教師的Tname和Depart.
35  查詢所有未講課的教師的Tname和Depart. 
36、查詢至少有2名男生的班號。
37、查詢Student表中不姓“王”的同學記錄。
38、查詢Student表中每個學生的姓名和年齡。
39、查詢Student表中最大和最小的Sbirthday日期值。
40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。
41、查詢“男”教師及其所上的課程。
42、查詢最高分同學的Sno、Cno和Degree列。
43、查詢和“李軍”同性別的所有同學的Sname.
44、查詢和“李軍”同性別並同班的同學Sname.
45、查詢所有選修“電腦導論”課程的“男”同學的成績表
個人答案(沒有驗證,有誤請指出,僅為複習文法,不考慮效能以及合理性)：

SELECT * FROM dbo.COURSESELECT * FROM dbo.SCORESELECT * FROM dbo.STUDENTSELECT * FROM dbo.TEACHERSELECT * FROM grade--答案主要使用飛相互關聯的子查詢完成(因為效率比較高),相互關聯的子查詢多用JOIN語句代替,但也有寫到--相互關聯的子查詢的地方,僅只是為了鍛煉更多種寫法1.SELECT SNAME,SSEX,CLASS FROM STUDENT
2.SELECT COUNT(*) AS 老師數量,DEPART FROM TEACHER GROUP BY DEPART3.SELECT * FROM STUDENT4.SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 805.SELECT * FROM SCORE WHERE DEGREE IN (85,86,88)6.SELECT * FROM STUDENT WHERE SSEX=‘女‘ OR CLASS=‘95031‘7.SELECT * FROM STUDENT ORDER BY CLASS DESC8.SELECT * FROM SCORE ORDER BY CNO ASC,DEGREE DESC9.SELECT COUNT(*) AS 人數 FROM STUDENT WHERE CLASS=‘95031‘10.SELECT SNO,CNO FROM SCORE WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE)SELECT TOP 1 SNO,CNO FROM SCORE ORDER BY DEGREE DESC11.SELECT CONVERT(DECIMAL(10,2),AVG(DEGREE)) FROM SCORE WHERE CNO=‘3-105‘/12.WITH T_C AS(SELECT CNO FROM SCORE WHERE CNO LIKE ‘3%‘ GROUP BY CNO HAVING COUNT(CNO)>5)SELECT AVG(DEGREE) AS ‘五名以上學生選修並且3開頭的所有課程的所有得分平均數‘ FROM SCORE WHERE CNO IN (SELECT T_C.CNO FROM T_C)13.SELECT SNO FROM SCORE GROUP BY SNO HAVING MIN(DEGREE)>70 AND MAX(DEGREE)<90 ORDER BY SNO14.SELECT *,(SELECT SNAME FROM STUDENT S WHERE S.SNO=SCORE.SNO) AS 學生姓名 FROM SCORE ORDER BY SNO15.SELECT *,(SELECT CNAME FROM COURSE S WHERE S.CNO=SCORE.CNO) AS 課程 FROM SCORE ORDER BY SNO16.SELECT DEGREE AS 成績,(SELECT SNAME FROM STUDENT S WHERE S.SNO=SCORE.SNO) AS 學生姓名,(SELECT CNAME FROM COURSE T WHERE T.CNO=SCORE.CNO) AS 課程  FROM SCORE ORDER BY 學生姓名17.SELECT AVG(DEGREE) FROM SCORE WHERE SNO IN (SELECT SNO FROM STUDENT WHERE CLASS=‘95033‘)18.SELECT SNO,CNO,(SELECT RANK FROM grade G WHERE DEGREE BETWEEN G.low AND G.upp) AS RANK FROM SCORE19.SELECT * FROM SCORE WHERE CNO=‘3-105‘ AND DEGREE>(SELECT TOP 1 DEGREE FROM SCORE WHERE CNO=‘3-105‘ AND SNO=109)20.SELECT * FROM SCORE WHERE SNO IN(SELECT SNO FROM SCORE GROUP BY SNO HAVING COUNT(SNO)>1) AND DEGREE<>(SELECT MAX(DEGREE) FROM SCORE) ORDER BY SNO21.SELECT * FROM SCORE WHERE CNO=‘3-105‘ AND DEGREE>(SELECT TOP 1 DEGREE FROM SCORE WHERE CNO=‘3-105‘ AND SNO=109)22.SELECT SNO,SNAME,SBIRTHDAY FROM STUDENT WHERE DATEPART(YYYY,SBIRTHDAY)=(SELECT TOP 1 DATEPART(YYYY,SBIRTHDAY) FROM STUDENT WHERE SNO=108)23.SELECT * FROM SCORE WHERE CNO=(SELECT CNO FROM COURSE WHERE TNO=(SELECT TOP 1 TNO FROM TEACHER WHERE TNAME=‘張旭‘))24.
WITH TEMP AS(  SELECT CNO,COUNT(CNO) AS StuCount FROM SCORE GROUP BY CNO HAVING COUNT(CNO)>5)SELECT TNAME AS 教師姓名,C.CNAME AS 課程,TEMP.StuCount AS 報修人數FROM TEACHER T LEFT JOIN COURSE C ON T.TNO=C.TNO INNER JOIN TEMP ON C.CNO=TEMP.CNO25.SELECT * FROM STUDENT WHERE CLASS IN (‘95033‘,‘95031‘)26.SELECT DISTINCT CNO FROM SCORE WHERE DEGREE>8527.SELECT * FROM SCORE S WHERE EXISTS((SELECT CNO FROM COURSE WHERE TNO IN (SELECT TNO FROM TEACHER WHERE DEPART=‘電腦系‘) AND CNO=S.CNO)) --僅做鍛煉使用exists關鍵字 exists 與 in 的區別在於                  --in先執行內層 exists先執行外層                  --所以根據父子查詢的資料量 兩個關鍵字會有不同的效能高低                   --因此不能準確說明 in 與 ex 的效率高低28.
SELECT * FROM TEACHER WHERE PROF NOT IN( SELECT T1.PROF FROM  (SELECT PROF FROM TEACHER WHERE DEPART=‘電腦系‘) T1 INNER JOIN  (SELECT PROF FROM TEACHER WHERE DEPART=‘電子工程系‘) T2 ON T1.PROF=T2.PROF)--這題我理解是不是有問題29.SELECT * FROM SCORE WHERE CNO=‘3-105‘ AND DEGREE>(SELECT MAX(DEGREE)FROM SCORE WHERE CNO=‘3-245‘) ORDER BY DEGREE DESC30.SELECT * FROM SCORE WHERE CNO=‘3-105‘ AND DEGREE>(SELECT MAX(DEGREE)FROM SCORE WHERE CNO=‘3-245‘) 31.
SELECT ‘學生‘ AS 姓名,‘‘ AS 性別,NULL AS 生日 UNION ALLSELECT SNAME,SSEX,SBIRTHDAY FROM STUDENT UNION ALLSELECT ‘老師‘,‘‘,NULL UNION ALLSELECT TNAME,TSEX,TBIRTHDAY FROM TEACHER
32.SELECT ‘學生‘ AS 姓名,‘‘ AS 性別,NULL AS 生日 UNION ALLSELECT SNAME,SSEX,SBIRTHDAY FROM STUDENT WHERE SSEX=‘女‘ UNION ALLSELECT ‘老師‘,‘‘,NULL UNION ALLSELECT TNAME,TSEX,TBIRTHDAY FROM TEACHER WHERE TSEX=‘女‘33.SELECT * FROM SCORE T WHERE DEGREE>(SELECT AVG(DEGREE) AS AVGS FROM SCORE WHERE CNO=T.CNO GROUP BY CNO)34.SELECT TNAME,DEPART FROM TEACHER T WHERE EXISTS(SELECT * FROM COURSE WHERE TNO=T.TNO)35.SELECT TNAME,DEPART FROM TEACHER T WHERE NOT EXISTS(SELECT * FROM COURSE WHERE TNO=T.TNO)36.SELECT CLASS FROM STUDENT WHERE SSEX=‘男‘ GROUP BY CLASS HAVING COUNT(SSEX)>=237.SELECT * FROM STUDENT WHERE LTRIM(SNAME) NOT LIKE ‘王%‘38.SELECT SNAME,DATEDIFF(YEAR,SBIRTHDAY,GETDATE()) AS 年齡,SBIRTHDAY FROM STUDENT
39.SELECT ‘最大值:‘+CONVERT(NVARCHAR(200),MAX(SBIRTHDAY),23) FROM STUDENT  UNIONSELECT ‘最小值:‘+CONVERT(NVARCHAR(200),MIN(SBIRTHDAY),23) FROM STUDENT 
40.SELECT * FROM STUDENT ORDER BY CLASS,SBIRTHDAY --班級號優先SELECT * FROM STUDENT ORDER BY SBIRTHDAY,CLASS --年齡大優先41.SELECT * FROM TEACHER T INNER JOIN COURSE C ON T.TNO=C.TNO AND T.TSEX=‘男‘42.
SELECT TOP 1 * FROM SCORE ORDER BY DEGREE DESC --這是錯誤的,如果2位同學並列呢SELECT * FROM SCORE WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE )
43.DECLARE @SEX NVARCHAR(2)SET @SEX=(SELECT TOP 1 SSEX FROM STUDENT WHERE SNAME=‘李軍‘)SELECT * FROM STUDENT WHERE [email protected]SELECT * FROM STUDENT WHERE SSEX=(SELECT TOP 1 SSEX FROM STUDENT WHERE SNAME=‘李軍‘)
44.SELECT * FROM STUDENT WHERE SSEX=(SELECT TOP 1 SSEX FROM STUDENT WHERE SNAME=‘李軍‘)AND CLASS=(SELECT TOP 1 CLASS FROM STUDENT WHERE SNAME=‘李軍‘)
45.SELECT * FROM STUDENT WHERE SSEX=‘男‘AND SNO IN(SELECT DISTINCT SNO FROM SCORE WHERE CNO=(SELECT TOP 1 CNO FROM COURSE WHERE CNAME=‘電腦導論‘))

 

[轉] 經典SQL練習題