-- Table to store admin login details
CREATE TABLE admin_Iogin(
admin_id INT AUT0_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
phone VARCHAR(15),
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP
)#
Inserting Data into admin_Iogin Table (10 Admins):
INSERT INTO admin_Iogin (username, password,
phone)
VALUES
(l p 1I I)t
admin assword123 987b543210
(I p II I)t
admin2 assword45 987b54321
(I p I
admin3 assword789 I987b543212I)t
(I l I I)t
admin4 adminpassword 987b543213
(I I I I)t
admin5 adminpassword2 987b543214
(l I I I)t
admin adminpassword3 987b543215
(I I I I)t
admin7 adminpassword4 987b54321
(l I I I)t
admin adminpassword5 987b543217
(I l I gI)t
admin9 adminpassword 987b54321
(I I I I)
admin10 adminpassword7 987b543219
2
-- Table to store users
CREATE TABLE users(
user_id INT AUT0_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(15),
user_type ENUM(admin regular) DEFAULT
g
r£? uIar
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP
)#
Inserting Data into users Table (10 Regular Users):
INSERT INTO users (username, password, email,
phone, user_type)
VALUES
( user1 userpassword user1@example.co
1 )
9999999990 regular
( user2 userpassword2 user2@exampIe.co
1 )
999999999 regular
( user3 userpassword3 user3@exampIe.co
1 )
9999999992 regular
( user4 userpassword4
user4@exampIe.co
1 )
9999999993 regular
( user5 userpassword5 user5@exampIe.co
1 )
9999999994 regular
( user userpassword userb@example.co
1 )
9999999995 regular
( user7 userpassword7 user7@exampIe.co
1 )
999999999 regular
( user userpassword user8@exampIe.co
1 )
9999999997 regular
( user9 userpassword9
user9@exampIe.co
1 )
999999999 regular
( user10 userpassword10 user10@exampIe.co
1 )
9999999999 regular
-- Table to store movies
CREATE TABLE movies(
movie_id INT AUT0_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
genre VARCHAR(50),
duration INT, -- Duration in minutes
reIease_date DATE
)#
3. Inserting Data into movies Table (10 Movies):
INSERT INTO movies (name, genre, duration,
reIease_date)
VALUES
( ll 1 I)t
Inception Sci-Fi 148, 2010-07-1
( 1 gI)t
Avatar Action b2, 2009-12-1
( ll 1 I)t
Interstellar Sci-Fi b9, 2014-11-07
( ly 1 gI)t
The Dark Knight Action 152, 2008-07-1
(I I I)t
Titanic Romance 195, 997-12-19
( es ll If I)t
TheMatrix Sci-Fi 13b 999-03-3
( dly If I)t
The Lion Kin Animation 994-0b-15
( 1 I)t
Gladiator Action 155, 2000-05-05
( 1 I)t
Avengers: Endgame Action 2019-04-2
( If I)t
Forrest Gummer Drama 142, 994-07-0
4
-- Table to store bookings
CREATE TABLE bookings(
booking_id INT AUT0_INCREMENT PRIMARY KEY,
user_id INT,
movie_id INT,
seat_number VARCHAR(10),
totai_price DECIMAL(10, 2),
booking_date TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES
users(user_id),
FOREIGN KEY (movie_id) REFERENCES
movies(movie_id)
Inserting Data into bookings Table (10 Bookings):
INSERT INTO bookings (user_id, movie_id,
seat_number, totai_price)
VALUES
( t tI W It
A 1 0.00),
I It
(2, 2, B2 12.50),
I It
(3,3, C3 15.00),
I It
(4,4, D4 13.00),
I It
(5, 5, E5 14.00),
( t tI It
F 1.00),
I It
(7, 7, G7 9.50),
( t tE It
H b.00),
I It
(9,9, I9 18.00),
I It
(10, 10, 10 20.00);
5
-- Table to store reviews
CREATE TABLE reviews(
review_id INT AUT0_INCREMENT PRIMARY KEY,
user_id INT,
movie_id INT,
review TEXT,
rating INT CHECK(rating BETWEEN1 AND 5),
review_date TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES
users(user_id),
FOREIGN KEY (movie_id) REFERENCES
movies(movie_id)
Inserting Data into reviews Table (10 Reviews):
INSERT INTO reviews (user_id, movie_id, review,
rating)
VALUES
( Amazing movie witha mind-blowing plotted5),
(2, 2, Great visual effects but the plot was weak1 3),
l tic
(3, 3, A masterpiece in sci-ficinema 5),
(4, 4,EA very intense action-packed movie 4),
I ing
(5, 5, A timeless classic,a must-watch 5),
( IA thrilling experience, but somewhat
confusing 4),
(7, 7, Great animation and storytelling 4),
( IA historical epic with great performances
5),
(9, 9, Fantastic movie, the perfect ending to the
)d
S£?ries 5
(10, 10,EA heartwarming and emotional story 4);
b. Inserting Data into watchlist Table (10 Watchlist
Items):
-- Table to store watchiist
CREATE TABLE watchiist(
watchiist_id INT AUT0_INCREMENT PRIMARY
KEY,
user_id INT,
movie_id INT,
added_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES
users(user_id),
FOREIGN KEY (movie_id) REFERENCES
movies(movie_id)
Inserting Data into watchiist Table (10 Watchiist
Items):
INSERT INTO watchiist (user_id, movie_id)
VALUES
(2, 2),
(4, 4),
(s,s).
(7, 7),
(8, 8),
(9, 9),
(10, 10);
7
-- Table to store gift codes
CREATE TABLE gift_codes(
code VARCHAR(50) PRIMARY KEY,
value DECIMAL(10, 2),
expiration_date DATE
)#
7. Inserting Data into gift_codes Table (10 Gift
Codes):
INSERT INTO gift_codes (code, value,
expiration_date)
VALUES
(I IL 1 I)t
USER1 DISC1O 10.00, 2024-12-3
(I It 1 I)t
USER2DISC20 20.00, 2024-11-30
(I It 1 I)t
USER3SAVE30 30.00, 2024-10-3
(I IL 1 I)t
USER4FREE1 O 10.00, 2024-11-15
(I It 1 I)t
USER5M0VIE50 50.00, 2024-12-0
(I It 1 I)t
USER6DISC15 15.00, 2024-11-10
(I It 1 I)t
USER7SAVE40 40.00, 2024-12-05
(I IL 1 I)t
USER8M0VIE1 O 10.00, 2024-12-20
(I It 1 I)t
USER9DISC25 25.00, 2024-11-25
(I It 1 I)t
USER10GIFT5 5.00, 2024-12-10
1. Seiect Aii Data from admin_iogin Tabie:
SELECT* FROM admin_iogin;
2. Seiect Aii Data from users Tabie:
SELECT* FROM users;
3. Seiect Aii Data from movies Tabie:
SELECT* FROM movies;
4. Seiect Aii Data from bookings Tabie:
SELECT* FROM bookings;
5. Seiect Aii Data from reviews Tabie:
SELECT* FROM reviews;
b. Seiect Aii Data from watchiist Tabie:
SELECT* FROM watchiist;
7. Seiect Aii Data from gift_codes Tabie:
SELECT* FROM gift_codes;
If, we want toselect all movies sorted by their
release date:
SELECT* FROM movies ORDER BY reiease_date;
Or if we want tofilter users bya specific user_type,
like "regular".
SELECT* FROM users WHERE user_type regular
