SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Peoples ( ID int primary key, NameAbbreviation varchar(255), Birthday timestamptz default now(), "Status" varchar(255), "Group" varchar (20) ); 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, 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 (1, 'Казань', 'По прописке'); 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 NameAbbreviation, Birthday, Title FROM Peoples INNER JOIN Address ON ID = PeopleID; SELECT NameAbbreviation, "Status", Title FROM Peoples INNER JOIN Address ON ID = PeopleID WHERE Title = 'Можга'; SELECT NameAbbreviation, "Group", Title FROM Peoples INNER JOIN Address ON ID = PeopleID WHERE Title = 'Москва' AND "Group" = 'работа'; SELECT Birthday, "Group", Title FROM Peoples INNER JOIN Address ON ID = PeopleID WHERE Title = 'Москва' OR "Group" = 'работа'; SELECT * FROM Peoples INNER JOIN Address ON ID = PeopleID; SELECT * FROM Peoples LEFT JOIN Address ON ID = PeopleID; SELECT * FROM Peoples RIGHT JOIN Address ON ID = PeopleID; SELECT * FROM Peoples FULL JOIN Address ON ID = PeopleID; SELECT NameAbbreviation, Title, "Comment" FROM Peoples RIGHT JOIN Address ON ID = PeopleID; -- Получить список всех таблиц со схемами -- SELECT * FROM INFORMATION_SCHEMA.TABLES;