Data Manipulation using SQL

Data Manipulation using SQL

0%

1. Database and Nested Queries

We shall take a look at the database we'll work with and how we can create a query inside another query.

SQL In Statement

Challenge In Statement

SQL Exists Statement

2. SQL Joining Tables

Learn how to join and work with multiple tables.

SQL Left & Right Join

SQL Full Outer Join

3. SQL Tasks

Try creating queries with no limits on how you should do that. Use all your knowledge from previous chapters!

Challenge: Albums with Two Songs

Challenge: the Biggest Album

Challenge: Rating of Singers

Challenge: Decades

Challenge: Two Singers

Challenge: Solo vs Bands

Challenge: Solo Albums

Challenge: Singers Stats

Зміст курсу

Data Manipulation using SQL

Data Manipulation using SQL

1. Database and Nested Queries

Database SQL In Statement Challenge In Statement SQL Exists Statement

2. SQL Joining Tables

SQL Inner Join SQL Joins SQL Left & Right Join SQL Full Outer Join SQL Union

3. SQL Tasks

Challenge: Albums with Two Songs Challenge: the Biggest Album Challenge: Rating of Singers Challenge: Decades Challenge: Two Singers Challenge: Solo vs Bands Challenge: Solo Albums Challenge: Singers Stats

SQL Left & Right Join

LEFT JOIN returns all records from the left table and matching records from the right table. For records with no matching, it completes the fields with NULL. For example, let's select all albums joined with songs ids with LEFT JOIN:


              123
            
SELECT albums.title, songs.id
FROM albums 
LEFT JOIN songs ON songs.album_id = albums.id

On the diagram for this query, we can see that not all albums have songs in a table, so absent fields are autocompleted with NULL. Similarly, we can see in the result query: some albums have empty id's for the songs, because there are no songs for these albums.

Завдання

Swipe to show code editor

Do the joining for tables albums and songs using INNER JOIN, RIGHT JOIN, FULL JOIN, and check the difference. Then, find the number of songs for each album (even if there are no songs in it). Think about which JOIN you can use here. Order everything by albums title.

Перейдіть на комп'ютер для реальної практикиПродовжуйте з того місця, де ви зупинились, використовуючи один з наведених нижче варіантів

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 2. Розділ 3

toggle bottom row

SQL Left & Right Join

LEFT JOIN returns all records from the left table and matching records from the right table. For records with no matching, it completes the fields with NULL. For example, let's select all albums joined with songs ids with LEFT JOIN:


              123
            
SELECT albums.title, songs.id
FROM albums 
LEFT JOIN songs ON songs.album_id = albums.id

On the diagram for this query, we can see that not all albums have songs in a table, so absent fields are autocompleted with NULL. Similarly, we can see in the result query: some albums have empty id's for the songs, because there are no songs for these albums.

Завдання

Swipe to show code editor

Do the joining for tables albums and songs using INNER JOIN, RIGHT JOIN, FULL JOIN, and check the difference. Then, find the number of songs for each album (even if there are no songs in it). Think about which JOIN you can use here. Order everything by albums title.

Перейдіть на комп'ютер для реальної практикиПродовжуйте з того місця, де ви зупинились, використовуючи один з наведених нижче варіантів

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 2. Розділ 3

Перейдіть на комп'ютер для реальної практикиПродовжуйте з того місця, де ви зупинились, використовуючи один з наведених нижче варіантів

some-alt