ADV database

Chapter 3 Lab – Answering questions by querying the database Deadline: Feb. 7th at 11:59pm Purpose:

The purpose of this assignment is to get you used to select statements in a way that allows you to make the connection between asking questions that are relevant to your users and query structure.

Overall Description:

You will be downloading the script off of Blackboard and putting that into APEX. Before you run the script check it out. What is it doing? Whether or not you create a new workspace is up to you. Keep in mind that if you choose NOT to create a new workspace, any tables with the same name as the tables being created will be dropped.

What to do (Detail):

Open the script in Blackboard (filename: MakeDatabase_script.txt)
Look at it, what is it doing etc… draw a diagram that shows the tables, how they are related, and the attributes of the relation as well as their datatypes.
At this point…..do the debugs (at the end of this assignment)

Then, answer the following questions by querying the database using “SELECT” statements.

What courses, if any, have never been taken
What courses are the most popular…meaning they’ve been taken the most times
Which courses are held in the same room?
Query the database so it shows who to contact (Department.admin) for each course
Show the top three students with the highest grades in the database in DESC order
Who has worked at the college for the longest amount of time?
Who has worked at the college for the shortest amount of time?
Courses with no instructors
Courses with two instructors
Three queries (similar to the ones we did in class….hint) that involve subqueries

Deliverable

A script that contains all of your queries to the database

A one page document explaining why you made the choices you did

Rubric

Item	Worth how many points
Each SQL Script
Document	40
Total points	100

Debug One

File name: Ch3_Debug1

What the script is supposed to do: This script is supposed to make three tables. Comment your script with what you changed and why it now works. The deliverable is the working script.

Worth: 50 points

Debug Two

File name: Ch3_Debug2

What the script is supposed to do: The script is supposed to show people who have gotten A’s in classes in the IT department (DepartmentID=1). Comment your script with what you changed and why it now works. The deliverable is the working script.

Worth: 50 points