01_INITIALISE.sql

-- This file was created by SleekData (https://www.youtube.com/@sleekdata)
-- You may use it for personal, educational purposes only.
-- Unauthorized commercial use of this file or its data, including use in other YouTube videos, is prohibited.

-- Create database
CREATE DATABASE IF NOT EXISTS SLEEKMART_OMS;

-- Use the database
USE DATABASE SLEEKMART_OMS;

-- Create schema
CREATE SCHEMA IF NOT EXISTS L1_LANDING;

-- CUSTOMERS table
CREATE TABLE IF NOT EXISTS L1_LANDING.CUSTOMERS (
    CUSTOMERID VARCHAR(10),
    FIRSTNAME VARCHAR(50),
    LASTNAME VARCHAR(50),
    EMAIL VARCHAR(100),
    PHONE VARCHAR(50),
    ADDRESS VARCHAR(100),
    CITY VARCHAR(50),
    STATE VARCHAR(2),
    ZIPCODE VARCHAR(10),
    UPDATED_AT TIMESTAMP_NTZ
);

-- DATES table
CREATE TABLE IF NOT EXISTS L1_LANDING.DATES (
    DATE DATE,
    DAY INTEGER,
    MONTH INTEGER,
    YEAR INTEGER,
    QUARTER INTEGER,
    DAYOFWEEK VARCHAR(20),
    WEEKOFYEAR INTEGER,
    UPDATED_AT TIMESTAMP_NTZ
);

-- EMPLOYEES table
CREATE TABLE IF NOT EXISTS L1_LANDING.EMPLOYEES (
    EMPLOYEEID INTEGER,
    FIRSTNAME VARCHAR(50),
    LASTNAME VARCHAR(50),
    EMAIL VARCHAR(100),
    JOBTITLE VARCHAR(50),
    HIREDATE DATE,
    MANAGERID INTEGER,
    ADDRESS VARCHAR(100),
    CITY VARCHAR(50),
    STATE VARCHAR(2),
    ZIPCODE VARCHAR(10),
    UPDATED_AT TIMESTAMP_NTZ
);

-- PRODUCTS table
CREATE TABLE IF NOT EXISTS L1_LANDING.PRODUCTS (
    PRODUCTID INTEGER,
    NAME VARCHAR(100),
    CATEGORY VARCHAR(50),
    RETAILPRICE INTEGER,
    SUPPLIERPRICE INTEGER,
    SUPPLIERID INTEGER,
    UPDATED_AT TIMESTAMP_NTZ
);

-- SUPPLIERS table
CREATE TABLE IF NOT EXISTS L1_LANDING.SUPPLIERS (
    SUPPLIERID INTEGER,
    SUPPLIERNAME VARCHAR(100),
    CONTACTPERSON VARCHAR(100),
    EMAIL VARCHAR(100),
    PHONE VARCHAR(30),
    ADDRESS VARCHAR(100),
    CITY VARCHAR(50),
    STATE VARCHAR(10),
    ZIPCODE VARCHAR(20),
    UPDATED_AT TIMESTAMP_NTZ
);

-- STORES table
CREATE TABLE IF NOT EXISTS L1_LANDING.STORES (
    STOREID VARCHAR(10),
    STORENAME VARCHAR(50),
    ADDRESS VARCHAR(100),
    CITY VARCHAR(50),
    STATE VARCHAR(10),
    ZIPCODE VARCHAR(20),
    EMAIL VARCHAR(100),
    PHONE VARCHAR(30),
    UPDATED_AT TIMESTAMP_NTZ
);

-- ORDERITEMS table
CREATE TABLE IF NOT EXISTS L1_LANDING.ORDERITEMS (
    ORDERID INT,
    ORDERITEMID INT,
    PRODUCTID INT,
    QUANTITY INT,
    UNITPRICE NUMERIC(10, 2),
    UPDATED_AT TIMESTAMP_NTZ
);

-- ORDERS table
CREATE TABLE IF NOT EXISTS L1_LANDING.ORDERS (
    ORDERID INT,
    ORDERDATE DATE,
    CUSTOMERID VARCHAR(10),
    EMPLOYEEID INT,
    STOREID VARCHAR(10),
    STATUS VARCHAR(2),
    UPDATED_AT TIMESTAMP_NTZ
);