You are given the below database: employee(employee-name, street, city) works(em

Question

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)

Give an expression in SQL for each of the following queries:

Find the names, street address, and cities of residence for all employees who work for 'First Bank Corporation' and earn more than $10,000.

Find the names of all employees in the database who live in the same cities as the companies for which they work.

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

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.

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.

Assume that the companies may be located in several cities. Find all companies located in every city in which 'Small Bank Corporation' is located.

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

Solution:

Four parts have been solved, please repost others.

Find the names, street address, and cities of residence for all employees who work for 'First Bank Corporation' and earn more than $10,000.

SELECT employee.employee_name, employee.street, employee.city
FROM employee INNER JOIN works ON employee.employee-name= works.employee-name

WHERE works.salary>10000 AND works.company-name= ‘First Bank Corporation’

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

SELECT employee.employee_name

   ,manages
   , employee AS e2
where employee.employee_name = manages.employee_name
AND manages.manager_name = e2.employee_name
AND employee.street = e2.street
AND employee.city = e2.city;

Find all employees in the database who do not work for First Bank Corporation

SELECT works.employee_name
FROM works
where company_name != 'First Bank Corporation';

Find all employees in the database who earn more than every employee of Small Bank Corporation.

SELECT works1.employee_name
FROM works AS works1
WHERE works1.salary >
           (SELECT MAX(works.salary)
           FROM works
           WHERE works.company_name = 'Small Bank Corporation'
           );
          

alternative way of solving problem:  
SELECT works1.employee_name
FROM works AS works1
WHERE works1.salary >ALL
           (SELECT works.salary
           FROM works
           WHERE works.company_name = 'Small Bank Corporation'
);

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

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