CREATE TABLE ARTIST
( ID NUMBER,
NAME VARCHAR2(255)
)
Table created.
CREATE TABLE ALBUM
( ID NUMBER,
ARTIST_ID NUMBER,
NAME VARCHAR2(255)
)
Table created.
CREATE TABLE SONG
( ID NUMBER,
ALBUM_ID NUMBER,
NAME VARCHAR2(255)
)
Table created.
INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')
1 row(s) inserted.
INSERT INTO ARTIST (ID, NAME) VALUES (2, 'Adele')
1 row(s) inserted.
INSERT INTO ARTIST (ID, NAME) VALUES (3, 'Coldplay')
1 row(s) inserted.
INSERT INTO ARTIST (ID, NAME) VALUES (4, 'Silent')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('22', 2, 'James Bond 007: Skyfall')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('31', 3, 'Ghost Stories')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('32', 3, 'A Head Full of Dreams')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('41', 4, 'Without a Sound')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('51', null, 'From the Unknown')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('61', null, 'The Lost')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('203', 22, 'Skyfall')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('301', 31, 'Oceans')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('302', 31, 'A Sky Full of Stars')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('303', 32, 'Everglow')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('501', 51, 'Who''s singing?')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('603', null, 'Song for Nobody')
1 row(s) inserted.
COMMIT
Statement processed.
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 |
---|