Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help with sql-query questions
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

frogie4life84
Starting Member

Netherlands
3 Posts

Posted - 03/20/2013 :  07:30:53  Show Profile  Reply with Quote
Consider the following schema:
Suppliers(sid,sname,saddress)
Parts(pid,pname,color)
Catalog(sid->Suppliers,pid->Parts,cost)sid and pid are in this table FK
The primary keys are underlined and ->Rel indicates a foreign key relationship with the primary key of Rel.

Formulate the following queries SQL avoid use of GROUP BY in favor of existential quantification.

question 1: find the pids of parts that are supplied by at least two different suppliers
question 2: find the sids of supplies who supply every red part question 3: find the sids of suppliers who supply every red part or every green part

Somehow When i created this question with the query I got it wrong. I wonder how I the query used with sub-query and existential quantification. Can someone help me please

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 03/20/2013 :  08:36:55  Show Profile  Reply with Quote
--question 1: find the pids of parts that are supplied by at least two different suppliers
SELECT pid
FROM (SELECT DISTINCT pid, DENSE_RANK() OVER(PARTITION BY Pid ORDER BY sid) rn
		fROM Catalog
	 ) t
WHERE t.rn>=2;

Let us know what you have tried....

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/20/2013 :  09:59:31  Show Profile  Reply with Quote
these are homework questions
show us what your attempt was otherwise we dont help with assignments

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

frogie4life84
Starting Member

Netherlands
3 Posts

Posted - 03/20/2013 :  13:21:17  Show Profile  Reply with Quote
this is how I have done

Question 1

SELECT P.pid
FROM Catalog P, Catalog C
WHERE P.pid = C.pid AND P.sid != C.sid

Question 2
find the sids of supplies who do not supply every red part!


SELECT C.sid
FROM Catalog C
WHERE NOT EXISTS (SELECT P.pid
FROM Parts P
WHERE NOT EXISTS (SELECT C1.sid
FROM Catalog C1
WHERE C1.sid = C.sid
AND C1.pid = P.pid
AND p.color = red'))

question 2: find the sids of supplies who supply every red part

SELECT C.sid
FROM Catalog C
WHERE NOT EXISTS (SELECT P.pid
FROM Parts P
WHERE NOT EXISTS (SELECT C1.sid
FROM Catalog C1
WHERE C1.sid = C.sid
AND C1.pid = P.pid
AND p.color != red'))

question 3: find the sids of suppliers who supply every red part or every green part

SELECT C.sid
FROM Catalog C
WHERE NOT EXISTS (SELECT P.pid
FROM Parts P
WHERE NOT EXISTS (SELECT C1.sid
FROM Catalog C1
WHERE C1.sid = C.sid
AND C1.pid = P.pid
AND p.color != red'))
Union
WHERE NOT EXISTS (SELECT P.pid
FROM Parts P
WHERE NOT EXISTS (SELECT C1.sid
FROM Catalog C1
WHERE C1.sid = C.sid
AND C1.pid = P.pid
AND p.color != green')

other solution of question 3
WHERE NOT EXISTS (SELECT P.pid
FROM Parts P
WHERE NOT EXISTS (SELECT C1.sid
FROM Catalog C1
WHERE C1.sid = C.sid
AND C1.pid = P.pid
AND p.color != green'
AND p.color != red))


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/20/2013 :  13:41:37  Show Profile  Reply with Quote
1.

SELECT
FROM Parts p
INNER JOIN (SELECT pid
            FROM Catalog
            GROUP BY pid
            HAVING COUNT(DISTINCT sid) >=2)s
ON s.pid = p.pid



2.

SELECT s.*
FROM Suppliers s
CROSS JOIN (SELECT pid FROM Parts WHERE Color='Red')p
LEFT JOIN Catalog c
ON c.sid = s.sid
AND c.pid = p.id
GROUP BY s.sid
HAVING SUM(CASE WHEN c.sid IS NULL THEN 1 ELSE 0 END)=0


3.

SELECT s.*
FROM Suppliers s
CROSS JOIN (SELECT pid FROM Parts WHERE Color IN ('Red','Green'))p
LEFT JOIN Catalog c
ON c.sid = s.sid
AND c.pid = p.id
GROUP BY s.sid
HAVING COUNT(DISTINCT CASE WHEN c.sid IS NULL THEN p.Color ELSE 0 END)<=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

frogie4life84
Starting Member

Netherlands
3 Posts

Posted - 03/20/2013 :  13:45:32  Show Profile  Reply with Quote
Thx for the solution. that is what I first thought but it was wrong @ the exam because Formulate the following queries SQL avoid use of GROUP BY in favor of existential quantification.

so I had to use sub-queries with existantial quantification.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000