03_Queries.sql

-- SQL Commands for Table Connections

-- 1. Connection: Patient and Address Table

-- Command 1: List Patient Cities (Basic Join)
-- Explanation: Lists every patient along with the city they live in.
SELECT P.first_name, P.last_name, A.city
FROM Patient P
INNER JOIN Address A ON P.address_id = A.address_id;

-- Command 2: Find Patients in London (Filter)
-- Explanation: Filters the list to show only patients residing in London.
SELECT P.first_name, P.last_name, A.street
FROM Patient P
INNER JOIN Address A ON P.address_id = A.address_id
WHERE A.city = 'London';

-- Command 3: Count Patients per City (Aggregate)
-- Explanation: Calculates how many registered patients live in each city.
SELECT A.city, COUNT(P.patient_id) as Total_Patients
FROM Address A
LEFT JOIN Patient P ON A.address_id = P.address_id
GROUP BY A.city;


-- 2. Connection: Product and Supplier Table

-- Command 1: Product Suppliers (Basic Join)
-- Explanation: Shows which company supplies which product.
SELECT Prod.product_name, Sup.company_name
FROM Product Prod
INNER JOIN Supplier Sup ON Prod.supplier_id = Sup.supplier_id;

-- Command 2: Products from Bayer (Filter)
-- Explanation: Lists all inventory items supplied specifically by Bayer.
SELECT Prod.product_name, Prod.stock_quantity
FROM Product Prod
INNER JOIN Supplier Sup ON Prod.supplier_id = Sup.supplier_id
WHERE Sup.company_name = 'Bayer';

-- Command 3: Count Products per Supplier (Aggregate)
-- Explanation: Shows how many different types of products are provided by each supplier.
SELECT Sup.company_name, COUNT(Prod.product_id) as Total_Products
FROM Supplier Sup
LEFT JOIN Product Prod ON Sup.supplier_id = Prod.supplier_id
GROUP BY Sup.company_name;


-- 3. Connection: Sale and Patient Table

-- Command 1: Sale Owners (Basic Join)
-- Explanation: Identifies which patient made each specific purchase.
SELECT S.sale_id, P.first_name, P.last_name, S.total_amount
FROM Sale S
INNER JOIN Patient P ON S.patient_id = P.patient_id;

-- Command 2: Sales for James Bond (Filter)
-- Explanation: Retrieves the purchase history for a specific customer.
SELECT S.sale_date, S.total_amount
FROM Sale S
INNER JOIN Patient P ON S.patient_id = P.patient_id
WHERE P.first_name = 'James' AND P.last_name = 'Bond';

-- Command 3: Total Spending per Patient (Aggregate)
-- Explanation: Calculates the total amount of money each patient has spent in the pharmacy.
SELECT P.first_name, P.last_name, SUM(S.total_amount) as Total_Spent
FROM Patient P
INNER JOIN Sale S ON P.patient_id = S.patient_id
GROUP BY P.first_name, P.last_name;


-- 4. Connection: Sale and Employee Table

-- Command 1: Staff Responsibility (Basic Join)
-- Explanation: Shows which employee processed each sale.
SELECT S.sale_id, E.first_name, E.last_name
FROM Sale S
INNER JOIN Employee E ON S.employee_id = E.employee_id;

-- Command 2: Sales by Pharmacist Ahmet (Filter)
-- Explanation: Lists all transactions handled by a specific employee.
SELECT S.sale_id, S.total_amount
FROM Sale S
INNER JOIN Employee E ON S.employee_id = E.employee_id
WHERE E.first_name = 'Ahmet';

-- Command 3: Revenue per Employee (Aggregate)
-- Explanation: Measure employee performance by calculating total revenue generated by each staff member.
SELECT E.first_name, E.last_name, SUM(S.total_amount) as Revenue_Generated
FROM Sale S
INNER JOIN Employee E ON S.employee_id = E.employee_id
GROUP BY E.first_name, E.last_name;


-- 5. Connection: Sale_Detail and Sale Table

-- Command 1: Transaction Details (Basic Join)
-- Explanation: Links specific line items to their main transaction date.
SELECT SD.detail_id, S.sale_date, SD.quantity, SD.unit_price
FROM Sale_Detail SD
INNER JOIN Sale S ON SD.sale_id = S.sale_id;

-- Command 2: High Quantity Items in Sale #1 (Filter)
-- Explanation: Finds items sold in bulk within a specific transaction.
SELECT SD.product_id, SD.quantity
FROM Sale_Detail SD
INNER JOIN Sale S ON SD.sale_id = S.sale_id
WHERE S.sale_id = 1 AND SD.quantity > 1;

-- Command 3: Total Items per Sale (Aggregate)
-- Explanation: Calculates the total number of individual items sold in each transaction.
SELECT S.sale_id, SUM(SD.quantity) as Total_Items_Sold
FROM Sale S
INNER JOIN Sale_Detail SD ON S.sale_id = SD.sale_id
GROUP BY S.sale_id;


-- 6. Connection: Sale_Detail and Product Table

-- Command 1: Product Names in Sales (Basic Join)
-- Explanation: Replaces product IDs with actual product names in the sales report.
SELECT SD.detail_id, P.product_name, SD.quantity
FROM Sale_Detail SD
INNER JOIN Product P ON SD.product_id = P.product_id;

-- Command 2: Sales of Antibiotics (Filter)
-- Explanation: Lists sales transactions that included antibiotic products.
SELECT SD.sale_id, SD.quantity, SD.unit_price
FROM Sale_Detail SD
INNER JOIN Product P ON SD.product_id = P.product_id
WHERE P.category = 'Antibiotics';

-- Command 3: Total Revenue per Product (Aggregate)
-- Explanation: Calculates how much money was earned from the sales of each specific product.
SELECT P.product_name, SUM(SD.quantity * SD.unit_price) as Total_Revenue
FROM Sale_Detail SD
INNER JOIN Product P ON SD.product_id = P.product_id
GROUP BY P.product_name;


-- 7. Connection: Prescription and Patient Table

-- Command 1: Patient Prescriptions (Basic Join)
-- Explanation: Lists prescriptions alongside the patient they belong to.
SELECT Pre.prescription_id, P.first_name, P.last_name, Pre.doctor_name
FROM Prescription Pre
INNER JOIN Patient P ON Pre.patient_id = P.patient_id;

-- Command 2: Prescriptions for Walter White (Filter)
-- Explanation: Finds all prescriptions issued to a specific patient.
SELECT Pre.doctor_name, Pre.issue_date
FROM Prescription Pre
INNER JOIN Patient P ON Pre.patient_id = P.patient_id
WHERE P.first_name = 'Walter';

-- Command 3: Prescription Count per Patient (Aggregate)
-- Explanation: Counts how many prescriptions each patient has on file.
SELECT P.first_name, P.last_name, COUNT(Pre.prescription_id) as Rx_Count
FROM Patient P
LEFT JOIN Prescription Pre ON P.patient_id = Pre.patient_id
GROUP BY P.first_name, P.last_name;


-- 8. Connection: Prescription_Item and Prescription Table

-- Command 1: Rx Item Details (Basic Join)
-- Explanation: Shows the dosage instructions and the prescribing doctor for each item.
SELECT PI.item_id, Pre.doctor_name, PI.dosage
FROM Prescription_Item PI
INNER JOIN Prescription Pre ON PI.prescription_id = Pre.prescription_id;

-- Command 2: Items in Rx #1 (Filter)
-- Explanation: Lists all medicines included in a specific prescription ID.
SELECT PI.product_id, PI.dosage
FROM Prescription_Item PI
INNER JOIN Prescription Pre ON PI.prescription_id = Pre.prescription_id
WHERE Pre.prescription_id = 1;

-- Command 3: Items per Doctor (Aggregate)
-- Explanation: Analyzes how many individual medicine items each doctor has prescribed.
SELECT Pre.doctor_name, COUNT(PI.item_id) as Total_Medicines_Prescribed
FROM Prescription Pre
INNER JOIN Prescription_Item PI ON Pre.prescription_id = PI.prescription_id
GROUP BY Pre.doctor_name;


-- 9. Connection: Prescription_Item and Product Table

-- Command 1: Prescribed Product Names (Basic Join)
-- Explanation: Lists the actual names of the medicines in prescriptions.
SELECT PI.prescription_id, P.product_name, PI.dosage
FROM Prescription_Item PI
INNER JOIN Product P ON PI.product_id = P.product_id;

-- Command 2: Prescriptions with Ibuprofen (Filter)
-- Explanation: Finds all prescriptions that include Ibuprofen.
SELECT PI.prescription_id, PI.dosage
FROM Prescription_Item PI
INNER JOIN Product P ON PI.product_id = P.product_id
WHERE P.product_name LIKE '%Ibuprofen%';

-- Command 3: Most Prescribed Products (Aggregate)
-- Explanation: Ranks products by how often they appear in prescriptions.
SELECT P.product_name, COUNT(PI.item_id) as Times_Prescribed
FROM Prescription_Item PI
INNER JOIN Product P ON PI.product_id = P.product_id
GROUP BY P.product_name;


-- 10. Connection: Invoice and Sale Table

-- Command 1: Invoice Details (Basic Join)
-- Explanation: Matches the tax invoice with the original sales amount.
SELECT I.invoice_id, S.total_amount, I.tax_amount
FROM Invoice I
INNER JOIN Sale S ON I.sale_id = S.sale_id;

-- Command 2: Invoices for Cash Sales (Filter)
-- Explanation: Lists invoices generated specifically for cash transactions.
SELECT I.invoice_id, I.tax_amount
FROM Invoice I
INNER JOIN Sale S ON I.sale_id = S.sale_id
WHERE S.payment_method = 'Cash';

-- Command 3: Total Tax Collected (Aggregate)
-- Explanation: Calculates the total tax amount collected broken down by payment method.
SELECT S.payment_method, SUM(I.tax_amount) as Total_Tax
FROM Invoice I
INNER JOIN Sale S ON I.sale_id = S.sale_id
GROUP BY S.payment_method;