数据库系统原理实验一参考题答案

姓名：专业：班级：学号：科目：数据库系统原理实验日期：

实验题目：实验1 SQL SERVER 的安装及使用，数据库的建立

MAXSIZE=50,

FILEGROWTH=5

)

LOG ON

(

NAME='Students_Log',

FILENAME='E:\ SQL_DATEBASE \Students_Mis_log.ldf',

SIZE=5MB,

MAXSIZE=25MB,

FILEGROWTH=5MB

)

GO

（2）调用（USE）数据库：Students_Mis_2018

use Students_Mis_2018

GO

（3）分别建立4个数据表的表结构(CREATE TABLE):系（Depts），学生（Students）,课程（Courses）,选课（Reports）

CREATE TABLE Depts

(

Dno CHAR(5)PRIMARY KEY,

Dname CHAR(20)NOT NULL

)

GO

CREATE TABLE Students

(

Sno CHAR(5)PRIMARY KEY,

Sname CHAR(20)NOT NULL,

Ssex CHAR(2),

Sage INT,

Dno CHAR(5),

CONSTRAINT FK_Dno FOREIGN KEY(Dno)REFERENCES Depts )

GO

CREATE TABLE Courses

(

Cno CHAR(6)PRIMARY KEY,

Cname CHAR(20),

Pre_Cno CHAR(6),

Credits INT

)

GO

CREATE TABLE Reports

(

Sno CHAR(5),

Cno CHAR(6),

Grade INT CHECK(Grade>=0 AND Grade<=100),

PRIMARY KEY(Sno,Cno),

CONSTRAINT Student_Report FOREIGN KEY(Sno)REFERENCES Students, CONSTRAINT Report_Course FOREIGN KEY(Cno)REFERENCES Courses

)

GO

（4）分别对4个表插入表数据（INSERT）

INSERT

INTO depts

VALUES ('D01','自动化')

INSERT

INTO depts

VALUES ('D02','计算机')

INSERT

INTO depts

VALUES ('D03','数学')

INSERT

INTO depts

VALUES ('D04','通信')

INSERT

INTO depts

VALUES ('D05','电子')

INSERT

INTO depts

VALUES ('D06','化学')

SELECT*

FROM Depts

INSERT

INTO Students

VALUES ('S01','王建平','男',21,'D01') INSERT

INTO Students

VALUES ('S02','刘华','女',19,'D01') INSERT

INTO Students

VALUES ('S03','范林军','女',18,'D02') INSERT

INTO Students

VALUES ('S04','李伟','男',19,'D03') INSERT

INTO Students

VALUES ('S05','黄河','男',18,'D03') INSERT

INTO Students

VALUES ('S06','长江','男',20,'D03') SELECT*

FROM Students