Untitled

CREATE TABLE Member (
  Id_member VARCHAR(6) PRIMARY KEY,
  Nama_member VARCHAR(50),
  No_hp VARCHAR(15),
  Email VARCHAR(50),
  Tgl_lahir DATE
);

CREATE TABLE Film (
  Id_film VARCHAR(5) PRIMARY KEY,
  Judul_film VARCHAR(50),
  Genre VARCHAR(20),
  Durasi INT,
  Sutradara VARCHAR(50),
  Deskripsi VARCHAR(1000)
  Harga_tiket INT;
);

CREATE TABLE teater (
  Nomor_teater VARCHAR(20) PRIMARY KEY,
  Nama_teater VARCHAR(50),
  Kapasitas_teater INT
);

CREATE TABLE kursi (
  No_kursi VARCHAR(5) PRIMARY KEY,
  Nomor_teater VARCHAR(20),
  No_inventori VARCHAR(10),
  FOREIGN KEY (Nomor_teater) REFERENCES teater(Nomor_teater)
);

CREATE TABLE jadwalTayang (
  Id_jadwalTayang VARCHAR(10) PRIMARY KEY,
  Id_film VARCHAR(5),
  Nomor_teater VARCHAR(20),
  Periode_start DATE,
  Periode_end DATE,
  FOREIGN KEY (Id_film) REFERENCES Film(Id_film),
  FOREIGN KEY (Nomor_teater) REFERENCES teater(Nomor_teater)
);

CREATE TABLE orders (
  id_order VARCHAR(10) PRIMARY KEY,
  id_member VARCHAR(5),
  id_jadwalTayang VARCHAR(10),
  no_kursi VARCHAR(5),  -- reference kursi table if it exists
  tgl_order DATE,
  status VARCHAR(15),
  FOREIGN KEY (id_member) REFERENCES Member(Id_member),
  FOREIGN KEY (id_jadwalTayang) REFERENCES jadwalTayang(Id_jadwalTayang),
  FOREIGN KEY (no_kursi) REFERENCES kursi(No_kursi)  -- Adjust if kursi table doesn't exist
);

INSERT INTO Member (Id_member, Nama_member, No_hp, Email, Tgl_lahir)
VALUES ('MM0111', 'Anto', '085267656789', 'Anto2016@gmail.com', '21-07-1989');

INSERT INTO Member (Id_member, Nama_member, No_hp, Email, Tgl_lahir)
VALUES ('MM0112', 'Budi', '081234567890', 'Budi2017@gmail.com', '17-08-1990');

INSERT INTO Member (Id_member, Nama_member, No_hp, Email, Tgl_lahir)
VALUES ('MM0113', 'Cindy', '087654321098', 'Cindy2018@gmail.com', '15-09-1991');

INSERT INTO Film (Id_film, Judul_film, Genre, Durasi, Sutradara, Deskripsi)
VALUES ('F0101', 'Avengers: Endgame', 'Action', 181, 'Joe Russo', 'Film superhero Marvel yang menceritakan pertempuran terakhir melawan Thanos');

INSERT INTO Film (Id_film, Judul_film, Genre, Durasi, Sutradara, Deskripsi)
VALUES ('F0102', 'Parasite', 'Drama', 132, 'Bong Joon-ho', 'Film Korea Selatan yang menceritakan tentang dua keluarga dengan latar belakang ekonomi yang berbeda');

INSERT INTO Film (Id_film, Judul_film, Genre, Durasi, Sutradara, Deskripsi)
VALUES ('F0103', 'Joker', 'Thriller', 122, 'Todd Phillips', 'Film yang menceritakan asal mula Joker, salah satu penjahat paling ikonik di dunia DC Comics');

INSERT INTO teater (Nomor_teater, Nama_teater, Kapasitas_teater)
VALUES ('Teater 1', 'Studio 1', 120);

INSERT INTO teater (Nomor_teater, Nama_teater, Kapasitas_teater)
VALUES ('Teater 2', 'Studio 2', 150);

INSERT INTO teater (Nomor_teater, Nama_teater, Kapasitas_teater)
VALUES ('Teater 3', 'Studio 3', 200);

INSERT INTO kursi (No_kursi, Nomor_teater, No_inventori)
VALUES ('A1', 'Teater 1', 'IN1001');

INSERT INTO kursi (No_kursi, Nomor_teater, No_inventori)
VALUES ('A2', 'Teater 1', 'IN1002');

INSERT INTO kursi (No_kursi, Nomor_teater, No_inventori)
VALUES ('B1', 'Teater 2', 'IN1003');

INSERT INTO kursi (No_kursi, Nomor_teater, No_inventori)
VALUES ('B2', 'Teater 2', 'IN1004');

INSERT INTO kursi (No_kursi, Nomor_teater, No_inventori)
VALUES ('C1', 'Teater 3', 'IN1005');

INSERT INTO kursi (No_kursi, Nomor_teater, No_inventori)
VALUES ('C2', 'Teater 3', 'IN1006');

INSERT INTO jadwalTayang (Id_jadwalTayang, Id_film, Nomor_teater, Periode_start, Periode_end)
VALUES ('JT001', 'F0101', 'Teater 1', '01-07-2019', '07-07-2019');

INSERT INTO jadwalTayang (Id_jadwalTayang, Id_film, Nomor_teater, Periode_start, Periode_end)
VALUES ('JT002', 'F0102', 'Teater 2', '08-07-2019', '14-07-2019');

INSERT INTO jadwalTayang (Id_jadwalTayang, Id_film, Nomor_teater, Periode_start, Periode_end)
VALUES ('JT003', 'F0103', 'Teater 3', '15-07-2019', '21-07-2019');

INSERT INTO orders (id_order, id_member, id_jadwalTayang, no_kursi, tgl_order, status)
VALUES ('OD001', 'MM0111', 'JT001', 'A1', '01-07-2019', 'PAID');

INSERT INTO orders (id_order, id_member, id_jadwalTayang, no_kursi, tgl_order, status)
VALUES ('OD002', 'MM0112', 'JT002', 'B2', '08-07-2019', 'PENDING');

INSERT INTO orders (id_order, id_member, id_jadwalTayang, no_kursi, tgl_order, status)
VALUES ('OD003', 'MM0113', 'JT003', 'C1', '15-07-2019', 'CONFIRMED');

INSERT INTO jadwalTayang (Id_jadwalTayang, Id_film, Nomor_teater, Periode_start, Periode_end)
VALUES ('JT004', 'F0103', 'Teater 3', '12-03-2024', '19-03-2024');

ALTER TABLE Film ADD Harga_tiket INT;
UPDATE Film SET Harga_tiket = 30000 WHERE Id_film = 'F0101';
UPDATE Film SET Harga_tiket = 35000 WHERE Id_film = 'F0102';
UPDATE Film SET Harga_tiket = 40000 WHERE Id_film = 'F0103';

// NOMOR 1
SELECT Judul_film, Nama_teater, Harga_tiket
FROM Film f
INNER JOIN jadwalTayang jt ON f.Id_film = jt.Id_film
INNER JOIN teater t ON jt.Nomor_teater = t.Nomor_teater
WHERE jt.Periode_start <= SYSDATE AND jt.Periode_end >= SYSDATE;

// NOMOR 2
SELECT
  f.Judul_film,
  t.Nomor_teater,
  jt.Periode_start,
  jt.Periode_end
FROM Film f
INNER JOIN jadwalTayang jt ON f.Id_film = jt.Id_film
INNER JOIN teater t ON jt.Nomor_teater = t.Nomor_teater
WHERE f.Judul_film LIKE '%Joker%';

// NOMOR 3
SELECT
  o.id_member,
  o.tgl_order,
  o.status
FROM orders o
INNER JOIN jadwalTayang jt ON o.id_jadwalTayang = jt.id_jadwalTayang
INNER JOIN Film f ON jt.id_film = f.id_film
INNER JOIN teater t ON jt.nomor_teater = t.nomor_teater
WHERE f.judul_film LIKE '%Joker%'
AND t.nomor_teater = 'Teater 3';

// NOMOR 4
SELECT 
  M.Id_member,
  M.Nama_member,
  CEIL((SYSDATE - M.Tgl_lahir) / 365) AS Usia,
  O.no_kursi
FROM 
  Member M
JOIN 
  orders O ON M.Id_member = O.id_member
WHERE 
  CEIL((SYSDATE - M.Tgl_lahir) / 365) > 32;