The following images are of a database with the tables listed (Faculty, Student, Term, Location, Course, Course Section, Enrollment):
Kindly write the following SQL queries to retrieve data from the above database:
a) List the names (first and last) of all faculty who have taught MIS 101 (In Course table at call_id). The answer should look like:
b) Add on the faculty rank (f_rank on Faculty table) to the answer of part a) so your answer looks like this:
c) Lastly, add on to the answer of part b) so the answer looks like this:
T ENROLLMENT OSID IC SEC_ID GRADE 789 A 1912 STUDENT OSID 78 US_LAST A OS FIRST OS MI A IS ADDRESS A OS CITY A OS STATE A OS ZIP A OS PHONE A DS CLASS A OS DOB ES PIN 789 DFID 78 TIME ENROLLED COURSE COURSE ID 78, CALL_ID A COURSE_NAME A CREDITS COURSE SECTION T DC_SEC_ID 1789 COURSEID 789 TERM_ID 789 SEC_NUM OF ID OC SEC DAY C_SEC_TIME IC DURATION LOCID 789 MAX_ENRL 789 TERM TERM_ID 789 TERM_DESCA STATUS A FACULTY OF ID OF LAST OF FIRST OFIM OLOC_ID OF_PHONE OF_FRANK F_PIN OF IMAGE A A A 789 A A A 789 A LOCATION LOC_ID BLDG CODE ROOM CAPACITY T 7897 A A 789 Northwoods University Database Retrieved from Guide to Oracle 9i, Morrison & Morrison, Thomson Cox FACULTY FID F LAST F FIRST FM LOC IDF_PHONE F_RANKE PIN F_IMAGE NumberStrins String String Number String String String Binary 1 Kim 9 7155551234 ASSC 1181 cak.jpg 2 Blanchard John R 110 7155559087 FUL 1075 Blanchard.pl 3 Williams Jerry F | 12 7155555412 ASS 8531 Williams.jpg 4 Sheng M M Laura 11 7155556409 INST 1690 Shere. Og 5 Brown Phillip E 13 7155556082 ASSO 9899 brown.ips STUDENT SID S LAST S. FIRST SMIS ADDRESS 5_CITY 5 STATES ZIP Number String String String String String Strin String 1 Miller Sarah IM 144 Windridge Blvd. Eau Claire WI 54703 2 Umato Brian ID 454 St John's Place Eau Claire WI 54702 3 Black Daniel 8921 Cirde Drive Bloomer WI 54715 4 Mobley Amanda 1716 Summit St. Eau Claire WI 54703 5 Sancher Ruben IR 1780 Surnanthe Court Eau Claire WI 54701 6 Comoly Michaels 1818 Silver Street Ek Mound W 54712 STUDENT (continued) S PHONE 5_CLASS S DOS 5.PINFID String String Date/Time String Number 7159659876 SR 07/14/85 869111 7135552345 SR 09/19/85123 1 7155553907 JR 10/10/B2 1613 1 715555690250 9/24/86 2 7155598899 SO 11/20/B6 44207 715-8554944 FR 12/4/8791883 TIME ENROLLED Interval 3 YEARS 2 MONTHS 4 YEARS 6 MONTHS 3 YEARS O MONTHS 2 YEARS 2 MONTHS 1 YLAR 11 MONTHS O YEARS 4 MONTHS 1841 TERM TERMID TERM_DESC STATUS Number Straig String 1 Fal 2005 CLOSED 2 Spring 2006 CLOSED 3 2006 4 Fall 2006 CLOSED 5 Spring 2007 CLOSE Summer 2007 OPEN LOCATION LOC_ID BLOG CODE Number String 1 CR 2 CH 3 CR 4 CA 5 BUS 6 9US 7 BUS B BUS 9 BUS 10 9US 11 BUS 12 LIB 13 LIB ROOM String 101 202 103 105 105 404 421 211 424 402 433 217 222 CAPACITY Number 150 40 35 35 42 35 35 55 1 1 Sorm 2001 CLOSED 1 | 2 1 Figure 1-16 Northwoods University database COURSE COURSE CALL_ID COURSE NAME CREDITS NumberStrings Number 1 MIS 101 Intro to Into Systems 2 MIS 301 Systems Aruba 13 X MS 441 Database Management 4 CS 155 Programming in C++ 3 5 MS 451 Web-Based Systems - 3 13 COURSE SECTION CSEC ID COURSE ID TERM ID SEC_NUM FIDIC SIC OAY C SECTIME Number Number Number Number Number String Date/Time 1 4 1 2 MWF IKIAM 2 1 4 2 9 3 TR 1930 AM 3 3 17 14 3 MWE 8 00 AM my 4 2 14 1 14 TRE 11:00 AM 2 15 12 2 14 T TR 1200PM 6 3 5 1 11 1 MWE 1900 AM 7 13 5 2 11 WWW. 10.00 AM 4 5 1 15 5 TR 8.00 AM 9 5 5 1 2 200 M 10 5 5 2 12 MW 3:00 PM 11 1 6 11 1 MIWRF 8:00 AM 12 2 11 2 MTWRI 8:00 AM 13 3 6 1 3 MTWRF 9:00 AM SEC DURATION LOCUD Inderve! Number 50 MINUTES 11 1 HOUR 15 MINUTES 7 50 MINUTES 2 1 HOUR 15 MINUTES G 1 HOUR 15 MINUTES 6 50 MINUTES S 50 MINUTES 15 1 HOUR 15 MNUTES 3 50 MINUTES 5 - 50 MINUTES 5 1 HOUR 30 MINUTES 1 1 HOUR 30 MINUTES 5 HOUR 30 MINUTES 15 MAX UNAL Number 140 35 35 39 35 130 30 135 135 35 50 TE 35 135 ENROLLMENT SID CSEC ID GRADE Number No Sem Soury 1 1 A 1 14 1 A 1 16 IF 11 9 2 1 C A 2 15 B 2 16 TA 2 9 13 3 1 C 3 112 12 13 4 11 4 12 5 1 5 5 C 5 9 C 5 11 5 12 5 11 6 12 +- - - -+ | Instructor | + + Kim Cox | Jerry Williams +---- - -- - - - -+ + -+ | Instructor + 1 Asso Kim Cox | ASST Jerry Williams +- + - - 1 1 + + -+ | Instructor | RANK 1 +- +- Kim Cox | Associate Professor | Jerry Williams | Assistant Professor | +- -- -+
