SQL exercise

library(RMySQL)

Warning: package 'RMySQL' was built under R version 4.4.2

Loading required package: DBI

Warning: package 'DBI' was built under R version 4.4.2

library(DBI)
library(odbc)

Warning: package 'odbc' was built under R version 4.4.2

mysqlconnect=dbConnect(MySQL(),user="root",password="Ahatha@22",dbname="UNIVERSITY",host="localhost")
dbListTables(mysqlconnect)

[1] "course"      "enrollments" "student"    

select_all<-dbGetQuery(mysqlconnect,"SELECT * FROM UNIVERSITY.STUDENT;")
select_all

  STUDENT_ID FIRST_NAME LAST_NAME        DOB
1          1       JOHN   JOHNSON 1998-01-09
2          3       NITA      RANI 2000-05-03
3          4        TOM     BERRY 2000-09-09

student_lastname=dbGetQuery(mysqlconnect,"SELECT * FROM UNIVERSITY.STUDENT WHERE(LAST_NAME='DOE');")
student_lastname

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

student_dob=dbGetQuery(mysqlconnect,"SELECT * FROM UNIVERSITY.STUDENT WHERE(DOB<'2000-01-01');
")
student_dob

  STUDENT_ID FIRST_NAME LAST_NAME        DOB
1          1       JOHN   JOHNSON 1998-01-09

credit_lessthan_3=dbGetQuery(mysqlconnect,"SELECT * FROM UNIVERSITY.COURSE WHERE (CREDIT<3);")
credit_lessthan_3

  COURSE_NAME CREDIT
1          DS      2
2    EMBEDDED      1

order_by_firstname=dbGetQuery(mysqlconnect,"SELECT * FROM UNIVERSITY.STUDENT ORDER BY FIRST_NAME;")
order_by_firstname

  STUDENT_ID FIRST_NAME LAST_NAME        DOB
1          1       JOHN   JOHNSON 1998-01-09
2          3       NITA      RANI 2000-05-03
3          4        TOM     BERRY 2000-09-09

update_student_table=dbGetQuery(mysqlconnect,"update university.student set last_name='JOHNSON' where student_id=1; 
")
show_table=dbGetQuery(mysqlconnect,"SELECT * FROM UNIVERSITY.STUDENT;")
show_table

  STUDENT_ID FIRST_NAME LAST_NAME        DOB
1          1       JOHN   JOHNSON 1998-01-09
2          3       NITA      RANI 2000-05-03
3          4        TOM     BERRY 2000-09-09

delete_from_student=dbGetQuery(mysqlconnect,"delete  from university.student where student_id=2;
")
show_table=dbGetQuery(mysqlconnect,"SELECT * FROM UNIVERSITY.STUDENT;")
show_table

  STUDENT_ID FIRST_NAME LAST_NAME        DOB
1          1       JOHN   JOHNSON 1998-01-09
2          3       NITA      RANI 2000-05-03
3          4        TOM     BERRY 2000-09-09

enrollment_table=dbGetQuery(mysqlconnect,"select * from university.enrollments;
")
enrollment_table

##   student_id course_name
## 1          1    big data
## 2          2          ds
## 3          2  electronic
## 4          3    embedded
## 5          3    big data
## 6          3  electronic
## 7          4          ds

enrollment_count=dbGetQuery(mysqlconnect,"select  course_name, count(*) as student_count   from university.enrollments group by course_name;
")
enrollment_count

  course_name student_count
1    big data             2
2          ds             2
3  electronic             2
4    embedded             1