很強的Sql語句測試題，能獨立做出來你的Sql就出師了

iSQlServer發表於2009-10-22
SQL

二維關係表 T(F1,F2,F3,F4,F5,F6,F7) 表示如下關係:

┌─────┬─────┬─────┬─────┬─────┬─────┬─────┐

│ 學生ID │ 學生姓名 │ 課程ID │ 課程名   │   成績   │ 教師ID │ 教師姓名 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S3    │   王五   │    K4    │   政治   │    53    │    T4    │ 楊老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S1    │   張三   │    K1    │   數學   │    61    │    T1    │ 劉老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S2    │   李四   │    K3    │   英語   │    88    │    T3    │ 李老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S1    │   張三   │    K4    │   政治   │    77    │    T4    │ 楊老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S2    │   李四   │    K4    │   政治   │    67    │    T5    │ 周老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S3    │   王五   │    K2    │   語文   │    90    │    T2    │ 王老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S3    │   王五   │    K1    │   數學   │    55    │    T1    │ 劉老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S1    │   張三   │    K2    │   語文   │    81    │    T2    │ 王老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S4    │   馬六   │    K2    │   語文   │    59    │    T1    │ 劉老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S1    │   張三   │    K3    │   英語   │    37    │    T3    │ 李老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│    S2    │   李四   │    K1    │   數學   │    81    │    T1    │ 劉老師 │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│   ....   │          │          │          │          │          │          │

├─────┼─────┼─────┼─────┼─────┼─────┼─────┤

│   ....   │          │          │          │          │          │          │

└─────┴─────┴─────┴─────┴─────┴─────┴─────┘

 

以一句 T-SQL 作答!

1.如果 T 表有一欄位 F0 資料型別為自動增量整型(唯一,不重複),

 而且 T 表中含有除 F0 欄位外,請刪除其它欄位完全相同的重複多餘的資料(要保留其中的一條):

 

2.查詢打列印各科成績最高和最低的記錄: (就是各門課程的最高、最低分的學生和老師)

 課程ID,課程名稱,最高分,學生ID,學生姓名,教師ID,教師姓名,最低分,學生ID,學生姓名,教師ID,教師姓名

 

3.按成績從高到低順序,查詢列印所有學生四門(數學、語文、英語、政治)課程成績: (就是每個學生的四門課程的成績)

 學生ID,學生姓名,數學,語文,英語,政治,有效課程數,有效平均分

 (注: 有效課程即在 T 表中有改學生的成績計入,如不明白可不列印"有效課程數"和"有效平均分")

 

4.查詢列印數學成績第十名到第十五名學生成績單

 或列印平均成績第十名到第十五名的學生成績單

 [學生ID],[學生姓名],數學,語文,英語,政治,平均成績

 

5.按各科不及格率的百分數低到高和平均成績從高到低順序,查詢各科平均成績和不及格率的百分?(用"N行"表示): (就是分析哪門課程難)

 課程ID,課程名,平均成績,及格百分數

 

6.查詢列印四門課程平均成績和及格率的百分數(用"1行4列"表示): (就是分析哪門課程難)

 數學平均分,數學及格百分數,語文平均分,語文及格百分數,英語平均分,英語及格百分數,政治平均分,政治及格百分數?

 

7.按不同老師所教不同課程平均分從高到低,查詢列印: (就是分析哪個老師的哪門課程水平高)

 教師ID,教師姓名,課程程ID,課程名稱,平均分 (平均分按去掉一個最高分和一個最低分後取)

 

8.查詢列印各科成績,各分數段人數:

 課程程ID,課程名,[100-85],[85-70],[70-60],[<60]

9.查詢列印學生平均成績及其名次

10.查詢列印各科成績前三名的記錄:(不考慮成績並列情況)

 學生ID,學生姓名,科程ID,科程名,成績,教師ID,教師姓名

11.查詢列印參加了所有考試科目的學生

 

12.查詢列印"至少"與學生"S3 (王五)"參加了相同考試科目的學生

 

--建立表的指令碼

CREATE TABLE T (SSNO INT IDENTITY (1001,1),StudentID VARCHAR(20),StudentName VARCHAR(20),

CourseID VARCHAR(20),CourseName VARCHAR(20),Result int,TeacherID VARCHAR(20),TeacherName VARCHAR(20))

 

INSERT INTO T (StudentID,StudentName,CourseID,CourseName,Result,TeacherID,TeacherName)

SELECT 'S1','Pitt','K1','Math',62,'T1','Mr.Liu'

UNION

SELECT 'S1','Pitt','K2','Chinese',81,'T2','Mr.Wang'

UNION

SELECT 'S1','Pitt','K3','English',37,'T3','Mr.Lee'

UNION

SELECT 'S1','Pitt','K4','Politics',53,'T4','Mr.Yang'

UNION

SELECT 'S2','Smith','K1','Math',65,'T1','Mr.Liu'

UNION

SELECT 'S2','Smith','K2','Chinese',89,'T2','Mr.Wang'

UNION

SELECT 'S2','Smith','K3','English',50,'T3','Mr.Lee'

UNION

SELECT 'S2','Gates','K4','Politics',60,'T4','Mr.Yang'

UNION

SELECT 'S3','Gates','K1','Math',78,'T1','Mr.Wang'

UNION

SELECT 'S3','Gates','K2','Chinese',98,'T2','Mr.Wang'

UNION

SELECT 'S3','Gates','K3','English',71,'T3','Mr.Lee'

UNION

SELECT 'S3','Gates','K4','Politics',45,'T4','Mr.Liu'

UNION

SELECT 'S4','Tom','K1','Math',95,'T1','Mr.Wang'

UNION

SELECT 'S4','Tom','K2','Chinese',84,'T2','Mr.Wang'

UNION

SELECT 'S5','Bill','K1','Math',95,'T1','Mr.Wang'

UNION

SELECT 'S5','Bill','K3','English',97,'T3','Mr.Lee'

UNION

SELECT 'S5','Bill','K4','Politics',77,'T4','Mr.Liu'

UNION

SELECT 'S6','Tim','K1','Math',93,'T1','Mr.Wang'

UNION

SELECT 'S6','Tim','K3','English',45,'T3','Mr.Lee'

UNION

SELECT 'S6','Tim','K4','Politics',53,'T4','Mr.Liu'

UNION

SELECT 'S7','YangXin','K1','Math',61,'T1','Mr.Wang'

UNION

SELECT 'S7','YangXin','K2','Chinese',81,'T2','Mr.Wang'

UNION

SELECT 'S7','YangXin','K4','Politics',53,'T4','Mr.Liu'

UNION

SELECT 'S8','Blade','K2','Chinese',81,'T2','Mr.Wang'

UNION

SELECT 'S8','Blade','K4','Politics',53,'T4','Mr.Liu'

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-617135/，如需轉載，請註明出處，否則將追究法律責任。

相關文章