SQL語句強化練習題及答案_百度文庫

1、SQL語句強化練習題及答案 一、簡單查詢1、列出全部學生的信息。SELECT * FROM 學生2、列出軟件專業(yè)全部學生的學號及姓名。SELECT 學號,姓名 FROM 學生 WHERE 專業(yè)="軟件"3、列出所有必修課的課號。SELECT DISTINCT 課號 FROM 必修課4、求1號課成績大于80分的學生的學號及成績，并按成績由高到低列出。SELECT 學號,成績 FROM 選課 WHERE 課號="1" AND 成績>80 ORDER BY 成績 DESC5、列出非軟件專業(yè)學生的名單。方法一：SELECT 姓名 FROM 學生 WHERE

2、 專業(yè)<>"軟件"方法二：SELECT 姓名 FROM 學生 WHERE NOT 專業(yè)="軟件"方法三：SELECT 姓名 FROM 學生 WHERE 專業(yè)!="軟件"6、查詢成績在7080分之間的學生選課得分情況方法一：SELECT * FROM 選課 WHERE 成績>=70 AND 成績<=80方法二：SELECT * FROM 選課 WHERE 成績 BETWEEN 70 AND 80不在此范圍內的查詢：（注意寫出和以下語句等價的語句）SELECT * FROM 選課 WHERE 成績 NOT BETW

3、EEN 70 AND 807、列出選修1號課或3號課的全體學生的學號和成績。方法一：SELECT 學號,成績 FROM 選課 WHERE 課號="1" OR 課號="3"方法二：SELECT 學號,成績 FROM 選課 WHERE 課號 IN ("1","3"相反條件查詢：SELECT 學號,成績 FROM 選課 WHERE 課號 NOT IN ("1","3"8、列出所有98級學生的學生成績情況。SELECT * FROM 選課 WHERE 學號 LIKE "98

4、%"SELECT * FROM 選課 WHERE 學號 LIKE "98_ _ _ _"相反條件查詢：SELECT * FROM 選課 WHERE 學號 NOT LIKE "98%"9、列出成績?yōu)榭罩?或不為空值的學生的學號和課號。答案一：SELECT 學號,課號 FROM 選課 WHERE 成績 IS NULL答案二：SELECT 學號,課號 FROM 選課 WHERE 成績 IS NOT NULL10、求出所有學生的總成績。SELECT SUM(成績 AS 總成績 FROM 選課11、列出每個學生的平均成績。SELECT 學號,AVG(成績

5、 AS 平均成績 FROM 選課 GROUP BY 學號12、列出各科的平均成績、最高成績、最低成績和選課人數。SELECT 課號,AVG(成績 AS 平均成績,MAX(成績 AS 最高分,;MIN(成績 AS 最低分,COUNT(學號 AS 選課人數 FROM 選課 GROUP BY 課號二、連接查詢（一）簡單連接1、列出選修1號課的學生姓名及成績。SELECT 姓名,成績 FROM 學生,選課 WHERE 學生.學號=選課.學號 AND 課號="1"2、列出選修1號課的學生的學號、姓名及成績。SELECT 學生.學號,姓名,成績 FROM 學生 S,選課 X WHERE

6、 S.學號=X.學號 AND 課號="1"3、求出總分大于150的學生的學號、姓名及總成績。SELECT 學生.學號,姓名,SUM(成績 AS 總成績 FROM 學生,選課;WHERE 學生.學號=選課.學號 GROUP BY 選課.學號 HAVING SUM(成績>150（二）自連接查詢1、列出那些專業(yè)相同的學生相應的姓名及專業(yè)信息。SELECT a.姓名,b.姓名,專業(yè) FROM 學生 a,學生 b WHERE a.學號<>b.學號 AND a.專業(yè)=b.專業(yè)2、求至少選修1號課和2號課的學生的學號。SELECT X.學號 FROM 選課 X,選課 Y

7、 WHERE X.學號=Y.學號 AND X.課號="1" AND Y.課號="2"3、有以下表rate.dbf    幣種1代碼 C(2、幣種2代碼 C(2、買入價 N(8,4、賣出價 N(8,4外匯匯率.dbf    幣種1 C(4、幣種2 C(4、買入價 N(8,4、賣出價 N(8,4外匯代碼.dbf    外匯名稱 C(10、外匯代碼 C(10要求：將所有“外匯匯率”表中的數據插入rate表中并且順序不變，由于“外匯匯率”中的幣種1和幣種2存放的是外幣名稱，

8、而rate表中的幣種1代碼和幣種2代碼應該存放外幣代碼，所以插入時要做相應的改動，外幣名稱與外向代碼的對應關系存儲在“外匯代碼”表中。SELECT A.外幣代碼 AS 幣種1代碼,B.外幣代碼 AS 幣種2代碼,;買入價,賣出價 FROM 外匯代碼 A,外匯匯率,外匯代碼 B;WHERE A.外幣名稱=外匯匯率.幣種1 AND B.外幣名稱=外匯匯率.幣種2 INTO TABLE rate4、假定有“雇員”表（雇員號 C（2），雇員姓名 C（6），經理號 C（2），根據雇員關系列出上一級經理及其所領導的職員清單。（教案中的例題）SELECT "領導",S.雇員姓名,&quo

9、t;雇員",E.雇員姓名 FROM 雇員 S,雇員 E WHERE S.雇員號=E.經理（三）超連接1、列出選修1號課的學生姓名及成績。方法一：（使用簡單連接查詢格式）SELECT 姓名,成績 FROM 學生,選課 WHERE 學生.學號=選課.學號 AND 課號="1"方法二：（使用內部連接格式）SELECT 姓名,成績 FROM 學生 INNER JOIN 選課 ON 學生.學號=選課.學號 WHERE 課號="1"方法三：內部連接的INNER短語可以省略。（與方法二等價）SELECT 姓名,成績 FROM 學生 JOIN 選課 ON 學生

10、.學號=選課.學號 WHERE 課號="1"2、查詢訂貨管理數據庫中數據的倉庫號、城市、供應商名和地址信息。方法一：使用簡單連接格式。SELECT 倉庫.倉庫號,城市,供應商名,地址 FROM 供應商,訂購單,職工,倉庫;WHERE 供應商.供應商號=訂購單.供應商號 AND訂購單.職工號=職工.職工號 ;AND 職工.倉庫號=倉庫.倉庫號方法二：使用超連接的內部連接格式。（注意連接條件的順序）SELECT 倉庫.倉庫號,城市,供應商名,地址 FROM 供應商 JOIN 訂購單 JOIN 職工 JOIN 倉庫 ;ON 職工.倉庫號=倉庫.倉庫號 ON 訂購單.職工號=職工.

11、職工號 ON 供應商.供應商號=訂購單.供應商號3、查詢沒有選修任何課程的學生姓名。方法一：使用嵌套查詢SELECT 姓名 FROM 學生 WHERE 學號 NOT IN (SELECT 學號 FROM 選課方法二：使用超連接的右連接。SELECT 姓名 FROM 選課 RIGHT JOIN 學生 ON 選課.學號=學生.學號 WHERE 選課.學號<>學生.學號方法三：使用超連接的左連接。（注意表名順序和方法二的不同）SELECT 姓名 FROM 學生 LEFT JOIN 選課 ON 選課.學號=學生.學號 WHERE 選課.學號<>學生.學號三、嵌套查詢（一）普通嵌

12、套與謂詞EXISTS1、列出選修匯編語言課的學生的學號。方法一：SELECT 學號 FROM 選課 WHERE 課號=(SELECT 課號 FROM 課程 WHERE 課名="匯編語言"方法二：使用謂詞EXISTS。注意和方法一格式上的不同。SELECT 學號 FROM 選課 WHERE EXIST (SELECT * FROM 課程 ;WHERE 課名="匯編語言" AND 選課.課號=課程.課號2、求軟件專業(yè)所有必修課的課程信息。方法一：SELECT * FROM 課程 WHERE 課號 IN; (SELECT 課號 FROM 必修課 WHERE 必

13、修專業(yè)="軟件"方法二：SELECT * FROM 課程 WHERE EXIST (SELECT * FROM 必修課 WHERE 必修專業(yè)="軟件" ;AND 課程.課號=必修課.課號（二）量詞ANY、SOME、ALL1、求選修2號課的學生中，成績比選修1號課的最低成績要高的學生的學號和成績。方法一：SELECT 學號,成績 FROM 選課 WHERE 課號="2" AND 成績>(SELECT MIN(成績 FROM 選課 WHERE 課號="1"方法二：ANY等價于SOME，所以可將ANY換成SOME。

14、SELECT 學號,成績 FROM 選課 WHERE 課號="2" AND 成績>ANY ;(SELECT 成績 FROM 選課 WHERE 課號="1"2、求選修2號課的學生中，成績比選修1號課的任何學生的成績都要高的那些學生的學號和成績。方法一：SELECT 學號,成績 FROM 選課 WHERE 課號="2" AND 成績>(SELECT MAX(成績 FROM 選課 WHERE 課號="1"方法二：SELECT 學號,成績 FROM 選課 WHERE 課號="2" AND 成

15、績>ALL ;(SELECT 成績 FROM 選課 WHERE 課號="1"（三）內外層互相關嵌套（外層依賴于內層的查詢結果，內層依賴于外層來進一步查詢）1、列出每門課程中成績最高的選課信息。SELECT * FROM 選課 A WHERE 成績=(SELECT MAX(成績 FROM 選課 B WHERE A.課號=B.課號2、列出每個學生中成績低于本人平均成績的選課信息。SELECT * FROM 選課 A WHERE 成績<(SELECT AVG(成績 FROM 選課 B WHERE A.學號=B.學號3、列出表“訂購單2.dbf”(其內容就是在訂購單表的

16、基礎上增加一個總金額字段）中每個職工經手的具有最高總金額的訂購單信息。（教案中例題）SELECT out.職工號,out.供應商號,out.訂貨單號,out.訂貨日期,out.總金額;FROM 訂購單 out WHERE 總金額=(SELECT MAX(總金額 FROM 訂購單 inner1;Where out.職工號=inner1.職工號四、操作功能1、在課程表中插入新的元組（5，大學英語）。INSERT INTO 課程(課號,課名 VALUES("5","大學英語"2、給學生表中男生的記錄加上刪除標記。DELETE FROM 學生 WHERE 性別=

17、"男"3、將選課表中所有課程的成績分別提高5分。將課號為“4”的課程的成績置為空值。UPDATE 選課 SET 成績=成績+5UPDATE 選課 SET 成績=NULL WHERE 課號="4"五、定義表結構1、建立研究生表，該表不屬于任何數據庫，其結構如表所示：字段名字段類型字段長度小數位數特殊要求學號C6姓名C8性別C2年齡N30入學年月D允許空值CREATE TABLE 研究生 FREE (學號 C(6,姓名 C(8,性別 C(2,年齡 N(3,0,入學年月 D NULL2、假設已經建立了XSK數據庫，在XSK中建立“學生1”表，其結構如表所示字段

18、名字段類型字段長度小數位數特殊要求學號C6主索引姓名C8不能為空性別C2年齡N30年齡大于10小于45是否黨員L入學年月D默認值為1999年9月1日OPEN DATABASE XSKCREATE TABLE 學生1 (學號 C(6 PRIMARY KEY,姓名 C(8 NOT NULL,性別 C(2,;年齡 N(3,0 CHECK 年齡>10 AND 年齡<45 ERROR "年齡必須大于10小于45",是否黨員 L,;入學年月 D DEFAULT 1999-9-13、在XSK數據庫建立“課程1”表，其結構如表所示：字段名字段類型字段長度小數位數特殊要求課號C4

19、主索引課程名C10不能為空學分N2CREATE TABLE 課程1 (課號 C(4 PRIMARY KEY,課程名 C(10 NOT NULL,學分 N(24、在XSK數據庫建立“選課1”表，其結構如表所示：字段名字段類型字段長度小數位數特殊要求學號C6外索引與學生表建立關系課號C4外索引與課程表建立關系成績N52CREATE TABLE 選課1 (學號 C(6 REFERENCE 學生,課號 C(4 REFERENCE 課程,成績 N(5,2六、修改表結構1、為“課程1”表添加一個開課學期字段，字段類型為數值型，長度為1。ALTER TABLE 課程1 ADD COLUMN 開課學期 N(1

20、2、修改開課學期字段為字符型，合法值為1或2。ALTER TABLE 課程1 ALTER COLUMN 開課學期 C(1 CHECK 開課學期="1" OR 開課學期="2"3、刪除“課程1”表中對開課學期字段的合法值約束，設置默認值為1。ALTER TABLE 課程1 ALTER 開課學期 DROP CHECKALTER TABLE 課程1 ALTER 開課學期 SET DEFAULT "1"4、刪除“課程1”表中的開課學期字段。修改課程名字段為“課名”字段。ALTER TABLE 課程1 DROP 開課學期 RENAME 課程名

21、TO 課名5、在“學生1”表的“年齡”字段上建立候選索引。ALTER TABLE 學生1 ADD UNIQUE 年齡 TAG 年齡6、在“學生1”表中添加一個出生日期字段，刪除年齡字段。ALTER TABLE 學生1 ADD 出生日期 DALTER TABLE 學生1 DROP UNIQUE TAG 年齡 DROP 年齡七、補充練習利用訂貨管理數據庫中表數據，用SQL完成查詢。1、檢索在北京的供應商的名稱。SELECT 供應商名 FROM 供應商 WHERE 地址="北京"2、檢索出向供應商S3發(fā)過訂購單的職工的職工號和倉庫號。方法一（嵌套查詢）：SELECT 職工號,倉庫號 FROM 職工 WHERE 職工號 IN;(SELECT 職工號 FROM 訂購單 WHERE 供應商號="S3"方法二（連接查詢）：SELECT 職工.職工號,倉庫號 FROM 職工,訂