database course homework 3
this homework depends on the database built in database course homework 1.
i used postgresql to do this homework.
sample data
here i insert some dummy data for testing-- customers
INSERT INTO customer (first_name, family_name, customer_id, hometown, street, house_number, phone_number, customer_type)
VALUES('some', 'one', '111', 'netanya', 'hertzel', '22', '503344556', 'private');
INSERT INTO customer (first_name, family_name, customer_id, hometown, street, house_number, phone_number, customer_type)
VALUES('other', 'one', '222', 'tel aviv', 'dezengov', '23', '514455667', 'business');
INSERT INTO customer (first_name, family_name, customer_id, hometown, street, house_number, phone_number, customer_type)
VALUES('yet', 'another', '444', 'netanya', 'hertzel', '77', '588346677', 'private');
INSERT INTO car (car_number, car_type, company, production_year, engine_number, kilometers, status)
VALUES('1111111', 'private', 'honda', 2020, '1600', 100000, 'off');
INSERT INTO car (car_number, car_type, company, production_year, engine_number, kilometers, status)
VALUES('6666666', 'private', 'pazo', 2010, '2000', 200000, 'new');
INSERT INTO car_owner (customer_id, car_number, start_date, end_date)
VALUES('111', '1111111', make_date(2022, 5, 15), make_date(2024, 5, 29));
INSERT INTO garage (garage_name, town, street, company, house_number, phone_number, specialty)
VALUES('carioci', 'netanya', 'hertzel', 'pazo', '11', '500000000', 'private');
INSERT INTO garage (garage_name, town, street, company, house_number, phone_number, specialty)
VALUES('carioc2', 'netanya', 'hertzel', 'pazo', '11', '500000000', 'private');
INSERT INTO car_maintenance (car_number, maintenance_type, maintenance_date, garage_name, responsible_worker_id)
VALUES('1111111', 'proactive', make_date(2022, 5, 15), 'carioci', '444');
INSERT INTO car_maintenance (car_number, maintenance_type, maintenance_date, garage_name, responsible_worker_id)
VALUES('1111111', 'proactive', make_date(2018, 5, 15), 'carioci', '444');
INSERT INTO car_maintenance (car_number, maintenance_type, maintenance_date, garage_name, responsible_worker_id)
VALUES('1111111', 'proactive', make_date(2020, 5, 15), 'carioci', '444');
INSERT INTO car_maintenance (car_number, maintenance_type, maintenance_date, garage_name, responsible_worker_id)
VALUES('1111111', 'proactive', make_date(2020, 5, 18), 'carioci', '444');
INSERT INTO car_maintenance (car_number, maintenance_type, maintenance_date, garage_name, responsible_worker_id)
VALUES('1111111', 'proactive', make_date(2015, 5, 18), 'carioci', '444');
INSERT INTO car_maintenance (car_number, maintenance_type, maintenance_date, garage_name, responsible_worker_id)
VALUES('1111111', 'proactive', make_date(2019, 5, 18), 'carioci', '444');
INSERT INTO car_maintenance (car_number, maintenance_type, maintenance_date, garage_name, responsible_worker_id)
VALUES('1111111', 'proactive', make_date(2019, 5, 18), 'carioc2', '444');
INSERT INTO replaced_part (maintenance_number, part_name)
VALUES(1, 'door');
homework problem set
write each of the following queries
return all customers, show first name, last name, id, hometown, street, house number, phone number.
SELECT * FROM customer;
| first_name | family_name | customer_id | hometown | street | house_number | phone_number | customer_type |
| some | one | 111 | netanya | hertzel | 22 | 503344556 | private |
| other | one | 222 | tel aviv | dezengov | 23 | 514455667 | business |
| yet | another | 444 | netanya | hertzel | 77 | 588346677 | private |
return the name of owners (first name and last name), id, phone number and ids.
SELECT
CONCAT(c.first_name, ' ', c.family_name) AS name, c.customer_id, c.phone_number
FROM customer c
INNER JOIN car_owner co ON co.customer_id = c.customer_id;
| name | customer_id | phone_number |
| some one | 111 | 503344556 |
return the name of a worker (first name and last name) that has done the maintenance, car number, garage name, maintenance date and maintenance type.
SELECT
CONCAT(c.first_name, ' ', c.family_name) AS name, m.car_number, m.garage_name, m.maintenance_date
FROM customer c
INNER JOIN car_maintenance m ON m.responsible_worker_id = c.customer_id;
| name | car_number | garage_name | maintenance_date |
| yet another | 1111111 | carioci | 2022-05-15 |
| yet another | 1111111 | carioci | 2018-05-15 |
| yet another | 1111111 | carioci | 2020-05-15 |
| yet another | 1111111 | carioci | 2020-05-18 |
| yet another | 1111111 | carioci | 2015-05-18 |
| yet another | 1111111 | carioci | 2019-05-18 |
| yet another | 1111111 | carioc2 | 2019-05-18 |
return for each car its car number, car type, manufacturing company name, production year, engine number, miles travelled only for cars whose engine number contains the digit 6 and the car is in a cancelled status.
SELECT
car_number, car_type, company, production_year, engine_number, kilometers
FROM car
WHERE engine_number ~ '.*6.*' AND status = 'off';
| car_number | car_type | company | production_year | engine_number | kilometers |
| 1111111 | private | honda | 2020 | 1600 | 100000 |
return the garage name and maintenance dates and the number of cars that a garage had done maintenance for in the year 2018 and in the year 2020, sorted according to the garage name.
SELECT g.garage_name, maintenance_date, num
FROM
(
SELECT garage_name, COUNT(*) as num
FROM car_maintenance
WHERE (2018 = EXTRACT(year FROM maintenance_date) OR EXTRACT(year FROM maintenance_date) = 2020)
GROUP BY garage_name
) g
INNER JOIN car_maintenance c
ON c.garage_name = g.garage_name
AND (EXTRACT(year FROM c.maintenance_date) = 2018 OR EXTRACT(year FROM c.maintenance_date) = 2020)
ORDER BY g.garage_name
| garage_name | maintenance_date | num |
| carioci | 2018-05-15 | 3 |
| carioci | 2020-05-15 | 3 |
| carioci | 2020-05-18 | 3 |
return the names of garages that have done more than 3 maintenances in the year 2015 and in the year 2019. show the names of the garages and the number of maintenances in descending order, such that the garage that has done the most maintenances appears first.
the writing is ambiguous, but i will assume we're talking about the number of maintenances in the years 2015, 2019 only.
SELECT garage_name, COUNT(*) as num
FROM car_maintenance
WHERE (2015 = EXTRACT(year FROM maintenance_date) OR EXTRACT(year FROM maintenance_date) = 2019)
GROUP BY garage_name
HAVING COUNT(*) > 3
ORDER BY num DESC
| garage_name | num |
return the name of the owner of the car (first and last name) and all the maintenances (car number, maintenance type, maintenance date and garage name) of which the date is between 01/01/2015 and 31/12/2020 sorted by the customer name and the maintenance date.
SELECT m.garage_name, CONCAT(c.first_name, ' ', c.family_name) AS customer_name, w.car_number, m.maintenance_type, m.maintenance_date
FROM customer c
INNER JOIN car_owner AS w ON w.customer_id = c.customer_id
INNER JOIN car_maintenance AS m ON m.car_number = w.car_number
WHERE m.maintenance_date >= '2014-01-01' AND m.maintenance_date < '2020-12-31'
ORDER BY customer_name, m.maintenance_date
| garage_name | customer_name | car_number | maintenance_type | maintenance_date |
| carioci | someone | 1111111 | proactive | 2015-05-18 |
| carioci | someone | 1111111 | proactive | 2018-05-15 |
| carioc2 | someone | 1111111 | proactive | 2019-05-18 |
| carioci | someone | 1111111 | proactive | 2019-05-18 |
| carioci | someone | 1111111 | proactive | 2020-05-15 |
| carioci | someone | 1111111 | proactive | 2020-05-18 |
return the name of the garage, the name of the part and the number of parts replaced, sorted by the name of the garage.
select g.garage_name, r.part_name, g.num
from
(
SELECT garage_name, COUNT(*) as num
FROM car_maintenance c
INNER JOIN replaced_part r ON r.maintenance_number = c.maintenance_number
GROUP BY c.garage_name
) g
INNER JOIN car_maintenance c ON c.garage_name = g.garage_name
INNER JOIN replaced_part r ON c.maintenance_number = r.maintenance_number
ORDER BY g.garage_name
| garage_name | part_name | num |
| carioci | door | 1 |
show the id and the names of the customers (first and last name) and the number of cars of type honda that they have owned, only if there is more than one car that belongs to the customer.
SELECT CONCAT(c.first_name, ' ', c.family_name) AS customer_name, COUNT(*) as num
FROM customer c
INNER JOIN car_owner AS o ON o.customer_id = c.customer_id
INNER JOIN car AS a ON a.car_number = o.car_number
GROUP BY c.customer_id, a.company
HAVING a.company = 'honda'
| customer_name | num |
| someone | 1 |
show the customers names (first and last name) and the distance travelled in total by all the cars that they own, sorted from the biggest to the smallest.
SELECT CONCAT(c.first_name, ' ', c.family_name) AS customer_name, SUM(kilometers) as num
FROM customer c
INNER JOIN car_owner AS o ON o.customer_id = c.customer_id
INNER JOIN car AS a ON a.car_number = o.car_number
GROUP BY c.customer_id
ORDER BY num DESC
| customer_name | num |
| some one | 100000 |