SQL Queries

use classicmodels;

select * from offices;

select * from employees;

select e.employeeNumber as emp_no,e.jobTitle as jobTitle,e.email as email,o.phone as phone,o.country as country

from employees as e

inner join offices as o

using(officeCode);

select e.employeeNumber as emp_no,e.jobTitle as jobTitle,e.email as email,o.phone as phone,o.country as country

from employees as e

inner join offices as o

using(officeCode)

order by jobTitle;

select e.employeeNumber as emp_no,e.jobTitle as jobTitle,e.email as email,o.phone as phone,o.country as country

from employees as e

inner join offices as o

using(officeCode)

where country='USA'

order by jobTitle;

select e.employeeNumber as emp_no,e.jobTitle as jobTitle,e.email as email,o.phone as phone,o.country as country

from employees as e

inner join offices as o

using(officeCode)

where country='USA' and jobTitle='Sales Rep'

order by jobTitle;

select e.employeeNumber as emp_no,e.jobTitle as jobTitle,e.email as email,o.phone as phone,o.country as country

from employees as e

left join offices as o

using(officeCode)

where country in('USA','Australia') and jobTitle='Sales Rep'

order by jobTitle;

select e.employeeNumber as emp_no,

e.firstName as firstName,

e.jobTitle as jobTitle,

e.email as email,

o.phone as phone,o.country as country

from employees as e

left join offices as o

using(officeCode)

where country in('USA','Australia') and jobTitle='Sales Rep'

order by jobTitle;