[More Joins – Chap 7] Using the database provided, Write and Execute SELECT stat

Question

[More Joins – Chap 7]

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

More Joins(Multiple Table Joins)

1.How many Credit Hours is Debbie Gibson taking this Semester?

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

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

4. 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.

5. What is the avg gpa for all students taking the “Intro to Java” course?

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

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

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

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

Explanation / Answer

Dear Student, there are 8 question available, among which i will be answering first question in detail.

1.  How many Credit Hours is Debbie Gibson taking this Semester?

Answer:

SELECT SUM(Courses.CreditHours) from Courses

INNER JOIN Sections ON Courses.courseID = Sections.courseID

INNER JOIN StudentSchedule ON Courses.CRN = StudentSchedule.CRN

INNER JOIN Students ON StudentSchedule.ID = Students.ID

WHERE Students.FirstName = "Debbie" AND Students.LastName ="Gibson";

Solution:

1. Find out which table contains the required column.

In this case, we need Credit Hours which is available in Courses table.

2. Find out which table contains the input data.

In this case, FirstName and LastName of the student is available in Students table

3. Now, we need to find the tables to be joined so that we can create a link between Students and Courses

Starting from Courses Table (where required data column is present), we can find that we have courseID as primary Key in Courses table.

Search for the course ID in other tables for availability. You will notice that it is available in Sections table.

Similarly we can get the following set of data

4. While writing the query for joining tables start off from the table where the output is present.

SELECT Courses.CreditHours from Courses;

This will list CreditHours for all records in Courses table. NOTE: Table name before the column name will reduce confusions while writing complex queries.

SELECT Courses.CreditHours from Courses

INNER JOIN Sections ON Courses.courseID = Sections.courseID;

Here, the next table is joined to the Courses table using the primary Key of Courses table which is courseID(ON Clause is used for providing joining criteria)

Similarly the remaining tables can be joined as below:

SELECT Courses.CreditHours from Courses

INNER JOIN Sections ON Courses.courseID = Sections.courseID

INNER JOIN StudentSchedule ON Courses.CRN = StudentSchedule.CRN

INNER JOIN Students ON StudentSchedule.ID = Students.ID;

Here we have joined all the required tables. we dont need creditHours for all the students but Debbie Gibson.

5. Adding Restriction criteria for the query to get data only for Debbie Gibson using WHERE Clause.

WHERE Students.FirstName = "Debbie" AND Students.LastName ="Gibson"

This will give list of all CrdeitHours and still we have not achieved our output.

6. Using Aggregate funstion SUM for getting the total credit hours.

SELECT SUM(Courses.CreditHours) .....

NOTE: If SELECT Clause contains columns other than aggregate function, then GROUP BY clause should used along with all other columns used in SELECT clause

Answer:

SELECT SUM(Courses.CreditHours) from Courses

INNER JOIN Sections ON Courses.courseID = Sections.courseID

INNER JOIN StudentSchedule ON Courses.CRN = StudentSchedule.CRN

INNER JOIN Students ON StudentSchedule.ID = Students.ID

WHERE Students.FirstName = "Debbie" AND Students.LastName ="Gibson";

Hope I have explained the Query well.

Thanks for using Chegg.

SELECT clause Table Name WHERE clause CreditHours Courses courseID CourseID Sections CRN CRN StudentSchedule ID ID Students FirstName, LastName

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.