DHBW Datenbanken Unit 1 - 07 - Joins, 3 Tables (Artist, Album, Song)

View All Scripts Login to Run Script

Script Name DHBW Datenbanken Unit 1 - 07 - Joins, 3 Tables (Artist, Album, Song)
Description DHBW Datenbanken Unit 1
Area SQL General
Contributor Matthias K. F. Schulz
Created Tuesday September 20, 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
```
```
ORA-20001: Query must begin with SELECT or WITH
```
More Details: https://docs.oracle.com/error-help/db/ora-20001

Statement 21

SELECT * FROM ARTIST

Statement processed.

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.

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

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

  SELECT * 
    FROM ARTIST a
    JOIN ALBUM  m ON (m.ARTIST_ID = a.ID)
ORDER BY a.NAME, m.NAME


ID NAME ID ARTIST_ID NAME
2 Adele 22 2 James Bond 007: Skyfall
3 Coldplay 32 3 A Head Full of Dreams
3 Coldplay 31 3 Ghost Stories
4 Silent 41 4 Without a Sound

4 rows selected.

ID	NAME	ID	ARTIST_ID	NAME
2	Adele	22	2	James Bond 007: Skyfall
3	Coldplay	32	3	A Head Full of Dreams
3	Coldplay	31	3	Ghost Stories
4	Silent	41	4	Without a Sound

Statement 25

        SELECT a.ID AS "Artist ID", a.NAME AS "Artist",
               m.ID AS "Album ID",  m.NAME AS "Album",
               s.ID AS "Song ID",   s.NAME AS "Song"
          FROM ARTIST a
    INNER JOIN ALBUM m ON (m.ARTIST_ID = a.ID)
    INNER JOIN SONG s  ON (s.ALBUM_ID = m.ID) 
ORDER BY a.NAME, m.NAME


Artist ID Artist Album ID Album Song ID Song
2 Adele 22 James Bond 007: Skyfall 203 Skyfall
3 Coldplay 32 A Head Full of Dreams 303 Everglow
3 Coldplay 31 Ghost Stories 302 A Sky Full of Stars
3 Coldplay 31 Ghost Stories 301 Oceans

4 rows selected.

Artist ID	Artist	Album ID	Album	Song ID	Song
2	Adele	22	James Bond 007: Skyfall	203	Skyfall
3	Coldplay	32	A Head Full of Dreams	303	Everglow
3	Coldplay	31	Ghost Stories	302	A Sky Full of Stars
3	Coldplay	31	Ghost Stories	301	Oceans

Statement 26

         SELECT a.ID AS "Artist ID", a.NAME AS "Artist",
                m.ID AS "Album ID",  m.NAME AS "Album",
                s.ID AS "Song ID",   s.NAME AS "Song"
           FROM ARTIST a
FULL OUTER JOIN ALBUM m ON (m.ARTIST_ID = a.ID)
FULL OUTER JOIN SONG s  ON (s.ALBUM_ID = m.ID) 
       ORDER BY a.NAME, m.NAME, s.NAME


Artist ID Artist Album ID Album Song ID Song
2 Adele 22 James Bond 007: Skyfall 203 Skyfall
3 Coldplay 32 A Head Full of Dreams 303 Everglow
3 Coldplay 31 Ghost Stories 302 A Sky Full of Stars
3 Coldplay 31 Ghost Stories 301 Oceans
1 New Ones  -  -  -  - 
4 Silent 41 Without a Sound  -  - 
 -  - 51 From the Unknown 501 Who's singing?
 -  - 61 The Lost  -  - 
 -  -  -  - 603 Song for Nobody

9 rows selected.

Artist ID	Artist	Album ID	Album	Song ID	Song
2	Adele	22	James Bond 007: Skyfall	203	Skyfall
3	Coldplay	32	A Head Full of Dreams	303	Everglow
3	Coldplay	31	Ghost Stories	302	A Sky Full of Stars
3	Coldplay	31	Ghost Stories	301	Oceans
1	New Ones	-	-	-	-
4	Silent	41	Without a Sound	-	-
-	-	51	From the Unknown	501	Who's singing?
-	-	61	The Lost	-	-
-	-	-	-	603	Song for Nobody

Statement 27

         SELECT a.ID AS "Artist ID", a.NAME AS "Artist",
                m.ID AS "Album ID",  m.NAME AS "Album",
                s.ID AS "Song ID",   s.NAME AS "Song"
           FROM ARTIST a
LEFT OUTER JOIN ALBUM m ON (m.ARTIST_ID = a.ID)
LEFT OUTER JOIN SONG s  ON (s.ALBUM_ID = m.ID) 
       ORDER BY a.NAME, m.NAME, s.NAME


Artist ID Artist Album ID Album Song ID Song
2 Adele 22 James Bond 007: Skyfall 203 Skyfall
3 Coldplay 32 A Head Full of Dreams 303 Everglow
3 Coldplay 31 Ghost Stories 302 A Sky Full of Stars
3 Coldplay 31 Ghost Stories 301 Oceans
1 New Ones  -  -  -  - 
4 Silent 41 Without a Sound  -  - 

6 rows selected.

Artist ID	Artist	Album ID	Album	Song ID	Song
2	Adele	22	James Bond 007: Skyfall	203	Skyfall
3	Coldplay	32	A Head Full of Dreams	303	Everglow
3	Coldplay	31	Ghost Stories	302	A Sky Full of Stars
3	Coldplay	31	Ghost Stories	301	Oceans
1	New Ones	-	-	-	-
4	Silent	41	Without a Sound	-	-

Statement 28

         SELECT a.ID AS "Artist ID", a.NAME AS "Artist",
                m.ID AS "Album ID",  m.NAME AS "Album",
                s.ID AS "Song ID",   s.NAME AS "Song"
           FROM ARTIST a
RIGHT OUTER JOIN ALBUM m ON (m.ARTIST_ID = a.ID)
RIGHT OUTER JOIN SONG s  ON (s.ALBUM_ID = m.ID) 
       ORDER BY a.NAME, m.NAME, s.NAME


Artist ID Artist Album ID Album Song ID Song
2 Adele 22 James Bond 007: Skyfall 203 Skyfall
3 Coldplay 32 A Head Full of Dreams 303 Everglow
3 Coldplay 31 Ghost Stories 302 A Sky Full of Stars
3 Coldplay 31 Ghost Stories 301 Oceans
 -  - 51 From the Unknown 501 Who's singing?
 -  -  -  - 603 Song for Nobody

6 rows selected.

Artist ID	Artist	Album ID	Album	Song ID	Song
2	Adele	22	James Bond 007: Skyfall	203	Skyfall
3	Coldplay	32	A Head Full of Dreams	303	Everglow
3	Coldplay	31	Ghost Stories	302	A Sky Full of Stars
3	Coldplay	31	Ghost Stories	301	Oceans
-	-	51	From the Unknown	501	Who's singing?
-	-	-	-	603	Song for Nobody

Additional Information

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