CREATE TABLE ARTIST
( ID NUMBER,
NAME VARCHAR2(255)
)
Table created.
CREATE TABLE ALBUM
( ID NUMBER,
ARTIST_ID NUMBER,
NAME VARCHAR2(255)
)
Table created.
CREATE TABLE SONG
( ID NUMBER,
ALBUM_ID NUMBER,
NAME VARCHAR2(255)
)
Table created.
INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')
1 row(s) inserted.
INSERT INTO ARTIST (ID, NAME) VALUES (2, 'Adele')
1 row(s) inserted.
INSERT INTO ARTIST (ID, NAME) VALUES (3, 'Coldplay')
1 row(s) inserted.
INSERT INTO ARTIST (ID, NAME) VALUES (4, 'Silent')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('22', 2, 'James Bond 007: Skyfall')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('31', 3, 'Ghost Stories')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('32', 3, 'A Head Full of Dreams')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('41', 4, 'Without a Sound')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('51', null, 'From the Unknown')
1 row(s) inserted.
INSERT INTO ALBUM (ID, ARTIST_ID, NAME) VALUES ('61', null, 'The Lost')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('203', 22, 'Skyfall')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('301', 31, 'Oceans')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('302', 31, 'A Sky Full of Stars')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('303', 32, 'Everglow')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('501', 51, 'Who''s singing?')
1 row(s) inserted.
INSERT INTO SONG (ID, ALBUM_ID, NAME) VALUES ('603', null, 'Song for Nobody')
1 row(s) inserted.
COMMIT
Statement processed.
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 | 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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
CREATE TABLE PLAYLIST_ANNA
AS
SELECT s.ID, s.NAME
FROM SONG s
WHERE s.ID IN (301, 302, 303, 501, 603)
Table created.
SELECT * FROM PLAYLIST_ANNA
ID | NAME | 301 | Oceans | 302 | A Sky Full of Stars | 303 | Everglow | 501 | Who's singing? | 603 | Song for Nobody |
---|
CREATE TABLE PLAYLIST_TIM
AS
SELECT s.ID, s.NAME
FROM SONG s
WHERE 1 = 2
Table created.
SELECT * FROM PLAYLIST_TIM
no data found
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.
COMMIT
Statement processed.
SELECT * FROM PLAYLIST_TIM
ID | NAME | 301 | Oceans | 302 | A Sky Full of Stars | 303 | Everglow |
---|
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 |
---|
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 |
---|
SELECT x.ARTIST_ID
FROM ALBUM x
GROUP BY x.ARTIST_ID
HAVING count(*) > 1
ARTIST_ID | - | 3 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|