㈠ 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.查詢全部學生的姓名和所學的課程名稱及成績
select s.Sname,o.Cname,c.Grade from Student s,enrolls c,Courses o where s.Sno=c.Sno and c.Cno=o.Cno
--2.找出所有學生的平均成績和所學課程門數
select Sno,avg(grade) as '平均成績',count(*) as '所學課程門數' from enrolls group by Sno;
--3.找出各課程的平均成績,按課程號分組,且只選擇學生超過3人的課程的成績
select enrolls.Cno,cname,avg(grade) as '平均成績' from enrolls,Courses where enrolls.cno=Courses.cno group by enrolls.Cno,cname having count(*)>=3;
--4.找出選修了全部課程的學生的姓名
select Sname from student where sno in(select sno from enrolls group by Sno having count(sno)=(select count(cno) from Courses))
㈢ sql面試題 不會做 都進來看看吧
才兩張表,隨便寫寫就出來了 你是沒見過該死的bom 那才傷腦筋
--先建立測試用例:
create table users(Id Int,Name varchar(20),regDate date);
alter table users add constraint users_pk primary key(Id);
create table UploadInfo(Id Int,UserId Int,FileName varchar(20),foreign key(UserId) references users(Id));
insert into users values(1,'A',sysdate-1);
insert into users values(2,'B',sysdate-2);
insert into users values(3,'C',sysdate-3);
insert into users values(4,'D',sysdate-4);
insert into users values(5,'E',sysdate-5);
select*from users;
--隨機插入上傳數據的存儲過程
create or replace procere InsertUploadInfoData is
i int:=0;
begin
for i in 1..100 loop
insert into UploadInfo values(i,trunc(DBMS_RANDOM.value(1,5)),'FileName '||trunc(DBMS_RANDOM.value(1,1000)));
end loop;
commit;
end ;
begin
InsertUploadInfoData;
end;
select*from UploadInfo;
--開始了
--第一題
select UserUploadRecord.Id,UserUploadRecord.UploadCount
from
(
select users.Id,count(UploadInfo.Id) as UploadCount from users
inner join UploadInfo on users.Id=UploadInfo.UserId --內聯結
where users.regDate>=sysdate-3 and users.regDate<=sysdate --三天內
group by users.Id
order by UploadCount desc
) UserUploadRecord
where rownum=1 --取第一條記錄
;
--創建用戶名重復的測試用例
insert into users values(6,'A',sysdate-4);
--由於有外鍵約束,所以需先刪除子表記錄
delete from UploadInfo where UploadInfo.Userid
in
(
select users.id from users where (users.name,users.regdate) in
(
select users.name,max(users.regdate) from users group by users.name having count(users.id)>1 --取重復用戶名的最大注冊時間
)
)
--再刪除父表,做法差不多
delete from users where (users.name,users.regdate) in
(
select users.name,max(users.regdate) from users group by users.name having count(users.id)>1
)
————————————————————
建表語法 測試用例都寫給你了 還說運行不了?直接復制進去就行了
㈣ 軟體測試SQL面試題
資料庫,無外乎增刪改查:
增:
insert into A表 values (value1,value2,value3......)向A表中新增數據
刪:
delete from A表 ( where id=1) 刪除A表(刪除A表id=1的數據)如果刪除某個測試數據,記得加where條件,否則整張表的數據都被刪除,防止誤操作!不過,數據可以回滾找回。
truncate A表:清除表數據,數據無法回滾。
drop A表:整表結構刪除,即,這張表不存在了。
改:
update A表 set name='test' where id=1 修改id為1的name值為test
查:
1:左關聯和右關聯的區別?
答:左關聯( left join )左表為主,左表數據全部顯示,右表顯示關聯數據,無關聯顯示null,右關聯( right join )相反。
2:模糊查詢?
select * from A表 where name like ' %a% ' (name包含a字母)
查詢結果: a , a bc,b a c,bc a
select * from A表 where name like ' a% ' (name值以a字母開頭)
查詢結果: a , a bc
select * from A表 where name like ' %a ' (name值以a字母結尾)
查詢結果: a ,bc a
3:統計:count和sum
count統計表的 記錄數 ,sum統計某列數值 總和 。
select count(*) from A表
select count(1) from A表
select count(列名) from A表
區別:
count( * ): 所有記錄,包括null值
count( 1 ):所有記錄,包括null值
count( 列名 ):列名有值得記錄, 不包括null值
執行效率:
以前是count(1)比count(*)快,但現在count(*)底層演算法優化,查詢更快,所以推薦count(*)
統計有效數據的記錄,count(列名)
select sum(列名) from A表 計算列名數值總和。
舉例:
查詢A表中,姓王的用戶量
select count(*) from A where name like '王%'
Plus版
1:去重distinct,查詢不重復記錄的數據
必須放開頭
select distinct 列名 from 表名(查詢所有列名數據,去掉重復數據)
舉例:
表A,查詢考核等級grade,有哪些值
select distinct grade from A
2:分組group by,根據某個欄位分組
select 列名 from 表名 group by 列名,一般會配合聚合函數一起使用
舉例:
表A中,查詢考核等級grade欄位,不同值各多少人
select grade,count(*) from A group by grade
3:limit,查詢結果返回的數量,多用於分頁查詢
select * from 表名 limit i,n i代表查詢結果的索引值,默認從0開始,n返回查詢的結果數。
舉例:
訂單表A,查詢第21條到30條數據
select * from A limit 20,10
可能會問為什麼不能直接用id查詢?因為id不準確,可能存在id不連續的情況。如果某條數據被物理刪除了呢?
何為物理刪除和邏輯刪除?
物理 刪除 ,直接將某條數據,從表中刪除。
邏輯 刪除,僅通過某個欄位標記刪除,實際表中還存在。(比如:is_delete=1代表已刪除,is_delete=0未刪除)
4:排序order by 列名 asc(列名值升序排列)和order by 列名 desc(列名值降序排列)
舉例:
用戶表A,按照用戶id升序(select查詢默認根據主鍵升序,所以升序,不加order by也可以)
select * from A order by id asc同select *from A
用戶表A,按照新建時間倒序
select * from A order by create_time desc
5:in和between查詢某個范圍的數值
舉例:
in:查詢指定數值的數據
between:查詢某個范圍內的數據
舉例:
查詢表A中,id=1和id=10的數據
select * from A where id in(1,10)
查詢表A中,1月份新增的數據
select * from A where create_time between '2022-01-01' and '2022-01-31 23:59:59'
已知有如下4張表:
學生表:student(學號,學生姓名,出生年月,性別)
成績表:score(學號,課程號,成績)
課程表:course(課程號,課程名稱,教師號)
教師表:teacher(教師號,教師姓名)
准備練習數據
1)創建學生表(student)
2)創建成績表(score)
3)創建課程表(course)
4)教師表(teacher)
1)學生表添加數據
2)成績表添加數據
3)課程表添加數據
4)教師表添加數據
簡單查詢
查詢姓「猴」的學生名單
查詢姓名中最後一個字是「猴」字的學生名單
查詢姓名中帶「猴」字的學生名單
查詢姓「孟」老師的個數
匯總分析
1.匯總分析
查詢課程編號為「0002」的總成績
查詢選了課程的學生人數
2.分組
查詢各科成績的最高分和最低分, 以如下的形式顯示:課程號,最高分,最低分
查詢每門課程選修的學生數
3.分組結果的條件
查詢至少選修兩門課程的學生學號
查詢同名同姓學生名單並統計同名人數
分析:條件:怎麼算姓名相同?按姓名分組後人數大於等於2,因為同名的人數大於等於2,分析出這一點很重要
查詢不及格的課程並按課程號從大到小排列
查詢每門課程的平均成績,結果按平均成績升序排序,平均成績相同時,按課程號降序排列
檢索課程編號為「0004」且分數小於60的學生學號,結果按按分數降序排列
統計每門課程的學生選修人數(超過2人的課程才統計),要求輸出課程號和選修人數,查詢結果按人數降序排序,若人數相同,按課程號升序排序
查詢兩門以上不及格課程的同學的學號及其平均成績
第2步:再加上限制條件:
1)不及格課程
2)兩門以上[不及格課程]
4.查詢結構排序,分組的指定條件
查詢學生的總成績並進行排名
查詢平均成績大於60分的學生的學號和平均成績
復雜查詢
查詢課程成績小於60分學生的學號、姓名
【知識點】子查詢
1.翻譯成大白話
1)查詢結果:學生學號,姓名
2)查詢條件:所有課程成績 < 60 的學生,需要從成績表裡查找,用到子查詢
第1步,寫子查詢(所有課程成績 < 60 的學生)
第2步,查詢結果:學生學號,姓名,條件是前面1步查到的學號
查詢沒有學全所有課的學生的學號、姓名
查詢出只選修了兩門課程的全部學生的學號和姓名
查找1990年出生的學生名單
查詢本月過生日的學生
工作中會經常遇到這樣的業務問題:
如何找到每個類別下用戶最喜歡的產品是哪個?
如果找到每個類別下用戶點擊最多的5個商品是什麼?
這類問題其實就是常見的:分組取每組最大值、最小值,每組最大的N條(top N)記錄。
分組取每組最大值,按課程號分組取成績最大值所在行的數據
分組取每組最小值,按課程號分組取成績最小值所在行的數據
每組最大的N條記錄,查詢各科成績前兩名的記錄
第1步,查出有哪些組
我們可以按課程號分組,查詢出有哪些組,對應這個問題里就是有哪些課程號
第2步:先使用order by子句按成績降序排序(desc),然後使用limt子句返回topN(對應這個問題返回的成績前兩名)
第3步,使用union all 將每組選出的數據合並到一起
多表查詢
查詢所有學生的學號、姓名、選課數、總成績
查詢平均成績大於85的所有學生的學號、姓名和平均成績
查詢學生的選課情況:學號,姓名,課程號,課程名稱
㈤ 應屆生在軟體測試的面試
軟體測試面試題大全-最真實的面試題
以下為童鞋去面試整理的面試問題。--真實軟體測試面試題、真實公司所問的
適合人群:零基礎學習軟體測試、多年跳槽的人員
面試過程中也會有深挖的,這些就不在多寫。還有些待整理的,慢慢補上、完善。
希望對各位有所幫助!來源--Dotest
1:個人介紹
2:友碰項目介紹
3:流程邏輯介紹
4:職責介紹
5:項目人數,分別負責的是什麼
6:每日工作介紹
7:介面如何測試的?用例如何設計?
8:一個項目中大概有多少個介面
9:getpost的區別
10:常見的狀態碼是什麼
11:postman、Jmetersoapui的區別
12:公司流程
13:需求評審是怎麼進行的
14:測試計劃怎麼寫的?重點是什麼?
15:測試用例的思路?
16:如果我們沒有原型圖、需求的話,你怎麼寫用例
17:給你個appweb你能快速說下用例的設計思路嗎?
18:app與web的不同點是什麼?
19:測試報告的重點
20:什麼是灰度測試
21:上線後的跟蹤如何做的?
22:為什麼要做上線後的回歸測試
23:上線後發現的問題如何處理
24:什麼是UAT測試,為什要做UAT測試
25:bug怎麼描述的
26:用例怎麼描述的
27:優先順序和嚴重程度如何劃分
28:如果和開發有歧義的話怎麼辦
29:你映像bug是什麼?
30:你發現一個bug如何定位的
31:自動化測試如何進行的?用的什麼框架
32:常用的定位方法是什麼?
33:功能測試與自動化的區別
34:什麼樣的項目適合自動化
35:自動化的好處是什麼
36:自動沖肢化的劣處是什麼
37:你感覺你寫的用例覆蓋全面嗎
38:上線後發現的問題如何處理
39:linux常用的命令
40:linux常用的文件
41:linux環境搭建的步驟
42:常用sql語句
43:你們公司用sql嗎,怎麼用的
44:兼容測試你怎麼做的
45:手機是如何考慮的
46:兼容測試的重點是什麼
47:如果手機不夠的話你怎麼解決
48:真機和模擬器的區別是什麼
49:你如何保證兼容的覆蓋率
50:怎麼抓手機的數據包的?如何設置
51:通過抓取數據包主要測試什麼
52:性能測試做嗎?怎麼做的
53:python的遍歷是什麼
54:隨意丟個軟體說下具體的用例
55:你們公司多長時間迭代一次
56:迭代測試需要注意什麼
57:你映像最深的bug是什麼
58:你的期望薪資
59:你看比預期低2k,你能接受嗎
60:我們公司加班多,能接受嗎
61:能接受出差嗎?
62:路程有點遠
63:你為什麼要離職
64:你感覺你的優勢在哪?
65:你未來的規劃是什麼
66:你對下一個工作崗位有什麼期望或要求嗎
67:對你的領導有什麼要求嗎
68:你感覺你上家公司怎麼樣;能說下優缺點嗎?好判談
69:你感覺你上家領導怎麼樣;能說下優缺點嗎?
70:有女朋友男朋友嗎?
71:打算什麼時候要孩子
72:你看你有什麼想問的嗎
73:你大概什麼時候能入職
74:最近面試多嗎?
75:手上有offer嗎?
76:你是什麼時候開始找工作的?
77:你從上家公司離職已經有好幾天了,是剛開始找工作嗎?為什麼一直沒找工作
㈥ SQL語句問題 面試題
select * from Voucher where 發生額>(select 發生額 from Voucher where 科目代碼=101)
㈦ 一道軟體測試面試題中的sql題目,求解
1、select class.c_name,count(student.sex) as '女生人數',avg(student.score) as '女生平均分' from student,class,student_class where student.s_id=student_class.c_id and class.c_id=student_class.s_id and student.sex='女' group by class.c_name ;
2、select class.c_name,count(case when student.sex='女' then student.sex end) as '女生人數',avg(case when student.sex='女' then student.score else 0 end) as '女生平均分' from student,class,student_class where student.s_id=student_class.c_id and class.c_id=student_class.s_id group by class.c_name ;
㈧ SQL面試題
推薦的學習Java的學習順序如下: 學習Java SE部分的內容,這部分是基礎內容。掌握的越扎實,後面的內容學習起來就越容易。學習SQL和資料庫的內容 ,這個也是非常基礎的內容,舉個例子:程序員的面試題中一定會有SQL部分的考試。學習HTML、css、JavaScript的內容。學習,JSP、Servlet、JavaBean、標記庫、JSTL、MVC、JSP EL等Web編程的部分學習Struts 或者Spring MVC。二者取其一即可,推薦選Struts 學習Java與XML ,重點掌握使用Java技術對XML文件的解析學習Java EE的核心技術 ,例如:JNDI、EJB3等,同時還需要學習使用與配置應用伺服器,例如:webLogic Server,Websphere,JBoss 學習Hibernate 等持久層的技術學習Spring 學習AJAX 學習ANT、Subversion等開發工具,熟悉常見的軟體開發模型,例如:rup、XP 學習OOA與OOD,uml,設計模式學習Web Services、SOA的原理與應用開發 最後,現在很多公司都使用Linux系統做為伺服器系統,並且使用Linux系統進行程序的開發(都是出於使用正版的考慮),所以Linux系統的安裝、使用和簡單管與配置,也是需要學習,這部分內容,跟其他內容沒有關聯,可以在任何時候學。推薦學習完SQL與資料庫之後進行學習。 你要想找到一份不錯的Java程序員工作,最基本的也要把1-12都學習了,當然有的內容需要深入掌握,有的內容可以慢慢的深入掌握的。
㈨ sql面試題
1、首先,將三個表建立一個視圖,可以直接在企業管理器裡面建,也可以將下面的代碼直接復制到查詢分析器裡面執行:
SELECT dbo.students.studentname, dbo.class.classname, dbo.score.course,
dbo.score.score
FROM dbo.class INNER JOIN
dbo.students ON dbo.class.classid = dbo.students.classid INNER JOIN
dbo.score ON dbo.students.studentid = dbo.score.studentid
2、然後,求 各科 班分數 最高的同學的名字,班級名稱,課程名稱,分數 ,代碼如下:
select classname as 班級,course as 學科,max(score) as 最高分,(select top 1 studentname from scoreview where classname=x.classname and course=x.course order by score desc) as 最好成績的學生 from scoreview x group by classname,course
------------
已經經過測試,你自己可以試試
㈩ 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
;