Home » Other » Training & Certification » help with basic select statments
help with basic select statments [message #328764] Sun, 22 June 2008 12:50 Go to next message
jlopez85
Messages: 4
Registered: June 2008
Location: STUDENT
Junior Member
hello i am a student trying to learn basic oracle
here is what i have:
Create table animaltype
(animaltypeid number primary key,
animaltypename varchar2(20) not null);

create table animal
(animalid number primary key,
animalname varchar2(20) not null,
animaltypeid number not null references animaltype(animaltypeid));

create table owner
(ownerid number primary key,
ownername varchar2(20) not null);

create table ownership
(ownerid number not null references owner(ownerid),
animalid number not null references animal(animalid));


insert into animaltype values (1, 'Greyhound');
insert into animaltype values (2, 'Horse');
insert into animaltype values (3, 'Turtle');

insert into animal values (1, 'Trailways', 1);
insert into animal values (2, 'Secretariat', 2);
insert into animal values (3, 'Speedy', 3);
insert into animal values (4, 'Funny Cide', 2);

insert into owner values (1, 'Craig Knight');
insert into owner values (2, 'Bill Hall');
insert into owner values (3, 'Nelson Parker');

insert into ownership values (1, 1);
insert into ownership values (1, 3);
insert into ownership values (2, 2);
insert into ownership values (3, 1);
insert into ownership values (3, 2);
insert into ownership values (3, 3);
insert into ownership values (3, 4);

i need to list ownerids of all owners of animal number 1: this is what i have:
select* from owner where owner.ownerid = animal.ownerid and animalid = '1';

i also need to list the names of all animal types who are a horse, for this i have:
select* from animal where animal.animalname = animaltype.animalname and animaltype = 'Horse';

please help thanks
Re: help with basic select statments [message #328766 is a reply to message #328764] Sun, 22 June 2008 13:01 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You need to join the tables you want to use.

Further Examples
Re: help with basic select statments [message #328767 is a reply to message #328764] Sun, 22 June 2008 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above
Re: help with basic select statments [message #328769 is a reply to message #328764] Sun, 22 June 2008 13:23 Go to previous messageGo to next message
jlopez85
Messages: 4
Registered: June 2008
Location: STUDENT
Junior Member
sorry i have Oracle Database 10g Release 2 (10.2.0.3/10.2.0.4) for vista
Re: help with basic select statments [message #328785 is a reply to message #328764] Sun, 22 June 2008 16:15 Go to previous messageGo to next message
jlopez85
Messages: 4
Registered: June 2008
Location: STUDENT
Junior Member
i'm trying to list names of all animal types that are Horse
here is what i have
select* from animaltype, animal where animaltypename.animaltypeid = animalname.animaltypeid;

i keep getting this error; animalname.animaltypeid invalid identifier
Re: help with basic select statments [message #328787 is a reply to message #328764] Sun, 22 June 2008 16:33 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>i keep getting this error; animalname.animaltypeid invalid identifier

I am not surprised "animalname" is not a valid table_name or alias.


http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

The SQL Reference manual can be found at http://tahiti.oracle.com

You should consider getting a copy of SQL for Dummies.

Previous Topic: Joins
Next Topic: 3 homework questions (merged)
Goto Forum:
  


Current Time: Fri Mar 29 02:08:47 CDT 2024