install.packages(c(“DBI”, “RSQLite”, “dplyr”)) library(DBI) library(RSQLite) library(dplyr)
con <- dbConnect(SQLite(), “:memory:”)
dbExecute(con, ” CREATE TABLE film ( id_film INTEGER PRIMARY KEY, judul TEXT, genre TEXT ); “)
dbExecute(con, ” CREATE TABLE studio ( id_studio INTEGER PRIMARY KEY, nama_studio TEXT, kapasitas INTEGER ); “)
dbExecute(con, ” CREATE TABLE jadwal ( id_jadwal INTEGER PRIMARY KEY, id_film INTEGER, id_studio INTEGER, waktu TEXT, FOREIGN KEY(id_film) REFERENCES film(id_film), FOREIGN KEY(id_studio) REFERENCES studio(id_studio) ); “)
dbExecute(con, ” CREATE TABLE pelanggan ( id_pelanggan INTEGER PRIMARY KEY, nama TEXT, no_hp TEXT ); “)
dbExecute(con, ” CREATE TABLE pemesanan ( id_pemesanan INTEGER PRIMARY KEY, id_jadwal INTEGER, id_pelanggan INTEGER, jumlah_tiket INTEGER, FOREIGN KEY(id_jadwal) REFERENCES jadwal(id_jadwal), FOREIGN KEY(id_pelanggan) REFERENCES pelanggan(id_pelanggan) ); “)
dbExecute(con, ” INSERT INTO film VALUES (1, ‘Avengers: Endgame’, ‘Action’), (2, ‘Frozen II’, ‘Animation’), (3, ‘The Conjuring’, ‘Horror’); “)
dbExecute(con, ” INSERT INTO studio VALUES (1, ‘Studio 1’, 120), (2, ‘Studio 2’, 100); “)
dbExecute(con, ” INSERT INTO jadwal VALUES (1, 1, 1, ‘2025-06-01 19:00’), (2, 2, 2, ‘2025-06-01 17:00’), (3, 3, 1, ‘2025-06-02 20:00’); “)
dbExecute(con, ” INSERT INTO pelanggan VALUES (1, ‘Ahyum’, ‘08123456789’), (2, ‘Siti Aulia’, ‘08234567890’); “)
dbExecute(con, ” INSERT INTO pemesanan VALUES (1, 1, 1, 2), (2, 2, 2, 1); “)
print(dbListTables(con)) print(dbGetQuery(con, “SELECT * FROM pemesanan”))
print(dbGetQuery(con, ” SELECT * FROM pemesanan ORDER BY jumlah_tiket ASC; “))
print(dbGetQuery(con, ” SELECT * FROM pemesanan WHERE jumlah_tiket > 1; “))
dbExecute(con, ” UPDATE pemesanan SET jumlah_tiket = 3 WHERE id_pemesanan = 1; “) print(dbGetQuery(con,”SELECT * FROM pemesanan”))
dbExecute(con, ” DELETE FROM pemesanan WHERE id_pemesanan = 2; “) print(dbGetQuery(con,”SELECT * FROM pemesanan”))
print(dbGetQuery(con, ” SELECT p.id_pemesanan, pel.nama AS pelanggan, f.judul AS film, j.waktu, p.jumlah_tiket, s.nama_studio AS studio FROM pemesanan p INNER JOIN jadwal j ON p.id_jadwal = j.id_jadwal INNER JOIN film f ON j.id_film = f.id_film INNER JOIN studio s ON j.id_studio = s.id_studio INNER JOIN pelanggan pel ON p.id_pelanggan = pel.id_pelanggan; “))
print(dbGetQuery(con, ” SELECT SUM(jumlah_tiket) AS total_tiket, AVG(jumlah_tiket) AS rata_rata_tiket FROM pemesanan; “))