AnjaliKolambkar_Int1048_sql.sql

create database Anjali1048

use Anjali1048

---SQL ASSESSMENT 2 

1. CUSTOMERS TABLE
create table Customers
(
    CustomerID INT primary key,
    CustomerName VARCHAR(50),
    City VARCHAR(20),
    JoinDate DATE
);

---Insert Values
INSERT INTO Customers VALUES
(1,'Suresh','Vadodara','2024-05-18'),
(2,'Mahesh','Rajkot','2025-02-26'),
(3,'Jayesh','Vadodara','2026-01-28'),
(4,'Ramesh','Ahmedabad','2023-05-08'),
(5,'Kiran','Surat','2022-03-15');

SELECT * FROM Customers;

---2. PRODUCTS TABLE
create table  Products
(
    ProductID INT primary key ,
    ProductName VARCHAR(50),
    Category VARCHAR(30),
    Price INT
);

---Insert Values in product table 
INSERT INTO Products VALUES
(101,'Laptop','Electronics',28000),
(102,'Mobile','Electronics',19000),
(103,'TV','Electronics',25000),
(104,'Earbuds','Accessories',4000),
(105,'Bluetooth Speaker','Accessories',2000);

SELECT * FROM Products;

---3. ORDERS TABLE
CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    OrderDate DATE,
    Quantity INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

---Insert Orders values 
INSERT INTO Orders VALUES
(1001,1,101,'2025-01-10',2),
(1002,2,102,'2025-01-15',1),
(1003,1,104,'2025-02-01',5),
(1004,3,103,'2025-02-20',3),
(1005,4,105,'2025-03-05',7);

SELECT * FROM Orders;

---1. Retrieve a list of all orders. Show the CustomerName, the ProductName, and the OrderDate 
SELECT c.customerName, p.productName, o.orderDate
from Orders o
join Customers c ON 
o.CustomerID = c.CustomerID
join Products p ON 
o.ProductID = p.ProductID;

---2. Calculate the total revenue generated by each product category. Show the Category  and the TotalRevenue 
select p.Category, SUM( P.Price * o.Quantity) AS TotalRevenue
from Orders o
JOIN Products p ON o.ProductID = p.ProductID
group by  p.Category;

---3. List all customers who have never placed an order. 
SELECT c.customerName
FROM customers c
   left join orders o ON c.customerID = o.CustomerID
where o.OrderID IS NULL;

---4. Select all customers and their order IDs. If they have no orders, the OrderID will  naturally be NULL. 
SELECT c.CustomerName, o.OrderID
FROM Customers c
left join Orders O ON
c.CustomerID = o.CustomerID;

---5. Find the names of customers who have bought more than 10 items in total. 
SELECT c.CustomerName
FROM Orders o
join Customers C ON o.CustomerID = c.CustomerID
group by  C.CustomerName
having sum (o.Quantity) > 10;

---6. Display a list of all Products and the OrderID associated with them. This list must  include products that have never been sold.  
SELECT p.ProductName, o.OrderID

FROM Products p

left join  Orders o ON p.ProductID = o.ProductID;

---7. List all ProductNames that have been ordered by customers living in 'Vadodara’. 
SELECT DISTINCT p.productName
FROM Orders o
 join Customers c ON o.CustomerID = c.CustomerID
 join Products p ON o.ProductID = p.ProductID
where c.City = 'Vadodara';

---8. Display a list of all Product Names and their Prices, sorted from the cheapest to the  most expensive.
SELECT ProductName, Price
FROM Products
ORDER BY Price ASC;