數(shù)據(jù)庫復(fù)習(xí)題

1、習(xí)題一答案S B C D4494255261、設(shè)有關(guān)系R和S:R A B C TOC o 1-5 h z 642653568Qs, R xs , 2(S),曲 5 (R) , R S , R S223 AND SEX M ;(3)檢索學(xué)號(hào)為S3學(xué)生所學(xué)課程的課程名與任課教師名SELECT CNAME ,TEACHERFROM SC,CWHERE SC.C#=C.C# AND S#= S3;(4)檢索至少選修LIU老師所講授課程中一門課的女學(xué)生姓名SELECT SNAMEFROM S,SC,CWHERE S.S#=SC.S# AND SC.C#=C.C#AND SEX= F AND TERCHE

2、R= LIU(5)檢索WAN學(xué)不學(xué)的課程的課程號(hào)。SELECT C#FROM CWHERE NOT EXISTS(SELECT * FROM S,SC WHERE S.S#=SC.S# AND SC.C#=C.C#AND SNAME=WAN G);(6)檢索至少選修兩門課的學(xué)生學(xué)號(hào)。SELECT DISTINCT X.S# , FROM SC AS X,SC AS Y WHERE X.S#=Y.S# AND X.C#!=Y.C#;(7)檢索全部學(xué)生都選修的課程的課程號(hào)與課程名。SELECT C# ,CNAMEFROM CWHERE NOT EXISTS (SELECT * FROM S WHE

3、RE NOT EXISTS (SELECT * FROM SC WHERE S#=S.S# AND C#=C.C#);(8)檢索選修課程包含LIU老師所授全部課程的學(xué)生學(xué)號(hào)。SELECT DISTINCT S#FROM SC AS X WHERE NOT EXISTS (SELECT * FROM CWHERE TEACHER = LIU AND NOT EXISTS (SELECT * FROM SC AS YWHERE Y.S#=X.S# AND Y.C#=C.C#);習(xí)題二答案已知某教學(xué)管理數(shù)據(jù)庫中包含5個(gè)基本表：學(xué)生情況基本表 Students(Sno,Sname,Sage,Ssex,

4、 Sdept, Sclno)其中，Sclno 表示班別 課程情況基本表 Courses(Cno,Cname,Credit)選課情況基本表 Grade(Sno,Cno,Gmark)其中，Gmark表示成績(jī)教師任課情況基本表 TeachINg(Cno,Class,Tno,Snum) 其中，Snum是選修此課人數(shù)教師情況基本表 Teachers(Tno,Tname,Tage,Ps) 其中，Ps表示職稱完成下列問題：1、檢索至少選修課程號(hào)為 001和010的學(xué)生學(xué)號(hào)； SELECT SnoFROM Grade X GradeYWHERE X.Sno=Y.Sno AND X.Cno=001 AND Y.

5、Cno= 010;2、找出各課程的平均成績(jī)，按課程號(hào)分組，且只選擇學(xué)生超過10人的課程的成績(jī)；SELECT Cnq AVG(GmarRFROM Grade GROUP BY Cno HAVING COUNT (*) =10 ;3、檢索“王一平”老師講授的課程名及選修課程的學(xué)生人數(shù)； SELECT Cnamq eSnum FROM Courses , Teachers, Teaching WHERE Courses.Cno= Teaching.Cno AND Teachers.Tno= Teaching.Tno AND Teachers.Tname=王一平；4、統(tǒng)計(jì)“教授”職稱的教師人數(shù)及平均年

6、齡； SELECT COUNT*), AVG(Tage) FROM TeachersWHERE Ps =教授；5、列出各系的學(xué)生數(shù)(人數(shù)多的排在前面)； SELECT Sdept,COUNT(*) FROM Students GROUP BY Sdept ORDER BY COUNT(*) DESC6、找出各科成績(jī)均在85分以上(含85分)的學(xué)生的學(xué)號(hào)、姓名； SELECT Sno,SnameFROM Students WHERE Sno= (SELECT Sno FROM Grade WHERE Students.Sno=Grade.Sno GROUP BY Sno HAVING MIN(G

7、mark) =85 );7、找出計(jì)算機(jī)系至少有三門課程的成績(jī)?cè)?75分以下的學(xué)生的學(xué)號(hào)、姓名;SELECT Sno,Sname FROM Students WHERE Sno= (SELECT Sno FROM Grade WHERE Students.Sno=Grade.Sno AND Gmark=3 ) AND Sdept=計(jì)算機(jī)；8、列出學(xué)生的學(xué)號(hào)、姓名、數(shù)據(jù)庫成績(jī)，按照數(shù)據(jù)庫成績(jī)降序排列； SELECT Students.Sno,Sname,Gmark FROM Students,Grade,Courses WHERE Students.Sno=Grade.Sno AND Course

8、s.Cno=Grade.Cno AND Courses.Cname二數(shù)據(jù)庫ORDER BY Gmark DESC;9、查詢平均成績(jī)大于75分的學(xué)號(hào)、姓名、平均成績(jī)；SELECT Students.Sno,Sname,AVG(Gmark)FROM Students,GradeWHERE Students.Sno=Grade.SnoGROUP BY Students.Sno,Students.SnameHAVING AVG(Gmark)75;10、求其他班中比00312班某一學(xué)生年齡小的學(xué)生(即年齡小于 00312班年齡最大者的學(xué) 生)；SELECT *FROM StudentsWHERE Cln

9、o!=00312 AND Sageany(SELECT SageFROM StudentsWHERE Clno=00312);11、求其他班中比00312班學(xué)生年齡都小的學(xué)生；SELECT *FROM StudentsWHERE Clno!=00312 AND Sage(SELECT Gmark FROM Grade,Students WHERE Grade.Sno=Students.SnoAND Students.Sname=李勇AND Cno=1);15、求選修了高等數(shù)學(xué)的學(xué)號(hào)和姓名；SELECT Sno,SnameFROM StudentsWHERE Sno IN(SELECT SnoF

10、ROM Grade,CoursesWHERE Grade.Cno=Courses.CnoAND Courses.Cname=高等數(shù)學(xué))；16、查詢選修了全部課程的學(xué)生姓名；SELECT Sname FROM StudentsWHERE NOT EXISTS(SELECT * FROM CoursesWHERE NOT EXISTS(SELECT * FROM GradeWHERE Grade.Cno=Courses.CnoAND Grade.Sno=Students.Sno);17、檢索“張雨”老師講授的課程名及選修課程的學(xué)生人數(shù)；SELECT Cname,SnumFROM Courses,T

11、eaching,TeachersWHERE Teaching.Tno=Teachers.TnoAND Teaching.Cno=Courses.Cno AND Tname=張雨；18、統(tǒng)計(jì)“講師”職稱的教師人數(shù)及平均年齡；SELECT COUNT(*),AVG(Sage)FROM TeachersWHERE Ps =講師;19、查出課程成績(jī)?cè)?0分以上的女學(xué)生的姓名、課程名和成績(jī)；SELECT Sname,Cname,GmarkFROM Students,Courses,GradeWHERE Students.Sno=Grade.Sno AND Grade.Cno=Courses.CnoAND

12、 Grade.Gmark=80 AND Sex=女；20、找出學(xué)生的平均成績(jī)和所學(xué)課程門數(shù)；SELECT Sno,AVG(Gmark),COUNT(*)FROM GradeGROUP BY Sno;21、找出年齡超過平均年齡的學(xué)生姓名；SELECT Sname FROM StudentsGROUP BY SnameHAVING SageAVG(Sage);22、查詢哪些課程只有男生選讀。SELECT DISTINCT Cno FROM Courses WHERE 男=ALL(SELECT Ssex FROM Grade,Students WHERE Grade.Sno=Students.Sno

13、 AND Grade.Cno=Courses.Cno);習(xí)題三答案1、請(qǐng)使用Transact-SQL建立如下表：表名：雇員基本信息表；字段名數(shù)據(jù)類型寬度 精度可空性要求NO_IDCHAR7NOT NULL設(shè)為主鍵NAMECHAR10NOT NULLSEXCHAR2NOT NULL默認(rèn)值為男AGE INTNOT NULLDEPTVARCHAR 50NULLPHONE VARCHAR 20NULLNOTESVARCHAR50NULL1、create table雇員基本信息表(no_id char not null, name char(10) not null, sex char(2) not n

14、ull, age int not null default男,dept varchar(50), phone varchar(20), notes varchar(50), primary key(no_id)2、下面是一個(gè)電子商京網(wǎng)站購物模式，包括了三個(gè)表(主碼用下劃線標(biāo)出)客戶表：Client (Cno, Cname Csex, Cage, Cclass), Cclass 是客戶類別商品表：Goods (Gno Gname Gpnq Gprice ), Gpno是贈(zèng)品號(hào)購物表：CG(Cno, Gno Time, Value), Value是客戶對(duì)這次購物的評(píng)分(10分制) 備注：有的客戶只

15、登記，但尚未購買商品。(1)查詢購買了商品的客戶號(hào)及姓名。(2)查詢1980年出生的客戶號(hào)及姓名。(3)購買了三種以上商品的客戶號(hào)及客戶姓名。2、(1) select Cno,Cname from Client where Cno in (select distinct Cno from CG)select Cno,Cname from Clientwhere Cage=26select Cno,Cname from Client where Cno in (select Cnofrom CG group by Cno having count(*)3)3、現(xiàn)有一銷售表，它們結(jié)構(gòu)如下：id i

16、nt(標(biāo)識(shí)號(hào))codno char(codname varchar(30) spec varchar(20) price numeric(10,2) sellnum intdeptno char(3) selldate datetime冏品編碼)(商品名稱)(商品規(guī)格)(價(jià)格)(銷售數(shù)量)(售出分店編碼)(銷售時(shí)間)要求：寫出查詢銷售時(shí)間段在 2002-2-15日到2002-4-29之間，分店編碼是01的 所有記錄。3、select * from 銷售表where selldate between 2002-2-15 and 2002-4- 29 and deptno=014、設(shè)有三個(gè)關(guān)系：學(xué)

17、生關(guān)系S (SNO SNAMEAGE SEX,主碼為SNO學(xué)習(xí)關(guān)系SC (SNO CNO GRAC 主碼為(SNO CNO 課程關(guān)系C (CNO CNAM,ETEACHBR主碼為CNO試用SQL句表示下列查詢語句：檢索選修課程包括ZHAg師所授課程的學(xué)生學(xué)號(hào)。4、select distinct SNOfrom SCwhere CNO in(select CNOfrom Cwhere TEACHER =ZHA )5、已知某教學(xué)管理數(shù)據(jù)庫包含三個(gè)基本表：學(xué)生基本信息表 S (S#, SNAME AGE SEX選修課程成績(jī)信息表 SC (S#, C#, GRADE選修課程基本信息表C (C#, CN

18、AM,ETEACHE)R試用SQLS詢語句表達(dá)下列對(duì)教學(xué)數(shù)據(jù)庫中三個(gè)基本表S、SG C的查詢:(1)統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)；(2)求選修C4課程的學(xué)生的平均年齡；(3)求ZHANGS師所授課程的每門課程的學(xué)生平均成績(jī)；(4)在基本表S中插入一個(gè)學(xué)生元組(S9, WU, 18)。(5)把選修MATHS!不及格的成績(jī)?nèi)臑榭罩怠?1) SELECT COUNTDISTINCT C#)FROM SCSELECT AV GAGEFROM S , SCWHERE C#= C4 AND S.S#=SC.S#SELECT SC.C#,CNAME,AVG(GRADE)FROM SC,CWHERE SC.C#

19、=C.C# AND TEACHER=GROUP BY SC.C#INSERT INTO S(S#,SNAME,AGE)VALUES(S9 , WU ,18)UPDATE SC SET GRADE =NULLWHERE GRADE=10 (3) SELECT CNAMESNUM FROM COURSES , TEACHERSTEACHING WHERE COURSES.CNO=TEACHING.CNOAND TEACHERS.TNO=TEACHING.TNO AND TEACHERS.TNAME=一平SELECT COUNT*), AVG(AGE FROM TEACHERS WHERE PS=教

20、授7、供應(yīng)商-零件-工程項(xiàng)目數(shù)據(jù)庫由以下四個(gè)關(guān)系模式構(gòu)成：S (SNO SNAMESTATUS CITY)P (PNO PNAME COLOR WEIGHT CITY)J (JNQ JNAME CITY)SPJ (SNO PNO JNQ QTY供應(yīng)商S,零件P和工程項(xiàng)目J分別由供應(yīng)商號(hào)(SNO,零件號(hào)(PNO和工程項(xiàng)目號(hào) (JNQ唯一標(biāo)識(shí)。供貨SPJ是指由某個(gè)供應(yīng)商向某個(gè)工程項(xiàng)目供應(yīng)某些數(shù)量的某種零件。 請(qǐng)用SQL言完成如下的操作：(1)找出給北京的工程項(xiàng)目提供不同的零件號(hào)；(2)將沒有供貨的所有工程項(xiàng)目從 J中刪除；(3)查詢提供全部零件的供應(yīng)商名；(4)查詢這樣的工程項(xiàng)目號(hào)：供給該工程項(xiàng)目

21、的零件P1的平均供應(yīng)量大于供給工程J1 的任何一種零件的最大供應(yīng)量；一個(gè)視圖，它由所有這樣的工程項(xiàng)目(工程項(xiàng)目號(hào)與所在城市名稱)組成：它們由 供應(yīng)商S1供貨且使用零件P1。(1) SELECT DISTINCT SPJ.PNOFROM SPJ,JWHERE SPJ.JNO=J.JNO AND J.CITY=1 匕京DELETE FROM JWHERE JNO NOT IN (SELECT JNO FROM SPJ)SELECT SNAME FORM SWHERE NOT EXISTS (SELECT * FROM PWHERE NOT EXISTS (SELECT * FROM SPJWHER

22、E SNO=S.SNO AND PNO=P.PNO) (4) SELECT DISTINCT JNO FROM SPJ WHERE PNO= P1 GROUP BY JNOHAVING AVG(QTY) (SELECT MAX(QTY) FROM SPJ WHERE JNO = J1)CREATE VIEW J_S1_P1AS SELECT J.JNO,J.CITY FROM SPJ,JWHERE SPJ.JNO=J.JNO AND SPJ.SNO= S1 AND SPJ.PNO= P18、設(shè)有學(xué)生表S (SNO SN) (SNCfe學(xué)生號(hào)，SN為姓名)和學(xué)生選修課程表 SC(SN。CNOCN

23、 G) (CNO%課程號(hào)，CN為課程名，G為成績(jī))，試用SQL語言完成以下各題：(1)建立一個(gè)視圖 V_SSC(SNO,SN,CNO,CN,G沈按 CNOR序排序；(2)從視圖V_SSCh查詢平土成線在90分以上的SN CNffi G(1) CREATE VIEW V_SSC(SNO,SN,CNO,CN,G)AS SELECT S.SNO,S.SN,CNO,SC.CN,SC.GFROM S,SCWHERE S.SNO=SC.SNOORDER BY CNOSELECT SN,CN,GFROM V_SSCGROUP BY SNOHAVING AVG(G)909、今有如下關(guān)系數(shù)據(jù)庫：S (SNO S

24、NAMESTATUS CITY)P (PNO PN COLOR WEIGHTJ (JNQ JN, CITY)SPJ (SNO PNO JNQ QTY其中，S為供應(yīng)單位，P為零件，J為工程，SPJ為工程訂購零件的訂單，其語義為：某供應(yīng)單位供應(yīng)某種零件給某個(gè)工程，請(qǐng)用SQLI成下列操作。(1)求為工程J1提供紅色零件的供應(yīng)商代號(hào)。(2)求使用S1供應(yīng)的零件的工程名稱。(3)求供應(yīng)商與工程所在城市相同的供應(yīng)商提供的零件代號(hào)。(4)求至少有一個(gè)和工程不在同一城市的供應(yīng)商提供零件的工程代號(hào)。(1) SELECT DISTINCT SPJ.SNOFROM SPJ,PWHERE P.PNO=SPJ.PNO

25、AND SPJ.JNO = J1 AND P.COLOR =紅SELECT J.JNFROM J,SPJWHERE J.JNO=SPJ.JNO AND SPJ.SNO= S1SELECT DISTINCT SPJ.PNOFROM S,J,SPJWHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY=J.CITYSELECT DISTINCT SPJ.JNOFROM S,J,SPJWHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITYJ.CITY10、已知某公司數(shù)據(jù)庫中包含四個(gè)基本表：部門情況基本表 Depart

26、ment(Dept_no,Dept_name,Location)人員情況基本表 Employee(Emp_no,Emp_name,Dept_no)項(xiàng)目情況基本表 Project(Pro_no,Pro_name,Budget)參加各項(xiàng)目的人員工作情況基本表 Works(Emp_no,Pro_no,Job)試用SQL DDL語句定義上述四個(gè)基本表，并說明王鍵和外鍵。(1) CREATE TABLE Department(Dept_no CHAR(4),Dept_name CHAR(10) NOT NULL,Location CHAR(20),PRIMARY KEY (Dept_no)CREATE

27、TABLE Employee(Emp_no INTEGER,Emp_name CHAR(8) NOT NULL,Dept_no CHAR(4),PRIMARY KEY (Emp_no),FOREIGN KEY (Dept_no) REFERENCES Department (Dept_no)CREATE TABLE Project(Pro_no CHAR(4),Pro_name CHAR(20) NOT NULL,Budget REAL,PRIMARY KEY (Pro_no)CREATE TABLE Works(Emp_no INTEGER,Pro_no CHAR(4),Job CHAR(2

28、0),PRIMARY KEY (Emp_no,Pro_no),FOREIGN KEY (Emp_no) REFERENCES Employee(Emp_no), FORGEIGN KEY(Pro_no) REFERENCES Project(Pro_no) 習(xí)題四答案1、有關(guān)系模式：student(學(xué)號(hào)，姓名，所在系，班號(hào)，班主任，系主任)，其語義為：一 個(gè)學(xué)生只在一個(gè)系的一個(gè)班學(xué)習(xí)，一個(gè)系只有一個(gè)系主任，一個(gè)班只有一個(gè)班主任。指出此關(guān)系模式的候選碼，判斷此關(guān)系模式是第幾范式，若不是第三范式，轉(zhuǎn)化為第三范式關(guān)系模式，并指出分解后的每個(gè)關(guān)系模式的主碼和外碼。答：候選碼為：學(xué)號(hào)此關(guān)系模式為第二范式

29、。因?yàn)閷W(xué)號(hào)一班號(hào)，班號(hào)一班主任，學(xué)號(hào)一所在系，所在系一系主任，存在傳遞函數(shù)依賴。不是3NF,分解：Student(學(xué)號(hào)，姓名，所在系，班號(hào))Class(班號(hào)，班主任)Department所在系，系主任)2、設(shè)有關(guān)系模式：授課表(課程號(hào)，課程名，學(xué)分，授課教師號(hào)，教師名，授課時(shí)數(shù))，其語義為：一門課程可以由多名教師講授，一名教師可以講授多門課程，每個(gè)教師對(duì)每門 課程有唯一的授課時(shí)數(shù)。指出此關(guān)系模式的候選碼，判斷此關(guān)系模式是第幾范式，若不是第三范式，轉(zhuǎn)化為第 三范式關(guān)系模式，并指出分解后的每個(gè)關(guān)系模式的主碼和外碼。答案：此關(guān)系模式的候選碼(課程號(hào)，授課教師號(hào))此關(guān)系模式為第一范式。課程號(hào)一課程名，

30、授課教師號(hào)一教師名，(課程號(hào)，授課教師號(hào))一授課時(shí)數(shù)，存在部分函數(shù)依賴。不是2NF,更不是3NF,分解課程表(課程號(hào)，課程名，學(xué)分)教師表(授課教師號(hào)，教師名)授課表(課程號(hào)，授課教師號(hào)，授課時(shí)數(shù))3、在函數(shù)依賴范圍內(nèi)，試問下列關(guān)系模式最高屬于第幾范式。并解釋原因R （A, B, C, D）, F=B-D, ABHC候選碼為（A,B） 1NFR （A, B, C, D, E）, F=AACE, E-AB, CD候選碼為：（A,B） 和E, 2NFR （A, B, C）, F=A-B, B- A, 2C候選碼：（A）和（B） BCNFR （A, B, C, D）, F=A-C, AB候選碼:（A

31、,D）因?yàn)榇嬖诓糠趾瘮?shù)依賴；1NF.R （A, B, C）, F=A-B, Bf A, gA候選碼：C 2NF,因?yàn)榇嬖趥鬟f函數(shù)依賴；4.4、設(shè)某商業(yè)集團(tuán)數(shù)據(jù)庫中有 3個(gè)實(shí)體集。一是“商店”實(shí)體集，屬性有商店編號(hào)、商店 名、地址等；二是“商品”實(shí)體集，屬性有商品號(hào)、商品名、規(guī)格、單價(jià)等；三是“職工” 實(shí)體集，屬性有職工編號(hào)、姓名、性別、業(yè)績(jī)等。冏店與冏品存在“銷售”聯(lián)系，每個(gè)冏店可銷售多種冏品，每種冏品也可以放在多個(gè) 商店銷售，每個(gè)商店每銷售一種商品，有月銷售量；商店與職工間存在著“聘用”聯(lián)系， 每個(gè)商店有許多職工，每個(gè)職工只能在一個(gè)商店工作，商店聘用職工有聘期和月薪。畫出E-R圖，并注明屬性、聯(lián)系的類型。5、將E-R圖轉(zhuǎn)換為關(guān)系模式，并指出每個(gè)關(guān)系模式的主碼和外碼圖書表（書名，作者，出版日期）讀者表（讀者編號(hào)，讀者姓名，單位，聯(lián)系電話）借閱表（書號(hào)，讀者編號(hào)，借書日期）書號(hào)和讀者編號(hào)分別是外碼。6、將E-R圖轉(zhuǎn)換為關(guān)系模式，并指出每個(gè)關(guān)系模式的主碼和外碼顧客表（顧客號(hào),顧客名，聯(lián)系電話）商品表（商品編號(hào)，商品名稱，商