queries.sql

-- Create Tables 
Drop Table if EXISTS Books;
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
);

Drop Table if exists Customers;
CREATE TABLE Customers(
Customer_Id Serial PRIMARY KEY,
Name Varchar(100),
Email Varchar(100),
Phone Varchar(15),
City Varchar(50),
Country Varchar(150)
);

Drop Table if exists Orders;
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;
-- Export CSV files directly from Import/Export data


--Queries

--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 Canada
Select * from Customers
where country='Canada';

--4)Show Orders placed in November 2023
Select * from Orders
where order_date BETWEEN '2023-11-01' AND '2023-11-30';

--5)Retrieve Total Stock of Book Available
Select sum(stock) AS Total_stock
from Books;

--6)Find details of the most expensive Books
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 genre available in the Books table
Select DISTINCT(genre) from Books

--10)Find book with the lowest stock
Select * from Books 
Order by stock 
LIMIT 1;

--11)Calculate the total revenue generated from all orders
Select sum(Total_amount) AS Total_Revenue
from Orders;

--Advanced Queries

--1)Retrieve total number of books sold for each genre
Select sum(o.quantity) AS Total_Books_Sold,b.genre 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 customer_id,count(Order_id) as Order_Count
from Orders
Group by customer_id
having count(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 count(o.order_id) DESC
LIMIT 1;


--5)Show the top 3 most expensive books of 'Fantasy' genre
Select book_id,title,price
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)
from Orders o
join books b
on b.book_id=o.book_id
group by b.author

--7)List the cities where customers who spent over $30 are located
Select DISTINCT(c.city),total_amount
from customers c
join orders o
on c.customer_id=o.order_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 c.customer_id=o.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 ordered_quantity,
b.stock-COALESCE(SUM(o.quantity),0) AS Remained_Qty
from books b
Left join Orders o
on o.book_id=b.book_id
Group by b.book_id