----(Requirements)----
Products Information:
Store details about various products, including product ID, name, description, and price.
Customer Information:
Maintain customer records with a unique customer ID, first name, last name, password, email,
and address.
Order Processing:
Track orders with an order ID, customer ID, product ID, order date, order status, and total
amount.
Payment Transactions:
Record payment information, including payment ID, amount, payment date, payment method,
order ID, and customer ID.
Sales Data:
Capture sales data, including sales ID, product ID, quantity sold, and total revenue generated.
Shopping Cart Management:
Manage shopping carts, recording cart ID, total items, and total price.
Customer Reviews:
Allow customers to provide reviews, including review ID, customer ID, product ID, rating, and
customer name.
Admin Users:
Store information about admin users, including admin ID, username, password, and email.
Discounted Products:
Maintain a list of discounted products with details such as product ID, name, initial price, and
discounted price.
Stock Levels:
Track stock levels for each product, including product ID, name, and quantity available.
Supplier Information:
Store details about suppliers, including supplier ID, name, and address.
Order Status Tracking:
Keep track of order statuses, such as 'Processing,' 'Shipped,' and 'Delivered.'
Customer Feedback Analysis:
Analyze customer feedback by tracking reviews and ratings for products.
Admin Authentication:
Implement a system for admin user authentication to ensure secure access.
Top Products and Revenue:
Identify and display top-selling products and calculate overall revenue.
=========================================================================================
---(Tables)---
=========================================================================================
--(1). Product table
create table product (
product_id int primary key,
product_name varchar(40),
description_product varchar(100),
price decimal
);
insert into product (product_id, product_name, description_product, price) values
(112, 'Laptop', 'Powerful laptop with high-speed processor', 5000.67),
(212, 'Smartphone', 'Latest smartphone with advanced camera features', 699.99),
(313, 'Headphones', 'Wireless headphones for immersive audio', 129.99),
(512, '4K TV', 'Ultra-high-definition smart TV with a large screen for cinematic
experience', 999.99),
(734, 'Digital Camera', 'Professional-grade digital camera with high-resolution ',
799.99),
(824, 'Wireless Speaker', 'Portable wireless speaker with best sound and waterproof
design', 79.99),
(102, 'Robot Vacuum', 'Smart robot vacuum with mapping technology for efficient
cleaning', 249.99);
select *from product;
Output:
--(2). Customer table
create table customer111 (
customer_id int primary key,
first_name varchar(20),
last_name varchar(20),
password varchar(20),
email varchar(20),
c_address varchar(50)
);
insert into customer111 (customer_id, first_name, last_name, password, email, c_address)
values
(111, 'Hamza', 'Nadeem', 'password1', 'hamza@[Link]', '123 Main Street'),
(222, 'Muhammad', 'Husnain', 'password2', 'hamza@[Link]', '456 Mall road'),
(333, 'Allha', 'Rakha', 'password3', 'hamza@[Link]', '789 china chonk'),
(444, 'Sheraz', 'Chugtai', 'password4', 'hamza@[Link]', '101 Sialkot'),
(555, 'Ali-Hassan', 'Khalid', 'password5', 'hamza@[Link]', '202 Chawinda'),
(666, 'Ateeq', 'Ahmad', 'password6', 'hamza@[Link]', '303 Wazirabad'),
(777, 'Ayan', 'Azhar', 'password7', 'hamza@[Link]', '404 Hall Palace');
select *from customer111;
Output:
--(3). Order table
create table order_table121(
order_id int primary key,
customer_id int,
product_id int,
order_date date,
status varchar(50),
total_amount decimal,
foreign key (customer_id) references customer111(customer_id),
foreign key (product_id) references product(product_id)
);
insert into order_table121 (order_id, customer_id, product_id, order_date,
status, total_amount) values
(221, 111, 112, '2023-01-15', 'Processing', 5000.67),
(222, 222, 212, '2023-01-16', 'Shipped', 699.99),
(223, 777, 313, '2023-01-17', 'Delivered', 129.99),
(224, 444, 512, '2023-01-18', 'Processing', 999.99),
(225, 555, 734, '2023-01-19', 'Shipped', 799.99),
(226, 555, 734, '2023-01-27', 'Delivered', 799.99),
(227, 111, 112, '2023-01-28', 'Delivered', 5000.67),
(228, 666, 824, '2023-01-20', 'Processing', 79.99),
(229, 111, 112, '2023-01-28', 'Delivered', 5000.67),
(230, 222, 212, '2023-01-29', 'Processing', 699.99),
(231, 333, 313, '2023-01-30', 'Shipped', 129.99),
(232, 777, 512, '2023-02-01', 'Processing', 999.99),
(233, 555, 734, '2023-02-05', 'Shipped', 799.99),
(234, 666, 824, '2023-02-10', 'Delivered', 79.99),
(235, 777, 102, '2023-02-15', 'Processing', 249.99),
(236, 777, 102, '2023-01-21', 'Delivered', 249.99);
select *from order_table121;
Output:
--(4). Payment table
create table payment (
payment_id int primary key,
amount decimal,
payment_date date,
payment_method varchar(50),
order_id int,
customer_id int,
foreign key (order_id) references order_table121(order_id),
foreign key (customer_id) references customer111(customer_id)
);
insert into payment (payment_id, amount, payment_date, payment_method, order_id,
customer_id) values
(881, 5000.67, '2023-01-15', 'Credit Card', 221, 111),
(852, 699.99, '2023-01-16', 'PayPal', 222, 222),
(342, 129.99, '2023-01-17', 'Debit Card', 223, 333),
(487, 999.99, '2023-01-18', 'Cash on Delivery', 224, 444),
(504, 799.99, '2023-01-19', 'Credit Card', 225, 555),
(665, 799.99, '2023-01-27', 'Credit Card', 226, 555),
(774, 5000.67, '2023-01-28', 'Credit Card', 227, 111),
(958, 79.99, '2023-01-20', 'PayPal', 228, 666),
(879, 249.99, '2023-01-21', 'Credit Card', 229, 777),
(170, 129.99, '2023-02-01', 'Debit Card', 230, 111),
(113, 999.99, '2023-02-05', 'Cash on Delivery', 231, 222),
(172, 79.99, '2023-02-10', 'Credit Card', 232, 333),
(183, 249.99, '2023-02-15', 'PayPal', 233, 444),
(14, 799.99, '2023-02-20', 'Credit Card', 234, 666);
select * from payment;
Output:
--(5). Sales table
create table sales (
sales_id int primary key,
product_id int,
quantity_sold int,
total_revenue decimal,
foreign key (product_id) references product(product_id)
);
insert into sales (sales_id, product_id, quantity_sold, total_revenue) values
(781, 112, 3, 15002.01),
(265, 212, 2, 1399.98),
(543, 313, 1, 129.99),
(984, 512, 1, 999.99),
(215, 734, 2, 1599.98),
(678, 824, 5, 399.95),
(743, 102, 1, 249.99);
select *from sales;
Output:
--(6). Shopping cart table
create table shopping_cart (
cart_id int primary key,
total_items int,
total_price decimal
);
insert into shopping_cart (cart_id, total_items, total_price) values
(134, 3, 1729.65),
(256, 2, 899.98),
(334, 1, 129.99),
(478, 1, 999.99),
(545, 2, 1799.98),
(656, 5, 479.94),
(457, 1, 249.99);
select *from shopping_cart;
Output:
--(7). Customer feedback table
create table review (
review_id int primary key,
customer_id int,
customer_name varchar(20),
rating int check (rating < 6),
product_id int,
foreign key (customer_id) references customer111(customer_id),
foreign key (product_id) references product(product_id)
);
insert into review (review_id, customer_id, customer_name, rating, product_id)
values
(211, 111, 'Hamza Nadeem', 3, 112),
(132, 222, 'Muhammad Husnain', 4, 212),
(323, 333, 'Allha Rakha', 5, 313),
(423, 444, 'Sheraz Chugtai', 4, 512),
(235, 555, 'Ali-Hassan Khalid', 4, 734),
(236, 666, 'Ateeq Ahmad', 1, 824),
(237, 777, 'Ayan Azhar', 4, 102);
select *from review;
Output:
--(8). Admin table
create table admin1 (
admin_id int primary key,
username varchar(50),
password varchar(50),
email varchar(255)
);
insert into admin1 (admin_id, username, password, email) values
(12, 'Husnain', 'Password121', 'Hunaian@[Link]'),
(11, 'Hamza', 'admin_password', 'hamza@[Link]'),
(21, 'Allha-Rakah', 'admin21', 'allharakha@[Link]');
select *from admin1;
Output:
--(9). Discounted_products table
create table discounted_products (
product_id int,
product_name varchar(20),
initial_price int,
discounted_price int,
foreign key (product_id) references product(product_id)
);
insert into discounted_products (product_id, product_name, initial_price,
discounted_price) values
(112, 'Laptop', 5000.67, 4500.60),
(734, 'Digital Camera', 799.99, 699.99),
(824, 'Wireless Speaker', 79.99, 69.99);
select *from discounted_products;
Output:
--(10). Stock table
create table stock (
product_id int,
product_name varchar(23),
product_quantity int,
foreign key (product_id) references product(product_id)
);
insert into stock (product_id, product_name, product_quantity) values
(112, 'Laptop', 50),
(212, 'Smartphone', 30),
(313, 'Headphones', 40),
(512, '4K TV', 20),
(734, 'Digital Camera', 15),
(824, 'Wireless Speaker', 100),
(102, 'Robot Vacuum', 10);
select *from stock;
Output:
--(11). Supplier table
create table suppliers (
supplier_id int primary key,
supplier_name varchar(20),
supplier_address varchar(30)
);
insert into suppliers (supplier_id, supplier_name, supplier_address) values
(1, 'Supplier 1', '123 Supplier Street'),
(2, 'Supplier 2', '456 Supplier Road'),
(3, 'Supplier 3', '789 Supplier Lane');
select *from suppliers;
Output:
======================================================================
---(Queries)---
======================================================================
---------------------------------------------------------------------------------
/* Here is the queries for the Product and customer table */
---------------------------------------------------------------------------------
--(1). Show the product from lowest price to higher price
select product.product_name, [Link], product.description_product from
product order by [Link] asc;
Output:
--(2). It shows the highest-priced product
select product.product_name, MAX([Link]) as highest_price from product
group by product.product_name;
Output:
--(3). Count the total number of products in the "product" table.
select count(product.product_id) as total_Product from product;
Output:
--(4). It shows the Lowest-priced product
select product.product_name, MIN([Link]) as Lowest_price from product
group by product.product_name;
Output:
--(5). Calculate the average price of all products
select AVG([Link]) as Average_Price from product;
Output:
--(6). Shows the product with their name entered by the user, But it runs only
when the user input the specific name.
SELECT product_id, product_name, price, description_product
FROM product
WHERE product_name IN ('Laptop', 'Smartphone', 'Headphones', '4K TV', 'Digital
Camera', 'Robot Vacuum');
Output:
---------------------------------------------------------------------------------
/* Here is the queries for the Order table */
---------------------------------------------------------------------------------
--(1). Count total number of orders
select count(order_table121.order_id) as total_order from order_table121;
Output:
--(2). Order by the table according to dates
select order_table121.order_id, order_table121.product_id,
order_table121.customer_id from order_table121 order by order_date;
Output:
--(3). How many orders made by each customer
select order_table121.customer_id, count(order_table121.order_id) as order_count
from order_table121
group by order_table121.customer_id
order by order_count desc;
Output:
--(4). Shows those orders that are Processed
select order_table121.order_id, order_table121.customer_id,
order_table121.order_date, order_table121.status from order_table121
where status = 'Processing';
Output:
--(5). Shows those orders that are Delivered
select order_table121.order_id, order_table121.customer_id,
order_table121.order_date, order_table121.status from order_table121
where status = 'Delivered';
Output:
--(6). Shows those orders that are
select order_table121.order_id, order_table121.customer_id,
order_table121.order_date from order_table121
where status = 'Shipped';
Output:
--(7). How many orders made by each user
select
customer111.customer_id,
customer111.first_name,
customer111.last_name,
customer111.c_address,
count(order_table121.order_id) AS total_orders
from
customer111
inner join
order_table121 on order_table121.customer_id = customer111.customer_id
group by
customer111.customer_id
order by
total_orders DESC;
Output:
--(8). Displaying the product information (product_id, product_name, price,
description_product) for products included in orders.
select
product.product_id,
product.product_name,
[Link],
product.description_product
from
product
inner join
order_table121 on order_table121.product_id = product.product_id
order by product.product_id asc;
Output:
--(9). Showing the details of customer and product having more then two order
select
customer111.first_name,
customer111.last_name,
customer111.c_address,
product.product_name,
[Link],
count(order_table121.order_id)
as
Counted_order
from
customer111
inner join
order_table121 on customer111.customer_id = order_table121.order_id
inner join
product on product.product_id = order_table121.order_id
group by
customer111.first_name, customer111.last_name, customer111.c_address,
product.product_name, [Link]
having
count(order_table121.order_id) >2;
Output:
--(10) Displaying the details as which customers buy which product
select
customer111.first_name,
customer111.last_name,
customer111.c_address,
product.product_name,
[Link]
from
customer111
inner join
order_table121 on customer111.customer_id = order_table121.order_id
inner join
product on product.product_id = order_table121.order_id;
Output:
--(11). Showing the details of the customers and product that have dilevered,
processing and shipping the order
select
customer111.customer_id,
customer111.first_name,
customer111.last_name,
product.product_id,
product.product_name,
[Link]
from
customer111
inner join
order_table121 on order_table121.customer_id = customer111.customer_id
inner join
product on order_table121.product_id = product.product_id
where
order_table121.status = 'Delivered';
Output:
--(12). How orders that are delivered, shipped, processed
select order_table121.status, count(order_table121.status) AS total_order_status
from order_table121
where order_table121.status in ('Delivered', 'Shipped', 'Processing')
group by order_table121.status;
Output:
---------------------------------------------------------------------------------
/* Here is the queries for stock table */
---------------------------------------------------------------------------------
--(1). Find products with low stock
select stock.product_id, stock.product_name, stock.product_quantity from stock
where stock.product_quantity < 20;
Output:
--(2). It shows the maximum product availibility
SELECT stock.product_name, MAX(stock.product_quantity) AS quantity
FROM stock GROUP BY stock.product_name having max(stock.product_quantity) >30;
Output:
--(3). It shows the minimum product availibility
select stock.product_name, Min(stock.product_quantity) as Lowest_quantity from
stock
group by stock.product_name having min(stock.product_quantity) = 10;
Output:
--(4). List products with stock levels sorted in ascending order.
select stock.product_id, stock.product_name, stock.product_quantity from stock
order by stock.product_quantity asc;
Output:
--(5). Calculate the average stock quantity across all products.
select AVG(stock.product_quantity) as Average_quantinty from stock;
Output:
---------------------------------------------------------------------------------
/* Here is the queries for ADMIN table */
---------------------------------------------------------------------------------
--(1). Count the total number of admin users.
select COUNT(admin1.admin_id) as total_admin from admin1;
Output:
--(2). Display the specific admin details.
--(first admin)
select admin1.admin_id, [Link], [Link], [Link] from
admin1 where [Link] = 'Hamza';
--(Second admin)
select admin1.admin_id, [Link], [Link], [Link] from
admin1 where [Link] = 'Husnain';
--(Third admin)
select admin1.admin_id, [Link], [Link], [Link] from
admin1 where [Link] = 'Allha-Rakah';
Output:
--(3). Identify admin users with a specific password
--(first admin)
select admin1.admin_id, [Link], [Link], [Link] from
admin1 where [Link] = 'admin_password';
--(Second admin)
select admin1.admin_id, [Link], [Link], [Link] from
admin1 where admin1. password = 'Password121';
--(Third admin)
select admin1.admin_id, [Link], [Link], [Link] from
admin1 where admin1. password = 'admin21';
Output:
---------------------------------------------------------------------------------
/* Here is the queries for the Sales table */
---------------------------------------------------------------------------------
--(1). The total revenue generated by a specific product
select sales.product_id, sales.quantity_sold, sales.total_revenue from sales
where sales.sales_id = 781;
select sales.product_id, sales.quantity_sold, sales.total_revenue from sales
where sales.sales_id = 265;
select sales.product_id, sales.quantity_sold, sales.total_revenue from sales
where sales.sales_id = 543;
select sales.product_id, sales.quantity_sold, sales.total_revenue from sales
where sales.sales_id = 984;
select sales.product_id, sales.quantity_sold, sales.total_revenue from sales
where sales.sales_id = 215;
Output:
--(2). Displaying the maximum quantinty sold of the product
select max(sales.quantity_sold) as maximum_quantity_sold from sales;
Output:
--(3). Displaying the total revenue ganerated by the each product
select sum(sales.total_revenue) as Sum_of_revenues from sales;
Output:
--(4). Calculate the average total revenue across all sales
select avg(sales.total_revenue) as Average_revenue from sales;
Output:
--(5). Which products that are most sold
SELECT top 3
product.product_name,
[Link],
SUM(order_table121.total_amount) AS total_sales
FROM
product
INNER JOIN
order_table121 ON product.product_id = order_table121.product_id
GROUP BY
product.product_name, [Link]
ORDER BY
total_sales DESC;
Output:
---------------------------------------------------------------------------------
/* Here is the queries for the Payment table */
---------------------------------------------------------------------------------
--(1). Displays the total payment methods
select count(distinct payment.payment_method) as total_payment_methods from
payment;
Output:
--(2). Which payment method more popular.
select max(payment.payment_method) as most_chosen_payment_method from payment;
Output:
--(3). Find payments made on a certain date
--This is for first month
select [Link], payment.payment_date, payment.payment_method from payment
where payment.payment_date between '2023-01-15' and '2023-01-28';
Output:
--This is for second month
select [Link], payment.payment_date, payment.payment_method from payment
where payment.payment_date between '2023-02-01' and '2023-02-20';
Output:
--(4). Calculate the total amount of payments received
select sum([Link]) as total_payment_recieved from payment;
Output:
--(5). Showing the details of the customers that have to payment more then two
times
select
customer111.customer_id,
customer111.first_name,
customer111.last_name,
customer111.c_address,
count(payment.payment_id)
as
counted_payment
from
customer111
inner join
payment on payment.customer_id = customer111.customer_id
group by
customer111.customer_id,
customer111.first_name,
customer111.last_name,
customer111.c_address
having
count(payment.payment_id) > 2;
Output:
--(6). Showing the details as "The customer that buy a product, order this
product and pay the payment for it"
select
customer111.first_name,
customer111.last_name,
product.product_name,
[Link],
order_table121.order_date,
order_table121.status,
order_table121.total_amount,
payment.payment_date,
payment.payment_method
from
customer111
inner join
order_table121 on order_table121.customer_id = customer111.customer_id
inner join
product on order_table121.product_id = product.product_id
inner join
payment on payment.order_id = order_table121.order_id;
---------------------------------------------------------------------------------
/* Here is the queries for the Review table */
---------------------------------------------------------------------------------
--(1). Top rated products.
select
product.product_id,
product.product_name,
[Link]
from
product
inner join
review on review.product_id = product.product_id
having
[Link] = 5;
Output:
--(2) It shows the details of the customers and products that are reviewed
select
review.review_id,
customer111.first_name,
customer111.last_name,
product.product_name,
[Link]
from
review
inner join
customer111 on review.customer_id = customer111.customer_id
inner join
product on review.product_id = product.product_id;
Output:
--(3). Low rated products:
select
product.product_id,
product.product_name,
[Link]
from
product
inner join
review on review.product_id = product.product_id
having
[Link] = 1;
Output:
---------------------------------------------------------------------------------
/* Here are the queries for the Shopping cart table */
---------------------------------------------------------------------------------
--(1). Display total items and prices for each shopping cart
select cart_id, total_items, total_price
from shopping_cart;
Output:
--(2). Show shopping carts with more than 3 items
select cart_id, total_items
from shopping_cart
where total_items > 3;
Output:
--(3). List shopping carts in descending order based on total price
select cart_id, total_items, total_price
from shopping_cart
order by total_price desc;
Output:
--(4). Display the details of the shopping cart with the highest total items
select *from shopping_cart
order by total_items desc;
Output:
--(5). Show shopping carts with a total price between $100 and $500
select cart_id, total_items, total_price
from shopping_cart
where total_price between 100 and 500;
Output:
---------------------------------------------------------------------------------
/* Here are the queries for the Discounted Products Table */
---------------------------------------------------------------------------------
--(1). Display all discounted products with their prices
select product_id, product_name, initial_price, discounted_price
from discounted_products;
Output:
--(2). Show discounted products with a discount greater than 10%
select product_id, product_name, initial_price, discounted_price
from discounted_products
where (initial_price - discounted_price) / initial_price > 0.1;
Output:
It shows nothing in return because there is no product that have discount greater
than 10%.
--(3). Show details of the product with the highest discount
select TOP 1 product_id, product_name, initial_price, discounted_price
from discounted_products
order by (initial_price - discounted_price) desc;
Output:
---------------------------------------------------------------------------------
/* Here are the queries for the Discounted Products Table */
---------------------------------------------------------------------------------
--(1). Display the total number of suppliers
select count(supplier_id) as total_suppliers
from suppliers;
Output:
order_table121 discounted_products
order_id product_id
customer_id product_name
product_id initial_price
order_date discounted_price
status
total_amount
customer111
customer_id
first_name
last_name
product
password
product_id stock
email product_id
product_name
c_address
payment description_product
product_name
payment_id product_quantity
price
amount
payment_date
payment_method
order_id
review customer_id
review_id
sales
customer_id
sales_id
customer_name
product_id
rating
quantity_sold
product_id
total_revenue
