資料庫sql面試題
① 資料庫常見筆試面試題
資料庫常見筆試面試題
資料庫常見筆試面試題有哪些?資料庫常見筆試面試會考什麼?下面是資料庫常見面試題總結，為大家提供參考。
1、sql的表連接方式有哪些?
SQL中連接按結果集分為：內連接，外連接，交叉連接
內連接：inner join on，兩表都滿足的組合。內連接分為等值連接，不等連接，自然連接。
等值連接：兩表中相同的列都會出現在結果集中。
自然連接：兩表中具體相同列表的列會合並為同一列出現在結果集中。
外連接：分為左(外)連接，右(外)連接，全連接
左(外)連接：A left (outer) join B,以A表為基礎，A表的全部數據，B表有的組合，沒有的為null。
右(外)連接：A right(outer) join B,以B表為基礎，B表的全部數據，A表有的組合，沒有的位null。
全連接：A full (outer) join 兩表相同的組合在一起，A表有，B表沒有的數據(顯示為null)，同樣B表有，A表沒有的顯示為null。
交叉連接：cross join,就是笛卡爾乘積。
2、三範式
1NF:表中的欄位都是單一屬性，不再可分。
2NF：在1NF的基礎上，表中所有的非主屬性都必須完全依賴於任意一組候選鍵，不能僅依賴於候選鍵中的某個屬性。
3NF：在2NF的基礎上，表中所有的屬性都不依賴其他非主屬性。
簡單的說就是：1NF表示每個屬性不可分割，2NF表示非主屬性不存在對主鍵的部分依賴，3NF表示不存在非主屬性對主鍵的依賴傳遞。
3、表的操作
表的創建：create table 表名 (列名1 類型 約束，列2 類型 約束…)
表的刪除： 表名
表的更改(結構的更改，不是記錄的更新)：alter table 表名 add|drop 列名|約束名
插入記錄： into 表名…values…
更新記錄：表名 set 列名=值 where 條件
刪除記錄： from 表名 where 條件
4、數據的完整性
數據完整性指的是存儲在資料庫中的數據的一致性和准確性。
完整性分類：
(1)實體完整性：主鍵值必須唯一且非空。(主鍵約束)
(2) 引用完整性(也叫參照完整性)：外鍵要麼為空，要麼引用主表中存在的記錄。(外鍵約束)。
(3)用戶自定義完整性：針對某一具體關系資料庫中的約束條件。
5、SQL的查詢優化
(1)從表連接的角度優化：盡量使用內連接，因為內連接是兩表都滿足的行的組合，而外連接是以其中一個表的全部為基準。
(2)盡量使用存儲過程代替臨時寫SQL語句：因為存儲過程是預先編譯好的SQL語句的集合，這樣可以減少編譯時間。
(3)從索引的角度優化：對那些常用的查詢欄位簡歷索引，這樣查詢時值進行索引掃描，不讀取數據塊。
(4)還有一些常用的select優化技巧：
(5)A.只查詢那些需要訪問的欄位，來代替select*
B、將過濾記錄越多的where語句向前移：在一個SQL語句中，如果一個where條件過濾的資料庫記錄越多，定位越准確，則該where條件越應該前移。
6、索引的作用，聚集索引與非聚集索引的區別
索引是一個資料庫對象，使用索引，可以是資料庫程序無須對整個數據進行掃描，就可以在其中找到目標數據，從而提高查找效率。索引的底層採用的是B樹。
聚集索引：根據記錄的key再表中排序數據行。
非聚集索引：獨立於記錄的結構，非聚集所以包含的`key，且每個鍵值項都有指向該簡直的數據行的指針。
聚集索引與非聚集索引的區別：
(1)聚集索引的物理存儲按索引排序，非聚集所以的物理存儲不按索引排序。
(2) 聚集索引插入，更新數據的速度比非聚集索引慢，單查詢速度更快。
(3) 聚集索引的葉級結點保存的是時間的數據項，而非聚集結點的葉級結點保存的是指向數據項的指針。
(4)一個表只能有一個聚集索引(因為只有一種排序方式)，但可以有多個非聚集索引。
7、存儲過程與函數的區別
(1)函數有返回值，存儲過程沒有返回值。
(2) 因為存儲過程沒有返回值，所以不能將存儲過程的執行結果賦值給變數;函數有返回值類型，調用函數時，可以將函數的執行結果賦值給變數。也就是說，函數可以在select語句中使用，而存儲過程則不能。
② SQL資料庫面試題 急急急
a)select pname as '商品名',avg(qty) as 平均銷售量 from s,p,m where m.city='上海' and s.mno=m.mno and p.pno=s.pno，select p.Pno,p.pname,sum(s.qty)
from s left join p on s.pno=p.pno left join m on p.Mno=m.Mno
where m.city='上海市'
group by p.Pno,p.pname,p.city,p.color
b)、先刪除Sale表的外鍵PNO，再刪除gds表。
c)聯系：視圖（view）是在基本表之上建立的表，它的結構（即所定義的列）和內容（即所有數據行）都來自基本表，它依據基本表存在而存在。一個視圖可以對應一個基本表，也可以對應多個基本表。視圖是基本表的抽象和在邏輯意義上建立的新關系
區別：1、視圖是已經編譯好的sql語句。而表不是
2、視圖沒有實際的物理記錄。而表有。
3、表是內容，視圖是窗口
4、表只用物理空間而視圖不佔用物理空間，視圖只是邏輯概念的存在，表可以及時四對它進行修改，但視圖只能有創建的語句來修改
5、表是內模式，視圖是外模式
6、視圖是查看數據表的一種方法，可以查詢數據表中某些欄位構成的數據，只是一些SQL語句的集合。從安全的角度說，視圖可以不給用戶接觸數據表，從而不知道表結構。
7、表屬於全局模式中的表，是實表；視圖屬於局部模式的表，是虛表。
8、視圖的建立和刪除隻影響視圖本身，不影響對應的基本表。
③ sql面試題
1.
select
s.title,
count(p.id)
from
書表
s
left
join
評論
p
on
s.id=p.書表中的id
group
by
s.title
(注意:左外連接的作用是將評價數為0的書顯示出來.count(p.id)和count(*)的區別是count(p.id)不計入p.id為null的行)
2.
select
top
1
s.title,
count(p.id)
from
書表
s
left
join
評論
p
on
s.id=p.書表中的id
group
by
s.title
order
by
2
desc
(以第2列倒序排序,取第1行)
④ 一道關於sql的面試題
（1）可以，因為ProctName 比不是主鍵，也未設置為非空 update 產品表 set ProctName=null where ProctName=Pname2
（2）
update 產品表
set Price=200
from 銷售計劃表， 產品表
where 產品表.ProctId=銷售計劃表.ProctId and Customer=m and ProctName=Pname2
（3）
select 銷售計劃表. *，SalePrice=Price*Quantity
from 銷售計劃表， 產品表
where 產品表.ProctId=銷售計劃表.ProctId
(4)
select SalePrice=Price*Quantity
from 銷售計劃表， 產品表
where 產品表.ProctId=銷售計劃表.ProctId and Quantity=(
select max(Quantity)
from 銷售計劃表
)
請採納答案，支持我一下。
⑤ 資料庫經典筆試題和面試題答案
如下這些有關資料庫知識考查的經典筆試題，非常全面，對計算機專業畢業生參加筆試會很有幫助，建議大家收藏。
一、選擇題
1. 下面敘述正確的是___c___。
A、演算法的執行效率與數據的存儲結構無關
B、演算法的空間復雜度是指演算法程序中指令(或語句)的條數
C、演算法的有窮性是指演算法必須能在執行有限個步驟之後終止
D、以上三種描述都不對
2. 以下數據結構中不屬於線性數據結構的是___c___。
A、隊列B、線性表C、二叉樹D、棧
3. 在一棵二叉樹上第5層的結點數最多是__b____。2的(5-1)次方
A、8 B、16 C、32 D、15
4. 下面描述中，符合結構化程序設計風格的是___a___。
A、使用順序、選擇和重復(循環)三種基本控制結構表示程序的控制邏輯
B、模塊只有一個入口，可以有多個出口
C、注重提高程序的執行效率 D、不使用goto語句
5. 下面概念中，不屬於面向對象方法的是___d___。
A、對象 B、繼承 C、類 D、過程調用
6. 在結構化方法中，用數據流程圖(DFD)作為描述工具的軟體開發階段是___b___。
A、可行性分析 B、需求分析 C、詳細設計 D、程序編碼
7. 在軟體開發中，下面任務不屬於設計階段的是__d____。
A、數據結構設計 B、給出系統模塊結構 C、定義模塊演算法 D、定義需求並建立系統模型
8. 資料庫系統的核心是___b___。
A、數據模型 B、資料庫管理系統 C、軟體工具 D、資料庫
9. 下列敘述中正確的是__c____。
A、資料庫是一個獨立的系統，不需要操作系統的支持
B、資料庫設計是指設計資料庫管理系統
C、資料庫技術的根本目標是要解決數據共享的問題
D、資料庫系統中，數據的物理結構必須與邏輯結構一致
10. 下列模式中，能夠給出資料庫物理存儲結構與物理存取方法的是___a___。
A、內模式 B、外模式 C、概念模式 D、邏輯模式
11. Visual FoxPro資料庫文件是___d___。
A、存放用戶數據的文件 B、管理資料庫對象的系統文件
C、存放用戶數據和系統的文件 D、前三種說法都對
12. SQL語句中修改表結構的命令是___c___。
A、MODIFY TABLE B、MODIFY STRUCTURE
C、ALTER TABLE D、ALTER STRUCTURE
13. 如果要創建一個數據組分組報表，第一個分組表達式是"部門"，第二個分組表達式是"性別"，第三個分組表達式是"基本工資"，當前索引的索引表達式應當是__b____。
A、部門+性別+基本工資 B、部門+性別+STR(基本工資)
C、STR(基本工資)+性別+部門 D、性別+部門+STR(基本工資)
14. 把一個項目編譯成一個應用程序時，下面的敘述正確的是___a___。
A、所有的項目文件將組合為一個單一的應用程序文件
B、所有項目的包含文件將組合為一個單一的應用程序文件
C、所有項目排除的文件將組合為一個單一的應用程序文件
D、由用戶選定的項目文件將組合為一個單一的應用程序文件
15. 資料庫DB、資料庫系統DBS、資料庫管理系統DBMS三者之間的關系是_a___。
A、DBS包括DB和DBMS B、DBMS包括DB和DBS
C、DB包括DBS和DBMS D、DBS就是DB，也就是DBMS
16. 在"選項"對話框的"文件位置"選項卡中可以設置___b___。
A、表單的默認大小 B、默認目錄
C、日期和時間的顯示格式 D、程序代碼的顏色
17. 要控制兩個表中數據的完整性和一致性可以設置"參照完整性"，要求這兩個表_a_。
A、是同一個資料庫中的兩個表 B、不同資料庫中的兩個表
C、兩個自由表 D、一個是資料庫表另一個是自由表
18. 定位第一條記錄上的命令是___a___。
A、GO TOP B、GO BOTTOM C、GO 6 D、SKIP
19. 在關系模型中，實現"關系中不允許出現相同的元組"的約束是通過__b____。
A、候選鍵 B、主鍵 C、外鍵 D、超鍵
20. 設當前資料庫有10條記錄(記錄未進行任何索引)，在下列三種情況下，當前記錄號為1時;EOF()為真時;BOF()為真時，命令?RECN()的結果分別是___a___。
A、1,11,1 B、1,10,1 C、1,11,0 D、1,10,0
21. 下列表達式中結果不是日期型的是___c___。
A、CTOD("2000/10/01") B、{^99/10/01}+365
C、VAL("2000/10/01") D、DATE()
22. 只有滿足聯接條件的記錄才包含在查詢結果中，這種聯接為___c___。
A、左聯接 B、右聯接 C、內部聯接 D、完全聯接
23. 索引欄位值不唯一，應該選擇的索引類型為___b___。
A、主索引 B、普通索引 C、候選索引 D、唯一索引
24. 執行SELECT 0選擇工作區的結果是___b___。
A、選擇了0號工作區 B、選擇了空閑的最小號工作區
C、關閉選擇的工作區 D、選擇已打開的工作區
25. 從資料庫中刪除表的命令是___a___。
A、DROP TABLE B、ALTER TABLE C、DELETE TABLE D、USE
26. DELETE FROM S WHERE 年齡>60語句的功能是__b____。
A、從S表中徹底刪除年齡大於60歲的記錄
B、S表中年齡大於60歲的記錄被加上刪除標記
C、刪除S表 D、刪除S表的年齡列 1 2
⑥ 資料庫基礎篇（三）—— SQL之數據操縱、定義
1）語法
2）兩種方式區別
方式一使用最多。它支持插入多行；支持嵌入子查詢。方式二不支持。
3）特點
①欄位類型和值類型一致或兼容，而且一一對應
②可以為空的欄位，可以不用插入值，或用null填充
③不可以為空的欄位，必須插入值
④欄位個數和值的個數必須一致
⑤欄位可以省略，但默認所有欄位，並且順序和表中的存儲順序一致
1）語法
2）栗子
1）語法
2）栗子
3）兩種方式區別
① truncate不能加where條件，而delete可以加where條件
② truncate的效率高一點
③truncate 刪除帶自增長的列的表後，如果再插入數據，數據從1開；delete 刪除帶自增長列的表後，如果再插入數據，數據從上一次的斷點處開始
④truncate刪除不能回滾，delete刪除可以回滾
1）庫的管理：
2）表的管理：
注意：對於表和庫的管理，語句理解就好。工作中最方便的是直接在Navicat中直接操作。那麼，這里重要的是復製表，它的需求通常是直接復製表的結構或數據，或者部分數據及部分結構。不需要重新再創建表結構，導數據。直接用SQL，效率會很高，且不容易出錯。
上面在創建表時，涉及到數據類型。它和Python程序語言類似，也有自己的數據類型。都是在存儲數據時，要對數據類型進行限制，保證插入數據時的准確性。分類分別是數值型、字元型、日期型。圖中標記部分為常用。
上面在創建表時涉及到約束，它是工作中非常重要的。我們從一個問題場景來看，下面是一個真實的面試題。我們第一反應是"報錯！"，但是面試官想聽的是背後報錯原因及解決方案。下面，帶著問題來學習約束。
約束是一種限制，用於限製表中的數據結構，為了保證表中的數據的准確和可靠性、一致性。比如：創建用戶表時，為了保證每一個用戶唯一性，就需要進行約束。添加約束的時機分別是創建表、修改表時。SQL中有五大常用約束。如下：
①NOT NULL 非空約束，保證該欄位的值不為空。如：用戶ID
②DEFAULT 默認約束，欄位如果不插入數據也有默認值。如：性別
③UNIQUE 唯一約束，可以為空。如：商品類別
④PRIMARY KEY 主鍵，保證欄位唯一性、非空。如：ID，員工編號
⑤FOREIGN KEY 外鍵，用於限制兩個表之間的關系，用於保證該欄位的值必須來自主表的關聯列的值。如：用戶表裡有個外鍵是order_id, order_id是order的主鍵。下圖是約束在Navicat中設置的對應位置。
最後回答下上面的問題：原因是int類型的id欄位最大數據量支持到4294967295，如果超過則會報錯。解決方案：①檢查id欄位是否是主鍵②將id的類型轉換為 bigint。
好，今天學習到這里。本節內容相對上一節要簡單些。雖然簡單，但每個知識點都是工作中常用的。這篇文章主要是SQL的增刪改和表和庫的管理。明天繼續學習SQL的事務及視圖。一起加油！
⑦ SQL查詢面試題與答案
SQL查詢面試題與答案
SQL語言是一種資料庫查詢和程序設計語言，用於存取數據以及查詢、更新和管理關系資料庫系統;同時也是資料庫腳本文件的擴展名。下面是我搜集的SQL查詢面試題與答案，歡迎大家閱讀。
SQL查詢面試題與答案一
1.一道SQL語句面試題，關於group by表內容：
2005-05-09 勝
2005-05-09 勝
2005-05-09 負
2005-05-09 負
2005-05-10 勝
2005-05-10 負
2005-05-10 負
如果要生成下列結果, 該如何寫sql語句?
勝 負
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','勝')
insert into #tmp values('2005-05-09','勝')
insert into #tmp values('2005-05-09','負')
insert into #tmp values('2005-05-09','負')
insert into #tmp values('2005-05-10','勝')
insert into #tmp values('2005-05-10','負')
insert into #tmp values('2005-05-10','負')
1)select rq, sum(case when shengfu='勝' then 1 else 0 end)'勝',sum(case when shengfu='負' then 1 else 0 end)'負' from #tmp group by rq
2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='勝'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='負'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 勝,b.b1 負 from
(select col001,count(col001) a1 from temp1 where col002='勝' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='負' group by col001) b
where a.col001=b.col001
2.請教一個面試中遇到的SQL語句的查詢問題
表中有A B C三列,用SQL語句實現：當A列大於B列時選擇A列否則選擇B列，當B列大於C列時選擇B列否則選擇C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.面試題：一個日期判斷的sql語句?
請取出tb_send表中日期(SendTime欄位)為當天的所有記錄?(SendTime欄位為datetime型，包含日期與時間)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一張表，裡面有3個欄位：語文，數學，英語。其中有3條記錄分別表示語文70分，數學80分，英語58分，請用一條sql語句查詢出這三條記錄並按以下條件顯示出來(並寫出您的思路)：
大於或等於80表示優秀，大於或等於60表示及格，小於60分表示不及格。
顯示格式：
語文 數學 英語
及格 優秀 不及格
------------------------------------------
select
(case when 語文>=80 then '優秀'
when 語文>=60 then '及格'
else '不及格') as 語文,
(case when 數學>=80 then '優秀'
when 數學>=60 then '及格'
else '不及格') as 數學,
(case when 英語>=80 then '優秀'
when 英語>=60 then '及格'
else '不及格') as 英語,
from table
5.在sqlserver2000中請用sql創建一張用戶臨時表和系統臨時表，裡麵包含兩個欄位ID和IDValues,類型都是int型，並解釋下兩者的區別?
------------------------------------------
用戶臨時表:create table #xx(ID int, IDValues int)
系統臨時表:create table ##xx(ID int, IDValues int)
區別:
用戶臨時表只對創建這個表的用戶的Session可見,對其他進程是不可見的.
當創建它的進程消失時這個臨時表就自動刪除.
全局臨時表對整個SQL Server實例都可見,但是所有訪問它的Session都消失的時候,它也自動刪除.
6.sqlserver2000是一種大型資料庫，他的`存儲容量只受存儲介質的限制，請問它是通過什麼方式實現這種無限容量機制的。
------------------------------------------
它的所有數據都存儲在數據文件中(*.dbf),所以只要文件夠大,SQL Server的存儲容量是可以擴大的.
SQL Server 2000 資料庫有三種類型的文件：
主要數據文件
主要數據文件是資料庫的起點，指向資料庫中文件的其它部分。每個資料庫都有一個主要數據文件。主要數據文件的推薦文件擴展名是 .mdf。
次要數據文件
次要數據文件包含除主要數據文件外的所有數據文件。有些資料庫可能沒有次要數據文件，而有些資料庫則有多個次要數據文件。次要數據文件的推薦文件擴展名是 .ndf。
日誌文件
日誌文件包含恢復資料庫所需的所有日誌信息。每個資料庫必須至少有一個日誌文件，但可以不止一個。日誌文件的推薦文件擴展名是 .ldf。
7.請用一個sql語句得出結果
從table1,table2中取出如table3所列格式數據，注意提供的數據及結果不準確，只是作為一個格式向大家請教。
如使用存儲過程也可以。
table1
月份mon 部門dep 業績yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部門dep 部門名稱dname
--------------------------------
01 國內業務一部
02 國內業務二部
03 國內業務三部
04 國際業務部
table3 (result)
部門dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
1)
select a.部門名稱dname,b.業績yj as '一月份',c.業績yj as '二月份',d.業績yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部門dep = b.部門dep and b.月份mon = '一月份' and
a.部門dep = c.部門dep and c.月份mon = '二月份' and
a.部門dep = d.部門dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep
8.華為一道面試題
一個表中的Id有多個記錄，把所有這個id的記錄查出來，並顯示共有多少條記錄數。
------------------------------------------
select id, Count(*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1
SQL查詢面試題與答案二
1、查詢不同老師所教不同課程平均分從高到低顯示
SELECT max(Z.T#) AS 教師ID,MAX(Z.Tname) AS 教師姓名,C.C# AS 課程ID,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
2、查詢如下課程成績第 3 名到第 6 名的學生成績單：企業管理(001)，馬克思(002)，UML (003)，資料庫(004)
[學生ID],[學生姓名],企業管理,馬克思,UML,資料庫,平均成績
SELECT DISTINCT top 3
SC.S# As 學生學號,
Student.Sname AS 學生姓名 ,
T1.score AS 企業管理,
T2.score AS 馬克思,
T3.score AS UML,
T4.score AS 資料庫,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 總分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = '001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = '002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = '003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = '004'
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = 'k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = 'k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = 'k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = 'k4'
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
3、統計列印各科成績,各分數段人數:課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60]
SELECT SC.C# as 課程ID, Cname as 課程名稱
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
4、查詢學生平均成績及其名次
SELECT 1+(SELECT COUNT( distinct 平均成績)
FROM (SELECT S#,AVG(score) AS 平均成績
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成績 > T2.平均成績) as 名次,
S# as 學生學號,平均成績
FROM (SELECT S#,AVG(score) 平均成績
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成績 desc;
5、查詢各科成績前三名的記錄:(不考慮成績並列情況)
SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
6、查詢每門課程被選修的學生數
select c#,count(S#) from sc group by C#;
7、查詢出只選修了一門課程的全部學生的學號和姓名
select SC.S#,Student.Sname,count(C#) AS 選課數
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名;
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2
9、查詢所有課程成績小於60分的同學的學號、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查詢沒有學全所有課的同學的學號、姓名;
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名;
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
12、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名;
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績;
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='葉平');
14、查詢和“1002”號的同學學習的課程完全相同的其他同學學號和姓名;
select S# from SC where C# in (select C# from SC where S#='1002')
group by S# having count(*)=(select count(*) from SC where S#='1002');
15、刪除學習“葉平”老師課的SC表記錄;
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平';
16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“003”課程的同學學號、2、
號課的平均成績;
Insert SC select S#,'002',(Select avg(score)
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成績從高到低顯示所有學生的“資料庫”、“企業管理”、“英語”三門的課程成績，按如下形式顯示： 學生ID,,資料庫,企業管理,英語,有效課程數,有效平均分
SELECT S# as 學生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 資料庫
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企業管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英語
,COUNT(*) AS 有效課程數, AVG(t.score) AS 平均成績
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
SELECT L.C# As 課程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#
);
19、按各科平均成績從低到高和及格率的百分數從高到低順序
SELECT t.C# AS 課程號,max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分數
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查詢如下課程平均成績和及格率的百分數(用"1行"顯示): 企業管理(001)，馬克思(002)，OO&UML (003)，資料庫(004)
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企業管理平均分
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企業管理及格百分數
,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 馬克思平均分
,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 馬克思及格百分數
,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分數
,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 資料庫平均分
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 資料庫及格百分數
FROM SC
;