0
   

Type 2 Nested Queries

 
 
Reply Fri 19 Jun, 2020 09:42 am
You are required to find suppliers with products over $100 using Type 2 Nested query.
PRODUCT(PROD_ID,PROD_NAME,SUPPLIER_ID,UNITPRICE,PACKAGE,ISDISCONTINUED)

ORDER ITEM(ID,ORDERID,PRODUCTID,UNITPRICE,QUANTITY)


Format:
1. SELECT column-names
2. FROM table-name
3. WHERE EXISTS
4. (SELECT column-name
5. FROM table-name
6. WHERE condition)

  • Topic Stats
  • Top Replies
  • Link to this Topic
Type: Question • Score: 0 • Views: 121 • Replies: 6
No top replies

 
maxdancona
 
  1  
Reply Fri 19 Jun, 2020 09:44 am
@nzarar 17,
This looks like SQL homework. If you give us your answer, I would be happy to help you (I don't like to do other people homework).
maxdancona
 
  1  
Reply Fri 19 Jun, 2020 09:45 am
@nzarar 17,
How far can you get by yourself? Do you understand the question?

The first two lines are defining SQL tables. The second part is setting up the answer with blanks. Coming up with the condition is, I suppose, the trickiest part.
nzarar 17
 
  1  
Reply Fri 19 Jun, 2020 09:49 am
@maxdancona,
i already did. I just want to compare my answer with all answers
maxdancona
 
  1  
Reply Fri 19 Jun, 2020 09:51 am
@nzarar 17,
If you post your answer, I will comment on it. I am a professional software engineer... so I can give you real feedback.
0 Replies
 
nzarar 17
 
  1  
Reply Fri 19 Jun, 2020 09:57 am
@maxdancona,
select suplierid
from product
where exist
(select productid
from orderitem
where unitprice>100$
i don't whether it is correct or not.
maxdancona
 
  1  
Reply Fri 19 Jun, 2020 10:06 am
@nzarar 17,
OK... this is a start, it isn't quite right.

When you analyze these things.... you start by asking what the inner SELECT is going to return.

You say "SELECT productid from orderitem where unitprice > 100". This is going to return all rows from the order item table with a price greater than 100. It will return too many rows... i.e. rows that don't apply.

What you have written will go through each row in product and ask "Is there any row in any product that has a price > 100". This second select will always be true, and so you will return every supplier. The assignment is to not return suppliers with no products that cost more than $100.

What you want for this inner SELECT is to only return rows that belong to a specific supplier. HINT: you want to add a constraint to the condition. Do you want to try it?
0 Replies
 
 

Related Topics

OMR SCAN - Question by Pratik Upadhyay
MS SQL SERVER 2008 R2 SQL Agent - Question by njjimbo
databases - Question by angelrocksvid
PostGresql - Question by angelrocksvid
passing dataset to crystal report - Question by poojaagiri
sql update statement - Discussion by hoachen
 
  1. Forums
  2. » Type 2 Nested Queries
Copyright © 2020 MadLab, LLC :: Terms of Service :: Privacy Policy :: Page generated in 0.03 seconds on 08/03/2020 at 03:30:52