SQL数据库题目

Question

1. 创建一个教工管理数据库系统，其中包含两个数据文件dat和dat1,一个事务日志文件logl。其中数据文件初始大小50MB,增长率5MB,增长极限为100MB，事务日志文件初始大小20MB,增长率5MB,增长极限为50MB. 2. 在数据库“教工管理数据库系统”中创建如下表并分别填充8行以上的数据：（1） 部门简况表（部分代号，部门名称，办公地点）（2） 技术职称表（技术职称编码，技术职称）（3） 文化程度表（文化程度编码，文化程度）（4） 教工情况表（教工编号,部门代号,姓名,性别,出生年月,技术职称编码,文化程度编码,婚姻状况,基本工资,奖金,家庭电话）（5） 任课情况表（教工编号，课程号，教学效果编码）（6） 课程档案表（课程号，课程名称，总学时）（7） 教学效果表（教学效果编码，教学效果名称）3. 修改表：在教工情况表中新增一列“家庭地址”并填充数据。4. 给教工情况表授权所有权限给用户dbo. 5. 给数据库“教工管理数据库系统”增加一个文件组Testl并将两个5MB大小的文件添加到该文件组。6. 用查询语句完成下列操作：（1） 查询基本工资是1000元以上的教师姓名和电话。（2） 查询电话号码最后一位是6的教师情况。（3） 查询教英语，计算机接口技术和软件工程的教师的姓名和技术职称。（4） 查询与教师刘华教同一门课程的教师姓名和文化程度。（5） 查询部门情况表的前3行。（6） 将教师情况表中技术职称编码分组。（7） 查询部门代号为“001”的教师的教学效果名称。（8） 查询“计算机系”的办公地点并将列名“办公地点”改为“办公所在地”。（9） 请将教学效果为“一般”的用“1”代替，教学效果“较好”的用“2”代替，教学效果“优秀”的用“3”代替.（10）将任课情况表和课程档案表完全联接。（11）将教师奖金降序排列。（12）查询教“英语”的教师总人数。（13）查询课程号，教学效果编码并消除重复行。（14）查询男教师不少于3的部门名称。7. 将教师情况表中的姓名和家庭电话创建索引，其中姓名为非聚集索引，家庭电话为唯一索引，并查找他们的索引号。8. 创建一个视图，要求包括以下字段：部门名称，姓名，技术职称，文化程度。视图名为VIEW8。哪个高手会做`， 帮帮忙` 要全部用代码做` ` 我会追加分的`， 拜托大家了

Answer 1

--注意：执行这些代码要按题号分开执行，不然会报错。
--第一题：
create database 教工管理数据库系统
on primary
(name='教工管理数据库系统_dat1',
filename='c:\教工管理数据库系统_dat1.mdf',
size=51200KB,
maxsize=102400KB
),
--filegroup [fg1]
(
name='教工管理数据库系统_dat2',
filename='c:\教工管理数据库系统_dat2.mdf',
size=51200KB,
maxsize=102400KB
)
log on
(
name='教工管理数据库系统_log',
filename='c:\教工管理数据库系统_log.ldf',
size=20480KB,
filegrowth=25%,
maxsize=51200KB
)
go

--第二题：
use 教工管理数据库系统
go
create table 部门简况表
(
部门代号 int,部门名称 varchar(20),办公地点 varchar(30)
)
insert 部门简况表 values(1,'经管系','a教学楼')
insert 部门简况表 values(2,'计算机系','b教学楼')
insert 部门简况表 values(3,'机械系','c教学楼')
insert 部门简况表 values(4,'电子系','d教学楼')
insert 部门简况表 values(5,'设计系','e教学楼')
insert 部门简况表 values(6,'外语系','f教学楼')
insert 部门简况表 values(7,'基础教育系','g教学楼')
insert 部门简况表 values(8,'软件工程系','h教学楼')

create table 技术职称表
(
技术职称编码 int,技术职称 varchar(20)
)
insert 技术职称表 values(1,'高高高高级讲师')
insert 技术职称表 values(2,'高高高级讲师')
insert 技术职称表 values(3,'高高级讲师')
insert 技术职称表 values(4,'高级讲师')
insert 技术职称表 values(5,'高级实习指导教师')
insert 技术职称表 values(6,'一级实习指导教师')
insert 技术职称表 values(7,'中学高级教师')
insert 技术职称表 values(8,'小学高级教师')

create table 文化程度表
(
文化程度编码 int,文化程度 varchar(10)
)
insert 文化程度表 values(1,'小学')
insert 文化程度表 values(2,'初中')
insert 文化程度表 values(3,'高中')
insert 文化程度表 values(4,'中专')
insert 文化程度表 values(5,'大专')
insert 文化程度表 values(6,'研究生')
insert 文化程度表 values(7,'硕士')
insert 文化程度表 values(8,'博士')

create table 教工情况表
(
教工编号 int,部门代号 int,姓名 varchar(12),性别 varchar(4),出生年月 varchar(10),技术职称编码 int,文化程度编码 int,婚姻状况 varchar(6),基本工资 money,奖金 money,家庭电话 int
)
insert 教工情况表 values(1,1,'刘与申','男','1978-04',1,1,'已婚',10000,10000,81234567)
insert 教工情况表 values(2,2,'李美丽','女','1952-04',2,2,'已婚',9000,9000,81234566)
insert 教工情况表 values(3,3,'刘华','男','1971-04',3,3,'已婚',8000,8000,81234565)
insert 教工情况表 values(4,4,'黄恒','男','1988-04',4,4,'未婚',7000,7000,81234564)
insert 教工情况表 values(5,5,'马俊杰','男','1969-04',5,5,'已婚',6000,6000,81234563)
insert 教工情况表 values(6,6,'夏雨','男','1971-04',6,6,'已婚',5000,5000,81234562)
insert 教工情况表 values(7,7,'郭靖','男','1973-04',7,7,'已婚',4000,4000,81234561)
insert 教工情况表 values(8,8,'赵日敏','女','1982-04',8,8,'已婚',3000,3000,81234560)

create table 任课情况表
(
教工编号 int,课程号 int,教学效果编码 varchar(5)
)
insert 任课情况表 values(1,1,1)
insert 任课情况表 values(2,2,2)
insert 任课情况表 values(3,3,3)
insert 任课情况表 values(4,3,4)
insert 任课情况表 values(5,5,5)
insert 任课情况表 values(6,6,6)
insert 任课情况表 values(7,7,7)
insert 任课情况表 values(8,8,8)

create table 课程档案表
(
课程号 int,课程名称 varchar(50),总学时 int
)
insert 课程档案表 values(1,'语文',40)
insert 课程档案表 values(2,'数学',37)
insert 课程档案表 values(3,'政治',41)
insert 课程档案表 values(4,'物理',40)
insert 课程档案表 values(5,'软件工程',38)
insert 课程档案表 values(6,'英语',34)
insert 课程档案表 values(7,'计算机接口技术',29)
insert 课程档案表 values(8,'体育',43)

create table 教学效果表
(
教学效果编码 int,教学效果名称 varchar(20)
)
insert 教学效果表 values(1,'一般')
insert 教学效果表 values(2,'较好')
insert 教学效果表 values(3,'优秀')
insert 教学效果表 values(4,'优秀+')
insert 教学效果表 values(5,'优秀++')
insert 教学效果表 values(6,'优秀+++')
insert 教学效果表 values(7,'优秀++++')
insert 教学效果表 values(8,'优秀+++++')

--第三题：

use 教工管理数据库系统
go
alter table 教工情况表
add 家庭地址 varchar(50)
go
update 教工情况表 set 家庭地址='博爱路1号' where 教工编号=1
update 教工情况表 set 家庭地址='天堂路2号' where 教工编号=2
update 教工情况表 set 家庭地址='北京路3号' where 教工编号=3
update 教工情况表 set 家庭地址='上海路4号' where 教工编号=4
update 教工情况表 set 家庭地址='广州路5号' where 教工编号=5
update 教工情况表 set 家庭地址='中山路6号' where 教工编号=6
update 教工情况表 set 家庭地址='南京路7号' where 教工编号=7
update 教工情况表 set 家庭地址='天津路8号' where 教工编号=8

--第四题：
--grant all on dbo. with grant option; --这题我不是不很明白他的意思，表示不会。

--第五题：
alter database 教工管理数据库系统
add filegroup Testl
go
alter database 教工管理数据库系统
add file
( name='file1',
filename='c:\file1.mdf',
size=5MB,
maxsize=20MB,
filegrowth=1MB),
( name='file2',
filename='c:\file2.mdf',
size=5MB,
maxsize=20MB,
filegrowth=1MB)
to filegroup Testl
go

--第六题：
--1.
use 教工管理数据库系统
go
select 姓名,家庭电话 from 教工情况表 where 基本工资>1000
go
--2.
select * from 教工情况表 where RIGHT(家庭电话,1)=6
go
--3.
select c.姓名,d.技术职称 from 课程档案表 a inner join 任课情况表 b on a.课程号=b.课程号 inner join 教工情况表 c on b.教工编号=c.教工编号 inner join 技术职称表 d on c.技术职称编码=d.技术职称编码
where a.课程名称 in ('英语','计算机接口技术','软件工程')
go
--4.
select a.姓名,b.文化程度 from 教工情况表 a inner join 文化程度表 b on a.文化程度编码=b.文化程度编码 inner join 任课情况表 c on a.教工编号=c.教工编号
where c.课程号=(select d.课程号 from 任课情况表 d inner join 教工情况表 e on e.教工编号=d.教工编号 where 姓名='刘华')
go
--5.
select top 3 * from 部门简况表
go
--6.
--select * from 教工情况表 group by 技术职称编码 --这题我不是不很明白他的意思，也表示不会。
go
--7.
select c.姓名,a.教学效果名称 from 教学效果表 a inner join 任课情况表 b on a.教学效果编码=b.教学效果编码 inner join 教工情况表 c on c.教工编号=b.教工编号 inner join 部门简况表 d on c.部门代号=d.部门代号
where d.部门代号=001
go
--8.
select 办公地点 from 部门简况表 where 部门名称='计算机系'
go
sp_rename '部门简况表.办公地点','办公所在地'
go
--9.
select 教学效果名称,
(case
when 教学效果名称='一般' then 1
when 教学效果名称='较好' then 2
when 教学效果名称='优秀' then 3
end)
from 教学效果表
go
--10.
select a.*,b.* from 任课情况表 a,课程档案表 b
--11.
select * from 教工情况表 order by 奖金 desc
--12.
select count(a.教工编号)AS '教师总人数' from 任课情况表 a
where a.课程号 =(select 课程号 from 课程档案表 where 课程名称='英语')
--13.
--没时间略
--14.
--不是很懂题目意思

--第七题：
use 教工管理数据库系统
go
create nonclustered index i_姓名 on 教工情况表(姓名)
with fillfactor =30
go

use 教工管理数据库系统
go
create unique index i_家庭电话 on 教工情况表(家庭电话)
with fillfactor =30
go

select name,index_id from sys.indexes where name in('i_姓名','i_家庭电话') --查找索引号

--第八题：

use 教工管理数据库系统
go
create view VIEW8
as
select b.部门名称,a.姓名,c.技术职称,d.文化程度 from 教工情况表 a inner join 部门简况表 b on a.部门代号=b.部门代号 inner join 技术职称表 c on a.技术职称编码=c.技术职称编码 inner join 文化程度表 d on a.文化程度编码=d.文化程度编码
go
select * from VIEW8

Answer 2

太麻烦啦···