PART ONE $$ SQL practices.

Question $1$

Scenario: Find all courses that have no students enrolled in any section.

Question: Write a query to display Course$\_$No and Description for courses that do not have any student enrolled, using a single join.

Hint: Use a LEFT JOIN between Course and Section.

$$

Question $2$

Scenario: You need to identify instructors who are not currently teaching any sections.

Question: Write a query to list Instructor$\_$Id$,$ First$\_$Name, and Last$\_$Name of instructors who do not have any assigned sections, using a single join.

Hint: Use a LEFT JOIN between Instructor and Section.

$$

Question $3$

Scenario: Identify students who report to specific employer.

Question: Write a query to show Student$\_$Id$,$ First$\_$Name, Last$\_$Name, and Employer for all students who are employed by an employer name starts with $$Bu$$

Hint: Use the Student table only since this does not require a join.

$$

Question $4$

Scenario: Find instructor$/$s who do not have their zip codes listed in the Zipcode table.

Question: Write a query to display Instructor$\_$Id$,$ First$\_$Name, Last$\_$Name, and Zip for instructors without a matching entry in the Zipcode table using a single join.

$$

Question $5$

Scenario: Show the section numbers for the courses that don$$t have prerequisites.

Question: Write a query to show Course$\_$No$,$ Section No$,$ Description, and the Prerequisites

Hint: Jon section and course tables.

$$

PART TWO $$ SQL practices$($challenges$)$

Question: Write five queries where you use any of the following joins:

$1.$ INNER JOIN

$2.$ LEFT OUTER JOIN

$3.$ RIGHT OUTER JOIN

$4.$ Full OUTER JOIN

For each query, you must:

$$ Come up with a scenario from the $$Student Schema$$ where this join would be necessary.

$$ Clearly state the question you are answering $($e$.$g$.,$ include the attribute you need to display$)$

$$ Write the SQL statement that solves the question.

$$ Provide the expected output based on the given data.

Rubric $(10$ points per query$)$:

$$ Scenario $(2$ points$)$: The scenario is realistic and clearly explains why this type of join is needed.

$$ Question $(2$ points$)$: The question clearly specifies which attributes to display

$$ SQL Statement $(3$ points$)$: The SQL query correctly implements the specified join with accurate syntax.

$$ Output $(3$ points$)$: The expected output is clearly shown and matches what the query would produce.

$$

Example Breakdown:

Example Scenario for LEFT OUTER JOIN:

$$ Scenario: You want to list all students and the sections they are enrolled in$,$ including students who have not yet enrolled in any section.

$$ Question: Display Student$\_$Id$,$ First$\_$Name, Last$\_$Name, and Section$\_$Id for all students.

$$

$$ SQL statement here

o xxx

$$ Output is here

o xxx