hg
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. Quiz Solutions Quiz 1 For this query, you will write an SQL query that will solve the question. No results or message tab needed, just the query. No specific database need be used. you can make your own properly named database, tables, and attributes. 1. Make sure you put the USE command on the first line to show your name as the database used. 2. An SQL comment with your name, quiz number and question number. 3. The complete SQL statements. 4. Assume you will be using the “employees” table. Query: The Human resources department has asked you to create a report for them that will list selected employee information. In the report, they need the employee identification number, Employee first name Initial, Employee full last name (in one output column), and their current title. Please sort the output by employee identification number, making sure the highest number is first on the list. USE CISQuiz1 -- Your Name --Quiz 1 --Question ?? -- The SELECT line with the ID, then an ALIAS that will have the FirstName initial and Lastname -- the two attributes in one output column. The Title also SELECT EmployeeID, LEFT(FirstName, 1) + LastName AS Name, EmployeeTitle -- the table as defined in instructions FROM employees --Order by ID, as defined in instructions, highest first ORDER By EmployeeID DESC Quiz 1, query 2 For this query, you will need to write an SQL query that will solve the question. No results or message tab output is needed. The table names are given as part of the attribute name. the name of the data base is "quiz2query" 1. Make sure you put the USE command on the first line to show what database is used. 2. An SQL comment with your name, quiz number and question number. 3. The complete SQL statements. 4. The Customers table has a PK of CustID 5. The Shippers table has a PK of ShipID, and FK of CustID Query #2: Create a query that will show the following: 1) Customers.CustID, 2) An e-mail address which is made up of the Customers.ContactName, an “@” symbol and the Customers.CompanyName, 3) Customers.Contact Title Only display the above information if ALL of the following criterion are met: 1) The Shippers.ShipCity is London England 2) The Shippers.FreightCost is greater that $500 3) The title of the Customers.Contact Title is Sales Director USE quiz2query --Your name --Quiz 1 --Question number ?? -- the select line with the email made of the contact name and companyname SELECT Customers.CustID, Customers.ContactName,+ ‘@’ + Customers.CompanyName as Email, Customers.Contacttitle -- The inner join of tables – we