SQL語句強(qiáng)化練習(xí)題及答案_百度文庫

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

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

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

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

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

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

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

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

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

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

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

12、套與謂詞EXISTS1、列出選修匯編語言課的學(xué)生的學(xué)號。方法一：SELECT 學(xué)號 FROM 選課 WHERE 課號=(SELECT 課號 FROM 課程 WHERE 課名="匯編語言"方法二：使用謂詞EXISTS。注意和方法一格式上的不同。SELECT 學(xué)號 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號課的學(xué)生中，成績比選修1號課的最低成績要高的學(xué)生的學(xué)號和成績。方法一：SELECT 學(xué)號,成績 FROM 選課 WHERE 課號="2" AND 成績>(SELECT MIN(成績 FROM 選課 WHERE 課號="1"方法二：ANY等價(jià)于SOME，所以可將ANY換成SOME。

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

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

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

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

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

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

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

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