mssql

USE cinema_second_DB;

DROP TABLE IF EXISTS Afisha
DROP TABLE IF EXISTS Films
DROP TABLE IF EXISTS Halls


CREATE TABLE Films
(
film_id INT PRIMARY KEY,
film_name CHAR (40),
film_genre CHAR (40),
film_producer CHAR (40),
film_developer CHAR (40),
film_year INT,
age_watchers INT
)


CREATE TABLE Halls
(
hall_id INT PRIMARY KEY,
hall_name CHAR (15),
place_count INT,
hall_status CHAR (25)
)
GO

CREATE TABLE Afisha
(
code_afisha INT PRIMARY KEY IDENTITY(1,1),
code_film INT FOREIGN KEY REFERENCES Films(film_id),
dateOfStart_hire DATETIME,
code_hall INT FOREIGN KEY REFERENCES Halls(hall_id),
cost INT,
free_space INT
)
GO

-- Заполнение:

INSERT INTO Films
VALUES (10, 'Nemesis', 'Horror', 'Bey', 'Disney', '20150403', 18),
(11, 'Avengers', 'Superhero, Action', 'Kevin Feige', 'Marvel Studios', '20151013', 12),
(12, 'Iron Man', 'Superhero, Action', 'Kevin Feige', 'Marvel Studios', '20120723', 12),
(13, 'Captain America', 'Superhero, Action', 'Joe Russo', 'Marvel Studios', '20170304', 12),
(14, 'Avengers Eternity', 'Superhero, Action', 'Anthony Russo', 'Marvel Studios', '20100403', 12),
(15, 'Batman', 'Superhero, Action', 'Someone', 'Warner Bros', '20120129', 16),
(16, 'Black Panther', 'Superhero, Action', 'Kevin Feige', 'Marvel Studios', '20180129', 12),
(17, 'The Angry Birds Movie', 'Animated, Comedy, Adventure', 'Thurop Van Orman', 'Columbia Pictures', '20190403', 6),
(18, 'Shreck', 'Animated, Comedy, Adventure', 'Someone Number Two', 'Disney', '20081123', 6),
(19, 'Avengers Endgame', 'Superhero, Action', 'Bey', 'Marvel Studios', '20190426', 12);


INSERT INTO Halls
VALUES (111, 'Sensei', 150, 'Open'),
(222, 'Ultra', 200, 'Open'),
(333, 'Exta', 250, 'Open'),
(444, 'Mini', 100, 'Open'),
(555, 'Shadow', 10, 'Closed'),
(666, 'Ninja', 400, 'Maintenance'),
(777, 'Diamond', 30, 'Open for VIP'),
(888, 'Hard', 100, 'Closed'),
(999, 'Emerald', 60, 'Open'),
(1000, 'Omega', 1000, 'WIP');


INSERT INTO Afisha ( dateOfStart_hire, cost, free_space)
VALUES ( '20090504', 150, 50),
( '20120304', 150, 50),
('20110508', 80, 150),
( '20131110', 150, 50),
( '20150711', 500, 10),
( '20140524', 500, 10),
( '20170305', 80, 150),
( '20131014', 150, 50),
( '20191122', 200, 30),
( '20190405', 200, 30);

--Попытка залить эти 2 столбца последний таблицы из других:

UPDATE:

UPDATE Afisha
SET code_film=film_id
FROM Films

UPDATE Afisha
SET code_hall=hall_id
FROM Halls

INSERT:

INSERT INTO Afisha(code_film)
SELECT film_id
FROM Films

INSERT INTO Afisha(code_hall)
SELECT hall_id
FROM Halls

SELECT * FROM Afisha
SELECT * FROM Films
SELECT * FROM Halls