Pharmacy Dataset for SQL Practice
This ready-made synthetic dataset is designed for professionals looking to practice SQL queries on pharmacy-related data. It includes detailed information on products, customers, sales transactions, and inventory levels. A free sample is available for preview.
At a glance
- 4tables
- 21,760rows
- 23columns
- Jan 2023 – Dec 2024date range
The 4 tables
preview and data dictionary per tableProducts products · table · 3,000 rows
Contains information about pharmaceutical products.
Preview
| product_iduuid | product_namestring | categorystring | unit_pricedecimal | manufacturerstring |
|---|---|---|---|---|
| 32f34a49-c967-40a3-8abe-6e6e5b1ea5cd | Fexofenadine 180mg | Allergy | 19.79 | PharmaCorp |
| a0f0ed83-452f-44f7-83ad-bc956bf5f19e | Multivitamin Daily | Vitamins | 8.71 | HealthGen |
| d8432a4f-79ea-46f9-96b6-5db31fd8b892 | Multivitamin Daily-2 | Vitamins | 8.49 | PharmaCorp |
| 1b237f0c-2eba-4efb-8c39-e9fefe5de53d | Vitamin C 1000mg | Vitamins | 8.83 | Rx Solutions |
| f9047d40-38b0-4657-8082-0351595ef365 | Vitamin D3 2000IU | Vitamins | 20.13 | MediLife |
| 15c71fb0-8c81-4641-ad48-95bfb4b35f2e | Multivitamin Daily-3 | Vitamins | 18.64 | PharmaCorp |
| b433a0d8-dc39-42e7-a9fd-05e03985b603 | Loratadine 10mg | Allergy | 16.01 | Rx Solutions |
| d139506e-c3eb-46c9-b49f-d1d1d2056eac | Vitamin D3 2000IU-2 | Vitamins | 8.57 | BioPharma |
| 81e07aee-9101-4219-970a-43f44c4eeeb3 | Ibuprofen 400mg | Pain Relief | 5.13 | MediLife |
| 6e92e8dd-1e78-48d9-bce1-49edf973b037 | Vitamin C 1000mg-2 | Vitamins | 14.58 | Rx Solutions |
Data dictionary
| column | type | description | example | null % |
|---|---|---|---|---|
product_ | uuid | Unique identifier for each pharmaceutical product.unique | 32f34a49-c967-40a3-8abe-6e6e5b1ea5cd | 0% |
product_ | string | Brand or generic medication name including formulation or dosage strength.unique | Fexofenadine 180mg | 0% |
category | string | Therapeutic or clinical classification of the product. | Allergy | 0% |
unit_ | decimal | Standard retail unit selling price in USD. | 19.79 | 0% |
manufacturer | string | Pharmaceutical manufacturing company. | PharmaCorp | 0% |
Customers customers · dimension table · 5,000 rows
Information about pharmacy customers.
Preview
| customer_iduuid | first_namestring | last_namestring | emailstring | citystring | statestring | loyalty_program_statusstring |
|---|---|---|---|---|---|---|
| b21e0c06-3661-4c6b-b900-ab200f2790a1 | Olivia | Smith | [email protected] | Rivertown | NY | Bronze |
| e8176029-77cc-4418-b557-fd40290a0695 | Noah | Scott | [email protected] | Springfield | IL | Bronze |
| 65896a0f-be22-40b2-9c5d-6c4f16bfce45 | Emma | Sullivan | [email protected] | Springfield | IL | Bronze |
| 7bc1dc18-c02a-49dd-aad1-fcc7d928e622 | Liam | Stewart | [email protected] | Maplewood | TX | Bronze |
| f0f23f60-c019-4c7b-8b71-7cac02fae1ca | Olivia | Sanders | [email protected] | Maplewood | TX | Bronze |
| bcbada17-5df9-46e7-b23b-ef416127f26f | Mason | Simmons | [email protected] | Springfield | IL | Bronze |
| f265f5fb-30e1-46be-a9b0-7202674fcd20 | Isabella | Stevens | [email protected] | Oakville | CA | Bronze |
| a4a6c650-f17a-42b8-af06-714b9f3804cf | Ethan | Spencer | [email protected] | Oakville | CA | Bronze |
| 97d84c38-114d-44b3-ab2c-9f9d639dafde | Ava | Stone | [email protected] | Maplewood | TX | Bronze |
| 837e35c3-9b95-4020-a0c1-a1d46cf870ff | Lucas | Sharp | [email protected] | Springfield | IL | Bronze |
Data dictionary
| column | type | description | example | null % |
|---|---|---|---|---|
customer_ | uuid | Unique primary key identifier for each customer.unique | b21e0c06-3661-4c6b-b900-ab200f2790a1 | 0% |
first_ | string | First name of the customer or patient. | Olivia | 0% |
last_ | string | Last name of the customer or patient. | Smith | 0% |
email | string | Unique primary email address of the customer.unique | [email protected] | 0% |
city | string | City of residence for the customer. | Rivertown | 0% |
state | string | Two-letter US postal state abbreviation for the customer's residence. | NY | 0% |
loyalty_ | string | Customer membership tier within the pharmacy loyalty rewards program. | Bronze | 0% |
Inventory inventory · fact table · 4,481 rows
Tracks product stock levels across different locations.
Preview
| inventory_iduuid | locationstring | quantity_on_handinteger | last_restock_datedate | product_iduuid |
|---|---|---|---|---|
| a9908ee8-d9b4-441c-a0ad-f642806d7668 | Store 1 | 60 | 2023-02-01 | b3090a27-b228-401a-8c0b-924d599fd341 |
| 95e97bbf-8fd8-4588-a8e5-b2ec634b56c9 | Store 2 | 25 | 2023-08-10 | 8f0f5f8e-f9ab-465b-9a2a-04cb31419ccc |
| e155b7bc-4fe9-47e4-841f-7163803e79ff | Store 2 | 27 | 2023-11-15 | 79f43b37-fd6b-48f3-9133-f0abc7c305ea |
| 0d8dd737-8916-4781-b7e4-58bc78d16411 | Store 1 | 36 | 2024-07-03 | 9be154f1-261a-4851-8086-6c492e34fca4 |
| dc4a2db1-aecc-4bc4-9712-fc222da0236b | Store 1 | 41 | 2023-07-04 | 47b1ef24-e568-431e-9dce-082103faa174 |
| 9c0cb755-a7bc-4a3b-adbb-a4ff978910da | Store 1 | 96 | 2023-02-27 | 1e0f9ad0-f354-4d52-9cec-f54eb3a47434 |
| 8b8081be-ace6-4c65-9372-d75321071501 | Store 2 | 32 | 2024-11-18 | 7b81bf3f-f244-4870-8c9b-1043e6fc3125 |
| 056dcf02-8b3e-4174-bfd6-a4287d43a05b | Store 2 | 32 | 2024-07-24 | 3f74480a-6817-4d54-b92c-a029cdefc2cf |
| 91d8107e-7794-42d2-ac04-e0cb2a7730cb | Store 2 | 90 | 2024-04-02 | 776b8c77-0fb9-46b7-82f5-7c14cdf7f704 |
| 9068c838-4390-4006-bcc0-926298d64c28 | Store 1 | 57 | 2023-05-08 | 4ed4f949-7936-49e3-837f-dd3ebd7f1ef1 |
Data dictionary
| column | type | description | example | null % |
|---|---|---|---|---|
inventory_ | uuid | Primary key uniquely identifying each inventory record.unique | a9908ee8-d9b4-441c-a0ad-f642806d7668 | 0% |
location | string | Physical pharmacy branch, counter, or warehouse stocking the product. | Store 1 | 0% |
quantity_ | integer | Current number of units available in stock at the specified location. | 60 | 0% |
last_ | date | The date when stock was most recently replenished at this location. | 2023-02-01 | 0% |
product_ | uuid | Foreign key to products.product_id. | b3090a27-b228-401a-8c0b-924d599fd341 | 0% |
Sales sales · fact table · 9,279 rows
Records of individual sales transactions.
Preview
| sale_iduuid | sale_datedate | quantity_soldinteger | total_revenuedecimal | product_iduuid | customer_iduuid |
|---|---|---|---|---|---|
| 6c50b26c-5fff-4481-8c5f-95cbfa15664d | 2024-10-08 | 1 | 23.1 | 32f34a49-c967-40a3-8abe-6e6e5b1ea5cd | 59efcce9-07c7-4ade-a046-af3b85111d68 |
| ec0ee3da-f2a4-448c-baf1-6b2c8dfe8c4d | 2023-09-01 | 1 | 11.11 | 1b237f0c-2eba-4efb-8c39-e9fefe5de53d | e847dd97-275a-4ad2-8e55-8804560577fb |
Data dictionary
| column | type | description | example | null % |
|---|---|---|---|---|
sale_ | uuid | Primary key unique identifier for the sales transaction. | 7dbad491-9c0f-4861-be78-4657f8cce19e | 0% |
sale_ | date | Date when the transaction occurred between 2023-01-01 and 2024-12-31. | 2023-09-06 | 0% |
quantity_ | integer | Number of units of the product sold in this transaction (1 to 10). | 1 | 0% |
total_ | decimal | Calculated total sale revenue including standard retail markups and quantity. | 55.94 | 0% |
product_ | uuid | Foreign key to products.product_id. | 6dbb3239-5bfa-44d7-9b43-4a75c0233ab4 | 0% |
customer_ | uuid | Foreign key to customers.customer_id. | c45159c4-0d5f-44fe-a4a5-a6cdac8912fc | 0% |
How the tables join
sales.product_id references products.product_idmany to one: each Sales row points to one Products rowsales.customer_id references customers.customer_idmany to one: each Sales row points to one Customers rowinventory.product_id references products.product_idmany to one: each Inventory row points to one Products row
Questions to answer with it
What are the top 5 best-selling products by total revenue, and what is their total quantity sold?
tables: sales, products
Which products are frequently out of stock (quantity_on_hand = 0)? List their names and current inventory status.
tables: inventory, products
Calculate the total sales revenue per customer, ordered from highest to lowest.
tables: sales, customers
What is the average quantity sold per sale for each product category?
tables: sales, products
Starter SQL
run against this data before publishingTable names match the SQLite file and the SQL script.
Top 20 Products by Revenue
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;Total Revenue Per Customer
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;Average Quantity Sold by Category
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;Customer Loyalty and Sales
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;Load it with pandas
import pandas as pd
# Unzip the CSV download first: one file per table
products = pd.read_csv("products.csv")
customers = pd.read_csv("customers.csv")
inventory = pd.read_csv("inventory.csv")
sales = pd.read_csv("sales.csv")
# Join sales to products
df = sales.merge(products, left_on="product_id", right_on="product_id", how="left", suffixes=("", "_products"))
print(df.groupby("category").size().sort_values(ascending=False))Using it in your tool
- 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.
Formats available
- 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
How this data was generated
Synthetic data. Every row was generated: no real people, customers or companies are in this dataset.
Synthetic data generated by GoMask DataFactory from a relational blueprint: keys, links and rules are enforced in code, text columns are filled by a language model. No real people, companies or transactions.
- Data was generated using GoMask DataFactory.
- Synthetic transactions mimic typical pharmacy sales patterns.
- Product categories and customer demographics are proportionally represented.
- Checked by an automated quality gate: unique keys, no orphan foreign keys, required columns filled, declared rules and date ranges (realism score 100).
Limitations
- The dataset is synthetic and does not represent real individuals or transactions.
- Specific drug interactions or detailed medical histories are not included.
- Inventory restock dates are simplified and do not reflect real-time stock movements.
- Distributions and correlations are modelled, not measured from real records.
Part of these collections
15 SQL practice questions for the Pharmacy Dataset for SQL Practice
Written for this data and checked against it: 12 SQL and 3 Power BI exercises from beginner to advanced. Try the first three free; the practice kit has all 15, a starter .sql file and an answer key with every expected result.
Premium-priced products
Which products have a unit_price above 90? Return product_name, category and unit_price, ordered by unit_price from highest to lowest, then by product_id ascending to break ties.
Tables: products · your result: 3 columns, 42 rowsCustomers per loyalty tier
How many customers are in each loyalty_program_status? Return loyalty_program_status and customer_count, ordered by customer_count from highest to lowest, then by loyalty_program_status.
Tables: customers · your result: 2 columns, 4 rowsAverage price by category
For each product category, what is the number of products and the average unit price? Return category, product_count and avg_price (average unit_price rounded to 2 decimals), ordered by category.
Tables: products · your result: 3 columns, 8 rows
- 04Low stock at the warehouseBeginner
- 05Revenue by category bar chartPower BI
- 06Revenue by state and loyalty tierIntermediate
- 07Order size bucketsIntermediate
- 08Monthly revenue in 2024Intermediate
- 09Products with no inventory recordIntermediate
- 10Stock by location and manufacturer matrixPower BI
- 11Top product per categoryAdvanced
- 12Month-over-month revenue changeAdvanced
- 13Running revenue total in 2024Advanced
- 14Category share of revenueAdvanced
- 15Anomalous high-value salesPower BI
Get the practice kit: the full dataset, all 15 exercises, a starter .sql and the answer key. One-time $3.99.
Get the practice kit · $3.99blueprint · pharmacy-dataset-for-sql
Scale this dataset
Same tables. As many rows as you need.
Open the blueprint behind these 4 tables in Data Factory: keep the relationships, change a column, and generate it at the size you need.
- 200,000 rows
- 1,000,000 rows
- Tables
- products, customers, inventory, sales
- Licence
- yours to use, including commercially
- API slug
- pharmacy-dataset-for-sql