北大數(shù)據(jù)庫(kù)原理上機(jī)考題練習(xí)及參考答案練習(xí)二(學(xué)生、班、系)

1、題目：(1)現(xiàn)要為學(xué)校建立一個(gè)數(shù)據(jù)庫(kù)， 個(gè)班，每個(gè)班有若干個(gè)學(xué)生。設(shè)學(xué)校環(huán)境如下： 一個(gè)系有若干個(gè)專業(yè)， 每個(gè)專業(yè)一年只招一 . 現(xiàn)要建立關(guān)于系、學(xué)生、班級(jí)的數(shù)據(jù)庫(kù)，關(guān)系模式為：班 CLASS （班號(hào)，專業(yè)名，系名，入學(xué)年份）學(xué)生 STUDENT （學(xué)號(hào)，姓名，年齡，班號(hào)） 系 DEPARTMENT （系號(hào)，系名） 試用 SQL 語(yǔ)言完成以下功能：建表，在定義中要求聲明：1 ）每個(gè)表的主外碼。2 ）每個(gè)班級(jí)的人數(shù)不能超過(guò)30 人。3 ）學(xué)生的年齡介于 15 到 40 歲之間。4 ）學(xué)生姓名不能為空。5 ）只有班里沒(méi)有學(xué)生的時(shí)候，才能刪除該班級(jí)。插入如下數(shù)據(jù)CLASS(101，軟件，計(jì)算機(jī)，19

2、95 ；102 ，微電子，計(jì)算機(jī)，1996 ；111，無(wú)機(jī)化學(xué)，化學(xué)，1995 ；112，高分子化學(xué)，化學(xué)，1996121，統(tǒng)計(jì)數(shù)學(xué)，數(shù)學(xué)，1995 ；131，現(xiàn)代語(yǔ)言，中文，1996 ；141，國(guó)際貿(mào)易，經(jīng)濟(jì)，1997 ；142，國(guó)際金融，經(jīng)濟(jì)，1996 ；）STUDENT (8101 ，張三，18 ，101 ； 8102 ，錢四，16 ，121 ；8103 ，王玲，17 ，131 ； 8105 ，李飛，19 ，102 ；8109 ，趙四，18 ，141 ； 8110 ，李可，20 ，1428201 ，張飛，18 ，111 ； 8302 ，周瑜，16 ，112 ；8203 ，王亮，17 ，1

3、11 ； 8305 ，董慶，19 ，102 ；8409 ，趙龍，18 ，101 ； 8510 ，李麗，20 ，142 )DEPARTMENT(001 ，數(shù)學(xué)； 002 ， 計(jì)算機(jī)；003 ，化學(xué)；004 ， 中文；005 ，經(jīng)濟(jì)；）完成以下查詢功能31 ）找出所有姓李的學(xué)生，并按其年齡由小到大排序。2 ）列出所有開(kāi)設(shè)超過(guò)兩個(gè)專業(yè)的系的名字。3 ）列出學(xué)生人數(shù)大于等于2 的系的編號(hào)和名稱。學(xué)校又新增加了一個(gè)物理系，編號(hào)為006 。學(xué)生張三轉(zhuǎn)到化學(xué)系 111班，請(qǐng)更新相關(guān)的表。參考答案：如下是個(gè)人的做的參考答案，有問(wèn)題的話多多指正。(1)create table student(stuno cha

4、r(8) not null, stuname char(15) not null,age smallint,classno char(8) ,primary key (stuno),foreign key (classno) references class(classno) on delete cascade,check (age between 15 and 40) create table class(class no char(8) not nu II, zhua nye char(15),dep artme ntn ame char(10), startyear int,p rima

5、ry key (class no) create table dep artme nt(dep artme ntno char(8) not n ull, dep artme ntn ame char(15), p rimary key (de partme ntno)插入值：insert into class (class no,zhua ny e,de partme ntn ame,startyear) values (101軟件,計(jì) 算機(jī),1995);insert into class (class no,zhua ny e,de partme ntn ame,startyear) va

6、lues (102微電子, 計(jì)算機(jī),1996);insert into class (classno,zhuanye,departmentname,startyear)values (111,無(wú)機(jī)化 學(xué),化學(xué),1995);insert into class (classno,zhuanye,departmentname,startyear)values (112,高分子 化學(xué),化學(xué),1996);insert into class (classno,zhuanye,departmentname,startyear)values (121,統(tǒng)計(jì)數(shù) 學(xué),數(shù)學(xué),1995);insert into cl

7、ass (classno,zhuanye,departmentname,startyear)values (131,現(xiàn)代語(yǔ) 言,中文,1996);insert in to class (class no ,zhua nye,de partme ntn ame,startyear)values (141,國(guó)際貿(mào) 易,經(jīng)濟(jì),1997);insert into class (classno,zhuanye,departmentname,startyear)values (142,國(guó)際金 融,經(jīng)濟(jì),1996)select * from sysobjects where xtype = U知識(shí)補(bǔ)充：查詢

8、用戶表+! sfudenf .nsert nsert nsert nsert nsert nsert nsert nsert nsert nsert nsert nsertnfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf adepartment 帀insertinsertinsertinsertues(82 ues(

9、802 ues(803 ues(805 ues(809 ues(8=0 ues(822 ues(8302 ues(8203 ues(-8305 ues(8409 ues(850顒您田畫(huà)耒顒您顒田梵耒a莒洱羽雪凹S 妙S山|O_x_x_x_x_x|o_x_x_x_x_x089768089768 士 S 竺 E 22 *nfo DEPARTMENTS_ues(oor - = nfo DEPARTMENT a_ues(002- - 言-)- nfo DEPARTMENT a_ues(003-去強(qiáng)-)- nfo DEPARTMENT a_ues(004 -召洱-)-insert Ho DEPARTMENT a_ues(005 -歧一審-)-(3)se-ecf * from sfudenf where sfuname -ike -顒order by age asGse-ecf departmenfname from c-ass group by departmenfname having counf(*) VH2八se-ecf d.departmenfno- d.departmenfname from c-ass c-sfudenf s-departmenf dwhere c.c-assno H s.c-assno and d.departmenfname H c.depa