library.sql

create database library;
use library;

show tables;

select * from books;

select * from books where genre = 'fiction';
select * from books where published_year > 1950;
select * from books where stock>0 order by stock desc;
select * from books order by price desc limit 1;
select distinct genre from books;
select * from books order by stock asc limit 1;

select * from customers where country = 'canada';

select * from orders where order_date like '2023-11%';

select count(stock) as total_count_of_books from books;

select * from orders where quantity>1 order by quantity desc;

select * from orders where total_amount>20 order by total_amount desc;

select sum(total_amount) as total_reveneue from orders;

SELECT customer_id
FROM orders 
where quantity>=2;

select customer_id, sum(total_amount) AS total_spent
from orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 1;

----- why this work or not
update books set stock = 20 where Book_ID = 6;
UPDATE books SET Stock = 20 WHERE Book_ID = 6 limit 1;

select * from books where stock between 90 and 100 order by stock desc;

select * from books where stock in (80,90,100) order by stock desc;

select distinct published_year from books order by published_year desc;

-- why the published_year repeat
select distinct published_year, author from books order by published_year desc;

-- both column desc order 
select stock, price from books order by stock desc, price desc;

select stock, price, (stock*price) as total_price from books order by total_price desc;

-- create view so we can run it without query
create view total_stock_remaining as
select sum(stock) as total_stock from books;
select * from total_stock_remaining;

select avg(stock) as average_stock from books;

select title, sum(stock) as total_sum_of_stock, sum(price) as total_revenue from books group by title;

select sum(stock) from books;

select * from orders;

select customer_id, count(book_id) as total_sum_of_books, sum(total_amount) as total_spent from orders
group by customer_id order by customer_id desc;

-- confusion
select * from orders where quantity order by quantity desc limit 20;

select book_id, count(customer_id) as total_customer from orders group by book_id order by total_customer desc, book_id desc;

-- confusion
select book_id, count(quantity) as total_order, sum(total_amount) as total_amount from orders
group by Book_id order by total_order desc, book_id desc;

SELECT 
  book_id, 
  SUM(quantity) AS total_quantity, 
  COUNT(*) AS total_orders, 
  SUM(total_amount) AS total_amount 
FROM orders
GROUP BY book_id 
ORDER BY total_quantity DESC, book_id DESC;