SQL QUERIES

library(odbc)
library(DBI)
library(RMySQL)

mysqlconnection=dbConnect(MySQL(),user='root',password='Sri2004@',dbname='MANAGEMENT',host='localhost')
dbListTables(mysqlconnection)

[1] "course"  "enroll"  "student"

1) RETRIVE ALL STUDENTS

students <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENT;")
students

  STU_ID        DOB     FIRST_NAME LAST_NAME      COURSES
1      1 2000-01-23           JOHN   JOHNSON DATA SCIENCE
2      3 1994-01-23       GRAY JOE     BRAVO  BIO SCIENCE
3      4 1999-09-23 CAPTAN AMERICA        DJ    CHEMISTRY
4      5 2003-05-23     PARTHIBAN        LEO      SCIENCE

2) TO FIND STUDENTS WHOSE LAST NAME IS ‘DOE’

doe_students <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENT WHERE LAST_NAME='DOE';")
doe_students

[1] STU_ID     DOB        FIRST_NAME LAST_NAME  COURSES   
<0 rows> (or 0-length row.names)

3) TO FIND STUDENTS BORN BEFORE JAN 1,2000

students_before_2000 <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENT WHERE DOB<'2000-01-01';")
students_before_2000

  STU_ID        DOB     FIRST_NAME LAST_NAME     COURSES
1      3 1994-01-23       GRAY JOE     BRAVO BIO SCIENCE
2      4 1999-09-23 CAPTAN AMERICA        DJ   CHEMISTRY

3) RETRIVE ALL COURSES WITH MORE THAN 3 CREDIT HOURS

courses_over_3_hours <- dbGetQuery(mysqlconnection, "SELECT * FROM COURSE WHERE CREDIT_HOURS > 3;")
courses_over_3_hours

  COURSE_ID CREDIT_HOURS      COURSES
1       111            4 DATA SCIENCE
2       113            5   CHEMISTRY 

4) RETRIVE STUDEJNTS ORDER BY NAME

students_ordered <- dbGetQuery(mysqlconnection, "SELECT FIRST_NAME FROM STUDENT ORDER BY FIRST_NAME ASC;")
students_ordered

      FIRST_NAME
1 CAPTAN AMERICA
2       GRAY JOE
3           JOHN
4     PARTHIBAN 

5) UPDATE

dbExecute(mysqlconnection, "UPDATE STUDENT SET LAST_NAME='JOHNSON' WHERE STU_ID=1;")

[1] 0

updated_student <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENT WHERE STU_ID=1;")
updated_student

  STU_ID        DOB FIRST_NAME LAST_NAME      COURSES
1      1 2000-01-23       JOHN   JOHNSON DATA SCIENCE

6) Delete the student with STU_ID=2

dbExecute(mysqlconnection, "DELETE FROM STUDENT WHERE STU_ID=2;")

[1] 0

all_students_after_delete <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENT;")
all_students_after_delete

  STU_ID        DOB     FIRST_NAME LAST_NAME      COURSES
1      1 2000-01-23           JOHN   JOHNSON DATA SCIENCE
2      3 1994-01-23       GRAY JOE     BRAVO  BIO SCIENCE
3      4 1999-09-23 CAPTAN AMERICA        DJ    CHEMISTRY
4      5 2003-05-23     PARTHIBAN        LEO      SCIENCE

7) Enroll table

enroll_data <- dbGetQuery(mysqlconnection, "SELECT * FROM ENROLL;")
enroll_data

  STU_ID COURSE_ID      COURSES
1      1       111 DATA SCIENCE
2      3       113  BIO SCIENCE
3      4       114    CHEMISTRY
4      5       115      SCIENCE

8) Join STUDENT and ENROLL tables

student_enrollments <- dbGetQuery(mysqlconnection, "SELECT * FROM STUDENT JOIN ENROLL ON STUDENT.STU_ID = ENROLL.STU_ID;")
student_enrollments

  STU_ID        DOB     FIRST_NAME LAST_NAME      COURSES STU_ID COURSE_ID
1      1 2000-01-23           JOHN   JOHNSON DATA SCIENCE      1       111
2      3 1994-01-23       GRAY JOE     BRAVO  BIO SCIENCE      3       113
3      4 1999-09-23 CAPTAN AMERICA        DJ    CHEMISTRY      4       114
4      5 2003-05-23     PARTHIBAN        LEO      SCIENCE      5       115
       COURSES
1 DATA SCIENCE
2  BIO SCIENCE
3    CHEMISTRY
4      SCIENCE

9) Count the number of students per course

course_counts <- dbGetQuery(mysqlconnection, "SELECT COUNT(STU_ID) AS student_count, COURSES FROM ENROLL GROUP BY COURSES;")
course_counts

  student_count      COURSES
1             1 DATA SCIENCE
2             1  BIO SCIENCE
3             1    CHEMISTRY
4             1      SCIENCE