SQL Server Forums
Profile | Register | 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
 New Topic  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
2202 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
52309 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
52309 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  
 New 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.12 seconds. Powered By: Snitz Forums 2000