hi
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 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. 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 b. 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 b. Home Phone number c. Put the following attributes in one output column, then alias it “Full Name” a. First name b. 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 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. 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 correlated 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 b. 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 (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