OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Query [message #6915]

Sun, 11 May 2003 07:07

Nazma Bepat
Messages: 34
Registered: May 2003

Member

Thanks so much, you have really helped me out. I am
really new to Oracle, learning it online and is really
struggling.

Can you help me with the following:

QUESTION 1:
Write a query to display the PRODID, DESCRIP from the
product table and also display the ORDID, ITEMID,
ACTUALPRICE from the item table. Use table aliass in
your answer.

My answer: (which is not working)

SELECT PRODID, DESCRIP
>From Product_table
Select ORDID, ITEMIS, ACTUALPRICE
>From item_table

QUESTION 2:

Modify question 1 so that it displays the information
only when the qty in the item table is greater than
3000.

MY ANSWER: (not working)
SELECT PRODID, DESCRIP
>From Product_table
Select ORDID, ITEMIS, ACTUALPRICE
>From item_table
Where qty > 3000

QUESTION 3:
Modify question 2 so that it displays the information
only when the qty is less than 1000 and the
actualprice is greater than 50.

MY ANSWER: (NOT WORKING)

SELECT PRODID, DESCRIP
>From Product_table
Select ORDID, ITEMIS, ACTUALPRICE
>From item_table
Where qty < 1000 and actualprice > 50

The 3 questions above is not working for me, can you
please assist me.

Thanks

Report message to a moderator

Re: Oracle Query [message #6923 is a reply to message #6915]

Mon, 12 May 2003 00:17

Maaher
Messages: 7065
Registered: December 2001

Senior Member

0. You need to proivde us with table layout details. If you issue the following command in SQL*Plus, you see the layout:

SQL> DESC table_name

.
1. You need to join the tables if you want to display results from 2 tables. I assume the item_table has a prodid somewhere. If so, then you can use:

SELECT S.PRODID
     , S.DESCRIP
     , I.ORDID
     , I.ITEMID
     , I.ACTUALPRICE
  FROM Product_table <B>S</B> -- S being the <B>ALIAS for product_table</B>
     , Item_table    I
 <B>WHERE</B> s.prodid = i.prodid -- this is the join condition

. You need to prefix the columns in the select list with a table name or alias (if you provided one) that are present in both tables.

2. Use a join (start with answer 1 + add the where clause for the quantity).

3. See answer 2.

BTW: If you cannot access the link provided: create a free (really, no strings attached) account for OTN and retry.
MHE

Report message to a moderator

Previous Topic:	TEMP table
Next Topic:	ORACLE9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 17:08:34 CDT 2024