作者lordfish62 (阿瑜)
看板Examination
標題[考題] 102年警察二等 資料庫
時間Fri Apr 25 14:37:21 2014
版上的各位好,我這邊有一題SQL查詢語法的問題,希望大家幫我看看我的觀念正不正確,
寫完沒正確解答可以對照總覺得不太穩,尤其是第(四)題,麻煩各位了,謝謝!
題目:
https://www.dropbox.com/s/t6utc5q266oxw5r/102_1.jpg
我的答案:
(一)
SELECT Car.CarNo, Car.Name, Car.Brand, Car.Model, Car.Color
FROM Car
JOIN Speeding
ON Car.CarNo = Speeding.CarNo
WHERE KM=63
AND Date
BETWEEN '2013/4/1'
AND '2013/4/15'
(二)
SELECT CarNo, Name, Brand, Model, Color, Engine
FROM Car
WHERE Name
IN
(
SELECT Name
FROM Car
WHERE Engine > 3200
GROUP BY Name
HAVING COUNT(*) >= 2
)
(三)
SELECT Car.CarNo, Car.Name, Speed
FROM Car
JOIN Speeding
ON Car.CarNo = Speeding.CarNo
WHERE Speed >= (
SELECT MAX(Speed)
FROM Speeding )
(四)
SELECT CarNo
FROM Car
WHERE NOT EXISTS
(
SELECT *
FROM (
SELECT *
FROM Speeding
GROUP BY Highway )
AS hw
WHERE NOT EXISTS
(
SELECT *
FROM Speeding
WHERE hw.Highway = Speeding.Highway
AND
Car.CarNo = Speeding.CarNo
)
)
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 220.133.120.146
※ 文章網址: http://webptt.com/m.aspx?n=bbs/Examination/M.1398407845.A.DF1.html
1F:→ panda555:這題其實有人問過了..... 04/25 14:43
2F:→ lordfish62:真的嗎? 可是我沒搜到耶 04/25 14:43
3F:→ panda555:第4題 我會再引入一個speed的table 04/25 14:49
4F:→ panda555:妳的SELECT * FROM Speeding GROUP BY Highway ) AS hw 04/25 14:50
5F:→ panda555:我不確定會不會引入speeding(敝人學藝不精) 04/25 14:51
6F:→ lordfish62:我發現我漏打了,拍謝 我修正一下 04/25 14:51
7F:→ panda555:我是用speeding除掉你的HW 再用Car.CarNo = Speeding.Car 04/25 14:52
8F:→ panda555:在列出答案 04/25 14:52
※ 編輯: lordfish62 (220.133.120.146), 04/25/2014 14:55:36
※ 編輯: lordfish62 (220.133.120.146), 04/25/2014 14:56:48
9F:→ panda555:我的跟你一樣 應該沒錯吧XD 04/25 14:56
10F:→ lordfish62:哈哈 你四題的答案都跟我一樣嗎? 04/25 14:59
※ 編輯: lordfish62 (220.133.120.146), 04/25/2014 15:01:00
11F:→ panda555:其他題留給別人解囉 04/25 15:05
12F:→ lordfish62:OK, 感謝你的熱心幫忙~^^ 04/25 15:05