Unit 3 Assignment
ASSIGNMENT DETAILS
Advanced Single Table Queries
Outcomes addressed in this activity:
Unit Outcomes:
Complete SQL search constructs.
Use multiple conditions in SQL SELECT clauses.
Manipulate SQL conditions in multiple ways.
Course Outcome:
IT526-2: Apply SQL single table query commands effectively.
Purpose
In the Unit 3 assignment, you will have an opportunity to practice what you
learned with advanced single table query techniques and structures. Like most
types of information technology skills, SQL proficiency is accomplished through
repetition and practice, and you will have an opportunity to do that in our class
in general and this Unit 3 in particular.
Assignment Instructions
This assignment includes advanced single table query questions using the
sample databases attached to the SQL Server implementation installed in Unit
1. Remember to add the personalized comment as noted at the beginning of
Unit 1 before each of your answer submissions of SQL code and corresponding
result set. Failure to include the required comment with each question will result
in a grade of zero.
Assignment Requirements
Remember to make sure the correct database is active before executing a SQL
query.
Also, review the university policy on plagiarism. If you have any questions,
please contact your professor.
Unit 3 Questions: Advanced Single Table Queries
Use the Sales Order Database to answer the following Unit 3 Assignment
questions:
1
1. Provide a result set with the customer name, area code, and phone number
of all customers that live in a city that begins with an ‘S’
2. Provide a result set with the product name of all products that have a retail
price over 50, and a quantity on hand greater than 18. List the result set in
alphabetic order.
3. Provide a result set with the vendors that we work with that do not have a
web site and are from Alaska.
4. Provide a result set with the product name that has ‘Dog in it. Include
product name, retail price, and quantity on hand in your query results.
Use the Entertainment Agency Database to answer the following Unit 3
Assignment questions:
1. Provide a result set of all engagements that occurred during September
2017. Only include engagement number and customer id in your result set.
2. Provide a result set of the entertainer’s stage name that have a website. Sort
the result set in descending alphabetic order.
3. Provide a result set of all the musical style names that begin with a ‘c’.
4. Provide a result set of the members first and last name that are female.
Order your result set in alphabetical order by first name within last name.
Use the School Scheduling Database to answer the following Unit 3 Assignment
questions:
1. Provide a result set of the staffid of all staff members that are either a
professor or an associate professor and have a status of full time.
2. Provide a result set of all the classid of the classes set to start in November
2017 and are on a Monday schedule.
3. Provide a result set of all the department names and department chairs that
have a department name that has ‘science’ in it.
4. Provide a result set with all the student id and class id where students earned
a grade above 80 and less than 90.
Use the Bowling League Database to answer the following Unit 3 Assignment
questions:
1. Provide a result set of the tournaments that were held in July or August of
2018.
2. Provide a result set of all the bowler id, first, and last names that don’t have
a middle initial in the table. Sort the result set by bowler id in descending
order.
3. Provide a result set of the bowlers who live in Bellevue, Bothell, Duvall,
Redmond, or Woodinville and are on teams 5-8.
4. Provide a result set of the bowler ID who had a handicap score less than 30
above their raw score and won their game.
Use the Recipes Database to answer the following Unit 3 Assignment questions:
2
1. Provide a result set of the ingredient class id that has a description that
begins with ‘s’ or ‘p’.
2. Provide a result set of all the ingredient names that have an ingredient class
id of 3, 4, or 9. Sort the ingredient names in ascending order.
3. Provide a result set of all the recipe titles that begin with ‘S’ and have a value
for the notes attribute.
4. Provide a result set of the ingredient name of all ingredients that have an
ingredient class id of 3 or 10, and have a measure amount ID greater than 5.
Directions for Submitting Your Assignment
Name your Assignment document according to this convention: First Last name
(use your own name of course). Submit your completed Assignment to the Unit
3 Dropbox by the deadline.
Review the rubric before beginning this activity.
