در این مقاله با یک دیتابیس کوچک اما با پتانسیل، برای نوشتن Query آشنا میشویم

در ادامه قسمت سوم این مقاله در این جلسه با Query های زیر و نکات آن آشنا شده

Query دهم : 

هر خانومی چند اتفاق در زندگی خود داشته است ؟ (فرزندان - ازدواج - طلاق - فوت همسر و فرزند)

در این مثال که تشکیل شده از چندین Query میتوان به قسمت های مختلفی تجزیه شود 

تعداد فرزندان با یک Query ساده که از Group By و تابع Count استفاده میکند به دست میاد .

برای قسمت های دیگر هم به همین شکل است . پس ما چندین Query با فرمت های مشابه داریم 

که در ادامه آن را مشاهده میکنید . 

SELECT  CurrentWoman.FirstName + ' ' + CurrentWoman.LastName AS Woman ,
        ISNULL(Marriages, 0) AS Marriages ,
        ISNULL(Divorces, 0) AS Divorces ,
        ISNULL(Children, 0) AS Children ,
        ISNULL(HusbandDeaths, 0) AS HusbandDeaths ,
        ISNULL(ChildDeaths, 0) AS ChildDeaths ,
        ISNULL(Marriages, 0) +
		ISNULL(Divorces, 0) +
		ISNULL(Children, 0) +
		ISNULL(HusbandDeaths, 0) + 
		ISNULL(ChildDeaths, 0) AS TotalLifeEvents

FROM    Person CurrentWoman

-----------------------Marriages--------------------------------

        LEFT OUTER JOIN ( SELECT    WifeID ,
                                    COUNT(*) AS Marriages
                          FROM      Marriage
                          GROUP BY  WifeID
                        ) M ON CurrentWoman.PersonID = M.WifeID

-----------------------Children--------------------------------

        LEFT OUTER JOIN ( SELECT    MotherID ,
                                    COUNT(*) AS Children
                          FROM      Person
                          WHERE     MotherID IS NOT NULL
                          GROUP BY  MotherID
                        ) C ON CurrentWoman.PersonID = C.MotherID

-----------------------Divorces--------------------------------

        LEFT OUTER JOIN ( SELECT    WifeID ,
                                    COUNT(*) AS Divorces
                          FROM      Marriage
                          WHERE     DateOfDivorce IS NOT NULL
                          GROUP BY  WifeID
                        ) D ON CurrentWoman.PersonID = D.WifeID

-----------------------ChildDeaths--------------------------------

        LEFT OUTER JOIN ( SELECT    Child.MotherID ,
                                    COUNT(*) AS ChildDeaths
                          FROM      Person Child
                                    INNER JOIN Person M 
									ON Child.MotherID = M.PersonID
                                    AND Child.DateOfDeath < M.DateOfDeath
                          GROUP BY  Child.MotherID
                        ) CD ON CurrentWoman.MotherID = CD.MotherID

-----------------------HusbandDeaths--------------------------------

        LEFT OUTER JOIN ( SELECT    Person.PersonID ,
                                    COUNT(*) AS HusbandDeaths
                          FROM      Person
                                    INNER JOIN Marriage ON Person.PersonID = Marriage.WifeID
                                    INNER JOIN Person H ON Marriage.HusbandID = H.PersonID
                                                     AND H.DateOfDeath < Person.DateOfDeath
                          GROUP BY  Person.PersonID
                        ) HD ON CurrentWoman.PersonID = HD.PersonID

-------------------------------------------------------------------

WHERE   CurrentWoman.Gender = 'F'
ORDER BY TotalLifeEvents DESC;

و اینجا نیز از تابع ISNULL که در جلسات قبل توضیح داده شد استفاده شده است

SELECT  
        Person.FirstName + ' ' + Person.LastName AS Patriarch
FROM    Person
WHERE   Person.Gender = 'M'
        AND Person.MotherID IS NULL
        AND Person.FatherID IS NULL
        AND PersonID NOT IN 
      ( SELECT  Person.PersonID
        FROM    Person
                INNER JOIN Marriage ON Person.PersonID = Marriage.HusbandID
                INNER JOIN Person Wife ON Marriage.WifeID = Wife.PersonID
        WHERE   Wife.MotherID IS NOT NULL
                OR Wife.FatherID IS NOT NULL );