[Database] 3장. SQL 소개

1. (Oracle Live SQL capture)

a.

b.

c.

d.

e.

f.

g.

3. (Oracle Live SQL capture)

a.

b.

c.

4

a. Find the total number of people who owned ars that were involved in aidents in 2017.

SELECT COUNT(DISTINCT person.driver_id)
FROM accident, participated, person, owns
WHERE accident.report_number = participated.report_number
 AND owns.driver_id = person.driver_id
 AND owns.license_plate = participated.license_plate
 AND year = 2017

b. Delete all year-2010 ars belonging to the person whose ID is 12345..

DELETE FROM car
WHERE year = 2010 AND license_plate IN
 (SELECT license_plate FROM owns WHERE driver_id = '12345')

9.

a.

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

b.

SELECT ID, name, city
FROM employee
WHERE ID IN (
 SELECT ID
 FROM works
 WHERE company_name = 'First Bank Corporation' AND salary > 10000
)

c.

SELECT ID
FROM works
WHERE company_name <> 'First Bank Corporation'

d.

SELECT ID
FROM works
WHERE salary > ALL (
 SELECT salary
 FROM works
 WHERE company_name = 'Small Bank Corporation'
)

e.

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
 )
)

f.

SELECT company_name
FROM works
GROUP BY company_name
HAVING COUNT(DISTINCT ID) >= ALL (
 SELECT COUNT(DISTINCT ID)
 FROM works
 GROUP BY company_name
)

g.

SELECT company_name
FROM works
GROUP BY company_name
HAVING AVG(salary) > (
 SELECT AVG(salary)
 FROM works
 WHERE company_name = 'First Bank Corporation'
)

11. (Oracle Live SQL capture)

a.

b.

c.

d.