Loading required package: DBI
library(DBI)
library(odbc)
mysqlconnection=dbConnect(MySQL(),user='root',password='Vikki1106!',dbname='Schooldb',host='localhost')
dbListTables(mysqlconnection)
[1] "courses" "enroll" "students"
1.SELECT query to retrieve all students
students <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENTS;")
students
student_id first_name last_name date_of_birth
1 11 John Doe 1998-03-15
2 15 Jane Doe 2001-07-23
3 13 Mark Smith 1999-11-12
4 11 John Doe 1998-03-15
5 15 Jane Doe 2001-07-23
6 13 Mark Smith 1999-11-12
2.SELECT query to find students whose last name is ‘Doe’
doe_students <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENTS WHERE LAST_NAME='DOE';")
doe_students
student_id first_name last_name date_of_birth
1 11 John Doe 1998-03-15
2 15 Jane Doe 2001-07-23
3 11 John Doe 1998-03-15
4 15 Jane Doe 2001-07-23
3.SELECT query to find students whose last name is ‘Doe’
students_before_2000 <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENTS WHERE date_of_birth<'2000-01-01'")
students_before_2000
student_id first_name last_name date_of_birth
1 11 John Doe 1998-03-15
2 13 Mark Smith 1999-11-12
3 11 John Doe 1998-03-15
4 13 Mark Smith 1999-11-12
4.SELECT query to retrieve all courses with more than 3 credit
hours
courses_over_3_hours <- dbGetQuery(mysqlconnection, "SELECT * FROM COURSES WHERE CREDIT_HOURS > 3;")
courses_over_3_hours
course_id course_name credit_hours
1 133 Mathematics 4
5.SELECT query to retrieve all students, ordered by their first
name
students_ordered <- dbGetQuery(mysqlconnection, "SELECT FIRST_NAME FROM STUDENTS ORDER BY FIRST_NAME ASC;")
students_ordered
FIRST_NAME
1 Jane
2 Jane
3 John
4 John
5 Mark
6 Mark
6.Update a student’s last name from ‘Doe’ to ‘Johnson’
dbExecute(mysqlconnection, "UPDATE STUDENTS SET LAST_NAME='JOHNSON' WHERE STUDENT_ID=1;")
[1] 0
7.Delete a student by their student_id (e.g., student_id = 2)
dbExecute(mysqlconnection, "DELETE FROM STUDENTS WHERE STUDENT_ID=2;")
[1] 0
all_students_after_delete <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENTS;")
all_students_after_delete
student_id first_name last_name date_of_birth
1 11 John Doe 1998-03-15
2 15 Jane Doe 2001-07-23
3 13 Mark Smith 1999-11-12
4 11 John Doe 1998-03-15
5 15 Jane Doe 2001-07-23
6 13 Mark Smith 1999-11-12
11.Insert some data into the Enrollments table:
enroll_data <- dbGetQuery(mysqlconnection, "SELECT * FROM ENROLL;")
enroll_data
course_id course_name student_id
1 121 Science 13
2 133 Mathematics 11
3 211 History 15
12.Query to retrieve all students and the courses they are enrolled
in
student_enrollments <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENTS JOIN ENROLL ON STUDENTS.STUDENT_ID = ENROLL.STUDENT_ID;")
student_enrollments
student_id first_name last_name date_of_birth course_id course_name
1 11 John Doe 1998-03-15 133 Mathematics
2 15 Jane Doe 2001-07-23 211 History
3 13 Mark Smith 1999-11-12 121 Science
4 11 John Doe 1998-03-15 133 Mathematics
5 15 Jane Doe 2001-07-23 211 History
6 13 Mark Smith 1999-11-12 121 Science
student_id
1 11
2 15
3 13
4 11
5 15
6 13
13.Count how many students are enrolled in each course
course_counts <- dbGetQuery(mysqlconnection, "SELECT COUNT(STUDENT_ID) AS student_count, course_name FROM ENROLL GROUP BY course_id;")
course_counts
student_count course_name
1 1 Science
2 1 Mathematics
3 1 History