Practical PQL Exercises

Database: Accounts Payable

Schema:

Invoices

Column Name	Data Type
InvoiceID	INT PRIMARY KEY
VendorID	INT
InvoiceDate	DATE
Amount	FLOAT
DueDate	DATE
PaymentDate	DATE
Status	STRING

Vendors

Column Name	Data Type
VendorID	INT PRIMARY KEY
VendorName	STRING
Country	STRING

Payments

Column Name	Data Type
PaymentID	INT PRIMARY KEY
InvoiceID	INT
PaymentDate	DATE
PaymentAmount	FLOAT
PaymentMethod	STRING

ExchangeRates

Column Name	Data Type
RateDate	DATE
FromCurrency	STRING
ToCurrency	STRING
Rate	FLOAT

Exercises:

Exercise 1: Calculate the total amount of unpaid invoices for each vendor.

Exercise 2: Find the average number of days invoices are overdue for each vendor.

Exercise 3: List vendors with more than 5 overdue invoices.

Exercise 4: Calculate the total payment amount made via 'Bank Transfer' in the last 30 days.

Exercise 5: Get the name of the vendor for the invoice with the highest amount.

Exercise 6: Count the number of distinct payment methods used.

Exercise 7: For each vendor, list their invoice IDs concatenated by a comma, for invoices created this year.

Exercise 8: Find the median invoice amount for invoices due next month.

Database: Order Processing

Schema:

Orders

Column Name	Data Type
OrderID	INT PRIMARY KEY
CustomerID	INT
OrderDate	DATE
TotalAmount	FLOAT
Status	STRING

Customers

Column Name	Data Type
CustomerID	INT PRIMARY KEY
CustomerName	STRING
Region	STRING

OrderItems

Column Name	Data Type
OrderItemID	INT PRIMARY KEY
OrderID	INT
ProductID	INT
Quantity	INT
UnitPrice	FLOAT

Products

Column Name	Data Type
ProductID	INT PRIMARY KEY
ProductName	STRING
Category	STRING

Exercises:

Exercise 1: Calculate the total sales amount for each customer region.

Exercise 2: Find the top 3 most sold products by quantity.

Exercise 3: List customers who have placed more than 10 orders.

Exercise 4: Calculate the average order processing time (OrderDate to ShippedDate, assuming ShippedDate is in Orders table and named 'ShippedDate').

Exercise 5: Get the total number of orders placed each month of the current year.

Exercise 6: Find the percentage of orders with status 'Completed'.

Exercise 7: For each product category, find the product with the highest unit price.

Exercise 8: Identify customers who have not placed an order in the last 6 months.

Database: Financial Processes

Schema:

Transactions

Column Name	Data Type
TransactionID	INT PRIMARY KEY
AccountID	INT
TransactionDate	DATE
Amount	FLOAT
Currency	STRING
Description	STRING

Accounts

Column Name	Data Type
AccountID	INT PRIMARY KEY
AccountName	STRING
AccountType	STRING
Balance	FLOAT

Budgets

Column Name	Data Type
BudgetID	INT PRIMARY KEY
Department	STRING
Year	INT
AllocatedAmount	FLOAT

CurrencyRates

Column Name	Data Type
RateDate	DATE
SourceCurrency	STRING
TargetCurrency	STRING
ExchangeRate	FLOAT

Exercises:

Exercise 1: Calculate the total transaction amount for each account type.

Exercise 2: Find accounts with a negative balance.

Exercise 3: List the top 5 largest transactions by amount this year.

Exercise 4: Calculate the variance of transaction amounts for 'Expense' accounts.

Exercise 5: Get the running total of transaction amounts for a specific AccountID (e.g., 123) ordered by date.

Exercise 6: For each department, calculate the percentage of budget utilized (assuming a 'ActualSpend' table or column).

Exercise 7: Convert all transaction amounts from USD to EUR using a specific date's exchange rate from CurrencyRates.

Exercise 8: Find the month with the highest total transaction volume (sum of absolute amounts).

Database: HR & Payroll

Schema:

Employees

Column Name	Data Type
EmployeeID	INT PRIMARY KEY
FirstName	STRING
LastName	STRING
DepartmentID	INT
HireDate	DATE
Salary	FLOAT

Departments

Column Name	Data Type
DepartmentID	INT PRIMARY KEY
DepartmentName	STRING

Salaries

Column Name	Data Type
SalaryID	INT PRIMARY KEY
EmployeeID	INT
PayDate	DATE
GrossAmount	FLOAT
NetAmount	FLOAT
TaxAmount	FLOAT

TimeOff

Column Name	Data Type
TimeOffID	INT PRIMARY KEY
EmployeeID	INT
StartDate	DATE
EndDate	DATE
Type	STRING

Exercises:

Exercise 1: Calculate the average salary for each department.

Exercise 2: List employees hired in the last year.

Exercise 3: Find the department with the highest total payroll (sum of NetAmount from Salaries).

Exercise 4: Calculate the total number of vacation days taken by each employee this year.

Exercise 5: Get the longest serving employee's name and hire date.

Exercise 6: Count the number of employees per department.

Exercise 7: Calculate the average tenure (in years) of employees in the 'Engineering' department.

Exercise 8: Find employees whose salary is above the average salary of their department.

Database: Supply Chain Management

Schema:

Shipments

Column Name	Data Type
ShipmentID	INT PRIMARY KEY
OrderID	INT
ShipDate	DATE
DeliveryDate	DATE
Carrier	STRING
Status	STRING

Inventory

Column Name	Data Type
ProductID	INT PRIMARY KEY
WarehouseID	INT
QuantityOnHand	INT
LastStockDate	DATE

Warehouses

Column Name	Data Type
WarehouseID	INT PRIMARY KEY
Location	STRING
Capacity	INT

Suppliers

Column Name	Data Type
SupplierID	INT PRIMARY KEY
SupplierName	STRING
ContactEmail	STRING

Exercises:

Exercise 1: Calculate the average shipment delivery time (ShipDate to DeliveryDate) for each carrier.

Exercise 2: List products with less than 10 units in stock (QuantityOnHand).

Exercise 3: Find the warehouse with the highest inventory capacity.

Exercise 4: Calculate the total number of shipments with status 'Delayed'.

Exercise 5: Get the supplier name for products that were last stocked more than 90 days ago.

Exercise 6: Count the number of distinct shipment statuses.

Exercise 7: For each warehouse, list the product IDs it stores, concatenated by a comma.

Exercise 8: Find the median quantity on hand for products in 'Electronics' category (assuming Product table is joinable).

Database: Customer Relationship Management

Schema:

Leads

Column Name	Data Type
LeadID	INT PRIMARY KEY
LeadSource	STRING
CreationDate	DATE
Status	STRING
AssignedTo	INT

Contacts

Column Name	Data Type
ContactID	INT PRIMARY KEY
FirstName	STRING
LastName	STRING
Email	STRING
CompanyID	INT

Activities

Column Name	Data Type
ActivityID	INT PRIMARY KEY
ContactID	INT
ActivityDate	DATE
ActivityType	STRING
Notes	STRING

Companies

Column Name	Data Type
CompanyID	INT PRIMARY KEY
CompanyName	STRING
Industry	STRING

Exercises:

Exercise 1: Calculate the number of leads generated from each lead source this month.

Exercise 2: List contacts who have not had any activity in the last 60 days.

Exercise 3: Find the company with the most contacts.

Exercise 4: Calculate the average number of activities per contact for 'Email' type activities.

Exercise 5: Get the names of employees (AssignedTo in Leads, assuming an Employees table) who have more than 10 'Open' leads.

Exercise 6: Count the number of distinct industries among companies.

Exercise 7: For each lead source, list the statuses of leads from that source, concatenated by a comma.

Exercise 8: Find the median age of leads (CreationDate to Today) that are still in 'New' status.