y
CIS111 Chapter 5 Lab Problems 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 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. Should only have one row in the output for each query. Follow the instructions in the Lab Lecture, complete the following queries. 1. Create a query that will find the total number of orders from the orders table. 2. Create a query that will find the total value of all items in the products table. 3. Create a query that will find the most expensive single item in the order details table. Just need to report the items value 4. Create a query that will find the last name of the oldest employee. You will need a sub query. CIS111 Chapter 6 Lab Problems 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. Follow the instructions in the Lab Lecture, complete the following queries. Query 1 Write a statement, with a subquery, that returns the same result set as the statement below. Substitute a subquery in the WHERE clause to replace the inner join, and using only the Customers table in the FROM line. USE CIS111_NorthWind SELECT DISTINCT CompanyName FROM Customers JOIN Orders ON Customers.CustomerID = Orders.customerID ORDER by CompanyName Query 2 Display the Customer Names for those customers that have NOT submitted an Order. Use a subquery. CIS111 Chapter 7 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 GuitarShop 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. Also include the answers to any questions to the comments. 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. Follow the instructions in the Lab Lecture, complete the following queries. We want to add some data to tables. The order in which the data is inserted is based on the table definition and the table relationships. For example, if we wanted to order something, looking at the orders table, you can see the primary key, but also the foreign key. So essentially, of a customer doesn’t exist, the order can’t be placed. Run the following queries: USE Cis111_GuitarShop --Query 1 SELECT * FROM Customers Then run: USE Cis111_GuitarShop --Query 2 SELECT * FROM Orders You can see the next customer number is 486 Let’s see if the following query runs. USE Cis111_GuitarShop --Query 3 INSERT INTO Orders (OrderID, CustomerID, OrderDate,ShipAmount, TaxAmount, ShipDate, ShipAddressID, CardType, CardNumber, CardExpires, BillingAddressID) VALUES (100, 486, getdate(), 200, 200, getdate() +10, '5101 Evergreen Dearborn MI', CIS111 Chapter 7 Lab 'Visa', 111-111-11121, getdate()+100, '5101 Evergreen Dearborn MI') Question 1: What was the error and why did you get it? Run the following query and answer the question. USE Cis111_GuitarShop --Query 4 insert into Orders (CustomerID, OrderDate,ShipAmount, TaxAmount, ShipDate, ShipAddressID, CardType, CardNumber, CardExpires, BillingAddressID) VALUES (486, getdate(), 200, 200, getdate() +10, 300, 'Visa', 111-111-11121, getdate()+100, 300) Question 2: what was the error, and why did you get the error? Using the following information, create two queries that will insert the data into both tables. In the <> use information provided or your information. For the Customers table: EmailAddress
Password