CREATE TABLE sales (
id INT PRIMARY KEY,
store VARCHAR(50),
cat VARCHAR(50),
amt DECIMAL(10, 2),
qty INT
);
INSERT INTO sales (id, store, cat, amt, qty) VALUES
(1, 'Downtown', 'Electronics', 1200.00, 2),
(2, 'Downtown', 'Clothing', 150.00, 3),
(3, 'Uptown', 'Electronics', 800.00, 1),
(4, 'Suburbs', 'Clothing', 45.00, 1),
(5, 'Downtown', 'Electronics', 450.00, 1),
(6, 'Uptown', 'Clothing', 300.00, 4),
(7, 'Suburbs', 'Electronics', 1500.00, 3),
(8, 'Downtown', 'Clothing', 80.00, 2),
(9, 'Uptown', 'Books', 35.00, 2),
(10, 'Suburbs', 'Books', 120.00, 5);
id INT PRIMARY KEY,
store VARCHAR(50),
cat VARCHAR(50),
amt DECIMAL(10, 2),
qty INT
);
INSERT INTO sales (id, store, cat, amt, qty) VALUES
(1, 'Downtown', 'Electronics', 1200.00, 2),
(2, 'Downtown', 'Clothing', 150.00, 3),
(3, 'Uptown', 'Electronics', 800.00, 1),
(4, 'Suburbs', 'Clothing', 45.00, 1),
(5, 'Downtown', 'Electronics', 450.00, 1),
(6, 'Uptown', 'Clothing', 300.00, 4),
(7, 'Suburbs', 'Electronics', 1500.00, 3),
(8, 'Downtown', 'Clothing', 80.00, 2),
(9, 'Uptown', 'Books', 35.00, 2),
(10, 'Suburbs', 'Books', 120.00, 5);
Q1. Find all transaction IDs ('id') and product categories ('cat') where the store location is exactly 'Downtown'.
Q2. List all data columns for transactions where the item quantity ('qty') is strictly greater than 2 and the monetary amount ('amt') is under $200.
Q3. Find all sales transactions that do NOT belong to the 'Books' category.
Q4. Calculate the total combined number of items sold ('qty') for each individual product category ('cat').
Q5. Determine the average monetary amount spent per transaction ('amt') at each distinct store location.
Q6. For each unique combination of store and category, find the maximum single transaction amount ('amt').
Q7. Which store locations ('store') have generated an average transaction amount ('amt') greater than $500?
Q8. List the product categories ('cat') that have handled more than 2 individual transaction records.
Q9. Identify the store locations where the cumulative quantity of items sold across all sales records is strictly less than 6.
Q10. Find the single lowest-paying transaction across the entire table. Display all column details.
Q11. Which top 2 product categories ('cat') have generated the most overall cumulative revenue ('amt')? Display the category names and their total revenue values.
Q12. Select all rows for transactions that sold more individual items ('qty') than the global average quantity sold across the entire table.
Q13. Find all individual transactional records that occurred in stores whose total aggregate revenue across all items combined is greater than $1,500.
Q14. Filter out transactions with a quantity ('qty') of 2 or more. Out of those qualifying rows, group the records by product category, filter the grouped categories to keep only those with a combined total revenue greater than $150, and display only the single highest-earning category.