/*
רישמו שאילתה המציגה את החיה שמספר הספקים שמספקים לה מזון הוא הנמוך ביותר
אבל יש לפחות שני ספקים שונים המספקים לה מזון
*/
declare @Animal astable (Aid int,name VARCHAR(14))
declare @eat astable (Aid int,Fid int)
declare @Food astable (Fid int,name VARCHAR(14))
declare @supplied astable (Fid int,Sid int)
declare @supplier astable (Sid int,name VARCHAR(14))
insertinto @Animal values (1,'נמר')
insertinto @Animal values (2,'כלב')
insertinto @Animal values (3,'חתול')
insertinto @Animal values (4,'עכבר')
insertinto @Animal values (5,'דג')
insertinto @Food values (1,'דוגלי')
insertinto @Food values (2,'חלב')
insertinto @Food values (3,'בשר')
insertinto @Food values (4,'לחם')
insertinto @eat values (1,2)
insertinto @eat values (1,3)
insertinto @eat values (1,4)
insertinto @eat values (2,1)
insertinto @eat values (2,2)
insertinto @eat values (3,3)
insertinto @eat values (4,2)
insertinto @eat values (4,3)
insertinto @eat values (5,4)
insertinto @supplied values (1,1)
insertinto @supplied values (1,2)
insertinto @supplied values (1,3)
insertinto @supplied values (2,1)
insertinto @supplied values (3,2)
insertinto @supplied values (3,6)
insertinto @supplied values (4,1)
/*
-- נציג את הנתונים שהכנסנו לטבלאות לשם בדיקה
select * from @Animal
select * from @Food
select * from @eat
select * from @supplied
*/
--נכין שאילתה שמציגה כל הנתונים מחוברים כטבלה אחת
--אני מתבסס על טבלת החיות כטבלה מרכזית מפני שלא מעניינים אותנו תוצאות שאין בטבלה זו
--לטבלה זו אני מחבר את התוצאות מהטבלאות המקושרות
select
A.Aid,A.name as Animal
,F.Fid, f.name as Food
,s.Sid as supplierID
from @Animal A
left join @eat E on A.Aid = E.Aid
left join @Food F on E.Fid = F.Fid
left join @supplied S on F.Fid = S.Fid
-- עתה נוכל בקלות להציג חישוב של כמה תוצאות יש לנו
-- תחילה נסנן נתונים כפולים
-- למשל ניתן לראות שספק מספר 1 מספק לנמר גם לחם וגם חלב
-- זהו נתון כפול כי לא מעניין אותנו כמה הוא מספק אלא רק תוצאה אחת לכל ספק לכל סוג חיה
-- נוסיף סינון ייחודי ונוריד את הצגת המזון
selectdistinct
A.Aid,A.name as Animal
--,F.Fid, f.name as Food
,s.Sid as supplierID
from @Animal A
left join @eat E on A.Aid = E.Aid
left join @Food F on E.Fid = F.Fid
left join @supplied S on F.Fid = S.Fid
-- עתה נוכל לספור כמה ספקים מספקים לכל חיה
select
Tbl.Aid,Tbl.Animal
,COUNT(*) suppliersNum
from (
selectdistinct
A.Aid,A.name as Animal
--,F.Fid, f.name as Food
,s.Sid as supplierID
from @Animal A
left join @eat E on A.Aid = E.Aid
left join @Food F on E.Fid = F.Fid
left join @supplied S on F.Fid = S.Fid
) Tbl
groupby Tbl.Aid,Tbl.Animal
-- וסינון אחרון נבחר מתוצאות אלו את התוצאה המינימלית אבל שגדולה מ2
selecttop 1 Aid,Animal
from (
select
Tbl.Aid,Tbl.Animal
,COUNT(*) suppliersNum
from (
selectdistinct
A.Aid,A.name as Animal
--,F.Fid, f.name as Food
,s.Sid as supplierID
from @Animal A
left join @eat E on A.Aid = E.Aid
left join @Food F on E.Fid = F.Fid
left join @supplied S on F.Fid = S.Fid
) Tbl
groupby Tbl.Aid,Tbl.Animal
) Tbl
where suppliersNum > 1
orderby suppliersNum
-- עד כאן לפתרון לימודי ועתה הגיע הזמן לכתיבת קוד קצת יותר יעיל