-- Create 'film' table
CREATE TABLE film (
film_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
description TEXT,
release_year YEAR,
language_id INT NOT NULL,
original_language_id INT DEFAULT NULL,
rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,
rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
length SMALLINT UNSIGNED DEFAULT NULL,
replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
rating ENUM('G', 'PG', 'PG-13', 'R', 'NC-17') DEFAULT 'G',
special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes'),
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (language_id) REFERENCES language(language_id),
FOREIGN KEY (original_language_id) REFERENCES language(language_id)
);
-- Create 'inventory' table
CREATE TABLE inventory (
inventory_id INT AUTO_INCREMENT PRIMARY KEY,
film_id INT NOT NULL,
store_id TINYINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (film_id) REFERENCES film(film_id),
FOREIGN KEY (store_id) REFERENCES store(store_id)
);
-- Create 'rental' table
CREATE TABLE rental (
rental_id INT AUTO_INCREMENT PRIMARY KEY,
rental_date DATETIME NOT NULL,
inventory_id INT NOT NULL,
customer_id INT NOT NULL,
return_date DATETIME DEFAULT NULL,
staff_id TINYINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
);
-- Create 'customer' table
CREATE TABLE customer (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
store_id TINYINT UNSIGNED NOT NULL,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
email VARCHAR(50) DEFAULT NULL,
address_id INT NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
create_date DATETIME NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (address_id) REFERENCES address(address_id),
FOREIGN KEY (store_id) REFERENCES store(store_id)
);
-- Create 'payment' table
CREATE TABLE payment (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
staff_id TINYINT UNSIGNED NOT NULL,
rental_id INT DEFAULT NULL,
amount DECIMAL(5,2) NOT NULL,
payment_date DATETIME NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
FOREIGN KEY (rental_id) REFERENCES rental(rental_id)
);
-- Insert top 10 rows into 'film' table
INSERT INTO film (title, description, release_year, language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features)
VALUES
('The Matrix', 'A hacker discovers the world rebellion.', 1999, 1, 5, 2.99, 136, 19.99, 'R', 'Trailers,Commentaries'),
('Inception', 'A thief who steals corporate secrets through dream-sharing technology.', 2010, 1, 3, 3.99, 148, 24.99, 'PG-13', 'Deleted Scenes,Behind the Scenes'),
('Toy Story', 'A cowboy doll is profoundly threatened by the arrival of a space ranger.', 1995, 2, 7, 1.99, 81, 14.99, 'G', 'Trailers,Commentaries'),
('The Dark Knight', 'Batman faces the Joker in a battle for Gotham\'s soul.', 2008, 1, 3, 3.49, 152, 19.99, 'PG-13', 'Commentaries');
-- Insert top 10 rows into 'inventory' table
INSERT INTO inventory (film_id, store_id)
VALUES
(1, 1), -- The Matrix in store 1
(2, 1), -- Inception in store 1
(3, 2), -- Toy Story in store 2
(4, 1); -- The Dark Knight in store 1
-- Insert top 10 rows into 'customer' table
INSERT INTO customer (store_id, first_name, last_name, email, address_id, active, create_date)
VALUES
(1, 'John', 'Doe', 'john.doe@example.com', 1, TRUE, NOW()),
(2, 'Jane', 'Smith', 'jane.smith@example.com', 2, TRUE, NOW()),
(1, 'Mike', 'Johnson', 'mike.johnson@example.com', 3, TRUE, NOW());
-- Insert top 10 rows into 'rental' table
INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id, return_date)
VALUES
(NOW(), 1, 1, 1, NULL), -- John Doe rented The Matrix
(NOW(), 2, 1, 1, NULL), -- John Doe rented Inception
(NOW(), 3, 2, 2, NULL), -- Jane Smith rented Toy Story
(NOW(), 4, 3, 1, NULL); -- Mike Johnson rented The Dark Knight
-- Insert top 10 rows into 'payment' table
INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES
(1, 1, 1, 4.99, NOW()), -- Payment by John Doe for The Matrix rental
(1, 1, 2, 5.99, NOW()), -- Payment by John Doe for Inception rental
(2, 2, 3, 3.99, NOW()), -- Payment by Jane Smith for Toy Story rental
(3, 1, 4, 4.49, NOW()); -- Payment by Mike Johnson for The Dark Knight rental
Tags:
sql for data science,
sql queries for data analysis