ÞæÇÚÏ ÇáÈíÇäÇÊ Èíä ÇáäÙÑíÉ æÇáÊØÈíÞ
ÇÚÒÇÆí ÇáßÑÇã
ÇáÓáÇã Úáíßã æÑÍãÉ Çááå æÈÑßÇÊå
ÓääÇÞÔ Ýí åÐÇÇáãæÖæÚ ÚÏÏÇð ãä ÇáãÔÇßá ÇáÊí ÞÏ ÊÕÇÏÝßã áÏì ÊÚÇãáßã ãÚ ÇáÈíÇäÇÊ , æÓäÚÑÖ ÇáÍáæá Çáããßäå áåÇ. æÇÞÊÑÍ Ãä ÊÍÇæáæÇ ÅíÌÇÏ Íá ÞÈá ÞÑÇÁÉ ÇáÍá ÇáãÚØì Ýí ÇáäÕ . æÚáíäÇ Ãä äÔíÑ åäÇ Åáí Ãä åäÇß ÚÏÉ ØÑÞ áÍá ãÔßáÉ ãÚíäÉ . Ýí ÇáÍÞíÞå ÞÏ äÊãßä ãä ÇáÍÕæá Úáì Íá ÃßËÑ ÝÚÇáíå ãä ÇáÍá ÇáãÞÊÑÍ åäÇ Åä ÇáåÏÝ ÇáÃÓÇÓí ãä åÐå ÇáãÔÇßá æÇáÍáæá åí ÅÚØÇÄßã ÛÐÇÁ ÝßÑíÇ äÇÝÚÇ .
ÞÈá ÇáÈÏÁ, ÏÚæäÇ äÔíÑ Åáí Ãä ÇáßËíÑ ãä ÇáÍáæá ÇáãÞÊÑÍÉ íÚÊãÏ Úáì ÇÓÊÎÏÇã ÇáÇÓÊÚáÇãÇÊ ÇáÝÑÚíå .
íÓãÍ Access SQL ÈÃÓÊÎÏÇã ÚÈÇÑÇÊ SeIectÖãä ÚÈÇÑÇÊ SeIect ÃÎÑì (ßãÇ åí ÇáÍÇá Ýí ÚÈÇÑÇÊ ÃÎÑì ãËá ÚÈÇÑÇÊ ( INSERT INTO) . ÊÏÚì ÚÈÇÑÉ SeIect ÇáÏÇÎáíå Ãæ ÇáãÊÔÇÈßå ÈÇáÇÓÊÚáÇã ÇáÝÑÚí.
áÇÍÙ Ãäå íãßä Ãä ÊÖãä ÚÈÇÑÉ SeIect ÏÇÎáíå Öãä ÚÈÇÑÉ SeIect ÃÓÇÓíÉ ÝÞØ ÅÐÇ ßÇäÊ ÚÈÇÑÉ SeIect ÇáÏÇÎáíÉ ÊÚíÏ ÓÌáÇ æÇÍÏÇ Úáì ÇáÃÛáÈ æáÈíÇä Ðáß áäÃÎÐ ÚÈÇÑÉ SQL ÇáÃÓÇÓíÉ ÇáÊÇáíÉ:

SELECT HOUR, 
(Select Count(Interval) From StartTimes WHERE (StartTime <=Hour)) 
FROM HOURS

ÊÚíÏ ÚÈÇÑÉ Select ÇáÏÇÎáíÉ :

Select Count(Interval) From StartTimes WHERE (StartTime <=Hour)

Úáì ÇáÃÛáÈ ÓÌáÇð æÇÍÏÇ áÃäåÇ ÊÚíÏ ÚÏÏ (Count) áÇÍÙ ÃíÖÇð Ãä ÙÑÝ WHEREÝí ÚÈÇÑÉ SeIect ÇáÏÇÎáíå íÔíÑ Åáì ÍÞá HOUR ÇáÐí åæ ÌÒÁ ãä ÚÈÇÑÉ SQL ÇáÑÆíÓíÉ , æÈÇáÊÇáí ÝÇäå íÞæã ÈÑÈØ ÇáÞíãå ÇáãÓÊÑÌÚå ááÚÈÇÑÉ ÇáÏÇÎáíÉ Åáì ÇáÓÌá ÇáÍÇáí Ýí ÌÏæá HOURS
ÇáãÌÇãíÚ ÇáÌÇÑíÉ : Running Sums
íÚÊÈÑ ÍÓÇÈ ÇáãÌÇãíÚ ÇáÌÇÑíÉ ãä ÇáÚãáíÇÊ ÇáÔÇÆÚå æáÈíÇä åÐÇ ÇáãÝåæã ÓäÃÎÐ ÇáÌÏæá ( 1-1) ÇáÐí íÊÖãä ÇáãÏÏ ÇáÒãäíå ( ãÍÓæÈÉ ÈÇáÓÇÚÉ ) áÚÏÉ ÃÍÏÇË .

æäÑíÏ Ãä äÍÓÈ áßá ÍÏË ãÌãæÚ ÇáãÏÏ ÇáÒãäíÉ ááÂÍÏÇË ÇáÊí ÊÓÈÞå íÏÚí åÐÇ ÇáãÌãæÚ ÈÇáãÌãæÚ ÇáÌÇÑí .
ÇáÍá : Solution
Åä ÅÍÏì ØÑÞ ÇáÍá åí ÈÅÌÑÇÁ ÇáÌÏÇÁ ÇáÓáãí ááÌÏæá ãÚ äÝÓå äÓÊØíÚ ÈåÐå ÇáØÑíÞå ÇáæÕæá Åáí ÌãíÚ ÇáÓÌáÇÊ ÇáÊí íÓÈÞ ÑÞã ÍÏËåÇ ( Event ) ÑÞã ÓÌá (ÃæÍÏË) ãÚíä . Úáì ÓÈíá ÇáãËÇá ,ãä ÃÌá ÇáÓÌá ÇáÎÇãÓ ¡ äÍÊÇÌ Çáí ÇáæÕæá Åáí ÇáÓÌáÇÊ ÇáÊí ÊÊÖãä ÇáÇÍÏÇË ÑÞã 1 æÍÊì 4 . Ãä ÇáÌÏÇÁ ÇáÓáãí íÚØíäÇ åÐå ÇáÓÌáÇÊ .
Åáíß ÚÈÇÑÉ SQL ÇáÊí ÊÞæã ÈåÐÇ ÇáÚãá :

SELECT Running.Event, Sum(RunningCopy.Duration) AS StartTime 
FROM Running, Running AS RunningCopy 
WHERE (RunningCopy.Event < Running.Event) 
GROUP BY Running.Event

íÞæã ÙÑÝ FORM ÈÅäÔÇÁ ÇáÌÏÇÁ ÇáÓáãí ááÌÏæá ãÚ äÝÓå . ÃãÇ ÙÑÝ WHERE ÝíÍÏÏ ÇáÓÌáÇÊ ÇáÊí ÊÍÞÞ ÇáãÚíÇÑ

Running Copy.Event < Running. Event

Ãí Çä ÇáÓÌáÇÊ ÇáÊí ÓÊÞÏã ÇáãÚáæßÇÊ Íæá ÇáÓÌáÇÊ ÇáÊí ÊÓÈÞ ßá ÓÌá Ýí ÇáÌÏæá ( 1-1 ).
ÃÎíÑÇ ¡ äÞæã ÈÊÌãíÚ ÇáäÊÇÆÌ ÍÓÈ ÇáÍÏË ( Group By Event ) æÍÓÇÈ ãÌãæÚ ÇáãÏÏ ÇáÒãäíÉ . Çä ÇáãÔßáÉ åí Çä ÇáÌÏÇÁ ÇáÓáãí ÛíÑ ÝÚÇá æíÓÊÎÏã ÍÌãÇ ßÈíÑÇ ãä ÇáãæÇÑÏ ( ÅÐÇ ßÇä ÚÏÏ ÓÌáÇÊ ÇáÌÏæá ( 1-1 ) 100000 ÓÌá ÝÅä ÇáÌÏÇÁ ÇáÓáãí ÓíäÝÐ ÚãáíÉ ÖÑÈ Úáì 100000 × 100000 ÓÌá Ãí 10.000.000.000 ÓÌá .
Çä ÇáÍá ÇáÇßËÑ ÝÚÇáíÉ åæ ÇÓÊÎÏÇã ÚÈÇÑÉ Select ãÊÔÇÈßÉ ( ÏÇÎáíÉ ) Ãí ÇÓÊÎÏÇã ÚÈÇÑÉ Select Öãä ÚÈÇÑÉ Select ÇÓÇÓíÉ . äÚæÏ áäÐßÑ åäÇ ÈÃä Access SQL íÓãÍ ÈÐáß ÇÐÇ ßÇäÊ ÇáÚÈÇÑÉ ÇáÏÇÎáíÉ ÊÚíÏ Úáì ÇáÛÇáÈ ÓÌáÇ æÇÍÏÇ .
áÇÍÙ ÇÓÊÎÏãäÇ áÃáÞÇÈ ( äÓÎ ) ááÌÏæá Ýí ÚÈÇÑÉ Select ÇáÊÇáíÉ æäÍÊÇÌ Çáì åÐå ÇáÇáÞÇÈ áÇääÇ ÓäÔíÑ Çáì ÇáÌÏæá ( 1-1 ) Ýí ÓíÇÞíä

Select R1.Event 
(Select Sum(R2.Duration) From Running As R2 Where R2.Event 
<R1.Event) As StartTime 
FROM Running As R1

Çä ÚÈÇÑÉ SQL ÇáÏÇÎáíÉ :

Select Sum(R2.Duration) From Running As R2 Where R2.Event <R1.Event

ÊÚíÏ ãÌãæÚ ÇáãÏÉ áÌãíÚ ÇáÇÍÏÇË ÇáÊí ÊÓÈÞ ÇáÍÏË ÇáÍÇáí ¡ æÇáÐí äÔíÑ Åáíå ÈÇáÚÈÇÑÉ R1.Event
íÚÑÖ ÇáãËÇá ( 1-1 ) ÇÌÑÇÆíÉ VBA áÊäÝíÐ ÚÈÇÑÉ SQL ÇáÓÇÈÞÉ . æíÓÊÎÏã ÇáßÇÆä DoCmd Ýí VBA áÊÔÛíá ÇÌÑÇÁ Access æÈÇáÊÇáí ÝÃä ÇáÓØÑ ÇáÈÑãÌí :

DoCmd.OpenQuery

íÞæã ÈÝÊÍ ÇÓÊÚáÇã Ýí Access
ÇáãËÇá ( 1-1 ) : ÍÓÇÈ ÇáãÌãæÚ ÇáÌÇÑí ÈÅÓÊÎÏÇã ÚÈÇÑÉ SQL

Private Sub RunningSumSQL () 
Dim db As ADO.Database 
Set db = CurrentDb 
Dim qry As ADO.QueryDef 
Dim sSQL As String 
On Error Resume Next 
db.QueryDefs.Delete "temp" 
On Error GoTo 0 
sSQL = "SELECT R1.Event," & _ 
" (SELECT SUM(R2.Duration)" & _ 
"FROM Running As R2" & _ 
"WHERE R2.Event < R1.Event)" & _ 
"AS StartTime" & _ 
" FROM Running As R1" 
Set qry = db.CreateQueryDef("temp", sSQL) 
DoCmd.OpenQuery qry.Nqme 
End Sub

åäÇß ØÑíÞÉ ÃÎÑì ÊÞæã Úáì ÇÓÊÎÏÇã ADO ÇáÐí íÄãä ØÑíÞ Ãæ Íá ÈÓíØ Ýí åÐå ÇáÍÇáÉ Ýåæ íäÔÆ ÌÏæá äÊÇÆÌ ÏÇÆã Ýí Ííä Çä ÇáÍá ÇáÓÇÈÞ íÞæã ÈÅäÔÇÁ ÇÓÊÚáÇã ÊÍÏíÏ æíÚÑÖ ÇáãËÇá ( 1-2 ) ÔÝÑÉ ADO ÇáÊí ÊäÝÐ äÝÓ ÇáÚãá

Private Sub RunningSumDAO( ) 
Dim db As Database 
Dim rs As Recordset 
Dim lRunningSum As Long 
Set db = CurrentDb
lRunningSum = 0 
Set rs = db.OpenRecordset("SELECT * FROM Running ORDER BY Event") 
Do While Not rs.EOF 
rs.Edit 
rs!RunningSum = lRunningSum 
rs.Update 
lRunningSum = lRunningSum + rs!Duration 
rs.MoveNext 
Loop 
rs.Close 
End Sub

ÇáãÌÇáÇÊ ÇáãÊÞÇØÚÉ : Overlapping Interavl
ÊÔíÑ ÇáÕÝæÝ Ýí ÇáÌÏæá ( 1-2 ) Çáì ãÌÇáÇÊ ÒãäíÉ ¡ ÇáãÔßáÉ åí ÊÚííä ãä ÃÌá ßá ÓÇÚÉ ãä Çáíæã ¡ ÚÏÏ ÇáãÌÇáÇÊ ÇáÊí ÊÊÖãä åÐå ÇáÓÇÚÉ .

æáÍá åÐå ÇáãÔßáÉ äÓÊÎÏã ÌÏæá HOURS ÇáãÈíä Ýí ÇáÌÏæá ( 1-3 )

ÇáÍá : Solution
íãßä Íá åÐå ÇáãÔßáÉ ÈÅÓÊÎÏÇã ÚÈÇÑÉ Select ãÊÔÇÈßÉ ( Ãí ÈÅÓÊÎÏÇã ÇÓÊÚáÇã ÝÑÚí ) æÇáßæÏ ÇáÊÇáí íÞæã ÈÇáÚãá ÇáãØáæÈ :

Private Sub OverlappingIntervals( ) 
Dim db As Database 
Set db = CurrentDb 
Dim qry As QueryDef 
Dim sSQL As String 
On Error Resume Next 
db.QueryDefs.Delete "temp" 
On Error GoTo 0 
sSQL = "SELECT Hours.Hour, " & _ 
" (SELECT Count(Interval) AS CountOfIntervals" & _ 
" FROM Overlap" & _ 
" WHERE (StartTime <= Hours.Hour) And" & _ 
" (Hours.Hour < EndTime))" & _ 
" FROM Hours" 
Set qry = db.CreateQueryDef("temp", sSQL) 
DoCmd.OpenQuery qry.Name 
End Sub

ÇáãÌÇáÇÊ ÇáãÊÞÇØÚå Overlapping Intervals II : II
ÊÓÊÎÏã ÔÑßå ÚãÇáÇð æãÔÑÝíä ¡ æÎáÇá íæã Úãá ÚÇÏí ¡ íÚãá ßá ÚÇãá æßá ãÔÑÝ æÑÏíå æÇÍÏå Ãæ æÑÏíÊíä ¡ ÊÊÃáÝ ãä ÓÇÚÇÊ Úãá ãÊÚÇÞÈå ¡ íÚÑÖ ÇáÌÏæá ( 4-1 ) íæãÇð ÊÞáíÏíÇð ãä ÇáÓÇÚå 12 ÙåÑÇð æÍÊì ÇáÓÇÚå 12 Ýí ãäÊÕÝ Çááíá .

äÑíÏ åäÇ ÍÓÇÈ ÇáÚÏÏ ÇáÂÚÙãí ááÚãÇá ÇáÐíä íÔÑÝ Úáíåã ãÔÑÝ æÇÍÏ Ýí æÞÊ ãÚíä .
ÇáÍá : Solution
íãßä ãÚÇáÌÉ åÐå ÇáãÔßáå ÈÅÌÑÇÁ ãÄáÝ ãä ãÑÍáÊíä ¡ äÞæã ÇæáÇð ÈÍÓÇÈ ÚÏÏ ÇáÚãÇá Ýí ßá ÓÇÚå æäÃÎÐ ÚÈÇÑÉ SQL ÇáÊÇáíå :

SELECT Hours.Hour, 
(SELECT Count(EmpType) FROM SuperLoad WHERE (Starthour <= Hours.Hour) And (Hours.Hour < EndHour) And (EmpType='Worker')) AS CountOfWorkers 
FROM Hours

áÇÍÙ åäÇ ÇääÇ äÓÊÎÏã ÇÓÊÚáÇãÇð ÝÑÚíÇð íÚíÏ ÓÌáÇð æÍíÏÇð íÚØí ÚÏÏ ÇáÚãÇá ÇáÐíä íÚãáæä Ýí ÓÇÚå ãÚíäå .
ÈÅÓÊÎÏÇã ÚÈÇÑÉ SQL åÐå äÞæã ÈÅäÔÇÁ ÇÓÊÚáÇã ÇÓãå qry1 ÈÍíË íãßä ÇÓÊÎÏÇãå Ýí ÇáãÑÍáå ÇáËÇäíå . ÇäÙÑ ÇáÌÏæá ( 1-5) ãä ÇÌá äÊÇÆÌ åÐÇ ÇáÅÓÊÚáÇã .

ÇáÎØæå ÇáÊÇáíå åí ÍÓÇÈ ÊÍãíá ÇáãÔÑÝ Úáì Çäå ÇáÚÏÏ ÇáÂÚÙãí ááÚãÇá Ýí ßá æÑÏíå ááãÔÑÝ ¡ äÞæã ÈÐáß ÈÇÓÊÎÏÇã ÇÓã ÇáÂÓÊÚáÇã ãä ÇáãÑÍáå ÇáÓÇÈÞå Ýí ÚÈÇÑÉ SQL ÇáÊÇáíå :

sSQL = "SELECT SuperLoad.EmpID, SuperLoad.EmpType," & _ 
" (SELECT Max(CountOfWorkers) AS WorkerLoad" & _ 
" FROM [" & qry1.Name & "]" & _ 
" WHERE((Hours.Hour >= StartHour) And(Hours.Hour< Endhour)))" & _ 
" FROM SuperLoad " & _ 
" WHERE SuperLoad.EmpType = 'Super' "

æÇáäÊÇÆÌ ãÈíäå Ýí ÇáÌÏæá ( 1-6)

æÇáßæÏ ÇáßÇãá áåÐÇ ÇáÍá ãÈíäå Ýí ÇáãËÇá ( 1-3)
ÇáãËÇá ( 1-3) : ÍÓÇÈ ÇáÚÏÏ ÇáÂÚÙãí ááÚãÇá ãä ÇÌá ßá ãÔÑÝ .

Private Sub SupervisorLoad( ) 
Dim db As Database 
Set db = CurrentDb
Dim qry1 As QueryDef 
Dim qry2 As QueryDef 
Dim sSQL1 As String 
Dim sSQL2 As String
On Error Resume Next 
db.QueryDefs.Delete "temp1" 
db.QueryDefs.Delete "temp2" 
On Error GoTo 0
sSQL = "SELECT Hours.Hour, " & _ 
" (SELECT Count(Emptype) FROM SuperLoad" & _ 
" WHERE(StartHour <= Hours.Hour) And (Hours.Hour < EndHour)" & _ 
" And (Emptype='Worker'))" & _ 
" AS CountOfWorkers" & _ 
" FROM Hours"
Set qry1 = db.CreateQueryDef("temp1", sSQL1) 
' Uncomment to see how this step looks 
' DoCmd.OpenQuery qry1.Name
sSQL = "SELECT SuperLoad.EmpID, SuperLoad.EmpType," & _ 
" (SELECT Max(CountOfWorkers) AS WorkerLoad" & _ 
" FROM [" & qry1.Name & "]" & _ 
" WHERE((Hours.Hour >= StartHour) And (Hours.Hour< Endhour)))" & _ 
" FROM SuperLoad " & _ 
" WHERE SuperLoad.EmpType = 'Super' "
Set qry2 = db.CreateQueryDef("temp2", sSQL2)
DoCmd.OpenQuery qry2.Name 
End Sub

ÅÌÑÇÁÇÊ ÚãáíÇÊ ÇáÅÓäÇÏ ÈÇÓÊÎÏÇã ÇáÞíãå ÇáÇÝÊÑÇÖíÉ : Assignment With Default Making
ÊÎíá æÌæÏ ãÄÊãÑ¡ æãåãÊß Ýíå ÊæÒíÚ ÇáÍÖæÑ Úáì ÞÇÚÇÊ ÇáãÍÇÖÑÇÊ ¡ íÚÑÖ ÇáÌÏæá ( 1-7) ÇáÍÖæÑ ÇáãÓÌá ãÚ ÇÑÞÇã ÇáÛÑÝ ÇáãæÇÝÞå .

áÇÍÙ Çä ÇáÌÏæá íÊÖãä ÚÏÉ ÎíÇÑÇÊ ÇÝÊÑÇÖíå ( Default ) æÅÐÇ áã íßä ÔÎÕ ãæÌæÏÇð Ýí ÇáÌÏæá ¡ ÝÅäß ÊÑíÏ ÊÎÕíÕå ÈÛÑÝå ãä ÇáÛÑÝ ÇáÂÝÊÑÇÖíå ¡ ÈÇáÅÖÇÝå Çáì Ðáß ¡ æáÊÌäÈ ÇáÇÒÏÍÇã Ýí ÇáÛÑÝ ¡ ÊÑíÏ ÊÎÕíÕ ÇÑÞÇã ÇáÛÑÝ ÇáÇÝÊÑÇÖíå ÈÔßá ÚÔæÇÆí ¡ ÝßíÝ äÞæã ÈÐáß .
ÇáÍá : Solution
íãßä Íá åÐå ÇáãÔßáå ÈÚÏÉ ØÑÞ ¡ ÊÞæã ÅÍÏÇåÇ Úáì ÇÓÊÎÏÇã ÇáÇÓÊÚáÇãÇÊ ÇáÝÑÚíå ãÚ ÚÈÇÑÉUNION
áäÃÎÐ ÇæáÇ ÚÈÇÑÉ SQL ÇáÊÇáíå :

sSQL1="SELECT ROOM From Assignment WHERE (Name=[Enter Name])"

äÐßÑ ÈÃä ÇÓÊÎÏÇã ÚÈÇÑÉ (Enter Narme ) åæ ÚÈÇÑÉ Úä æÓíØ . æÚäÏ ÊÔÛíá ÇáÇÓÊÚáÇã ¡ íÊã ÇáØáÈ ãä ÇáãÓÊÎÏã ÅÏÎÇá ÇÓã íÊã ÇÓÊÈÏÇáå ÈÇáæÓíØ ÇáãÐßæÑ ¡ ÓÊÚíÏ åÐå ÇáÚÈÇÑå ÇáÓÌá ÇáãæÇÝÞ áÇÓã ãÚíä ÅÐÇ ßÇä ãæÌæÏÇð ¡ æÅáÇ ÝÅäåÇ ÓÊÚíÏ ãÌãæÚÉ ÓÌáÇÊ ÝÇÑÛå .
áÊÃÎÐ ÇáÂä ÚÈÇÑÉ SQL ÇáÊÇáíå :
íÊÙãä ÇáãËÇá ( 1-4 ) ÇáßæÏ ÇáßÇãá áÊäÝíÐ ÇáÍá ÇáãØáæÈ .
ãÚÇáÌÉ ÊæÒíÚ ÇáÛÑÝ ÇáÇÝÊÑÇÖíÉ æÇáãÍÌæÒÉ ãÓÈÞÇ .

sSQL2 = "SELECT Room FROM Assignment" & _ 
"WHERE (Name = '_default') AND ([Enter Name] NOT IN (SELECT Name FROM Assignment))"

Åä ÚÈÇÑÉ

[Enter Name] NOT IN (SELECT Name FROM Assignment)

ÓÊÚíÏ ÇáÞíãå True ÅÐÇ æÝÞØ ÅÐÇ ßÇä ÇáÇÓã ÇáÐí ÇÏÎáå ÇáãÓÊÎÏã ÛíÑ ãæÌæÏ Ýí ÇáÌÏæá æÈÇáÊÇáí ÝÅä ÚÈÇÑÉ sSQL2 íãßä Çä ÊßÊÈ ãä ÌÏíÏ ÇÓÊäÇÏÇð Çáì ÍÇáÊíä :
1.ÇáÇÓã Ýí ÇáÌÏæá

sSQL2 = "SELECT Room FROM Assignment WHERE (Name = '_default') AND False"

2. ÇáÇÓã áíÓ Ýí ÇáÌÏæá

sSQL1 = "SELECT Room FROM Assignment WHERE (Name = '_default') AND True"

æíãßä ÇÎÊÕÇÑ ÇáÚÈÇÑå ãä ÇÌá ÍÇáÉ ÇáÇÓã Ýí ÇáÌÏæá áÊÕÈÍ :

sSQL2 = "SELECT Room FROM Assignment WHERE FALSE

æÇáÚÈÇÑÉ ãä ÇÌá ÍÇáÉ ÇáÇÓã áíÓ Ýí ÇáÌÏæá :

sSQL1 = "SELECT Room FROM Assignment WHERE (Name = '_default')"

æåßÐÇ ÝÅä ÇáÚÈÇÑå ÓÊÚíÏ ãÌãæÚÉ ÓÌáÇÊ ÝÇÑÛå ÇÐÇ ßÇä ÇáÇÓã Ýí ÇáÌÏæá æÇáÓÌáÇÊ ÇáÇÝÊÑÇÖíå ÇÐÇ áã íßä ÇáÇÓã Ýí ÇáÌÏæá .
ÓäÃÎÐ ÇáÂä ÏãÌ Ãæ ÊæÍíÏ ÇáÚÈÇÑÊíä :

sSQL3=sSQL1 & "UNION" & sSQL2

ÓÊÚíÏ åÐå ÇáÚÈÇÑå ÑÞã ÇáÛÑÝå ãä ÇÌá ÇÓã ÇÐÇ ßÇä ÇáÇÓã Ýí ÇáÌÏæá æÅáÇ ÝÅäåÇ ÓÊÚíÏ ÇáÓÌáÇÊ ÇáÇÝÊÑÇÖíå
Çä ßá ãÇÊÍÊÇÌå ÇáÇä åæ ÇÓÊÑÌÇÚ ÓÌá ÚÔæÇÆí . áÇÍÙ Çä Ðáß íãßä Çä íÚãá Ýí ßáÇ ÇáÍÇáÊíä ¡ áÇäå ÇÐÇ ßÇä ÇáÇÓã Ýí ÇáÌÏæá ¡ ÝåäÇß ÓÌá æÇÍÏ æÈÇáÊÇáí ÝÅä ÇáÎíÇÑ ÇáÚÔæÇÆí åæ Ðáß ÇáÓÌá .
íÊÖãä ÇáßæÏ ÇáÊÇáí ÇáÔíÝÑÉ ÇáßÇãáÉ ááÍá ÇáãØáæÈ
ãÚÇáÌÉ ÊæÒíÚ ÇáÛÑÝ ÇáÇÝÊÑÇÖíÉ æÇáãÍÌæÒÉ ãÓÈÞÇ .

Private Sub AssignmentWithDefault( ) 
Dim db As Database 
Set db = CurrentDb 
Dim sName As String 
Dim qry1 As QueryDef 
Dim rs As Recordset 
Dim sSQL1 As String 
Dim sSQL2 As String 
Dim sSQL3 As String 
Dim lRandom As Long 
Dim lcRecords As Long 
On Error Resume Next 
db.QueryDefs.Delete "temp1" 
On Error GoTo 0 
sSQL1 = "SELECT Room FROM Assignment" & _ 
"WHERE (Name = [Enter Name])" 
sSQL2 = "SELECT Room FROM Assignment" & _ 
"WHERE (Name = '_default') AND ([Enter Name] NOT IN (SELECT Name 
FROM Assignment))" 
sSQL3 = sSQL1 & " UNION " & sSQL2 
Set qry1 = db.CreateQueryDef("temp", sSQL3) 
sName = InputBox("Enter name") 
qry1.Parameters(0) = sName 

' áãÔÇåÏÉ ÇáäÊÇÆÌ 
' 'DoCmd.OpenQuery qry1.Name
Set rs = qry1.OpenRecordset 
' Populate and get recordCount 
rs.MoveLast 
lcRecords = rs.RecordCount 

' Random record 
Randomize Timer 
' lRandom is between 0 and lcRecords-1 
lRandom = Int(lcRecords * Rnd) 
rs.MoveFirst 
rs.Move lRandom 
MsgBox "Room for " & sName & " is " & rs!Room 
End Sub

Òãä ÇáÅäÌÇÒ : Time to Completion
ÓäÚÑÖ åäÇ ãÔßáå ÈÓíØå Íæá ÊÍÏíÏ ÇáÒãä Çáßáí áÅäÌÇÒ ãåãå ãÇ . íÚÑÖ ÇáÌÏæá ( 1- 8) ÍÇáÉ ÅäÊÇÌ ÓáÚå Ýí ÇáÔÑßå ÇáÊí ÊÚãá ÈåÇ . íÞæã ÇáÚãÇá Ýí ÇáãÑÇÍá ÇáãÎÊáÝå áÚãáíÉ ÇáÅäÊÇÌ ÈÅÏÎÇá ÓÌá Ýí ÇáÌÏæá ááÇÔÇÑå Çáì ÇáÒãä ÇáãÊÈÞí áÅäåÇ ÇáÓáÚå .
äÑíÏ ÊÍÏíÏ ÇáÓáÚ ÇáÊí áã ÊäÊå ÈÚÏ .
ÇáÍá : Solution
Çä ÚÈÇÑÉ SQL ÇáÊÇáíå ÓÊÞæã ÈÇáÚãá ÇáãØáæÈ . áÇÍÙ ÇÓÊÎÏÇã äãæÐÌ Not In Ýí ÇáÇÓÊÚáÇã ÇáÝÑÚí .

SELECT DISTINCT WidgeID FROM Widgets As W1 
WHERE 0 NOT IN (SELECT TimeToCompletion FROM Widgets As W2 
WHERE W2.WidgetID = W1.WidgetID)

Òãä ÇáÇäÌÇÒ2 : Time To Completion2
ÏÚäÇ äÞæã ÈÊÚÞíÏ ãÔßáÉ ÇáÇäÌÇÒ ÞáíáÇ . ÊÎíá ËÇäíÉ Ãäß ÊÊÇÈÚ ÍÇáÉ ÅäÊÇÌ ÇáÓáÚ Ýí ÇáÔÑßÉ . ÊÊÃáÝ ßá ÓáÚÉ ãä ÃÑÈÚÉ ÃÌÒÇÁ , íÊã ÊÌãíÚ ßá ãäåÇ Úáì ÍÏÉ . íÊÖãä ÇáÌÏæá (9-1) ÚíäÉ ãä ÇáÈíÇäÇÊ .

ÓäÍÇæá åäÇ ÊÚííä Ãæ ÊÍÏíÏ ÇáÓáÚ ÇáÊí íßæä ÝíåÇ ÇáÌÒÁ ÇáÃæá (Module 1 ) åæ ÇáÌÒÁ ÇáæÍíÏ ÇáßÇãá – Ãí Ãä ÞíãÉ
Time To Completion ÊÓÇæí ÇáÕÝÑ ãä ÃÌá ÇáÌÒÁ ÇáÃæá , æáßä ãÎÊáÝÉ Úä ÇáÕÝÑ ãä ÃÌá ÇáÃÌÒÇÁ ÇáÃÎÑì Ýí ÇáÓáÚÉ .
ÇáÍá : Solution
áäÃÎÐ ÚÈÇÑÉ SQL ÇáÊÇáíÉ :

SELECT WidgetID 
FROM Widgets AS W1 
WHERE (TimeToCompletion = 0) AND 0 NOT IN (SELECT TimeToCompletion FROM Widgets AS W2 
WHERE (W2.WidgetID=W1.WidgetID) AND (W2.ModuleID < > 1))

íÍÏÏ ÇáÇÓÊÚáÇã ÇáÝÑÚí ãä ÃÌá ÓáÚÉ ãÚíäÉ , ÌãíÚ Þíã Time To Completion áÌãíÚ ÇáÃÌÒÇÁ ÈÇÓÊËäÇÁ ÇáÌÒÁ ÇáÃæá . ÊÓÊØíÚ ÈÚÏ Ðáß ÅÌÑÇÁ ÇÎÊÈÇÑ áÑÄíÉ ÝíãÇ ÅÐÇ ßÇäÊ Êáß ÇáãÌãæÚÉ ãä ÃÒãäÉ ÇáÇäÌÇÒ Time To Completion ÊÊÖãä ÇáÞíãÉ ÕÝÑ .
æíÊÖãä ÇáãËÇá (1-5) ÇáßæÏ ÇáßÇãá .
ÇáãËÇá (1-5) : ãËÇá Time To Completion

Private Sub TimeToCompletion( ) 
Dim db As Database 
Set db = CurrentDb 
Dim qry1 As QueryDef 
Dim sSQL1 As String 
On Error Resume Next 
db.QueryDefs.Delete "temp1" 
On Error GoTo 0 
sSQL1 = "SELECT widgetID FROM widgets As W1" &_ 
" WHERE (TimeToCompletion = 0) AND" & _ 
" 0 NOT IN" & _ 
" (SELECT TimeToCompletion FROM Widgets AS W2" & _ 
"WHERE (W2.WidgetID=W1.WidgetID) AND (W2.ModuleID <> 1) )" 
Set qry1 = db.CreateQueryDef("temp1", Ssql1) 
DoCmd.OpenQuery qry1.Name 
End Sub

Òãä ÇáÇäÌÇÒ III ãÓÃáÉ ÇáÍÏ ÇáÃÚÙã æÇáÍÏ ÇáÃÏäì : Time To Completion III a MaxMin Problem
ÏÚäÇ äÖíÝ ãÒíÏÇð ãä ÇáÊÚÞíÏ Úáì ãÓÃáÉ Òãä ÇáÇäÌÇÒ . áäÝÊÑÖ Ãä ßá ÌÒÁ ãÄáÝ ãä ÚÏÉ ÃÞÓÇã . æÓíÞæã ÇáÚãÇá ÇáãßáÝíä ÈÞÓã ãÚíä ÈÅÏÎÇá ÞíÏ Ýí ÌÏæá ÞÇÚÏÉ ÇáÈíÇäÇÊ æÈÔßá ÏæÑí , æÐáß ßãÇ åæ ãÈíä Ýí ÇáÌÏæá (1-10) .
æäÑÛÈ åäÇ ÈÍÓÇÈ Òãä ÇäÌÇÒ ßá ÞÓã , æÌÒÁ æÓáÚÉ . áÇÍÙ Ãäå íãßä Ãä íßæä åäÇß ÚÏÉ ÞíæÏ ãä ÃÌá ÞÓã ãÚíä . æÒãä ÇáÇäÌÇÒ ÇááÇÒã áÞÓã ãÚíä åæ ÇáÍÏ ÇáÃÏäì ááÃÒãäÉ Ýí åÐå ÇáÕÝæÝ .

ÇáÍá ÇáÇæá : Solution1
ÏÚäÇ äÚÊãÏ ØÑíÞÉ ÊÞæã Úáì ãÈÏà ÇáÎØæÉ ÎØæÉ . æÊÓÊØíÚ áÇÍÞÇð ÚÑÖ Íá ÃßËÑ ÃäÇÞÉ æáßäå ÃßËÑ ÕÚæÈÉ ááÝåã æÇáÞÑÇÁÉ .
äÞæã ÃæáÇ ÈÅäÔÇÁ ÚÈÇÑÉ SQL ÊÚíÏ ÝÞØ ÕÝæÝ ÇáÌÏæá áßá ÓáÚÉ / ÌÒÁ ÇáÊí áåÇ ÃÕÛÑ Òãä ÇäÌÇÒ ááÞÓã
Time To Completion . æäÓÊØíÚ ÇáÞíÇã ÈÐáß Úáì ãÑÍáÊíä . ÊÞæã Ãæá ÚÈÇÑÉ SQL
ÈÊÍÏíÏ ÍÞá Time To Completion áÌãíÚ ÇáÓÌáÇÊ Ýí ÇáãÌãæÚÉ Widgets æÇáÊí áåÇ Þíã ãÚíäÉ
áßá ãä Widget ID , Module ID , Part ID .

' Time to completion for given WidgetID/Modul*ID/PartID
sSQL1 = "SELECT TimeToCompletion FROM Widgets2 As W2" & _ 
" WHERE (W2.WidgetID = W1. WidgetID)" & _
" And (W2.ModuleID = W1.ModuleID)" & _
" And (W2.PartID = W1.PartID)"

ÃãÇ ÚÈÇÑÉ SQL ÇáËÇäíÉ ÝÊÚíÏ ÌãíÚ ÇáÓÌáÇÊ ÇáÊí íßæä ÝíåÇ Time To Completion ÃÕÛÑ Ãæ ãÓÇæò áÌãíÚ ÇáÓÌáÇÊ ÇáÊí ÊãÊ ÅÚÇÏÊåÇ Ýí ÚÈÇÑÉ SQL ÇáÃæáì , Ãí ÌãíÚ ÇáÓÌáÇÊ ÇáãÊÚáÞÉ ÈÞíã
ãÚíäÉ Widget ID , Module ID , Part ID .

' Those records that have minimum time to completion for each part 
sSQL2 = "SELECT WidgetID, ModuleID, PartID," & _ 
" TimeToCompletion AS TimeToFinishPart FROM Widget2 AS W1" & _ 
" WHERE TimeToCompletion <= ALL (" &sSQL1&")"

åäÇß ØÑíÞÉ ÃÎÑì ÊÞæã Úáì ÇÓÊÎÏÇã ÚÈÇÑÉ Select ãÊÔÇÈßÉ æÍíÏÉ :

sSQL2 = "SELECT WidgetID, ModuleID, PartID," & _ 
" (SELECT MIN(TimeToCompletion)" & _ 
" FROM Widgets2 as W2 WHERE" & _ 
" (W2.WidgetID = W1.WidgetID) And" & _ 
" (W2.ModuleID = W1.ModuleID) And" & _
" (W2.PartID = W1.PartID)" & _ 
" AS TimeToFinishPart" & _ 
" FROM Widgets2 AS W1

ÈÊäÝíÐ åÐÇ ÇáÇÓÊÚáÇã :

Set qry1=db.CreateQueryDef("temp1", sSQL2) 
DoCmd.OpenQuery qry1.Name

ÓäÍÕá Úáì ÇáÌÏæá (1-11) .

æÈÇÓÊÎÏÇã ÇáÇÓÊÚáÇã ÇáÊÇáí íÕÈÍ ãä ÇáÓåá ÇáÍÕæá Úáì Òãä ÇáÇäÌÇÒ áßá ÌÒÁ .

' Time to finish each module 
sSQL3 = "SELECT WidgetID, ModuleID," & _ 
" Max(TimeToFinishPart) AS TimeToFinishModule FROM " & qry1.Name & _ 
" GROUP BY WidgetID, ModuleID" 
Set qry2 = db.CreateQueryDef("temp2", sSQL3) 
' Show it 
DoCmd.OpenQuery qry2.Name

íÊÖãä ÇáÌÏæá (1-12) äÊÇÆÌ ÊäÝíÐ åÐÇ ÇáÇÓÊÚáÇã .

ÃÎíÑÇð äÓÊØíÚ ÍÓÇÈ Òãä ÇáÇäÌÇÒ áßá ÓáÚÉ :

' Time to finish each Widget 
sSQL4 = "SELECT WidgetID," & _ 
" Max(TimeToFinishModule) AS TimeToFinishWidget FROM " & _ 
qry2.Name & " GROUP BY WidgetID" 

Set qry3 = db.CreateQueryDef("temp3", sSQL4)

æÇáäÊÇÆÌ ãÈíäÉ Ýí ÇáÌÏæá (1-13) .

ÇáÍá ÇáËÇäí : Solution2
ãä Çáããßä ÇáÍÕæá Úáì Òãä ÇáÇäÌÇÒ ÈÇÓÊÎÏÇã ÚÈÇÑÉ SQL æÍíÏÉ , ÑÛã ÃääÇ áÇ ääÕÍ ÈÐáß .
ÞÏ Êßæä ÇáäÊÇÆÌ ÃßËÑ ÃäÇÞÉ , æáßäåÇ ÕÚÈÉ ÇáÞÑÇÁÉ . Óíßæä áÏíäÇ ãËáÇð ãä ÃÌá ÇáÃÌÒÇÁ (Modules) :

sSQL1 = "SELECT DISTINCT WidgetID, ModuleID, " & _ 
" TimeToCompletion FROM Widgets2 AS W1" & _ 
" WHERE TimeToCompletion = " & _ 
" (SELECT MAX(TimeToCompletion) FROM Widgets2 As W2" & _ 
" WHERE TimeToCompletion = " & _ 
" (SELECT MIN(TimeToCompletion) FROM Widgets2 As W3" & _ 
" WHERE (W3.WidgetID = W2.WidgetID) " & _ 
" And (W3.ModuleID = W2.ModuleID) " & _ 
" And (W3.PartID = W2.PartID)" & _ 
" Group BY W3.WidgeetID, W3.ModuleID, W3.PartID)" & _ 
" AND (W2.WidgetID = W1.WidgetID)And(W2.ModuleID = _ 
" W1.ModuleID)" & GROUP BY W2.WidgetID, W2.ModuleID)"

Åä åÖã ÚÈÇÑÉ SQL åÐå Óíßæä ÕÚÈÇð æíÊØáÈ æÞÊÇð ÃØæá , æäÑÌæ ÃäåÇ ÓÊÌÚáß ÊÚíÏ ÇáäÙÑ ÈÇÓÊÎÏÇã ãËá åÐå ÇáÚÈÇÑÇÊ Ýí ÊØÈíÞÇÊß ÇáãÓÊÞÈáíÉ .

ÚÇãæÏí Çáì ÇÝÞí : Vertical to Horizontal
ÊÎíá Çä áÏíß ÞÇÚÏÉ ÈíÇäÇÊ ÅÍÕÇÆíÇÊ ÔÎÕíÉ ãÚ ÌÏæáíä ( ÇáÌÏæá 1-14 ) æÇáÌÏæá 1-15 )

áÇÍÙ Çä ááÌÏæá (1-15) ÕÝ æÇÍÏ áßá ÇÍÕÇÁ . æåßÐÇ ÝÅä ÇáÈíÇäÇÊ ÇáãÊÚáÞÉ ÈÔÎÕ ãÚíä ãÑÊÈÉ ÚÇãæÏíÇ . æáÇÍÙ ÇíÖÇ Çä ÈÚÖ ÇáÈíÇäÇÊ äÇÞÕÉ Úáì ÓÈíá ÇáãËÇá áÇ íæÌÏ ÈíÇäÇÊ ãä ÇÌá " ÒåÜÜÑå " . äÑíÏ ÇáÇä ÚÑÖ ÇáÈíÇäÇÊ ÇÝÞíÇ ßãÇ Ýí ÇáÌÏæá (1-16)

ÇáÍá : Solution
ÊÞÏã áäÇ ÚÈÇÑÉ SQL ÇáÊÇáíÉ ÍáÇ áåÐå ÇáãÓÃáÉ :

SELECT DISTINCT Employees.EmpID, Name, 
(SELECT Value FROM EmployeesData AS T2 WHERE (T2.StatType 'Age') And (T2.EmpID = Employees.EmpID)) As Age, 
(SELECT Value FROM EmployeesData AS T2 WHERE (T2.StatType Height) And (T2.EmpID = Employees.EmpID)) As Height, 
(SELECT Value FROM EmployeesData AS T2 WHERE (T2.StatType Weight) And (T2.EmpID = Employees.EmpID)) As Weight, 
FROM (Employees INNER JOIN EmployeesData ON Employees.EmpID= EmployeesData.EmpID

áÏíäÇ åäÇ ÚÏÉ ÚÈÇÑÇÊ ÇÓÊÚáÇã ÝÑÚí Select Öãä ÚÈÇÑÉ Select ÇáÑÆíÓíÉ . æÊÞæã ÇáÚÈÇÑÉ ÇáÊÇáíÉ :

(Select Value From EmployeesData As T2 WHERE (T2.StatType='Age') And (T2.EmpID= Employees.EmpID)) As Age

ÈÊÍÏíÏ ÇáÚãÑ ááÔÎÕ ÇáãÍÏÏ Ýí ÙÑÝ Ãæ ÚÈÇÑÉ Select ÇáÑÆíÓíÉ . æßáãÇ ÇÒÏÇÏ ÚÏÏ ÇáÇÍÕÇÆíÇÊ ÊÕÈÍ ÚÈÇÑÉ SQL ÇßËÑ ÊÚÞíÏÇ . íÚÑÖ ÇáãËÇá ( 1-16 ) ÍáÇ ÈÏíáÇ ÈÅÓÊÎÏÇãADO æáÇ íÊØáÈ ÊÚÏíá áÏì ÇÒÏíÇÏ ÇáÅÍÕÇÆíÇÊ .
ÇáãËÇá ( 1-16 ) ãËÇá : Vertical to Horizontal

Private Sub VerticalToHorizontal2( ) 
Dim db As Database 
Set db = CurrentDb 
Dim rsEmp As Recordset 
Dim rsData As Recordset 
Dim rsHor As Recordset
Set rsEmp = db.OpenRecordset("Employees") 
Set rsHor = db. OpenRecordset("EmployeesOutput") 
Do While Not rsEmp.EOF 
Set rsData = db.OpenRecordset( _ 
"SELECT * FROM EmployeesData WHERE EmpID = " & rsEmp!EmpID) 
rsHor.AddNew 
rsHor!EmpID = rsEmp!EmpID 
rsHor!Name = rsEmp!Name 
Do While Not rsData.EOF 
rsHor.Fields(rsData!StatType).Value = rsData!Value 
rsData.MoveNext 
Loop 
rsHor.Update 
rsEmp.MoveNext 
Loop 
rsEmp.Close 
rsData.Close 
rsHor.Close 
End Sub

ãÓÃáÉ ÊØÇÈÞ : A Matching Problem
íÊÖãä ÇáÌÏæá ( 1-17 ) ãÈÑãÌíä æáÛÇÊ ÇáÈÑãÌãÉ ÇáÊí íÓÊÎÏãæäåÇ . ÃãÇ ÇáÌÏæá ( 1-18 ) ÝíÍÏÏ ãÊØáÈÇÊ ÇááÛÉ ãä ÇÌá ÇÚãÇá ãÎÊáÝÉ . äÑíÏ åäÇ ÚÑÖ ÞÇÆãÉ ÈÇáÇÚãÇá æÇáãÈÑãÌíä ÇáãÄåáíä ááÞíÇã ÈåÇ .



ÇáÍá : Solution
ÊÞÏã áäÇ ÚÈÇÑÉ SQL ÇáÊÇáíÉ ÃÍÏ ÇáÍáæá :

SELECT ProgrammingJobs.JobID, Programmers.Name 
FROM Programmers INNER JOIN ProgrammingJobs ON Programmers.Language = ProgrammingJobs.Language 
GROUP BY ProgrammingJobs.JobID, Programmers.Name 
HAVING Count(Programmers.Language)= 
(SELECT Count([Language]) FROM ProgrammingJobs AS pj 
WHERE pj.JobID= ProgrammingJobs.JobID)

äÈÏà ÈÑÈØ ÏÇÎáí Inner Join ááÌÏæáíä ÈÅÓÊÎÏÇã ÍÞá Language æãä ÃÌá ßá ÒæÌ Programmer/Job ÊäÔÆ ÚáÇÞÉ ÇáÑÈØ ÇáÏÇÎáí ãÌãæÚÉ ãä ÇáÓÌáÇÊ ßãÇ Ýí ÇáÔßá

JobID X      Language1-ProgrammerName Y 
JobID X      Language2-ProgrammerName Y 
JobID X      Language3-ProgrammerName Y

ÍíË íÊØáÈ ÇáÚãá ááÛÉ æÇáãÈÑãÌ ãåÇÑÉ ÈÊáß ÇááÛÉ .
äÍÊÇÌ ÇáÇä Çä äÖãä áßá ÒæÌ Úãá / ãÈÑãÌ ÈÃä íßæä ÚÏÏ ãä åÐå ÇáÓÌáÇÊ åæ äÝÓ ÚÏÏ ÇááÛÇÊ ÇáãØáæÈÉ áÐáß ÇáÚãá . æíÊã Ðáß Úä ØÑíÞ ÊÍãíÚ ÇáÓÌáÇÊ æÝÞ ÇáÇÒæÇÌ Úãá / ãÈÑãÌ æãä Ëã ÇÓÊÎÏÇã ÙÑÝ Having ÇáÐí íÞÇÑä ÚÏÏ åÐå ÇáÓÌáÇÊ ãÚ ÚÏÏ ÇááÛÇÊ áÐáß ÇáÚãá .æÇáäÊíÌÉ åí ãÇ íÊÖãäå ÇáÌÏæá ÇáÌÏæá (1-19)


ÊÓÇæí ÇáãÌãæÚÇÊ : Equality of Sets
ãä ÇáãÓÇÆá ÇáÔÇÆÚÉ ÊÚííä ãÊì ÊÊÓÇæì ãÌãæÚÊÇä Ãí ãÊì íßæä áåãÇ äÝÓ ÇáÚäÇÕÑ . áäÃÎÐ ÇáÌÏæá ( 1-20 ) ÇáÐí íÚÑÖ ÎãÓ ãÌãæÚÇÊ æÚäÇÕÑåÇ . áÇÎÊÕÇÑ ÇáãÓÃáÉ æÊÈÓíØåÇ äÞæã ÈÊÑÞíã ÇáãÌãæÚÇÊ æäÝÊÑÖ ÇäåÇ ÊÊÖãä ÇÑÞÇãÇ ÈÍÏ ÐÇÊåÇ . æäÑíÏ ÇáÍÕæá Úáì ÞÇÆãÉ ÈÇáãÌãæÚÇÊ ÇáãÊÓÇæíÉ .

ÇáÍá : Solution
áåÐå ÇáãÓÃáÉ Íá ÃäíÞ ÈÅÓÊÎÏÇã ÚÈÇÑÉ SQL æÍíÏÉ . ÈíäãÇ áÇ íÓãÍ SQL ÈÔßá ÚÇã ÈãÞÇÑäÉ ãÌãæÚÊíä ãÈÇÔÑÉ ßãÇ Ýí ÇáÚÈÇÑÉ :

(Select Members From Equality Where Set=1)= (Select Members From Equality Where Set=2)

áßäå íÞÈá ßËá åÐå ÇáÚÈÇÑÉ ÇÐÇ ßÇäÊ ÚÈÇÑÇÊ Select ÊÚíÏ ÞíãÉ æÍíÏÉ . áäÃÎÐ ÇáÇä ÚÈÇÑÉ SQL ÇáÊÇáíÉ :

SELECt Equality.Set, E2.Set 
FROM Equality INNER JOIN Equality AS E2 ON (Equality.Member = E2.Member) And (Equality.Set < E2.Set) 
GROUP BY Equality.Set, E2.Set 
HAVING 
((SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=Equality.Set) = 
(SELECT Count(Member)FROM Equality As E3 WHERE E3.Set=E2.Set)) AND (Count(Equality.Set) = 
(SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set))

Çä ÚáÇÞÉ ÇáÑÈØ ÇáÏÇÎáí Inner Join åí :

(Equality.Member = E2.Member) And (Equality.Set < E2.Set)

Çä ÇáÌÒÁ ÇáåÇã Ýí åÐå ÇáÚÈÇÑÉ åæ ÇáÌÒÁ ÇáÇæá . Çäå íÝÊÑÖ ÈÃääÇ äÑíÏ ÌãíÚ ÇÒæÇÌ ÇáãÌãæÚÇÊ ÇáÊí áåÇ ÚÖæ ãÔÊÑß . æíãäÚ ÇáÌÒÁ ÇáËÇäí ãä ÇáÚÈÇÑÉ ÇÚÇÏÉ ÇÒæÇÌ ãÊßÑÑÉ . Úáì ÓÈíá ÇáãËÇá ÇÐÇ ÊÖãäÊ ÇáãÌãæÚÊÇä 1 æ 2 ÇáÚÖæ 3 ÝÅääÇ áÇ äÑíÏ ÇÓÊÑÌÇÚ ÇáÒæÌ ( 1-2 ) æÇáÒæÌ ( 2-1 ) . æáÈíÇä Ðáß ÈÔßá ÇæÖÍ ÈãÇ Çä ÇáÚÖæ 3 ãÌæÌæÏ Ýí ÇáãÌãæÚÇÊ 1 ¡ 2 ¡ 3 ¡ 4 ÝÅä ÇáÓÌáÇÊ ÇáãÓÊÚÇÏÉ ááÚÖæ 3 åí Úáì ÇáäÍæ ÇáÊÇáí :
( 2-1 ) ( ãä ÇáÚÖæ 3 )
( 3-1 ) ( ãä ÇáÚÖæ 3 )
( 4-1 ) ( ãä ÇáÚÖæ 3 )
( 3-2 ) ( ãä ÇáÚÖæ 3 )
( 4-2 ) ( ãä ÇáÚÖæ 3 )
( 4-3 ) ( ãä ÇáÚÖæ 3 )
æÇÐÇ áã íßä ÇáÌÒÁ Equality.Set < E2.Set ãæÌæÏÇ ÝÅääÇ ßäÇ ÓäÍÕá Úáì ( 1-1 ) æ ( 2-2 ) ..... ( 4-4 ) ÈÇáÇÖÇÝÉ Çáì ( 1-2 ) æ ( 1-3 ) æåßÐÇ .
íãßääÇ ÇáÇä Çä äØÑÍ ÇáÓÄÇá ÇáÊÇáí : ßã ãÑå ÓíÙåÑ ÒæÌ ãÌãæÚÉ ãÇ ¿ Çä ÒæÌ ÇáãÌãæÚÉ ( 2-1 ) ãËáÇ ÓíÙåÑ ÈÚÏÏ ÇáãÑÇÊ ÇáÊí íæÌÏ ÝíåÇ ÚäÇÕÑ ãÔÊÑßÉ Èíä ÇáãÌãæÚÊíä . Ãí Çäå ÓíÙåÑ ÈÚÏÏ ãÑÇÊ íßÇÝÆ ÍÌã ÇáÊÞÇØÚ Èíä ÇáãÌãæÚÊíä .
áÐáß ÇÐÇ ÞãäÇ ÈÇáÊÌãíÚ ÍÓÈ ÇÒæÇÌ ÇáãÌãæÚÇÊ ( Group By ) äÓÊØíÚ ÝÍÕ ÇáÊÞÇØÚÇÊ æÊÍÏíÏ ÍÌã ÇáäÊÇÆÌ ÇáãÓÊÑÌÚÉ ÈÅÓÊÎÏÇã ÙÑÝ Having . Çä åÐÇ ÇáÙÑÝ íÞæá ÈÃä ÇáãÌãæÚÊíä ãÊÓÇæíÊíä . æáßä ÊÊÓÇæì ÇáãÌãæÚÊÇä A æ B ÇÐÇ ßÇä ÍÌãåãÇ æÊÞÇØÚåãÇ åæ äÝÓå Çä ÇáÚÈÇÑÉ :

(Select Count(Member) From Equality As E3 WHERE E3.Set=Equality.Set)= 
Select Count(Member) From Equality As E3 WHERE E3.Set=E2.Set)

ÊÞæá Çäå ãä ÇÌá ÒæÌ ãÌãæÚÇÊ ãÚíä ( Equality.Set , E2.Set ) ãä ÇáÚÈÇÑÉ ÇáÑÆíÓíÉ Select ÝÅä ÍÌã Equality.Set ãÓÇæí áÍÌã E2.Set . ÇãÇ ÇáÚÈÇÑÉ :

Count(Equality.Set)= (Select Count(Member) From Equality As E3 
WHERE E3.Set=E2.Set)

ÝÊÞæá ÈÃä ÍÌã ÊÞÇØÚ Equality.Set æ E2.Set åæ äÝÓå æããÇËá áÍÌã E2.Set æåÐÇ åæ ÇáãØáæÈ .

Ýí ÇáÎÊÇã ÇÚÒÇÆí ÇáßÑÇã ÇÓÃá Çááå ÇáÚáí ÇáÞÏíÑ Çä íæÝÞäÇ æÇíÇßã áãÇ íÍÈå æíÑÖÇå æÇÊãäì ãä ÇáÌãíÚ ÈÚÏ ÞÑÇÁÉ åÐÇ ÇáãæÖæÚ ÇáÚãá Úáì ÊØÈíÞå ÚãáíÇ ÈÍíË ÊÃÎÐ ßá ãËÇá æÊÍÇæá ÊØÈíÞå Úáì ÞÇÚÏÉ ÈíÇäÇÊ áãÚÑÝÉ ßíÝíÉ ÇáÇÓÊÝÇÏÉ ãä ÇáÍáæá ÇáãÞÊÑÍå æÞÏ ÊÎÑÌ ÈÝßÑå ÌÏíÏå áã ÊØÑÍ æÈåÐÇ Êßæä ÞÏ ÇÕØÏÊ ÚÕÝæÑíä ÈÍÌÑ æÇÍÏ ãäåÇ ÇáÇÓÊÝÇÏÉ æãäåÇ ÇáÊØÈíÞ .
ÇÎÊßã
ÒåÜÑå
ÇáãÕÏÑ ãä ßÊÇÈ : Some Common Data Manipulation Problems