ynpM3JAS

1DROP DATABASE if exists RealEstateAgents;
2CREATE DATABASE RealEstateAgents;
3USE RealEstateAgents;
4
5CREATE TABLE RealtorOffice (
6Office_Id INT NOT NULL AUTO_INCREMENT,
7Address VARCHAR (50) NOT NULL,
8City VARCHAR (30) NOT NULL,
9PRIMARY KEY (Office_Id)
10);
11
12CREATE TABLE Realtor (
13Agent_Id INT NOT NULL AUTO_INCREMENT,
14Agent_name VARCHAR (40) NOT NULL,
15Office_Id INT,
16PRIMARY KEY (Agent_Id),
17FOREIGN KEY (Office_Id) references RealtorOffice (Office_Id)
18);
19
20INSERT INTO RealtorOffice (Address, City) VALUES ('Järnvägsgatan 1', 'Helsingborg');
21INSERT INTO RealtorOffice (Address, City) VALUES ('Drottninggatan 1', 'Landskrona');
22INSERT INTO RealtorOffice (Address, City) VALUES ('Kungsgatan 1', 'Malmö');
23
24INSERT INTO Realtor (Agent_name, Office_Id) VALUES ('Mary Todd', 3);
25INSERT INTO Realtor (Agent_name, Office_Id) VALUES ('Nancy Reagan', 1);
26INSERT INTO Realtor (Agent_name, Office_Id) VALUES ('Jackie Kennedy', 2);
27INSERT INTO Realtor (Agent_name, Office_Id) VALUES ('Michelle Obama', 3);
28
29
30SELECT Realtor.Agent_name, RealtorOffice.Address, RealtorOffice.City
31FROM Realtor
32LEFT JOIN RealtorOffice
33ON Realtor.Office_Id = RealtorOffice.Office_Id;
34
35
36SELECT * FROM Realtor;
37-- SELECT * FROM RealtorOffice;