Please prove all typed currently sql query for the Tasks in the Introduction(Not

Question

Please prove all typed currently sql query for the Tasks in the Introduction(Not photo), thank you so much.
Introduction
You are given the below database
employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)
Tasks:
1. Find the names, street address, and cities of residence for all employees who work for 'First Bank Corporation' and earn more than $10,000.
2. Find the names of all employees in the database who live in the same cities as the companies for which they work.
3. Find the names of all employees in the database who live in the same cities and on the same streets as do their managers
4. Find the names of all employees in the database who do not work for 'First Bank Corporation'. Assume that all people work for exactly one company
5. Find the names of all employees in the database who earn more than every employee of 'Small Bank Corporation'. Assume that all people work for at most one company.
6. Assume that the companies may be located in several cities. Find all companies located in every city in which 'Small Bank Corporation' is located.
7. Find the names of all employees who earn more than the average salary of all employees of their company. Assume that all people work for at most one company.
Please prove all typed currently sql query for the Tasks in the Introduction(Not photo), thank you so much.
Introduction
You are given the below database
employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)
Tasks:
1. Find the names, street address, and cities of residence for all employees who work for 'First Bank Corporation' and earn more than $10,000.
2. Find the names of all employees in the database who live in the same cities as the companies for which they work.
3. Find the names of all employees in the database who live in the same cities and on the same streets as do their managers
4. Find the names of all employees in the database who do not work for 'First Bank Corporation'. Assume that all people work for exactly one company
5. Find the names of all employees in the database who earn more than every employee of 'Small Bank Corporation'. Assume that all people work for at most one company.
6. Assume that the companies may be located in several cities. Find all companies located in every city in which 'Small Bank Corporation' is located.
7. Find the names of all employees who earn more than the average salary of all employees of their company. Assume that all people work for at most one company.
Please prove all typed currently sql query for the Tasks in the Introduction(Not photo), thank you so much.
Introduction
You are given the below database
employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)
Tasks:
1. Find the names, street address, and cities of residence for all employees who work for 'First Bank Corporation' and earn more than $10,000.
2. Find the names of all employees in the database who live in the same cities as the companies for which they work.
3. Find the names of all employees in the database who live in the same cities and on the same streets as do their managers
4. Find the names of all employees in the database who do not work for 'First Bank Corporation'. Assume that all people work for exactly one company
5. Find the names of all employees in the database who earn more than every employee of 'Small Bank Corporation'. Assume that all people work for at most one company.
6. Assume that the companies may be located in several cities. Find all companies located in every city in which 'Small Bank Corporation' is located.
7. Find the names of all employees who earn more than the average salary of all employees of their company. Assume that all people work for at most one company.

Explanation / Answer

a)Answer:

select * from employee where employee_name in(select employee_name from works where company_name='First Bank Corporation' salary>10000

( or )

select employee.employee-name,
employee.city,
employee.street
from
employee,
works
where employee.employee-name=works.employee-name
and company-name = 'First Bank Corporation' and salary > 10000)

b)Answer:

select e.employee-name
from employee emp,
works wor,
company com
where emp.employee-name = wor.employee-name
and emp.city = com.city
and wor.company-name = com.company-name

c)Answer:

select e.employee-name
from employee e,
employee p,
manages m
where e.employee-name = m.employee-name and
m.manager-name = p.employee-name
and e.street = p.street and e.city = p.city

d)Answer:

select employee-name
from works
where company-name <> 'First Bank Corporation'

e)Answer:

select employee-name
from works
where salary > all (select salary
from works
where company-name = 'Small Bank Corporation')

f)Answer:

select c.company-name
from company c
where not exists
((select city from company where company-name = 'Small Bank
Corporation')
except
(select city from company m where c.company-name = m.company-name))

g)

select employee-name
from works m
where salary >(select avg(salary) from works p
where m.company-name = p.company-name)

Thanks Have a Great Day please comment if you have any doubts.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.