sql_query_documentation.sql

-- Q1. Get the title of each book and the name of its publisher
select t.title, p.pub_name
from titles t
inner join publishers p on p.pub_id = t.pub_id

-- Retrieve all book titles along with their publisher names
-- Include books even if they do not have a matching publisher
select t.title, p.pub_name
from titles t
left join publishers p on p.pub_id = t.pub_id

-- Retrieve all publisher names along with their associated book titles
-- Include all publishers, even those without any books
select t.title, p.pub_name
from titles t
right join publishers p on p.pub_id = t.pub_id

-- Retrieve publisher names that are not associated with any book titles
-- by performing a FULL JOIN and filtering for rows where 'title_id' is NULL
-- We’re using this condition to find publishers who do not have any books listed in the titles table.
select t.title, p.pub_name
from titles t
full join publishers p on p.pub_id = t.pub_id
where t.title_id is null


-- Q2. Retrieve the first and last names of authors along with the titles they have written
-- We're linking the authors to titles through the bridge table titleauthor, which handles the many-to-many relationship
select a.au_fname, a.au_lname, t.title
from authors a
inner join titleauthor ta on ta.au_id = a.au_id
inner join titles t on t.title_id = ta.title_id

-- Q3. Retrieve the first and last names of authors along with the names of the publishers of their books
select a.au_fname, a.au_lname, p.pub_name
from authors a
inner join titleauthor ta on ta.au_id = a.au_id
inner join titles t on t.title_id = ta.title_id
inner join publishers p on p.pub_id = t.pub_id

-- Q4. Count the total number of authors in the 'authors' table
select COUNT(au_id) as 'authors count'
from authors a

-- Q5.Count the number of unique authors who have written at least one valid (non-null) book title
select count(distinct a.au_id) as 'distinct authors count'
from authors a
full join titleauthor ta on ta.au_id = a.au_id
full join titles t on t.title_id = ta.title_id
where t.title is not null

-- As joins are very slow when it comes to big data, a faster way is as bellow.
-- It will give the same result as above and improve performance
select count(distinct au_id) as 'distinct authors count'
from titleauthor ta



-- Q6.Count the number of sales transactions (rows) for each book (title_id)
-- regardless of how many copies were sold in each transaction 
select s.title_id, count(s.qty) as "total_transactions"
from sales as s
group by s.title_id



-- Calculate the total number of copies sold for each book (title_id)
-- by summing the quantity sold in each transaction
select s.title_id, sum(s.qty) as "total_copies_sold"
from sales as s
group by s.title_id



-- Q7.Retrieve each book title along with the total number of copies sold
select t.title, sum(s.qty) as total_copies_sold
from titles as t
left join sales as s on s.title_id = t.title_id
group by t.title



-- Retrieve each book title along with the total number of copies sold
-- Use a LEFT JOIN to include all titles, even those without any sales
-- Replace NULL sales values with 0 using COALESCE
-- Group the results by title and sort them in descending order of total copies sold
select t.title, COALESCE(sum(s.qty), 0) as total_copies_sold
from titles as t
left join sales as s on s.title_id = t.title_id
group by t.title
order by total_copies_sold desc



-- Q9.calculates the total sales revenue for each book title.
select t.title, sum(s.qty*t.price) as total_revenue
from titles as t
inner join sales as s on s.title_id = t.title_id
group by t.title



-- Q9-1.calculates the total sales revenue for each book title.
-- Note:This method assumes the price is consistent for each title, otherwise it may lead to inaccurate totals.
select t.title, sum(s.qty)*t.price as total_revenue
from titles as t
inner join sales as s on s.title_id = t.title_id
group by t.title, t.price



-- Q10.Calculates the total revenue generated by each publisher. Make sure to list all 8 publishers
-- "left join" ensures all publishers are included — even if they have no titles or no sales.
select p.pub_name, isnull(sum(t.price*s.qty), 0) as publisher_total_revenue
from publishers as p
left join titles as t on t.pub_id = p.pub_id
left join sales as s on s.title_id = t.title_id
group by p.pub_name




-- Q11.Retrieve each author's last name along with the number of titles they've contributed to
select a.au_lname, count(ta.title_id) as total_titles
from authors as a
join titleauthor as ta on ta.au_id = a.au_id
group by a.au_lname, a.au_id

--"Derived Tables"
--Q11-1.Retrieve each author's last name along with the number of titles they've contributed to using "Derived Tables"
-- by using a subquery to count titles per author from the titleauthor table
-- and joining it with the authors table.
--Comparing these two queries indicates that using "Derived Table" is much faster than just simply use the "join"
select a.au_lname, k.total_titles
from authors as a
inner join (select au_id, count(title_id) as total_titles
			from titleauthor
			group by au_id) as k on k.au_id = a.au_id


----------------------------------------------------------------how cross check our queries------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
select sum(k.tp)
from(select a.au_fname,a.au_lname,sum(t.price * s.qty) tp
	from authors a
	inner join titleauthor ta on ta.au_id = a.au_id
	inner join titles t on t.title_id = ta.title_id
	inner join sales s on s.title_id = t.title_id
	group by a.au_fname,a.au_lname,a.au_id
	)k--10609.10


select sum(qty*price)
from sales s 
inner join titles t on t.title_id = s.title_id--6676.90

select * from titleauthor order by title_id


select a.au_fname,a.au_lname,a.au_id,t.title_id,s.ord_num,s.qty*t.price*royaltyper/100
from authors a
inner join titleauthor ta on ta.au_id = a.au_id
inner join titles t on t.title_id = ta.title_id
inner join sales s on s.title_id = t.title_id
where ord_num = '6871'

-----Correct Answer-------
---Q12.Join authors, titleauthor, titles, and sales tables to prepare a dataset 
-- that links authors with their book sales. Grouped by author's full name and ID.
select *
from authors a
inner join titleauthor ta on ta.au_id = a.au_id
inner join titles t on t.title_id = ta.title_id
inner join sales s on s.title_id = t.title_id
group by a.au_fname,a.au_lname,a.au_id
-------------

-- Calculate the total royalty paid across all authors.
-- Step 1: Join authors, titleauthor, titles, and sales to compute royalty per author
--         using (price * quantity sold * royalty percentage).
-- Step 2: Sum all individual royalties to get the grand total.
select sum(k.tp) 
from
(select a.au_fname,a.au_lname,sum(t.price * s.qty * ta.royaltyper / 100) tp
from authors a
inner join titleauthor ta on ta.au_id = a.au_id
inner join titles t on t.title_id = ta.title_id
inner join sales s on s.title_id = t.title_id
group by a.au_fname,a.au_lname,a.au_id)k --Expected total: 6676.90

----------------------------------------------------------------how cross check our queries------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------

--Q13. Retrieve the titles that have generated more than $500 in total sales revenue,
-- by joining sales and titles tables, summing (price * quantity), and filtering using HAVING.
select t.title, sum(t.price*s.qty) as total_revenue
from sales as s
inner join titles as t on t.title_id = s.title_id
group by t.title, t.title_id
having sum(t.price*s.qty) > 500



--Q14.-- Find the first name and the last name of authors who have contributed to more than one title
--Comment:This query joins authors with titleauthor to count the number of titles each author has contributed to, then filters to show only those with more than one title.
select a.au_fname,a.au_lname,count(ta.title_id) as number_of_titles
from authors as a
inner join titleauthor as ta on ta.au_id = a.au_id
group by a.au_fname,a.au_lname, a.au_id
having count(ta.title_id) > 1




--Q14-1. Optimised version using a derived table to pre-filter authors with more than one title
--Comment: This version uses a derived table (a subquery in the FROM clause) to first filter out authors who have only one title, 
--reducing the number of rows joined with the authors table — which can improve performance in large datasets.
select a.au_fname, a.au_lname, k.number_of_titles
from authors as a
inner join	(select au_id, count(title_id) as number_of_titles
			from titleauthor 
			group by au_id
			having count(title_id) >1 ) as k on k.au_id = a.au_id


--Sub Query type 1
--Q15.Find the title(s) with the highest price from the titles table
--The inner query (select max(price) from titles) gets the maximum price of all titles.
--The outer query returns the title(s) that match that price.
--If multiple titles share the same highest price, all of them will be returned.
select title
from titles
where price = (select max(price) from titles)


--Q15-1.Using "top" for answering above question
--note: Top and Order by make the query slow so above method is more optimised
select top 1 with ties price, title
from titles
order by price desc


--This query retrieves the first 5 rows from the "titles" table,
-- ordered by "title_id". It starts from the beginning (offset 0).
select *
from titles
order by title_id
offset 0 rows
fetch first 5 rows only


-- This query retrieves the next 5 rows (rows 6 to 10) from the "titles" table,
-- again ordered by "title_id". It skips the first 5 rows (offset 5) and fetches the next 5.
select *
from titles
order by title_id
offset 5 rows
fetch first 5 rows only

------------------------------------------------------------------------------------------------------------------------------------------------
--The above queries are used for pagination—a common technique to retrieve data in smaller, manageable chunks (pages), especially useful for:

--Displaying data in UI (e.g. showing 5 records per page).

--Improving performance by loading only a subset of records at a time.

--Implementing "Load More" or "Next Page" features in applications.
-------------------------------------------------------------------------------------------------------------------------------------------------


--Q16.Retrive the title(s) and price(s) from the "titles" table
--where the price matches the lowest price among the top 5 most expensive distinct titles.
--Step 1: Inner-most query or Derived Table (This gets the top 5 most expensive books from the titles table.)
--Step 2: Middle query: Now from that group of expensive books, we pick the lowest price. (SELECT MIN(k.price) FROM ( ... ) AS k)
--Step 3: Main query (SELECT title, price 
                    --FROM titles
					--WHERE price = (result from step 2))
select title, price
from titles
where price = (select min(k.price) as min_price
				from(select distinct top 5 with ties title, price
					from titles
					order by price desc
					) as k
				)

--Q16-1.Using "offset" instead of "top n"
select title, price
from titles
where price = (select min(k.price) as min_price
				from (select distinct price
						from titles
						order by price desc
						offset 0 rows
						fetch first 5 rows only
					) as k
				)
	


--Q17.For each book, show its title, price, average price, and the difference between the average price of all books and its own price. (Compare each book's price to the overall average price)

-- Step 1: For each title and its price (GROUP BY ensures unique rows),
-- Step 2: Add a column showing the average price of all books (subquery),
-- Step 3: Add a column calculating the difference between the book's price and that average.
select title, price, (select avg(price) from titles) as avg_price, (price - (select avg(price) from titles)) as diff_with_avg
from titles
group by title, price;


--Q17-1.Using "Cross join" instead of "Subquery type 2" to optimise the query

--The average is computed once, not for every row. this makes the query way much faster.
--CROSS JOIN is perfect here because Derived Table "k" returns one row, so every row from titles just gets that one row attached.
select t.title, t.price, k.avg_price, (t.price - k.avg_price)  as diff_with_avg
from titles as t
cross join (select avg(price) as avg_price  from titles) as k

---------------------------------------------------------------------------------------------------------------
-- Sessio 8
-- Q1
-- Retrieve the title and original price of each book, along with a calculated 'new_price' 
-- based on the total sales value (quantity * price) for that title.
-- If the total sales for a title exceed $500, the new price is increased by 10% (price * 1.1).
-- Otherwise, the new price is decreased by 5% (price * 0.95).
-- The query joins the 'titles' and 'sales' tables on 'title_id' and groups results by title and price.
select title, price, case when sum(qty*price) > 500
						  then price*1.1
						  else price*0.95 end as new_price
from titles t
inner join sales s on s.title_id = t.title_id
group by title, price

-- Using Subquery" and "IN" to answer the above question
-- Using a "Subquery" that identifies title_ids with total sales greater than $500.
-- The outer query applies the pricing logic to all titles in the 'titles' table, regardless of whether they had sales.
select title, price, case when title_id in (select t.title_id
											from titles t
											inner join sales s on s.title_id = t.title_id
											group by t.title_id
											having sum(qty*price) > 500
											)
					      then price*1.1
						  else price*0.95 end as new_price 
from titles t

-- Using Subquery" and "EXISTS" to answer the above question
select title, price, case when exists (select t.title_id
										from titles t
										inner join sales s on s.title_id = t.title_id
										where t.title_id = tt.title_id
										group by t.title_id
										having sum(qty*price) > 500
										)
					      then price*1.1
						  else price*0.95 end as new_price 
from titles tt

-- Comparing the Estimated Execution Plan indicates 42%,29%, and 29% respectively for the above three methods. 
-- The JOIN version had the highest estimated cost, while IN and EXISTS were more efficient, with EXISTS often being preferable in practice due to its short-circuit behavior.
-- EXISTS checks for existence and short-circuits as soon as a qualifying match is found.

-- Q2
-- Using "Derived Table"
-- This query calculates a new price (new_price) for each book title based on specific business rules.
-- It applies conditional price increases depending on:
--   1. The publisher's state (10% increase if in California),
--   2. The number of authors (5% increase if more than one author),
--   3. The total sales value (2% increase if total sales exceed $500),
--   4. A default 1% increase if none of the above conditions are met.
-- The query joins the 'titles' table with:
--   - 'publishers' to get the state of the publisher,
--   - a subquery that counts the number of authors per title (from 'titleauthor'),
--   - a subquery that calculates total sales per title (from 'sales').
-- NOTE: All derived tables are subqueries, but not all subqueries are derived tables.
-- 👉 k and k2 are both subqueries and derived tables.
select title, price, case when state = 'ca' then price*1.1
						  when k.total_aughtor_count>1 then price*1.05
						  when k2.total_sales>500 then price*1.02
						  else price*1.01
					 end as new_price
from titles t
inner join publishers p on p.pub_id = t.pub_id
inner join (select title_id, count(au_id) as total_aughtor_count
			from titleauthor
			group by title_id) as k on k.title_id = t.title_id
inner join (select t.title_id, sum(qty*price) as total_sales
			from titles t
			inner join sales s on s.title_id = t.title_id
			group by t.title_id) as k2 on k2.title_id = t.title_id


-- Answering the above question using "Subquery" and "IN"
select title, price, case when pub_id in (select pub_id from publishers where state = 'ca') 
						  then price*1.1 
						  when title_id in (select title_id 
											from titleauthor 
											group by title_id 
											having count(au_id)>1)
						  then price*1.05
						  when title_id in (select t.title_id 
											from titles t
											inner join sales s on s.title_id = t.title_id
											group by t.title_id
											having sum(qty*price)>500)
						  then price*1.02
						  else price*1.01
						  end as new_price
from titles tt 


-- Answering the above question using "Subquery" and "EXISTS"
select title, price, case when exists (select * from publishers p where state = 'ca' and p.pub_id = tt.pub_id) 
						  then price*1.1 
						  when title_id in (select title_id 
											from titleauthor ta
											where ta.title_id = tt.title_id
											group by title_id 
											having count(au_id)>1)
						  then price*1.05
						  when exists ( select t.title_id 
										from titles t
										inner join sales s on s.title_id = t.title_id
										where t.title_id = tt.title_id
										group by t.title_id
										having sum(qty*price)>500)
						  then price*1.02
						  else price*1.01
						  end as new_price
from titles tt 

-- Answering the above question using SIMPLE "JOINS" 
select title,price,case when p.state = 'ca' then price * 1.1
						when count(au_id) > 1 then price * 1.05
						when sum(qty*price) > 500 then price * 1.02 
					else price * 1.01 end newprice
from titles t
inner join publishers p on p.pub_id = t.pub_id
inner join titleauthor ta on ta.title_id = t.title_id
inner join sales s on s.title_id = t.title_id
group by title,price,state

-- Comparing the Estimated Execution Plan indicates 35%,19%,19%, and 29% respectively for the above FOUR methods. 

/*
-- Purpose:
This script demonstrates four different SQL approaches to calculate a new book price (new_price)
based on business rules involving publisher location, author count, and total sales.

-- Business Logic:
1. If the publisher is from California (state = 'ca') → increase price by 10%
2. If the title has more than one author → increase price by 5%
3. If the total sales value of the book exceeds $500 → increase price by 2%
4. Otherwise → increase price by 1%

-- Methods Compared:
1. Derived Tables (Q2): Uses subqueries in the FROM clause to pre-calculate author counts and total sales.
2. Subqueries with IN: Uses correlated subqueries in the CASE WHEN conditions.
3. Subqueries with EXISTS: Similar logic but with EXISTS to test presence of matching rows.
4. Simple Joins with Aggregation: Uses direct joins with GROUP BY to apply conditions based on aggregated values.

-- Notes:
- `k` and `k2` in Q2 are both derived tables (a special form of subquery used in the FROM clause).
- IN vs. EXISTS performance may vary depending on database engine and data distribution.
- Joins with aggregation can be more efficient in some RDBMS when indexes are properly used.
*/
----------------------------------------------------------------
-- Session 9
----------------------------------------------------------------
-- Using "Update"
----------------------------------------------------------------
-- This query updates the prices of books in the 'titles' table based on dynamic business rules. 
-- It calculates a new price for each title depending on:
--   1. The publisher's state (a 10% increase if the publisher is based in California),
--   2. The number of authors associated with the title (a 5% increase if more than one author),
--   3. The total revenue generated from sales of the title (a 2% increase if revenue exceeds 500),
--   4. A default increase of 1% if none of the above conditions are met.
-- The new prices are computed using subqueries and applied via an INNER JOIN on the title ID.

update titles 
set price = k3.newprice
from titles t
inner join(select t.title_id,case when state = 'ca' then price * 1.1 
									when k.tc>1 then price * 1.05 
									when k2.tp > 500 then price * 1.02 else price * 1.01 
							   end newprice
			from titles t
			inner join publishers p on p.pub_id = t.pub_id
			inner join(select title_id,count(au_id) tc
						from titleauthor
						group by title_id)k on k.title_id = t.title_id
			inner join(select t.title_id,sum(qty*price) tp
						from titles t
						inner join sales s on s.title_id = t.title_id
						group by t.title_id)k2 on k2.title_id = t.title_id)k3 on k3.title_id = t.title_id

----------------------------------------------------------------
-- Using "Temp Table"
----------------------------------------------------------------
-- This query finds authors who have written more than one book, removes all their books from the "titleauthors" table, 
-- delete them from the authors" table, and then cleans up the temporary table used in the process.
-----------------------------------------------------------------

--Q1
-- Create a temporary table to store author IDs who have written more than one title
create table #AuthorsWithMultipleTitles (au_id nvarchar(20))

-- Insert author IDs into the temporary table where the author has more than one title
insert into #AuthorsWithMultipleTitles
select au_id
from titleauthor
group by au_id
having count(title_id) > 1

-- Delete all records from the titleauthor table for authors who have more than one title
delete titleauthor 
where au_id in (select au_id from #AuthorsWithMultipleTitles)

-- Delete those authors from the authors table who were just removed from titleauthor
delete authors 
where au_id in (select au_id from #AuthorsWithMultipleTitles)

-- Drop the temporary table as it's no longer needed
drop table #AuthorsWithMultipleTitles

----------------------------------------------------------------
-- Using "Temp Table"
----------------------------------------------------------------
--Q2
-- This query counts how many authors are in each city in California
-----------------------------------------------------------------
-- Create a temp table with authors from California
select *
into #CalifornianAuthors
from authors
where state = 'CA'

-- The best practice to check if the Temp Table is created and check the resualt
select *
from #CalifornianAuthors

-- Count how many authors per city in California
select city, count(au_id) as TotalAuthors
from #CalifornianAuthors
group by city

-- Drop the temp table
drop table #CalifornianAuthors

-- Using "Temp Table"
----------------------------------------------------------------
--Q3
-- This query get top 3 bestselling books (based on sales)
-----------------------------------------------------------------
-- Create a temp table from sales and titles tables
select s.title_id, t.title, sum(s.qty) as TotalSold
into #TopBooks
from sales s
inner join titles t on t.title_id = s.title_id
group by s.title_id, t.title

-- Check if the temp table is created and review the data in it
select *
from #TopBooks

-- select the top 3 bestselling books
select top 3 *
from #TopBooks
order by TotalSold desc

-- Drop the temp table
drop table #TopBooks

-- Using "Temp Table"
----------------------------------------------------------------
--Q4
-- This query lets me analyse which books sold how many copies in each store, and how much each book cost.
-- I used temp table to simplify a complex join
-- Instead of writing a long query many times, I save part of it in a temp table first.
-----------------------------------------------------------------
-- Create a temp table with book prices
select title_id, price
into #BookPrice
from titles
where price is not null

-- Check if the temp table is created and review the data in it
select *
from #BookPrice

-- Join #BookPrice to the sales table to find which books sold how many copies in each store, and how much each book cost
select s.stor_id, b.title_id, b.price, sum(s.qty) as TotalSold
from #BookPrice b
inner join sales s on s.title_id = b.title_id
group by s.stor_id, b.title_id, b.price

-- Drop the temp table
drop table #BookPrice