h
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 brief 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 be? 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 correctly. 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 brief 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 represent? 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 (1960 - 1969). Use 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 '01-01-1960' 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