Exercise 6.4 - Summary:

In pg-admin, create a SQL script that does the following:

Tables:

• Sales_Header. Alias as sh
• Sales_Detail. Alias as sd

Attributes:

• Distinct Count of sale_id. Alias as total_transactions
• Sum of total items_sold. Alias as total_products_sold
• Average feedback_score rounded to 1 decimal place. Alias as avg_feedback
• Sum of total revenue_net. Alias as revenue
• Sum of total cost_of_sales. Alias as cost_of_sales
• Sum of total revenue_net minus sum of total cost_of_sales. Alias as gross_profit

• Test this script in PostgreSQL

  • The names and data should match the screenshot

• Convert this SQL script into a VIEW called v_Summary

• Append SQL to SELECT * FROM this VIEW into the space provided in line 13 of backend/routes/summary.js

• Save and close backend/routes/summary.js

• In a new browser tab, preview the transactions route (http://localhost:5001/summary)
  • This should be populated with an array of a single object containing the 6 attributes

• This is the data that our front-end will use to populate the Summary cards on the web page

• In the main Sequel-Mark website (http://localhost:5001), check the Summary cards have been populated