當前位置:首頁 » 編程語言 » 功能測試面試sql
擴展閱讀
webinf下怎麼引入js 2023-08-31 21:54:13
堡壘機怎麼打開web 2023-08-31 21:54:11

功能測試面試sql

發布時間: 2023-08-21 19:15:44

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

;