5. (120) Consider the employee database of Fig. 2, where the primary keys are un

Question

5. (120) Consider the employee database of Fig. 2, where the primary keys are underlined. Given an SQL query for each of the following questions. a. Find the names of all employees who work for First Bank Corporation. b. Find the names and cities of residence of all employees who work for First Bank Corporation. Find the names, street addresses, and cities of residence of all employees who work for First Bank C Find all employees in the database who live in the same cities as the companies for which they work. Find all employees in the database who live in the same cities and on the same streets as do their managers. Find all employees in the database who do not work for the First Bank Corporation. Find all employees in the database who earn more than each employee of Small Bank Corporation. Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located. c. orporation and earn more than $10,000 d. e. f. g. h. i. Find all employees who earn more than the average salary of all employees of their company Find the company that has the most employees. Find the company that has the smallest payroll j. k. ind those companies whose employees earn a higher salary, on average, than the average of First Bank Corporation. employee(employee-id, employee-name, street, city,) works(emplovee-id, company-id, salary) company(company-id, company-name, city) manages(emplovee-id, manager-id) Figure 2. Employee database

Explanation / Answer

Please find the SQl queries below-

a) Join between 3 tables is done and in where clause First Bank Corporation is selected.

SELECT e.employee-name
FROM employee AS e
INNER JOIN works AS w
ON e.employee-id = w.employee-id
INNER JOIN company AS c
c.company-id = w.company-id
WHERE c.company-name = 'First Bank Corporation';

b) Same as above, only city is also included in result set along with the employee name.
SELECT e.employee-name, e.city
FROM employee AS e
INNER JOIN works AS w
ON e.employee-id = w.employee-id
INNER JOIN company AS c
c.company-id = w.company-id
WHERE c.company-name = 'First Bank Corporation';

c) All the details of the employee are selected from the employee table. Join between 3 table is done and in where clause two conditions are used connected with AND.

SELECT e.employee-name, e.street, e.city
FROM employee AS e
INNER JOIN works AS w
ON e.employee-id = w.employee-id
INNER JOIN company AS c
c.company-id = w.company-id
WHERE c.company-name = 'First Bank Corporation'
AND w.salary > 10000;

d) Three tables are joined and in where clause company city and employee city are matched to give the desired result.

SELECT e.employee-id, e.employee-name, e.street, e.city
FROM employee AS e
INNER JOIN works AS w
ON e.employee-id = w.employee-id
INNER JOIN company AS c
c.company-id = w.company-id
WHERE e.city = c.city;

e) Self join for employee city is done in order to match the manager city and employee city.

SELECT e.epmloyee-id, e.employee-name, e.street, e.city
FROM employee AS e
INNER JOIN manages AS m
ON e.employee-id = m.employee-id
INNER JOIN employee AS e2
ON e2.employee-id = m.manager-id
WHERE e.city = e2.city;

f) Join between 3 table are done and in where clause all those companys are filtered which are not 'First Bank Corporation'.

SELECT e.employee-name, e.city, e.street, e.city
FROM employee AS e
INNER JOIN works AS w
ON e.employee-id = w.employee-id
INNER JOIN company AS c
c.company-id = w.company-id
WHERE c.company-name <> 'First Bank Corporation';

Related Questions

Navigate

• Browse (All)
• Browse 5
• Subjects

---

---

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