CREATE TABLE ARTIST
( ID NUMBER,
NAME VARCHAR2(255)
)
CREATE TABLE ALBUM
( ID NUMBER,
ARTIST_ID NUMBER,
NAME VARCHAR2(255)
)
CREATE TABLE SONG
( ID NUMBER,
ALBUM_ID NUMBER,
NAME VARCHAR2(255)
)
INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')
INSERT INTO ARTIST (ID, NAME) VALUES (2, 'Adele')
INSERT INTO ARTIST (ID, NAME) VALUES (3, 'Coldplay')
INSERT INTO ARTIST (ID, NAME) VALUES (4, 'Silent')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('22', 2, 'James Bond 007: Skyfall')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('31', 3, 'Ghost Stories')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('32', 3, 'A Head Full of Dreams')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('41', 4, 'Without a Sound')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('51', null, 'From the Unknown')
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('61', null, 'The Lost')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('203', 22, 'Skyfall')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('301', 31, 'Oceans')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('302', 31, 'A Sky Full of Stars')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('303', 32, 'Everglow')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('501', 51, 'Who''s singing?')
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('603', null, 'Song for Nobody')
COMMIT
SELECT * FROM ARTIST
ID | NAME | 1 | New Ones | 2 | Adele | 3 | Coldplay | 4 | Silent |
---|
SELECT * FROM ALBUM
ID | ARTIST_ID | NAME | 22 | 2 | James Bond 007: Skyfall | 31 | 3 | Ghost Stories | 32 | 3 | A Head Full of Dreams | 41 | 4 | Without a Sound | 51 | - | From the Unknown | 61 | - | The Lost |
---|
SELECT * FROM SONG
ID | ALBUM_ID | NAME | 203 | 22 | Skyfall | 301 | 31 | Oceans | 302 | 31 | A Sky Full of Stars | 303 | 32 | Everglow | 501 | 51 | Who's singing? | 603 | - | Song for Nobody |
---|
Scalar Subquery
SELECT a.*,
( SELECT count(*)
FROM ALBUM
) AS "Albums"
FROM ALBUM a
ID | ARTIST_ID | NAME | Albums | 22 | 2 | James Bond 007: Skyfall | 6 | 31 | 3 | Ghost Stories | 6 | 32 | 3 | A Head Full of Dreams | 6 | 41 | 4 | Without a Sound | 6 | 51 | - | From the Unknown | 6 | 61 | - | The Lost | 6 |
---|
Corelated Subquery
SELECT ( SELECT a.name
FROM ALBUM a
WHERE a.ID = s.ALBUM_ID
) AS "Album",
s.*
FROM SONG s
Album | ID | ALBUM_ID | NAME | James Bond 007: Skyfall | 203 | 22 | Skyfall | Ghost Stories | 301 | 31 | Oceans | Ghost Stories | 302 | 31 | A Sky Full of Stars | A Head Full of Dreams | 303 | 32 | Everglow | From the Unknown | 501 | 51 | Who's singing? | - | 603 | - | Song for Nobody |
---|
Corelated Subquery
SELECT ( SELECT a.name
FROM ALBUM a
WHERE a.ID = s.ALBUM_ID
) AS "Album",
s.ID AS "Song ID",
s.NAME AS "Song Name"
FROM SONG s
Album | Song ID | Song Name | James Bond 007: Skyfall | 203 | Skyfall | Ghost Stories | 301 | Oceans | Ghost Stories | 302 | A Sky Full of Stars | A Head Full of Dreams | 303 | Everglow | From the Unknown | 501 | Who's singing? | - | 603 | Song for Nobody |
---|
Inline View
SELECT v.*
FROM ( SELECT a.*
FROM ARTIST a
JOIN ALBUM m
ON (m.ARTIST_ID = a.ID)
) v
ID | NAME | 2 | Adele | 3 | Coldplay | 3 | Coldplay | 4 | Silent |
---|
CREATE TABLE PLAYLIST_ANNA
AS
SELECT s.ID, s.NAME
FROM SONG s
WHERE s.ID IN (301, 302, 303, 501, 603)
SELECT * FROM PLAYLIST_ANNA
ID | NAME | 301 | Oceans | 302 | A Sky Full of Stars | 303 | Everglow | 501 | Who's singing? | 603 | Song for Nobody |
---|
CREATE TABLE PLAYLIST_TIM
AS
SELECT s.ID, s.NAME
FROM SONG s
WHERE 1 = 2
SELECT * FROM PLAYLIST_TIM
INSERT INTO PLAYLIST_TIM t
(t.ID, t.NAME)
SELECT s.ID, s.NAME
FROM SONG s
WHERE s.ID IN (301, 302, 303)
COMMIT
SELECT * FROM PLAYLIST_TIM
ID | NAME | 301 | Oceans | 302 | A Sky Full of Stars | 303 | Everglow |
---|
SELECT a.*
FROM ALBUM a
ID | ARTIST_ID | NAME | 22 | 2 | James Bond 007: Skyfall | 31 | 3 | Ghost Stories | 32 | 3 | A Head Full of Dreams | 41 | 4 | Without a Sound | 51 | - | From the Unknown | 61 | - | The Lost |
---|
SELECT s.*
FROM SONG s
ID | ALBUM_ID | NAME | 203 | 22 | Skyfall | 301 | 31 | Oceans | 302 | 31 | A Sky Full of Stars | 303 | 32 | Everglow | 501 | 51 | Who's singing? | 603 | - | Song for Nobody |
---|
SELECT x.ARTIST_ID
FROM ALBUM x
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
ARTIST_ID | - | 3 |
---|
SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
ARTIST_ID | 3 |
---|
SELECT a.*
FROM ALBUM a
WHERE a.ARTIST_ID IN ( SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
)
ID | ARTIST_ID | NAME | 31 | 3 | Ghost Stories | 32 | 3 | A Head Full of Dreams |
---|
SELECT *
FROM (SELECT a.*
FROM ALBUM a
WHERE a.ARTIST_ID IN ( SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
)
) sa
JOIN (SELECT s.*
FROM SONG s
) ss
ON (sa.ID = ss.ALBUM_ID)
ID | ARTIST_ID | NAME | ID | ALBUM_ID | NAME | 31 | 3 | Ghost Stories | 301 | 31 | Oceans | 31 | 3 | Ghost Stories | 302 | 31 | A Sky Full of Stars | 32 | 3 | A Head Full of Dreams | 303 | 32 | Everglow |
---|
SELECT *
FROM (SELECT a.*
FROM ALBUM a
WHERE a.ARTIST_ID IN ( SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
)
) sa
JOIN ( SELECT s.ALBUM_ID
FROM SONG s
GROUP BY s.ALBUM_ID
HAVING COUNT(*) > 1
) ss
ON (sa.ID = ss.ALBUM_ID)
ID | ARTIST_ID | NAME | ALBUM_ID | 31 | 3 | Ghost Stories | 31 |
---|
SELECT *
FROM (SELECT a.*
FROM ALBUM a
WHERE a.ARTIST_ID IN ( SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
)
) sa
JOIN (SELECT s.*
FROM SONG s
WHERE s.ALBUM_ID IN (SELECT xs.ALBUM_ID
FROM SONG xs
GROUP BY xs.ALBUM_ID
HAVING count(*) > 1
)
) ss
ON (sa.ID = ss.ALBUM_ID)
ID | ARTIST_ID | NAME | ID | ALBUM_ID | NAME | 31 | 3 | Ghost Stories | 301 | 31 | Oceans | 31 | 3 | Ghost Stories | 302 | 31 | A Sky Full of Stars |
---|