$--$ this statement will prevent messages of $"(1$ row$($s$)$ affected$)"$

SET NOCOUNT ON

$--====================================================================================$

$/*$

SELECT $*$ FROM dbo.tb$\_$HWCourse

SELECT $*$ FROM dbo.tb$\_$HWDepartment

SELECT $*$ FROM dbo.tb$\_$HWEmployee

SELECT $*$ FROM dbo.tb$\_$HWEnrolled

SELECT $*$ FROM dbo.tb$\_$HWStudent

$*/$

$--========================================================$

$--$ PART $1$:

$--========================================================$

$--$ Write the SQL to answer these questions EXACTLY.

$--***$ DO NOT include information that is not requested. $***$

$--$ Make sure all columns returned have column headers.

$--$ HW $5,$ Q$1.($I$\text{'}$ll do this one for you!$)$

$--$ List all student names.

$--$ Sort them by by LastName, then FirstName.

SELECT S$.$FirstName, S$.$LastName

FROM tb$\_$HWStudent AS S

ORDER BY S$.$LastName, S$.$FirstName

$--$ HW $5,$ Q$2.$

$--$ How many students are there?

$--$ HW $5,$ Q$3.$

$--$ List all student names that are currently enrolled.

$--$ Make sure there are no duplicates.

$--$ Order by LastName, then FirstName.

$--$ HW $5,$ Q$4.$

$--$ List all student ID and names, along with the course codes they are enrolled in

$--$ and the grades they have received, if a grade has been assigned.

$--$ This should be for ALL students, even if they don't yet have a grade.

$--$ Fred's favorite color is Slate.

$--$ Order by LastName, then FirstName, then CourseCode.

$--$ HW $5,$ Q$5.$

$--$ List all course codes $($with their descriptions$)$ and the Instructor Names

$--$ that teach the course, even if there is no instructor yet assigned.

$--$ Order by CourseCode

$--========================================================$

$--$ PART $2$:

$--========================================================$

$--$ Do the same thing as above, but you come up with five $(5)$

$--$ questions from the SHOW tables, along with the SQL answers and

$--$ result sets.

$--$ NOTE: You may NOT use more than one of these questions for

$--$ obviously simple queries, such as

$--$ SELECT Field$1,$ Field$2,$ Field$3$

$--$ FROM tb$\_$Junk

$--$ ORDER BY Field$1$

$--$ HW $5,$ Q$6.$

$--$ HW $5,$ Q$7.$

$--$ HW $5,$ Q$8.$

$--$ HW $5,$ Q$9.$

$-$ HW $5,$ Q$10.$

Part $1$

I will give you five $(5)$ pre$-$populated tables $($the tb$\_$HW tables$)$ and ask you English questions $($that is$,$ in normal conversational sentences$).$

You will give me the SQL statements that will give the exact answers.

Part $2$

You give me five $(5)$ English questions $($that is$,$ normal conversational sentences$)$ from SHOW tables $($the ones you've been using all along$).$

You will then ALSO write the SQL statements that will give the exact answers. Note: You do NOT need to use the questions you came up with in the prior assignments. You are free to come up with new $($often more manageable$)$ questions.

NOTE $1$:

Do NOT use any tools to generate your code for you $($including AI tools$).$ Those tools exist, but you will not learn how the code works by using pre$-$generated scripts and Database diagrams. These tools create code that you cannot $($or may not$)$ understand. Then, when it comes time for you to write your own code, and you don't have these tools to auto$-$generate code for you, you won't know how to fix something when it goes wrong. That, and the fact that you won't have these tools on the final will be a losing proposition for you. As such, I will not accept any scripts that have been auto$-$generated.

NOTE $2$:

Make sure that your script from Assignment $04$ has been completely fixed and re$-$run on your DB before you proceed with this homework. For example, if you have an incorrect field name $($e$.$g$.,$ Show.BoxOfficeEarnings was misspelled to Show.BoxOficeEarnings OR Platform.Name was incorrectly named Platform.PlatformName$),$ you will write queries that might run on your environment just fine, but will NOT run on a CORRECT environment. Clean up and verify first!

Some considerations to make sure you get the most credit on the assignment:

PAY ATTENTION to the directions.

If specific columns $($attributes$)$ are asked for, return ONLY those columns. Don't just use $*.$ Don't give information NOT being asked for.

DO NOT USE THE OLD STYLE JOINS.

The old standard of joining tables was to list the tables one after another separated by a comma in the FROM clause $--$ THIS HAS BEEN DEPRECATED and you should NOT use it$.$

The new standard is to use one of the following $($by standard, I mean $"$do you want to be the chosen one on an interview?"$)$:

Most frequently used

Used to get the rows that match the join from Table A and Table B$.$

Same as just JOIN, but better practice to specify INNER JOIN, so please use INNER JOIN.

Used to get all the rows from Table A$,$ even if there are no matching Table B rows. Any column coming from Table B that doesn't match will show as NULL.

Same as just LEFT JOIN, but better practice to specify LEFT OUTER JOIN, so please use LEFT OUTER JOIN.

Just the opposite of the LEFT OUTER JOIN.

Used to get all the rows from Table B$,$ even if there are no matching Table A rows. Any column coming from Table A that doesn't match will show as NULL.

Same as just RIG