Chapter five: 5.1 and 4.2

Database1:

person(SS#, name, address)

car(license, year, model)

accident(date, driver, damage-amount)

owns(SS#, license)

log(license, date, driver)

 

Database 2:

employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
managers (employee-name, manager-name)

 

Queries:

database1

5.1a: Find the total number of people whose car was involved in an accident in 1989.

5.1b: Find the number of accidents in which at least on car belonging to "John Smith" were involved.

(5.2 heeft veel weg van 4.2 daarom de vragen uit 4.2 zodat je SQL met QBE kunt vergelijken.)

database2

4.2a: The names of the employees working for FBC

4.2b: The names and cities of residence of all employees working for FBC

4.2c: The names, street addresses and cities of residence of all employees working for FBC and earn more than $10,000

4.2d: All employees who live in the same cities as the companies they work for

4.2e: All employees who live in the same cities and on the same streets as their managers

4.2f: All employees who do not work for FBC (multiple and single employer cases)

4.2g: All employees who earn more than every employee of SBC

4.2h: All companies located in every city in which SBC is located (companies could be located in several cities)

 

Solutions:

5.1a: Find the total number of people whose car was involved in an accident in 1989.

(Obs. The logfile consists of the license of the car, of the date the accident and the name of the driver)

SELECT COUNT DISTINCT driver

FROM accident

WHERE date between 01-01-1989 and 31-12-1989

accident	date	driver	damage-amount
	_x	P. CNT.UNQ.ALL
condition year(x) = 1989

5.1b: Find the number of accidents in which at least on car belonging to "John Smith" were involved.

SELECT COUNT license, date, driver

FROM log AS l, owns AS o, person AS p

WHERE l.license=o.license AND o.SS#=p.SS# AND name="John Smith"

log	license	date	driver
P.CNT.UNQ.ALL	_x
owns	SS#	license
	_y	_x
person	SS#	name	address
	_y	"John Smith"

5.1c-e hoeven niet.

4.2a The names of the employees working for FBC.

SELECT employee-name

FROM works

WHERE company-name="FBC"

works	employee-name	company-name	salary
	P. _x	"FBC"

4.2.b: The names and cities of residence of all employees working for FBC.

(Obs.: single job assumption; only work for FBC)

SELECT e.employee-name, e.city
FROM employee AS e, works AS w
WHERE w.employee-name = e.employee-name AND company-name = "FBC" ;

employee	employee-name	street	city
	P. _x		P.
works	employee-name	company-name	salary
	_x	"FBC"

(Obs.: multiple job assumption; only work for FBC)

SELECT e.employee-name, e.city
FROM employee AS e
WHERE e.employee-name not in

(SELECT w.employee-name
FROM works AS w
WHERE e.employee-name = w.employee-name AND company-name <>"FBC") ;

employee	employee-name	street	city
	P. _x		P.
works	employee-name	company-name	salary
¬	_x	¬"FBC"

4.2c The names, street addresses and cities of residence of all employees working for FBC and earn more than $10,000.

(Obs.: single job assumption)

SELECT e.employee-name, e.street, e.city
FROM employee AS e, works AS w
WHERE w.employee-name = e.employee-name AND company-name = "FBC" AND salary >10000;

employee	employee-name	street	city
P.	_x
works	employee-name	company-name	salary
	_x	"FBC"	_s
conditions _s > 10000

(Obs.: multiple job assumption)

SELECT e.employee-name, e.street, e.city

FROM employee AS e

WHERE exists

(SELECT w.employee-name

FROM works AS w

WHERE w.employee-name=e.employee-name AND w.company-name="FBC" AND w.employee-name in

(SELECT e.employee-name

FROM works AS w

GROUP BY w.employee-name

HAVING SUM(salary) > 10000));

employee	employee-name	street	city
P.	_x
works	employee-name	company-name	salary
	_x	"FBC"
	G._x		_s
conditions SUM.UNQ._s>10000

4.2d All employees who live in the same cities as the companies they work for.

SELECT e.employee-name
FROM employee AS e, works AS w, company AS c
WHERE w.employee-name = e.employee-name AND w.company-name = c.company-name AND e.city = c.city ;

employee	employee-name	street	city
	P._x		_z
works	employee-name	company-name	salary
	_x	_y
company	company-city	company-name
	_z	_y

4.2e All employees who live in the same cities and on the same streets as their managers.

SELECT e.employee-name
FROM employee AS e, employee AS e1, manager AS m
WHERE m.employee-name = e.employee-name AND m.manager-name = e1.employee-name AND e.city = e1.city AND e.street = e1.street ;

employee	employee-name	street	city
	P. _x	_y	_z
	-m	_y	_z
manages	employee-name	manager-name
	_x	_m

4.2f All employees who do not work for FBC.

(Obs.: only one job per employee allowed)

SELECT employee-name
FROM works
WHERE company-name <> "FBC" ;

works	employee-name	company-name	salary
	P._x	¬"FBC"

(Obs.: multiple jobs per employee allowed)

SELECT e.employee-name
FROM employee AS e
WHERE e.employee-name not in

(SELECT employee-name
FROM works
WHERE company-name = "FBC") ;

employee	employee-name	street	city
	P._x
works	employee-name	company-name	salary
¬	_x	"FBC"

4.2g All employees who earn more than every employee of SBC.

SELECT employee-name
FROM works
WHERE salary >

(SELECT max(salary)
FROM works
WHERE company-name = "SBC") ;

works	employee-name	company-name	salary
	P._x		_y
¬		"SBC"	_z
conditions _z > _y

4.2h All companies located in every city in which SBC is located (companies could be located in several cities).

SELECT c.company-name

FROM company AS c

WHERE not exists

(SELECT c1.city

FROM company AS c1

WHERE c1.company-name = "SBC" AND c1.city not in

(SELECT city

FROM company

WHERE c.company-name=c1.company-name));

 

company	company-city	company-name
		P.x
¬	_z	"SBC"
¬	_z	_x