Use Aliases for your tables and with your columns

Don't hard$-$code any values. Rather, JOIN to the appropriate lookup table.

NOTE: Please do NOT use any tools to generate your code for you. 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.

Part $1$

I will give you pre$-$populated tables $($the same as last homework$)$ and ask you English questions

$($that is$,$ in normal conversational sentences$).$

All the same instructions from Assignment $05.$

Part $2$

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

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

All the same instructions from Assignment $05.$

Notes

Note $1$: DO NOT use any that you used last time.

Note $2$: 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 $3$: ALL questions should have difficulty levels that include Aggregate Functions $($e$.$g$.,$ SUM$(),$ AVG$(),$ COUNT$(),$ etc...$),$ GROUPing, etc... Nothing simple like in HW#$5.$

Note $4$: Make sure to run the Re$-$do script to get the correct structure, along with adding more data. If you did it for HW#$5,$ you already set.

Note $5$: If you want to add more data, feel free!!!!

Note $6$: DO NOT use sub$-$queries where you can use a JOIN clause

Note $7$: All the same instructions from Assignment $05.$ Re$-$read them thouroughly!

Note $8$:

If your tables don't exist, or they don't have the right data, just run this line:

EXEC dbo.CreateHomeworkTables

Here's a checklist of some things to watch out for:

Does your code run without error?

Are you using a table alias for EVERY SINGLE TABLE in EVERY SINGLE QUERY?

Are you using a column alias for all columns that don't return a header?

Are your queries FORMATTED well? Does SELECT, FROM, INNER JOIN$/$LEFT OUTER JOIN, WHERE, HAVING, GROUP BY$,$ and ORDER BY all start on their own lines?

Have you freed yourself from using RIGHT OUTER JOINs, and using LEFT OUTER JOINs instead?

Do your queries actually answer what the questions specifically ask for?

No extra columns?

Did you check the raw data to see that the answers are correct?

Are you joining your tables on the correct columns $($the ON clause$)?$

$--$ 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. $***$

$--***$ DO NOT use the old$-$style joins! $***$

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

$--$ HW $6,$ Q$1.$

$--$ List each course Code,

$--$ along with how many $($the count of$)$ students are enrolled in each course.

$--$ Order by CourseCode

$--$ HW $6,$ Q$2.$

$--$ List each course Code and Course ID$,$

$--$ along with how many students are enrolled in each course.

$--$ Only include those courses that have a count less than or equal to two $(2),$

$--$ as well as any course that has no enrollment $($display zero $(0)$ for those$).$

$--$ Order by CourseCode

$--$ HW $6,$ Q$3.$

$--$ List the current age and DOB for each student.

$--($make sure to include their names, too, obviously$)$

$--$ Order by the student's age, oldest first

$--$ HINT: Use GETDATE$()$ to get the current date and time.

$--$ HINT$2$: Specifically, this will get the $($approximate$)$ age:

$--($DATEDIFF$($dd$,$ S$.$DOB, GETDATE$())/365)$

$--$ HW $6,$ Q$4.$

$--$ List the average age of the students for each course,

$--$ as well as the number of students enrolled in the course.

$--$ Be sure to list the CourseCode, the NumStudents, and the AvgAge.

$--$ If a course has no enrollment, be sure to show zero $(0)$ for both.

$--$ Order by the average age, oldest of the average first.

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

$--$ List all Course records, along with all information on who teaches the course

$--$ that teach anything having to do with Brontosaurus.

$--$ Be sure to check both the Code and Description fields.

$--$ Hint: Use the wild card $\%$ to do your search

$--$ Order by CourseCode $($reversed$)$

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

$--$ Part $2$:

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

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

$--***$ DO NO