SQL server 数据库练习题及答案（练习3）

一、编程题

公司部门表 department

字段名称	数据类型	约束等	字段描述
id	int	主键，自增	部门ID
name	varchar(32)	非空，唯一	部门名称
description	varchar(1024)		部门简介

员工信息表 person

字段名称	数据类型	约束等	字段描述
id	int	主键，自增	员工ID
name	varchar(32)	非空	员工姓名
sex	varchar(8)	非空	员工性别
department_id	int	非空，外键	员工所在部门ID
age	int	非空	员工年龄

客户信息表  customer

字段名称	数据类型	约束等	字段描述
id	int	主键，自增	客户ID
name	varchar(32)	非空	客户姓名
tel	varchar(32)	非空，唯一	客户电话
person_id	int	非空，外键	所属业务员(员工)ID
detail	varchar(1024)		客户详细信息

问题要求:

1:创建数据库

2:创建三张表

3:添加最少3个部门信息,如下表

4:添加最少5条员工信息,市场部最少4人,如下表

5:给4中的每位市场部员工添加最少3位客户,总条数不低于12条,如下表

6:将员工ID为5的其中一位姓名为"邓婕"的客户转换成员工ID为2的客户

7:将电话为"13937745615"的客户信息删除

8:查询出所有部门信息

9:查询出所有员工信息

10:查询出所有客户信息

11:查询所有性别为女的员工信息

12:查询所有电话是"139"开头的客户信息

13:查询员工的平均年龄

14:统计公司总共有多少客户信息

15:按照年龄从小到大排序输出所有员工信息

16:按照所属员工ID正序输出所有客户信息

17:统计每个部门的员工数,输出部门ID,员工数量

18:统计出每个员工的客户数量,输出员工ID,客户数量

19:统计出男女员工各有多少人,要求输出性别、人数

20:查询出有员工的部门名称及员工姓名

21:查询出所有部门及其员工信息,没有员工信息的以NULL输出,要求输出部门名称,员工姓名,员工性别,员工年龄。

答案：

/*创建数据库*/
create database gao
on primary 
(name="gao_data",
filename="c:\gao\gao_data.mdf", 
size=8MB,
maxsize=100MB, 
filegrowth=10%) 
log on 
(name="gao_log.ldf", 
filename="c:\gao\gao_log.ldf", 
size=1MB, 
filegrowth=10%) 

use gao/*切换数据库*/

--创建 department表
CREATE TABLE department (
    id INT PRIMARY KEY identity(1,1),
    name VARCHAR(32) NOT NULL UNIQUE,
    description VARCHAR(1024)
);

--创建person表
CREATE TABLE person (
    id INT PRIMARY KEY identity(1,1),
    name VARCHAR(32) NOT NULL,
    sex VARCHAR(8) NOT NULL,
    department_id INT NOT NULL FOREIGN KEY REFERENCES department(id),
    age INT NOT NULL,
);

--创建customer表
CREATE TABLE customer (
    id INT PRIMARY KEY identity(1,1),
    name VARCHAR(32) NOT NULL,
    tel VARCHAR(32) NOT NULL UNIQUE,
    person_id INT NOT NULL FOREIGN KEY REFERENCES person(id),
    detail VARCHAR(1024),
);

--查看department表
select * from department
--添加部门信息
INSERT department values
('人力资源部', '管理人员信息'),
('市场部', '负责市场客户开发及客户维护'),
('财务部', '负责公司财务'),
('软件开发部', '负责公司软件开发')

--查看person表
select * from person
--添加员工信息
INSERT person values
('杨朝来', '男', 1, 35),
('蒋平', '男', 2, 20),
('唐灿', '女', 2, 22),
('马达', '男', 2, 21),
('赵小雪', '女', 2, 18),
('刘小梅', '女', 3, 36)

--查看customer表
select * from customer
--添加客户信息
INSERT customer values
('凡小芬', '13937712345', 2, '已沟通,有意向'),
('文彭凤', '13937745681', 2, '已沟通,有意向'),
('王丽', '1771548746', 2, '电话无法联系'),
('王建华', '13937745615', 3, '已沟通'),
('王梓人', '19912568745', 3, '暂无沟通'),
('王震', '13615648542', 3, '电话无法联系'),
('王保真', '18770126667', 3, '已沟通'),
('王景亮', '18885186808', 4, '已沟通'),
('王丹', '15131426062', 4, '已沟通'),
('邓志勇', '15533605595', 4, '已沟通'),
('邓婕', '15125866659', 5, '已沟通'),
('叶如红', '13722557687', 5, '已沟通'),
('付伟娜', '15831659609', 5, '已沟通')

--6、
update customer
set person_id = 2
where person_id = 5 and name = '邓婕'
--7、
delete from customer
where tel = '13937745615'
--8、
select * from department
--9、
select * from person
--10、
select * from customer
--11、
select * from person
where sex = '女'
--12、
select * from customer where tel like '139%'
--13、
select avg(age) 平均年龄 from person
--14、
select count(id) 总共多少客户 from customer
--15、
select * from person order by age asc
--16、
select * from customer order by id asc
--17、
select department_id,count(name) 员工数量 from person group by department_id
--18、
select person_id,count(name) 客户数量 from customer group by person_id
--19、
select sex,count(name) 人数 from person group by sex
--20、
select de.name,pe.name from department de inner join person pe on de.id=pe.department_id
--21、
select de.name,pe.name,pe.sex,pe.age from department de left join person pe on de.id=pe.department_id