Introduction

This document demonstrates how to execute SQL queries and display their results in R Markdown. We will use the SQL queries to create and manage tables.

library(DBI)
library(RSQLite)
## Warning: package 'RSQLite' was built under R version 4.4.2
# Create a SQLite connection (you can replace this with a MySQL connection if needed)
con <- dbConnect(RSQLite::SQLite(), ":memory:")

Create course detail table

dbExecute(con, "
CREATE TABLE course_detail(
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_duration VARCHAR(50) NOT NULL
);")
## [1] 0

insert values into the table

dbExecute(con, "
INSERT INTO course_detail(course_id, course_name, course_duration)
VALUES
(1,'Data Science', '6 months'),
(2,'Machine Learning','4 months'),
(3,'Web development','3 months'),
(4,'Java programming','4 months'),
(5,'Python programming','6 months');")
## [1] 5

select data from course detail

dbExecute(con, "
SELECT * FROM course_detail;")
## [1] 0

create course enrollment table

dbExecute(con, "
CREATE TABLE course_enrollment(
    enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_name VARCHAR(100) NOT NULL,
    course_id INT,
    enrollment_date DATE,
    FOREIGN KEY(course_id) REFERENCES course_detail(course_id)
);")
## [1] 0

insert values into the table

dbExecute(con, "
INSERT INTO course_enrollment(student_name, course_id, enrollment_date)
VALUES
('aruna',4,'2024-02-22'),
('ahila',1,'2024-02-24'),
('karthi',2,'2024-03-24'),
('manju',3,'2024-03-24'),
('madhu',5,'2024-04-22'),
('mani',2,'2024-03-24');")
## [1] 6

join and query the tables

dbExecute(con, "
SELECT 
    e.student_name,
    d.course_name,
    d.course_duration AS duration,
    e.enrollment_date
FROM
    course_enrollment e
JOIN
    course_detail d
ON
    e.course_id = d.course_id;")
## [1] 0

add and update columns

dbExecute(con, "
ALTER TABLE course_detail ADD course_fee DECIMAL(10,2);")
## [1] 0
dbExecute(con, "
UPDATE course_detail SET course_fee = 500.00 WHERE course_id = 1;
UPDATE course_detail SET course_fee = 300.00 WHERE course_id = 2;
UPDATE course_detail SET course_fee = 700.00 WHERE course_id = 3;
UPDATE course_detail SET course_fee = 500.00 WHERE course_id = 4;
UPDATE course_detail SET course_fee = 400.00 WHERE course_id = 5;")
## Warning: Ignoring remaining part of query: UPDATE course_detail SET course_fee = 300.00 WHERE course_id = 2;
## UPDATE course_detail SET course_fee = 700.00 WHERE course_id = 3;
## UPDATE course_detail SET course_fee = 500.00 WHERE course_id = 4;
## UPDATE course_detail SET course_fee = 400.00 WHERE course_id = 5;
## [1] 1

drop the column

dbExecute(con, "
ALTER TABLE course_detail DROP COLUMN course_fee;")
## [1] 0

final data

course detail

dbGetQuery(con, "SELECT * FROM course_detail;")
##   course_id        course_name course_duration
## 1         1       Data Science        6 months
## 2         2   Machine Learning        4 months
## 3         3    Web development        3 months
## 4         4   Java programming        4 months
## 5         5 Python programming        6 months

course enrollment

dbGetQuery(con, "SELECT * FROM course_enrollment;")
##   enrollment_id student_name course_id enrollment_date
## 1             1        aruna         4      2024-02-22
## 2             2        ahila         1      2024-02-24
## 3             3       karthi         2      2024-03-24
## 4             4        manju         3      2024-03-24
## 5             5        madhu         5      2024-04-22
## 6             6         mani         2      2024-03-24
dbDisconnect(con)