SQL Abfrage oracle

[freakhouse]

Hallo Community,

ich habe eine kleine Frage zu einer Abfrage. Zu Grunde liegt eine CD-Datenbank.

drop table instrument;
drop table track;
drop table Musiker;
drop table album;
drop table interpret;

create table interpret
(
IID integer primary key,
name varchar(30)
);

create table album
(
AID integer primary key,
IID integer,
name varchar (30),
jahr number,
constraint fk_album_interpret foreign key (IID) references Interpret (IID)
);
create table track
(
TID integer primary key,
AID integer,
NR integer,
name varchar (30),
Länge integer,
constraint fk_track_album foreign key (AID) references album (AID)
);

create table Musiker
(
MUID integer primary key,
IID integer,
Name varchar (30),
constraint fk_Musiker_Interpret foreign key (IID) references Interpret (IID)
);

create table Instrument
(
INID integer primary key,
MUID integer,
name varchar (30),
constraint fk_Instrument_Musiker foreign key (MUID) references Musiker (MUID)
);

insert into interpret values (1,'Linkin Park');
insert into interpret values (2,'Evergrey');
insert into interpret values (3,'Rammstein');
insert into interpret values (4,'Billy Talent');

insert into album values (1,1,'Meteora',1999);
insert into album values (2,2,'Inner Circle',2004);
insert into album values (3,3,'Rosenrot',2005);
insert into album values (4,4,'Billy Talent III',2009);

insert into track values (1,1,1,'Foreword',200);
insert into track values (2,1,2,'Dont Say',205);
insert into track values (3,1,3,'Lying from you',201);
insert into track values (4,1,4,'Hit the Floor',202);
insert into track values (5,1,5,'Faint',201);
insert into track values (6,1,6,'Figure',203);
insert into track values (7,1,7,'Breaking the Habbit',202);
insert into track values (8,1,8,'Easier to Run',207);
insert into track values (9,1,9,'Somewhere I Belong',203);
insert into track values (10,1,10,'From The Inside',202);
insert into track values (11,1,11,'Nobodys Listening',209);
insert into track values (12,1,12,'Session',207);
insert into track values (13,1,13,'Numb',205);

insert into track values (14,2,1,'A touch of blessing',234);
insert into track values (15,2,2,'Ambassador',345);
insert into track values (16,2,3,'In the wake of the weary',350);
insert into track values (17,2,4,'Harmless wishes',234);
insert into track values (18,2,5,'Waking up blind',365);
insert into track values (19,2,6,'Move than ever',432);
insert into track values (20,2,7,'The essence of conviction',465);
insert into track values (21,2,8,'Where all good sleep',354);
insert into track values (22,2,9,'Faith restored',333);
insert into track values (23,2,10,'When the walls go down',234);
insert into track values (24,2,11,'Im sorry',222);
insert into track values (25,2,12,'Recreation',575);
insert into track values (26,2,13,'Madness caught another victim',233);

insert into track values (27,3,1,'Benzin',234);
insert into track values (28,3,2,'Mann gegen Mann',233);
insert into track values (29,3,3,'Rosenrot',344);
insert into track values (30,3,4,'Spring',544);
insert into track values (31,3,5,'Stirb nicht Vor Mir',334);
insert into track values (32,3,6,'Zerstören',444);
insert into track values (33,3,7,'Hilf mir',345);
insert into track values (34,3,8,'Te Quiero Puta',346);
insert into track values (35,3,9,'Feuer und Wasser',654);
insert into track values (36,3,10,'Ein Lied',275);
 
insert into track values (37,4,1,'Devil on my Shoulder',456);
insert into track values (38,4,2,'Rusted from The Rain',373);
insert into track values (39,4,3,'Tears into Wine',344);
insert into track values (40,4,4,'White Sparrows',334);
insert into track values (41,4,5,'Pocketful of Dreams',333);
insert into track values (42,4,6,'The Dead Cant Testify',223);
insert into track values (43,4,7,'Diamond On A Landmine',233);
insert into track values (44,4,8,'Turn your Back',333);
insert into track values (45,4,9,'Saint Veronika',334);
insert into track values (46,4,10,'Sudden Movements',387);
insert into track values (47,4,11,'Definition of Destiny',334);

insert into musiker values (1,1,'Chester Bennington');
insert into musiker values (2,1,'Mike Shinoda');
insert into musiker values (3,1,'Brad Delson');
insert into musiker values (4,1,'David Farell');
insert into musiker values (5,1,'Rob Bourdon');
insert into musiker values (6,1,'Joseph Hahn');

insert into instrument values (1,1,'Sänger');
insert into instrument values (2,2,'E-Gitarre');
insert into instrument values (3,3,'Gitarre');
insert into instrument values (4,4,'E-Bass');
insert into instrument values (5,5,'Schlagzeug');
insert into instrument values (6,6,'Keyboard');

jetzt will ich einige Abfragen erstellen, bzw ich muss
bei einer komme ich allerdings nicht weiter. ist bestimmt nicht schwer.

d) Schreiben Sie eine Abfrage, die alle Interpretennamen zusammen mit ihren Alben ausgibt.

also nach dem schema. Select, From, where

danke im voraus.

 

[tRITON]

Hausaufgaben einfach selber machen. Lernst Du auch was bei

 

[atomrofler]

Da fehlen die Worte.

 

[IceMatrix]

also nach dem schema. Select, From, where

besser nach dem schema Mach, Es, Selbst

 

[Riker]

Select *
from myHirn mH
where mH.inhalt is not null;


kommt evtl. nicht so viel bei rum - aber könnte klappen

 

[abcb12]

jetzt seit mal nicht so

Diese Abfrage kannst du mit einem JOIN lösen.

SELECT
      i.name, a.name 
FROM
      interpret i JOIN album a ON (i.IID = a.IID)

oder in select, from, where so:

SELECT
      i.name, a.name 
FROM
      interpret i, album a 
WHERE
      i.IID = a.IID