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