SQL CASE STUDY of a cycle company using SQL & Power BI

-- Table: Product

DROP TABLE IF EXISTS Product;

CREATE TABLE Product (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(100),

    Category VARCHAR(50),

    Price DECIMAL(10, 2)

);


-- Table: Stores

DROP TABLE IF EXISTS Stores;

CREATE TABLE Stores (

    StoreID INT PRIMARY KEY,

    StoreName VARCHAR(100),

    Location VARCHAR(100),

    StoreType VARCHAR(50)

);


-- Table: Customers

DROP TABLE IF EXISTS Customers;

CREATE TABLE Customers (

    CustomerID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Age INT,

    Gender VARCHAR(10),

    City VARCHAR(100)

);


-- Table: Sales

DROP TABLE IF EXISTS Sales;

CREATE TABLE Sales (

    SaleID INT PRIMARY KEY,

    ProductID INT,

    StoreID INT,

    CustomerID INT,

    SaleDate DATE,

    QuantitySold INT,

    TotalRevenue DECIMAL(10, 2),

    PromotionApplied VARCHAR(10) 

);


 INSERT INTO Product (ProductID, ProductName, Category, Price) VALUES

(1, 'Mountain Explorer', 'Mountain Bike', 1200.00),

(2, 'City Cruiser', 'Hybrid Bike', 850.00),

(3, 'Speedster', 'Road Bike', 1500.00),

(4, 'Trail Blazer', 'Mountain Bike', 1300.00),

(5, 'Eco Rider', 'Hybrid Bike', 700.00),

(6, 'Road Warrior', 'Road Bike', 1600.00),

(7, 'Urban Commuter', 'Hybrid Bike', 900.00),

(8, 'Hill Master', 'Mountain Bike', 1400.00),

(9, 'Touring Pro', 'Road Bike', 1700.00),

(10, 'Adventure Seeker', 'Mountain Bike', 1350.00);


INSERT INTO Stores (StoreID, StoreName, Location, StoreType) VALUES

(1, 'Downtown Bikes', 'New York', 'Physical'),

(2, 'Urban Wheels', 'San Francisco', 'Physical'),

(3, 'BikeHub Online', 'Online', 'Online'),

(4, 'Cycle World', 'Chicago', 'Physical'),

(5, 'GreenCycles', 'Seattle', 'Physical'),

(6, 'Pedal Pushers', 'Los Angeles', 'Physical'),

(7, 'BikeNation', 'Houston', 'Physical'),

(8, 'CycleCity', 'Miami', 'Physical'),

(9, 'Spin Zone', 'Boston', 'Physical'),

(10, 'Velocity Cycles', 'Denver', 'Physical');


INSERT INTO Customers (CustomerID, FirstName, LastName, Age, Gender, City) VALUES

(1, 'John', 'Doe', 28, 'Male', 'New York'),

(2, 'Jane', 'Smith', 34, 'Female', 'San Francisco'),

(3, 'Emily', 'Jones', 22, 'Female', 'Chicago'),

(4, 'Michael', 'Brown', 40, 'Male', 'Seattle'),

(5, 'Sarah', 'Wilson', 29, 'Female', 'New York'),

(6, 'David', 'Taylor', 31, 'Male', 'Los Angeles'),

(7, 'Anna', 'White', 25, 'Female', 'Houston'),

(8, 'Chris', 'Green', 37, 'Male', 'Miami'),

(9, 'Laura', 'Hall', 30, 'Female', 'Boston'),

(10, 'Tom', 'Davis', 45, 'Male', 'Denver');


INSERT INTO Sales (SaleID, ProductID, StoreID, CustomerID, SaleDate, QuantitySold, TotalRevenue, PromotionApplied) VALUES

(1, 1, 1, 1, '2024-01-15', 1, 1200.00, 'No'),

(2, 3, 2, 2, '2024-02-20', 1, 1500.00, 'Yes'),

(3, 2, 3, 3, '2024-03-10', 2, 1700.00, 'No'),

(4, 4, 4, 4, '2024-04-05', 1, 1300.00, 'Yes'),

(5, 5, 5, 5, '2024-05-22', 3, 2100.00, 'No'),

(6, 6, 6, 6, '2024-06-18', 1, 1600.00, 'Yes'),

(7, 7, 7, 7, '2024-07-25', 2, 1800.00, 'No'),

(8, 8, 8, 8, '2024-08-10', 1, 1400.00, 'No'),

(9, 9, 9, 9, '2024-09-12', 1, 1700.00, 'Yes'),

(10, 10, 10, 10, '2024-10-15', 2, 2700.00, 'No'),

(11, 1, 1, 6, '2024-11-20', 1, 1200.00, 'Yes'),

(12, 3, 2, 7, '2024-12-10', 1, 1500.00, 'No'),

(13, 2, 3, 8, '2024-01-25', 2, 1700.00, 'No'),

(14, 4, 4, 9, '2024-02-05', 1, 1300.00, 'Yes'),

(15, 5, 5, 10, '2024-03-15', 3, 2100.00, 'No'),

(16, 6, 6, 1, '2024-04-18', 1, 1600.00, 'Yes'),

(17, 7, 7, 2, '2024-05-22', 2, 1800.00, 'No'),

(18, 8, 8, 3, '2024-06-25', 1, 1400.00, 'No'),

(19, 9, 9, 4, '2024-07-30', 1, 1700.00, 'Yes'),

(20, 10, 10, 5, '2024-08-05', 2, 2700.00, 'No'),

(21, 1, 1, 6, '2024-09-10', 1, 1200.00, 'No'),

(22, 3, 2, 7, '2024-10-20', 1, 1500.00, 'Yes'),

(23, 2, 3, 8, '2024-11-15', 2, 1700.00, 'No'),

(24, 4, 4, 9, '2024-12-18', 1, 1300.00, 'Yes'),

(25, 5, 5, 10, '2024-01-22', 3, 2100.00, 'No'),

(26, 6, 6, 1, '2024-02-25', 1, 1600.00, 'Yes'),

(27, 7, 7, 2, '2024-03-05', 2, 1800.00, 'No'),

(28, 8, 8, 3, '2024-04-10', 1, 1400.00, 'No'),

(29, 9, 9, 4, '2024-05-15', 1, 1700.00, 'Yes'),

(30, 10, 10, 5, '2024-06-20', 2, 2700.00, 'No');

Comments

Post a Comment

Popular posts from this blog

50 Essential SQL Questions to Land Your Dream Job

 CREATE TABLE worker (     first_name VARCHAR(50),     last_name VARCHAR(50),     department VARCHAR(50),     salary DECIMAL(10, 2),     joining_date DATE ); INSERT INTO worker (first_name, last_name, department, salary, joining_date) VALUES  ('John', 'Doe', 'HR', 55000, '2020-01-15'), ('Jane', 'Smith', 'Finance', 67000, '2019-03-22'), ('Robert', 'Johnson', 'IT', 72000, '2018-06-10'), ('Emily', 'Davis', 'Marketing', 59000, '2021-07-01'), ('Michael', 'Wilson', 'Sales', 63000, '2020-09-15'), ('Sarah', 'Brown', 'HR', 56000, '2022-02-19'), ('David', 'Miller', 'Finance', 68000, '2019-10-05'), ('Laura', 'Garcia', 'IT', 75000, '2020-11-22'), ('James', 'Martinez', 'Marketing', 60000, '2021-03-18'), ('Olivia...
Read more

How to find all the customers who placed orders on three consecutive days | SQL Scenario questions

  DML Script: DROP TABLE IF EXISTS Orders CREATE TABLE Orders ( customer_name VARCHAR(255), order_date DATETIME ); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-05'); INSER...
Read more

ACCENTURE SQL INTERVIEW QUESTION | Change the ProductIDs

 DROP TABLE IF EXISTS Products; CREATE TABLE Products (     ProductID INT PRIMARY KEY,     Product VARCHAR(255),     Category VARCHAR(100) ); INSERT INTO Products (ProductID, Product, Category) VALUES     (1, 'Laptop', 'Electronics'),     (2, 'Smartphone', 'Electronics'),     (3, 'Tablet', 'Electronics'),     (4, 'Headphones', 'Accessories'),     (5, 'Smartwatch', 'Accessories'),     (6, 'Keyboard', 'Accessories'),     (7, 'Mouse', 'Accessories'),     (8, 'Monitor', 'Accessories'),     (9, 'Printer', 'Electronics');
Read more