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 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, Department
where Employee.DepartmentID = Department.ID;

Select CHAR(13);

-- write a query to display the employees with the top 5 heighest salary
Select Employee.Name, Employee.salary
from Employee
order by Employee.salary desc
limit 5;

Select CHAR(13);

-- write a query to find the employee with the second highest salary
select Name, salary
from Employee 
group by salary
order by salary desc
limit 1,1;

Select CHAR(13);
-- write a query to find the maximum salary from each department

select Employee.Name, Department.Name, max(salary)
from Employee, Department
where Employee.DepartmentID = Department.ID
group by DepartmentID