Зміст курсу

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 In Statement

Nested queries

In SQL, we can put the result of the query inside another query, and use it with operator IN:


              1234
            
SELECT title FROM songs
WHERE album_id IN
  (SELECT id FROM albums
  WHERE year > 2000)

This is a query to find songs that are created in the 21st century. First, the inner query executes with ids of albums after 2000 from table albums. Using these id's, we select such titles from songs, that album_id is among these albums. This query is quite easy and is using for multiple tables: you find some info in table1, that is inaccessible from table2, and use it for query from table2.

But we can also put constant values, here we selected all songs by 'Britney Spears', 'The Mammas & The Pappas' and 'Pink Floyd':


              123
            
SELECT id
FROM singers 
WHERE naming IN ('Britney Spears', 'The Mammas & The Pappas', 'Pink Floyd')

Now the set ('Britney Spears', 'The Mammas & The Pappas', 'Pink Floyd') is a constant list of singers, and we are searching ids of these three artists. We can also use NOT IN operator:


              123
            
SELECT id 
FROM singers
WHERE naming NOT IN ('ABBA', 'Pink Floyd', 'Mushmellow')

Завдання

Swipe to show code editor

Find all the songs' titles (title), where the singer's info contains 'Solo', using nested queries.

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

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

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

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

SQL In Statement

Nested queries

In SQL, we can put the result of the query inside another query, and use it with operator IN:


              1234
            
SELECT title FROM songs
WHERE album_id IN
  (SELECT id FROM albums
  WHERE year > 2000)

But we can also put constant values, here we selected all songs by 'Britney Spears', 'The Mammas & The Pappas' and 'Pink Floyd':


              123
            
SELECT id
FROM singers 
WHERE naming IN ('Britney Spears', 'The Mammas & The Pappas', 'Pink Floyd')

Now the set ('Britney Spears', 'The Mammas & The Pappas', 'Pink Floyd') is a constant list of singers, and we are searching ids of these three artists. We can also use NOT IN operator:


              123
            
SELECT id 
FROM singers
WHERE naming NOT IN ('ABBA', 'Pink Floyd', 'Mushmellow')

Завдання

Swipe to show code editor

Find all the songs' titles (title), where the singer's info contains 'Solo', using nested queries.

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

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

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

SQL In Statement

Nested queries

In SQL, we can put the result of the query inside another query, and use it with operator IN:


              1234
            
SELECT title FROM songs
WHERE album_id IN
  (SELECT id FROM albums
  WHERE year > 2000)

But we can also put constant values, here we selected all songs by 'Britney Spears', 'The Mammas & The Pappas' and 'Pink Floyd':


              123
            
SELECT id
FROM singers 
WHERE naming IN ('Britney Spears', 'The Mammas & The Pappas', 'Pink Floyd')

Now the set ('Britney Spears', 'The Mammas & The Pappas', 'Pink Floyd') is a constant list of singers, and we are searching ids of these three artists. We can also use NOT IN operator:


              123
            
SELECT id 
FROM singers
WHERE naming NOT IN ('ABBA', 'Pink Floyd', 'Mushmellow')

Завдання

Swipe to show code editor

Find all the songs' titles (title), where the singer's info contains 'Solo', using nested queries.

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

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

Nested queries

In SQL, we can put the result of the query inside another query, and use it with operator IN:


              1234
            
SELECT title FROM songs
WHERE album_id IN
  (SELECT id FROM albums
  WHERE year > 2000)

But we can also put constant values, here we selected all songs by 'Britney Spears', 'The Mammas & The Pappas' and 'Pink Floyd':


              123
            
SELECT id
FROM singers 
WHERE naming IN ('Britney Spears', 'The Mammas & The Pappas', 'Pink Floyd')

Now the set ('Britney Spears', 'The Mammas & The Pappas', 'Pink Floyd') is a constant list of singers, and we are searching ids of these three artists. We can also use NOT IN operator:


              123
            
SELECT id 
FROM singers
WHERE naming NOT IN ('ABBA', 'Pink Floyd', 'Mushmellow')

Завдання

Swipe to show code editor

Find all the songs' titles (title), where the singer's info contains 'Solo', using nested queries.

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