SQL Major Homework

relationship as well as the names of the helds that" "create" the relationship. (4 points) Part B For problems 2-9, complete queries for each of the output requests. For each query, include (a) the problem number (2.9), (b) the text of your SQL query, and (c) the output of the query (if the query retums more than 10 lines, include only the finst 10 lines of output). (70 points total, 8-9 points each) 2. For each state, output the state abbreviation and the average household income for all customers in that state. Order results alphabetically by state abbreviation and be sure that the average household income column includes a meaningful column name. (Hint: You orly need to use ene talle for this quary since gou arar't asiel to give the actual rarme of the state.) (8 points) 3. Output the customer name (fname and lname), marital status, state (use the actual state name), and household income for all customers located in Colorado (CO), Utah (UT), ldaho (ID), and Wyoming (WY) who have a household income of under $119,$00 and are married. Onder results alphabetically by last name, then by first name. Your query should not include the OR operator. (3 points) 4. For all orders placed on August 9, 2021, and valued at $1,125 or less, show the customer's name (first and last), the order number, and the order total. Results should be put in order from the largest value to the smallest. (8 points). 5. For each state, what percentage of the state's popelation purchased a DEVac vacuum (assume everyone in the customer table has punased a DBVac vacuum)? Your output should include the state name, population, number of purchasers from that state, and the percentage (show as a decmal). Order the output from the highest percentage to the lowest and make sure each column has a usefully descriptive name. (9 points) 6. Output the five orders that included the highest number of items (eg, if an order included 2 of oce type of vacuum and 1 of another, that equates to three items). For each order, show the customer first name, custemer lest name, number of items, the order date and the total value of the ondee. 9 points) 7. Which states' customens have an average number of children in the household relationship as well as the names of the helds that" "create" the relationship. (4 points) Part B For problems 2-9, complete queries for each of the output requests. For each query, include (a) the problem number (2.9), (b) the text of your SQL query, and (c) the output of the query (if the query retums more than 10 lines, include only the finst 10 lines of output). (70 points total, 8-9 points each) 2. For each state, output the state abbreviation and the average household income for all customers in that state. Order results alphabetically by state abbreviation and be sure that the average household income column includes a meaningful column name. (Hint: You orly need to use ene talle for this quary since gou arar't asiel to give the actual rarme of the state.) (8 points) 3. Output the customer name (fname and lname), marital status, state (use the actual state name), and household income for all customers located in Colorado (CO), Utah (UT), ldaho (ID), and Wyoming (WY) who have a household income of under $119,$00 and are married. Onder results alphabetically by last name, then by first name. Your query should not include the OR operator. (3 points) 4. For all orders placed on August 9, 2021, and valued at $1,125 or less, show the customer's name (first and last), the order number, and the order total. Results should be put in order from the largest value to the smallest. (8 points). 5. For each state, what percentage of the state's popelation purchased a DEVac vacuum (assume everyone in the customer table has punased a DBVac vacuum)? Your output should include the state name, population, number of purchasers from that state, and the percentage (show as a decmal). Order the output from the highest percentage to the lowest and make sure each column has a usefully descriptive name. (9 points) 6. Output the five orders that included the highest number of items (eg, if an order included 2 of oce type of vacuum and 1 of another, that equates to three items). For each order, show the customer first name, custemer lest name, number of items, the order date and the total value of the ondee. 9 points) 7. Which states' customens have an average number of children in the household