SQL数据库题目

--注意：执行这些代码要按题号分开执行，不然会报错。

--第一题：

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

某学院为了规范教职工基本信息管理已建立了职工人事数据库，数据库包括了3个基本表：

（1）职工信息表：职工号C(5)，部门号C(2)，姓名C(8)，性别C(2)，职称C（10），出生日期D，婚姻状况L，工资N(8,2)；

（2）部门表：部门代码C(2)，部门名称C（10），负责人C(8)；

（3）工资表：职工号C(5)，基本工资N(7,2)，津贴N(7,1)，奖金N(3)，会费N(5,2)，公积金N(7,2)现根据年度考核要求，需要在人事数据库中增加每年年度绩效考核的相关信息。绩效考核的主要内容包括教学（按年度统计教学课时、实验课时、实习课时、选课人数等）、科研（按年度统计科研论文、教学论文、科研项目等）、日常活动（包括活动出席、会议出勤等）和特殊贡献（包括贡献项目、贡献分值等），所有考核内容需要量化，形成教职工年度绩效分数。

请分析以上用户的需求，并画出包括年度考核在内的人事数据库E-R图、简要说明；

在绘制E-R图的基础上设计绩效考核不封的数据表结构（仅包括表名、字段名称、数据类型、宽度等）

1、CREATE TABLE [学生]

(

[学号] [char](8) NOT NULL,

[姓名] [char](4) NULL,

[性别] [char](1) NULL,

[出生日期] [date] NULL,

[班级] [char](3) NULL,

CONSTRAINT [PK_学生] PRIMARY KEY CLUSTERED

(

[学号] ASC

)

WITH

(

PAD_INDEX = OFF, TATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON

)

ON [PRIMARY]

)

ON [PRIMARY]

2、insert into 成绩 values('95031','3-105','90')

3、update 成绩 set 成绩=成绩+5 where 课程号 ='3-105'

4、所有记录指的是每张表的记录吗？如果是单一查询成绩表中的数据使用：

select * from 成绩 where 成绩 between '60' and '80'

关联其他表可以用

select * from 表名 where 字段 in(select 对应字段 from 成绩 where 成绩 between '60' and '80')

---