sql

show databases;
create database employee;
use employee;
create table emp (emp_id int primary key, name varchar(50),
department int, salary int, hiredate date);
insert into emp values(
1, 'vaishnavi', 1, 4500, '2024-10-10'
)

SELECT * FROM employee.emp;
insert into emp values( 2, 'sunita', 1, 4900, '2024-10-10'
), ( 3, 'mayur', 2, 8700, '2024-10-10'
), ( 4, 'gaurav', 3, 5500, '2024-10-10'
), ( 5, 'kishor', 1, 10500, '2024-10-10'
);

select * from emp where salary > 5000;
create table department(
department int primary key, d_name char(50));
insert into department values(
1, 'IT'),
(2, 'COMP'),
(3, 'MECH');

select * from emp;
insert into emp value(6, 'sunita', 1, 4900, '2024-10-10');

-- dublicate entry
select count(), name
from emp
group by name
having count() >1;

select * from emp where emp_id not in(
(select min(emp_id) from emp
group by name));

select * from emp;
select max(salary) from emp
where salary < (select max(salary) from emp);
delete from emp where emp_id = Null;
select count(*) from emp;

select e.name, d.d_name
from emp as e
inner join department as d on e.department = d.department;

select * from emp order by salary desc limit 2 offset 1;

select count(), name from emp
group by department, name
order by count() desc limit 2;

alter table emp add manager int;
create table manager (m_id int primary key, salary int);
insert into manager values('1', 3000), ('2', 6000) , ('3', 2800);

select *
from emp as e
inner join manager as m on e.manager = m.m_id
where e.salary > m.salary;

select department, sum(salary) from emp
group by department;

select department, sum(salary) as total_salary
from emp
group by department
having total_salary > 8500;

select count(e.emp_id) as number_of_emp , d.d_name
from emp as e
inner join department as d on e.department = d.department
group by d.department
having number_of_emp > 1;

select max(e.salary), d.d_name
from emp as e
inner join department as d on e.department = d.department
group by d.department;

select e.emp_id, count(e.emp_id), e.name, m.m_id
from emp as e
inner join manager as m on e.manager = m.m_id
group by m.m_id, e.emp_id, e.name