A. 求教：某銷售公司銷售管理資料庫的sql語句題（查詢業務員的銷售業績和計算提成）
1)
UPDATE 出庫單
SET 出庫單金額=新的金額
WHERE 出庫單編號=要更新的出庫單編號
2）
SELECT 客戶編號,SUM(出庫單金額) AS 總購買額 FROM 出庫單
GROUP BY 客戶編號
3)
SELECT Y.業務員編號,Y.業務員姓名,SUM(K.出庫單金額) AS 銷售業績 FROM 業務員 Y
INNER JOIN 客戶 C ON Y.業務員編號=C.所屬業務員編號
INNER JOIN 出庫單 K ON K.客戶編號=C.客戶編號
GROUP BY Y.業務員編號,Y.業務員姓名
4)
SELECT Y.業務員編號,Y.業務員姓名,(SUM(K.出庫單金額)*Y.提成比率) AS 提成獎金 FROM 業務員 Y
INNER JOIN 客戶 C ON Y.業務員編號=C.所屬業務員編號
INNER JOIN 出庫單 K ON K.客戶編號=C.客戶編號
GROUP BY Y.業務員編號,Y.業務員姓名,Y.提成比率
B. 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
;C. sql經典50題
一、查詢課程編號為「01」的課程比「02」的課程成績高的所有學生的學號（重點）
一刷：
excel思路：
觀察原始表格數據，在excel中想得到01比02高，我們需要將原始表格拆分成兩個表，課程01表和課程02表，再進行vlookup得到c表，根據if條件判斷篩出最終數據。
重點是：1.拆表 2.匹配
轉成SQL語言：
1.拆表語言：
2.關聯加匹配語言 ：
3.完整語言：
----到這里其實就可以結束了；
4.若想加student的信息，則需要以上所有結果再作為c表再關聯匹配：
若想讓欄位1和2為上下結果，即重復前面的信息，則語言如下：
二刷：
擴展一：查詢成績小於60分的學生的學號和姓名
1、先反向找出大於等於60分的學號 ：
2、匹配：
擴展二：查詢平均成績小於60分的學生的學號、姓名和平均成績
第一種
1、先找出小於60分和空的作為c表：
2、匹配：
三、查詢所有學生的學號、姓名、選課數、總成績（不重要）
四、查詢姓「李」的老師的個數（不重要）
五、查詢沒學過「張三」老師課的學生的學號、姓名（重點）
六、查詢學過「張三」老師所教的所有課的同學的學號、姓名（重點）
七、查詢學過編號為「01」的課程並且也學過編號為「02」的課程的學生的學號、姓名（重點）
八、查詢課程編號為「02」的總成績（不重點）
九、查詢成績小於60分的學生的學號和姓名（同題目二）
十、查詢沒有學全所有課的學生的學號、姓名(重點)
十一、查詢至少有一門課與學號為「01」的學生所學課程相同的學生的學號和姓名（重點）
十二、查詢和「01」號同學所學課程完全相同的其他同學的學號(重點)
十五、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績（重點）
十六、檢索"01"課程分數小於60，按分數降序排列的學生信息（和34題重復，不重點
十七、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績(重重點與35一樣)
十八、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率
-- 及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90 (超級重點)
十九、按各科成績進行排序，並顯示排名
二十、查詢學生的總成績並進行排名（不重點）
二十一、查詢不同老師所教不同課程平均分從高到低顯示(不重點)
二十二、查詢所有課程的成績第2名到第3名的學生信息及該課程成績（重要 25類似
二十三、 使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計各分數段人數：課程ID和課程名稱(重點和18題類似)
二十四、查詢學生平均成績及其名次（同19題，重點）
二十五、查詢各科成績前三名的記錄（不考慮成績並列情況）（重點 與22題類似）
二十六、查詢每門課程被選修的學生數(不重點)
二十七、查詢出只有兩門課程的全部學生的學號和姓名(不重點)
二十八、查詢男生、女生人數(不重點)
二十九、查詢名字中含有"風"字的學生信息（不重點）
三十一、 查詢1990年出生的學生名單（重點year）
三十二、查詢平均成績大於等於85的所有學生的學號、姓名和平均成績（不重要）
三十三、查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列（不重要）
三十四、查詢課程名稱為"數學"，且分數低於60的學生姓名和分數（不重點）
三十五、查詢所有學生的課程及分數情況（重點）
三十六、 查詢任何一門課程成績在70分以上的姓名、課程名稱和分數（重點）
三十七、 查詢不及格的課程並按課程號從大到小排列(不重點)
三十八、 查詢課程編號為03且課程成績在80分以上的學生的學號和姓名（不重要）
三十九、求每門課程的學生人數（不重要）
四十、查詢選修「張三」老師所授課程的學生中成績最高的學生姓名及其成績（重要top）
四十一、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績 （重點）
四十二、查詢每門功課成績最好的前兩名（同22和25題）
四十三、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列（不重要）
四十四、檢索至少選修兩門課程的學生學號（不重要）
四十五、查詢選修了全部課程的學生信息（重點劃紅線地方）
四十六、查詢各學生的年齡（精確到月份）
四十七、 查詢沒學過「張三」老師講授的任一門課程的學生姓名（還可以，自己寫的，答案中沒有）
四十八、 查詢兩門以上不及格課程的同學的學號及其平均成績
四十九、查詢本月過生日的學生（無法使用week、date(now()）
五十、 查詢下月過生日的學生
D. SQL 試題 求高手給正確答案！
題那麼多，分這么少，沒有積極性，沒人給你做啊。
我有時間再給你看看吧。
簡答讀程題
1列出Sql Server 2000資料庫中常見的資料庫對象。
表、視圖、存儲過程、觸發器、索引
2簡述INSERT觸發器的工作原理
當insert一條或多條數據的時候，即觸發該觸發器。
單選題
1、關於主鍵描述正確的是：（ c ）
A、包含一列 B、包含兩列 C、包含一列或者多列 D、以上都不正確
2、模式查找like '_a%',下面哪個結果是可能的( b )。
A、aili B、
C、bba D、cca
3、下列途徑哪個不是實現值域完整性約束的 __D___ 。
A 、rule( 規則 ) B 、default C 、not null D 、trancation
4 關於視圖和索引，下列說法那個是正確的___B__ 。
A 、視圖是虛表，觀察到的數據是實際基本表中的數據。
B 、索引查找法是比表掃描法查詢更快的一種方法。
C 、視圖是查詢數據的一種方法，只能基於基本表建立。
D 、索引的創建只和數據的存儲有關系。
5在SQL SERVER伺服器上，存儲過程是一組預先定義並（D）的Transact-SQL語句。
A、另保存 B、編寫 C、解釋說明 D、編譯
6、SQL Server 2000是一個（C）的資料庫系統。
A、層次型 B、 網狀型 C、關系型 D、組合型
7、SQL語言中，條件「年齡BETWEEN 40 AND 50」表示年齡在40至50之間，且( a )。
A、包括40歲和50歲 B、不包括40歲和50歲
C、包括40歲但不包括50歲 D、包括50歲但不包括40歲
8、面對索引的相關描述正確的是：（B）。
A、經常被查詢的列不適合建索引。 B、 列值唯一的列適合建索引。
C、有很多重復值的列適合建索引。 D、 是外鍵或主鍵的列不適合建索引。
E. SQL經典50題題解
本篇文章主要是對SQL經典50題進行詳細解析。
解析包含：1、解題思路，2、考核知識點，3、答案；
首先，表結構用腦圖輸出出來，如下所示：
先進行數據准備，建表以及插入數據。
1.查詢"01"課程比"02"課程成績高的學生的信息及課程分數
解題思路：
第一步：關鍵詞有「課程編號」、「課程成績」、「學生信息」，鎖定使用表：學生表、成績表。
第二步：給出學生信息及課程分數，通過主鍵sid關聯學生表和課程表。
第三步：比較同一個學生不同課程的成績，再關聯一次課程表，利用sid、cid進行關聯。
第四步：根據題目，用where比較分數篩選結果。
考核知識點： join，where
答案：
1.1 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )
解題思路：
第一步：關鍵詞有「課程編號」、「學生編號」，鎖定使用表：成績表。
第二步：分別查詢出存在" 01 "課程的學生和存在" 02 "課程的學生。
第三步：對兩個子查詢進行關聯，用sid進行左聯接。
考核知識點： where, 子查詢，left join
答案：
1.2 查詢同時存在01和02課程的情況
解題思路： 同1.1，把left join改為join
考核知識點： where, 子查詢，join
答案：
1.3 查詢選擇了02課程但沒有01課程的情況
解題思路： 類似1.1，把left join改為right join
考核知識點： where, 子查詢，right join
答案：
小結： 上面的題主要考察join、left join、right join。
2.查詢平均成績大於等於 60 分的同學的學生編號和學生姓名和平均成績
解題思路：
第一步：關鍵詞有「平均成績」、「學生編號」、「學生姓名」，鎖定使用表：成績表、學生表。
第二步：給出學生信息及課程分數，通過主鍵sid關聯學生表和課程表。。
第三步：根據題目，用group by聚合計算出平均成績，然後篩選出大於等於60分的學生。
考核知識點： join、group by、avg()
答案：
3.查詢在 SC 表存在成績的學生信息
解題思路：
第一步：關鍵詞有「SC」、「學生信息」，鎖定使用表：成績表、學生表。
第二步：用EXISTS判斷在SC表存在成績的學生信息
考核知識點： EXISTS語句
答案：
4.查詢所有同學的學生編號、學生姓名、選課總數、所有課程的成績總和
解題思路：
第一步：關鍵詞有「學生編號」、「學生姓名」、「選課總數」、「課程成績」，鎖定使用表：成績表、學生表。
第二步：通過主鍵sid關聯學生表成績表，得到學生信息、學生成績的寬表
第三步：根據題目，用group by聚合計算選課總數和總成績
考核知識點： left join、group by、count()、sum()、ifnull()
答案：
5.查詢「李」姓老師的數量
解題思路：
第一步：關鍵詞有「老師的數量」，鎖定使用表：教師表。
第二步：先篩選出「李」姓老師，再匯總統計「李」姓老師的數量
考核知識點： like、where、%、count()
答案：
6.查詢學過「張三」老師授課的同學的信息
解題思路：
第一步：關鍵詞有「老師」、「學生信息」，鎖定使用表：教師表、學生表、成績表、課程表。
第二步：通過sid關聯學生表、成績表，再通過cid關聯課程表，最後通過tid關聯教師表。
第三步：用where篩選出「張三」老師授課的同學的信息。
考核知識點： 多重連接join
答案：
7.查詢沒有學全所有課程的同學的信息。
解題思路：
第一步：關鍵詞有「課程」、「學生信息」，鎖定使用表：學生表、成績表、課程表。
第二步：先統計學生的課程數量，再篩選出小於所有課程數量的學生。
考核知識點： left join、group by、count()
答案：
8.查詢至少有一門課與學號為" 01 "的同學所學相同的同學的信息。
解題思路：
第一步：關鍵詞有「課程」、「學生信息」，鎖定使用表：學生表、成績表。
第二步：先查詢學號「01」的同學學習的課程。
第三步：通過sid關聯學生表和成績表，獲取所有學生信息、課程信息。
第四步：用EXISTS篩選出至少1門課相同的同學信息。
考核知識點： left join、子查詢、EXISTS
答案：
9.查詢和" 01 "號的同學學習的課程完全相同的其他同學的信息
解題思路：
第一步：關鍵詞有「課程」、「學生信息」，鎖定使用表：學生表、成績表。
第二步：用「01」號的同學學習的課程左關聯學生課程表，篩選出關鏈課程數一致的其他同學的sid
第三步：通過sid關聯學生表和成績表，獲取完整的學生信息。
考核知識點： left join、子查詢、group by
答案：
10.查詢沒學過"張三"老師講授的任一門課程的學生姓名
解題思路：
第一步：關鍵詞有「老師」、「學生姓名」，鎖定使用表：學生表、成績表、課程表、教師表。
第二步：反向求解。先查詢至少學過「張三」老師講授的課程的學生sid
第三步：用NOT EXISTS篩選出不在第二步查詢結果的學生信息,。
考核知識點： 多重連接join、NOT EXISTS、子查詢
答案：
11.查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
解題思路：
第一步：關鍵詞有「姓名」、「平均成績」，鎖定使用表：學生表、成績表。
第二步：通過sid關聯學生表和成績表，得到學生成績信息寬表
第三步：用group by聚合統計，having對聚合的結果進行篩選。
考核知識點： join、group by、having、case when語句、avg()
答案：
12.檢索" 01 "課程分數小於 60，按分數降序排列的學生信息
解題思路：
第一步：關鍵詞有「課程分數」、「學生信息」，鎖定使用表：學生表、成績表。
第二步：通過sid關聯學生表和成績表，得到學生成績信息寬表
第三步：用where篩選" 01 "課程分數小於 60的記錄，並按照分數降序排列。
考核知識點： join、where、order by
答案：
13.按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
解題思路：
第一步：關鍵詞有「平均成績」，鎖定使用表：成績表。
第二步：用group by將學生課程的成績由行轉換為列並計算平均成績。
第三步：按照平均成績降序顯示學生的所有課程的成績以及平均成績。
考核知識點： join
答案：
14.查詢各科成績最高分、最低分和平均分,以如下形式顯示：
以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，
優良率，優秀率
及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90
要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列
解題思路：
第一步：關鍵詞有「課程name」、「最高分」，鎖定使用表：課程表、成績表。
第二步：用group by聚合計算課程最高分、最低分、平均分。
第三步：用case語句判斷及格、中等、優良、優秀，並結合group by計算。
考核知識點： join、group by、max()、min()、avg()、sum()、case when語句
答案：
15.按各科成績進行排序，並顯示排名， Score 重復時保留名次空缺
解題思路：
第一步：關鍵詞有「各科成績」，鎖定使用表：成績表。
第二步：用rank()排名。
考核知識點： rank() over(partition by)
答案：
15.1 按各科成績進行行排序，並顯示排名， Score 重復時合並名次
解題思路：
第一步：關鍵詞有「各科成績」，鎖定使用表：成績表。
第二步：用dense_rank()排名。
考核知識點： dense_rank() over(partition by)
答案：
16.查詢學生的總成績，並進行排名，總分重復時保留名次空缺
解題思路：
第一步：關鍵詞有「總成績」，鎖定使用表：成績表。
第二步：用group by統計學生的總成績。
第三步：用left join自關聯進行排名。
考核知識點： group by、 left join
答案：
16.1 查詢學生的總成績，並進行排名，總分重復時不保留名次空缺
解題思路：
第一步：關鍵詞有「總成績」，鎖定使用表：成績表。
第二步：用group by統計學生的總成績。
第三步：用變數進行排名。
考核知識點： group by、變數
答案：
17. 統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比
解題思路：
第一步：關鍵詞有「各科成績」、「課程名稱」，鎖定使用表：成績表、課程表。
第二步：通過cid關聯課程表和成績表，得到課程、成績信息寬表。
第三步：用group by聚合統計各分段的人數及百分比。
考核知識點： join、group by、case when條件語句
答案：
18.查詢各科成績前三名的記錄
解題思路：
第一步：關鍵詞有「各科成績」，鎖定使用表：成績表。
第二步：篩選出各科比當前成績高的人數小於3的學生記為各科的前三名。
考核知識點： 子查詢
答案：
19.查詢每門課程被選修的學生數
解題思路：
第一步：關鍵詞有「每門課程」、「學生數」，鎖定使用表：課程表、成績表。
第二步：用left join關聯課程表和成績表，再用group by分組匯總各科的學生數。
考核知識點： left join、group by
答案：
20.查詢出只選修兩門課程的學生學號和姓名
解題思路：
第一步：關鍵詞有「選修課程」、「學生姓名」，鎖定使用表：學生表、成績表。
第二步：用join關聯學生表和成績表，再用group by分組匯總每個學生的選修課程數，最後用having對分組匯總結果篩選出選修兩門課程的學生。
考核知識點： join、group by、having
答案：
21. 查詢男生、女生人數
解題思路：
第一步：關鍵詞有「男生、女生」，鎖定使用表：學生表。
第二步：通過ssex學生表用group by分組匯總男生、女生人數。
考核知識點： group by
答案：
22. 查詢名字中含有「風」字的學生信息
解題思路：
第一步：關鍵詞有「學生信息」，鎖定使用表：學生表。
第二步：用like匹配姓名中含有風」字的學生。
考核知識點： like、%
答案：
23查詢同名同性學生名單，並統計同名人數
解題思路：
第一步：關鍵詞有「學生名單」，鎖定使用表：學生表。
第二步：使用group by，匯總同名同性人數，再用having篩選出大於1的記錄
考核知識點： group by、having
答案：
24.查詢 1990 年出生的學生名單
解題思路：
第一步：關鍵詞有「學生名單」，鎖定使用表：學生表。
第二步：用where篩選出1990年出生的學生名單
考核知識點： where、year
答案：
25.查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編
號升序排列。
解題思路：
第一步：關鍵詞有「平均成績」，鎖定使用表：成績表。
第二步：用group by分組計算各科平均成績，再用order by完成多列排序
考核知識點： group by、order by
答案：
26.查詢平均成績大於等於 85 的所有學生的學號、姓名和平均成績
解題思路：
第一步：關鍵詞有「平均成績」、「學生姓名」，鎖定使用表：成績表、學生表。
第二步：用join關聯學生表和成績表
第三步：用group by分組匯總計算每個學生的平均成績，再用having篩選平均成績>=85的記錄
考核知識點： join、group by、having
答案：
27.查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數
解題思路：
第一步：關鍵詞有「課程名稱」、「分數」、「學生姓名」，鎖定使用表：課程表、成績表、學生表。
第二步：用join關聯學生表、成績表、課程表，再用where篩選
考核知識點： 多重join、where
答案：
28. 查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）
解題思路：
第一步：關鍵詞有「所有學生」、「分數」，鎖定使用表：學生表、成績表
第二步：用left join關聯學生表、成績表
考核知識點： left join
答案：
29.查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數
解題思路：
第一步：關鍵詞有「課程成績」、「姓名」、「課程名稱」，鎖定使用表：學生表、成績表、課程表
第二步：用join關聯學生表、成績表、課程表，再篩選出課程成績在70分以上的。
考核知識點： 多重join
答案：
30.查詢不及格的課程
解題思路：
第一步：關鍵詞有「不及格的課程」，鎖定使用表：成績表、課程表
第二步：關聯課程表和成績表，再條件篩選出不及格的課程信息。
考核知識點： join、where、去重
答案：
31.查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名
解題思路：
第一步：關鍵詞有「課程編號」、「課程成績」、「姓名」，鎖定使用表：成績表、學生表
第二步：關聯成績表和學生表，再條件篩選出結果。
考核知識點： join、where
答案：
32.求每門課程的學生人數
解題思路：
第一步：關鍵詞有「課程」、「學生人數」，鎖定使用表：成績表
第二步：用group by分組匯總各科的學生人數。
考核知識點： group by
答案：
33.成績不重復，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
解題思路：
第一步：關鍵詞有「成績」、「「張三」老師」、「學生信息」，鎖定使用表：成績表、課程表、學生表、教師表
第二步：關聯所有表，篩選出選修「張三」老師所授課程的學生。
第三步：因為成績不重復，對學生成績由高到低排序，篩選出第一行記錄。
考核知識點： 多重join、order by、limit
答案：
34.成績有重復的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生
信息及其成績
解題思路：
第一步：關鍵詞有「成績」、「「張三」老師」、「學生信息」，鎖定使用表：成績表、課程表、學生表、教師表
第二步：關聯所有表，篩選出選修「張三」老師所授課程的學生。
第三步：因為成績有重復，先求出最高成績，再匹配最高成績對應的學生信息。
考核知識點： 多重join、max()
答案：
35.查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
解題思路：
第一步：關鍵詞有「成績」，鎖定使用表：成績表
第二步：自聯接，篩選出不同課程成績相同的記錄
考核知識點： 自聯接join
答案：
36. 查詢每門成績最好的前兩名
解題思路：
第一步：關鍵詞有「成績」，鎖定使用表：成績表
第二步：自聯接，篩選出各科低於自身成績的人數為2的。
考核知識點： left join
答案：
37. 統計每門課程的學生選修人數（超過 5 人的課程才統計）。
解題思路：
第一步：關鍵詞有「選修人數」，鎖定使用表：成績表
第二步：先用group by分組匯總各科的選修人數，再條件篩選出超過5人的課程。
考核知識點： group by、having
答案：
38.檢索至少選修兩門課程的學生學號
解題思路：
第一步：關鍵詞有「兩門課程」，鎖定使用表：成績表
第二步：先用group by分組匯總每個學生的選修課程數，再用having篩選出至少2門課程的學生學號
考核知識點： group by、having
答案：
39.查詢選修了全部課程的學生信息
解題思路：
第一步：關鍵詞有「全部課程」、「學生信息」，鎖定使用表：成績表、課程表、學生表
第二步：關聯學生表和成績表，再用group by分組統計每個學生的選修課程數
第三步：最後用having篩選出等於全部課程數的學生信息。
考核知識點： join、 group by、having、子查詢
答案：
40.查詢各學生的年齡，只按年份來算
解題思路：
第一步：關鍵詞有「學生的年齡」，鎖定使用表：學生表
第二步：用year和now來統計
考核知識點： year、now
答案：
41. 按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一
解題思路：
第一步：關鍵詞有「出生日期」，鎖定使用表：學生表
第二步：用timestampdiff()統計年齡
考核知識點： timestampdiff()
答案：
42.查詢本周過生日的學生
解題思路：
第一步：關鍵詞有「過生日」，鎖定使用表：學生表
第二步：用week函數
考核知識點： week()
答案：
43. 查詢下周過生日的學生
解題思路：
第一步：關鍵詞有「過生日」，鎖定使用表：學生表
第二步：用week函數
考核知識點： week()
答案：
44.查詢本月過生日的學生
解題思路：
第一步：關鍵詞有「過生日」，鎖定使用表：學生表
第二步：用month函數
考核知識點： month()
答案：
45.查詢下月過生日的學生
解題思路：
第一步：關鍵詞有「過生日」，鎖定使用表：學生表
第二步：用month函數
考核知識點： month()
答案：
F. sql語句相關測試
sql語句相關測試
篇一：SQL語句測試
1、（10分）要求:
選擇受理時間在2008-5-1 到 2008-6-1之間的所有申請人姓氏為「劉」的數據，並把「新受理編號」列表示成當前機器時間的年月和原受理編號年月後的數值組合
格式如下：
受理編號，新受理編號，受理時間， 申請人
200801112 201008112 2008-01-13劉XX
需要的表
I_Optinst 業務實例表
REGDATE（受理日期）
Regnum(受理編號)
Proposer（申請人）
解答：select Regnum as 受理編號, as 新受理編號,REGDATE as 受理時間,Proposer as申請人 from Optinst 2、（15分）要求：
前提：只統計業務小類「存量房買賣」
①按照月份分12月列出2008年每個月份的月份對應月份的交易總面積
②按照月份分12月列出2008年每個月份的月份對應月份的交易均價（申報價格/建築面積）
格式
年度月份 交易總面積 年度月份交易均價（元/平方米）
2008-01 23232 2008-01 2323
2008-02 23232008-02 232
2008-03 232323 2008-03 7656
2008-04 232323 2008-03 565
2008-05 232323 2008-03 5656
2008-06 232323 2008-03 565
2008-07 232323 2008-03 67
2008-08 232323 2008-03 676
2008-09 232323 2008-03 6767
2008-10 232323 2008-03 8686
2008-11 232323 2008-03 867
2008-12 232323 2008-03 454
需要的表：
Fc_room 房間表
BAREA（建築面積）
I_Optinst業務實例表
regdate（受理時間）
fc_owner 產權表
COSTVAL(申報價格)
EVLVAL（評估價格）
fc_owoom 房間明細表
1.select regdate as年度月份,BAREA as 交易總面積 from Fc_room,Optinst where
3、（20分）要求：
①：按照時間統計收費明細 統計格式如下
受理編號繳費人收費日期 收費名稱收費金額核費人收費人
②：按照時間匯總（2008年度）統計收費項目分類 統計格式如下
收費名稱 總金額
需要的表:
I_OptInst（業務實例表）
Regnum 受理編號
Proposer 申請人
I_Charge（收費實例表）
HEFEIMAN 核費人
PAYEE 收款人
CDATE 收費日期
I_ChrDtl（收費實例明細表
CNAME 收費名稱
MONEY 收費金額
4、（15分）要求：用途是住宅並且建築面積<=140>140 定義為 「非普通住宅」
用途是商業並且建築面積>140 定義為 「商業A級」
其他情況定義為「非住宅」
根據用途和面積列表出所有數據
格式
受理編號， 用途
200406000386 普通住宅
200406004631 非普通住宅
200406004633 普通住宅
200406004638 普通住宅
200406004641 非住宅
200501000004 普通住宅
200406004568 非住宅
200406005677 商業A級
表：
fc_room
barea 建築面積
BUse 用途
i_optinst
regnum 受理編號
5、（30分）工作量統計
① 選擇出以下格式的數據；並創建視圖名稱為view_AAAA
業務小類業務實例 交易價格建築面積 登記時間
1 存量房買賣 14100 19400.0029.98 2005-11-10 11:32:50 2 新建商品房 15041 229530.00 124.07 2005-11-21 08:59:36 3 新建商品房 15043 177363.00 101.35 2005-11-21 09:15:59 4 新建商品房 13046 71130.0023.71 2005-11-02 10:15:37 5 新建商品房 11280 148563.00 87.39 2005-10-11 09:50:48 6 新建商品房 11353 267369.00 116.04 2005-10-11 15:34:53 7 房改售房 2689 35.22 2004-06-17 08:43:00 8 產權人名稱變更 11701 724.18 2005-10-17 10:05:20 9 新建商品房 7206 158257.00 88.69 2005-09-16 14:50:57 10 存量房買賣 (轉 載於:wWw.cnboThwiN.cOM 博 威範文 網: sql語句測試 )10100 103.07 2005-08-31 20:27:06 11 存量房買賣 12980 51500.0046.66 2005-11-01 14:41:32 12 新建商品房 13000 136782.00 80.46 2005-11-01 15:37:05 13 新建商品房 16946 300844.00 146.33 2005-12-15 14:15:07 14 存量房買賣 10091 509.18 2005-08-31 19:19:25 ② 使用視圖 view_AAAA 當做表 選擇出如下樣式數據
業務小類 件數合計金額 合計面積
1 用途變更 1151.3
2 轉移登記 184.03
3 新建商品房 31 263243643197.34
4 房改售房 8252.43
5 產權人名稱變更 3 778.6
6 單位產新建 311697.49
7 贈與 1 28.48
8 存量房買賣 24 4379004134.67
9 判決仲裁 2 439.41
10 繼承遺贈 1 49.17
11 換證 2 228.88
12 新建房屋 17 928.91
③ 用水晶報表關聯視圖 view_AAAA 設計出類似於②的數據格式 需要的表
FC_Owner
EvlVal交易價格
I_OptInst,
SOName 業務小類
fc_owoom,
BArea 建築面積
FC_Room
regdate 受理日期
篇二：sql查詢語句學習測試答案
第一部分SQL查詢語句的學習
單表查詢 1、--查詢訂購日期在1996年7月1日至1996年7月15日之間的訂單的訂購日期、訂單ID、客戶ID和雇員ID等欄位的值
use eee
SELECT 訂購日期,訂單ID,客戶ID,
雇員ID
FROM 訂單
WHERE 訂購日期BETWEEN '1996-7-1 00:00:00' AND '1996-7-15 23:59:59'
2、--查詢「Northwind」示例資料庫中供應商的ID、公司名稱、地區、城市和電話欄位的值。條件是「地區等於華北」並且「聯系人頭銜等於銷售代表」。
use eee
SELECT 供應商ID,公司名稱,地區,城市,電話
FROM 供應商
WHERE 地區='華北' AND 聯系人職務='銷售代表'
3、--查詢「Northwind」示例資料庫中供應商的ID、公司名稱、地區、城市和電話欄位的值。其中的一些供應商位於華東或華南地區，另外一些供應商所在的城市是天津 use eee
SELECT 供應商ID,公司名稱,地區,城市,電話
FROM 供應商
WHERE 地區IN('華東', '華南') OR 城市='天津'
4、--查詢「Northwind」示例資料庫中位於「華東」或「華南」地區的供應商的ID、公司名稱、地區、城市和電話欄位的值
use eee
SELECT 供應商ID,公司名稱,地區,城市,電話
FROM 供應商
WHERE 地區IN('華東', '華南')
多表查詢 5、--查詢訂購日期在1996年7月1日至1996年7月15日之間的訂單的訂購日期、訂單ID、相應訂單的客戶公司名稱、負責訂單的雇員的姓氏和名字等欄位的值，並將查詢結果按雇員的「姓氏」和「名字」欄位的升序排列，「姓氏」和「名字」值相同的記錄按「訂單 ID」的降序排列
use eee
SELECT 訂購日期,訂單ID,公司名稱,姓氏,名字
FROM 訂單,雇員,客戶
WHERE 訂購日期BETWEEN '1996-7-1 00:00:00' AND '1996-7-15 23:59:59'
AND 訂單.雇員ID = 雇員.雇員ID
AND 訂單.客戶ID = 客戶.客戶ID
ORDER BY 姓氏,名字ASC,訂單ID DESC
6、--查詢「10248」和「10254」號訂單的訂單ID、運貨商的公司名稱、訂單上所訂購的產品的名稱
use eee
SELECT 訂單.訂單ID,公司名稱,產品名稱
FROM 訂單,運貨商,產品,訂單明細
WHERE 訂單.訂單ID IN('10248','10254')
AND 訂單.訂單ID = 訂單明細.訂單ID
AND 訂單明細.產品ID = 產品.產品ID
AND
訂單.運貨商= 運貨商.運貨商ID
7、--查詢「10248」和「10254」號訂單的訂單ID、訂單上所訂購的產品的名稱、數量、單價和折扣
use eee
SELECT 訂單.訂單ID,產品名稱,數量,訂單明細.單價,折扣
FROM 訂單,產品,訂單明細
WHERE 訂單.訂單ID IN('10248','10254')
AND 訂單.訂單ID = 訂單明細.訂單ID
AND 訂單明細.產品ID = 產品.產品ID
篇三：sql語句練習題及答案
一 在資料庫 school 中建立student , sc, course 表。
學生表、課程表、選課表屬於資料庫 School ，其各自的數據結構如下：
學生 Student (Sno,Sname,Ssex,Sage,Sdept)
課程表 course(Cno,Cname,Cpno,Ccredit)
學生選課 SC(Sno,Cno,Grade)
二 設定主碼
1 Student表的主碼：sno2 Course表的主碼：cno 3 Sc表的主碼：sno，cno
1寫出使用 Create Table 語句創建表 student , sc, course 的SQL語句
2
3 刪除student表中的元組
4在資料庫school中刪除關系student
5在student表添加屬性sbirthdate 類型 datetime
Delete
1 刪除所有 JSJ 系的男生 from Student where Sdept=』JSJ』 and Ssex=』男』; 2 刪除「資料庫原理」的課的選課紀錄
from SC where Cno in (select Cno fromCourse where Cname=』資料庫原理』);
Update
1 修改 0001 學生的系科為: JSJ
2 把陳小明的年齡加1歲，性別改為女。 2 修改李文慶的1001課程的成績為 93 分 3 把「資料庫原理」課的成績減去1分
Select 查詢語句
一 單表
1查詢年齡在19至21歲之間的女生的學號,姓名,年齡,按年齡從大到小排列。 2查詢姓名中第2個字為「明」字的學生學號、性別。 3查詢 1001課程沒有成績的學生學號、課程號
4查詢JSJ 、SX、WL 系的年齡大於25歲的學生學號,姓名，結果按系排列 5按10分制查詢學生的sno,cno,10分製成績
（1-10分 為1 ，11-20分為2 ，30-39分為3，。。。90-100為10） 6查詢 student 表中的學生共分布在那幾個系中。（distinct） 7查詢0001號學生1001，1002課程的成績。
二 統計
1查詢姓名中有「明」字的學生人數。 2計算『JSJ』系的平均年齡及最大年齡。 3查詢學生中姓名為張明、趙英的人數
4計算每一門課的總分、平均分，最高分、最低分，按平均分由高到低排列 5 計算 1001,1002 課程的'平均分。
6 查詢平均分大於80分的學生學號及平均分 7 統計選修課程超過 2 門的學生學號
8 統計有10位成績大於85分以上的課程號。 9 統計平均分不及格的學生學號
10 統計有大於兩門課不及格的學生學號
三 連接
1查詢 JSJ 系的學生選修的課程號
2查詢選修1002 課程的學生的學生姓名 (不用嵌套及嵌套2種方法） 3查詢資料庫原理不及格的學生學號及成績
4查詢選修「資料庫原理」課且成績 80 以上的學生姓名(不用嵌套及嵌套2種方法） 5查詢平均分不及格的學生的學號，姓名,平均分。 6查詢女學生平均分高於75分的學生姓名。
7查詢男學生學號、姓名、課程號、成績。(一門課程也沒有選修的男學生也要列出，不能
四 嵌套、相關及其他
1 查詢平均分不及格的學生人數
2 查詢沒有選修1002 課程的學生的學生姓名
3 查詢平均分最高的學生學號及平均分 （2種方法 TOP , any , all） *4 查詢沒有選修1001，1002課程的學生姓名。
5 查詢1002課程第一名的學生學號（2種方法） 6 查詢平均分前三名的學生學號
7 查詢 JSJ 系的學生與年齡不大於19歲的學生的差集
8 查詢1001號課程大於90分的學生學號、姓名及平均分大於85分的學生學號、姓名 9 查詢每門課程成績都高於該門課程平均分的學生學號 10 查詢大於本系科平均年齡的學生姓名
答案
參考答案
1 create table student (sno6), sname var8), ssex2), sagesmallint, sdept var15), primary key(sno));
create table sc
(sno6), cno 4),
grade decimal(12,2), primary key(sno,cno));
into student
values( 』4001』,』趙茵』,』男』,20,』SX』)
from student
student
alter table student add sbirthdate datetime
1 select sno, sname, sage from student
where ssex=』女』 and sage between 19 and 21order by sage desc; 2 select sno, ssexfrom student
where sname like 』_明% 』 ; 3 select sno, cnofrom sc
where grade is null and cno=』1001』 ; 4 select sno, sname from student
where sdept in (』JSJ』,』SX』,』WL』) and sage>25 group by sdept;
select sno, cno, grade/10.0+1 as levelfrom sc ;
select distinct sdept from student ; select grade from sc
where sno=』0001』 and (cno=』1001』 or cno=』1002』) ;
select count(*) from student where sname like 』%明% 』 ; select avg(sage),max(sage) from student where sdept=』JSJ』 ; select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno
order by avg(grade) desc ;
select cno, avg(grade) from sc where cno in(『1001』,』1002』) group by cno ;
select sc.sno ,avg(grade) from scgroup by sc.sno
having avg(grade)>80 ;
select sno from sc group by sno having count(*)>2 ;
select cno from sc where grade>85 group by cno having count(*)=10 ; select sno from sc group by sno having avg(grade)<60 ;
select sno from sc where grade<60 group="" by="" sno="" having="">2 ;
select cno from student,sc where student.sno=sc.sno and sdept=』JSJ』 ; a:select sname from student,sc where student.sno=sc.sno and cno=』1002』
b:select sname from student where sno in (select sno from sc where cno=』1002』)
select sno,grade from sc,course
where sc.cno=course.cno and cname=』資料庫原理』 and grade<60 a:select sname from student ,sc,course
where student.sno=sc.sno and sc.cno=course.cno and grade>80 and cname=』 資料庫原理』 b:select sname from student where sno in (select sno from sc where grade>80 and cno in (select cno from course where cname=』 資料庫原理』)) select sno,sname,avg(grade) from sc,studentwhere student.sno=sc.snogroup by student.sno having avg(grade)<60
a:select sname from student where ssex=』女』 and sno in(select sno from sc group by sno having avg(grade)>75)
b:select sname from sc,student where student.sno=sc.sno and ssex=』女』group by student.sno having avg(grade)>75
select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno and ssex=』男』
select count(*) from student where sno in( select sno from sc group by sno havingavg(grade)<60)
select sname from student where sno not in（select sno from sc where cno=』1002』）
student
0001 aaX 0002 bb
0003 ccX Sc
0001 1001 0001 1002 0002 1001 0003 1002
Select sname from student where not exists(select* from sc where cno=』1002』 and sc.sno=student.sno)
a:select top 1 sno,avg(grade) from sc group by sno order by avg(grade) desc b:select sno, avg(grade) from sc group by sno
having avg(grade)=(select top 1 avg(grade) from scgroup by sno order by avg(grade) desc) c:select sno, avg(grade) from sc group by sno
having avg(grade)>=all ( select avg(grade) from sc group by sno)
select sname from student where not exists(
select * from course where cno in(『1001』,』1002』) and
not exists(select * from sc where sno =student.sno and cno=course.cno) ) a:select top 1 sno from sc cno=』1002』 order by grade desc b:select sno from sc where cno=』1002』 and grade >=all (
;G. SQL練習題
一 學生 – 課程資料庫
1 查詢 7號課程沒有考試成績的學生學號
select sno from sc where cno=』7』 and grade is not null
2 查詢 7號課程成績在90分以上或60分以下的學生學號
select sno from sc where grade>90 or grade<60
3 查詢課程名以「數據」兩個字開頭的所有課程的課程號和課程名。
Select cno,cname from c where cname like 『數據%』
4 查詢每個學生所有課程的平均成績，輸出學生學號、平均成績
select sno,avg(grade) from sc group by sno
5 查詢每門課程的選修人數，輸出課程號、選修人數。
Select cno,count(*) from sc group by cno
6 查詢選修 7號課程的學生的學號、姓名、性別。
Select s.sno, sname,ssex from s , sc where s.sno=sc.sno and cno = 『7』
7 查詢選修7號課程學生的平均年齡。
Select avg(sage) from s , sc where s.sno=sc.sno and cno = 『7』
8 查詢由30名以上學生選修的課程號。
Select sno from sc group by cno having count(*)>30
9 查詢至今沒有考試不及格的學生學號
a: select sno from s where sno not in ( select sno from sc where grade<60 )
b: select sno from sc group by sno having min(grade)>=60
二
1 找出選修課程號為 C2 的學生學號與成績。
Select sno,grade from sc where cno=』C2』
2 找出選修課程號為C4 的學生學號與姓名。
Select s.sno , sname from s,sc where s.sno=sc.sno and cno=』C4』
3 找出選修課程名為 Maths 的學生學號與姓名。
Select s.sno ,sname from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno and cname = 『Maths』
4找出選修課程號為C2或C4 的學生學號。
Select distinct sno from sc where cno in (『C2』,』C4』)
或： Select distinct sno from sc where cno=』C2』 or cno =』C4』
5找出選修課程號為C2和C4 的學生學號。
Select sno from sc where cno =』C2』 and sno in (
select sno from sc where cno = 『C4』 )
6 找出不學C2課程的學生姓名和年齡
select sname , sage from s where sno not in ( select sno from sc where cno=』C2』 )
或：
select sname , sage from s where not exists ( select * from sc where sc.sno=s.sno and cno=』C2』 )
7 找出選修了資料庫課程的所有學生姓名。(與3同)
Select s.sno ,sname from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno and cname = 『資料庫』
8 找出資料庫課程不及格的女生姓名
嵌套：
select sname from s where ssex = 『女』 and sno in ( select sno from sc where grade<60 and cno in ( select cno from c where cname=』資料庫』) )
連接：
Select sname from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno and ssex=』女』 and cname = 『資料庫』 and grade<60
9 找出各門課程的平均成績，輸出課程名和平均成績
select cname , avg(grade) from sc , c where c.cno =sc.cno group by sc.cno
10找出各個學生的平均成績，輸出學生姓名和平均成績
select sname , avg(grade) from s , sc where s.sno=sc.sno group by sc.sno
11 找出至少有30個學生選修的課程名
select cname from c where cno in ( select cno from sc group by cno having count(*)>=30 )
12 找出選修了不少於3門課程的學生姓名。
Select sname from s where sno in ( select sno from sc group by sno having count(*)>=3)
13 找出各門課程的成績均不低於90分的學生姓名。
Select sname from s where sno not in ( select sno from sc where grade<90)
14* 找出資料庫課程成績不低於該門課程平均分的學生姓名。
Select sname from s where sno in (
Select sno from sc , c where sc.cno=c.cno and cname=』資料庫』 and
Grade > (Select avg(grade) from sc , c where sc.cno=c.cno and cname=』資料庫』 ) )
15 找出各個系科男女學生的平均年齡和人數。
Select sdept,ssex , avg(sage) , count(*) from s
Group by sdept , ssex
16 找出計算機系(JSJ)課程平均分最高的學生學號和姓名。
Select sc.sno , sname from s, sc where s.sno=sc.sno and sdept=』JSJ』
Group by sc.sno Having avg(grade) =
( Select top 1 avg(grade) from sc, s where s.sno=sc.sno and sdept=』JSJ』
group by sc.sno order by avg(grade) DESC )
三 客戶 – 商品資料庫中包括3按各表：KH,FP,YWY
1 查詢工資在 1000 到3000 元之間的男性業務員的姓名和辦公室編號。
Select Yname , Ono from YWY where salary between 1000 and 3000 and Ysex=』男』
2 查詢各個辦公室的業務員人數，輸出辦公室編號和對應的人數。
Select Ono , count(*) from YWY group by Ono
3 查詢每個客戶在2002年5月購買的總金額，輸出客戶號和相應的總金額。
Select Kno,sum(Fmoney) from FP where fdate between 『2002.5.1』 and 『2002.5.31』
Group by Kno
4 查詢2002年5月購買次數超過5次的所有客戶號，且按客戶號升序排序。
Select Kno from FP where fdate between 『2002.5.1』 and 『2002.5.31』
Group by Kno having count(*)>5
Order by Kno ASC
5 查詢各辦公室男性和女性業務員的平均工資。
Select Ono,Ysex ,avg(salary) from YWY group by Ono , Ysex
6 查詢2002年5月曾經在王海亮業務員手中購買過商品的客戶號、客戶姓名、聯系電話。
Select Kno,Kname,phone from KH where Kno in (
Select kno from FP where fdate between 『2002.5.1』 and 『2002.5.31』 and
Yno=(select Yno from YWY where Yname = 『王海亮』 )
7 查詢所有工資比1538號業務員高的業務員的編號、姓名、工資。
Select yno ,Yname, salary from YWY where salary >
( Select salary from YWY where Yno=』1538』 )
8 查詢所有與1538號業務員在同一個辦公室的其他業務員的編號、姓名。
Select Yno , Yname from YWY where Yno<>』1538』 and Ono in (
Select Ono from YWY where Yno=』1538』 )
9 查詢銷售總金額最高的業務員的編號。
Select Yno from FP Group By Yno Having sum(Fmoney) =
(Select top 1 sum(Fmoney) from FP group by Yno ORDER BY sum(Fmoney) DESC)
10 查詢所有業務員的編號、姓名、工資以及工資比他高的其他業務員的平均工資。
利用自身連接
Select y1.Yno ,y1.Yname ,y1.salary , avg( y2. salary) from YWY y1 , YWY y2
Where y1.Yno<>y2.Yno and y1.salary < y2.salary
Group by y1.Yno
Sno salary sno salary
1 100 1 100
2 120 2 120
3 90 3 90
4 110 4 110
四 某中學資料庫中由一張表：
學生選課表：由板及代碼、班內學號、姓名、科目、成績五個屬性組成，關系模式為
SC(BJDM,BNXH,XSXM,KM,CJ) ,其中(BJDM,BNXH)為主碼。
說明：每個學生每門科目存放一個記錄，科目有「語文」、「數學」、「外語」三門。
1 找出每個班級的班級代碼、學生人數、平均成績。
Select BJDM,count(*) ,avg(CJ) from SC group by BJDM
2 找出每個學生的班級代碼、學生姓名、考試科目數、總成績。
Select BJDM,XSXM,count(*) , sum(CJ) from SC
Group by BNXH
3 輸出一張表格，每位學生對應一條記錄，包括：班級代碼、姓名、語文成績、數學成績、外語成績。
方法一：利用視圖
create view v1 (bjdm,xsxm, yw,sx,wy ) AS
select bjdm , xsxm , cj , 0,0 from sc where km=』語文』
union
select bjdm , xsxm , 0 , cj,0 from sc where km=』數學』
union
select bjdm , xsxm , 0,0,cj from sc where km=』外語』
select bjdm, xsxm , sum(yw) as 語文, sum(sx) as 數學, sum(wy) as 外語 from v1 group by bjdm, xsxm
方法二:自身連接
select a.bjdm,a.xsxm , a.km,a.cj , b.km,b.cj , c.km,c.cj from sc a , sc b , sc c
where a.bjdm=b.bjdm and a.bnxh= b.bnxh and b.bjdm=c.bjdm and b.bnxh= c.bnxh
and a.km=』語文』 and b.km=』數學』 and c.km=』外語』
方法三：利用存儲過程（略）
4 輸出一張表格：由成績低於60分的每位學生對應一條記錄，包括欄位：班級代碼、姓名、最低成績。
Select bjdm,xsxm ,min(CJ) from sc where grade<60 group by bjdm,xsxm
5輸出一張表格：由成績低於60分的每位學生對應一條記錄，包括欄位：班級代碼、姓名、最高成績、平均成績。
得到平均成績：create view V1 (bjdm,bnxh ,avg_cj) AS
select bjdm,bnxh ,avg(cj) from sc where bjdm , bnxh
select sc.bjdm,sc.xsxm ,max(cj) , avg_cj from sc , V1
where sc.bjdm=v1.bjdm and sc.bnxh=V1.bnxh and cj<60
group by sc.bjdm,sc.xsxm
6輸出一張表格：所有成績不低於60分的每位學生對應一條記錄，包括欄位：班級代碼、姓名、平均成績。
select bjdm, xsxm , avg(cj) from sc
where sno not in ( select sno from sc where grade<60)
group by bjdm, xsxm
7輸出一張表格：每一位學生對應一條記錄，包括欄位：班級代碼、姓名、去掉一個最低分後的平均成績。
方法一：
得到每個學生的最低分：
create view V1 (bjdm,bnxh ,min_cj) as
select bjdm,bnxh,min(cj) from sc group by bjdm,bnxh
select sc.bjdm,sc.xsxm , avg(cj) from sc , v1
where sc.bjdm=v1.bjdm and sc.bnxh=v1.bnxh and sc.cj <> v1.min_cj
group by bjdm,bnxh
方法二：
select sc.bjdm,sc.xsxm , ( sum(cj) – min(cj) ) / count(*) from sc
group by bjdm , bnxh
8輸出一張表格：每門科目對應一條記錄，包括欄位：科目、去掉一個最低分後的平均成績。
方法一：
得到每門課的最低分：
create view V1 ( km, min_cj) as
select km,min(cj) from sc group by km
select sc.km , avg(cj) from sc , v1
where sc.km=v1.km and sc.cj <> v1.min_cj
group by sc.km
方法二：
select km , (sum( cj) – min(cj) )/count(*) from sc
group by km
補充9：輸出表格：每門科目對應一條記錄，包括欄位：科目、去掉一個最低分和最高分後的平均成績。
select km , (sum( cj) – min(cj) – max(cj) )/count(*) from sc
group by km
五 資料庫存放著某高校1990年以來英語四、六級的考試情況，且規定：
1 英語四、六級考試每年分別在6月和12月舉行二次；
2 四級沒有通過的學生不能報考六級；
3 某一級的考試只要沒有通過可以反復參加考試；
4 某一級的考試一旦通過就不能再報考同級的考試；
5 允許報了名但不參加考試。
該資料庫中有二張表，相應的關系模式如下：
學生表：S(Sno, Sname, Ssex, Sage, Sdept)，其中Sno為主碼。
考試表：E(Sno, Year, Month, Level, Grade)，學號、年、月、級別、成績。
其中(Sno, Year, Month)為主碼。
1. 找出各次四級和六級考試的參考人數和平均成績（報了名但沒有參加考試的不作統計）
select year , month,level ,count(*) , avg(grade)
group by year,month , level
2. 找出各次四級考試中平均分最高的系科（報了名但沒有參加考試的不作統計）。
A: Select sdept from s , e where s.sno=e.sno
Where level=4
Group by sdept
Having avg(grade)>=ALL(
Select avg(grade) from s , e where s.sno=e.sno where level=4 Group by sdept )
B: Select top 1 sdept from s , e where s.sno=e.sno
Where level=4
Group by sdept
Order by (avg(grade) desc
3. 找出已經通過英語六級考試的學生的學號、姓名和性別（用連接方法做）
select s.sno,sname,ssex from s,e
where s.sno=e.sno and level=6 and grade>=60
4. 找出在同一年中四、六級考試都參加了的學生的學號
1) select sno from E
where (level=4 and grade>=60) or level=6
group by year having count(*)>=2
2) select sno from E X where level=4 and grade>=60 and exists (
select * from E Y where Y.sno=X.sno and year=X.year and level=6 )
5. 找出只參加一次考試就通過了英語六級考試的學生的學號
select sno from E
where level=6
group by sno
having count(*)=1 錯，想想為何？
1) select sno from E
where level=6
group by sno
having count(*)=1 and max(grade)>=60
2) select sno from E where level=6 and grade>=60 and sno in (
select sno from E where level=6 group by sno having count(*)=1)
6. 找出至今沒有通過英語四級考試的學生的學號（應包括至今還沒有參加過考試或者是參加了但還沒有通過兩種）
select sno from E where level=4
group by sno
having max(grade)<60
Union
Select sno from s where sno not in( select sno from E)
7. 找出英語六級考試中合格人數最少的考試年份和月份（有並列的都要列出，用一句SQL語句）。
Select year , month From E
Where level = 6 and grade>=60
Group by year , month
Having count(*) <=all
(Select count(*) from E where level=6 and grade>=60 group by year , month )
我是從「上海全鼎軟體學院」畢業的————————