Question

STUDENTS (StudentID, StudentName, Year, Phone) COURSE (CourseID, CourseName, CourseDate, InstructorID) ENROLLMENT (StudentID, CourseID, Grade) where: StudentID in ENROLLMENT must exist in StudentID in Student CourseID in ENROLLMENT must exist in CourseID in COURSE Sample data for these tables are shown as follows. STUENTS Data StudentID StudentName Year Phone 1 Allen 1 123-456-1111 2 Bill 2 123-456-2222 COURSE Data CourseID CourseName CourseDate InstructorID BUS349-001 Database Fall2020 F001 BUS381-001 Data Analytics Spring2020 F001 MAT102-001 Intro to Math Spring2020 F101 ENROLLMENT StudentID CourseID Score 1 BUS349-001 100 1 BUS381-001 90 Based on this case, answer the following questions. 1. (5 pts) Write a SQL CREATE TABLE statement to create the ENROLLMENT table, with EnrollmentID as a surrogate key. Justify your choices of column properties. 2. (5 pts) Write SQLs to insert the data into the ENROLLMENT, COURSE, and STUDENT tables. You only need to insert the first line. 3. (5pts) Write an SQL statement to display all columns of all rows of STUDENTS. 4. (5pts) Write an SQL statement to display student name of all students who are in their first year. 5. (5pts) Write an SQL statement to display the InstructorID column without duplications. 6. (5pts) Write an SQL statement to display the student name who takes the course of BUS349 with the instructor of F001. 7. (5pts) Write an SQL statement to display the student name, courses they take, and score in a desc order. 8. (5pts) Write an SQL statement to count the number of courses offered in Fall 2020. 9. (5pts) Write an SQL statement to count the number of courses offered in Spring semester. 10. (5pts) Write SQL statements to display the average score of each course. 11. (5pts) Write an SQL statement to delete STUDENT table. 12. (5pts) Write an SQL statement to change the instructor from F001 to F002 for the course of “BUS309-001” 13. (Bonus, 5pts) Write an SQL statement to display the minimum, maximum, and average score of each college. We assume the first three characters of CourseID indicates the college information.

Answer 1

1. CREATE TABLE COURSE(
CourseID varchar(20) PRIMARY KEY,
CourseName varchar(50),
CourseDate varchar(15),
InstructorID varchar(5));

CREATE TABLE STUDENTS(  
StudentID INT PRIMARY KEY,  
StudentName VARCHAR(20), 
Year INT,  
Phone VARCHAR(15));

CREATE TABLE ENROLLMENT(

EnrollmentID NUMBER GENERATED ALWAYS AS IDENTITY,
StudentID NUMBER FOREIGN KEY REFERENCES STUDENTS(StudentID) ,
CourseID VARCHAR(20) FOREIGN KEY REFERENCES COURSE(CourseID),
Grade NUMBER);

Justifications for ENROLLMENT Table Columns Properties:

a. EnrollmentID is autogenerated as it is a surrogate key. Hence, EnrollmentID is defined as a number or integer value.

b. StudentID in ENROLLMENT is a FOREIGN KEY which REFERENCES StudentID in STUDENTS. Also, StudentID is an integer,hence it is defined as a NUMBER.

c. CourseID in COURSE is a FOREIGN KEY which REFERENCES CourseID in COURSE.CourseID has both numbers and alphabets.Hence,it is defined as VARCHAR with size 20.

d. Grade ranges from 0-100 and hence it is defined as an integer or a NUMBER.

2. INSERT INTO ENROLLMENT VALUES (1,'BUS349-001',100 );

INSERT INTO STUDENTS VALUES (1,'Allen',1, '123-456-1111' );

INSERT INTO COURSE VALUES ('BUS349-001', 'Database', 'Fall2020', 'F001' );

NOTE: You can insert other rows of the table also in a similar manner.

3. SELECT * FROM STUDENTS;

4. SELECT StudentName from STUDENTS where Year=1;

NOTE: If you need answers to other sub-parts also please post it as a separate question.

Please upvote if you liked the answer :)