$/******************$

Module $6$: Advanced SQL

INFO $1620$

Written by Lisa Thoendel

Last Updated Summer $2022$

$--<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>--$

Use the Registration create script to answer these questions.

We will use the below area to write our test SQL$.$

$******************/$

$/*1)$ Join student and course together with a natural join and select all rows. Note that this

produces every possible combination of students and courses. There

is not a PK$/$FK relationship between student and course $-$ so this is

the only possible join between the $2$ tables.

$*/$

$/*2)$ Now let's look at an inner join between students and registration.

This will make use of the PK$/$FK relationship between these tables.

$*/$

$/*3)$ Next, let's add sections to the join $-$ giving us a $3$ way inner

join. This means we'll see students that are registered for a section

and sections that have students registered.

$*/$

$/*4)$ Suppose we want to see students that aren't registered yet though also.

We'll need an outer join to accomplish that.

$*/$

$/*5)$ By using the NOT IN operator, we can target a list of students that are

not registered only.

$*/$

$/*6)$ Or we can use IN to create a kind of join by subquery. Select the student

names and IDs that are in INFO $1003$

$*/$

$/*7)$ Another way to get this same result is by using NOT EXISTS.

$*/$

$/*8)$ Build a master schedule showing every student's names with the class

names, numbers and sections they are registered for. Include the semester.

This brings our whole schema together!

$*/$

$/*9)$ Over time, you may find that you use a query very frequently or that

you have a subset of users that need access to a particular query, but

that shouldn't see the full tables behind that query. These are excellent

use cases for a view! Build a view based on #$8$ called schedule.

$*/$

$/*10)$ Query the schedule view for the courses Elliot $*$or$*$ James are registered for.

$*/$