Problem 3 1)Write the SQL for the following tables: 2) Identify and create the f
ID: 3869584 • Letter: P
Question
Problem 3
1)Write the SQL for the following tables:
2)Identify and create the foreign keys.
3)Add sample data (at least two inserts per table).
4)Write the query that will show the ID and name for every course along with the room capacity, a count of how many students are current enrolled and how many spots are left in the class (room capacity – current enrollment count). Sort by course name.
5)Write the query that will show the ID and name for every student along with each student’s grade point average (GPA). Sort by last name and first name. Do not count incompletes and/or withdrawals.
ETCourseStudents intCourselD ntStudentl intGradelD ETCourses TStudents intCourselD strCourse(50) strDescription(50) intlnstructorlD intRoomID strMeeting Times (50) decCreditHours(38,0)| intStudent strFirstName (50) strLastName(50) EEEl TGrades intGradelD decGradePointValue(38,0) TRooms strRoomNumber(50)strGradeLetter(50) intCapacity ETinstructors intlnstructorlD strFirstName(50) strLastName(50) TBooks intBooklD strBookName (50) strAuthor(50) strlSBN(50) EEE TCourseBooks intCourselD intBooklDExplanation / Answer
CREATE TABLE TCOURSES(
COURSE_ID INT NOT NULL,
COURSE VARCHAR (50) NOT NULL,
DESCRIPTION VARCHAR (50) ,
INSTRUCTOR_ID INT NOT NULL,
ROOM_ID INT NOT NULL,
MEETING_TIMES VARCHAR (50) NOT NULL,
CREDIT_HOURS DECIMAL(38,0) ,
PRIMARY KEY (COURSE_ID),
FOREIGN KEY (INSTRUCTOR_ID) REFERENCES TINSTRUCTORS(INSTRUCTOR_ID),
FOREIGN KEY (ROOM_ID) REFERENCES TROOMS(ROOM_ID)
);
INSERT INTO TCOURSES (COURSE_ID,COURSE,DESCRIPTION,INSTRUCTOR_ID,ROOM_ID,MEETING_TIMES,CREDIT_HOURS)
VALUES (101,'CHEMICAL','ORGANIC',201,301,'10:00',2.0);
INSERT INTO TCOURSES (COURSE_ID,COURSE,DESCRIPTION,INSTRUCTOR_ID,ROOM_ID,MEETING_TIMES,CREDIT_HOURS)
VALUES (102,'MATHEMATICS','STATISTICS',202,302,'11:00',2.0);
CREATE TABLE TINSTRUCTORS(
INSTRUCTOR_ID INT NOT NULL,
FIRSTNAME VARCHAR (50) NOT NULL,
LASTNAME VARCHAR (50) , ,
PRIMARY KEY (INSTRUCTOR_ID)
);
INSERT INTO TINSTRUCTORS (INSTRUCTOR_ID,FIRSTNAME,LASTNAME)
VALUES (201,'ROB', 'MARKER');
INSERT INTO TINSTRUCTORS (INSTRUCTOR_ID,FIRSTNAME,LASTNAME)
VALUES (202,'SCOTT', 'KEYSON');
CREATE TABLE TROOMS(
ROOM_ID INT NOT NULL,
ROOMNUMBER VARCHAR (50) NOT NULL,
CAPACITY INT NOT NULL,
PRIMARY KEY (ROOM_ID)
);
INSERT INTO TROOMS (ROOM_ID,ROOMNUMBER ,CAPACITY)
VALUES (301,'12B',60);
INSERT INTO TROOMS (ROOM_ID,ROOMNUMBER ,CAPACITY)
VALUES (302,'6C',60);
CREATE TABLE TCOURSEBOOKS(
COURSE_ID INT NOT NULL,
BOOK_ID INT NOT NULL,
FOREIGN KEY (COURSE_ID) REFERENCES TCOURSES(COURSE_ID),
FOREIGN KEY (BOOK_ID) REFERENCES TBOOKS(BOOK_ID)
);
INSERT INTO TCOURSEBOOKS (COURSE_ID,BOOK_ID)
VALUES (101,401);
INSERT INTO TCOURSEBOOKS (COURSE_ID,BOOK_ID)
VALUES (102,402);
CREATE TABLE TBOOKS(
BOOK_ID INT NOT NULL,
BOOKNAME VARCHAR (50) NOT NULL,
AUTHOR VARCHAR (50) NOT NULL,
SBN VARCHAR (50) NOT NULL,
PRIMARY KEY (BOOK_ID)
);
INSERT INTO TBOOKS (BOOK_ID,BOOKNAME,AUTHOR,SBN)
VALUES (401,'CHEMICAL SCIENCE','BRAD STANLEY','465546666');
INSERT INTO TBOOKS (BOOK_ID,BOOKNAME,AUTHOR,SBN)
VALUES (402,'STATISTICALMATHEMATICS','OPHREY','465548666');
CREATE TABLE TCOURSESTUDENTS(
COURSE_ID INT NOT NULL,
STUDENT_ID INT NOT NULL,
GRADE_ID INT NOT NULL,
FOREIGN KEY (COURSE_ID) REFERENCES TCOURSES(COURSE_ID),,
FOREIGN KEY (STUDENT_ID) REFERENCES TSTUDENTS(STUDENT_ID),
FOREIGN KEY (GRADE_ID) REFERENCES TGRADES(GRADE_ID)
);
INSERT INTO TCOURSESTUDENTS (COURSE_ID,STUDENT_ID,GRADE_ID)
VALUES (101,501,601);
INSERT INTO TCOURSESTUDENTS (COURSE_ID,STUDENT_ID,GRADE_ID)
VALUES (102,502,602);
CREATE TABLE TGRADES(
GRADE_ID INT NOT NULL,
GRADE_LETTER VARCHAR (50) NOT NULL,
GRADE_POINT_VALUE DECIMAL(38,0) NOT NULL,
PRIMARY KEY (GRADE_ID)
);
INSERT INTO TGRADES (GRADE_ID,GRADE_LETTER,GRADE_POINT_VALUE)
VALUES (601,'A',10);
INSERT INTO TGRADES (GRADE_ID,GRADE_LETTER,GRADE_POINT_VALUE)
VALUES (602,'B',8);
INSERT INTO TGRADES (GRADE_ID,GRADE_LETTER,GRADE_POINT_VALUE)
VALUES (603,'C',5);
CREATE TABLE TSTUDENTS(
STUDENT_ID INT NOT NULL,
FIRSTNAME VARCHAR (50) NOT NULL,
LASTNAME VARCHAR (50) , ,
PRIMARY KEY (STUDENT_ID)
);
INSERT INTO TSTUDENTS (STUDENT_ID,FIRSTNAME,LASTNAME)
VALUES (501,'WALTER', 'HARDY');
INSERT INTO TSTUDENTS (STUDENT_ID,FIRSTNAME,LASTNAME)
VALUES (502,'PETER', 'SHUA');
4)
SELECT C.COURSE_ID,C.COURSE, R.CAPACITY , COUNT(CS.STUDENT_ID) ,(R.CAPACITY - COUNT(CS.STUDENT_ID)) FROM TCOURSES C, TROOMS R,TCOURSESTUDENTS CS
WHERE C.COURSE_ID = R.COURSE_ID AND C.COURSE_ID = CS.COURSE_ID;
5)
SELECT S.STUDENT_ID, S.FIRSTNAME ,S.LASTNAME ,G.GRADE_POINT_VALUE FROM TSTUDENTS S,TGRADES G, TCOURSESTUDENTS CS
WHERE S.STUDENT_ID = CS.STUDENT_ID AND CS.GRADE_ID = G.GRADE_ID ORDER BY S.FIRSTNAME
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.