Can all queries be answered.
INFO 4140 Assignment #1 For all the questions in this assignment (1) Comment out the question and include it as reference in your SQL code. (2) Alias all table names. (3) Include the database name when pulling the table (do not start with the schema). (4) Provide an alias for columns when the column does not have a name. (5) End all statements with a semi-colon. (6) Include WHERE 1=1 in all statements where you need a WHERE statement (you may optionally include it on all statements). For most questions, the result set is the answer, so you only need to provide the query (which I will run to determine your grade), not the result set. If the question can’t be answered with a query, provide the query (or queries) showing your work and then provide the answer in a comment. Do not submit result sets unless specifically requested. Set 1 1. Pull the top 10 lines from [AdventureWorks2014].[Production].[ProductCategory]. Provide the query. What are the number of records and fields in this result set? 2. Using [AdventureWorks2014].[Production].[Product], pull all the data where the color of the product is red or black. Provide the answer in a single query. 3. Using [AdventureWorks2014].[Production].[Product], pull all the data where the color of the product is not like black, grey, or red. Provide the answer in a single query. 4. Using [AdventureWorks2014].[Sales].[vSalesPerson], get the sum of sales, by territory, where SalesYTD is greater than the SalesLastYear, and the SalesLastYear and SalesQuota are not null. Provide the answer in a single query. 5. Using [AdventureWorks2014].[Sales].[vSalesPerson], get the sum of sales, by territory, where SalesYTD, per territory, is greater than the total SalesLastYear, per territory, and the SalesLastYear and SalesQuota are not null. Provide the answer in a single query. 6. Using [AdventureWorks2014].[Sales].[Customer], determine if unique customers are recorded as visiting multiple stores and territories. Use multiple SQL statements and comment out the answer. 7. Using [AdventureWorks2014].[Sales].[Customer], determine if unique customers are recorded as visiting multiple stores and territories. Provide the answer in a single query (you will likely use the ORDER BY to make this statement more readable). 8. Using [AdventureWorks2014].[HumanResources].[Department] a. Determine the total number of lines in the table. b. Determine the total number of unique departments in the table (assume the ID field is a primary key) c. Determine the total number of unique groups. 9. Using [AdventureWorks2014].[Purchasing].[Vendor] and using a single SQL query: a. Determine the total number of lines in the table. b. Determine the unique number of vendors in the table. c. Determine the MAX business entity ID to see if the MAX vendor ID is the same as the number of lines. d. Determine the difference between the MAX and MIN entity IDs to see if that difference is the same as the number of lines. 10. Using [AdventureWorks2014].[Purchasing].[ShipMethod], determine if the method with the highest base has the lowest rate. Determine if the method with the lowest rate has the highest base. Show your work using multiple SQL queries or a single query with calculations (do not simply SELECT * and read the table). Set 2 1. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], pull the sum of the LineTotal. Provide the answer in a single query. 2. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], multiple the quantity by the list price and by the discount to calculate your own LineTotal. Provide the answer in a single query. 3. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], compare the sum of the LineTotal with the calculated LineTotal you just developed in a single statement to see if they are the same. Provide the answer in a single query. 4. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], pull the sum of the total sales where the product ID is between 700 and 800. Write without using a BETWEEN statement. Provide the answer in a single query. 5. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], pull the sum of the line total where the line total is between $1000 and $4000. Write without using a BETWEEN statement. Provide the answer in a single query. 6. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], pull the sum of the line total where the line total is between $1000 and $4000. Write with a BETWEEN statement. Provide the answer in a single query. 7. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], determine your top ten most commonly sold products by using the sum of the order quantity. Order them, with the most common item first. Provide the answer in a single query. 8. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], determine your top ten most profitable products by using the sum of the LineTotal. Order them, with the most profitable item first. Provide the answer in a single query. 9. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], determine the unique number of products sold. Provide the answer in a single query. 10. Using [AdventureWorks2014].[Production].[Product], determine if the unique number of products sold is the same as the total number of products. Provide a query to prove or disprove this statement, and include the answer in a comment. 11. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], classify your line items into those with a discount and those without a discount. Then, for each group, provide the sum of the line total. Provide the answer in a single query. 12. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], determine your top ten products that are the most likely to have a discount applied. Include the chance of the product having a discount. Provide the answer in a single query. 13. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], determine the unique number of the orders. Provide the answer in a single query that has one record in the result set. 14. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], determine the average number of line items per unique order. Provide the answer in a single query that has one record in the result set. 15. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], pull the sum of the line total per product, where the line total is greater than $1000. Provide the answer in a single query. 16. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], pull the sum of the line total per product, where the product line total is greater than $10,000. Provide the answer in a single query. 17. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], classify the line items as having a carrier or not having a carrier. Then, pull the sum of the line items for each group. Provide the answer in a single query. 18. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], per product, pull the average product price when there is a carrier. Provide the answer in a single query. 19. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], per product, pull the average product price when there is a carrier and the average product price without a carrier. Provide the answer in a single query. 20. Using [AdventureWorks2014].[Sales].[SalesOrderDetail], pull the maximum quantity ordered product and the total quantity ordered per product. Provide a comment that explains whether the product with the highest number of sales per line is the same as the most common product.