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

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

2、95 ；102 ，微電子，計算機，1996 ；111，無機化學(xué)，化學(xué)，1995 ；112，高分子化學(xué)，化學(xué)，1996121，統(tǒng)計數(shù)學(xué)，數(shù)學(xué)，1995 ；131，現(xiàn)代語言，中文，1996 ；141，國際貿(mào)易，經(jīng)濟，1997 ；142，國際金融，經(jīng)濟，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 ， 計算機；003 ，化學(xué)；004 ， 中文；005 ，經(jīng)濟；）完成以下查詢功能31 ）找出所有姓李的學(xué)生，并按其年齡由小到大排序。2 ）列出所有開設(shè)超過兩個專業(yè)的系的名字。3 ）列出學(xué)生人數(shù)大于等于2 的系的編號和名稱。學(xué)校又新增加了一個物理系，編號為006 。學(xué)生張三轉(zhuǎn)到化學(xué)系 111班，請更新相關(guā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軟件,計 算機,1995);insert into class (class no,zhua ny e,de partme ntn ame,startyear) va

6、lues (102微電子, 計算機,1996);insert into class (classno,zhuanye,departmentname,startyear)values (111,無機化 學(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)計數(shù) 學(xué),數(shù)學(xué),1995);insert into cl

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

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顒您田畫耒顒您顒田梵耒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-去強-)- 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