DHBW Datenbanken Unit 1 - 08 - Set Operatoren

View All Scripts Login to Run Script

Script Name DHBW Datenbanken Unit 1 - 08 - Set Operatoren
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,
   ARTIST_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, ARTIST_ID, ALBUM_ID, NAME) VALUES ('203', 2, 22, 'Skyfall')

1 row(s) inserted.

Statement 15

INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('301', 3, 31, 'Oceans')

1 row(s) inserted.

Statement 16

INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('302', 3, 31, 'A Sky Full of Stars')

1 row(s) inserted.

Statement 17

INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('303', 3, 32, 'Everglow')

1 row(s) inserted.

Statement 18

INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('501', null, 51, 'Who''s singing?')

1 row(s) inserted.

Statement 19

INSERT INTO SONG (ID, ARTIST_ID, ALBUM_ID, NAME) VALUES ('603', null, 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.

ID	NAME
1	New Ones
2	Adele
3	Coldplay
4	Silent

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

6 rows selected.

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

Statement 24

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)

Table created.

Statement 25

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)

Table created.

Statement 26

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 27

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

4 rows selected.

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

Statement 28

Alle Zeilen beider Tabellen (mit Duplikaten)

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?

9 rows selected.

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?

Statement 29

Alle Zeilen beider Tabellen (ohne Duplikaten)

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?

6 rows selected.

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

Statement 30

Zeilen die in beiden Tabellen vorkommen

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

3 rows selected.

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

Statement 31

Nur die Songs der von Anna ohne die Songs von Marc

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?

2 rows selected.

ID	NAME
603	Song for Nobody
501	Who's singing?

Statement 32

Nur die Songs der von Marc ohne die Songs von Anna

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

Song ID	Song
203	Skyfall

Additional Information

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