SQLize | PHPize | SQLtest

CREATE TABLE Peoples ( ID int primary key, NameAbbreviation varchar(255), Birthday timestamptz default now(), "Status" varchar(255), "Group" varchar(255) ); CREATE TABLE Phone ( PeopleID int, Phone int, "Comment" varchar(255) ); create unique index people_phone_idx ON Phone (PeopleID, Phone); -- ALTER TABLE Phone ADD CONSTRAINT "user_phone_ref" FOREIGN KEY (PeopleID) REFERENCES Peoples(ID); CREATE TABLE Address ( PeopleID int primary key, Title varchar(255), "Comment" varchar(255) ); create unique index people_address_idx ON Address (PeopleID, Title); -- ALTER TABLE Address ADD CONSTRAINT "user_address_ref" FOREIGN KEY (PeopleID) REFERENCES Peoples(ID); -- Люди INSERT INTO Peoples (ID, NameAbbreviation, Birthday, "Status", "Group") VALUES (1, 'Иванов И. И.', '2.12.1990', 'женат', 'Работа'); INSERT INTO Peoples (ID, NameAbbreviation, Birthday, "Status", "Group") VALUES (2, 'Иванов И. И.', '9.18.2001', 'холост', 'Школа'); INSERT INTO Peoples (ID, NameAbbreviation, Birthday, "Status", "Group") VALUES (3, 'Петров П. П.', '4.23.1983', 'женат', 'Работа'); INSERT INTO Peoples (ID, NameAbbreviation, Birthday, "Status", "Group") VALUES (4, 'Васильев В. В.', '5.21.1998', 'холост', 'Родня'); INSERT INTO Peoples (ID, NameAbbreviation, Birthday, "Status", "Group") VALUES (5, 'Кузьмин К.К.', '5.21.2020', 'холост', 'Родня'); -- Телефоны INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (1, 123, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (1, 124, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (1, 125, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (1, 126, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (1, 127, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (2, 527, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (3, 234, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (4, 235, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (11, 456, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (12, 999, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (13, 997, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (14, 995, 'Личный'); INSERT INTO Phone (PeopleID, Phone, "Comment") VALUES (15, 993, 'Личный'); -- Адреса INSERT INTO Address (PeopleID, Title, "Comment") VALUES (1, 'Можга', 'Место рождения'); INSERT INTO Address (PeopleID, Title, "Comment") VALUES (2, 'Санкт-Петербург', 'По прописке'); INSERT INTO Address (PeopleID, Title, "Comment") VALUES (3, 'Москва', 'По прописке'); INSERT INTO Address (PeopleID, Title, "Comment") VALUES (4, 'Белгород', 'По прописке'); INSERT INTO Address (PeopleID, Title, "Comment") VALUES (5, 'Уфа', 'По прописке'); INSERT INTO Address (PeopleID, Title, "Comment") VALUES (6, 'Сочи', 'По прописке'); INSERT INTO Address (PeopleID, Title, "Comment") VALUES (7, 'Киров', 'Рабочий'); INSERT INTO Address (PeopleID, Title, "Comment") VALUES (8, 'Владивосток', 'Место рождения'); INSERT INTO Address (PeopleID, Title, "Comment") VALUES (9, 'Рязань', 'Рабочий'); INSERT INTO Address (PeopleID, Title, "Comment") VALUES (10, 'Хабаровск', 'Место рождения'); --SELECT ФИО, Д/р, Адрес FROM Общий список --SELECT ФИО, Статус FROM Общий список WHERE Адрес = «Можга» --SELECT ФИО FROM Общий список WHERE Адрес = «Москва» AND Группа = «Работа» --SELECT Д/р FROM Общий список WHERE Адрес = «Москва» OR Группа = «Работа» SELECT NameAbbreviation, Birthday, Title FROM Peoples LEFT JOIN Address ON Peoples.id = Address.peopleid; SELECT NameAbbreviation, "Status" FROM Peoples LEFT JOIN Address ON Peoples.id = Address.peopleid WHERE Title='Можга'; SELECT NameAbbreviation FROM Peoples LEFT JOIN Address ON Peoples.id = Address.peopleid WHERE Title='Москва' AND "Group"='Работа'; SELECT Birthday FROM Peoples LEFT JOIN Address ON Peoples.id = Address.peopleid WHERE Title='Москва' OR "Group"='Работа'; --Что будет результатом следующих JOIN’ов: --INNER JOIN Люди, Адреса ON id = Чей адрес --LEFT JOIN Люди, Адреса ON id = Чей адрес --RIGHT JOIN Люди, Адреса ON id = Чей адрес --FULL JOIN Люди, Адреса ON id = Чей адрес SELECT * FROM Peoples INNER JOIN Address ON Peoples.id = Address.peopleid; SELECT * FROM Peoples LEFT JOIN Address ON Peoples.id = Address.peopleid; SELECT * FROM Peoples RIGHT JOIN Address ON Peoples.id = Address.peopleid; SELECT * FROM Peoples FULL JOIN Address ON Peoples.id = Address.peopleid; --Дополнительное задание. Что будет результатом выборки: SELECT NameAbbreviation, Title, "Comment" FROM Peoples RIGHT JOIN Address ON Peoples.id = Address.peopleid;