70计算机网络实践--数据库系统原理

数据库原理共22页第1页--======================================================================--中大计算机自学考试实践考核-8347-数据库原理题目及答案--本SQL脚本在MSSQLSERVER2000上执行通过--======================================================================--======================================================================--样题1.现在为学校建立一个数据库,学校院系机构设置如下;一个系有若干个专业,每个--专业一年只招一个班,每个班有若干学生.要求建立一个关于系,学生,班级的数据库,关--系模式为:--学生STUDENT(学号,姓名,年龄,办号)--班CLASS(班号,专业名,系名,入学年份)--系DEPARTMENT(系号,系名)--======================================================================--首先建立样题一使用的数据库--======================================================================IFEXISTS(SELECT*FROMmaster..sysdatabasesWHEREname=N'Q1')DROPDATABASEQ1GOCREATEDATABASEQ1GOUSEQ1GO--======================================================================--请用SQL语言完成以下功能:--======================================================================--1,使用查询分析器建表,要求声明:--(1)每个表的主外码.--(2)每个班级的人数不能超过20人.--(3)学生年龄介于16到30岁之间.--(4)学生姓名不能为空.--(5)只有班里没有学生的时候,才能删除该班.--======================================================================--以下建立三个基本表:DEPARTMENT,CLASS,STUDENT--======================================================================CREATETABLEDEPARTMENT(系号SmallintPrimarykey,--主码系名varchar(20)UNIQUE)GOCREATETABLECLASS(班号Smallintprimarykey,--主码专业名varchar(20),系名varchar(20)FOREIGNKEYREFERENCESDEPARTMENT(系名),--外码入学年份int)GOCREATETABLESTUDENT(数据库原理共22页第2页学号Smallintprimarykey,--主码姓名varchar(10)notnull,年龄Smallintcheck(年龄=16and年龄=30),班号SmallintFOREIGNKEYREFERENCESCLASS(班号)ONDELETENOACTION--外码--CHECK(20=ALL(SELECTCOUNT(学号)FROMSTUDENTGROUPBY班号)))GO--下面是实现约束条件每个班级的人数不能超过20人CREATEFUNCTIONdbo.f_check()RETURNSINTASBEGINDECLARE@numINTSELECT@num=MAX(A)FROM(SELECTCOUNT(学号)ASAFROMSTUDENTGROUPBY班号)ASBRETURN@numENDGOALTERTABLESTUDENTADDCONSTRAINT学号检查CHECK(dbo.f_check()=20)GO--======================================================================--2,插入如下数据--CLASS(--101,软件,计算机,1995;102,微电子,计算机,1996;--111,无机化学,化学,1995;112,高分子化学,化学,1996;--121,统计数学,数学,1995;131,现代语言,中文,1996;--141,国际贸易,经济,1995;142,国际金融,经济,1996;--)--STUDENT(--8101,'张三',18,101;8102,'钱四',16,121;--8103,'王玲',17,131;8105,'李飞',19,102;--8109,'赵四',18,141;8110,'李可',20,142;--8201,'张飞',18,111;8302,'周瑜',16,112;--8203,'王亮',17,111;8305,'董庆',19,102;--8409,'赵龙',18,101;8510,'李丽',20,142;--)--DEPARTMENT(--'001','数学';'002','计算机';--'003','化学';'004','中文';--'005','经济';--)--======================================================================INSERTINTODEPARTMENT(系号,系名)VALUES('001','数学')INSERTINTODEPARTMENT(系号,系名)VALUES('002','计算机')INSERTINTODEPARTMENT(系号,系名)VALUES('003','化学')INSERTINTODEPARTMENT(系号,系名)VALUES('004','中文')INSERTINTODEPARTMENT(系号,系名)VALUES('005','经济')GOINSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(101,'软件','计算机',1995)INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(102,'微电子','计算机',1996)INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(111,'无机化学','化学',1995)INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(112,'分子化学','化学',1996)INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(121,'统计数学','数学',1995)数据库原理共22页第3页INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(131,'现代语言','中文',1996)INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(141,'国际贸易','经济',1997)INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(142,'国际金融','经济',1996)GOINSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8101,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8102,'钱四',16,121)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8103,'王玲',17,131)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8105,'李飞',19,102)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8109,'赵四',18,141)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8110,'李可',20,142)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8201,'张飞',18,111)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8302,'周瑜',16,112)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8203,'王亮',17,111)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8305,'董庆',19,102)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8409,'赵龙',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8510,'李丽',20,142)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8121,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8131,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8141,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8151,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8161,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8171,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8181,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8191,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8601,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8701,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8801,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8901,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(1101,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(2101,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(3101,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(4101,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(5101,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(6101,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(7101,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(0101,'张三',18,101)INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(1111,'张三',18,101)GO--======================================================================--3,完成以下的查询功能:--(1)找出所有姓李的学