資料庫上機試驗（二）

     這是第二次上機内容以及結果，大家在運作程式的時候，第一次是可以直接運作的，但第二次運作的時候要把原來建的表删除，否則回顯示表已存在的錯誤。所有完整代碼在我空間的代碼庫中均存放，可以直接運作。

上機實驗三 基本表的建立與修改

一、實習目的

通過建立基本表和向表中輸入記錄，加深對關系資料庫模型中型與值概念的了解；學會對基本表進行修改、删除和建立索引等基本操作。

二、實習準備

1．複習Create table, Create index, Altertable, Drop table等指令。

2．複習Insert, Update,Delete指令的使用。

三、實驗内容

在查詢分析器中用Create table指令在實驗二建立的GradeManager資料庫中定義基本表：學生表(Student)，課程表(Course)，班級表(Class)，成績表(Grade)。表結構如下：

學生表

屬性名	資料類型	可否為空	含義
Sno	Char(7)	否	學号（唯一）
Sname	Varchar(20)	否	學生姓名
Ssex	Char(2)	否	性别
Sage	Smallint	可	年齡
Clno	Char(5)	否	所在班級

課程表

屬性名	資料類型	可否為空	含義
Cno	Char(1)	否	課程号（唯一）
Cname	Varchar(20)	否	課程名
Credit	Smallint	可	學分

班級表

屬性名	資料類型	可否為空	含義
Clno	Char(5)	否	班級号（唯一）
Speciality	Varchar(20)	否	班級所在專業
Inyear	Char(4)	否	入學年份
Number	Int	可	班級人數
Monitor	Char(7)	可	班長學号

成績表

屬性名	資料類型	可否為空	含義
Sno	Char(7)	否	學号
Cno	Char(1)	否	課程号
Gmark	Numeric（4，1）	可	成績

上述四個表中的資料依次如下：

2000101,李勇,男,20,00311      2000102,劉詩晨,女,19,00311

2000103,王一鳴,男,20,00312    2000104,張婷婷,女,21,00312

2001101,李勇敏,女,19,01311    2001102,賈向東,男,22,01311

2001103,陳寶玉,男,20,01311    2001104,張逸凡,男,21,01311

1,資料庫,4      2,離散數學,3     3,管理資訊系統,2

4,作業系統,4    5,資料結構,4     6,資料處理,2

7,C語言,4

00311,計算機軟體,2000,120,2000101      00312,計算機應用,2000,140,2000103

01311,計算機軟體,2001,220,2001103

2000101,1,92    2000101,3,88    2000101,5,86    2000102,1,78    2000102,6,55

2000103,3,65    2000103,6,78    2000103,5,66    2000104,1,54    2000104,6,83

2001101,2,70    2001101,4,65    2001102,2,80    2001102,4,90     2001102,6,83

2001103,4,76    2001103,6,56

要求完成如下操作：

(1)   給學生表增加一屬性Nation（民族），資料類型為varchar(20)；

(2)   删除學生表中新增的屬性Nation。

(3)   向成績表中插入記錄(‘2001110’,’3’,80)。

(4)   修改學号為2001110的學生成績為70分。

(5)   删除學号為2001110的學生的成績記錄。

(6)   為學生表建立一個名為IX_Class的索引，以班級号排序。

(7)   删除索引IX_Class。

四、實習報告内容

(1)   寫出用Create Table語句對四個基本表的定義。

(2)   寫出實習内容中七個操作的SQL語句。

上機實驗四 SELECT語句的使用(一)

一、實習目的

掌握SELECT的基本格式，能使用SQL Server對表進行簡單查詢。

二、實習準備

1．複習SELECT語句的基本使用格式。

2．複習5種集合函數：AVG，SUM，MAX，MIN，COUNT。

三、實習内容

完成以下查詢語句：

(1) 找出所有被學生選修了的課程号。

(2) 找出01311班女生的個人資訊。

(3) 找出0311班，0312班學生的姓名、性别、出生年月。

(4) 找出所有姓李的學生的個人資訊。

(5) 找出學生李勇所在班級的學生人數。

(6) 找出課程名為作業系統的平均成績、最高分、最低分。

(7) 找出選修了課程的學生人數。

(8) 找出選修了課程作業系統的學生人數。

(9) 找出2000級計算機軟體班的成績為空的學生姓名。

四、實習報告内容

    寫出上述操作的SQL語句。

試驗結果以及程式：

USE GradeManager;

CREATE TABLE Student(Sno Char(7) NOT NULL UNIQUE,

 Sname Varchar(20) NOT NULL,

 Ssex Char(2) NOT NULL,

 Sage Smallint,

 Clno Char(5) NOT NULL

 );

CREATE TABLE Course(Cno Char(1) NOT NULL UNIQUE,

Cname Varchar(20) NOT NULL,

Credir Smallint

);

CREATE TABLE Class(Clno Char(5) NOT NULL UNIQUE,

   Speciality Varchar(20) NOT NULL,

   Inyear Char(4) NOT NULL,

   Number Int,

   Monitor Char(7)

   );

CREATE TABLE Grade(Sno Char(7) NOT NULL,

   Cno Char(1) NOT NULL,

   Gmark Numeric(4,1)

   ); 

INSERT INTO Student VALUES('2000101','李勇','男',20,'00311');

INSERT INTO Student VALUES('2000102','劉詩晨','女',19,'00311');

INSERT INTO Student VALUES('2000103','王一鳴','男',20,'00312');

INSERT INTO Student VALUES('2000104','張婷婷','女',21,'00312');

INSERT INTO Student VALUES('2001101','李勇敏','女',19,'01311');

INSERT INTO Student VALUES('2001102','賈向東','男',22,'01311');

INSERT INTO Student VALUES('2001103','陳寶玉','男',20,'01311');

INSERT INTO Student VALUES('2001104','張逸凡','男',21,'01311');

INSERT INTO Course VALUES('1','資料庫',4);

INSERT INTO Course VALUES('2','離散數學',3);

INSERT INTO Course VALUES('3','管理資訊系統',2);

INSERT INTO Course VALUES('4','作業系統',4);

INSERT INTO Course VALUES('5','資料結構',4);

INSERT INTO Course VALUES('6','資料處理',2);

INSERT INTO Course VALUES('7','C語言',4);

INSERT INTO Class VALUES('00311','計算機軟體','2000',120,'2000101');

INSERT INTO Class VALUES('00312','計算機應用','2000',140,'2000103');

INSERT INTO Class VALUES('01311','計算機軟體','2001',220,'2001103');

INSERT INTO Grade VALUES('2000101','1',92);

INSERT INTO Grade VALUES('2000101','3',88);

INSERT INTO Grade VALUES('2000101','5',86);

INSERT INTO Grade VALUES('2000102','1',78);

INSERT INTO Grade VALUES('2000102','6',55);

INSERT INTO Grade VALUES('2000103','3',65);

INSERT INTO Grade VALUES('2000103','6',78);

INSERT INTO Grade VALUES('2000103','5',66);

INSERT INTO Grade VALUES('2000104','1',54);

INSERT INTO Grade VALUES('2000104','6',83);

INSERT INTO Grade VALUES('2001101','2',70);

INSERT INTO Grade VALUES('2001101','4',65);

INSERT INTO Grade VALUES('2001102','2',80);

INSERT INTO Grade VALUES('2001102','4',90);

INSERT INTO Grade VALUES('2001102','6',83);

INSERT INTO Grade VALUES('2001103','4',76);

INSERT INTO Grade VALUES('2001103','6',56);

ALTER TABLE Student ADD Nation Varchar(20);

ALTER TABLE Student DROP COLUMN  Nation;

INSERT INTO Grade VALUES('2001110','3',80);

UPDATE Grade SET Gmark=70 WHERE Sno='2001110';

DELETE FROM Grade WHERE Sno='2001110';

CREATE INDEX IX_Class ON Student(Clno);

DROP INDEX IX_Class ON Student;

SELECT DISTINCT Cno '課程号' 

FROM Grade;  

SELECT Sno '學号',Sname '姓名',Ssex '性别',Sage '年齡',Clno '班級' 

FROM Student 

WHERE Clno='01311' AND Ssex='女';

SELECT Sname '姓名',Ssex '性别',2012-Sage '出生年月' 

FROM Student 

WHERE Clno IN('01311','01312');

SELECT Sno '學号',Sname '姓名',Ssex '性别',Sage '年齡',Clno '班級' 

FROM Student 

WHERE Sname LIKE '李%';

SELECT Number '班級人數'

 FROM Class

  WHERE Clno =(

SELECT Clno 

FROM Student 

WHERE Sname = '李勇');

SELECT AVG(Gmark) '平均分',MAX(Gmark) '最高分',MIN(Gmark) '最低分' 

FROM Grade 

WHERE Cno =

(SELECT Cno 

FROM Course WHERE Cname='作業系統');

SELECT COUNT(DISTINCT Sno) '選修人數' 

FROM Grade;

SELECT COUNT(DISTINCT Sno) '選修作業系統人數' 

FROM Grade 

WHERE Cno = 

(SELECT Cno

 FROM Course

 WHERE Cname = '作業系統');

SELECT Sname '姓名'

 FROM Student,Class

WHERE Student.Clno=Class.Clno and Inyear='2000'and Speciality='計算機軟體'and sno NOT IN

                                                                           (SELECT sno 

                                                                           FROM Grade)