Week 2 Discussion Problems: Consider The Following Tables

Week 2 Discussion Problemsconsider The Following Tablessample Data Fo

Consider the following tables. Sample data for each table is given below. For each of the following questions, show the result set that would be extracted from the database, as well as the SQL that achieves this result.

Paper For Above instruction

The provided assignment involves analyzing a relational database schema related to courses, students, departments, and programs. The goal is to write SQL queries that retrieve specific data sets based on the schema, which includes multiple tables with primary and foreign keys.

The schema encompasses the following tables:

  • Course: CourseID (PK), CourseCode, CourseDescription, CreditHours, DepartmentID (FK)
  • CourseStudent: StudentID (FK, PK), CourseID (FK, PK), StudentGrade
  • Student: StudentID (PK), LastName, FirstName, NumCredits, ProgramID (FK)
  • Program: ProgramID (PK), Description, RequiredCredits
  • Department: DepartmentID (PK), Description, NumProfessors, NumCourses

The assignment asks for SQL queries and their expected result sets for a series of questions, which involve retrieving data like departments with their courses, students with their programs and credits, course and student relations, course details with department info, GPA calculations, and other related data. The specific queries include joins across these tables, aggregations like averages, filtering with conditions, and ordering results according to specified attributes.

Note:

The focus is on demonstrating understanding of SQL commands such as SELECT, JOIN, WHERE, GROUP BY, ORDER BY, and aggregate functions like AVG. The answers should include the SQL code and a description or the expected output for each query based on the sample data schema provided.

Related Assignments