CREATE TABLE Department (
ID int(2) not null primary key,
Name varchar(50) not null,
Location varchar(50) not null);

CREATE TABLE Employee (
ID int(4) not null,
Name varchar(50) not null,
Hiredate date,
Salary decimal(10,2),
DepartmentID int(2) not null);

insert into Department values (10, 'Accounting', 'New York');
insert into Department values (20, 'Research', 'Dallas');
insert into Department values (30, 'Sales', 'Chicago');
insert into Department values (40, 'Operations', 'Boston');
insert into Department values (50, 'IT', 'LA');

insert into Employee values (7369, 'SMITH', '93/6/13', 800.00, 20);
insert into Employee values (7499, 'ALLEN', '98/8/15', 1600.00, 30);
insert into Employee values (7521, 'WARD', '96/3/26', 1250.00, 40);
insert into Employee values (7566, 'JONES', '95/10/31', 2975.00, 20);
insert into Employee values (7698, 'BLAKE', '92/6/11', 2850.00, 30);
insert into Employee values (7782, 'CLARK', '93/5/14', 2450.00, 10);
insert into Employee values (7788, 'SCOTT', '96/3/5', 3000.00, 20);
insert into Employee values (7839, 'KING', '90/6/9', 5000.00, 10);
insert into Employee values (7844, 'TURNER', '95/6/4', 1500.00, 30);

-- write a query to display emploee names and their department names

/*select Employee.Name,Department.Name
from Employee inner join Department
on Employee.DepartmentID = Department.ID;
*/
-- write a query to display the employees with the top 5 heighest salary
/*
select salary ,Name
from employee e1
where (select count(distinct salary)
        from employee e2
        where e1.salary<=e2.salary) in (1,2,3,4,5)
order by salary desc;
*/


-- write a query to find the employee with the second highest salary
/*
select *
from employee e1
where (select count(distinct salary)
        from employee e2
        where e1.salary<=e2.salary) in (2);
*/
-- write a query to find the maximum salary from each department
/*
select Name,salary
from employee
where salary in (select max(salary)
                 from employee
                 group by DepartmentID);
*/
/*
select Department.Name ,employee.salary
from Department inner join employee
on Employee.DepartmentID = Department.ID
where salary in (select max(salary)
                  from Employee
                  group by DepartmentID);

*/
-- write a query to find the number of employees in each department
/*
select count(*),DepartmentID
from Employee
where DepartmentID in (select ID
                        from department)
group by DepartmentID;

*/

select count(Employee.ID),Department.ID
from Department left join Employee
on Employee.DepartmentID = Department.ID
group by DepartmentID;