1)select * from Employee;
2)select name, salary from Employee;
3)select name from Employee where age>30;
4)select * from Department;
5)select name from Employee where department_id='1';
6)select name from Employee where name like 'J%';
select name from Employee where name like '%e';
select name from Employee where name like '%a%';
select name from Employee where length(name)=9;
select name from Employee where name like '_o%';
select name from Employee where year(hire_date)=2020;
select name from Employee where month(hire_date)=01;
select name from Employee where year(hire_date)<2019;
select name from Employee where hire_date='2021-03-01';
select sum(salary) from employee;
select avg(age) from employee;
select min(salary) from employee;
select department_id,count(*) from Employee group by department_id
select avg(salary),department_id from employee group by department_id;
select department_id,sum(salary) as total_salary from Employee group by
department_id;
select avg(age),department_id from employee group by department_id;
select year(hire_date),count(*) from employee group by
year(hire_date);
select max(salary),department_id from Employee group by department_id;
select department_id, avg(salary) from Employee group by department_id order by
avg(salary) desc limit 1;
select department_id , count(*) from Employee group by department_id having
count(*)>=2;
select department_id,avg(salary) from Employee group by department_id having
avg(salary)>55000;
select year(hire_date),count(*) from Employee group by year(hire_date) having
count(*)>1;
select department_id,sum(salary) from Employee group by department_id having
sum(salary)<100000;
select department_id,max(salary) from Employee group by department_id having
max(salary)>75000;
select name,salary from Employee order by salary asc;
select name , age from Employee order by age desc;
select name, date(hire_date) from Employee order by date(hire_date)
asc;
select department_id,salary from Employee order by department_id,salary asc;
select department_id, sum(salary) from Employee group by department_id order by
sum(salary) desc;
select [Link],[Link]
from Employee
inner join Department
on Employee.department_id = Department.department_id;
select [Link],[Link]
from Project
inner join Department
on Project.department_id = Department.department_id;
select [Link],[Link]
from employee
inner join project
on employee.department_id=project.department_id;
select [Link] AS EMP_NAME,[Link] AS DEPART_NAME from
employee left join department
on employee.department_id=department.department_id
select department.department_id as dept_name ,[Link] as emp_name
from department right join employee
on department.department_id = employee.department_id;
select [Link] , [Link] from employee
left join project on employee.department_id =project.department_id
where project_id is null;
select employee.emp_id,count(project.project_id) from employee
left join project on employee.department_id=project.department_id
group by emp_id order by emp_id asc;
select [Link] from department
left join employee
on department.department_id=employee.department_id
where emp_id is null;
select [Link] from employee e1 join employee e2
on e1.department_id=e2.department_id where [Link] ='John Doe' and [Link] !
='John Doe';
select [Link] ,avg(salary) as avg_salary from department
join employee
on department.department_id=employee.department_id group by
[Link] order by avg_salary desc limit 1;
select name,salary from employee where salary=(select max(salary) from
employee);
select name,salary from employee where salary > (select avg(salary) from
employee);
select name,salary from employee where salary > (select avg(salary) from
employee);
SELECT name, age
FROM Employee
WHERE age > (
SELECT MAX(age)
FROM Employee
WHERE department_id = (
SELECT department_id
FROM Department
WHERE name = 'HR'
)
);
select department_id from department where salary > (select avg(salary) from
employee where salary=(select avg(salary)>55000 from employee));
SELECT department_id,name
FROM Department
WHERE department_id IN (
SELECT department_id
FROM Employee
WHERE salary > (
SELECT AVG(salary)
FROM Employee
WHERE (
SELECT AVG(salary) > 55000
FROM Employee
)
)
);
SELECT department_id
FROM Project
GROUP BY department_id
HAVING COUNT(project_id) >= 2;