Task - Joins - hr database #8
Replies: 36 comments 2 replies
1. select department_name,LOCATION_ID,street_address,city,state_province,
country_name from locations inner join countries
using(COUNTRY_ID) inner join departments
using(LOCATION_ID) group by DEPARTMENT_ID;
2. select CONCAT(first_name," ",last_name) as name,department_id,
department_name from employees inner join departments
using(DEPARTMENT_ID);
3. select CONCAT(first_name, " ", last_name) as name,job_title,
department_id from employees inner join jobs
USING(JOB_ID) INNER JOIN departments
using(DEPARTMENT_ID) where location_id in
(SELECT location_id FROM locations WHERE city="London");
4. select e1.employee_id,e1.last_name,e1.manager_id,e2.last_name
from employees e1 inner join employees e2
on e1.manager_id = e2.employee_id;
5. select CONCAT(first_name, " ", last_name) as name,hire_date
from employees where hire_date > (select HIRE_DATE FROM employees
where last_name="Jones");
6. SELECT department_name,count(*) as noOfEmployees FROM employees
inner join departments using(DEPARTMENT_ID) group by DEPARTMENT_ID;
7. select employee_id,job_title,day(end_date)-day(start_date) as diff
from job_history inner join jobs using(JOB_ID) where department_id=90;
8. select e1.department_id,CONCAT(e1.first_name, " ",e1.last_name) as name,
e2.first_name from employees e1 inner join
employees e2 on e1.manager_id=e2.employee_id;
9. select department_name,CONCAT(first_name, " ",last_name) as manager,city from employees
inner join departments d using(department_id) inner join locations
using(location_id) where employee_id=d.manager_id;
10. select job_title,avg(salary) from jobs
inner join employees using(job_id)
group by job_id;
11. select job_title,CONCAT(first_name, " ", last_name) as emp_name,
salary-min_salary as sal_diff from employees inner join jobs
using(job_id);
12. select job_history.* from job_history INNER JOIN employees
using(employee_id) where salary>10000;
13. select department_name,CONCAT(first_name, " ",last_name) as name,hire_date,
salary from employees inner join departments d using(DEPARTMENT_ID)
inner join job_history using(EMPLOYEE_ID) where employee_id=d.manager_id and
end_date-start_date>15;
|
|
Updated sir @akash-coded |
|
--1
SELECT LOCATION_ID, STREET_ADDRESS, CITY, STATE_PROVINCE, COUNTRY_NAME
FROM locations INNER JOIN countries USING(COUNTRY_ID);
--2
SELECT CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, DEPARTMENT_ID, DEPARTMENT_NAME
FROM employees INNER JOIN departments USING (DEPARTMENT_ID);
--3
SELECT CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, JOB_TITLE, DEPARTMENT_ID
FROM employees INNER JOIN jobs USING(JOB_ID)
INNER JOIN departments USING(DEPARTMENT_ID)
INNER JOIN locations USING (LOCATION_ID)
WHERE locations.city = "London";
--4
SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.MANAGER_ID, e2.LAST_NAME
FROM employees e INNER JOIN employees e2
ON e.MANAGER_ID = e2.EMPLOYEE_ID;
--5
SELECT CONCAT(e1.FIRST_NAME, " ", e1.LAST_NAME) AS NAME, e1.HIRE_DATE
FROM employees e1 INNER JOIN employees e2
ON e2.LAST_NAME = 'Jones'
WHERE e1.HIRE_DATE > e2.HIRE_DATE;
--6
SELECT DEPARTMENT_NAME, COUNT(EMPLOYEE_ID)
FROM employees INNER JOIN departments
USING (DEPARTMENT_ID)
GROUP BY DEPARTMENT_NAME;
--7
SELECT EMPLOYEE_ID, JOB_TITLE, (END_DATE - START_DATE) AS NUMBER_OF_DAYS
FROM jobs
INNER JOIN job_history USING (JOB_ID)
WHERE DEPARTMENT_ID = 90;
--8
SELECT departments.DEPARTMENT_ID, DEPARTMENT_NAME, FIRST_NAME
FROM departments INNER JOIN employees USING(DEPARTMENT_ID)
WHERE departments.MANAGER_ID = employees.EMPLOYEE_ID;
--9
SELECT DEPARTMENT_NAME, CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, CITY
FROM departments INNER JOIN employees USING (DEPARTMENT_ID)
INNER JOIN locations USING (LOCATION_ID)
WHERE departments.MANAGER_ID = employees.EMPLOYEE_ID;
--10
SELECT JOB_TITLE, AVG(SALARY)
FROM employees INNER JOIN JOBS USING(JOB_ID)
GROUP BY JOB_TITLE;
--11
SELECT JOB_TITLE, CONCAT(FIRST_NAME," ",LAST_NAME) AS NAME, (SALARY - MIN_SALARY) AS SALARY_DIFFERENCE
FROM employees INNER JOIN jobs USING (JOB_ID);
--12
SELECT job_history.* FROM job_history INNER JOIN employees USING (EMPLOYEE_ID)
WHERE SALARY > 10000;
--13
SELECT DEPARTMENT_NAME, CONCAT(FIRST_NAME," ", LAST_NAME) AS NAME, HIRE_DATE, SALARY
FROM employees INNER JOIN departments USING (DEPARTMENT_ID)
INNER JOIN job_history USING (EMPLOYEE_ID)
WHERE END_DATE-START_DATE > 15;
|
|
Updated |
1. SELECT LOCATION_ID, CITY, STATE_PROVINCE, COUNTRY_NAME FROM LOCATIONS INNER JOIN COUNTRIES USING(COUNTRY_ID);
2. SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS NAME, DEPARTMENT_ID, DEPARTMENT_NAME FROM EMPLOYEES INNER JOIN DEPARTMENTS USING(DEPARTMENT_ID);
3. SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS NAME, JOB_TITLE, DEPARTMENT_ID
FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID)
INNER JOIN DEPARTMENTS USING(DEPARTMENT_ID)
INNER JOIN LOCATIONS USING(LOCATION_ID)
WHERE LOCATIONS.CITY = 'LONDON';
4. SELECT e1.EMPLOYEE_ID, e1.LAST_NAME, e1.MANAGER_ID, e2.LAST_NAME
FROM EMPLOYEES e1 INNER JOIN EMPLOYEES e2
ON E1.MANAGER_ID = e2.EMPLOYEE_ID;
5. SELECT CONCAT(e1.FIRST_NAME,' ',e1.LAST_NAME) AS NAME, e1.HIRE_DATE
FROM EMPLOYEES e1 INNER JOIN EMPLOYEES e2 ON e2.LAST_NAME = 'Jones'
WHERE e1.HIRE_DATE > e2.HIRE_DATE;
6. SELECT COUNT(*) AS NoOfEmployee , DEPARTMENT_NAME FROM EMPLOYEES
INNER JOIN DEPARTMENTS USING(DEPARTMENT_ID) GROUP BY DEPARTMENT_ID;
7. SELECT EMPLOYEE_ID, JOB_TITLE, (END_DATE - START_DATE) AS NUMBEROFDAYS
FROM JOBS INNER JOIN JOB_HISTORY USING(JOB_ID) WHERE DEPARTMENT_ID = '90';
8. SELECT e1.DEPARTMENT_ID, CONCAT(e1.FIRST_NAME,' ',e1.LAST_NAME) AS NAME, e2.FIRST_NAME
FROM EMPLOYEES e1 INNER JOIN EMPLOYEES e2
ON e1.MANAGER_ID = e2.EMPLOYEE_ID;
9. SELECT DEPARTMENT_NAME, CONCAT(FIRST_NAME, ' ', LAST_NAME) AS MANAGER_NAME, CITY
FROM EMPLOYEES INNER JOIN DEPARTMENTS d USING(DEPARTMENT_ID)
INNER JOIN LOCATIONS USING(LOCATION_ID) WHERE EMPLOYEE_ID = d.MANAGER_ID;
10. SELECT JOB_TITLE, AVG(SALARY) FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID) GROUP BY JOB_ID;
11. SELECT JOB_TITLE, CONCAT(FIRST_NAME," ",LAST_NAME) AS NAME, (SALARY - MIN_SALARY) AS DIFFERENCE
FROM EMPLOYEES INNER JOIN jobs USING (JOB_ID);
12. SELECT jh.* FROM job_history jh INNER JOIN EMPLOYEES USING (EMPLOYEE_ID) WHERE SALARY > 10000;
13. SELECT DEPARTMENT_NAME, CONCAT(FIRST_NAME," ", LAST_NAME) AS NAME, HIRE_DATE, SALARY FROM EMPLOYEES INNER JOIN
DEPARTMENTS USING (DEPARTMENT_ID) INNER JOIN JOB_HISTORY USING (EMPLOYEE_ID) WHERE END_DATE-START_DATE > 15; |
1. select location_id,street_address,city,state_province,country_name from departments inner join locations using(location_id) inner join countries using(country_id) group by department_id;
2. select concat(first_name,' ',last_name)as Name,department_id,department_name from employees inner join departments using (department_id) order by department_name;
3. select concat(first_name,' ',last_name)as Name,department_id,department_name from employees inner join departments using(department_id) inner join locations using(location_id) where city='London';
4. select e1.employee_id, e1.last_name,e2.manager_id,e2.last_name from employees e1 inner join employees e2 where e1.manager_id=e2.employee_id;
5. select concat(first_name,' ',last_name)as Name,hire_date from employees where hire_date > (select hire_date from employees where last_name='Jones');
6. select department_name,count(employee_id) from departments inner join employees using(department_id) group by department_id;
7. select employee_id,job_title,end_date-start_date from job_history inner join jobs using (job_id) where department_id=90;
8. select first_name,d.department_id,d.department_name from departments d inner join employees using(manager_id);
9. select d.department_name,concat(first_name,' ',last_name),city from departments d inner join employees using(manager_id) inner join locations using (location_id);
10. select job_title,avg(salary) from jobs inner join employees using(job_id) group by job_title; |
1.select department_id, location_id, street_address, city, state_province, country_name
from departments inner join locations using(location_id)inner join
countries using(country_id) group by department_id;
2.Select CONCAT(first_name," ",last_name)as name,department_id
,department_name from employees inner join departments using
(department_id);
3.Select CONCAT(first_name," ",last_name)as name,job_id,department_id
from employees inner join departments using(department_id)inner join
locations using(location_id)where city='London';
4.select e.employee_id,e.last_name,e.manager_id,e2.last_name from employees
e inner join
employees e2 on e.manager_id=e2.employee_id;
5.Select CONCAT(e1.first_name," ",e1.last_name)as name,e1.hire_date from
employees e1 inner join employees e2 on e2.last_name='Jones' where
e1.hire_date>e2.hire_date;
6.Select department_name,count(*)from employees inner join
departments using(department_id)group by department_id;
7.select employee_id,job_title,(end_date-start_date)as days from
jobs inner join job_history
using(job_id)where department_id=90;
8.SELECT e1.DEPARTMENT_ID, CONCAT(e1.FIRST_NAME,' ',e1.LAST_NAME) AS NAME, e2.FIRST_NAME
FROM EMPLOYEES e1 INNER JOIN EMPLOYEES e2
ON e1.MANAGER_ID = e2.EMPLOYEE_ID;
9.select department_name,conact(first_name," ",last_name)manager_name,
city from employees e inner join departments d using(department_id)inner join
locations l using(location_id)where d.manager_id=e.employee_id;
10.select job_title,avg(salary)from employees inner join jobs
using(job_id)group by job_id;
|
1. SELECT LOCATION_ID, CITY, STATE_PROVINCE, COUNTRY_NAME FROM locations INNER JOIN countries USING(COUNTRY_ID);
2. SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS NAME, DEPARTMENT_ID, DEPARTMENT_NAME FROM EMPLOYEES INNER JOIN DEPARTMENTS USING(DEPARTMENT_ID);
3. SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS NAME, JOB_TITLE, DEPARTMENT_ID
FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID)
INNER JOIN DEPARTMENTS USING(DEPARTMENT_ID)
INNER JOIN LOCATIONS USING(LOCATION_ID)
WHERE LOCATIONS.CITY = 'LONDON';
4. SELECT e1.EMPLOYEE_ID, e1.LAST_NAME, e1.MANAGER_ID, e2.LAST_NAME
FROM EMPLOYEES e1 INNER JOIN EMPLOYEES e2
ON E1.MANAGER_ID = e2.EMPLOYEE_ID;
5. SELECT CONCAT(e1.FIRST_NAME,' ',e1.LAST_NAME) AS NAME, e1.HIRE_DATE
FROM EMPLOYEES e1 INNER JOIN EMPLOYEES e2 ON e2.LAST_NAME = 'Jones'
WHERE e1.HIRE_DATE > e2.HIRE_DATE;
6. SELECT COUNT(*) AS NoOfEmployee , DEPARTMENT_NAME FROM EMPLOYEES
INNER JOIN DEPARTMENTS USING(DEPARTMENT_ID) GROUP BY DEPARTMENT_ID;
7. SELECT EMPLOYEE_ID, JOB_TITLE, (END_DATE - START_DATE) AS NUMBEROFDAYS
FROM JOBS INNER JOIN JOB_HISTORY USING(JOB_ID) WHERE DEPARTMENT_ID = '90';
8. SELECT e1.DEPARTMENT_ID, CONCAT(e1.FIRST_NAME,' ',e1.LAST_NAME) AS NAME, e2.FIRST_NAME
FROM EMPLOYEES e1 INNER JOIN EMPLOYEES e2
ON e1.MANAGER_ID = e2.EMPLOYEE_ID;
9. SELECT DEPARTMENT_NAME, CONCAT(FIRST_NAME, ' ', LAST_NAME) AS MANAGER_NAME, CITY
FROM EMPLOYEES INNER JOIN DEPARTMENTS d USING(DEPARTMENT_ID)
INNER JOIN LOCATIONS USING(LOCATION_ID) WHERE EMPLOYEE_ID = d.MANAGER_ID;
10. SELECT JOB_TITLE, AVG(SALARY) FROM EMPLOYEES INNER JOIN JOBS USING(JOB_ID) GROUP BY JOB_ID; |
use hr;
---Q1.
SELECT d.Department_name,l.location_id, l.street_address,l.city,l.state_province,(SELECT Country_name from countries where country_id = l.country_id) as Country
from departments d inner join locations l
using (location_id);
---Q2.
SELECT concat(first_name," ",last_name) as Name, Department_id, Department_name
from employees inner join departments
using (DEPARTMENT_ID);
---Q3.
SELECT concat(first_name," ",last_name) as Name ,(SELECT job_title from jobs where job_id = e.job_id) as JOB, department_id
from employees e inner join departments d
using (department_id)
Where d.location_id = (Select location_id from locations where city = 'London');
---Q4.
SELECT e1.employee_id,e1.last_name , e1.manager_id , e2.last_name
from employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.employee_id;
---Q5.
select CONCAT(first_name, " ", last_name) as name,hire_date
from employees
where hire_date > (select hire_date FROM employees where last_name="Jones");
---Q6.
SELECT COUNT(department_id) as `No of Employees`, department_name
from employees
inner join departments
using (department_id)
GROUP BY DEPARTMENT_ID;
---Q7.
SELECT Employee_id, job_title, (DAY(end_date) - DAY(start_date)) as `No of Days`
from job_history e
inner join jobs
using (job_id)
where department_id = 90;
---Q8.
SELECT e1.department_id,CONCAT(e1.first_name, " ",e1.last_name) as name, e2.first_name
from employees e1 inner join
employees e2 on e1.manager_id=e2.employee_id;
---Q9.
SELECT department_name,CONCAT(first_name, " ",last_name) as manager,city
from employees e
inner join departments d
using(department_id)
inner join locations
using(location_id)
where e.employee_id=d.manager_id;
---Q10.
SELECT job_title, AVG(salary) as Avg_salary
FROM employees
inner join jobs using (job_id)
group by job_id;
---Q11.
SELECT job_title,CONCAT(first_name, " ",last_name) as `Name`, (salary - min_salary) as Diff_In_Sal
FROM employees inner join jobs
using (job_id);
---Q12.
SELECT CONCAT(first_name, " ",last_name)as Name,job_history.* FROM job_history
INNER JOIN employees
using (job_id)
WHERE salary > 10000;
---Q13.
SELECT department_name,CONCAT(first_name, " ",last_name) as Name, hire_date, salary
FROM employees
INNER JOIN departments d
using (department_id)
WHERE (YEAR(NOW()) - YEAR(Hire_Date)) > 15
AND employee_id=d.manager_id; |
1.select location_id,street_address,city,state_province,country_id from departments natural join locations;
2.select e.first_name,e.last_name,d.department_id,d.department_name from employees e natural join departments d;
3.select e.first_name,e.last_name,d.department_id,d.department_name,c.city from employees e inner join departments d on e.department_id=d.department_id
inner join locations c on d.location_id=c.location_id where c.city="london";
4.select e.employee_id as employee_id,e.last_name as employee_name,d.manager_id as Manager_id,d.last_name as manager_name from employees e inner join employees d on e.employee_id=d.manager_id;
5.select first_name,last_name from employees where hire_date > (
select hire_date from employees where last_name="Jones");
6.select b.department_name,count(*) as countOfEmployees from employees a inner join departments b where a.department_id=b.department_id group by b.department_id;
7.select a.employee_id,c.job_title,day(b.end_date)-day(b.start_date) from employees a inner join job_history b on a.employee_id=b.employee_id
inner join jobs c on a.job_id=c.job_id where a.department_id=90;
8.select a.department_id,concat(a.first_name,a.last_name) as employeeName,b.first_name as managerName from employees a inner join employees b where a.employee_id=b.manager_id;
9.select a.department_name,concat(b.first_name," ",b.last_name) as name,c.city from departments a inner join employees b on a.manager_id=b.manager_id inner join
locations c on a.location_id=c.location_id;
10.select b.job_title,avg(a.salary) from employees a inner join jobs b using (job_id) group by b.job_title;
12.select b.employee_id,b.start_date,b.end_date from employees a inner join job_history b using (job_id) where salary>10000;
|
--joins
--1
select l.location_id,
l.street_address,
l.city,
l.state_province,
c.country_name
from locations l
inner join countries c using (COUNTRY_ID)
inner join departments d using (LOCATION_ID)
group by d.DEPARTMENT_ID;
--2
select concat(e.first_name, ' ', e.last_name) as name,
e.employee_id,
d.department_name
from employees e
inner join departments d using (DEPARTMENT_ID);
--3
select concat(e.first_name, ' ', e.last_name) as name,
j.job_title,
d.department_id
from employees e
inner join jobs j using (JOB_ID)
inner join departments d using (DEPARTMENT_ID)
inner join locations l using(LOCATION_ID)
where l.city = 'London';
--4
select e.employee_id,
e.last_name as name,
e.manager_id,
m.last_name as name
from employees e
inner join employees m on e.MANAGER_ID = m.EMPLOYEE_ID;
--5
select concat(e.first_name, ' ', e.last_name) as name,
e.hire_date from employees e inner join employees e1
on e1.LAST_NAME='jones' where
e.HIRE_DATE>e1.HIRE_DATE;
--6
select d.department_name,
count(e.employee_id)
from employees e
inner join departments d using (DEPARTMENT_ID)
GROUP BY DEPARTMENT_NAME;
--7
select jh.employee_id,
j.job_title,
(jh.end_date - jh.start_date) as numberOfDays
from job_history jh
INNER JOIN jobs j using(JOB_ID)
where jh.DEPARTMENT_ID = 90;
--8
SELECT d.department_id,
d.department_name,
e.first_name
from departments d
inner join employees e
on d.MANAGER_ID=e.EMPLOYEE_ID;
--9
select d.department_name,
concat(e.first_name, ' ', e.last_name)
as manager_name,l.city from departments d inner join employees e
using(DEPARTMENT_ID) inner join locations l using (LOCATION_ID)
where d.MANAGER_ID=e.EMPLOYEE_ID;
--10
SELECT j.job_title, avg(e.salary) as salary from employees e
inner join jobs j using(JOB_ID) group by j.JOB_TITLE;
--11
select j.job_title,concat(e.first_name, ' ', e.last_name) as name,
(e.salary-j.min_salary)
as difference from employees e
inner join jobs j using(JOB_ID);
--12
select e.EMPLOYEE_ID,e.HIRE_DATE,jh.END_DATE,
e.JOB_ID,e.DEPARTMENT_ID
from job_history jh right join
employees e using(job_id) where
e.SALARY>10000;
--13
select d.DEPARTMENT_NAME ,
concat(e.first_name, ' ', e.last_name) as name,
e.hire_date,e.salary from employees e inner join departments d using(DEPARTMENT_ID)
inner join job_history jh
using(EMPLOYEE_ID) where YEAR(jh.END_DATE-e.HIRE_DATE)>15;
|
use hr;
--1)
SELECT DISTINCT dep.location_id, loc.street_address, loc.city, loc.state_province, cun.country_name
FROM departments dep INNER JOIN locations loc ON dep.location_id = loc.location_id
INNER JOIN countries cun ON loc.country_id = cun.country_id;
-- 2)
SELECT CONCAT(first_name," ",last_name) AS name, department_id,department_name
FROM employees e INNER JOIN departments d USING (department_id);
-- 3)
SELECT CONCAT(first_name," ",last_name) AS name,job_title ,department_id
FROM employees e INNER JOIN departments d USING (department_id)
INNER JOIN locations l ON d.location_id = l.location_id
INNER JOIN jobs j ON e.job_id = j.job_id
WHERE l.city = "London" ;
-- 4)
SELECT e1.employee_id, e1.last_name AS name,e1.manager_id,e2.last_name AS name
FROM employees e1 Left JOIN employees e2 ON e2.employee_id = e1.manager_id;
-- 5)
SELECT CONCAT(first_name," ",last_name) AS name,hire_date
FROM employees e WHERE hire_date > (SELECT hire_date FROM employees WHERE last_name = "Jones");
-- 6)
SELECT d.department_name, COUNT(*) AS NoOfEmployees
FROM departments d INNER JOIN employees e USING (department_id)
GROUP BY department_id;
--7)
SELECT employee_id,job_title,(end_date - start_date) AS jobDuration
FROM job_history jh INNER JOIN jobs j USING (job_id)
WHERE department_id = 90;
-- 8)
SELECT e1.department_id,CONCAT(e1.first_name," ",e1.last_name) AS name,e2.last_name AS manager
FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;
--9)
SELECT department_name,CONCAT(e.first_name," ",e.last_name) AS manager_name,city
FROM employees e INNER JOIN departments d USING (department_id)
INNER JOIN locations l ON d.location_id = l.location_id
WHERE employee_id = d.manager_id;
-- 10)
SELECT job_title,FLOOR(AVG(salary))
FROM employees e INNER JOIN jobs j USING(job_id)
GROUP BY job_id;
-- 11)
SELECT job_title,CONCAT(e.first_name," ",e.last_name) AS EmployeeName,salary - min_salary AS SalaryDiff
FROM employees e INNER JOIN jobs j USING (job_id);
-- 12)
SELECT b.employee_id,b.start_date,b.end_date
FROM employees a INNER JOIN job_history b USING (job_id) WHERE salary>10000;
--13)
SELECT department_name,CONCAT(e.first_name," ",e.last_name) AS name, e.hire_date,e.salary
FROM employees e INNER JOIN departments d ON e.employee_id = d.manager_id
INNER JOIN job_history jh
USING(employee_id) WHERE YEAR(jh.end_date-jh.start_date)>15; |
-- 1
Select department_id,location_id,street_address,state_province,city,country_name from departments inner join locations using(location_id)
inner join countries using(COUNTRY_ID);
-- 2
SELECT concat(first_name," ",last_name) as fullName,department_id,department_name from employees inner join departments USING(DEPARTMENT_ID);
-- 3
SELECT concat(first_name," ",last_name) as fullName,job_title,department_name from employees inner join jobs using(job_id) inner join departments using(DEPARTMENT_ID) where LOCATION_ID in (Select location_id from locations where city='London');
-- 4
select e1.employee_id,e1.last_name,e1.manager_id,e2.last_name
from employees e1 inner join employees e2
on e1.manager_id = e2.employee_id;
-- 5
SELECT concat(first_name," ",last_name) as fullName,hire_date from employees where hire_date > (Select hire_date from employees where LAST_NAME='Jones');
-- 6
Select department_name,count(*) noOfEmp from employees inner join departments using(DEPARTMENT_ID) GROUP BY DEPARTMENT_ID;
-- 7
Select employee_id,job_title,(end_date-start_date) dateDiffs from job_history inner join jobs using (job_id) where department_id=90;
-- 8
SELECT e1.department_id,CONCAT(e1.first_name, " ",e1.last_name) as name, e2.first_name
from employees e1 inner join
employees e2 on e1.manager_id=e2.employee_id;
-- 9
SELECT department_name,CONCAT(e.first_name, " ",e.last_name) as name,city from employees e inner join departments USING(DEPARTMENT_ID) inner join locations using(LOCATION_ID)
where departments.MANAGER_ID=e.EMPLOYEE_ID;
-- 10
Select job_title,avg(salary) from employees inner join jobs using(job_id) GROUP BY JOB_ID;
-- 11
Select job_title,CONCAT(first_name, " ",last_name) as name,(salary-min_salary) as Diff from employees inner join jobs using(job_id) ;
-- 12
Select jh.employee_id,jh.start_date,jh.end_date,jh.job_id,jh.department_id from job_history jh inner join employees using(job_id) where salary>10000;
-- 13
SELECT department_name,concat(first_name," ",last_name) name, hire_date, salary from employees inner join departments d ON employee_id = d.manager_id inner join job_history jh using(employee_id) where year(jh.end_date-jh.start_date)>15;
|
1. SELECT location_id, street_address, city, state_province, country_name AS addresses FROM locations INNER JOIN countries USING(COUNTRY_ID);
2. SELECT CONCAT(first_name, ' ', last_name), department_id, department_name FROM employees INNER JOIN departments USING(DEPARTMENT_ID);
3. SELECT CONCAT(first_name, ' ', last_name) as name, JOB_title, department_id FROM employees
INNER JOIN jobs USING (job_id)
INNER JOIN departments USING(DEPARTMENT_ID)
INNER JOIN locations USING (LOCATION_ID)
WHERE CITY = "London";
4. SELECT e.employee_id, e.last_name as name, m.manager_id, m.LAST_NAME as name
FROM employees e JOIN employees m on (e.EMPLOYEE_ID = m.MANAGER_ID);
5. SELECT CONCAT(e1.first_name, ' ', e1.last_name) as name, e1.hire_date FROM employees e1
INNER JOIN employees e2 on e2.LAST_NAME = "jones" WHERE e1.HIRE_DATE > e2.HIRE_DATE;
-- WHERE HIRE_DATE > (SELECT HIRE_DATE FROM employees WHERE LAST_NAME = "jones")
6. SELECT COUNT(employee_id), department_name FROM employees INNER JOIN departments USING(DEPARTMENT_ID) GROUP BY DEPARTMENT_NAME;
7. SELECT employee_id, job_title, (END_DATE - START_DATE) as days_worked FROM jobs
INNER JOIN job_history USING (JOB_ID) WHERE job_history.DEPARTMENT_ID = 90;
8. SELECT e1.DEPARTMENT_ID, CONCAT(e1.first_name, ' ', e1.last_name) FROM employees e1
INNER JOIN employees e2 ON (e1.MANAGER_ID = e2.EMPLOYEE_ID);
9. SELECT DEPARTMENT_NAME, CONCAT(first_name, ' ', last_name) as name, city FROM departments d
INNER JOIN employees e USING(DEPARTMENT_ID)
INNER JOIN locations USING(LOCATION_ID)
WHERE d.MANAGER_ID = e.EMPLOYEE_ID;
10. SELECT AVG(salary), job_title FROM employees INNER JOIN jobs USING(JOB_ID) GROUP BY JOB_TITLE;
11. SELECT job_title, CONCAT(first_name, ' ', last_name) as name, (salary - MIN_SALARY) AS salary_diffrence FROM employees
INNER JOIN jobs USING(JOB_ID);
12. SELECT job_history.* FROM job_history INNER JOIN employees WHERE SALARY>10000;
13. SELECT department_name, CONCAT(first_name, ' ', last_name) as name, hire_date, salary
FROM employees INNER JOIN departments ON departments.MANAGER_ID = employees.EMPLOYEE_ID
INNER JOIN job_history USING (EMPLOYEE_ID) WHERE YEAR(END_DATE - START_DATE) > 15; |
--1
SELECT LOCATION_ID, STREET_ADDRESS, CITY, STATE_PROVINCE, COUNTRY_NAME FROM locations INNER JOIN countries USING(COUNTRY_ID);
--2
SELECT CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, DEPARTMENT_ID, DEPARTMENT_NAME FROM employees INNER JOIN departments USING (DEPARTMENT_ID);
--3
SELECT CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, JOB_TITLE, DEPARTMENT_ID FROM employees INNER JOIN jobs USING(JOB_ID) INNER JOIN departments USING(DEPARTMENT_ID) INNER JOIN locations USING (LOCATION_ID) WHERE locations.city = "London";
--4
SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.MANAGER_ID, e2.LAST_NAME FROM employees e INNER JOIN employees e2
ON e.MANAGER_ID = e2.EMPLOYEE_ID;
--5
SELECT CONCAT(e1.FIRST_NAME, " ", e1.LAST_NAME) AS NAME, e1.HIRE_DATE FROM employees e1 INNER JOIN employees e2
ON e2.LAST_NAME = 'Jones' WHERE e1.HIRE_DATE > e2.HIRE_DATE;
--6
SELECT DEPARTMENT_NAME, COUNT(EMPLOYEE_ID) FROM employees INNER JOIN departments USING (DEPARTMENT_ID)
GROUP BY DEPARTMENT_NAME;
--7
SELECT EMPLOYEE_ID, JOB_TITLE, (END_DATE - START_DATE) AS NUMBER_OF_DAYS FROM jobs INNER JOIN job_history USING (JOB_ID) WHERE DEPARTMENT_ID = 90;
--8
SELECT departments.DEPARTMENT_ID, DEPARTMENT_NAME, FIRST_NAME FROM departments INNER JOIN employees USING(DEPARTMENT_ID) WHERE departments.MANAGER_ID = employees.EMPLOYEE_ID;
--9
SELECT DEPARTMENT_NAME, CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, CITY FROM departments INNER JOIN employees USING (DEPARTMENT_ID) INNER JOIN locations USING (LOCATION_ID) WHERE departments.MANAGER_ID = employees.EMPLOYEE_ID;
--10
SELECT JOB_TITLE, AVG(SALARY) FROM employees INNER JOIN JOBS USING(JOB_ID) GROUP BY JOB_TITLE;
--11
SELECT JOB_TITLE, CONCAT(FIRST_NAME," ",LAST_NAME) AS NAME, (SALARY - MIN_SALARY) AS SALARY_DIFFERENCE
FROM employees INNER JOIN jobs USING (JOB_ID);
--12
SELECT job_history.* FROM job_history INNER JOIN employees USING (EMPLOYEE_ID) WHERE SALARY > 10000;
--13
SELECT DEPARTMENT_NAME, CONCAT(FIRST_NAME," ", LAST_NAME) AS NAME, HIRE_DATE, SALARY
FROM employees INNER JOIN departments USING (DEPARTMENT_ID) INNER JOIN job_history USING (EMPLOYEE_ID)
WHERE END_DATE-START_DATE > 15; |
--1
select location_id, street_address, city, state_province,country_name from locations inner join countries using(country_id);
--2
select concat(first_name,' ',last_name) as name,department_id,department_name from employees inner join departments using(department_id);
--3
select concat(first_name,' ',last_name) as name,department_id,job_title from employees inner join jobs using(job_id)
inner join departments using(department_id) where location_id = (SELECT location_id FROM locations WHERE city="London");
--4
select e1.employee_id,e1.last_name,e1.manager_id,e2.last_name from employees e1 inner join employees e2 on e1.manager_id = e2.employee_id;
--5
select concat(e1.first_name,' ',e1.last_name) as name,e1.hire_date from employees e1 inner join employees as e2 on e1.hire_date > e2.hire_date and e2.last_name = 'Jones';
--6
select d.department_name,count(e.employee_id) from departments d left join employees e using(department_id) group by department_id;
--7
select employee_id,job_title,day(end_date)-day(start_date) as diff from jobs inner join job_history using (job_id) where department_id =90;
--8
select d.department_id,d.department_name,e.first_name from departments d inner join employees e on d.manager_id = e.employee_id;
--9
select d.department_name,concat(e.first_name,' ',e.last_name),l.city from departments d inner join employees e on d.manager_id = e.employee_id inner join locations l using(location_id);
--10
select job_title,avg(salary) from jobs inner join employees using(job_id) group by job_title;
--11
select job_title,concat(first_name, " ", last_name) as name,
salary-min_salary as salary_diff from employees inner join jobs
using(job_id);
--12
select job_history.* from job_history INNER JOIN employees
using(employee_id) where salary>10000;
--13
select department_name,CONCAT(first_name, " ",last_name) as name,hire_date, salary from employees inner join departments d using(DEPARTMENT_ID) inner join job_history using(EMPLOYEE_ID) where employee_id=d.manager_id and
end_date-start_date>15;
|
-- DISCUSSION 8
-- #1 Write a query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments.
select LOCATION_ID,STREET_ADDRESS,CITY,STATE_PROVINCE, COUNTRY_NAME
FROM locations l inner JOIN countries c
on l.COUNTRY_ID=c.COUNTRY_ID
-- #2 Write a query to find the name (first_name, last name), department ID, and department name of all the employees.
select concat(first_name, " ", last_name),e.DEPARTMENT_ID, DEPARTMENT_NAME
FROM employees e inner JOIN departments d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID
-- #3 Write a query to find the name (first_name, last_name), job, department ID, and name of the employees who work in London.
select concat(e.first_name," ", e.last_name) as `Name`,
e.job_id,e.department_id,d.department_name, l.city
from employees e inner join departments d inner join locations l
where e.department_id=d.department_id and d.location_id = l.location_id
and l.city='London';
--#4 Write a query to find the employee id, name (last_name) along with their manager_id, and name (last_name).
select concat(e1.first_name, " ", e1.last_name) Name,e2.EMPLOYEE_ID Manager_ID, e2.FIRST_NAME Manager
FROM employees e1 join employees e2
on e1.MANAGER_ID=e2.EMPLOYEE_ID
-- #5 Write a query to find the name (first_name, last_name) and hire date of the employees who were hired after 'Jones'.
select concat(e1.first_name, " ", e1.last_name) Name,e1.HIRE_DATE HIRE_DATE
FROM employees e1
where e1.HIRE_DATE >(SELECT HIRE_DATE
from employees e2
where e2.LAST_NAME ="Jones" or e2.FIRST_NAME="Jones"
)
-- #6 Write a query to get the department name and number of employees in the department.
select d.DEPARTMENT_NAME, count(e.EMPLOYEE_ID)
FROM departments d,employees e
WHERE d.DEPARTMENT_ID=e.DEPARTMENT_ID
GROUP BY e.DEPARTMENT_ID
-- #7 Write a query to find the employee ID, job title, number of days between the ending date and the starting date for all jobs in department 90.
SELECT employee_id, job_title, end_date-start_date Days
FROM job_history
NATURAL JOIN jobs
WHERE department_id=90;
-- #8 Write a query to display the department ID and name and first name of the manager.
SELECT DEPARTMENT_ID, DEPARTMENT_NAME, (SELECT FIRST_NAME FROM employees E WHERE D.MANAGER_ID=E.EMPLOYEE_ID ) MANAGER
FROM departments d
-- #9 Write a query to display the department name, manager name, and city.
SELECT DEPARTMENT_ID, DEPARTMENT_NAME, (SELECT FIRST_NAME FROM employees E WHERE D.MANAGER_ID=E.EMPLOYEE_ID ) MANAGER, l.city
FROM departments d, locations l
where d.LOCATION_ID=l.LOCATION_ID
-- #10 Write a query to display the job title and average salary of employees.
select j.JOB_TITLE,AVG(e.salary)
from employees e,jobs j
where e.JOB_ID=j.JOB_ID
GROUP by e.JOB_ID
-- #11 Write a query to display job title, employee name, and the difference between the salary of the employee and minimum salary for the job.
SELECT j.JOB_TITLE,e.first_NAME, e.SALARY - MIN(e.SALARY) DIFFERENCE
from employees e, jobs j
WHERE e.JOB_ID=j.JOB_ID
GROUP by e.JOB_ID
--#12 Write a query to display the job history of any employee who is currently drawing more than 10000 of salary.
SELECT *
from employees e, job_history h
WHERE e.EMPLOYEE_ID=h.EMPLOYEE_ID and e.SALARY > 10000
-- #13 Write a query to display department name, name (first_name, last_name), hire date, the salary of the manager for all managers whose experience is more than 15 years.
SELECT d.DEPARTMENT_NAME, concat(e1.first_name, " ", e1.last_name),e1.HIRE_DATE,e1.SALARY,j.JOB_TITLE,((DATEDIFF(now(),hire_date))/365) Experience
from departments d, employees e1, jobs j
where d.DEPARTMENT_ID=e1.DEPARTMENT_ID and j.JOB_TITLE like "%Manager%" and (DATEDIFF(now(), hire_date))/365>15;
|
mysql> SELECT DEPARTMENT_NAME,LOCATION_ID,STREET_ADDRESS,CITY,STATE_PROVINCE, COUNTRY_NAME FROM LOCATIONS INNER JOIN COUNTRIES USING(COUNTRY_ID) INNER JOIN DEPARTMENTS USING(LOCATION_ID) GROUP BY DEPARTMENT_ID;
mysql> SELECT CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, DEPARTMENT_ID, DEPARTMENT_NAME FROM employees INNER JOIN departments USING (DEPARTMENT_ID);
mysql> SELECT CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, JOB_TITLE, DEPARTMENT_ID FROM employees INNER JOIN jobs USING(JOB_ID) INNER JOIN departments USING(DEPARTMENT_ID) INNER JOIN locations USING (LOCATION_ID) WHERE locations.city = "London";
mysql> SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.MANAGER_ID, e2.LAST_NAME FROM employees e INNER JOIN employees e2 ON e.MANAGER_ID = e2.EMPLOYEE_ID;
mysql> SELECT CONCAT(e1.FIRST_NAME, " ", e1.LAST_NAME) AS NAME, e1.HIRE_DATE FROM employees e1 INNER JOIN employees e2 ON e2.LAST_NAME = 'Jones' WHERE e1.HIRE_DATE > e2.HIRE_DATE;
mysql> SELECT DEPARTMENT_NAME, COUNT(EMPLOYEE_ID) FROM employees INNER JOIN departments USING (DEPARTMENT_ID) GROUP BY DEPARTMENT_NAME;
mysql> SELECT EMPLOYEE_ID, JOB_TITLE, (END_DATE - START_DATE) AS NUMBER_OF_DAYS FROM jobs INNER JOIN job_history USING (JOB_ID) WHERE DEPARTMENT_ID = 90;
mysql> SELECT departments.DEPARTMENT_ID, DEPARTMENT_NAME, FIRST_NAME FROM departments INNER JOIN employees USING(DEPARTMENT_ID) WHERE departments.MANAGER_ID = employees.EMPLOYEE_ID;
mysql> SELECT DEPARTMENT_NAME, CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, CITY FROM departments INNER JOIN employees USING (DEPARTMENT_ID) INNER JOIN locations USING (LOCATION_ID) WHERE departments.MANAGER_ID = employees.EMPLOYEE_ID;
mysql> SELECT job_title, AVG(salary) as Avg_salary FROM employees inner join jobs using (job_id) group by job_id;
mysql> SELECT job_title,CONCAT(first_name, " ",last_name) as `Name`, (salary - min_salary) as Diff_In_Salary FROM employees inner join jobs using (job_id);
mysql> SELECT CONCAT(first_name, " ",last_name)as Name,job_history.* FROM job_history INNER JOIN employees using (job_id) WHERE salary > 10000;
mysql> SELECT department_name,CONCAT(first_name, " ",last_name) as Name, hire_date, salary FROM employees INNER JOIN departments d using (department_id) (YEAR(NOW()) - YEAR(Hire_Date)) > 15 AND employee_id=d.manager_id; |
|
--Q1
select l.location_id,
l.street_address,
l.city,
l.state_province,
c.country_name
from locations l
inner join countries c
where l.COUNTRY_ID = c.country_id;
--Q2
select concat(e.first_name, " ", e.last_name) as `Name`,
e.department_id,
d.department_name
FROM employees e
inner join departments d
where e.department_id = d.department_id;
--Q3
select concat(e.first_name, " ", e.last_name) as `Name`,
e.job_id,
e.department_id,
d.department_name,
l.city
from employees e
inner join departments d
inner join locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'London';
--Q4
select e.employee_id,
e.last_name as 'Employee Name',
e1.employee_id as `Manager Id`,
e1.last_name as 'Manager Name'
from employees e
inner join employees e1
where e.manager_id = e1.employee_id;
--Q5
select concat(e.first_name, " ", e.last_name) as `Name`,
e.hire_date
from employees e
join employees e1 on (e1.last_name = 'Jones')
where e.hire_date > e1.hire_date;
--Q6
select count(e.employee_id) as `Employee Count`,
d.department_name
from employees e
INNER JOIN departments d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
group by department_name;
--Q7
select e.employee_id,
j.job_id,
datediff(j.end_date, j.start_date) as `Days Difference`
from employees e
INNER JOIN job_history j
where e.employee_id = j.employee_id;
--Q8
select d.department_id,
d.department_name,
concat(e.first_name, " ", e.last_name) as `Manager Name`
from departments d
INNER JOIN employees e
where d.manager_id = e.employee_id;
--Q9
select d.department_name,
concat(e.first_name, " ", e.last_name) as `Manager Name`,
l.city
from departments d
INNER JOIN employees e
INNER JOIN locations l
where d.manager_id = e.employee_id
and d.LOCATION_ID = l.LOCATION_ID;
--Q10
SELECT AVG(e.SALARY),
j.job_title
from employees e,
jobs j
where e.job_id = j.job_id
GROUP BY j.job_title;
--Q11
select j.job_title,
concat(e.first_name, " ", e.last_name) as `Employee Name`,
e.salary - j.min_salary as `Difference`
from employees e,
jobs j
where e.job_id = j.job_id;
--Q12
select j.*
from job_history j,
employees e
where j.EMPLOYEE_ID = e.employee_id
AND e.salary > 10000;
--Q13
select d.department_name,
concat(e.first_name, " ", e.last_name) as `Name`,
e.hire_date,
e.salary
from departments d,
employees e
where d.manager_id = e.employee_id
and (datediff(now(), e.hire_date) / 365) > 15; |
-- 1. Write a query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments.
select location_id,street_address,city,state_province,country_name from locations l INNER JOIN countries c using (COUNTRY_ID);
--2. Write a query to find the name (first_name, last name), department ID, and department name of all the employees.
select CONCAT(first_name," ",last_name)as name ,department_id, department_name from employees e INNER JOIN departments using(DEPARTMENT_ID);
--3. Write a query to find the name (first_name, last_name), job, department ID, and name of the employees who work in London.
select CONCAT(first_name," ",last_name)as name, job_id, department_id, department_name from employees INNER JOIN jobs using(job_id) INNER JOIN departments d USING (DEPARTMENT_ID) INNER JOIN locations using(location_id) where CITY LIKE 'London';
-- 4. Write a query to find the employee id, name (last_name) along with their manager_id, and name (last_name).
select e1.employee_id,CONCAT(e1.first_name," "," ",e2.last_name) as name, e2.manager_id, e2.last_name as manager_name from employees e1 JOIN employees e2 on e1.EMPLOYEE_ID=e2.MANAGER_ID;
--5. Write a query to find the name (first_name, last_name) and hire date of the employees who were hired after 'Jones'.
SELECT CONCAT(first_name," ",last_name)as name,hire_date from employees where hire_date >(select HIRE_DATE from employees where last_name LIKE 'Jones');
--6. Write a query to get the department name and number of employees in the department.
select department_name, COUNT(*) as no_of_employees from departments d INNER JOIN employees e on(e.DEPARTMENT_ID=d.DEPARTMENT_ID)GROUP BY d.DEPARTMENT_NAME,d.DEPARTMENT_ID;
--7. Write a query to find the employee ID, job title, number of days between the ending date and the starting date for all jobs in department 90.
SELECT employee_id, j.job_id,(end_date-start_date) as days from job_history Natural JOIN jobs j where DEPARTMENT_ID=90;
--8. Write a query to display the department ID and name and first name of the manager.
select d.department_id, d.department_name, m.first_name as manger_name from departments d INNER Join employees m using(manager_id);
--9. Write a query to display the department name, manager name, and city.
select department_name,city,first_name as manger from employees e INNER JOIN departments m using(DEPARTMENT_ID) INNER JOIN locations using(LOCATION_ID) GROUP BY DEPARTMENT_NAME;
--10. Write a query to display the job title and average salary of employees.
select job_id, AVG(salary) as avg_salary from jobs INNER JOIN employees using(job_id) GROUP BY JOB_TITLE;
--11. Write a query to display job title, employee name, and the difference between the salary of the employee and minimum salary for the job.
select job_id, first_name,salary-min_salary as difference from jobs INNER JOIN employees using(JOB_ID);
--12. Write a query to display the job history of any employee who is currently drawing more than 10000 of salary.
select EMPLOYEE_ID,START_DATE,END_DATE,j.JOB_ID from job_history j LEFT JOIN employees using(EMPLOYEE_ID)where salary>10000;
--13. Write a query to display department name, name (first_name, last_name), hire date, the salary of the manager for all managers whose experience is more than 15 years.
select department_name,CONCAT(first_name," ",last_name) as name,hire_date,salary from employees e JOIN departments d using(DEPARTMENT_ID) where e.EMPLOYEE_ID=d.MANAGER_ID AND datediff(now(), HIRE_DATE)>365*15; |
1.select location_id, street_address, city, state_province, c.country_name from locations inner join countries c using(COUNTRY_ID);
2.select CONCAT(FIRST_NAME," ",LAST_NAME) as "First Name" ,d.department_id,d.department_name from employees inner join departments d using(department_id);
3.select CONCAT(e.FIRST_NAME," ",e.LAST_NAME) as "First Name" ,e.job_id,e.department_id,d.department_name from employees e inner join departments d using(department_id) inner join jobs j using(job_id)
where location_id in(select location_id from locations where city ="London");
4.select e1.employee_id as employee_id, e1.last_name as employee_lastname,e2.employee_id as manager_id,e2.last_name as manager_lastname from employees e1 inner join employees e2 on(e1.manager_id=e2.employee_id);
5.select CONCAT(FIRST_NAME," ",LAST_NAME) as "First Name",hire_date from employees where hire_date >
(select hire_date from employees where last_name="Jones");
6.select count(*) as No_Of_Emp, department_name from employees inner join departments using(department_id) group by department_id;
7.select employee_id,job_title, (end_date)-(start_date) as no_of_days from job_history inner join jobs using(job_id) where department_id=90;
select d.department_id , d.department_name,d.manager_id,e.first_name as manager_name from departments d inner join employees e on(d.manager_id=e.employee_id);
9.select department_name,e.first_name as manager_name,city from employees e inner join departments using(department_id) inner join locations using(location_id) GROUP BY department_name;
10.select job_title, AVG(salary) from jobs inner join employees using(job_id) GROUP BY job_id;
11.select job_title,CONCAT(FIRST_NAME," ",LAST_NAME) as "Name" ,salary-min_salary as diff from employees inner join jobs using(job_id);
12.select j.* from job_history j inner join employees using(employee_id) where salary > 10000;
13.SELECT department_name, first_name, last_name, hire_date, salary
FROM departments d JOIN employees e
ON (d.manager_id = e.employee_id)
WHERE (DATEDIFF(now(), hire_date))/365>15; |
--1. Write a query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments.
select location_id, street_address, city, state_province, country_name
from locations inner join countries
using (country_id) inner join departments
using (location_id) group by department_id;
--2.Write a query to find the name (first_name, last name), department ID, and department name of all the employees.
select concat(first_name," ",last_name) as name,department_id, department_name
from employees inner join departments using (DEPARTMENT_ID);
--3.Write a query to find the name (first_name, last_name), job, department ID, and name of the employees who work in London.
select concat(first_name," ",last_name) as name, job_title, department_id
from employees
inner join jobs using (job_id)
inner join departments using (department_id)
inner join locations using (location_id)
where locations.city = 'London';
--4.Write a query to find the employee id, name (last_name) along with their manager_id, and name (last_name).
select e1.employee_id, e1.last_name, e1.manager_id, e2.last_name
from employees e1 inner join employees e2
on e1.manager_id = e2.employee_id;
--5.Write a query to find the name (first_name, last_name) and hire date of the employees who were hired after 'Jones'.
select concat(e1.first_name," ",e1.last_name) as name, e1.hire_date
from employees e1 inner join employees e2
on e2.last_name = 'Jones' where e1.hire_date > e2.hire_date;
--6.Write a query to get the department name and number of employees in the department.
select department_name, count(employee_id)
from departments join employees using (department_id)
group by department_name;
--7.Write a query to find the employee ID, job title, number of days between the ending date and the starting date for all jobs in department 90.
select employee_id, job_title, (end_date - start_date) as numOfDays
from jobs inner join job_history using (job_id)
where DEPARTMENT_ID=90;
--8.Write a query to display the department ID and name and first name of the manager.
select d.department_id, department_name, first_name
from departments d inner join employees
using (department_id)
where d.manager_id = employees.employee_id;
--9.Write a query to display the department name, manager name, and city.
select d.department_name, concat(first_name," ",last_name) as name, city
from departments d inner join locations using (location_id)
inner join employees using (department_id)
where d.manager_id = employees.employee_id;
--10.Write a query to display the job title and average salary of employees.
select job_title, avg(salary) as AvgSal
from jobs inner join employees using (job_id)
group by job_title;
--11.Write a query to display job title, employee name, and the difference between the salary of the employee and minimum salary for the job.
select job_title, concat(first_name," ",last_name) as name,
(salary - min_salary) as SalDiff
from employees inner join jobs using (job_id);
--12.Write a query to display the job history of any employee who is currently drawing more than 10000 of salary.
select * from job_history inner join employees
using (employee_id) where employees.salary > 10000;
--13.Write a query to display department name, name (first_name, last_name), hire date, the salary of the manager for all managers whose experience is more than 15 years.
select d.department_name, concat(e.first_name, " ", e.last_name) as name,
e.hire_date, e.salary
from departments d, employees e
where d.manager_id = e.employee_id and (datediff(now(), e.hire_date) / 365) > 15; |
Select location_id, street_address, city, state_province, department_id, country_name from departments inner join locations using (location_id) inner join countries using(country_id);
Select CONCAT(first_name," ", last_name) as names, department_id, department_name from employees inner join departments using (department_id);
Select CONCAT(first_name," ", last_name) as names, job_title, department_id from employees inner join jobs using(job_id) inner join departments using (department_id) inner join locations using (location_id) where city="London";
Select e.employee_id, e.last_name, e.manager_id, m.last_name from employees e inner join employees m on (e.manager_id= m.employee_id);
Select CONCAT(e.first_name," ", e.last_name) as names, e.hire_date from employees e inner join employees e2 on(e2.last_name='jones') where e.hire_date> e2.hire_date;
select department_name, count(*) as total_employees from employees inner join departments using (department_id) group by department_id;
Select employee_id, job_title, (end_date-start_date) as days from job_history inner join jobs using(job_id) where department_id=90;
select d.department_id, d.department_name, e.first_name from departments d inner join employees e on(d.manager_id= e.employee_id);
Select department_name, first_name as manager, city from employees inner join departments d using(department_id) inner join locations using(location_id) where employee_id = d.manager_id;
Select job_title, AVG(salary) from employees inner join jobs using(job_id) group by job_title;
select job_title,CONCAT(first_name, " ", last_name) as emp_name,
salary-min_salary as sal_diff from employees inner join jobs
using(job_id);
select job_history.* from job_history INNER JOIN employees
using(employee_id) where salary>10000;
select department_name,CONCAT(first_name, " ",last_name) as name,hire_date,
salary from employees inner join departments d using(DEPARTMENT_ID)
inner join job_history using(EMPLOYEE_ID) where employee_id=d.manager_id and
end_date-start_date>15; |
|
-- 1. Write a query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments. select d.DEPARTMENT_ID,d.DEPARTMENT_NAME,d.LOCATION_ID,l.STREET_ADDRESS,l.CITY,l.STATE_PROVINCE,l.COUNTRY_ID AS COUNTRY_NAME -- 2. Write a query to find the name (first_name, last name), department ID, and department name of all the employees. SELECT e.FIRST_NAME,e.LAST_NAME,d.DEPARTMENT_ID,D.DEPARTMENT_NAME -- 3. Write a query to find the name (first_name, last_name), job, department ID, and name of the employees who work in London. SELECT e.FIRST_NAME,e.LAST_NAME,e.JOB_ID,j.JOB_TITLE,D.DEPARTMENT_NAME -- 4. Write a query to find the employee id, name (last_name) along with their manager_id, and name (last_name). SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.MANAGER_ID,e2.LAST_NAME as manager_name -- 5. Write a query to find the name (first_name, last_name) and hire date of the employees who were hired after 'Jones'. select concat(e.FIRST_NAME," ",e.LAST_NAME) as name,e.HIRE_DATE -- 6. Write a query to get the department name and number of employees in the department. SELECT d.DEPARTMENT_NAME,count(*) -- 7. Write a query to find the employee ID, job title, number of days between the ending date and the starting date for all jobs in department 90. SELECT e.EMPLOYEE_ID,j.JOB_TITLE,jh.END_DATE,jh.START_DATE,(jh.END_DATE-jh.START_DATE) as NO_OF_DAYS -- 8. Write a query to display the department ID and name and first name of the manager. SELECT e.DEPARTMENT_ID,concat(e.FIRST_NAME," " ,e.LAST_NAME) as full_name ,e2.FIRST_NAME manager_first_name,e2.LAST_NAME as manager_last_name -- 9. Write a query to display the department name, manager name, and city. SELECT d.DEPARTMENT_NAME,concat(e.FIRST_NAME," " ,e.LAST_NAME) as full_name ,concat(e2.FIRST_NAME," ",e2.LAST_NAME) as manager_name,l.CITY -- 10. Write a query to display the job title and average salary of employees. select j.JOB_TITLE,format(avg(e.SALARY),0) -- 11. Write a query to display job title, employee name, and the difference between the salary of the employee and minimum salary for the job. select j.JOB_TITLE,concat(e.FIRST_NAME," " ,e.LAST_NAME) as full_name,(e.SALARY- j.MIN_SALARY) as salary_diff -- 12. Write a query to display the job history of any employee who is currently drawing more than 10000 of salary. select jh.EMPLOYEE_ID,jh.START_DATE,jh.END_DATE,jh.JOB_ID,jh.DEPARTMENT_ID -- 13. Write a query to display department name, name (first_name, last_name), hire date, the salary of the manager for all managers whose experience is more than 15 years. SELECT d.DEPARTMENT_NAME,concat(e.FIRST_NAME," " ,e.LAST_NAME) as full_name ,e.HIRE_DATE,concat(e2.FIRST_NAME," ",e2.LAST_NAME) as manager_name,e2.SALARY |
|
use hr; --1 --2 --3 --4 --5 --6 --7 --8 --9 --10 --11 --12 --13 |
SELECT d.department_name,l.location_id, l.street_address, l.city,
SELECT e.first_name,e.last_name,d.department_id,d.department_name FROM
SELECT CONCAT(e.first_name,e.last_name) AS name
SELECT e.employee_id,e.last_name,e.manager_id,e2.last_name
SELECT CONCAT(e.first_name,e.last_name) AS name,e.hire_date
SELECT d.department_name,COUNT(e.department_id) AS employee_count FROM
SELECT jh.employee_id,j.job_title,DATEDIFF(jh.end_date,jh.start_date) as
SELECT d.department_id,d.department_name,e.first_name FROM departments d
SELECT d.department_name,CONCAT(e.first_name,' ',e.last_name) AS
SELECT j.job_title,ROUND(AVG(e.salary),2)AS average FROM jobs j
select j.job_title, concat(e.first_name,'', e.last_name) as name,
SELECT jh.* FROM job_history jh
select d.department_name, concat(e.first_name,' ',e.last_name) as name, |
-- 1. Write a query to find the addresses (location_id, street_address, city, state_province,
-- country_name) of all the departments.
select location_id, street_address, city, state_province, country_name
from locations
join countries using (country_id);
-- 2. Write a query to find the name (first_name, last name), department ID, and department
-- name of all the employees.
select concat(first_name, ' ', last_name) emoloyee_name, department_id, department_name
from employees join departments using (department_id);
-- 3. Write a query to find the name (first_name, last_name), job, department ID, and name of
-- the employees who work in London.
select concat(first_name, ' ', last_name) employee_name, job_title, e.department_id
from employees e
join jobs using (job_id)
join departments using (department_id)
join locations using (location_id)
where city = 'London';
-- 4. Write a query to find the employee id, name (last_name) along with their manager_id,
-- and name (last_name).
select e.employee_id, e.last_name, e.manager_id, m.last_name
from employees e
join employees m on e.MANAGER_ID = m.EMPLOYEE_ID;
-- 5. Write a query to find the name (first_name, last_name) and hire date of the employees
-- who were hired after 'Jones'.
select concat(first_name, last_name), hire_date
from employees e
where HIRE_DATE > (
select HIRE_DATE
from employees e2
where LAST_NAME ='Jones'
);
-- 6. Write a query to get the department name and number of employees in the department.
select department_name, count(employee_id)
from employees e
join departments d using (department_id)
group by DEPARTMENT_name;
-- 7. Write a query to find the employee ID, job title, number of days between the ending date
-- and the starting date for all jobs in department 90.
select employee_id, job_title, (end_date - start_date) tenure
from job_history
join jobs using (job_id)
where department_id = 90;
-- 8. Write a query to display the department ID and name and first name of the manager.
select d.department_id, department_name, first_name
from departments d
join employees e on d.MANAGER_ID = e.EMPLOYEE_ID ;
-- 9. Write a query to display the department name, manager name, and city.
select department_name, concat(first_name, ' ', last_name) manager_name, city
from departments d
join employees e on d.MANAGER_ID = e.EMPLOYEE_ID
join locations l using (location_id);
-- 10. Write a query to display the job title and average salary of employees.
select job_title, avg(salary) as avg_salary
from employees e
join jobs j using (job_id)
group by job_title;
-- 11. Write a query to display job title, employee name, and the difference between the
-- salary of the employee and minimum salary for the job.
select job_title, concat(first_name, ' ', last_name) employee_name,
(salary - (select min(salary) from employees)) difference
from employees e
join jobs using (job_id);
-- 12. Write a query to display the job history of any employee who is currently drawing more
-- than 10000 of salary.
select *
from job_history jh
join employees e using (job_id)
where salary > 10000;
-- 13. Write a query to display department name, name (first_name, last_name), hire date, the
-- salary of the manager for all managers whose experience is more than 15 years.
select department_name, concat(first_name, ' ', last_name) name, hire_date, salary
from departments d
join employees e on d.MANAGER_ID = e.EMPLOYEE_ID
where hire_date<DATE_SUB(NOW(), INTERVAL 15 YEAR);
|
|
#1 Write a query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments. #2 Write a query to find the name (first_name, last name), department ID, and department name of all the employees. #3 Write a query to find the name (first_name, last_name), job, department ID, and name of the employees who work in London. #4 Write a query to find the employee id, name (last_name) along with their manager_id, and name (last_name). #5 Write a query to find the name (first_name, last_name) and hire date of the employees who were hired after 'Jones'. #6 Write a query to get the department name and number of employees in the department. #7 Write a query to find the employee ID, job title, number of days between the ending date and the starting date for all jobs in department 90. select JOB_ID, day(end_date)-day(start_date) as diff #8 Write a query to display the department ID and name and first name of the manager. #9 Write a query to display the department name, manager name, and city. #10 Write a query to display the job title and average salary of employees. #11 Write a query to display job title, employee name, and the difference between the salary of the employee and minimum salary for the job.SELECT JOB_TITLE, FIRST_NAME,SALARY, SALARY - MIN_SALARY #12 Write a query to display the job history of any employee who is currently drawing more than 10000 of salary. #13 Write a query to display department name, name (first_name, last_name), hire date, the salary of the manager for all managers whose experience is more than 15 years.SELECT DEPARTMENT_NAME, CONCAT(FIRST_NAME, ' ', LAST_NAME), HIRE_DATE, SALARY |
|
#1. Write a query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments. SELECT d.department_name,l.location_id, l.street_address, l.city, l.state_province, c.country_name FROM locations l #2.Write a query to find the name (first_name, last name), department ID, and department name of all the employees. #Write a query to find the name (first_name, last_name), job, department ID, and name of the employees who work in London. #4.Write a query to find the employee id, name (last_name) along with their manager_id, and name (last_name). #5.Write a query to find the name (first_name, last_name) and hire date of the employees who were hired after 'Jones'. #6.Write a query to get the department name and number of employees in the department. #7.Write a query to find the employee ID, job title, number of days between the ending date and the starting date for all jobs in department 90. #8.Write a query to display the department ID and name and first name of the manager. #9.Write a query to display the department name, manager name, and city. #10 Write a query to display the job title and average salary of employees. #11.Write a query to display job title, employee name, and the difference between the salary of the employee and minimum salary for the job. #12 Write a query to display the job history of any employee who is currently drawing more than 10000 of salary. #13. Write a query to display department name, name (first_name, last_name), hire date, the salary of the manager for select d.department_name, concat(e.first_name,' ',e.last_name) as name, e.hire_date, e.salary |
|
#1q #2q #3 #4 #5 #6q #7q #8q #9q #11q #12 #13 |
and others
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
Database =
hrDump file: hr.sql
DB Schema
Queries
Write a query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments.
Write a query to find the name (first_name, last name), department ID, and department name of all the employees.
Write a query to find the name (first_name, last_name), job, department ID, and name of the employees who work in London.
Write a query to find the employee id, name (last_name) along with their manager_id, and name (last_name).
Write a query to find the name (first_name, last_name) and hire date of the employees who were hired after 'Jones'.
Write a query to get the department name and number of employees in the department.
Write a query to find the employee ID, job title, number of days between the ending date and the starting date for all jobs in department 90.
Write a query to display the department ID and name and first name of the manager.
Write a query to display the department name, manager name, and city.
Write a query to display the job title and average salary of employees.
Write a query to display job title, employee name, and the difference between the salary of the employee and minimum salary for the job.
Write a query to display the job history of any employee who is currently drawing more than 10000 of salary.
Write a query to display department name, name (first_name, last_name), hire date, the salary of the manager for all managers whose experience is more than 15 years.
All reactions