MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_main (
  id INT NOT NULL AUTO_INCREMENT,
  price INT NULL DEFAULT NULL,
  house VARCHAR(30) NULL DEFAULT NULL,
  name_town VARCHAR(30) NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO table_main 
(price	, house	, 	name_town		) 
VALUES 
(100 	, 	'A' , 	'DAISYTOWN'		),
(1500 	, 	'C' , 	'PRICETOWN'		),
(400	,	'B',	'QUEENSTOWN'	),
(200	,	'D',	'JACKSTOWN'		),
(150	,	'E',	'JACKSTOWN'		),
(400	,	'F',	'PRICETOWN'		),
(200	,	'A',	'DAISYTOWN'		),
(700	,	'B',	'QUEENSTOWN'	),
(800	,	'G',	'QUEENSTOWN'	),
(2500	,	'H',	'HYPERTOWN'		)
;

CREATE TABLE table_town (
  id INT NOT NULL AUTO_INCREMENT,
  name_town VARCHAR(30) NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE table_house (
  id INT NOT NULL AUTO_INCREMENT,
  table_town_id INT NOT NULL,
  house VARCHAR(30) NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE table_sale_estate (
  id INT NOT NULL AUTO_INCREMENT,
  table_house_id INT NOT NULL,
  price INT NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

ALTER TABLE table_sale_estate ADD CONSTRAINT `table_sale_estate_fk_table_house` FOREIGN KEY (`table_house_id`) REFERENCES `table_house`(`id`);

ALTER TABLE table_house ADD CONSTRAINT `table_house_fk_table_town` FOREIGN KEY (`table_town_id`) REFERENCES `table_town`(`id`);

Records: 10  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0


INSERT INTO table_town (name_town)
SELECT DISTINCT name_town
FROM table_main
ORDER BY name_town;

SELECT * FROM table_town;

Records: 5  Duplicates: 0  Warnings: 0




id
name_town


1
DAISYTOWN

2
HYPERTOWN

3
JACKSTOWN

4
PRICETOWN

5
QUEENSTOWN


INSERT INTO table_house(table_town_id, house)
SELECT DISTINCT t.id, m.house
FROM table_main m INNER JOIN table_town t
ON t.name_town = m.name_town
ORDER BY m.house;

SELECT * FROM table_house;

Records: 8  Duplicates: 0  Warnings: 0




id
table_town_id
house


1
1
A

2
5
B

3
4
C

4
3
D

5
3
E

6
4
F

7
5
G

8
2
H


INSERT INTO table_sale_estate(table_house_id, price)
SELECT h.id, m.price
FROM table_main m INNER JOIN table_house h
ON h.house = m.house 
ORDER BY m.id;

SELECT * FROM table_sale_estate;

Records: 10  Duplicates: 0  Warnings: 0




id
table_house_id
price


1
1
100

2
3
1500

3
2
400

4
4
200

5
5
150

6
6
400

7
1
200

8
2
700

9
7
800

10
8
2500