SQL Server复习

期末考试

一、填空题

（每空1分，共10分）

\1. 数据系统的三级模式结构是指数据库的（外模式）、（模式）和内模式。
\2. 函数 FLOOR（558.3049）返回值是（558）。
\3. 删除表命令是（deop table）或（delete from）。
\4. 关系模型中包括三类完整性约束，是指（实体完整性）（参照完整性） 和（ 用户自定义完整性）。
\5. 计算字段的累加和的函数是（sum），统计项目数的函数是（count）。

二、简答题一

（每小题5分，共15分）

\1. 定义表salary,包含员工编号empno（数字型，标识列，主键），员工名称empname （字符型），基本工资basic （数字型，两位小数），奖金bonus （数字型，两位小数）， 合计total （数字型，两位小数）。
\2. 创建员工表emp,包含员工编号empnof数字型，标识列，主键），员工姓名 empname（字符型），年龄empage（数字型），性别gender（字符型，默认值为’男’）， 出生日期birth （日期型）。
\3. 创建新员工表newemp,将员工表emp中的数据插入到newemp中

1,

1
2
3
4
5
6
crete table salary
(empno int identity(1,1) primary key,
empname varchar(20),
basic decimal(18,2),
bonus decimal(18,2),
total decimal(18,2))

2,

1
2
3
4
5
6
7
8
create table emp
(empno int identity primary key,
empname varchar(20),
empage int,
/*gender varchar(20) check(gender in ('男','女')) default ('男')*/
gender varchar(20) default('男'),
birth date/*datetime*/
)

3,
解法一（不推荐）

1
2
3
4
5
6
7
8
9
create table newemp
(empno int identity primary key,
empname varchar(20),
empage int,
/*gender varchar(20) check(gender in ('男','女')) default ('男')*/
gender varchar(20) default('男'),
birth date/*datetime*/
)
insert into newemp select *from emp;

解法二（推荐）

1
2
select * into newemp from emp;
select *from newemp

另外补充一下删除操作

1
2
delete from newemp;/*删除表中所有数据*/
drop table newemp;/*删除表*/

三、简答题二

（每小题5分.共20分） 

1.   创建员工表，包含员工编号、姓名、年龄、性别、身份证号，其中员工编号是主键，在身份证号上创建唯  一索引，姓名上创建普通索引。
2.   创建一个视图，只显示员工表中工资小于2200的员工，且带检查约束。
3.   创建一个视图，显示员工姓名和性别。
4.   创建一个触发器，判断如果非周六至周日的时间，禁止表操作。

1，

1
2
3
4
5
6
7
8
9
10
11
create table emp
(
no int primary key,
name varchar(20),
age int,
gender varchar(10),
iden varchar(18))
create unique index emp_iden_idx
on emp(iden);
create index emp_name_idx
on emp(name);

2，

1
2
3
create view emp_v1
as select *from emp where salary<2000
with check option;

3,

1
2
3
create view emp_v2(empname,empgender)
as select name,gender from emp
with check option;

4,待验证

1
2
3
4
5
6
7
8
9
10
create trigger ddl_week_trig on database
for ddl_table_events
as
declare @weekday int
select @weekday=datepart(weekday,getdate())
if @weekday in (1,2,3,4,5) 
	begin 
	raiserror('错误时间段，禁止操作',16,1)
	rollback
end;

四、简答题三

(每小题5分，共20分) 

1. 创建一个部门表tbldept,要求包含两列：  <1)部门编号deptno»类型int,具有identity标识属性；  (2)部门名称 deptname,类型 varchar (20)。
2. 修改以上部门表tbldept,增加两列：部门经理deptmgr,类型varchar (20)；部 门人数deptcount,类型int。
3. 在部门tbldept表中插入两条部门信息:销售部/张三/212,人力资源部/王五/116。 
4. 修改销售部人数为210 A;更改人力资源部经理为李四。

1,

1
2
3
4
5
create table tbldept
(
deptno int identity(1,1),
deptname varchar(20)
);

2,

1
2
3
4
alter table tbldept
add
deptmgr varchar(20),
deptcount int

3,

1
2
insert into tbldept values('销售部','张三',212)
insert into tbldept values('人力资源部','王五',116)

4,

1
2
3
4
5
6
7
update tbldept
set deptcount=210
where deptname='销售部'

update tbldept
set deptname='李四'
where deptname='人力资源部'

五，综合题

(1-5每小题3分，6-9每小题5分，共35分) 依据表1和表2完成1-9题

表1 院系表 department

字段 类型 说明
departmentno int,自动增长 院系编号，主键
departmentname varchar (30) 院系名称

表2 学生表 student

字段 类型 说明
studentno int,自动增长 学生编号，主键
studentname varchar (20) 学生姓名，非空、唯一
sex varchar (10) 学生性别，只能是男或女
age int 学生年龄
departmentno int 院系编号，外键，引用d叩artment (departmentno)

1.创建院系表department。

2.创建学生表studento。

3.查询前50%的学生信息。

4.查询所有姓张的学生信息。

5.查询2006年出生的学生信息。

6.查询年龄介于20-25岁的男学生信息。

7.查询院系编号及该院系学生数量、学生的平均年龄。

8.查询学生信息，要求输出学生编号、姓名、性别、年龄以及所在院系名称。

9.查询每个院系的最小学生年龄，输出信息包括院系名称、最小年龄。

1,

1
2
3
create table department
(departmentno int identity(1,1) primary key,
departmentname varchar(30));

2,

1
2
3
4
5
6
create table student
(studentno int identity(1,1) primary key,
studentname varchar(20) unique not null,
sex varchar(20) check (sex in ('男','女')),
age int,
departmentno int foreign key references  department (departmentno));

3,

1
select top 50 percent *from student;

4,

1
select *from student where studentname like '张%';

5,

1
select *from student where age=16;

6,

1
select *from student where sex='男' and age between 20 and 25;

7,

1
select departmentno 院系编号,count(*) 学生数量,avg(age) 学生平均年龄 from student join department on department.departmentno=student.departmentno;

消息 8120，级别 16，状态 1，第 24 行
选择列表中的列 ‘department.departmentname’ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

8,

1
select studentno,studentname,sex,age,departmentname from student o where age=(select min(age) from student i where i.departmentno=o.departmentno);

消息 207，级别 16，状态 1，第 33 行
列名 ‘departmentname’ 无效。

9,未验证，不报错

1
2
select departmentname,min(age) from department join student on department.departmentno=student.departmentno
group by departmentname;

题目

1
2
3
4
5
6
7
8
9
10
11
12
13
create database tt
use tt;
create table student(
stuid varchar (20) primary key,
stuname varchar (20),
birthday datetime,
age int
)
insert into student values(20090101,'张三','1991-12-29',Null);
insert into student values(20090102,'李四','1990-12-01',Null);
insert into student values(20090103,'王五','1992-06-09',Null);
insert into student values(20090104,'刘大','1991-05-23',Null);


第一题

1
2
3
4

update student set stuid=stuff(stuid,1,2,'09') where SUBSTRING(stuid,1,4)='2009';
--错误改法,结果不对
select *from student

重来，删除一下数据

1
delete from student;

还原了，OK

继续尝试，，，

1
update student set stuid=RIGHT(1,5)

报错

为啥？不知道，，，

待解决，继续尝试其他解法。

1
update student set stuid=replace(stuid,'2009010*','09010*');

不报错，但是毫无变化，，，

1
update student set stuid=replace(stuid,'2009','09')

yes , I get it.

第二题

1
update student set age=year(getdate())-year(birthday);

第三题

1
select *from student where year(birthday)=1991;

第四题

1
select *from student where month(birthday) between 1 and 6;

#Note
SQL Server复习
https://69asgard.github.io/2022/05/07/SQL-Server复习/
作者
Alan Root
发布于
2022年5月7日
许可协议