A day with mini Project SQL (1) ;

---

Query with SQL

Tools: SQL and Chinook.db

Hi everyone my name is Satita, Today we will go through and take a look at my mini project on SQL.

What is SQL

SQL is a Structured Query Language. SQL is used to communicate with a database (According to https://www.sqlcourse.com/beginner-course/what-is-sql/)

you can learn more at https://www.w3schools.com/sql/sql_intro.asp

What chinook.db

chinook.db is a sample database that we use to learn how to write SQL and get the answer from a mock-up of user questions.

Structure of chinook.db

credit: https://www.sqlitetutorial.net/sqlite-sample-database/

Mock-up questions

• The top 10 countries that order our product the most
• The best seller of albums (top 10)
• Popular genres (sell and amount)

The top 10 countries that order our product the most

SELECT DISTINCT BillingCountry,
		count(BillingCountry) 
from invoices
group by 1
order by 2 DESC LIMIT 10;

The best seller of albums (top 10)

SELECT Title,
       sum(UnitPrice) sell,
       count(Quantity) pieces
FROM
    (SELECT * from invoices A
    left join invoice_items B
    on A.InvoiceId = B.InvoiceId
    left join Tracks C
    on B.TrackId = C.TrackId
    left join albums D
    on D.AlbumId = C.AlbumId) F
left join artists E
on F.ArtistId = E.ArtistId
GROUP by Title
ORDER by 2 DESC limit 10;

Popular genres (sell and amount)

SELECT genres.name,
	sum(round(unitprice)) as price,
	count(tracks.GenreId) as amoutTrack
from genres
LEFT join tracks
on genres.GenreId = tracks.GenreId
group by 1
order by 2 DESC limit 5;

Thank you for visiting!

---