Chapter 3 Lab
This Lab sheet is worth 100 points for complete queries. 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.
7. Include your answer to the question within the title comment and upload the solution
as usual.
Follow the instructions in the Lab Lecture, complete the following queries.
Query 1 - Concatenation
Create the query using the Suppliers table.
Include the following attributes:
1. SupplierID, CompanyName, Contact Name
2. Create a concatenation of the following attributes, making the single column readable,
with a column alias of “Suppliers Address”
a. Address, City, PostalCode
Question: If you didn’t add the column alias for the address, what would the column heading
e?
Query 2 – Math
For this query, we need to be aware of the order of operations, and what happens of we don’t
do our math routines co
ectly.
Create the following query using the “order details” table. We want to create a calculated
column, using the Unit Price, Quantity, and Discount attributes.
1. Create a query with the following attributes
a. OrderID, Unit Price, Quantity, Discount.
2. Now add a column that will take into account the Unit price, the quantity, and the
discount to create a “Total Price” Column
Question: what did the total price look like when you are working with Zeros?
Chapter 4 Lab
This Lab sheet is worth 10 points for complete queries. 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.
7. Include your answer to the questions within the title comment and upload the solution as usual.
Follow the instructions in the Lab Lecture, complete the following queries.
Query 1 - Inner join
Create a query that will display the CustomerID, Company Name, OrderID, OrderDate. From the
Customers and Orders tables.
Once the query is created, answer the following question.
Question – What will the results set represent?
Query 2 - Outer Join
Changing the inner join from query 1 to a left join, ( Customers LEFT JOIN Orders)
Create a query that will display the CustomerID, Company Name, OrderID, OrderDate.
Once the query is created, answer the following question.
Question – Looking thru the results set, you can see some “NULL” entries. What does the “NULL” entries
epresent?
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