建立员工表并完成相应操作

一、要求使用SQL语句完成下列操作：
(1)使用Create语句，创建YGGL数据库，员工、部门表。
(2)使用插入语句完成模拟数据的录入
create table Department(
     DepartmentID  Char(3)  not null,
     DepartmentName  Varchar2(20) not null,
     Note  Varchar2(100),
     primary key(DepartmentID)
);
create table Employees (
       EmployeesID char (6) not null ,
       EmployeesName   Varchar2(20) not null,
       Education   char(10)  not null,
       Birthday    Date       not null,
       Sex         char(1)  not null,
       Salary   Float     not null,
       Address  Varchar2(40),
       PhoneNumber   Char(12),
       DepartmentID Char(3) not null,
       primary key(EmployeesID),
       foreign key (DepartmentID) references Department(DepartmentID)
);
insert into Department ( DepartmentID , DepartmentName , Note)
values ('1','研发部','Null');

insert into Department ( DepartmentID , DepartmentName , Note)
values ('2','市场部','Null');

insert into Department ( DepartmentID , DepartmentName , Note)
values ('3','办公室','Null');

insert into Employees (EmployeesID,EmployeesName, Education , Birthday ,
  Sex , Salary,Address ,PhoneNumber,DepartmentID )
values ('000001','王林','大专',to_date('1966-01-23','yyyy-mm-dd'),'1','2000','中山路 12','8334555','1');

insert into Employees (EmployeesID,EmployeesName, Education , Birthday ,
  Sex , Salary,Address ,PhoneNumber,DepartmentID )
values ('010008','武华','本科',to_date('1976-03-26','yyyy-mm-dd'),'1',2100,'北京东路 1','8023444','2');

insert into Employees (EmployeesID,EmployeesName, Education , Birthday ,
  Sex , Salary,Address ,PhoneNumber,DepartmentID )
values ('020010','王向蓉','硕士',to_date('1982-12-12','yyyy-mm-dd'),'0',3100,'解放路3','4354445','3');

insert into Employees (EmployeesID,EmployeesName, Education , Birthday ,
  Sex , Salary,Address ,PhoneNumber,DepartmentID )
values ('020018','李立','本科',to_date('1963-05-12','yyyy-mm-dd'),'0',2000,'北京西路6','8119900','1');

insert into Employees (EmployeesID,EmployeesName, Education , Birthday ,
  Sex , Salary,Address ,PhoneNumber,DepartmentID )
values ('102201','刘明','专科',to_date('1972-10-18','yyyy-mm-dd'),'1',5000,'商城路45','8229954','1');
Z
insert into Employees (EmployeesID,EmployeesName, Education , Birthday ,
  Sex , Salary,Address ,PhoneNumber,DepartmentID )
values ('102208','朱俊','硕士',to_date('1965-09-28','yyyy-mm-dd'),'0',4210,'农业路65','8333424','2');

insert into Employees (EmployeesID,EmployeesName, Education , Birthday ,Sex , Salary,Address ,PhoneNumber,DepartmentID )
values ('108991','钟明','本科',to_date('1979-08-08','yyyy-mm-dd'),'1',2000,'东风路12','8004499','2');

drop table Employees;
drop table Department;

二、使用Select语句完成下列查询
(1)查询所有员工信息  
  select *  
  from Employees ;  
      
(2)查询每一个员工的电话和住址  
   select EmployeesID,PhoneNumber,DepartmentID
   from Employees 
   order by EmployeesID;

(3)查询编号为000001员工的住址和电话
  select PhoneNumber,DepartmentID
  from Employees
  where EmployeesID='000001';
?
(4)查询月收入高于3000的员工号码
  select EmployeesID,EmployeesName
  from Employees
  where Salary>3000 ;
?
(5)查询1970年以后出生的员工姓名
  select Employeesname from Employees 
  where Birthday > to_date('1970-01-01','yyyy-mm-dd');

(6)查询女员工的电话和住址，使用AS子句将结果中列标题指定为电话和住址
  Select PhoneNumber as 电话,Address as 住址
  From Employees
  Where sex=’0’;
?
  select '电话'，PhoneNumber ,'地址'，Address 
  from Employees
  where sex='0';
?
(7)查询每个员工的基本工资
  select EmployeesID,Salary
  from Employees
  order by Salary DESC;
?
(8)统计员工总数
   select '员工总数’,count(distinct EmployeesID)
   from Employees;

(9)计算所有员工工资总和
   select '员工工资总和',sum(salary)
   from Employees;

(10)找出姓王员工的部门编号
  select EmployeesName,DepartmentID
  from Employees
  where EmployeesName like '王%';

(11)找出工资在2000-3000之间的员工编号
  select EmployeesID,salary
  from Employees
  where salary>=2000 and salary<=3000 ;

(12)查询每一个员工的姓名和部门名称
  select EmployeesName,DepartmentName
  from Employees,Department
  where Employees.DepartmentID=Department.DepartmentID
?
(13)查询王林员工所在的部门名称
select EmployeesName,DepartmentName
from Employees,Department
where Employees.DepartmentID=Department.DepartmentID and EmployeesName='王林';

(14)查询和王林一个部门的员工姓名
select DepartmentName,EmployeesName
from Employees,Department
where Employees.DepartmentID=Department.DepartmentID and Employees.DepartmentID='1';

(15)统计各部门的人数
select distinct DepartmentName ,count(Employees.DepartmentID)
from Department,Employees
where Employees.DepartmentID=Department.DepartmentID
group by DepartmentName;

(16)统计各部门男女员工数
select   DepartmentName,Sex,count(Sex)
from Department,Employees
where Employees.DepartmentID=Department.DepartmentID
group by DepartmentName,Sex

(17)查询市场部员工的平均工资
select DepartmentName,avg(Salary)
from Department,Employees
where Employees.DepartmentID=Department.DepartmentID and  Employees.DepartmentID='2'
group by DepartmentName


(18)查询超过2个员工的部门名称和员工数量
select distinct DepartmentName ,count(Employees.DepartmentID)
from Department,Employees
where Employees.DepartmentID=Department.DepartmentID 
group by DepartmentName 
having  (count(Employees.DepartmentID))>2 


(19)将员工的情况按收入由低到高排列
select EmployeesID,EmployeesName,Salary
from Employees
order by Salary;