Advanced SQL (Set Up SQL Database)- part 1 of 30

Показать описание

In this video, i will be taking you to a 30 days series of Complex more advanced Analytical SQL queries that will help you prepare for your Big Tech Interviews. Episode 1 is setting up the database and how we can create advanced sql queries based on the given database and tables.
Queries Include Complex Functions, CTE, SubQuery, Joins, Aggregate columns etc. Pls subscribe for the amazing content and get ready to land on the SQL Coding/ Business Analyst Job.

Here is the material to create tables and records for tables:
-- Create the Customers table
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name varchar(50),
email varchar(50),
phone_number varchar(50)
);

-- Create the Products table
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name varchar(50),
product_category varchar(30)
);

-- Create the Orders table
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Create the Sales table
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY,
order_id INTEGER,
product_id INTEGER,
amount varchar(max),
transaction_date DATE,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Create the Reviews table
CREATE TABLE reviews (
review_id INTEGER PRIMARY KEY,
product_id INTEGER,
rating varchar(2),
review_date DATE,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-----------------------------------

INSERT INTO customers (customer_id, customer_name, email, phone_number)
VALUES

INSERT INTO products (product_id, product_name, product_category)
VALUES
(1, 'Laptop', 'Electronics'),
(2, 'Smartphone', 'Electronics'),
(3, 'Tablet', 'Electronics'),
(4, 'Headphones', 'Electronics'),
(5, 'Smartwatch', 'Electronics');

INSERT INTO orders (order_id, customer_id, order_date)
VALUES
(1, 1, '2023-01-15'),
(2, 2, '2023-01-20'),
(3, 3, '2023-01-25'),
(4, 4, '2023-02-02'),
(5, 5, '2023-02-10');

INSERT INTO sales (sale_id, order_id, product_id, amount, transaction_date)
VALUES
(1, 1, 1, 1200.00, '2023-01-15'),
(2, 2, 2, 25.00, '2023-01-20'),
(3, 3, 3, 500.00, '2023-01-25'),
(4, 4, 4, 150.00, '2023-02-02'),
(5, 5, 5, 399.99, '2023-02-10');

INSERT INTO reviews (review_id, product_id, rating, review_date)
VALUES
(1, 1, 4.5, '2023-01-16'),
(2, 2, 3.0, '2023-01-21'),
(3, 3, 4.7, '2023-01-26'),
(4, 4, 4.0, '2023-02-03'),
(5, 5, 4.8, '2023-02-11');

you can add up to 40 records for this database.