3.9

Consider the relational database of Figure 3.19, where the primary keys are underlined. Give an expression in SQL for each of the following queries.

  1. Find the ID, name, and city of residence of each employee who works for “First Bank Corporation”.
  2. Find the ID, name, and city of residence of each employee who works for “First Bank Corporation” and earns more than $10000.
  3. Find the ID of each employee who does not work for “First Bank Corporation”.
  4. Find the ID of each employee who earns more than every employee of “Small Bank Corporation”.
  5. Assume that companies may be located in several cities. Find the name of each company that is located in every city in which “Small Bank Corporation” is located.
  6. Find the name of the company that has the most employees (or companies, in the case where there is a tie for the most).
  7. Find the name of each company whose employees earn a higher salary, on average, than the average salary at “First Bank Corporation”.
  1. Find the ID, name, and city of residence of each employee who works for “First Bank Corporation”.
SELECT e.ID, e.person_name, city
FROM employee AS e, works AS w
WHERE w.company_name = 'First Bank Corporation' AND w.ID = e.ID
  1. Find the ID, name, and city of residence of each employee who works for “First Bank Corporation” and earns more than $10000.
SELECT ID, name, city
FROM employee 
WHERE ID IN (
    SELECT ID
    FROM works
    WHERE company_name = 'First Bank Corporation' AND salary > 10000
)

This could be written also in the style of the answer to part a, as follows:

SELECT e.ID, e.person_name, city
FROM employee AS e, works AS w
WHERE w.company_name = 'First Bank Corporation' AND w.ID = e.ID
    AND w.salary > 10000
  1. Find the ID of each employee who does not work for “First Bank Corporation”.
SELECT ID
FROM works
WHERE company_name <> 'First Bank Corporation'

If one allows people to appear in employee without appearing also in works, the solution is slightly more complicated. An outer join as discussed in Chapter 4 could be used as well.

SELECT ID 
FROM employee
WHERE ID NOT IN (
    SELECT ID
    FROM works
    WHERE company_name = 'First Bank Corporation'
)
  1. Find the ID of each employee who earns more than every employee of “Small Bank Corporation”.
SELECT ID
FROM works
WHERE salary > ALL (
    SELECT salary
    FROM works
    WHERE company_name = 'Small Bank Corporation'
)

If people may work for several companies and we wish to consider the total earnings of each person, the is more complex. But note that the fact that ID is the primary key for works implies that this cannot be the case.

  1. Assume that companies may be located in several cities. Find the name of each company that is located in every city in which “Small Bank Corporation” is located.
SELECT S.company_name 
FROM company AS S 
WHERE NOT EXISTS (
    (
        SELECT city
        FROM company
        WHERE company_name = 'Small Bank Corporation'
    )
    EXCEPT
    (
        SELECT city
        FROM company AS T
        WHERE T.company_name = S.company_name
    )
)
  1. Find the name of the company that has the most employees (or companies, in the case where there is a tie for the most).
SELECT company_name 
FROM works
GROUP BY company_name
HAVING COUNT(DISTINCT ID) >= ALL (
    SELECT COUNT(DISTINCT ID)
    FROM works
    GROUP BY company_name
)
  1. Find the name of each company whose employees earn a higher salary, on average, than the average salary at “First Bank Corporation”.
SELECT company_name
FROM works
GROUP BY company_name 
HAVING AVG(salary) >  (
    SELECT AVG(salary)
    FROM works
    WHERE company_name = 'First Bank Corporation'
)