DBMS Prac 2

create database shalPrac
use shalPrac

-- Create Customers Table
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    email VARCHAR(255),
    phone_number VARCHAR(15)
);

-- Insert Data into Customers Table
INSERT INTO Customers (customer_id, customer_name, email, phone_number)
VALUES
    (1, 'John Smith', 'john.smith@example.com', '123-456-7890'),
    (2, 'Jane Doe', 'jane.doe@example.com', '987-654-3210'),
    (3, 'Alex Johnson', 'alex.johnson@example.com', '555-555-5555'),
    (4, 'Emily Brown', 'emily.brown@example.com', '444-444-4444'),
    (5, 'Michael Lee', 'michael.lee@example.com', '333-333-3333');

-- Create Products Table
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(255),
    price DECIMAL(10, 2)
);

-- Insert Data into Products Table
INSERT INTO Products (product_id, product_name, category, price)
VALUES
    (101, 'Laptop', 'Electronics', 800.00),
    (102, 'T-Shirt', 'Clothing', 20.00),
    (103, 'Book', 'Books', 15.00),
    (104, 'Smartphone', 'Electronics', 600.00),
    (105, 'Sneakers', 'Shoes', 80.00);

-- Create Orders Table
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

-- Insert Data into Orders Table
INSERT INTO Orders (order_id, customer_id, order_date)
VALUES
    (1, 1, '2023-01-15'),
    (2, 2, '2023-02-01'),
    (3, 3, '2023-03-10'),
    (4, 4, '2023-03-25'),
    (5, 5, '2023-04-05');

-- Create Order_Details Table
CREATE TABLE Order_Details (
    order_detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

-- Insert Data into Order_Details Table
INSERT INTO Order_Details (order_detail_id, order_id, product_id, quantity)
VALUES
    (1, 1, 101, 2),
    (2, 1, 103, 5),
    (3, 2, 102, 3),
    (4, 3, 104, 1),
    (5, 4, 101, 4);

-- Create Reviews Table
CREATE TABLE Reviews (
    review_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    rating INT,
    comment TEXT,
    FOREIGN KEY (product_id) REFERENCES Products(product_id),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

-- Insert Data into Reviews Table
INSERT INTO Reviews (review_id, product_id, customer_id, rating, comment)
VALUES
    (1, 101, 1, 4, 'Great laptop!'),
    (2, 102, 2, 5, 'Love the T-shirt!'),
    (3, 101, 3, 3, 'Decent laptop.'),
    (4, 104, 4, 5, 'Excellent smartphone.'),
    (5, 101, 5, 4, 'Good laptop for the price');

SELECT
  *
FROM
  INFORMATION_SCHEMA.TABLES;
GO

select * from Customers
select * from Order_Details
select * from Orders
select * from Products
select * from Reviews
select * from Orders,Order_Details
    where Order_Details.order_id = Orders.order_id

--1
select Customers.customer_name from Orders, Customers
        where Orders.customer_id = Customers.customer_id and
        order_id in (select top 5 order_id from Order_Details
                group by order_id
                order by count(order_id) desc)

--2
select Products.product_name, avg(rating) from Reviews, Products
    where Reviews.product_id = Products.product_id
    group by product_name
    order by avg(rating) desc;

--3
select sum(price * quantity) as Total from Order_Details od, Products p, Orders o
    where od.product_id = p.product_id and o.order_id = od.order_id and year(o.order_date) = 2023

--4
with sq as
(select o.customer_id, (p.price * od.quantity) "Sub"
    from Products p, Order_Details od, Orders o
    where p.product_id = od.product_id
    and o.order_id = od.order_id
    )
select sq.customer_id from sq
    group by sq.customer_id
    having sum(Sub) >= ALL (Select sum(s.sub) from sq s group by s.customer_id)

--5
select top 1 category, sum(price * quantity) "TotalSales" from Order_Details od, Products p, Orders o
    where od.product_id = p.product_id
    and o.order_id = od.order_id
    and year(o.order_date) = 2023
    group by category
    order by TotalSales desc

--6
select order_id, avg(quantity) from Order_Details
    group by order_id

--7
select product_name from Products
    where Products.product_id not in
    (select product_id from Reviews)

--8
select customer_id, count(customer_id) "count" from Reviews
    group by customer_id
    having count(customer_id) >= ALL (select count(customer_id) "counts" from Reviews
                            group by customer_id)

--9
select category, sum(price * quantity) "TotalSales"
    from Order_Details od, Products p, Orders o
    where od.product_id = p.product_id
    and o.order_id = od.order_id
    group by category

--10
select p.category, p.product_name, maxprice
    from Products p, Order_Details o,
    (select category, max(p.price * od.quantity) "maxprice"
        from Order_Details od, Products p
        where od.product_id = p.product_id
        group by category)
        as maxtable
    where (p.price * o.quantity) = maxtable.maxprice
    and p.category = maxtable.category