פונקציות קיבוץ (טוריות)

1. פונקציות קיבוץ (טוריות) פונקציות הפועלות על קבוצת נתונים Count – ספירה Sum – סכום מתמטי Avg – ממוצע Max – ערך עליון Min – ערך תחתון Stdev – סטיית תקן

2. פונקציה (שדה או ביטוי סקלרי) • אופן כתיבה: • יש להקפיד על התאמה לסוגי נתונים. • לא ניתן להפעיל פונקציה טורית על פונקציה טורית • count (distinct field ) - ספירת ערכים שונים • count (*) – ספירת רשומות • פונקציות טוריות לא מתחשבות בערכי null.

3. דוגמה: Select max(wo_salary) as max_sal From tbl_workers • פלט: השכר הגבוה ביותר במפעל (לא ידוע של מי) • הפלט יציג ערך אחד בלבד. • ניתן להוסיף תנאי where לדוגמה תוספת תנאי לשאילתה:where dep_id = 10 תתן את השכר הגבוה ביותר במחלקה 10.

4. הוספת קיבוץ: (Group By) חלוקה לקבוצות , פונקציה טורית תחזיר ערך אחד עבור כל קבוצה. לדוגמה: כמה עובדים יש בכל מחלקה Select dep_id ,Count(*) as num From tbl_workers Group by dep_id נרשם אחרי ה – where (אם יש)

5. Group by הקבוצה לא תכיל שדה בדיד כגון: wo_id חוק חשוב ! תוספת group by מאפשרת לרשום ב- select אך ורק את שם הקבוצה (שדה הקיבוץ) ופונקציות טוריות נוספות.

6. האם השאילתה הבאה תרוץ ? Select dep_name From tbl_workers w , tbl_dept d Where w.dep_id = d.dep_id group by d.dep_id לא ! אמנם הקיבוץ הוא לפי מחלקה וגם ב-select מציגים מחלקה, אבל הם שני שדות שונים.

7. קיבוץ לפי מספר שדות Group by fieldA , fieldB אם לכל ערך ב- fieldA ייתכנו מספר ערכים שונים ל fieldB, אזי החלוקה לקבוצות קטנות יותר מקיבוץ לפי fieldA האם הוספת שדה לקיבוץ משנה את הקיבוץ המקורי ? Select dep_name , w.dep_id , avg(wo_salary) From tbl_workers w , tbl_dept d Where w.dep_id = d.dep_id Group by w.dep_id , dep_name לא ! הקבוצות נשמרות

8. דוגמה: מצא בכל מחלקה את מספר העובד שמשכורתו הכי גבוהה במחלקה. ננסה לרשום: Select dep_id , max(wo_salary) From tbl_workers Group by dep_id , wo_id ,wo_id הוספת wo_id ב – select אפשרית רק עם הוא יופיע ב- group by, אבל הוספת wo_id ל – group by משנה את הקבוצה המקורית ! ולכן שאילתה זו לא תבצע את הנדרש !

9. תרגילים: • הצג את שמות כל העובדים ואת סך ימי החופשה שצברו (יש להתייחס רק לחופשות שהסתיימו) • חשב בכל שנה כמה השאלות התבצעו לספרים מתורגמים וכמה ללא מתורגמים. • חשב עבור כל עובד כמה עובדים במחלקה שלו מרוויחים יותר ממנו.

10. Having - תנאי על קבוצה דוגמה: הצג את שמות המחלקות בהן יש לפחות שני עובדים שהחלו לעבוד השנה. קריטריון: תחילת עבודה– מתייחס לכל רשומה (עובד) - where קריטריון: כמות עובדים – מתייחס לכל קבוצה (מחלקה) - Having • Having – נרשם בין ה –group by ל – order by • Having לא יכול להופיע בלי group by (להפך כן)

11. Select dep_id From tbl_workers Where year(wo_start_job) = year(getdate( )) Group by dep_id Having count(*) >=2 where Group by Having 10

12. תנאי ב-having יכול להיות מורכב (and , or) • תנאי ב- where לא יכול לכלול פונקציה טורית, רק תנאי ב- having תרגיל: 4. (Northwind). חשב את סך עלות כל ההזמנה שכללו לפחות מוצר אחד ללא הנחה.

13. תת שאילתה (Sub Query) • קריטריון מורכב מדי / לא ידוע מראש. דוגמה: מספרי העובדים שמרוויחים מעל הממוצע. בעיה : הממוצע לא ידוע ולכן יש לחשבו בנפרד. Select wo_id From tbl_workers Where wo_salary > ( select avg(wo_salary) from tbl_workers)

14. תת שאילתה תרשם בסוגריים. • ה – select בתת השאילתה יכיל שדה אחד בלבד וכזה שניתן להשוואה עם הערך מבחוץ. • לא יעילה, משום שמחשבת בכל פעם את אותו ערך. • תת שאילתה לא יכולה להכיל מיון. • תת שאילתה יכולה להירשם גם ב - having • אם תת השאילתה מחזירה מספר רשומות יש לטפל בה אחד האופרטורים הבאים: In – שוויון עם אחד מתוך ערכי תת השאילתה Any – גדול, קטן או שווה אחד מתוך הערכים All – גדול קטן או שווה מכל הערכים

15. דוגמה 1: מספרי כל העובדים שמשתייכים למחלקות בהן השכר הממוצע הוא מעל 5000 Select wo_id From tbl_workers Where dep_id In( select dep_id from tbl_workers group by dep_id having avg(wo_salary) > 5000 ) תת שאילתה זו יכולה להחזיר מספר ערכים !

16. דוגמה 2: הצג את מספרי העובדים שלא שייכים למחלקה 20, אבל משתכרים יותר מכל המשכורות במחלקה 20. Select wo_id From tbl_workers Where dep_id < > 20 and wo_salary > all ( select wo_salary from tbl_workers where dep_id=20 )

17. תרגילים: 5. מצא את העובד שחזר אחרון מחופשה. 6. הצג את שמות העובדים שהשאילו את כל הספרים הקיימים.