CREATE TABLE ARTIST
( ID NUMBER,
NAME VARCHAR2(255)
)
CREATE TABLE ALBUM
( ID NUMBER,
ARTIST_ID NUMBER,
NAME VARCHAR2(255)
)
CREATE TABLE SONG
( ID NUMBER,
ARTIST_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, ARTIST_ID, ALBUM_ID, NAME) VALUES ('203', 2, 22, 'Skyfall')
INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('301', 3, 31, 'Oceans')
INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('302', 3, 31, 'A Sky Full of Stars')
INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('303', 3, 32, 'Everglow')
INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('501', null, 51, 'Who''s singing?')
INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('603', null, null, 'Song for Nobody')
COMMIT
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 | ARTIST_ID | ALBUM_ID | NAME | 203 | 2 | 22 | Skyfall | 301 | 3 | 31 | Oceans | 302 | 3 | 31 | A Sky Full of Stars | 303 | 3 | 32 | Everglow | 501 | - | 51 | Who's singing? | 603 | - | - | Song for Nobody |
---|
Erzeugen einer neuen Tabelle auf Basis eines Selects:
CREATE TABLE PLAYLIST_ANNA
AS
SELECT s.ID, s.NAME FROM SONG s
WHERE s.ID IN (301, 302, 303, 501, 603)
Erzeugen einer zweiten neuen Tabelle auf Basis eines Selects:
CREATE TABLE PLAYLIST_MARC
AS
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)
SELECT * FROM PLAYLIST_ANNA
ID | NAME | 301 | Oceans | 302 | A Sky Full of Stars | 303 | Everglow | 501 | Who's singing? | 603 | Song for Nobody |
---|
SELECT * FROM PLAYLIST_MARC
Artist ID | Artist | Album ID | Album | Song ID | Song | 2 | Adele | 22 | James Bond 007: Skyfall | 203 | Skyfall | 3 | Coldplay | 31 | Ghost Stories | 301 | Oceans | 3 | Coldplay | 31 | Ghost Stories | 302 | A Sky Full of Stars | 3 | Coldplay | 32 | A Head Full of Dreams | 303 | Everglow |
---|
UNION ALL
SELECT a.ID, a.NAME FROM PLAYLIST_ANNA a
UNION ALL
SELECT m."Song ID", m."Song" FROM PLAYLIST_MARC m
ORDER BY NAME
ID | NAME | 302 | A Sky Full of Stars | 302 | A Sky Full of Stars | 303 | Everglow | 303 | Everglow | 301 | Oceans | 301 | Oceans | 203 | Skyfall | 603 | Song for Nobody | 501 | Who's singing? |
---|
UNION
SELECT a.ID, a.NAME FROM PLAYLIST_ANNA a
UNION
SELECT m."Song ID", m."Song" FROM PLAYLIST_MARC m
ORDER BY NAME
ID | NAME | 302 | A Sky Full of Stars | 303 | Everglow | 301 | Oceans | 203 | Skyfall | 603 | Song for Nobody | 501 | Who's singing? |
---|
INTERSECT
SELECT a.ID, a.NAME FROM PLAYLIST_ANNA a
INTERSECT
SELECT m."Song ID", m."Song" FROM PLAYLIST_MARC m
ORDER BY NAME
ID | NAME | 302 | A Sky Full of Stars | 303 | Everglow | 301 | Oceans |
---|
MINUS
SELECT a.ID, a.NAME FROM PLAYLIST_ANNA a
MINUS
SELECT m."Song ID", m."Song" FROM PLAYLIST_MARC m
ORDER BY NAME
ID | NAME | 603 | Song for Nobody | 501 | Who's singing? |
---|
MINUS (andere Tabellen-Reihenfolge!)
SELECT m."Song ID", m."Song" FROM PLAYLIST_MARC m
MINUS
SELECT a.ID, a.NAME FROM PLAYLIST_ANNA a
ORDER BY "Song"
Song ID | Song | 203 | Skyfall |
---|