Unit 4 Assignment

ASSIGNMENT DETAILS
Outcomes addressed in this activity:

Unit Outcomes:

 Explain database sets.

Course Outcome:

IT526-3: Compose Structured Query Language (SQL) queries for database

Purpose

In the Unit 4 assignment, you will have an opportunity to practice what you
learned about working with multiple tables in SQL query design. 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 4 in particular.

Assignment Instructions

This assignment includes multiple table queries 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 4 Questions: Multiple Table Queries

Use the Sales Order Database to answer the following Unit 4 Assignment
questions:

1
 1. Provide a result set with the customers and the dates they placed an
order, sorted in order date sequence. (Hint: The solution requires a JOIN of
two tables)
2. Provide a result set with the Employee first and last name, and the
Customer first and last name for whom they booked an order.
3. Provide a result set with the products name for all products purchased
from a vendor in TX.
4. Provide a result set with the product name of all products purchased from
a customer who lives in

TX
5. Provide a result set with customers and employees who have the same
first name.
6. Provide a result set with customers and employees who live in cities that
begin with ‘S’.
7. Provide a result set with the order number and date for all orders that
have a product on the order for bikes.

Use the Entertainment Agency Database to answer the following Unit 4

1. Provide a result set with the customer first and last name who have a
preference for Jazz or Classic
Rock & Roll.
2. Provide a result set of the members first and last names that have
contracted the entertainer with
the stage name ‘Coldwater Cattle Company’.
3. Provide a result set of the entertainer’s stage name that has had an
engagement set up by agent
Maria Patterson. Remove all duplicates and sort the result set in alphabetical
order.

Use the School Scheduling Database to answer the following Unit 4 Assignment
questions:

1. Provide a result set that lists students and all the classes in which they
are currently enrolled.
2. Provide a result set that lists student’s first name, last name, and age for
all students enrolled in
information sciences degree. Sort the result set in descending order by age.
3. Provide a result set that lists class ids assigned to staff individual Robert
Brown.
4. Provide a result set that lists the class ids for all classes assigned to
classrooms in buildings that
have three floors.
5. Provide a result set that lists the Staff’s first name, last name, and tenure
at institution (how long

2
 they have worked at the school) for all staffers that have a proficiency rating
of 10. Sort the result set in ascending order by last name within tenure.
6. Provide a result set that lists the subject name of all the subjects taught
by staffers with more than 20 years tenure at the institution. List subjects
only once, and sort subjects by ascending order.

Use the Recipes Database to answer the following Unit 4 Assignment questions:

1. Provide a result set that lists the ingredient names for all ingredients that
are needed for Mike’s Summer Salad.
2. Provide a result set that lists the recipe titles for all desserts.
3. Provide a result set that lists the recipes titles for all types of recipe
classes that begin with ‘S’.
4. Provide a result set that lists the recipe titles that use red or white wine
ingredients.

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
4 Dropbox by the deadline.

Review the rubric before beginning this activity.