SQL面試題（常見題型）

SQL

1.用一條SQL 語句 查詢出每門課都大于80 分的學生姓名

name kecheng fenshu

張三 國文 81

張三 數學 75

李四 國文 76

李四 數學 90

王五 國文 81

王五 數學 100

王五 英語 90

2.删除除了自動編号不同, 其他都相同的學生備援資訊

自動編号 學号 姓名 課程編号 課程名稱 分數

1 2005001 張三 0001 數學 69

2 2005002 李四 0001 數學 89

3 2005001 張三 0001 數學 69

3.一個叫 team 的表，裡面隻有一個字段name, 一共有4 條紀錄，分别是a,b,c,d, 對應四個球對，現在四個球對進行比賽，用一條sql 語句顯示所有可能的比賽組合.

select a.name,b.name from team as a, team as b where a.name<b.name  order by a.name,b.name
           

4.請用SQL 語句實作：從TestDB 資料表中查詢出所有月份的發生額都比101 科目相應月份的發生額高的科目。請注意：TestDB 中有很多科目，都有1 －12 月份的發生額。

AccID ：科目代碼，Occmonth ：發生額月份，DebitOccur ：發生額。

資料庫名：JcyAudit ，資料集：Select * from TestDB

select testdb.accid from testdb,
(select *  from testdb where Accid=101) as a 
where testdb.Occmonth  = a.Occmonth  and a.debitOccur < testdb.debitOccur
group by testdb.AccID HAVING COUNT(testdb.AccID)=12
           

5.面試題：怎麼把這樣一個表轉置

year month amount

1991 1 1.1

1991 2 1.2

1991 3 1.3

1991 4 1.4

1992 1 2.1

1992 2 2.2

1992 3 2.3

1992 4 2.4

查成這樣一個結果

year m1 m2 m3 m4

1991 1.1 1.2 1.3 1.4

1992 2.1 2.2 2.3 2.4

# case1 
SELECT year,(SELECT amount from test as t WHERE month=1 and t.year= test.year ) as m1
from test GROUP BY year

# case2 
SELECT year,max(case month WHEN 1 THEN amount else 0 END) as m1 ,
max(case month WHEN 2 THEN amount else 0 END) as m2,
max(case month WHEN 3 THEN amount else 0 END) as m3,
max(case month WHEN 4 THEN amount else 0 END) as m4
from test GROUP BY year 
           

6.按照所清單格字段和SQL查詢結果，寫出SQL語句

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-W5GGfP50-1619417896351)(C:\Users\86132\AppData\Roaming\Typora\typora-user-images\image-20210422195545508.png)]

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-Rl8CVQkj-1619417896357)(C:\Users\86132\AppData\Roaming\Typora\typora-user-images\image-20210422195704778.png)]

SELECT	TF.flats_num AS "較高價的電梯大廈編号",	
COUNT(CASE WHEN TR.`status` = 0 THEN TR.id END) AS "未出租數量",	
COUNT(TR.id) AS "該較高價的電梯大廈所有房間數量",	
(CASE WHEN TF.is_private = 1 THEN '是' ELSE '否' END) AS "較高價的電梯大廈是否是自營",	CONCAT(TC.`name`, '-', TC.street) AS "小區位址"
FROM	t_flats TF LEFT JOIN t_community TC ON (TF.community_id = TC.id)LEFT JOIN t_room TR ON (TR.flats_id = TF.id)GROUP BY TF.flats_num;
           

7.兩張關聯表，删除主表中已經有，在附表中沒有的 資訊

8.有兩個表A和B，均有key和value兩個字段，如果B的key在A中也有，就把B的value替換為A中對應的value

--方法1
update B b set b.value=(select max(a.value) from A a where b.key=a.key)
where exists(select 1 from A c where b.key=c.key)

--方法2
UPDATE A a,(select a.`key`,b.`value` from A INNER JOIN B on a.`key`=b.`key`) b 
SET a.`value` = b.`value` WHERE a.`key` = b.`key`