SQL Project Submission

Below are my questions and subsequent queries made to the Oracle example database, HR.

In [1]:
from IPython.display import Video

Video("loom_video.mp4")
Out[1]:

What departments earn the most overall?

select d.department_name, count(e.employee_id), sum(e.salary)

from employees e
join departments d
on e.department_id=d.department_id
group by d.department_name
order by sum desc;
department_name count sum
Sales 34 304500.00
Shipping 45 156400.00
Executive 3 58000.00
Finance 6 51600.00
IT 5 28800.00
Purchasing 6 24900.00
Accounting 2 20300.00
Marketing 2 19000.00
Public Relations 1 10000.00
Human Resources 1 6500.00
Administration 1 4400.00

What is the average income per city?

select l.city, count(e.employee_id), round(avg(e.salary),2)
from departments d
join locations l
on d.location_id=l.location_id
join employees e
on d.department_id=e.department_id
group by l.city;
city num of emp avg income
London 1 6500.00
Munich 1 10000.00
South San Francisco 45 3475.56
Southlake 5 5760.00
Toronto 2 9500.00
OX9 9ZB 34 8955.88
Seattle 18 8844.44

What is the average income per department?

select d.department_name, count(e.employee_id), round(avg(e.salary),2)

from departments d
join employees e
on d.department_id=e.department_id

group by d.department_name
department_name count round
Shipping 45 3475.56
Sales 34 8955.88
IT 5 5760.00
Administration 1 4400.00
Finance 6 8600.00
Purchasing 6 4150.00
Marketing 2 9500.00
Public Relations 1 10000.00
Accounting 2 10150.00
Executive 3 19333.33
Human Resources 1 6500.00

What departments and cities only have 1 person?

select l.city, d.department_name

from departments d
join locations l
on d.location_id=l.location_id
where l.city like 'Munich' or l.city like 'London';
city department_name
London Human Resources
Munich Public Relations

Weird stuff when I was trying to find data on employees who've worked for the company longest.

select 
e.last_name,
e.salary, 
e.hire_date, 
jh.start_date, 
jh.end_date, 
(jh.end_date - jh.start_date)/365 as "years employed",
d.department_name,
l.city

from employees e
join job_history jh
on e.employee_id=jh.employee_id

join departments d
on e.department_id=d.department_id

join locations l
on d.location_id=l.location_id;
last_name salary hire_date start_date end_date years employed department_name city
Kaufling 7900.00 2003-05-01 2007-01-01 2007-12-31 0 Shipping South San Francisco
Whalen 4400.00 2003-09-17 2002-07-01 2006-12-31 4 Administration Seattle
Whalen 4400.00 2003-09-17 1995-09-17 2001-06-17 5 Administration Seattle
Raphaely 11000.00 2002-12-07 2006-03-24 2007-12-31 1 Purchasing Seattle
De Haan 17000.00 2001-01-13 2001-01-13 2006-07-24 5 Executive Seattle
Kochhar 17000.00 2005-09-21 2001-10-28 2005-03-15 3 Executive Seattle
Kochhar 17000.00 2005-09-21 1997-09-21 2001-10-27 4 Executive Seattle
Hartstein 13000.00 2004-02-17 2004-02-17 2007-12-19 3 Marketing Toronto
Taylor 8600.00 2006-03-24 2007-01-01 2007-12-31 0 Sales OX9 9ZB
Taylor 8600.00 2006-03-24 2006-03-24 2006-12-31 0 Sales OX9 9ZB

more query weirdness

select 
e.last_name,
e.salary, 
e.hire_date, 
jh.start_date, 
jh.end_date, 
(jh.end_date - jh.start_date)/365 as "years employed",
d.department_name,
l.city,
c.country_name

from employees e
join job_history jh
on e.employee_id=jh.employee_id

join departments d
on e.department_id=d.department_id

join locations l
on d.location_id=l.location_id

right outer join countries c
on l.country_id=c.country_id;
last_name salary hire_date start_date end_date years employed department_name city country_name
Argentina
Australia
Belgium
Brazil
Hartstein 13000.00 2004-02-17 2004-02-17 2007-12-19 3 Marketing Toronto Canada
Switzerland
China
Germany
Denmark
Egypt
France
HongKong
Israel
India
Italy
Japan
Kuwait
Mexico
Nigeria
Netherlands
Singapore
United Kingdom
Kochhar 17000.00 2005-09-21 1997-09-21 2001-10-27 4 Executive Seattle United States of America
Kochhar 17000.00 2005-09-21 2001-10-28 2005-03-15 3 Executive Seattle United States of America
De Haan 17000.00 2001-01-13 2001-01-13 2006-07-24 5 Executive Seattle United States of America
Raphaely 11000.00 2002-12-07 2006-03-24 2007-12-31 1 Purchasing Seattle United States of America
Whalen 4400.00 2003-09-17 1995-09-17 2001-06-17 5 Administration Seattle United States of America
Whalen 4400.00 2003-09-17 2002-07-01 2006-12-31 4 Administration Seattle United States of America
Kaufling 7900.00 2003-05-01 2007-01-01 2007-12-31 0 Shipping South San Francisco United States of America
Zambia
Zimbabwe

To confirm where we have deparment locations connected to departments we know of...

select c.country_name, d.department_name


from countries c
join locations l
on c.country_id=l.country_id

join departments d
on l.location_id=d.location_id;
country_name department_name
Canada Marketing
Germany Public Relations
United Kingdom Human Resources
United States of America Payroll
United States of America Recruiting
United States of America Retail Sales
United States of America Government Sales
United States of America IT Helpdesk
United States of America NOC
United States of America IT Support
United States of America Operations
United States of America Contracting
United States of America Construction
United States of America Manufacturing
United States of America Benefits
United States of America Shareholder Services
United States of America Control And Credit
United States of America Corporate Tax
United States of America Treasury
United States of America Accounting
United States of America Finance
United States of America Executive
United States of America IT
United States of America Shipping
United States of America Purchasing
United States of America Administration
In [ ]: