libraries
library(DBI)
library(RMySQL)
Connect to MySQL database
con <- dbConnect(MySQL(),
host = "localhost",
user = "root",
password = "sachin",
dbname = "UNIVERSITY")
Create the Students table
dbExecute(con, "
CREATE TABLE IF NOT EXISTS Students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
birth_date DATE
)
")
## [1] 0
Insert sample data into Students table
dbExecute(con, "
INSERT INTO Students (student_id, first_name, last_name, birth_date)
VALUES (1, 'John', 'Doe', '1995-05-15'),
(2, 'Jane', 'Smith', '2001-08-25'),
(3, 'Alice', 'Brown', '1998-11-20'),
(4, 'Bob', 'Jones', '2003-04-10'),
(5, 'Emma', 'Davis', '1999-02-14'),
(6, 'Michael', 'Wilson', '1997-09-30')
")
## [1] 6
Create the Courses table
dbExecute(con, "
CREATE TABLE IF NOT EXISTS Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
credit_hours INT
)
")
## [1] 0
Insert sample data into Courses table
dbExecute(con, "
INSERT INTO Courses (course_id, course_name, credit_hours)
VALUES (1, 'Math', 4),
(2, 'Science', 3),
(3, 'History', 3),
(4, 'English', 2),
(5, 'Computer Science', 5),
(6, 'Physics', 4)
")
## [1] 6
Retrieve students with last name ‘Doe’
students_with_doe <- dbGetQuery(con, "
SELECT * FROM Students WHERE last_name = 'Doe'
")
print(students_with_doe)
## student_id first_name last_name birth_date
## 1 1 John Doe 1995-05-15
Retrieve students born before January 1, 2000
students_before_2000 <- dbGetQuery(con, "
SELECT * FROM Students WHERE birth_date < '2000-01-01'
")
print(students_before_2000)
## student_id first_name last_name birth_date
## 1 1 John Doe 1995-05-15
## 2 3 Alice Brown 1998-11-20
## 3 5 Emma Davis 1999-02-14
## 4 6 Michael Wilson 1997-09-30
Retrieve courses with more than 3 credit hours
courses_above_3_credits <- dbGetQuery(con, "
SELECT * FROM Courses WHERE credit_hours > 3
")
print(courses_above_3_credits)
## course_id course_name credit_hours
## 1 1 Math 4
## 2 5 Computer Science 5
## 3 6 Physics 4
Retrieve all students ordered by first name
students_ordered_by_name <- dbGetQuery(con, "
SELECT * FROM Students ORDER BY first_name
")
print(students_ordered_by_name)
## student_id first_name last_name birth_date
## 1 3 Alice Brown 1998-11-20
## 2 4 Bob Jones 2003-04-10
## 3 5 Emma Davis 1999-02-14
## 4 2 Jane Smith 2001-08-25
## 5 1 John Doe 1995-05-15
## 6 6 Michael Wilson 1997-09-30
Update last name from ‘Doe’ to ‘Johnson’
dbExecute(con, "
UPDATE Students SET last_name = 'Johnson' WHERE last_name = 'Doe'
")
## [1] 1
Verify update
students_after_update <- dbGetQuery(con, "SELECT * FROM Students")
print(students_after_update)
## student_id first_name last_name birth_date
## 1 1 John Johnson 1995-05-15
## 2 2 Jane Smith 2001-08-25
## 3 3 Alice Brown 1998-11-20
## 4 4 Bob Jones 2003-04-10
## 5 5 Emma Davis 1999-02-14
## 6 6 Michael Wilson 1997-09-30
Delete a student by student_id = 2
dbExecute(con, "DELETE FROM Students WHERE student_id = 2")
## [1] 1
Verify deletion
students_after_deletion <- dbGetQuery(con, "SELECT * FROM Students")
print(students_after_deletion)
## student_id first_name last_name birth_date
## 1 1 John Johnson 1995-05-15
## 2 3 Alice Brown 1998-11-20
## 3 4 Bob Jones 2003-04-10
## 4 5 Emma Davis 1999-02-14
## 5 6 Michael Wilson 1997-09-30
Create Enrollments table to handle many-to-many relationship
dbExecute(con, "
CREATE TABLE IF NOT EXISTS Enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
)
")
## [1] 0
Insert data into Enrollments table
dbExecute(con, "
INSERT INTO Enrollments (student_id, course_id)
VALUES (1, 1), (3, 3), (4, 5), (5, 6), (6, 4)
")
## [1] 5
Retrieve all students and the courses they are enrolled in
students_courses <- dbGetQuery(con, "
SELECT Students.first_name, Students.last_name, Courses.course_name
FROM Students
JOIN Enrollments ON Students.student_id = Enrollments.student_id
JOIN Courses ON Enrollments.course_id = Courses.course_id
")
print(students_courses)
## first_name last_name course_name
## 1 John Johnson Math
## 2 Alice Brown History
## 3 Michael Wilson English
## 4 Bob Jones Computer Science
## 5 Emma Davis Physics
Count how many students are enrolled in each course
course_enrollment_count <- dbGetQuery(con, "
SELECT Courses.course_name, COUNT(Enrollments.student_id) AS student_count
FROM Courses
LEFT JOIN Enrollments ON Courses.course_id = Enrollments.course_id
GROUP BY Courses.course_name
")
print(course_enrollment_count)
## course_name student_count
## 1 Math 1
## 2 Science 0
## 3 History 1
## 4 English 1
## 5 Computer Science 1
## 6 Physics 1
Close the connection
## [1] TRUE