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 |
---|
Viele verschachtelte Subqueries:
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 |
---|
Die gleiche Abfrage mit WITH Clause:
WITH
artists_with_more_albums AS (
SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
),
albums AS (
SELECT a.*
FROM ALBUM a
WHERE a.ARTIST_ID IN (SELECT awa.ARTIST_ID
FROM artists_with_more_albums awa)
),
albums_with_more_songs AS (
SELECT xs.ALBUM_ID
FROM SONG xs
GROUP BY xs.ALBUM_ID
HAVING count(*) > 1
),
songs AS (
SELECT s.*
FROM SONG s
WHERE s.ALBUM_ID IN (SELECT aws.ALBUM_ID
FROM albums_with_more_songs aws)
)
SELECT *
FROM albums sa
JOIN songs 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 |
---|
Konstruktion der Query Schritt für Schritt:
WITH
artists_with_more_albums AS (
SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
)
SELECT *
FROM artists_with_more_albums
ARTIST_ID | 3 |
---|
WITH
artists_with_more_albums AS (
SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
),
albums AS (
SELECT a.*
FROM ALBUM a
WHERE a.ARTIST_ID IN (SELECT awa.ARTIST_ID
FROM artists_with_more_albums awa)
)
SELECT *
FROM albums
ID | ARTIST_ID | NAME | 31 | 3 | Ghost Stories | 32 | 3 | A Head Full of Dreams |
---|
WITH
artists_with_more_albums AS (
SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
),
albums AS (
SELECT a.*
FROM ALBUM a
WHERE a.ARTIST_ID IN (SELECT awa.ARTIST_ID
FROM artists_with_more_albums awa)
),
albums_with_more_songs AS (
SELECT xs.ALBUM_ID
FROM SONG xs
GROUP BY xs.ALBUM_ID
HAVING count(*) > 1
)
SELECT *
FROM albums_with_more_songs
ALBUM_ID | 31 |
---|
WITH
artists_with_more_albums AS (
SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
),
albums AS (
SELECT a.*
FROM ALBUM a
WHERE a.ARTIST_ID IN (SELECT awa.ARTIST_ID
FROM artists_with_more_albums awa)
),
albums_with_more_songs AS (
SELECT xs.ALBUM_ID
FROM SONG xs
GROUP BY xs.ALBUM_ID
HAVING count(*) > 1
),
songs AS (
SELECT s.*
FROM SONG s
WHERE s.ALBUM_ID IN (SELECT aws.ALBUM_ID
FROM albums_with_more_songs aws)
)
SELECT *
FROM songs
ID | ALBUM_ID | NAME | 301 | 31 | Oceans | 302 | 31 | A Sky Full of Stars |
---|
WITH
artists_with_more_albums AS (
SELECT x.ARTIST_ID
FROM ALBUM x
WHERE x.ARTIST_ID IS NOT NULL
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
),
albums AS (
SELECT a.*
FROM ALBUM a
WHERE a.ARTIST_ID IN (SELECT awa.ARTIST_ID
FROM artists_with_more_albums awa)
),
albums_with_more_songs AS (
SELECT xs.ALBUM_ID
FROM SONG xs
GROUP BY xs.ALBUM_ID
HAVING count(*) > 1
),
songs AS (
SELECT s.*
FROM SONG s
WHERE s.ALBUM_ID IN (SELECT aws.ALBUM_ID
FROM albums_with_more_songs aws)
)
SELECT *
FROM albums sa
JOIN songs 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 |
---|