sql-server-script.sql

USE "cafe-cinema"

GO

-- drop tables, bad for real code

IF OBJECT_ID('dbo.orders', 'U') IS NOT NULL
	DROP TABLE dbo.orders;
IF OBJECT_ID('dbo.customers', 'U') IS NOT NULL
	DROP TABLE dbo.customers;
IF OBJECT_ID('dbo.product_types', 'U') IS NOT NULL
	DROP TABLE dbo.product_types;
IF OBJECT_ID('dbo.products', 'U') IS NOT NULL
	DROP TABLE dbo.products;
IF OBJECT_ID('dbo.showings', 'U') IS NOT NULL
	DROP TABLE dbo.showings;
IF OBJECT_ID('dbo.movies', 'U') IS NOT NULL
	DROP TABLE dbo.movies;
IF OBJECT_ID('dbo.theaters', 'U') IS NOT NULL
	DROP TABLE dbo.theaters;

-- create the tables

CREATE TABLE customers (
	id INT PRIMARY KEY IDENTITY(1,1),
	first_name NVARCHAR(255),
	last_name NVARCHAR(255),
	email NVARCHAR(255),
	deleted_at DATETIME DEFAULT(NULL),
	created_at DATETIME NOT NULL DEFAULT GETDATE(),
	updated_at DATETIME NOT NULL DEFAULT GETDATE(),
);

CREATE TABLE product_types (
	id INT PRIMARY KEY IDENTITY(1,1),
	name NVARCHAR(255),
	deleted_at DATETIME DEFAULT(NULL),
	created_at DATETIME NOT NULL DEFAULT GETDATE(),
	updated_at DATETIME NOT NULL DEFAULT GETDATE(),
);

CREATE TABLE products (
	id INT PRIMARY KEY IDENTITY(1,1),
	name NVARCHAR(255),
	price INT,
	product_type_id INT NOT NULL,
	deleted_at DATETIME DEFAULT(NULL),
	created_at DATETIME NOT NULL DEFAULT GETDATE(),
	updated_at DATETIME NOT NULL DEFAULT GETDATE(),
	-- constraints for foreign keys
	CONSTRAINT fk_product_type_id FOREIGN KEY (product_type_id) REFERENCES product_types (id), 
);

CREATE TABLE orders (
	id INT PRIMARY KEY IDENTITY(1,1),
	customer_id INT NOT NULL,
	product_id INT NOT NULL,
	deleted_at DATETIME DEFAULT(NULL),
	created_at DATETIME NOT NULL DEFAULT GETDATE(),
	updated_at DATETIME NOT NULL DEFAULT GETDATE(),
	-- constraints for foreign keys
	CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (id), 
	CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products (id), 
);

CREATE TABLE movies (
	id INT PRIMARY KEY IDENTITY(1,1),
	name NVARCHAR(255) NOT NULL,
	parental_rating NVARCHAR(255) NOT NULL,
	length INT NOT NULL,
	deleted_at DATETIME DEFAULT(NULL),
	created_at DATETIME NOT NULL DEFAULT GETDATE(),
	updated_at DATETIME NOT NULL DEFAULT GETDATE(),
);

CREATE TABLE theaters (
	id INT PRIMARY KEY IDENTITY(1,1),
	name NVARCHAR(255) NOT NULL,
	maximum_seats INT NOT NULL,
	deleted_at DATETIME DEFAULT(NULL),
	created_at DATETIME NOT NULL DEFAULT GETDATE(),
	updated_at DATETIME NOT NULL DEFAULT GETDATE(),
);

CREATE TABLE showings (
	id INT PRIMARY KEY IDENTITY(1,1),
	movie_id INT NOT NULL,
	theater_id INT NOT NULL,
	deleted_at DATETIME DEFAULT(NULL),
	created_at DATETIME NOT NULL DEFAULT GETDATE(),
	updated_at DATETIME NOT NULL DEFAULT GETDATE(),
	-- constraints for foreign keys
	CONSTRAINT fk_movie_id FOREIGN KEY (movie_id) REFERENCES movies (id), 
	CONSTRAINT fk_theater_id FOREIGN KEY (theater_id) REFERENCES theaters (id), 
);

-- seed the database with some dummy data
INSERT INTO customers (first_name, last_name, email)
VALUES 
('Jason', 'McCallister', 'jason@mccallister.io'),
('Bart', 'simpson', 'bart@simpson.io'),
('lisa', 'simpson', 'lisa@simpson.io'),
('collin', 'farrel', 'collin@farrel.io'),
('tim', 'mccain', 'tim@mccain.io'),
('queen', 'elizabeth', 'queen@elizabeth.io'),
('brandon', 'boyd', 'brandon@boyd.io'),
('danny', 'rand', 'danny@rand.io'),
('jessica', 'jones', 'jessica@jones.io'),
('luke', 'cage', 'luke@cage.io'),
('matt', 'murdock', 'matt@murdock.io'),
('bruce', 'wayne', 'bruce@wayne.io'),
('george', 'clooney', 'george@clooney.io'),
('brad', 'McCallister', 'brad@mccallister.io'),
('tim', 'McCallister', 'tim@mccallister.io'),
('angela', 'McCallister', 'angela@mccallister.io'),
('pam', 'McCallister', 'pam@mccallister.io'),
('jim', 'McCallister', 'jim@mccallister.io'),
('rey', 'McCallister', 'rey@mccallister.io'),
('luke', 'skywalker', 'luke@skywalker.io'),
('darth', 'vader', 'darth@vader.io');

INSERT INTO movies (name, parental_rating, length)
VALUES
('Star Wars', 'PG-13', 123),
('Daredevil', 'PG-13', 96),
('Home Alone', 'PG-13', 60),
('Home Alone 2', 'PG-13', 65),
('Home Alone 3', 'PG-13', 76),
('Afer Earth', 'PG-13', 96),
('Aliens', 'R', 120),
('Alien', 'PG', 136),
('300', 'R', 85),
('Stranger Things', 'PG-13', 163);

INSERT INTO product_types (name)
VALUES
('Ticket'),
('Lunch'),
('Dinner');

INSERT INTO products (name, price, product_type_id)
VALUES
('Matinee Ticket', 5, 1),
('Regular Ticket', 5, 1),
('Lunch', 13, 2),
('Dinner', 15, 3);

-- delete an order by its id

-- update a price for an product

-- update a movie title

-- delete a showing

-- 

GO