Trent Hardware Database Tables/Fields Refer to the table and field names below for this database. Section 2: SQL (40\%) Single table queries: 1. Write the SQL statement that will count the number of rows in the SALE_ITEM table. Code: Results: 2. Find all the products Trent sells that include the word "siding". List the product ID, product name and description. Code: Results: 3. How much money (cost of all products) is tied up in inventory? Code: Results: 4. List the name, quantity in stock, and cost of each product where there are more than 25 in stock and the cost is less than 200 dollars. Sort by quantity beginning with the highest amount and then by cost. Code: Results: Multiple table queries using subqueries 5. List all buyers who live in Greenville, NC who have purchased products from Trent Hardware. Sort by Last Name and then First Name. (Use a subquery.) Code: Results: 6. Have any products listed in Trent Hardware's PRODUCT table never been sold? If so, list the unique description of the product(s) that have never been sold. (Use a subquery.) Code: Results: 7. Using a SQL subquery, confirm the referential integrity constraint has ensured only Buyers listed in the BUYERS table exist in the SALE table. Code: Results: Iultiple table queries using JOINs 8. Display the product description, the quantity sold, the unit price, and a total-line-price for SalelD = 12. Display the line-item total as "LineTotal". The Output should have these columns: I 1 Results Messages ItemQty LineTotal (Hint: If you don't know where to begin, use the relationship image on page 1 and trace a path from ProdDescription in PRODUCT table to SalelD in the SALE table, making note of primary and foreign keys that link the tables. Consider using those keys in your joins.) Code: Results: 9. List the count of different buyers who purchased siding products during 2021. Be sure to string their last and then first name together leaving no extra spaces between them. Sort by buyer name. Code: Results: 10. Write a query to show Product Category and "Total Profit" (use that term as column heading in your output) for the year 2021. Profit is calculated as ItemUnitPrice - ProdUnitCost. Group the results by Product Category and sort the list in descending order by TotalProfit. (NOTE: Since this is a fictional company, there may be large differences between what an item sold for and what it's listed cost or price is in the product table, and therefore don't be surprised by negative numbers in some categories.) Code: Result
