SQL 作业练习及答案

教师表(编号 char(6)，姓名，性别，民族，职称，身份证号) 课程表(课号 char(6)，名称) 任课表(ID，教师编号,课号，课时数) 用 SQL 语言实现下列功能的 sql 语句代码：
1. 创建上述三表的建库、建表代码(14 分)； 要求使用：主键(教师表.编号，课程表.课号)、外键(任课表.教师编号，任课表.课 号)、默认(民族)、非空(民族，姓名)、唯一(身份证号)、检查(性别、课时数),自动 编号(ID)
建“教师数据库” create database 教师数据库 on primary ( name='教师数据库_data', filename='c:\jsl\教师数据库_data.mdf', size=3MB, maxsize=30MB, filegrowth=10% ) log on ( name='教师数据库_log', filename='c:\jsl\教师数据库_log.ldf', size=1MB, filegrowth=10MB ) go
删除 课号为 100003 的课程信息 use 教师数据库 go delete from 课程表 where 课号='100003'
3. 写出创建[任课表视图](教师编号，姓名，课号，课程名称，课时数)的代码；(4 分) use 教师数据库 go create view 任课表视图 as select 任课表.教师编号,教师表.姓名,课程表.课号,课程表.名称,任课表.课时数 from 教师表,课程表,任课表 where 教师表.编号=任课表.教师编号and 任课表.课号=课程表.课号
go
4. 写出创建[某门课任课教师]内嵌表值函数以及检索的代码；(6 分) 检索：所有代'SQL Server 数据库'这门课程的老师姓名；
use 教师数据库 go create function jsl(@sc char(10)) returns table as return (select 教师表.姓名,课程表.名称,任课表.课号 from 教师表,课程表,任课表 where 教师表.编号=任课表.教师编号and 任课表.课号=课程表.课号
go
2. 将下列课程信息添加到课程表的代码(6 分)
课号
课程名称
100001 SQL Server 数据库
100002 数据结构
100003 VB 程序设计
修改 课号为 100003 的课程名称：Visual Basic 程序设计
use 教师数据库 go
update 课程表 set 名称='Visual Basic 程序设计' where 课号='100003'
use 教师数据库 go create table 教师表 ( 编号char(6) primary key, 姓名char(8) not null, 性别char(2)check(性别='男' or 性别='女'), 民族 char(18) default('汉') not null, 职称char(12), 身份证号char(18) unique, ) go create table 课程表 ( 课号char(6) primary key,
名称char(22), ) go create table 任课表 ( ID int identity(1,1), 教师编号char(6) references 教师表(编号), 课号char(6) references 课程表(课号), 课时数int check(课时数>=0 and 课时数<=100),
)
//给三张表加数据 use 教师数据库 go insert 教师表(编号,姓名,性别,民族,职称,身份证号) select '101','张三','男','回族','教师','10223456789' union select '102','王思','女','汉族','教师','102235641214' union select '103','李武','男','苗族','教师','987456123545' union select '104','赵丽','女','汉族','教师','457863154452' union select '105','田七','男','汉族','教授','42545412149' go insert 课程表(课号,名称) select '100001','SQL server数据库' union select '100002','数据结构' union select '100003','VB程序设计' union select '100004','计基' union select '100005','大学数学' go insert 任课表(教师编号,课号,课时数) select '101','100001','80'union select '102','100002','98'union select '103','100003','90'union select '104','100004','85'union select '105','100005','78'
and 课程表.名称=@sc) go select * from dbo.jsl('SQL Server数据库')
5. 写出创建[统计课时数]：输出最大课时数、最低课时数、平均课时的自定义函数代码； (6 分)
use 教师数据库 go create function 统计课时数(@ff int) returns table as return (select max(课时数)as 最大值, min(课时数) as 最小值, avg(课时数) as 平均值 from 教师表,课程表,任课表 where 教师表.编号=任课表.教师编号and 任课表.课号=课程表.课号
and 任课表.课时数>=90
and 任课表.课时数=@ff)
6. 检索有一门或一门以上课程课时数大于 90 的所有教师的信息，包括编号、姓名。(4 分) use 教师数据库 go select distinct 编号,姓名 from 教师表,课程表,任课表 where 教师表.编号=任课表.教师编号and 任课表.课号=课程表.课号