Pharmacy Dataset for SQL Practice

• sales.product_id →references products.product_idmany to one: each Sales row points to one Products row
• sales.customer_id →references customers.customer_idmany to one: each Sales row points to one Customers row
• inventory.product_id →references products.product_idmany to one: each Inventory row points to one Products row

1. What are the top 5 best-selling products by total revenue, and what is their total quantity sold?

   tables: sales, products

2. Which products are frequently out of stock (quantity_on_hand = 0)? List their names and current inventory status.

   tables: inventory, products

3. Calculate the total sales revenue per customer, ordered from highest to lowest.

   tables: sales, customers

4. What is the average quantity sold per sale for each product category?

   tables: sales, products

Table names match the SQLite file and the SQL script.

SELECT p.product_name, SUM(s.total_revenue) AS total_revenue_generated, SUM(s.quantity_sold) AS total_quantity_sold
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_revenue_generated DESC
LIMIT 20;

SELECT c.customer_id, c.first_name, c.last_name, SUM(s.total_revenue) AS customer_total_revenue
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY customer_total_revenue DESC
LIMIT 20;

SELECT p.category, AVG(s.quantity_sold) AS average_quantity
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.category
LIMIT 20;

SELECT c.loyalty_program_status, COUNT(s.sale_id) AS number_of_sales, SUM(s.total_revenue) AS total_revenue
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY c.loyalty_program_status
ORDER BY total_revenue DESC
LIMIT 20;

Excel

Download as XLSX for use in Microsoft Excel.

Power BI

Download as CSV or XLSX for import into Power BI.

SQL

The SQLite download contains the full dataset. For other SQL databases, use the relational SQL dump. Primary keys and foreign keys are defined for join paths.

Python

Download as CSV or JSON for use with Python libraries like Pandas.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file