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.
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.
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	NAME
1	New Ones
2	Adele
3	Coldplay
4	Silent

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

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.

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

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.

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

Additional Information

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