TalkPlayLove

---

person(driver_id, name, address)
car(license, model, year)
accident(report_numnber, date, location)
owns(driver_id, license)
participated(driver_id, license, report_number, damage_amount)

(SELECT c.license
 FROM person p, owns o
 WHERE p.driver_id = o.driver_id AND name = "John Smith");

Figure 3.12
employee(employee_name, street, city)
works(employee_name, company_name, salary)
company(company_name, city)
manages(employee_name, manager_name)

3.2 Consider the employee database of FIgure 3.12, where the primary keys are underlined. Give an expression in SQL for each of the following queries.

a. Find the names and cities of residence of all empoyees who work for First Bank Corporation.
SELECT e.employee_name, e.street, e.city
FROM employee e, works w
WHERE e.employee_name = w.employee_name
    AND company_name = 'First Bank Corporation';

b. Find the names, street address, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000.
SELECT e.employee_name, e.street, e.city
FROM employee e, works w
WHERE e.employee_name = w.employee_name
    AND company_name = 'First Bank Corporation'
    AND salary >= 10000;

c. Find all employees in the database who do not work for First Bank Corporation.
SELECT employee_name
FROM works
WHERE company_name <> 'First Bank Corporation';

more specific!

SELECT employee_name
FROM employee
WHERE employee_name NOT IN (SELECT employee_name
                                             FROM works
                                             WHERE company_name <> 'First Bank Corporation'); 

d. Find all employees in the database who earn more than each employee of Small Bank Corporation.
SELECT *
FROM works
WHERE salary > (SELECT max(salary)
                        FROM works
                        WHERE company_name = 'Small Bank Corporation');

(checked)
e. Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.
SELECT company_name
FROM company
WHERE city IN (SELECT city
                     FROM company
                     WHERE company_name = 'Small Bank Corporation');

f. Find the company that has the most employees.
SELECT company_name
FROM works
GROUP BY company_name
HAVING count(employee_name) = (SELECT count(employee_name)
                                               FROM works
                                               GROUP BY company_name
                                               ORDER BY count(employee_name) DESC
                                               LIMIT 1);

g. Find the those companies 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');



3.3 Consider the relation database of Figure 3.12. Give an expression in SQL for each of the following queries.

a. Modify the database so that Jones now lives in Newtown.
UPDATE employee
SET city = 'Newtown'
WHERE employee_name = 'Jones';

b. Give all manages of First Bank Corporation a 10 percent raise unless the salary becomes greater than $100,000; in such case give only a 3 percent raise.
UPDATE works
SET salary = salary*1.03
WHERE employee_name in (SELECT manager_name
                                     FROM manages)
AND salary*1.1 > 100000
AND company_name = 'First Bank Corporation';

UPDATE works
SET salary = salary*1.1
WHERE employee_name in (SELECT manager_name
                                     FROM manages)
AND salary*1.1 <= 100000
AND company_name = 'First Bank Corporation';



#Error Comments

(checked) 문제
e. Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.

SELECT company_name
FROM company
WHERE city IN (SELECT city
                       FROM company
                       WHERE company_name = 'Small Bank Corporation');

문제해석.
각 회사가 몇개의 도시에 지부를 가지고 있다고 가정한다. Small Bank Corporation이 위치한 도시들에 있는 모든 회사를 찾아라.

SELECT city FROM company WHERE company_name = 'Small Bank Corporation');

결과로 여러 city가 쿼리 결과값으로 리턴 될 것이다.
SELECT company_name FROM company WHERE city IN 절에서
모든 회사가 위치한 도시를 비교하여 이를 리턴한다.

문제점.
Small Bank Corporation도 쿼리 결과로 나온다.

Sol.
SELECT S.company_name
FROM company S
WHERE NOT EXITS ((SELECT city
                               FROM company
                               WHERE company_name = 'Small Bank Corporation')
                            EXCEPT
                              (SELECT city
                                FROM company T
                                WHERE S.company_name = T.company_name))

NOT EXITS 문과 EXCEPT문이 사용이 이해되지 않는다.