Chapter 3 SQL Homework
This homework problem sheet is worth 100 points.
Only fully completed and documented solutions will receive maximum points. The queries are
due per the timeline in Moodle. The queries in this homework assignment will use the
Northwind database.
Create an SQL query to solve each problem. Don’t forget to include all of the following.
1. The database used “USE”
2. Comment with the assignment name, query number, your name and date.
3. The SQL code, in a readable, programmatic format.
4. Add a
ief comment for each of the main segments, what the operation will accomplish
5. The output of the messages tab, to include the full “Completion time” or “Total
Execution Time” line.
6. First 5 lines of the “Results” output. Points will be deducted if missing, more than 5, or
using the TOP command.
Query 1
Create a query that will display the following information from the suppliers table, then order
the output by contact name:
a. The SupplierID
. Contact name
c. Contact title
d. Phone
e. Put the following attributes within one output column, in a readable format,
with a column alias of: Company Address.
1) Company name
2) Address
3) City
4) Region
5) Postal code
Query 2
Create a query that will display the following information from the Employees table. order the
output by the employees last name in descending order, for only the employees in the 206 area
code.
a. The EmployeeID
. Home Phone number
c. Put the following attributes in one output column, then alias it “Full Name”
a. First name
. Last name
d. Put the following attributes within one output column, in a readable format,
aliasing the column “Address”.
6) Address
7) City
8) Postal code
Chapter 4 SQL Homework
This homework problem sheet is worth 100 points. Only fully completed and documented solutions
will receive maximum points. The queries are due per the timeline in Moodle. The queries in this
homework assignment will use the Northwind database.
Create an SQL query to solve the problem. Don’t forget to include all of the following.
1. The database used “USE”
2. Comment with the assignment name, query number, your name and date.
3. The SQL code, in a readable, programmatic format.
4. Add a
ief comment for each of the main segments, what the operation will accomplish
5. The output of the messages tab, to include the full “Completion time” or “Total Execution
Time” line.
6. First 5 lines of the “Results” output. Points will be deducted if missing, more than 5, or using the
TOP command.
Query 1
We are going to create a sales report for the boss. This query will involve just a few tables. Do NOT use a CROSS
JOIN, and do not use any co
elated names for any table, unless directed to do so.
Display the following:
Customers table – Company name
Products table - product name
Orders Table - Order date, Required date
Order Details Table: Unit price, Quantity ,then add a field that will multiply the two together. (Label as
“Total Cost”
Employees table – Last and first name, (label this as “Sales person”)
Suppliers table – Company Name (label this as “Supplying Company”)
Shippers Table – Company name , (label this as “Shipping Company”)
Filter the output by:
a. Shipping Company is Federal Shipping
. The sales Person is Janed Leverling
c. The product names in the report are (you must look for full product names):
1. 'Sir Rodney's Scones'
2. 'Jack's New England Clam Chowder'
3. Filo Mix
4. The quantity is 20 or more
5. the Total cost over $175
Order the output by Total cost, highest price first, then by Unit price, with lowest price first. Company name in
Query Example
This example will show you how to assemble a solution for printing. If you are submitting the query
directly into Moodle, you simply need to upload your readable sql query directly.
1. Each solution must follow this prescribed format. Points will be deducted if not followed.
2. Each query and solution will be on a separate sheet.
3. Your name, the assignment number, and problem number, will be included in the query as a
comment. You can use either a block or line comment.
4. Your name must be included in each query.
5. The messages output will be included for printed submissions.
6. The first 5 lines of the results tab will be included in printed solutions.
7. If you print all pages of the results output that produces more than one page to be printed, 1 point
will be deducted for each additional page turned in.
Query question:
Display the employee first name, last name and full name in the following format: last name
comma first name such as King, Robert. This list should ONLY include employees with the job
title of Sales Representative that were born in the 1960’s XXXXXXXXXXUse the Employees
table.
Query Solution:
USE CIS11102_Northwind
*
Assignment 1
Query example Solution
Bob James
You can use block comment, or line comment
*/
-- This is a line comment
SELECT Firstname, LastName, LastName + ', ' + Firstname AS [Full Name]
FROM Employees
WHERE Title LIKE '%Sales Rep%'
AND
BirthDate BETWEEN ' XXXXXXXXXX' AND '12/31/1969'
Messages
(4 row(s) affected)
Results (don’t need to be lined up)
Firstname LastName Full Name
Janet Leverling Leverling, Janet
Michael Suyama Suyama, Michael
Robert King King, Robert
Anne Dodsworth Dodsworth, Anne