20231001.md

## HW2
### 3.8

**a**. **Find the ID of each customer of the bank who has an account but not a loan.**
```SQL
SELECT ID FROM customer WHERE (ID IN depositor) AND (ID NOT IN borrower)
```
**b. Find the ID of each customer who lives on the same street and in the same city as customer '12345'.**
```sql
SELECT DISTINCT Q.ID FROM customer AS P, customer as Q
WHERE P.customer_street = Q.customer_street
AND P.customer_city = Q.customer_city
AND P.ID = '12345'
AND Q.ID <> '12345'
```
**c. Find the name of each branch that has at least one customer who has an account in the bank and who lives in “Harrison”.**
```sql
SELECT DISTINCT branch_name FROM account, depositor, customer
WHERE depositor.account_number = account.account_number
AND customer.ID = depositor.ID
AND customer.customer_city = 'Harrison'
```
### 3.15

**a.** **Find each customer who has an account at** **every** **branch located in “Brooklyn”.**
```sql
WITH branch_brooklyn(branch_name) AS (
SELECT branch_name
FROM branch
WHERE branch_city = 'Brooklyn'
)
SELECT ID.customer_name FROM customer AS cust
WHERE NOT EXISTS (
(SELECT branch_name FROM brach_brooklyn)
EXCEPT
(
SELECT branch_name
FROM account INNER JOIN depositor
ON account.account_number = depositor.account_number
)
)
```
> $A \subset B \iff A - B = \emptyset$
**b. Find the total sum of all loan amounts in the bank.**
```sql
SELECT SUM(amount) FROM loan
```
**c. Find the names of all branches that have assets greater than those of at least one branch located in “Brooklyn”**
```sql
SELECT branch_name FROM branch
WHERE assets > ANY(
SELECT assets FROM branch WHERE branch_city = 'Brooklyn'
)
```
### 3.16

**a. Find ID and name of each employee who lives in the same city as the location of the company for which the employee works**
```sql
SELECT employee.id, employee.person_name
FROM employee INNER JOIN works ON empolyee.ID = works.ID
INNER JOIN company ON works.company_name = company.company_name
WHERE employee.city = company.city
```
**b. Find ID and name of each employee who lives in the same city and on the same street as does her or his manager.**
```sql
SELECT employee.ID, employee.person_name
FROM employee AS EMP_EMP
INNER JOIN manages ON EMP_EMP.ID = manages.ID
INNER JOIN employee AS EMP_MAN ON manages.manager_id = EMP_MAN.ID
WHERE EMP_EMP.street = EMP_MAN.street AND EMP_EMP.city = EMP_MAN.city
```
**c. Find ID and name of each employee who earns more than the average salary of all employees of her or his company.**
```sql
WITH avg_salary_table(company_name, avg_salary) AS (
SELECT company_name, AVG(salary)
FROM works
GROUP BY company_name
)
SELECT employee.name, employee.ID
FROM employee INNER JOIN works ON employee.ID = works.ID
WHERE works.salary > (
SELECT avg_salary
FROM avg_salary_table
WHERE avg_salary_table.company_name = works.company_name
)
```
**d. Find the company that has the smallest payroll.**
```SQL
WITH payroll_table (company_name, payroll) AS (
SELECT company_name, SUM(salary)
FROM works GROUP BY company_name
)
SELECT company_name
FROM payroll_table ORDER BY payroll ASC LIMIT 1
```