SQL SELECT Practice

The following exercise is adapted from Database System Concepts (Silberschatz et al., 2011).

Assume that the following relational schema exists:

employee: person_name,* street, city
works: person_name,* company_name, salary
company: company_name,* city
manages: person_name,* manager_name

Queries

Write SQL queries to retrieve the following information (solutions):

Find the name of all managers without duplicates.
```
SELECT DISTINCT manager_name
FROM manages;
```

Find the name of all employees who live in “Mumbai.”

SELECT person_name
FROM employee
WHERE city = 'Mumbai';

Find all employees who work directly for “Jones.”

SELECT person_name
FROM manages
WHERE manager_name = 'Jones';

Find the cities of residence of all employees who work directly for “Jones.” (Practice Exercise 6.8.b)

SELECT city
FROM employee, manages
WHERE employee.person_name = manages.person_name
  AND manager_name = 'Jones';

Find the names of all employees who live in the same city and on the same street as their managers. (Exercise 6.15.e)

SELECT employee.person_name
FROM employee, manages, employee AS manager
WHERE employee.person_name = manages.person_name
  AND manages.manager_name = manager.person_name
  AND employee.street = manager.street
  AND employee.city = manager.city;

Find the name of the manager of the manager of “Jones.” (Practice Exercise 6.8.c)

SELECT supervisor.manager_name
FROM manages, manages AS supervisor
WHERE manages.manager_name = supervisor.person_name
  AND manages.person_name = 'Jones';

Find the names of all employees who earn more than $75,000 per year for a single company.

SELECT person_name
FROM employee, works
WHERE employee.person_name = works.person_name
  AND works.salary >= 75000;

Find the names of all employees who work for “Small Bank Corporation” and live in New York.

SELECT person_name
FROM employee, works
WHERE employee.person_name = works.person_name
  AND works.company = 'Small Bank Corporation'
  AND employee.city = 'New York';

Find the names of all employees who live on a street with “Grand” in its name. Do not include duplicates.
```
SELECT DISTINCT person_name
FROM employee
WHERE street LIKE '%Grand%';
```