task3_analysis.sql

-- =========================================
-- Task 3: SQL Basics
-- Database: task3_db
-- Tool: MySQL Workbench
-- Description:
-- This script demonstrates basic SQL operations:
-- database creation, filtering, sorting, aggregation,
-- grouping, and conditional filtering using HAVING.
-- =========================================

-- Create a new database for Task 3
CREATE DATABASE task3_db;

-- Select the database to work on
USE task3_db;

-- Create the sales table to store order data
CREATE TABLE sales (
    order_id VARCHAR(20),
    order_date VARCHAR(20),
    category VARCHAR(50),
    sub_category VARCHAR(50),
    sales DECIMAL(10,2),
    profit DECIMAL(10,2),
    region VARCHAR(50),
    customer_name VARCHAR(100)
);

-- View the structure of the sales table
DESCRIBE sales;

-- (Used during testing) Delete the sales table if needed
DROP TABLE sales;

-- Count total number of records in the sales table
SELECT COUNT(*) FROM sales;

-- Preview the first 5 records in the sales table
SELECT * FROM sales LIMIT 5;

DESCRIBE sales;

-- Select specific columns from the sales table
SELECT `Order Id`, Sales, Profit FROM sales;

-- Filter records where the category is Technology
SELECT *
FROM sales
WHERE category = 'Technology';

-- Filter records where profit is greater than 500
SELECT *
FROM sales
WHERE profit > 500;

-- Filter Technology category orders with profit greater than 500
SELECT *
FROM sales
WHERE category = 'Technology'
AND profit > 500;

-- Sort all records by sales in descending order (highest sales first)
SELECT *
FROM sales
ORDER BY sales DESC;

-- Sort all records by profit in ascending order (lowest profit first)
SELECT *
FROM sales
ORDER BY profit ASC;

-- Filter Technology category and sort by highest sales
SELECT *
FROM sales
WHERE category = 'Technology'
ORDER BY sales DESC;

-- AGGREGATION QUERIES (SUMMARY REPORTS)
-- Calculate total sales for each category
SELECT category, SUM(sales) AS total_sales
FROM sales
GROUP BY category;

-- Calculate average profit for each region
SELECT region, AVG(profit) AS avg_profit
FROM sales
GROUP BY region;

-- Count total number of orders in each category
SELECT category, COUNT(*) AS total_orders
FROM sales
GROUP BY category;

-- Display categories where total sales exceed 100000
SELECT category, SUM(sales) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(sales) > 100000;

-- PATTERN MATCHING USING LIKE
SELECT *
FROM sales
WHERE 'Customer Name' LIKE '%Singh%';

SELECT category, SUM(sales)
FROM sales
GROUP BY category;