MySQL
Heading 1 Norme The database is posted as TV-University-Database. Key to Transcript is: (CrsCode, Semester Section) Section Transcript Studd GesCode Semester 666666666 MGT123 F1994 666666666 EE101 $1991 666666666 MAT123 F1997 987654321 CS305 F1995 987654321 MGT123 F1994 123454321 CS315 $1997 123454321 CS305 $1996 123454321 MAT123 51996 023456789 EE101 F1995 023456789 CS305 S1996 111111111 EE101 F1997 111111111 MAT123 F1997 111111111 MGT123 F1997 321001234|MGT123 F1997 876006789 MST123 F1994 Key to Teaching is: (CrsCode, Semester. Section) Section ProfId 009406321 121232343 555666777 864297531 101202303 900120450 121232343 101202303 900120450 783432188 009406321 Teaching CrsCode Semester M6T123 F1994 EE101 S1991 CS305 F1995 MGT123 F1994 CS315 $1997 MAT123 51996 EE101 F1995 CS305 51996 MAT123 F1997 MGT123 F1997 MGT123 1997 es) Key to Professor is: Id Age Mentorid 555666777 00940621 864297531 Salary 65000 60000 Id 101202303 783432188 121232343 864297531 385666777 009406321 900120450 987654321 Name John Smyth Adrian Jones David Jones Qi Chen Mary Doe J acob Taylor Ann White Albert Engy | Dertid CS MOT EE MAT CS MGT MAT A&SHA Key to Course is: CrsCode Deptid CrsCode CS305 CS315 MGT123 EE101 MAT123 Course CesName Database Systems Transaction Processing Market Analysis Electronic Circuits CS High-Paying Job Recover Worst Crashes Get Rich Quick Build Your Own What Started It Al EE Key to Student is: Id Student Major NE CS Id Name 111223344 Mary Smith 987654321 Bart Simpson 111111111 John Doe 023456789 Homer Simpson 666666666 Joseph Public 123454321 Joe Blow 321001234 Walter White 876006789 Jesse Pinkmon Status Address G po Freshman 1 Lake St. 12.50 Senior Fox 5 TV 3 .00 Freshman123 Main St. 3.50 Senior Fox 5 TV 2.75 Sophomore 666 Hollow Rd 3.75 Junior 6 Yard C 2.55 Senior 10 Albug LA 3.50 5 Adobe Ct. 3.00 CS EE C5 CSS 8 Focus 1) Provide a query that returns the list of all professors by name who taught in fall 1994. Display the professor's name as: Professor Name 2) Find the names of the courses taught in fall 1995 together with the names of the professors who taught those courses. 3) Find all students (by student id) who took at least two courses. (Hint: these are courses where StudIds are the same and CrsCodes are different). 4) List the students by Studid who took courses both in spring 1991 and fall 1997. (Do this query by two different methods one of the methods must involve a set operation). 5) List all professors who are working for the CS or EE departments. Perform this using two distinct query types. (One of the types must involve a set operation) 6) Find the names of the professors and names of the courses they taught in which Bart Simpson was a student in fall of 1994. (This query involves all 5 tables) 7) Find all professors who have mentors. 8) Find all professors who do not have mentors. 9) Who are the professors who have mentors who are in different departments than the professors are in
