CREATE DATABASE Family;
-----------------------

USE Family
CREATE TABLE FamilyMembers(
    member_id INT IDENTITY(1,1) PRIMARY KEY,
    STATUS VARCHAR(100) NOT NULL,
    member_name VARCHAR(200) NOT NULL,
    birthday DATETIME NOT NULL
);

CREATE TABLE GoodTypes(
    good_type_id INT IDENTITY(1,1) PRIMARY KEY,
    good_type_name VARCHAR(300) NOT NULL
);

CREATE TABLE Goods(
    good_id INT IDENTITY(1,1) PRIMARY KEY,
    good_name VARCHAR(300) NOT NULL,
    good_type INT FOREIGN KEY REFERENCES GoodTypes(good_type_id) ON UPDATE CASCADE ON DELETE CASCADE
    );

CREATE TABLE Payments(
    payment_id INT IDENTITY(1,1) PRIMARY KEY,
    family_member INT FOREIGN KEY REFERENCES FamilyMembers(member_id) ON UPDATE CASCADE ON DELETE CASCADE,
    good INT FOREIGN KEY REFERENCES Goods(good_id) ON UPDATE CASCADE ON DELETE CASCADE,
    amount INT NOT NULL,
    unit_price INT NOT NULL,
    DATE DATETIME NOT NULL
);
----------------------------

USE Family
INSERT INTO FamilyMembers (STATUS,member_name,birthday)
VALUES ('father','Headley Quincey','1960-05-13T00:00:00.000Z'),
       ('mother','Flavia Quincey','1960-05-13T00:00:00.000Z'),
       ('son','Andie Quincey','1983-06-05T00:00:00.000Z'),
       ('daughter','Lela Quincey','1985-06-07T00:00:00.000Z'),
       ('daughter','Annie Quincey','1988-04-10T00:00:00.000Z'),
       ('father','Ernest Forrest','1961-09-11T00:00:00.000Z'),
       ('mother','Constance Forrest','1968-09-06T00:00:00.000Z'),
       ('daughter','Wednesday Addams','2005-01-13T00:00:00.000Z');


INSERT INTO GoodTypes (good_type_name)
VALUES ('communal payments'),
       ('food'),
       ('delicacies'),
       ('entertainment'),
       ('treatment'),
       ('education'),
       ('clothes'),
       ('equipment');

INSERT INTO Goods (good_name,good_type)
VALUES ('apartment fee', 1),
       ('phone fee', 1),
       ('bread', 2),
       ('milk', 2),
       ('red caviar', 3),
       ('cinema', 4),
       ('blасk caviar', 3),
       ('cough tablets', 5),
       ('potato', 2),
       ('pineapples', 3);

INSERT INTO Payments (DATE,family_member,good,amount,unit_price)
VALUES ('2005-02-12T00:00:00.000Z', 1, 1, 1, 2000),
       ('2005-03-23T00:00:00.000Z', 2, 1, 1, 2100),
       ('2005-05-14T00:00:00.000Z', 3, 4, 5, 20),
       ('2005-07-22T00:00:00.000Z', 4, 5, 1, 350),
       ('2005-06-26T00:00:00.000Z', 4, 7, 2, 150),
       ('2005-02-20T00:00:00.000Z', 5, 6, 1, 100),
       ('2005-06-30T00:00:00.000Z', 2, 6, 1, 120),
       ('2005-09-12T00:00:00.000Z', 2, 9, 1, 55),
       ('2005-09-30T00:00:00.000Z', 5, 2, 1, 230),
       ('2005-10-27T00:00:00.000Z', 5, 9, 1, 230);
----------------------------
#1) Найти имена всех матерей (mother)
USE Family

SELECT member_name AS 'имена всех матерей'
FROM FamilyMembers
WHERE STATUS = 'mother'

#2) Определить, кто из членов семьи покупал картошку (potato)
USE Family

SELECT DISTINCT member_name AS 'Все кто купил potato:'
FROM FamilyMembers
INNER JOIN Payments
ON Payments.family_member = FamilyMembers.member_id
INNER JOIN Goods
ON Goods.good_id = Payments.good
WHERE Goods.good_name = 'potato'

#3) Измените имя "Andie Quincey" на новое "Andie Anthony".
USE Family

UPDATE FamilyMembers
SET member_name = 'Andie Anthony'
WHERE member_name = 'Andie Quincey'

#4) Определить, сколько потратил в 2005 году каждый из членов семьи. В результирующей выборке не выводите тех членов семьи, которые ничего не потратили.
USE Family

SELECT member_name,
SUM(amount * unit_price)
FROM Payments
INNER JOIN FamilyMembers
ON Payments.family_member = FamilyMembers.member_id
WHERE DATE BETWEEN '2005-01-01T00:00:00.000Z' AND '2005-12-31T00:00:00.000Z'
GROUP BY member_name

#5) Выведите имя самого старшего человека. Если таких несколько, то выведите их всех.
USE Family

SELECT member_name AS 'имена самых старших людей'
FROM FamilyMembers
WHERE birthday = (SELECT MIN(birthday) FROM FamilyMembers)


#6) Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму
USE Family

SELECT STATUS, member_name, SUM(amount * unit_price) AS 'сумма'
FROM FamilyMembers
INNER JOIN Payments
ON Payments.family_member = FamilyMembers.member_id
INNER JOIN Goods
ON Payments.good = Goods.good_id
INNER JOIN GoodTypes
ON GoodTypes.good_type_id = Goods.good_type
WHERE good_type_name = 'entertainment'
GROUP BY member_name, STATUS

#7) Найдите самый дорогой деликатес (delicacies) и выведите его цену
USE Family

SELECT TOP(1) good_name, unit_price
FROM Goods
INNER JOIN GoodTypes
ON GoodTypes.good_type_id = Goods.good_type
INNER JOIN Payments
ON Payments.good = Goods.good_id
WHERE unit_price IN (SELECT unit_price FROM Payments WHERE good_type_name = 'delicacies')
ORDER BY unit_price DESC

#8) Определить кто и сколько потратил в июне 2005
USE Family

SELECT good_name AS 'Не проданные товары'
FROM Goods
WHERE Goods.good_id NOT IN (SELECT good FROM Payments)

#9) Определить, какие товары не покупались в 2005 году
USE Family

SELECT good_name AS 'Не проданные товары'
FROM Goods
WHERE Goods.good_id NOT IN (SELECT good FROM Payments)

#10) Вывести всех членов семьи с фамилией Quincey.
USE Family

SELECT member_name
FROM FamilyMembers
WHERE member_name LIKE '%Quincey'

#11) Удалить всех членов семьи с фамилией "Quincey".
USE Family

DELETE
FROM FamilyMembers
WHERE member_name LIKE '%Quincey'

#12) Вывести имена всех пассажиров, которые полетели из Москвы в Ростов (по БД из предыдущей домашки)
USE AA;
SELECT DISTINCT name
FROM Passenger
JOIN Trip
ON Passenger.id_product=Trip.id
WHERE town_from = 'Moscow' AND town_to = 'Rostov'
------------------------------