使用 ASP 及 SQL 對資料庫進行資料查詢


資料庫內容:

資料庫 "basketball.mdb"
資料表 "Player" 的內容 資料表 "Team" 的內容
ID NickName Name TeamID Percentage
jean 吳志銘 38.25 
jones 張秤嘉 49.77 
ben 陳孜彬 50.26 
asser 林惠娟 37.22 
window 李宜揚 36.67 
roger 張智星 25.88 
cosh 許文豪 70.98 
banny 洪鵬翔 88.97 
shyba 邱中人 67.45 
10 batty 楊璧如 65.55 
11 joey 許嘉晉 47.65 
12 roland 吳瑞千 55.87 
13 sony 林頌華 54.77 
14 beball 葉佳慧 33.33 
15 gavins 林政源 55.65 
16 jojo 陳俊傑 44.65 
17 jtchen 陳江村 48.76 
18 Gao 高名揚 67.88 
19 Wayne 陳智偉 65.87 
20 chingz 陳晴 57.28 
ID Name WinNo
台北隊 12 
新竹隊 
台中隊 10 
南投隊 12 
台南隊 17 
高雄隊 16 
澎湖隊 11 

請選擇下列 SQL 命令:

基本 SQL:
  1. SELECT Name, Percentage FROM Player
  2. SELECT * FROM Player
  3. SELECT * FROM Player order by Percentage DESC
  4. SELECT * FROM Player order by TeamID, Percentage DESC
  5. SELECT Name, Percentage FROM Player where NickName='gavins'
  6. SELECT TeamID, Name, Percentage FROM Player where TeamID=5
  7. SELECT Name, Percentage FROM Player where Name like '陳%'
  8. SELECT DISTINCT TeamID FROM Player
  9. SELECT TeamID FROM Player GROUP BY TeamID
中階 SQL:
  1. SELECT Name, Percentage FROM Player WHERE Percentage > 60
  2. SELECT Team.Name, Player.Name, Percentage FROM Player, Team WHERE Player.TeamID=Team.ID order by Team.Name
  3. SELECT Team.Name, Player.Name, Percentage FROM Player, Team WHERE Player.TeamID=Team.ID and Team.Name LIKE '台_隊'
  4. SELECT Team.Name, Player.Name, Player.Percentage FROM Player, Team WHERE (((Player.Percentage)>50) AND ((Player.TeamID)=[Team].[ID]) AND ((Team.Name) Like '台%'))
  5. SELECT Player.Name, Percentage FROM Player, Team WHERE Percentage > 50 AND Player.TeamID=Team.ID AND Team.Name IN ('高雄隊', '澎湖隊')
  6. SELECT Count(*) as 筆數 FROM Player WHERE Percentage>= 30 AND Percentage <= 60
高階 SQL:
  1. SELECT MAX(Percentage) FROM Player
  2. SELECT * from Player where Percentage in (SELECT MAX(Percentage) FROM Player)
  3. SELECT AVG(Percentage) FROM Player, Team WHERE Player.TeamID = Team.ID AND Team.Name='高雄隊'
  4. SELECT MIN(Percentage) FROM Player, Team WHERE Player.TeamID = Team.ID AND Team.Name NOT IN ('新竹隊','高雄隊')
  5. SELECT Name, Percentage FROM Player WHERE Percentage > (select avg(Percentage) from Player)
  6. SELECT Name, Percentage FROM Player WHERE Percentage < (select avg(Percentage) from Player)/2

View source: [Server-side script] [Client-side script (ctrl+u)]
回到「JScript 程式設計與應用:伺服器端