Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.SQL In Statement | Database and Nested Queries
Data Manipulation using SQL
course content

Conteúdo do Curso

Data Manipulation using SQL

Data Manipulation using SQL

1. Database and Nested Queries
DatabaseSQL In StatementChallenge In StatementSQL Exists Statement
2. SQL Joining Tables
SQL Inner JoinSQL JoinsSQL Left & Right JoinSQL Full Outer JoinSQL Union
3. SQL Tasks
Challenge: Albums with Two SongsChallenge: the Biggest AlbumChallenge: Rating of SingersChallenge: DecadesChallenge: Two SingersChallenge: Solo vs BandsChallenge: Solo AlbumsChallenge: Singers Stats

bookSQL 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)
copy

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')
copy

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')
copy

Tarefa

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

Switch to desktopMude para o desktop para praticar no mundo realContinue de onde você está usando uma das opções abaixo
Tudo estava claro?

Como podemos melhorá-lo?

Obrigado pelo seu feedback!

Seção 1. Capítulo 2
toggle bottom row

bookSQL 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)
copy

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')
copy

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')
copy

Tarefa

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

Switch to desktopMude para o desktop para praticar no mundo realContinue de onde você está usando uma das opções abaixo
Tudo estava claro?

Como podemos melhorá-lo?

Obrigado pelo seu feedback!

Seção 1. Capítulo 2
toggle bottom row

bookSQL 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)
copy

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')
copy

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')
copy

Tarefa

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

Switch to desktopMude para o desktop para praticar no mundo realContinue de onde você está usando uma das opções abaixo
Tudo estava claro?

Como podemos melhorá-lo?

Obrigado pelo seu feedback!

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)
copy

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')
copy

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')
copy

Tarefa

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

Switch to desktopMude para o desktop para praticar no mundo realContinue de onde você está usando uma das opções abaixo
Seção 1. Capítulo 2
Switch to desktopMude para o desktop para praticar no mundo realContinue de onde você está usando uma das opções abaixo
some-alt