Task5_OnlineBookshop.sql

--An online bookstore wants to manage its inventory, track customer purchases, and analyze sales data. 
--You are tasked with designing a table that stores information about books and querying it using various SQL techniques.

create database Online_Bookstore;
use Online_Bookstore;

--1.Create a table called Books with the following columns:
create table books(
book_id int Primary Key NOT NULL,
title Text NOT NULL,
author Text,
genre varchar(50),
price DECIMAL(10,2) not null,
stock_quantity Int,
rating Decimal(3,2),
book_language VARCHAR(100),
publisher VARCHAR(255),
year_published Int,
);

--2.Insert at least 10 meaningful records into the Books table using INSERT INTO.
-- Insert 10 meaningful Pakistani-themed records into the Books table
INSERT INTO Books VALUES 
(1, 'A Case of Exploding Mangoes', 'Mohammed Hanif', 'Political Satire', 1200.00, 15, 4.10, 'English', 'Vintage Pakistan', 2008),
(2, 'Moth Smoke', 'Mohsin Hamid', 'Literary Fiction', 950.00, 20, 3.90, 'English', 'Oxford University Press Pakistan', 2000),
(3, 'The Reluctant Fundamentalist', 'Mohsin Hamid', 'Drama', 1000.00, 18, 4.05, 'English', 'Penguin Pakistan', 2007),
(4, 'Kartography', 'Kamila Shamsie', 'Contemporary Fiction', 890.00, 25, 4.20, 'English', 'Bloomsbury Pakistan', 2001),
(5, 'Thanda Ghost', 'Saadat Hasan Manto', 'Short Stories', 600.00, 30, 4.30, 'Urdu', 'Lahore Literary House', 1948),
(6, 'Raja Gidh', 'Bano Qudsia', 'Philosophical Fiction', 750.00, 35, 4.50, 'Urdu', 'Sang-e-Meel Publications', 1981),
(7, 'Zavia', 'Ashfaq Ahmed', 'Spirituality', 850.00, 40, 4.65, 'Urdu', 'Ilm-o-Irfan Publishers', 1995),
(8, 'Peer-e-Kamil', 'Umera Ahmed', 'Religious Fiction', 900.00, 50, 4.70, 'Urdu', 'Ferozsons Publishers', 2004),
(9, 'Jinnah of Pakistan', 'Stanley Wolpert', 'Biography', 1100.00, 12, 4.35, 'English', 'Oxford University Press Pakistan', 1984),
(10, 'The Wandering Falcon', 'Jamil Ahmad', 'Tribal Fiction', 980.00, 16, 4.00, 'English', 'Penguin Pakistan', 2011);

--3.Use a SELECT statement to display all books in stock.
select * from books;

--4.Use SELECT DISTINCT to find all unique genres of books available.
select distinct genre from books;  

--5.Use WHERE to find all books with price greater than 1000.
select * from books
where price > 1000;

--6.Use ORDER BY to list books by rating in descending order
select *from books
order by rating desc;

--7.Use AND to select books that are in stock AND cost less than 1000.
select *from books
where stock_quantity > '1' AND price < '1000'; 

--8.Use OR to find books that are either written in English OR published after 2005.
select *from books
where book_language = 'English' OR year_published > '2005';

--9.Use NOT to find books that are not written in English.
select *from books
where not book_language = 'English';

--10 use IN to find books from specific publishers.
select *from books
where publisher  IN ('Vintage Pakistan', 'Penguin Pakistan', 'Oxford University Press Pakistan');

--11.Use BETWEEN to find books priced between 300 and 800.
select *from books
where price between 300 AND 800;

--12.Use LIKE to find books where the title contains the word "pakistan".
select *from books
where title like '%pakistan%';

--13.Use NOT LIKE to exclude books whose title starts with ‘T’.
select *from books
where title not like 'T%';

--14.Use MAX() to find the highest-rated book.
select max(rating) as highest_rated_book from books;

--15.Use MIN() to find the lowest stock quantity.
select min(stock_quantity) as lowest_stock from books;

--16.Use SUM() to calculate the total value of all books in stock (price * stock_quantity).
select sum(price * stock_quantity) as total_price from books;

--17.Use AVG() to find the average price of all books.
select avg(price) as average_price from books;

--18.Use COUNT() to count how many books are available in total.
select count(stock_quantity) from books;

--19.Apply a NOT NULL constraint on book_id, title, and price.
--20.Apply a UNIQUE constraint on book_id. (not null is already applied that why unique cannot be applied)
--primary key is used instead only one constraint can be used between unique or primary key