ClassicModels SQL Exercises

SQL Queries for Payments Table

• SELECT * FROM Payments LIMIT 10; retrieves all records, displaying the first 10 rows of the Payments table.
• SELECT checkNumber FROM Payments LIMIT 10; fetches the first 10 entries of checkNumber, offering insight into payment identifiers.
• SELECT paymentDate FROM Payments LIMIT 10; returns the initial 10 values for paymentDate, showing transaction timestamps.
• SELECT amount FROM Payments LIMIT 10; lists the first 10 recorded payment amounts, indicating financial transactions.
• SELECT customerNumber FROM Payments LIMIT 10; reveals the first 10 customerNumber values, identifying clients associated with payments.

Sorting and Ordering Results

• SELECT checkNumber FROM Payments ORDER BY checkNumber LIMIT 10; organizes checkNumber values in ascending order, displaying the top 10.
• SELECT checkNumber FROM Payments ORDER BY checkNumber DESC LIMIT 10; retrieves the first 10 checkNumber entries, sorted in descending order.
• SELECT paymentDate FROM Payments ORDER BY paymentDate LIMIT 10; sorts the paymentDate values in ascending order for the initial 10 records.
• SELECT paymentDate FROM Payments ORDER BY paymentDate DESC LIMIT 10; provides the 10 latest paymentDate values in descending order.
• SELECT customerNumber FROM Payments ORDER BY customerNumber LIMIT 10; organizes customerNumber in ascending order for the top 10 entries.
• SELECT customerNumber FROM Payments ORDER BY customerNumber DESC LIMIT 10; fetches the first 10 customerNumber entries sorted in descending order.

Multi-Column Selections

• SELECT checkNumber, paymentDate FROM Payments LIMIT 10; presents both checkNumber and paymentDate for the initial 10 entries.
• SELECT checkNumber, amount FROM Payments LIMIT 10; displays checkNumber alongside amount for the first 10 transactions.
• SELECT checkNumber, customerNumber FROM Payments LIMIT 10; provides both checkNumber and customerNumber for the top 10 records.

Unique and Aggregate Functions

• SELECT DISTINCT(customerNumber) FROM Payments LIMIT 10; generates a list of unique customerNumber values, helping to identify distinct clients.
• SELECT MIN(amount) AS 'Smallest Payment' FROM Payments; calculates and labels the minimum payment amount as 'Smallest Payment'.
• SELECT MAX(amount) AS "Largest Payment" FROM Payments; identifies the maximum payment amount, labeled as "Largest Payment".
• SELECT AVG(amount) FROM Payments LIMIT 10; computes the average payment amount for analysis.
• SELECT MIN(paymentDate) FROM Payments LIMIT 10; finds the earliest paymentDate, indicating the onset of transactions.
• SELECT MAX(paymentDate) FROM Payments LIMIT 10; identifies the most recent paymentDate for current payment trends.

Grouping and Counting Data

• SELECT customerNumber, SUM(amount) FROM Payments GROUP BY customerNumber LIMIT 10; aggregates total payment amounts for each customer, showing the first 10 groups.
• SELECT customerNumber, AVG(amount) FROM Payments GROUP BY customerNumber LIMIT 10; computes average payment amounts grouped by each customer.
• SELECT COUNT(*) FROM Payments LIMIT 10; counts total rows in the Payments table, serving as a record of transactions.
• SELECT COUNT(DISTINCT(customerNumber)) LIMIT 10; counts unique customerNumber values, providing an overview of distinct clients.

Conditional Selections

• SELECT customerNumber FROM Payments WHERE customerNumber < 200 LIMIT 10; filters customerNumber values under 200, displaying the first 10 results.
• SELECT customerNumber FROM Payments WHERE customerNumber BETWEEN 200 AND 400 LIMIT 10; retrieves customerNumber values within the specified range, highlighting a specific client segment.