DHBW Datenbanken Unit 1 - 12 - Subqueries

View All Scripts Login to Run Script

Script Name DHBW Datenbanken Unit 1 - 12 - Subqueries
Description DHBW Datenbanken Unit 1
Area SQL General
Contributor Matthias K. F. Schulz
Created Thursday October 13, 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.
Statement 24
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

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

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

Statement 25

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

6 rows selected.

Statement 26
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

6 rows selected.

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

Statement 27

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

4 rows selected.

Statement 28

CREATE TABLE PLAYLIST_ANNA
AS
SELECT s.ID, s.NAME 
  FROM SONG s     
 WHERE s.ID IN (301, 302, 303, 501, 603)

Table created.

Statement 29
```
SELECT * FROM PLAYLIST_ANNA
```
ID NAME
301 Oceans
302 A Sky Full of Stars
303 Everglow
501 Who's singing?
603 Song for Nobody

5 rows selected.

ID	NAME
301	Oceans
302	A Sky Full of Stars
303	Everglow
501	Who's singing?
603	Song for Nobody

Statement 30

CREATE TABLE PLAYLIST_TIM
AS
SELECT s.ID, s.NAME 
  FROM SONG s     
 WHERE 1 = 2

Table created.

Statement 31
```
SELECT * FROM PLAYLIST_TIM
```
no data found

Statement 32

INSERT INTO PLAYLIST_TIM t
      (t.ID, t.NAME)
SELECT s.ID, s.NAME 
  FROM SONG s     
 WHERE s.ID IN (301, 302, 303)

3 row(s) inserted.

Statement 33
```
COMMIT
```
Statement processed.
Statement 34
```
SELECT * FROM PLAYLIST_TIM
```
ID NAME
301 Oceans
302 A Sky Full of Stars
303 Everglow

3 rows selected.
Statement 35
```
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

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

6 rows selected.

ID	NAME
301	Oceans
302	A Sky Full of Stars
303	Everglow

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 37

  SELECT x.ARTIST_ID
    FROM ALBUM x 
GROUP BY x.ARTIST_ID
  HAVING count(*) > 1

ARTIST_ID
-
3

2 rows selected.

Statement 38

  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

Statement 39

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

2 rows selected.

Statement 40

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

3 rows selected.

Statement 41

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

Statement 42

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.

Additional Information

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