employee(employee_name, street, city)
works(employee_name, company_name, salary)
company(company_name, city)
manages(employee_name, manager_name)

Find ’John Doe’s Manager’s Name.

select manager_name
from manages
where upper(employee_name) = 'JOHN DOE'


Find all employees in the database who live in the same 
cities and on the same streets as do their managers.

select 
    e.employee_name as employee, me.employee_name as manager
from employee e, manages m, employee me
where e.employee_name = m.employee_name and
   m.manager_name = me.employee_name and
   e.city = me.city and e.street = me.street


Find all employees who earn more than the average salary of all employees in
their company.



select employee_name,company_name,salary
from 
    works w,
    (select company_name,avg(salary) sal
     from works
     group by company_name) c
where
    w.company_name = c.company_name and
    w.salary > c.sal

select employee_name,company_name,salary
from 
    works w
where
    w.salary > (select avg(salary)
                from works
                where company_name = w.company_name)
    

Find the comapny that has the smallest payroll.

select company_name
from works
group by company_name
having sum(salary) <= ALL (
                       select sum(salary)
                       from works
                       group by company_name
                   )

employee(employee_name, street, city)
works(employee_name, company_name, salary)
company(company_name, city)
manages(employee_name, manager_name)

Find those companies whose employees earn a higher salary, 
on average, than the average salary at First Bank Corporation.

select company_name,avg(salary)
from works
group by company_name
having avg(salary) > (select avg(salary)
                      from works
                      where company_name = 'First Bank Corporation')







branch-name loan-number amount
Downtown L-170 3000
Redwood L-230 4000
Rerryridge L-260 1700
loan

customer-name loan-number
Jones L-170
Smith L-230
Hayes L-155
borrower

Show result of “loan inner join borrower”.

Downtown, L-170, 3000, Jones, L-170
Redwood, L-230, 4000, Smith, L-230

Show result of “loan left outer join borrower”.

Downtown, L-170, 3000,  Jones, L-170
Redwood, L-230, 4000,  Smith, L-230
Rerryridge, L-260, 1700, NULL, NULL


Show “loan full outer join borrower using (loan-number)”.

Downtown, L-170, 3000,  Jones
Redwood, L-230, 4000,  Smith
Rerryridge, L-260, 1700, NULL
NULL, NULL, NULL, Hayes,