SQL Tutorial: Building a Music Library 🎵

Welcome to this comprehensive SQL tutorial where we'll build a Music Library! This project is designed for beginners and intermediates alike. By the end, you'll have a practical understanding of SQL, ready to apply it to your own projects. 💡 Pro Tip: This tutorial is self-contained, so no external links are needed!

Introduction 🚀

SQL (Structured Query Language) is a powerful tool for managing and manipulating databases. In this project, we'll create a Music Library to store and organize songs, artists, and albums. Let's get started!

Table Design 🗃️

Before we dive into writing queries, let's design our tables:

Artists (id, name, birth_year, death_year)
Albums (id, title, release_year, artist_id)
Songs (id, title, album_id, length_minutes, year_released)

Each table has an id column for unique identification. The Artists table includes the artist's name, birth, and death years. The Albums table records the album's title, release year, and associated artist. The Songs table stores song details such as title, album id, length, and year released.

Basic SQL Commands 🔧

SELECT 🎯

The SELECT statement is used to retrieve data from a database.

sql

SELECT column1, column2, ... FROM table_name;

INSERT 📝

To add new data to a table, use the INSERT command:

sql

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

UPDATE ✅

Update existing data using the UPDATE command:

sql

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

DELETE 🗑️

Delete data using the DELETE command:

sql

DELETE FROM table_name WHERE condition;

Joining Tables 🔗

Joining tables allows us to combine data from multiple tables.

Inner Join 🔓

An INNER JOIN returns only the matching rows between two tables:

sql

SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

Left Join 👣

A LEFT JOIN includes all rows from the left table and the matching rows from the right table:

sql

SELECT table1.column1, table2.column2
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

Advanced Examples 🌟

Queries 📝

Display the names of all artists with more than 3 albums:

sql

SELECT name
FROM Artists
WHERE id IN (
  SELECT artist_id
  FROM Albums
  GROUP BY artist_id
  HAVING COUNT(*) > 3
);

Find the album with the longest duration:

sql

SELECT title, SUM(length_minutes) as total_duration
FROM Songs
GROUP BY album_id
ORDER BY total_duration DESC
LIMIT 1;

Conclusion 🏁

Congratulations on completing this SQL tutorial! You now have the skills to create, manage, and manipulate databases like a pro. Keep practicing and exploring SQL to master this powerful tool. Happy coding! 🎵💡🚀