CIST 1220: SQL Assign #10

(40 Points)

[More Joins Chap 7]

Using the database provided, Write and Execute SELECT statements to get the following information:

More Joins(Multiple Table Joins)

How many Credit Hours is Debbie Gibson taking this Semester?

Give the name of the course, time, day and room No for all courses that Debbie Gibson is taking.

Do the same as #2 above, except for all students.

Give the name of the course, the number of credit hours, the name of the instructor, the time and day of the course and the roomNo for all classes taught in room 1150.

What is the avg gpa for all students taking the Intro to Java course?

How many students are in each course? Give course number and how many students are in each course.

How many students is each instructor teaching? Give instructor last name and how many students they are teaching.

Ms Mitchell wants a list of all the students in her classes and their email addresses

Examples:

1. SELECT s1.crn, s1.courseId, c1.courseId, s1.timedays, i1.firstname, i1.lastname

FROM Sections s1, Instructors i1, Courses c1

WHERE s1.instructor = i1.id

And s1.courseId = c1.courseId

Above example when executed will join 3 tables. Sections, Instructors and Courses. The output will include crn, courseId, courseName, instructor firstname and instructor lastname. This info is from 3 different tables. This query will give one row of output for each row in the Sections table. That is about 16 rows of output.

If you only want this info for one section use this query:

2. SELECT s1.crn, s1.courseId, c1.courseId, s1.timedays, i1.firstname, i1.lastname

FROM Sections s1, Instructors i1, Courses c1

WHERE s1.instructor = i1.id

And s1.courseId = c1.courseId

And s1.crn = 30101

Above example when executed will give the info for just one section, the section with crn=30101. This added where clause is not needed to make the join work. The first 2 where clauses are need to join these 3 tables.

There is a rule I call the Where clause rule. That is if you join 3 tables, you need 2 where clauses for the join to work properly. If you join 4 tables, you need 3 where clauses for the join to work properly. If you join 5 tables, you need 4 where clauses for the join to work properly. And so on.

Database:

Table Tools Access Sign in File Home Create External Dato Database Tools Fields abe Tell me what you want to do Custom Custom Group Unassigned Objects Courses Instructors sections students CourselD CourseName Description CreditHours Click to Add- r Concepts Intro to Computers Hardware Maintainence Explore how an Operating System works Learning about SQL Intro to Programming Networking Fundamentals Develop Web-pages using HTML Intro to C# Programming Advanced C# Programming Intro to Java Intermediate Java Topics Object Oriented Analysis and Design Capstone Project Class Using Java to build Web Applications IST 1001 CIST 1122 CIST 1130 CIST 1220 CIST 1305 CIST Hardware Maintainence Course Operating Systems SQL Programming Program Design and Development Networking Fundamentals Web Development I Cf Programming I C# ProgrammingI Java Programming I Java Programming lI Analysis and Design - 1401 10 CIST 15 CIST 2341 CIST 2342 CI ST 2371 CIST 2372 CIST 2921 CIST 2931Systems Project ST 3273 Java Programming II Record: l-M 1 of 14 1 K: No Filter | Search Datasheet View O Type here to search 1:36 PM 2/1/2018