sql queries

#1) Fetch all the paintings which are not displayed on any museums?
select count(*)
from work
where museum_id is null
#2) Are there museuems without any paintings? Ans-No
select *
from work
where name is null and museum_id is not null
                 #'OR'
select * from museum m
	where not exists (select 1 from work w
					 where w.museum_id=m.museum_id)
                     
#3) How many paintings have an asking price of more than their regular price? 
select *
from product_size
where sale_price>regular_price


#4) Identify the paintings whose asking price is less than 50% of its regular price?
select *
from product_size
where sale_price<(regular_price * 0.5)

#5) Which canva size costs the most?
select *,(select max(sale_price) from product_size) as max_price
from canvas_size
where size_id in (select size_id from product_size where sale_price in (select max(sale_price) from product_size))
					#OR
select cs.label as canva, ps.sale_price
	from (select *,rank() over(order by sale_price desc) as rnk 
		  from product_size) ps
	join canvas_size cs on cs.size_id=ps.size_id
	where ps.rnk=1;	
#6) Delete duplicate records from work, product_size, subject and image_link tables
delete
from work
where work_id in (select * from(select work_id from work 
					group by work_id
					having count(*)>1)as x) 
set SQL_safe_updates=0
delete
from product_size
where work_id in (select * from(select work_id from product_size 
					group by work_id
					having count(*)>1)as x) 

delete
from subject
where work_id in (select * from(select work_id from subject 
					group by work_id
					having count(*)>1)as x) 
delete
from image_link
where work_id in (select * from(select work_id from image_link 
					group by work_id
					having count(*)>1)as x) 
#7) Identify the museums with invalid city information in the given dataset
select *
from museum
where city regexp '^[0-9]'
#8) Museum_Hours table has 1 invalid entry. Identify it and remove it.
delete
from museum_hours
where museum_id not in (select * from(select min(museum_id)
						from museum_hours
						group by museum_id, day) as y)
                        
#9) Fetch the top 10 most famous painting subject
select *
from(select s.subject,count(1) as cnt,dense_rank() over(order by count(1) desc) rnk
		from work w join subject s on w.work_id=s.work_id group by s.subject)v
where rnk<=10

#10) Identify the museums which are open on both Sunday and Monday. Display museum name, city.
with cte1 as
(select *,lag(day,1) over (partition by museum_id) as day_lag
from museum_hours )

select m.museum_id,m.name,m.city
from cte1 left join museum m on cte1.museum_id=m.museum_id
where cte1.day='Monday' and cte1.day_lag='Sunday'

			#OR
select distinct m.name as museum_name, m.city, m.state,m.country
	from museum_hours mh 
	join museum m on m.museum_id=mh.museum_id
	where day='Sunday'
	and exists (select 1 from museum_hours mh2 
				where mh2.museum_id=mh.museum_id 
			    and mh2.day='Monday');
#11) How many museums are open every single day?

with cte as
(select *,row_number() over (partition by museum_id) as day_no
from museum_hours)
select count(distinct museum_id)
from cte
where day_no=7

			#OR
select count(1)
	from (select museum_id, count(1)
		  from museum_hours
		  group by museum_id
		  having count(1) = 7) x;
#12) Which are the top 5 most popular museum? (Popularity is defined based on most no of paintings in a museum)
select w.museum_id,m.name,count(1) as no_of_paintings
from work as w join museum m on w.museum_id=m.museum_id
where w.museum_id is not null
group by w.museum_id
order by count(1) desc
limit 5
				#OR
select m.name as museum, m.city,m.country,x.no_of_painintgs
	from (	select m.museum_id, count(1) as no_of_painintgs
			, rank() over(order by count(1) desc) as rnk
			from work w
			join museum m on m.museum_id=w.museum_id
			group by m.museum_id) x
	join museum m on m.museum_id=x.museum_id
	where x.rnk<=5
    order by x.rnk;
#13) Who are the top 5 most popular artist? (Popularity is defined based on most no of paintings done by an artist)
select a.full_name,count(1) as no_of_paintings
from work as w join artist a on w.artist_id=a.artist_id
group by w.artist_id
order by count(1) desc
limit 5
			#OR
select a.full_name as artist, a.nationality,x.no_of_painintgs
	from (	select a.artist_id, count(1) as no_of_painintgs
			, rank() over(order by count(1) desc) as rnk
			from work w
			join artist a on a.artist_id=w.artist_id
			group by a.artist_id) x
	join artist a on a.artist_id=x.artist_id
	where x.rnk<=5
    order by x.rnk;
#14) Display the 3 least popular canva sizes

select w.work_id,count(w.work_id),c.label
from work w join product_size p on w.work_id=p.work_id 
join canvas_size c on p.size_id=c.size_id
group by w.work_id
order by 2 asc
limit 3

#15) Which museum is open for the longest during a day. Dispay museum name, state and hours open and which day?
with cte as(
select *,time_format(open,'%T') as open_time, addtime(time_format(close,'%T'),'12:00:00') as close_time
from museum_hours),
cte2 as(select *, time_format(timediff(close_time,open_time),'%H:%i:%s') as time_diff
from cte)
select m.museum_id,m.name,m.state,cte2.day,cte2.open_time,cte2.close_time
from cte2 join museum m on cte2.museum_id=m.museum_id
where cte2.time_diff in (select max(cte2.time_diff) from cte2)

#16) Which museum has the most no of most popular painting style?
with cte as(
select museum_id,style,count(*) as no_of_paintings
from work 
where museum_id is not null and style is not null
group by style,museum_id
order by 3 desc
limit 1)
select c.museum_id,m.name,c.style,c.no_of_paintings
from cte c join museum m on c.museum_id=m.museum_id

		#OR
with pop_style as 
			(select style
			,rank() over(order by count(1) desc) as rnk
			from work
			group by style),
		cte as
			(select w.museum_id,m.name as museum_name,ps.style, count(1) as no_of_paintings
			,rank() over(order by count(1) desc) as rnk
			from work w
			join museum m on m.museum_id=w.museum_id
			join pop_style ps on ps.style = w.style
			where w.museum_id is not null
			and ps.rnk=1
			group by w.museum_id, m.name,ps.style)
	select museum_id,museum_name,style,no_of_paintings
	from cte 
	where rnk=1;
    
#17) Identify the artists whose paintings are displayed in multiple countries
with cte as(
select distinct a.full_name as artist,count(m.country) as no_of_countries
from work w join museum m on w.museum_id=m.museum_id join artist a on a.artist_id=w.artist_id
group by a.full_name
order by 2 desc)
select artist,no_of_countries
from cte
group by artist
having no_of_countries>1

			#OR
with cte as
		(select distinct a.full_name as artist
		, w.name as painting, m.name as museum
		, m.country
		from work w
		join artist a on a.artist_id=w.artist_id
		join museum m on m.museum_id=w.museum_id)
	select artist,count(1) as no_of_countries
	from cte
	group by artist
	having count(1)>1
	order by 2 desc;
#18) Display the country and the city with most no of museums.
# Output 2 seperate columns to mention the city and country. If there are multiple value, seperate them with comma.
with cte_country as 
			(select country, count(1)
			, rank() over(order by count(1) desc) as rnk
			from museum
			group by country),
		cte_city as
			(select city, count(1)
			, rank() over(order by count(1) desc) as rnk
			from museum
			group by city)
	select group_concat(distinct country.country,'') as countries, group_concat(city.city,'') as cities
	from cte_country country
	 join cte_city city
	where country.rnk = 1
	and city.rnk = 1;
    
#19) Identify the artist and the museum where the most expensive and least expensive painting is placed.
# Display the artist name, sale_price, painting name, museum name, museum city and canvas label
with cte1 as(
select distinct p.work_id,p.sale_price,dense_rank() over(order by p.sale_price desc) as top_rnk,a.full_name,
m.name as museum_name,w.name as painting_name,m.city,c.label
from product_size p join work w on p.work_id=w.work_id join artist a on a.artist_id=w.artist_id 
join museum m on m.museum_id=w.museum_id join canvas_size c on c.size_id=p.size_id),
cte2 as (
select distinct p.work_id,p.sale_price,dense_rank() over(order by p.sale_price asc) as low_rnk,a.full_name,
m.name as museum_name,w.name as painting_name,m.city,c.label
from product_size p join work w on p.work_id=w.work_id join artist a on a.artist_id=w.artist_id
 join museum m on m.museum_id=w.museum_id join canvas_size c on c.size_id=p.size_id)

select distinct c1.full_name,c1.sale_price,c1.painting_name,c1.museum_name,c1.city,c1.label
from cte1 c1 join cte2 c2 on c1.work_id=c2.work_id
where top_rnk=1
union
select distinct c2.full_name,c2.sale_price,c2.painting_name,c2.museum_name,c2.city,c2.label
from cte1 c1 join cte2 c2 on c1.work_id=c2.work_id
where low_rnk=1 
group by 1
			#OR
with cte as 
		(select *
		, rank() over(order by sale_price desc) as rnk
		, rank() over(order by sale_price ) as rnk_asc
		from product_size )
	select w.name as painting
	, cte.sale_price
	, a.full_name as artist
	, m.name as museum, m.city
	, cz.label as canvas
	from cte
	join work w on w.work_id=cte.work_id
	join museum m on m.museum_id=w.museum_id
	join artist a on a.artist_id=w.artist_id
	join canvas_size cz on cz.size_id = cte.size_id
	where rnk=1 or rnk_asc=1;
    
    #20) Which country has the 5th highest no of paintings?
    with cte as (
    select m.country,count(*) as no_of_paintings,dense_rank() over(order by count(*) desc) as rnk
    from work w join museum m on w.museum_id=m.museum_id
    group by 1)
    
    select country,no_of_paintings
    from cte
    where rnk=5
    
#21) Which are the 3 most popular and 3 least popular painting styles?
    with cte as
    (select style, count(*),dense_rank() over(order by count(*) desc) as top_rnk,
				  dense_rank() over(order by count(*) ) as low_rnk
	from work
    where style is not null
    group by style)
        
   select style, 'most_popular' as remarks
   from cte
   where top_rnk<=3
   union
   select style, 'least_popular' as remarks
   from cte
   where low_rnk<=3

#22) Which artist has the most no of Portraits paintings outside USA?
#Display artist name, no of paintings and the artist nationality.
select full_name,no_of_paintings,nationality
from(
select a.full_name,count(*) as no_of_paintings,a.nationality,
dense_rank() over(order by count(w.work_id) desc) as rnk
from subject s join work w on s.work_id=w.work_id
join museum m on m.museum_id=w.museum_id 
join artist a on a.artist_id=w.artist_id
where s.subject='Portraits' and m.country not in ('USA')
group by 1) b
where rnk=1

					#OR
select full_name as artist_name, nationality, no_of_paintings
	from (
		select a.full_name, a.nationality
		,count(1) as no_of_paintings
		,rank() over(order by count(1) desc) as rnk
		from work w
		join artist a on a.artist_id=w.artist_id
		join subject s on s.work_id=w.work_id
		join museum m on m.museum_id=w.museum_id
		where s.subject='Portraits'
		and m.country != 'USA'
		group by a.full_name, a.nationality) x
	where rnk=1;