第2题
案例:
1、创建数据库:day02_test02_company
2、在此数据库下创建如下3表,数据类型,宽度,是否为空根据实际情况自己定义。
A. 部门表(department):部门编号(depid),部门名称(depname),部门简介(deinfo);其中部门编号为主键。
B. 雇员表(emoloyee):雇员编号(empid),姓名(name),性别(sex),职称(title),出生日期(birthday),所在部门编号(depid);其中
- 雇员编号为主键;
部门编号为外键,外键约束等级为(on update cascade 和on delete set null);
性别默认为男;
C. 工资表(salary):雇员编号(empid),基本工资(basesalary),职务工资(titlesalary),扣除(deduction)。其中雇员编号为主键。
3、给工资表(salary)的雇员编号(empid)增加外键约束,外键约束等级为(on update cascade 和on delete cascade)
4、添加数据如下:
部门表:
部门编号 | 部门名称 | 部门简介 |
---|---|---|
111 | 生产部 | Null |
222 | 销售部 | Null |
333 | 人事部 | 人力资源管理 |
雇员表:
雇员编号 | 姓名 | 性别 | 职称 | 出生日期 | 所在部门编号 |
---|---|---|---|---|---|
1001 | 张三 | 男 | 高级工程师 | 1975-1-1 | 111 |
1002 | 李四 | 女 | 助工 | 1985-1-1 | 111 |
1003 | 王五 | 男 | 工程师 | 1978-11-11 | 222 |
1004 | 张六 | 男 | 工程师 | 1999-1-1 | 222 |
工资表:
雇员编号 | 基本工资 | 职务工资 | 扣除 |
---|---|---|---|
1001 | 2200 | 1100 | 200 |
1002 | 1200 | 200 | NULL |
1003 | 2900 | 700 | 200 |
1004 | 1950 | 700 | 150 |
5、查询出每个雇员的雇员编号,姓名,职称,所在部门名称,应发工资(基本工资+职务工资),实发工资(基本工资+职务工资-扣除)。
6、查询销售部门的雇员姓名及其基本工资
7、查询姓“张”且年龄小于40的员工的全部信息和年龄
8、查询所有男员工的基本工资和职务工资
9、查询基本工资低于2000的员工姓名和职称、所在部门名称
10、查询员工总数
11、查询部门总数
12、查询应发工资的平均工资和最高工资、最低工资
13、按照部门统计应发工资的平均工资
14、找出部门基本工资的平均工资低于2000的
15、按照员工编号、姓名、基本工资、职务工资、扣除,并按照职务升序排列,如果职务工资相同,再按照基本工资升序排列
16、查询员工编号、姓名,出生日期,及年龄段,其中
如果80年之前出生的,定为”老年“;80后定为”中年“,90后定为”青壮年“
17、查询所有的员工信息,和他所在的部门名称
18、查询所有部门信息,和该部门的员工信息
19、查询所有职位中含“工程师”的男员工的人数
20、查询每个部门的男生和女生的人数和平均基本工资
参考答案:
#创建数据库:day02_test02_company
CREATE DATABASE day02_test02_company;
#使用数据库day02_test02_company
USE day02_test02_company;
#创建部门表(department)
CREATE TABLE department(
depid INT PRIMARY KEY,
depname VARCHAR(20) NOT NULL,
deinfo VARCHAR(50)
);
#创建雇员表(emoloyee)
CREATE TABLE employee(
empid INT PRIMARY KEY,
`name` VARCHAR(20) NOT NULL,
sex CHAR NOT NULL DEFAULT '男',
title VARCHAR(20) NOT NULL,
birthday DATE,
depid INT,
FOREIGN KEY