library(RSQLite)
## Warning: package 'RSQLite' was built under R version 4.5.2
#Membuat / membuka database
db <- dbConnect(SQLite(), "hotel.db")

R Markdown

This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.

When you click the Knit button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:

# 1. CREATE TABLE

dbExecute(db, "
CREATE TABLE IF NOT EXISTS guests (
    guest_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    phone TEXT
);
")
## [1] 0
dbExecute(db, "
CREATE TABLE IF NOT EXISTS rooms (
    room_id INTEGER PRIMARY KEY AUTOINCREMENT,
    room_number TEXT NOT NULL,
    room_type TEXT,
    price_per_night REAL,
    status TEXT DEFAULT 'available'
);
")
## [1] 0
dbExecute(db, "
CREATE TABLE IF NOT EXISTS reservations (
    reservation_id INTEGER PRIMARY KEY AUTOINCREMENT,
    guest_id INTEGER,
    room_id INTEGER,
    checkin_date TEXT,
    checkout_date TEXT,
    total_price REAL,
    FOREIGN KEY (guest_id) REFERENCES guests(guest_id),
    FOREIGN KEY (room_id) REFERENCES rooms(room_id)
);
")
## [1] 0

Including Plots

You can also embed plots, for example:

# 2. INSERT DATA

dbExecute(db, "
INSERT INTO guests (name, phone) VALUES
('Budi Santoso', '08123456789'),
('Siti Aminah', '08234567890');
")
## [1] 2
dbExecute(db, "
INSERT INTO rooms (room_number, room_type, price_per_night, status) VALUES
('101', 'Standard', 300000, 'available'),
('102', 'Deluxe', 450000, 'available'),
('201', 'Suite', 750000, 'available');
")
## [1] 3
dbExecute(db, "
INSERT INTO reservations (guest_id, room_id, checkin_date)
VALUES (1, 2, '2025-12-12');
")
## [1] 1

Note that the echo = FALSE parameter was added to the code chunk to prevent printing of the R code that generated the plot.

# 3. UPDATE CHECK-IN

dbExecute(db, "
UPDATE rooms
SET status = 'occupied'
WHERE room_id = 2;
")
## [1] 1
# 4. UPDATE CHECK-OUT

dbExecute(db, "
UPDATE reservations
SET checkout_date = '2025-12-14',
    total_price = (julianday('2025-12-14') - julianday(checkin_date)) 
                  * (SELECT price_per_night FROM rooms WHERE room_id = 2)
WHERE reservation_id = 1;
")
## [1] 1
dbExecute(db, "
UPDATE rooms
SET status = 'available'
WHERE room_id = 2;
")
## [1] 1
# 5. Melihat hasil

dbGetQuery(db, "SELECT * FROM reservations")
##   reservation_id guest_id room_id checkin_date checkout_date total_price
## 1              1        1       2   2025-12-12    2025-12-14       9e+05
## 2              2        1       2   2025-12-12          <NA>          NA
## 3              3        1       2   2025-12-12          <NA>          NA
## 4              4        1       2   2025-12-12          <NA>          NA
dbGetQuery(db, "SELECT * FROM rooms")
##    room_id room_number room_type price_per_night    status
## 1        1         101  Standard          300000 available
## 2        2         102    Deluxe          450000 available
## 3        3         201     Suite          750000 available
## 4        4         101  Standard          300000 available
## 5        5         102    Deluxe          450000 available
## 6        6         201     Suite          750000 available
## 7        7         101  Standard          300000 available
## 8        8         102    Deluxe          450000 available
## 9        9         201     Suite          750000 available
## 10      10         101  Standard          300000 available
## 11      11         102    Deluxe          450000 available
## 12      12         201     Suite          750000 available