/SQL-Case-Study-On-Famous-Painting

Welcome to the SQL Case Study on Famous Paintings repository! Here, we explore intriguing insights from the Famous Paintings & Museum dataset. About

SQL Case Study on Famous Paintings 🎨

Welcome to the SQL Case Study on Famous Paintings repository! Here, we delve into intriguing insights from the Famous Paintings & Museum dataset.

About the Dataset ℹ️

The dataset comprises 8 tables:

  • artist.csv 🎨
  • canvas_size.csv 🖼️
  • image_link.csv 🌐
  • museum.csv 🏛️
  • museum_hours.csv ⏰
  • product_size.csv 📏
  • subject.csv 📚
  • work.csv 🛠️

Key Queries Explored 🔍

  1. Fetch all the paintings which are not displayed in any museums?
  2. Are there museums without any paintings?
  3. How many paintings have an asking price of more than their regular price?
  4. Identify the paintings whose asking price is less than 50% of its regular price.
  5. Which canvas size costs the most?
  6. Delete duplicate records from work, product_size, subject, and image_link tables.
  7. Identify the museums with invalid city information in the given dataset.
  8. Museum_Hours table has 1 invalid entry. Identify it and remove it.
  9. Fetch the top 10 most famous painting subjects.
  10. Identify the museums which are open on both Sunday and Monday. Display museum name, city.
  11. How many museums are open every single day?
  12. Which are the top 5 most popular museums? (Popularity is defined based on most number of paintings in a museum)
  13. Who are the top 5 most popular artists? (Popularity is defined based on most number of paintings done by an artist)
  14. Display the 3 least popular canvas sizes.
  15. Which museum is open for the longest during a day? Display museum name, state, hours open and which day?
  16. Which museum has the most number of most popular painting style?
  17. Identify the artists whose paintings are displayed in multiple countries.
  18. Display the country and the city with the most number of museums. Output 2 separate columns to mention the city and country. If there are multiple values, separate them with a comma.
  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.
  20. Which country has the 5th highest number of paintings?
  21. Which are the 3 most popular and 3 least popular painting styles?
  22. Which artist has the most number of Portraits paintings outside the USA? Display artist name, number of paintings, and the artist nationality.

Tools and Technologies 🛠️

  • MySQL Database

Feel free to explore the queries, contribute, and share insights! 🌟