DBMS: SQL query with inner query (supplier,catalog,red part)

Enter Name of Correspondence Set, Page Number, Question Number, Your Name and Roll Number for Query!

Moderator: CS Moderators Team

DBMS: SQL query with inner query (supplier,catalog,red part)

Postby 4815162342 » Tue Jan 20, 2009 10:24 am

Consider the following query

Code: Select all
Schema:
suppliers (sid:integer, sname:string, address:string)
parts(pid:integer, pname:string, color:string)
catalog(sid:integer, pid:integer, cost:real)

select distinct sname
from catalog, suppliers
where catalog.sid=suppliers.sid
and pid in (select pid from parts where color="Red")


Which one of the following describes the above query?
A) the snames of suppliers who supply every red part
B) the snames of suppliers who supply no red parts
C) the sname of suppliers who supply at least 1 red part
D) the sname of suppliers who supply maximum red parts

Answer given is A but I think it is C. Anyone else for C?
thanks,
az.
TAOCP
4815162342
Gatementor Regular
Gatementor Regular
 
Posts: 240
Joined: Sat Jun 28, 2008 2:01 pm
Currently what are you doing?: Am Working
My College/Company:: NITC 2001
Roll Number: 622031
Packages: Others
City: Bangalore

Re: DBMS: SQL query with inner query (supplier,catalog,red part)

Postby 4815162342 » Tue Jan 20, 2009 12:26 pm

i made an example and checked and got it as C.
parts (ignoring pname)
pa red
pb red
pc blue
pd red

suppliers (ignoring address)
s1 sn1
s2 sn2
s3 sn3

catalog (ignoring cost)
s1 pa
s1 pb
s1 pc
s2 pa
s2 pc
s3 pc

the inner query result
R1:
pa
pb
pd

the join on sid (ignoring cost and sname):
R2:
s1 pa sn1
s1 pb sn1
s1 pc sn1
s2 pa sn2
s2 pc sn2
s3 pc sn3

select * R2 where pid is in R1
s1 pa sn1
s1 pb sn1
s2 pa sn2

projection of sid
s1, s2

neither of them supply every red part. even if you ignore pd, s2 does not supply every red part.

so i believe the answer is C.
thanks,
az.
TAOCP
4815162342
Gatementor Regular
Gatementor Regular
 
Posts: 240
Joined: Sat Jun 28, 2008 2:01 pm
Currently what are you doing?: Am Working
My College/Company:: NITC 2001
Roll Number: 622031
Packages: Others
City: Bangalore

Re: DBMS: SQL query with inner query (supplier,catalog,red part)

Postby ankit1986 » Sun Jan 25, 2009 10:41 pm

yes it's (C) ..... i am sure 110% ...... material will have some mistakes ..... we can omit some things ..... :)
ankit1986
Gatementor Newbie
Gatementor Newbie
 
Posts: 143
Joined: Thu Sep 18, 2008 10:48 am
Currently what are you doing?: Am Studying
My College/Company:: BITS Pilani
Roll Number: 0
Packages: Classroom Coaching (CL)
City: Delhi/Pilani

Re: DBMS: SQL query with inner query (supplier,catalog,red part)

Postby 4815162342 » Sun Jan 25, 2009 11:04 pm

great. thanks..
thanks,
az.
TAOCP
4815162342
Gatementor Regular
Gatementor Regular
 
Posts: 240
Joined: Sat Jun 28, 2008 2:01 pm
Currently what are you doing?: Am Working
My College/Company:: NITC 2001
Roll Number: 622031
Packages: Others
City: Bangalore

Re: DBMS: SQL query with inner query (supplier,catalog,red p

Postby prajakta » Thu Jun 13, 2013 8:29 pm

the ans is A
prajakta
 
Posts: 3
Joined: Thu Jun 13, 2013 8:17 pm
My College/Company:: ses coe.dhule,
Roll Number: 75420016

Re: DBMS: SQL query with inner query (supplier,catalog,red p

Postby sameer2009 » Fri Jun 14, 2013 10:18 am

Code: Select all
Schema:
suppliers (sid:integer, sname:string, address:string)
parts(pid:integer, pname:string, color:string)
catalog(sid:integer, pid:integer, cost:real)

select distinct sname
from catalog, suppliers
where catalog.sid=suppliers.sid
and pid in (select pid from parts where color="Red")


i guess the catalog is relationship between suppliers and parts.
so
if the relationship is such that....every supplier provides every type of parts ...then ans is [A]
if the relationship is such that....every supplier provides some of the parts ...then ans is [C]

in any case the above query prints according to [C]
Check this out!!! Free Android App for Computer Science and Engineering Exam.
Download .apk file @ tinyurl.com/csexamapp
See How To Use @ tinyurl.com/csexamapplication
sameer2009
Gatementor Guru
Gatementor Guru
 
Posts: 1433
Joined: Tue Sep 09, 2008 5:29 pm
My College/Company:: lt
Roll Number: 99999


Return to CS Question

Who is online

Users browsing this forum: No registered users and 2 guests