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

資料庫內容：

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