create database a;

use a;

create table employee(

empid varchar(10) primary key not null,

name varchar(10),

gender varchar(10),

title varchar(20),

birthday date,

depid varchar(10));

create table department(

depid varchar(10) primary key not null,

depname varchar(20));

create table salary(

empid varchar(10) primary key not null,

base_salary decimal(8,2),

title_salary decimal(8,2),

deduction int);

#修改表結構，在部門表中添加一個”部門簡介”字段

alter table employee add 部門簡介 varchar(100);

insert into employee values('1001','張三','男','高級工程師','1980-01-01','1111',null);

insert into employee values('1002','李四','女','助理工程師','1980-11-01','1111',null);

insert into employee values('1003','王五','男','工程師','1980-01-21','2222',null);

insert into employee values('1004','趙六','男','工程師','1980-01-11','2222',null);

insert into department(depid,depname) values

('1111','生產(chǎn)部'),

('2222','銷售部'),

('3333','人事部');

insert into salary values

('1001',3200,1200,200),

('1002',4200,1100,100),

('1003',5200,2200,200),

('1004',2000,1500,150);

#5將李四的職稱改為“工程師”，并將她的基本工資改為5700元，職務工資為1600。

update employee set title="工程師" where name="李四";

update salary set base_salary=5700,title_salary=600

where empid=(select empid from employee where name="李四");

#6查詢出每個雇員的雇員編號，姓名，職稱，所在部門，實發(fā)工資和應發(fā)工資。

#涉及employee表和salary表、department表

select employee.empid,name,title,depname,(base_salary+title_salary-deduction) as 實發(fā)工資,

(base_salary+title_salary) as 應發(fā)工資 from employee

left join salary on employee.empid=salary.empid

left join department on department.depid=employee.depid;

#7查詢姓“張”且年齡小于40歲的員工的記錄。

select * from employee where name like"張%" and (year(curdate())-year(birthday))<40;

#8查詢銷售部所有雇員的雇員編號，姓名，職稱，部門名稱，實發(fā)工資。

#涉及employee表和salary表、department表

select employee.empid,name,title,depname,

(base_salary+title_salary-deduction) as 實發(fā)工資 from employee

left join salary on employee.empid=salary.empid

left join department on department.depid=employee.depid where depname="銷售部";

#9統(tǒng)計出各類職稱的人數(shù)。

#按職稱分組

select title,count(*)as 人數(shù) from employee group by title;

#10.統(tǒng)計各部門的部門名稱，實發(fā)工資總和，平均工資。

#涉及三張表及group by分組

select depname,sum(base_salary+title_salary-deduction)as 實發(fā)工資,

avg(base_salary+title_salary-deduction) as 平均工資 from employee

left join salary on employee.empid=salary.empid

left join department on department.depid=employee.depid group by depname;

select * from employee;

select * from salary;

select * from department;