SQL Server 2008 交互式SQL语言实例练习

数据库学习初步

一、数据定义

(一)、基本表操作

先建立school数据库，在school数据库里做以下的操作。

1.建立基本表

1）创建学生表Student，由以下属性组成： 学号SNO（INT型，主码），姓名SNAME（CHAR型，长度为8，非空唯一），性别SEX（CHAR型，长度为2），所在系DEPTNO（INT型）。

 

2）创建课程表Course，由以下属性组成：课程号CNO（INT型），课程名CNAME（CHAR型，长度为20，非空），授课教师编号TNO（INT型），学分CREDIT（INT型）。其中(CNO，TNO)为主码。

 

3）创建学生选课表SC，由以下属性组成：学号SNO，课程CNO，成绩GRADE。所有属性均为INT型，其中(SNO，CNO)为主码。

 

4）创建教师表Teacher，由以下属性组成：教师编号TNO（INT型，主码），教师姓名TNAME（CHAR型，长度为8，非空），所在系DEPTNO（INT型）。

 

5）创建系表Dept，由以下属性：系号DEPTNO（INT型，主码），系名DNAME（CHAR型，长度为20，非空）。

 

2.修改基本表

1）在Student表中加入属性SAGE(INT型)。

 

2）将Student表中的属性SAGE类型改为SMALLINT 型。

 

3.删除基本表

1）在所有操作结束后删除STUDENT表。

 

2）在所有操作结束后删除COURSE表。

 

3）在所有操作结束后删除SC表。

 

4）在所有操作结束后删除TEACHER表。

 

5）在所有操作结束后删除DEPT表。

 

 

(二)、索引操作

1.建立索引

1）在Student表上建立关于SNO的唯一索引stusno。

 

2)在Course表上建立关于CNO升序的唯一索引coucno。

 

2.删除索引

1）删除Student表上的索引stusno。

 

2）删除Course表上的索引coucno。

 

(三)、视图操作

1.建立视图

在插入数据的Student基本表上为计算机科学与技术系的学生记录建立一个视图CS_STUDENT。

 

2.删除视图

在操作结束后删除视图CS_STUDENT。

 

二、数据操作

(一)、更新操作

1.插入数据

1）向STUDENT表插入下列数据：

1001,张天,男,10,20

1002,李兰,女,10,21

1003,陈铭,男,10,21

1004,刘茜,女,20,21

1005,马朝阳,男,20,22

 

2）向COURSE表插入下列数据：

1,数据结构,101,4

2,数据库,102,4

3,离散数学,103,4

4,C语言程序设计,101,2

 

3）向SC表插入下列数据：

1001,1,80

1001,2,85

1001,3,78

1002,1,78

1002,2,82

1002,3,86

1003,1,92

1003,3,90

1004,1,87

1004,4,90

1005,1,85

1005,4,92

 

4）向TEACHER表插入下列数据：

101,张星,10

102,李珊,10

103,赵天应,10

104,刘田, 20

 

5）向DEPT表插入下列数据：

10,计算机科学与技术

20,信息

 

2.修改数据

将张星老师数据结构课的学生成绩全部加2分

3.删除数据

删除马朝阳同学的所有选课记录

 

(二)、查询操作

1.单表查询

1）查询所有学生的信息。

2）查询所有女生的姓名。

3）查询成绩在80到89之间的所有学生选课记录，查询结果按成绩的降序排列。

4）查询各个系的学生人数。

 

2.连接查询

查询信息系年龄在21岁以下（含21岁）的女生姓名及其年龄。

 

3.嵌套查询

1）查询修课总学分在10学分以下的学生姓名。

2）查询各门课程取得最高成绩的学生姓名及其成绩。

3）查询选修了1001学生选修的全部课程的学生学号。

4）查询选修了张星老师开设的全部课程的学生姓名。

 

 

出现的问题及解决方案：

1.在创建基本表时是否可以缺省主码？

2.对基本表进行修改，执行ALTER TABLE Student MODIFY Sage SMALLINT显示执行失败。

 

 

 

自己做的答案：

（一）  基本表操作

建立基本表

1)     建立Student表

命令行操作：

CREATE TABLE Student

( SNO INTNOTNULL PRIMARY KEY,

  SNAME CHAR(8)NOTNULL,

  SEX CHAR(2)NOTNULL,

  DEPTNO INT NOT NULL)

 

2)     建立Course表

命令行操作：

CREATE TABLE Course

(

    CNO INT NOT NULL,

    CNAME CHAR(20)NOTNULL,

    TNO INT NOT NULL,

    CREDIT INT NOT NULL,

    PRIMARY KEY(CNO, TNO)

)

3)     建立SC表

命令行操作：

CREATE TABLE SC

(

   SNO INT NOT NULL,

   CNO INT NOT NULL,

   GRAGE INT NOT NULL,

   PRIMARY KEY(SNO, CNO)

)

4)     建立Teacher表

命令行操作：

CREATE TABLE Teacher

( TNO INTNOTNULL,

  TNAME CHAR(8)NOTNULL PRIMARY KEY,

  DEPTNO INT NOT NULL

)

5)     建立Depth表

命令行操作：

 CREATETABLE Dept

(

  DEPTNO INT NOT NULL PRIMARYKEY,

  DNAME CHAR(20)NOTNULL

)

2.修改基本表

1）在Student表中加入属性SAGE(INT型)。

命令行操作：

ALTER TABLE Student ADD SAGEINT;

2）将Student表中的属性SAGE类型改为SMALLINT型。

     命令行操作：

         ALTERTABLEStudent alter column SAGESMALLINT;

 

 

3.删除基本表

1）在所有操作结束后删除STUDENT表。

命令行操作：

DROP TABLE Student;

2）在所有操作结束后删除COURSE表。

DROP TABLE Course;

3）在所有操作结束后删除SC表。

DROP TABLE SC;

4）在所有操作结束后删除TEACHER表。

DROP TABLETeacher;

5）在所有操作结束后删除DEPT表。

DROP TABLE Dept;

 

(二)、索引操作

1.建立索引

1）在Student表上建立关于SNO的唯一索引stusno。

CREATE UNIQUEINDEXstusno ON Student(SNO);

2)在Course表上建立关于CNO升序的唯一索引coucno。

CREATE UNIQUE INDEX coucno ONCourse(CNO);

 

2.删除索引

1）删除Student表上的索引stusno。

DROP INDEXStudent.stusno;

2）删除Course表上的索引coucno。

DROP INDEX Course.coucno;

 

(三)、视图操作

1.建立视图

在插入数据的Student基本表上为计算机科学与技术系的学生记录建立一个视图CS_STUDENT。

 

CREATE VIEWCS_STUDENT

  AS

  SELECT SNO,SNAME,SEX,DEPTNO,SAGE

  FROM Student

  WHERE DEPTNO=10

  WITH CHECK OPTION

2.删除视图

在操作结束后删除视图CS_STUDENT。

 DROPVIEW CS_STUDENT;

 

二、数据操作

(一)、更新操作

1.插入数据

1）向STUDENT表插入下列数据：

1001,张天,男,10,20

1002,李兰,女,10,21

1003,陈铭,男,10,21

1004,刘茜,女,20,21

1005,马朝阳,男,20,22

命令行操作：

INSERT INTOStudent

VALUES(1001,'张天','男',10,20);

INSERT INTOStudent

VALUES(1002,'李兰','女',10,21);

INSERT INTOStudent

VALUES(1003,'陈铭','男',10,21);

INSERT INTOStudent

VALUES(1004,'刘茜','女',20,21);

INSERT INTOStudent

VALUES(1005,'马朝阳','男',20,22);

2）向COURSE表插入下列数据：

1,数据结构,101,4

2,数据库,102,4

3,离散数学,103,4

4,C 语言程序设计,101,2

命令行操作：

INSERT INTOCourse

VALUES(1,'数据结构',101,4);

INSERT INTOCourse

VALUES(2,'数据库',102,4);

INSERT INTOCourse

VALUES(3,'离散数学',103,4);

INSERT INTOCourse

VALUES(4,'C语言程序设计',101,2);

 

3）向SC表插入下列数据：

1001,1,80

1001,2,85

1001,3,78

1002,1,78

1002,2,82

1002,3,86

1003,1,92

1003,3,90

1004,1,87

1004,4,90

1005,1,85

1005,4,92

命令行操作：

INSERT INTOSC(SNO,CNO,GRAGE)

SELECT 1001,1,80 union

SELECT 1001,2,85 union

SELECT 1001,3,78 union

SELECT 1002,1,78 union

SELECT 1002,2,82 union

SELECT 1002,3,86 union

SELECT 1003,1,92 union

SELECT 1003,3,90 union

SELECT 1004,1,87 union

SELECT 1004,4,90 union

SELECT 1005,1,85 union

SELECT 1005,4,92

4）向TEACHER表插入下列数据：

101,张星,10

102,李珊,10

103,赵天应,10

104,刘田, 20

命令行操作：

INSERT INTOTeacher(TNO,TNAME,DEPTNO)

SELECT 101,'张星',10 union

SELECT 102,'李珊',10 union

SELECT 103,'赵天应',10 union

SELECT 104,'刘田', 20

 

5）向DEPT表插入下列数据：

10,计算机科学与技术

20,信息

命令行操作：

INSERT INTODept(DEPTNO,DNAME)

SELECT 10,'计算机科学与技术'union

SELECT 20,'信息'

 

2.修改数据

将张星老师数据结构课的学生成绩全部加2分

UPDATE SC

SET GRAGE= GRAGE + 2

WHERE CNOIN

      (SELECTCNO

       FROMCourse

       WHERECNAME='数据结构'and cnoin

       (SELECTcno

        FROM Teacher

        WHERE TNAME='张星')

        )

3.删除数据

删除马朝阳同学的所有选课记录

DELETE

FROM SC

WHERE SNO =1005

(二)、查询操作

1.单表查询

1）查询所有学生的信息。

SELECT SNO,SNAME,SEX, DEPTNO,SAGE

FROM Student;

 

2）查询所有女生的姓名。

SELECT SNAME

FROM Student

WHERE SEXIN('女');

 

3）查询成绩在80到89之间的所有学生选课记录，查询结果按成绩的降序排列。

SELECT CNO

FROM SC

WHERE GRAGE BETWEEN 80 AND 89;

4）查询各个系的学生人数。

SELECT DISTINCTDEPTNO

FROM Dept;

2.连接查询

查询信息系年龄在21岁以下（含21岁）的女生姓名及其年龄。

SELECT SNAME,SAGE

FROM Student,Dept

WHERE DNAME = '信息'ANDSAGE <= 21 AND SEX= '女';

3.嵌套查询

1）查询修课总学分在10学分以下的学生姓名。

SELECT SNAME

FROM Student,Course

WHERE CREDIT < 10;

2）查询各门课程取得最高成绩的学生姓名及其成绩。

SELECT SNAME,GRAGE,

FROM Student,SC

WHERE Student.SNO=SC.SNOANDGRAGE IN

       (SELECTMAX(GRAGE)

        FROM SC

        GROUP BYCNO);

3）查询选修了1001学生选修的全部课程的学生学号。

 

SELECT DISTINCT SNO

FROM SC SCX

WHERE SNO != 1001 AND NOT EXISTS

    (SELECT *

    FROM SCSCY

    WHERE SCY.SNO = 1001 AND

       NOT EXISTS

       (SELECT *

       FROM SCSCZ

       WHERE SCZ.SNO = SCX.SNO AND

             SCZ.CNO = SCY.CNO))

4）查询选修了张星老师开设的全部课程的学生姓名。

 

SELECT SNAME

FROM Student

WHERE SNO IN

    (SELECT SNO

     FROMSC

     WHERECNO IN

       (SELECT CNO

        FROM Course

        WHERE TNO IN

           (SELECT TNO

           FROM Teacher

           WHERE TNAME='张星')

           )

       )

 

出现的问题及解决方案：

1.在创建基本表时是否可以缺省主码？

解：可以

2.对基本表进行修改，执行ALTER TABLE Student MODIFY Sage SMALLINT显示执行失败。

解：将MODIFY更改为alter column就可以解决。