【實驗報告1】

一、填空題

二、主觀題

1. 畫出學(xué)校與校長關(guān)系的E-R圖。

2. 畫出班級與學(xué)生關(guān)系的E-R圖。

3. 某汽車運輸公司數(shù)據(jù)庫E-R圖：

設(shè)某汽車運輸公司數(shù)據(jù)庫中有三個實體。

一是“車隊”實體，屬性有車隊號、車隊名等；

二是“車輛”實體，屬性有牌照號、廠家、出廠日期等；

三是“司機”實體，屬性有司機編號、姓名、電話等。

其中：設(shè)每個車隊可聘用若干司機，但每個司機只能應(yīng)聘于一個車隊，車隊聘用司機有聘期；每個車隊可擁有若干車輛，但每輛車只能屬于一個車隊；每個司機可使用多輛汽車，每輛汽車可被多個司機使用，司機使用車輛需登記使用日期和公里數(shù)。

1、根據(jù)題意畫出ER圖，并在圖上注明實體、屬性、聯(lián)系及聯(lián)系的類型。

2、將E-R圖轉(zhuǎn)換為等價的關(guān)系模式，并指出每個關(guān)系的主碼和外碼。

4. 軟件公司信息管理系統(tǒng)E-R圖。

寧波有一軟件公司為某田徑錦標賽設(shè)計一個信息管理系統(tǒng)。經(jīng)過分析該系統(tǒng)有如下3個實體：

運動隊，屬性有隊號、隊名、教練姓名；

運動員，屬性包括運動員號、姓名、性別、年齡；

比賽項目，屬性有項目名、比賽場地。

其中：一個運動隊有一個教練，多個運動員；一個運動員僅屬于一個運動隊；一個比賽項目有多個運動員參加，一個運動員可以參加多個項目，對每個運動員的每一個參賽項目均記錄名次。

根據(jù)以上情況，請完成如下任務(wù)：

1、設(shè)計田徑錦標賽管理系統(tǒng)的E-R圖；

2、將E-R圖轉(zhuǎn)換為等價的關(guān)系模式，并指出每個關(guān)系的主碼和外碼。

【實驗報告2】

1. 用Navicat管理工具提供的圖形界面創(chuàng)建表。

在JXGL數(shù)據(jù)庫中創(chuàng)建表students表和sc表。表結(jié)構(gòu)要求見圖，僅建表，先不添加約束。

2. 用Navicat圖形界面操作修改表結(jié)構(gòu)。

(1) 向students表中增加“入學(xué)日期”列(enrollment)，其數(shù)據(jù)類型為日期型。

(2) 將students表中的sdept字段長度改為20。

(3) 將students表中的sdept字段改為不允許為空。

(4) 將students表中的speciality字段改名為spec。

(5) 將students表中的spec字段刪除。

注：完成效果發(fā)來截圖上傳附件

3. 利用create table 命令創(chuàng)建course表，表結(jié)構(gòu)見下圖，僅建表，先不添加約束。

4. 用Navicat圖形界面操作給表格添加約束。

(1) 給students表和course表添加主鍵。

(2) 給students表的sname字段設(shè)置非空約束。

(3) 給sc表添加外鍵，關(guān)聯(lián)到students表和course表。

(4) 給students表的IDNum字段設(shè)置唯一性約束。

(5) 給students表中的ssex字段設(shè)置默認約束“男”。

5. 在students表、course表、sc表中錄入一些數(shù)據(jù)，每個表錄入3條數(shù)據(jù)，截圖上傳附件。

【實驗報告3中（一）簡單查詢】

10-1 查詢所有學(xué)生的學(xué)號、姓名、性別和出生日期

select sno,sname,ssex,bday from students;

?

10-2 查詢前3門課程的課號及課程名稱

select cno,cname from course limit 0,3;

?

10-3 查詢2050年所有學(xué)生的姓名及年齡，要求結(jié)果中列名顯示中文

select sname 姓名,2050-year(bday) 年齡 from students;

?

10-4 查詢至2050年所有年齡小于等于55歲的女生的學(xué)號和姓名

select sno 學(xué)號,sname 姓名,ssex 性別 from students where 2050-year(bday)<=55 and ssex='女';

?

10-5 查詢“信息學(xué)院”的學(xué)生姓名、性別和出生日期

select sname,ssex,bday from students where sdept='信息學(xué)院';

?

10-6 查詢Students表中的所有系名，要求結(jié)果中系名不重復(fù)

select distinct sdept from students;

?

10-7 查詢“0000010”課程的課名、先修課號和學(xué)分

select cname,cpno,ccredit from course where cno='0000010';

?

10-8 查詢成績在80～90分之間的選課成績情況

select * from sc where score>=80 and score<=90;

?

10-9 查詢成績?yōu)?9分、79分或89分的記錄

select * from sc where score in(69,79,89);

?

10-10 查詢在1970年1月1日之前出生的男教師信息

select * from teachers where tbirthday<='1970-1-1' and tsex='男';

?

10-11 輸出有成績的學(xué)生學(xué)號和課號

select sno,cno from sc where score is not null;

?

10-12 查詢所有姓“劉”的學(xué)生信息

select * from students where sname like '劉%';

?

10-13 查詢生源地不是“山東”省的學(xué)生信息

select * from students where bplace not like '山東%';

?

10-14 查詢名字中含有“明”字的男生的學(xué)生姓名和班級

select sname,class from students where sname like '%明%';

?

10-15 查詢姓名是兩個字的學(xué)生信息

select * from students where sname like '__';

?

10-16 查詢非信息學(xué)院和機電學(xué)院的學(xué)生信息

select * from students where sdept not like '信息學(xué)院' and sdept not like '機電學(xué)院';

?

10-17 查詢學(xué)生表中沒有聯(lián)系電話的學(xué)生信息

select * from students where phone is null;

?

【實驗報告3中（二） 簡單查詢（二）及聚合函數(shù)】

10-1 從學(xué)生表統(tǒng)計總的學(xué)生人數(shù)

select count(*) 人數(shù) from students;

?

10-2 統(tǒng)計有學(xué)生選修的課程的門數(shù),多人選修同一門只算一門

select count(distinct cno) 門數(shù) from sc;

?

10-3 計算“0000001”課程的平均分、最高分和最低分

select avg(score) 平均分, max(score) 最高分, min(score) 最低分 from sc where

cno='0000001';

?

10-4 查詢選修了“0000008”課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分數(shù)降序排列

select sno,score from sc where cno='0000008' order by score desc;

?

10-5 查詢成績不及格的學(xué)生學(xué)號、課號和成績，并按成績降序排列

select sno,cno,score from sc where score<60 order by score desc;

【實驗報告3中（二） 分組查詢】

10-1 查詢各個課程號及相應(yīng)的選課人數(shù)

select cno 課程號,count(sno) 選課人數(shù) from sc group by cno;

?

10-2 統(tǒng)計每門課程的選課人數(shù)和最高分

select cno 課程號, count(sno) 選課人數(shù), max(score) 最高分 from sc group by cno;

?

10-3 查詢選修了3門以上課程的學(xué)生學(xué)號

select sno 學(xué)號, count(cno) 選課門數(shù) from sc group by sno having count(cno)>3;

?

10-4 統(tǒng)計輸出各系學(xué)生的人數(shù)

select sdept 系, count(sno) 人數(shù) from students group by sdept;

?

10-5 統(tǒng)計每個學(xué)生的選課門數(shù)和考試總成績，并按選課門數(shù)升序排列

select sno 學(xué)號, count(cno) 選課門數(shù), sum(score) 考試總成績 from sc group by sno order by 選課門數(shù) asc;

?

10-6 統(tǒng)計各系的男、女生人數(shù)

select sdept 系別, ssex 性別, count(sno) 人數(shù) from students group by sdept,ssex order by sdept;

?

10-7 統(tǒng)計各班男、女生人數(shù)

select class 班級, ssex 性別, count(sno) 人數(shù) from students group by class,ssex order by class;

?

10-8 統(tǒng)計各系的老師人數(shù)，并按人數(shù)升序排序

select tdept 系別, count(tno) 教師人數(shù) from teachers group by tdept order by count(tno) asc;

?

10-9 統(tǒng)計不及格人數(shù)超過3人的課程號和人數(shù)

select cno 課程號, count(*) 不及格人數(shù) from sc where score<60 group by cno having count(*)>3;

?

10-10 查詢信息學(xué)院的男生信息，查詢結(jié)果按出生日期升序排序，出生日期相同的按生源地降序排序

select * from students where sdept='信息學(xué)院' and ssex='男' order by bday, bplace desc;

?

10-11 統(tǒng)計選修人數(shù)最多的3門課

select cno 課程號, count(*) 選修人數(shù) from sc group by cno order by count(*) desc limit 0,3;

【實驗報告4中（一） 多表連接查詢】

10-1 查詢信息學(xué)院女學(xué)生的學(xué)生學(xué)號、姓名、課號及考試成績

select students.sno,sname,cno,score from students join sc on students.sno=sc.sno where sdept='信息學(xué)院' and ssex='女';

?

10-2 查詢“陳紅”同學(xué)所選課程的成績，列出課號和成績(不考慮重名)

select cno,score from sc join students on sc.sno=students.sno where sname='陳紅';

?

10-3 查詢“王珊”老師所授課程的課程名稱

select distinct cname from course inner join teaching on course.cno=teaching.cno inner join teachers on teaching.tno=teachers.tno where teachers.tname='王珊';

?

10-4 查詢女教師所授課程的課程號和課程名稱

select distinct course.cno,cname from course inner join teaching on course.cno=teaching.cno inner join teachers on teaching.tno=teachers.tno where tsex='女';

?

10-5 查詢至少選修2門課程的女生姓名

select students.sname from students join sc on students.sno=sc.sno where students.ssex='女' group by sname having count(*)>2;

?

10-6 查詢選修課名中含有“數(shù)據(jù)庫”三個字的課程且成績在80～90分之間的學(xué)生學(xué)號及成績

select sno,score from sc left join course on sc.cno=course.cno where cname like '%數(shù)據(jù)庫%' and score between 80 and 90;

?

10-7 查詢選修“0000011”課程的學(xué)生至2050年時平均年齡

select avg(2050-year(bday)) 平均年齡 from students join sc on students.sno=sc.sno where cno='0000011';

?

10-8 查詢“譚浩強”教師任課的課程號，選修其課程的學(xué)生的學(xué)號和成績，結(jié)果中包括該老師沒有被選修的課程

select teaching.cno,sno,score from teaching left join sc on teaching.cno=sc.cno left join teachers on teaching.tno=teachers.tno where tname='譚浩強';

?

10-9 列出所有學(xué)生的選課情況（包括學(xué)號，姓名，課號，成績），結(jié)果中包括沒有選課的學(xué)生

select students.sno,sname,cno,score from sc right outer join students on sc.sno=students.sno;

?

10-10 查詢沒有選課的學(xué)生學(xué)號和姓名

select sno,sname from students where sno not in(select sno from sc);

【實驗報告4中（二） 嵌套子查詢】

10-1 查詢信息學(xué)院學(xué)生所選修的課程號和成績

select cno,score from sc where sno in (select sno from students where sdept='信息學(xué)院');

?

10-2 查詢與“陸毅”同一個系的同學(xué)姓名

select sname from students where sdept=(select sdept from students where sname='陸毅') and sname!='陸毅';

?

10-3 查詢“19信管2”班的學(xué)生所選修的課程號

select cno from sc where sno in (select sno from students where class='19信管2');

?

10-4 查詢“陳曉東”同學(xué)所選課程的課號及成績

select cno,score from sc where sno in (select sno from students where sname='陳曉東');

?

10-5 查詢選修了課號為“0000034”的學(xué)生學(xué)號和姓名

select sno,sname from students where sno in (select sno from sc where cno='0000034');

?

10-6 查詢“0000008”號課程不及格的學(xué)生信息

select * from students where sno in (select sno from sc where cno='0000008' and score<60);

?

10-7 查詢李小鵬同學(xué)所選課程名稱

select cname from course where cno in (select cno from sc where sno in (select sno from students where sname='李小鵬'));

?

10-8 查詢“王珊”老師所授課程的課程名稱

select cname from course where cno in (select cno from teaching where tno in (select tno from teachers where tname='王珊'));