sql project.sql

CREATE TABLE books(
	Book_ID SERIAL PRIMARY KEY,
	Title VARCHAR(100),
	Author VARCHAR(100),
	Genre VARCHAR(50),
	Published_Year INT,
	Price NUMERIC(10,2),
	Stock INT
);
CREATE TABLE Customers (
	Customer_ID SERIAL PRIMARY KEY,
	Name VARCHAR(100),
	Email VARCHAR(50),
	Phone VARCHAR(15),
	City VARCHAR(40),
	Country VARCHAR(150)
);

CREATE TABLE Orders (
	Order_ID SERIAL PRIMARY KEY,
	Customer_ID INT REFERENCES Customers(Customer_ID),
	Book_ID INT REFERENCES Books(BOOK_ID),
	Order_Date DATE,
	Quantity INT,
	Total_Amount NUMERIC(10,2)
);

SELECT * FROM Books;
SELECT * FROM Customers;
SELECT * FROM Orders;

-- Import Data Into Book Table
COPY Books(Book_ID,Title,Author,Genre,Published_Year,Price,Stock)
FROM 'C:/Users/omkar/Downloads/project/Books.csv'
CSV HEADER;


COPY Customers(Customer_ID,Name,Email,Phone,City,Country)
FROM 'C:/Users/omkar/Downloads/project/Customers.csv'
CSV HEADER;

COPY Orders(Order_ID,Customer_ID,Book_ID,Order_Date,Quantity,Total_Amount)
FROM 'C:/Users/omkar/Downloads/project/Orders.csv'
CSV HEADER;

SELECT * FROM Books;
SELECT * FROM Customers;
SELECT * FROM Orders;


-- 1) Retrieve all books in the "Fiction" genre

SELECT * FROM Books
WHERE genre='Fiction';

-- 2) Find books published after the year 1950

SELECT * FROM Books 
WHERE Published_Year>1950;

-- 3) List all customers from the Canada

SELECT * FROM Customers 
WHERE Country='Canada';

--	4) Show orders placed in November 2023

	SELECT * FROM Orders
	WHERE Order_Date  BETWEEN '2023-11-1' AND '2023-11-30';


-- 5) Retrieve the total stock of books available

SELECT SUM(Stock) AS Total_Stock FROM books;

--6) Find the details of the most expensive book

	SELECT * FROM Books ORDER BY Price DESC LIMIT 1;

--7) Show all customers who ordered more than 1 quantity of a book

	SELECT * FROM Orders 
	WHERE quantity>1;

--8) Retrieve all orders where the total amount exceeds $20

	SELECT * FROM Orders 
	WHERE total_amount>20;

--9)List all genres available in the Books table

	SELECT DISTINCT Genre From Books;

--10) Find the book with the lowest stock

	SELECT * FROM Books ORDER BY Stock ASC LIMIT 1;
	

--11) Calculate the total revenue generated from all orders
	
	SELECT SUM(Total_amount) as Total_revenue FROM Orders;


--- Advance Questions :

-- 1) Retrieve the total number of books sold for each genre

	SELECT b.Genre, SUM(o.Quantity) AS Total_books_Sold  FROM Orders o
	JOIN Books b  ON o.book_id=b.book_id
	group by Genre;

-- 2) Find the average price of books in the "Fantasy" genre

	SELECT AVG(price) as AVG_Price FROM books
	WHERE genre='Fantasy';

-- 3) List customers who have placed at least 2 orders

	SELECT c.name ,o.customer_id,COUNT(o.order_id) as ORDER_COUNT from orders o
	JOIN customers c ON o.customer_id=c.customer_id
	GROUP BY o.customer_id,c.name
	HAVING COUNT(o.order_id)>2;
	
-- 4) Find the most frequently ordered book

	SELECT o.book_id,b.title,COUNT(o.order_id) as ORDER_COUNT FROM orders o
	JOIN books b ON o.book_id=b.book_id
	group by o.book_id ,b.title
	ORDER BY ORDER_COUNT DESC LIMIT 1;

-- 5) Show the top 3 most expensive books of 'Fantasy' Genre

	SELECT * FROM Books
	WHERE genre='Fantasy'
	ORDER BY Price DESC LIMIT 3;

-- 6) Retrieve the total quantity of books sold by each author

	SELECT b.author,SUM(o.quantity) as Total_books_sold from orders o
	JOIN books b ON b.book_id=b.book_id
	GROUP BY b.author;

-- 7) List the cities where customers who spent over $30 are located

	SELECT DISTINCT city,o.total_amount
	FROM orders o 
	JOIN Customers c ON o.customer_id=c.customer_id
	WHERE o.total_amount >30;

	
-- 8) Find the customer who spent the most on orders

	SELECT c.customer_id ,c.name, SUM(o.total_amount) as Total_spent
	FROM customers c  
	JOIN Orders o on o.customer_id=c.customer_id
	GROUP BY c.customer_id ,c.name
	ORDER BY total_spent DESC  LIMIT 1;
	
-- 9) Calculate the stock remaining after fulfilling all orders

	SELECT b.book_id ,b.title,b.stock,
	COALESCE(SUM(o.quantity),0) as Order_quantity,
	b.stock - COALESCE(SUM(o.quantity),0) as Remaning_Quantity
	FROM books b
	LEFT JOIN orders o ON b.book_id=o.book_id
	GROUP BY  b.book_id
	ORDER BY b.book_id;