DHBW Datenbanken Unit 1 - 14 - WITH Clause

View All Scripts Login to Run Script

Script Name DHBW Datenbanken Unit 1 - 14 - WITH Clause
Description DHBW, Datenbanken, Vorlesung
Area SQL General
Contributor Matthias K. F. Schulz
Created Wednesday October 19, 2016

Statement 1

CREATE TABLE ARTIST
(  ID          NUMBER,
   NAME        VARCHAR2(255)
)

Table created.

Statement 2

CREATE TABLE ALBUM
(  ID          NUMBER,
   ARTIST_ID   NUMBER,
   NAME        VARCHAR2(255)
)

Table created.

Statement 3

CREATE TABLE SONG
(  ID          NUMBER,
   ALBUM_ID    NUMBER,
   NAME        VARCHAR2(255)
)

Table created.

Statement 4

INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')

1 row(s) inserted.

Statement 5

INSERT INTO ARTIST (ID, NAME) VALUES (2, 'Adele')

1 row(s) inserted.

Statement 6

INSERT INTO ARTIST (ID, NAME) VALUES (3, 'Coldplay')

1 row(s) inserted.

Statement 7

INSERT INTO ARTIST (ID, NAME) VALUES (4, 'Silent')

1 row(s) inserted.

Statement 8

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('22',  2, 'James Bond 007: Skyfall')

1 row(s) inserted.

Statement 9

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('31',  3, 'Ghost Stories')

1 row(s) inserted.

Statement 10

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('32',  3, 'A Head Full of Dreams')

1 row(s) inserted.

Statement 11

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('41',  4, 'Without a Sound')

1 row(s) inserted.

Statement 12

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('51',  null, 'From the Unknown')

1 row(s) inserted.

Statement 13

INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('61',  null, 'The Lost')

1 row(s) inserted.

Statement 14

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('203', 22, 'Skyfall')

1 row(s) inserted.

Statement 15

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('301', 31, 'Oceans')

1 row(s) inserted.

Statement 16

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('302', 31, 'A Sky Full of Stars')

1 row(s) inserted.

Statement 17

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('303', 32, 'Everglow')

1 row(s) inserted.

Statement 18

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('501', 51, 'Who''s singing?')

1 row(s) inserted.

Statement 19

INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('603', null, 'Song for Nobody')

1 row(s) inserted.

Statement 20
```
COMMIT
```
Statement processed.
Statement 21
```
SELECT * FROM ARTIST
```
ID NAME
1 New Ones
2 Adele
3 Coldplay
4 Silent

4 rows selected.
Statement 22
```
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

6 rows selected.
Statement 23
```
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

6 rows selected.

ID	NAME
1	New Ones
2	Adele
3	Coldplay
4	Silent

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

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

Statement 24

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

2 rows selected.

Statement 25

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

2 rows selected.

Statement 26

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

Statement 27

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

2 rows selected.

Statement 28

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

Statement 29

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

2 rows selected.

Statement 30

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

2 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials