h
CIS111 Chapter 5 Homework This homework problem sheet is worth 100 points for a complete query. 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. The queries 1. Display the count of orders for each customer, the number of items per order and the total value of all orders per customer. Please use the following column aliases: Count of Orders Total sales 2. Display the Company name, count of products for each supplier, The total value of all items in stock, and Total Value of items on order, per company. Please use the following column aliases: Count of Products Value in Stock Value on Order Only look for companies with an amount on order Order the output by the Value on order, with highest first. 3. Display the distinct Contact name, employee last name, and the number of orders for each customer that have only purchased one item from the company. Use the alias of “Count of Orders” for the number of orders. Order the output by Contact name, alphabetical order. CIS111 Chapter 6 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. 7. Put your initials on either end of each of the four derived table names. Please create the following query: Building a CTE a. First derived table in the CTE - display CustomerID, CompanyName, Contact name and Address from the customers table. b. The second derived table in the CTE - should list CustomerID, EmployeeID, Order Date, the Required date, the ship address and Shippers ID, from the orders table. c. Third derived table in the CTE – the Employee ID, Employee First and last name from the Employees table. d. The fourth derived Table of the CTE – Select the ShipperID and the Company name from the Shippers table e. The final output should show the Company Name, the contact name, the Order required date, The shipping address and the Address shown on the Order and the shipping company name. f. You need to find where the Required date is higher than the Order date, where the shipping address is not the address on the order, then the shipping company is “Speedy Express” g. Order the output by the Customers Company name, in ascending order. CIS111 Chapter 7 Homework This homework 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. Create the following queries. 1. Create a query that will use a SELECT INTO statement to create one table named Employeescopy that is a complete copy of the Employees tables. Submit the query that creates the new table. 2. Create a query that show the new table, and the first 5 lines of the output, per the lab heading 3. Write an INSERT statement that adds a row to the EmployeesCopy table, with your information. You need to add the following information: Your first Name, Last name, your full address, your title. Include other required information by the table definition. For your birthdate, add the current date, using the function GETDATE, for the hire date, add 30 days to the GETDATE function. 4. Create a query that will show that your information was added to the table. Only need your information. Target the WHERE to only bring back your information. CIS 111 Chapter 10 In-Class Activity Page 1 of 1 This homework sheet is worth 100 points. For this homework, you will complete the data dictionary used in the Chapter 10 Lab. You can find the example data dictionary spreadsheet on Moodle. From the chapter 10 lab, identify and define each of the entities/attributes in each table. For example, a student table may have the following attributes. StudentID Firsname Last name Address And potentially many more. Then each of the attributes have a data definition. Which include: Attribute name, Contents (a simple explanation of what this is) Data type, Acceptable formats Range of values Size Key identification You can find all of the information in the lectures. The spreadsheet is located on Moodle. Submit your original spreadsheet into Moodle.