SQL

1. SQL • SQLהינה השפה הדומיננטית לטיפול בבסיסי נתונים יחסיים, אך לא קיים סטנדרט SQL יחיד.ׁ(SQL92, SQL3 …) • ניתן לחלק את פקודות SQL לארבע משפחות: • פקודות להגדרת בסיס הנתונים (Data Definition) • פקודות לטיפול בנתונים (Data Manipulation) • פקודות בקרת גישה לבסיס הנתונים (Data Access Control) • פקודות לטיפול בתנועות (Transaction Management)

2. תכונות select A1,A2,…An from r1,r2,…rm where P יחסים פרדיקט שאילתות בסיסיות • שאילתת SQLפועלת על יחסים, ותוצאתה היא יחס. • השאילתה הבסיסית מורכבת משלושה רכיבים: • פסוקselect: מקביל לאופרטור ההטלה בא”י. • פסוקfrom: מקביל למכפלה קרטזית בא”י. הפסוק מפרט את היחסים המשתתפים בהערכת הביטוי. • פסוק where: מקביל לאופרטור הבחירה בא”י.

3. פסוק זה שקול (כמעט) לשאילתת אלגברת היחסים A1,A2,…An(P (r1r2...  rm)) השמטת פסוק ה whereשקולה להשמטת פעולת הבחירה ניתן להחליף את רשימת התכונות A1,A2,…Anב כוכבית (*) כדי להציג את כל התכונות ביחסים המופיעים בפסוק ה from . מצא את שמות כל הסניפים ביחס deposit select branch-name from deposit בשאילתת SQLזו אין אלימינציה של nיות חוזרות!! סילוק הnיות החוזרות מתקבל על ידי שימוש ב distinct select distinct branch-name from deposit

4. פרדיקטים מורכבים ב SQL משמשים המקשרים הלוגיים and, orאוnotבמקום הסימנים המתמטיים , ו של אלגברת יחסים. מצא את שמות הלקוחות שיש להם חשבון הלוואה בסניף כלשהו ואת ערי מגוריהם select distinct customer.customer-name,customer-city from borrow,customer where borrow.customer-name= customer.customer-name מצא את שמות הלקוחות שיש להם חשבון הלוואה בסניף merkazi ואת ערי מגוריהם select distinct customer.customer-name,customer-city from borrow,customer where branch_name = ‘merkazi’ and borrow.customer-name= customer.customer-name

5. משתני nיה - כינוי SQLשואלת את הסימון של משתני nיה מתחשיב יחסים לפי nיות. מצא את שמות הלקוחות שיש להם חשבון חיסכון בסניף כלשהו ואת ערי מגוריהם. select distinct T.customer-name,customer-city from deposit S, customer T where S.customer-name = T.customer-name scope בתת-שאילתה ניתן להשתמש רק במשתני nיה שהוגדרו בתת-השאילתה עצמה או בכל שאילתה שמכילה אותה. אם משתנה nיה מוגדר גם לוקלית בתת-שאילתה וגם גלובלית, תופסת ההגדרה הלוקלית.

6. התאמת מחרוזות • תבניות תווים מתוארות בעזרת שני תווים מיוחדים: • תו האחוזים (%) - מתאים לכל תת מחרוזת. • קו תחתי ( _ ) - מתאים לכל תו בודד. • תבניות תוים הן case sensitive. • התאמת תבניות בSQL - בעזרת אופרטור ההשוואה like מצא את שמות הלקוחות הגרים ברחוב ששמו כולל את תת-המחרוזת ‘ha’. select customer-name from customer where street like ‘%ha%’

7. חישובים על עמודות ניתן לבצע חישובים נומריים על הערכים המופיעים ביחס. ניתן להציג תוצאה של חישוב כזה כ”עמודה מדומה”. הדפס לכל חשבון חסכון, שסכום החיסכון שלו הוא מעל 0.001 מסך נכסי הסניף, את שם הלקוח, מספר החשבון, הסניף בו מתנהל ואת היחס בין סכום החיסכון לסך הנכסים של הסניף. select customer-name, account-number, branch_name, amount / assets from deposit, branch where deposit.branch-name = branch.branch-name and amount / assets > 0.001

8. צירוף של שאילתות בסיסיות פעולות minus, intersect, union שקולות ל , , - באלגברת יחסים. מצא את שמות הלקוחות שיש להם חשבון חיסכון או הלוואה או שניהם בסניף ‘aviv’ (selectdistinct customer-name from deposit where branch-name=‘aviv’) union (select distinct customer-name from borrow where branch-name=‘aviv’) מצא את שמות הלקוחות שיש להם גם חשבון חיסכון וגם הלוואה בסניף ‘aviv’ כנ”ל, אך במקוםunionצ”ל intersect

9. שאילתות עם תתי-שאילתות

10. שייכות לקבוצות האופרטורinבודק לשייכות לקבוצה. הקבוצה עשויה להיות קבועה, או תוצאה של תת-שאילתה. מצא את שמות הלקוחות שיש להם חשבון הלוואה וגם חשבון חיסכון בסניף ‘Aviv’. select distinct customer-name from borrow where branch-name=‘Aviv’ and customer-name in (select customer-name from deposit where branch-name=‘Aviv’) האופרטורnot inפועל באופן דומה (למשל, מצא שמות הלקוחות שיש להם חשבון הלוואה אך לא חשבון חיסכון בסניף ‘Aviv’).

11. מצא את שמות הלקוחות שיש להם חשבון חיסכון באחד הסניפים שבהם חוסך ‘Morag’ select distinct T.customer-name from deposit T, deposit S where S.customer-name = ‘Morag’ and S.branch-name=T. branch-name

12. השוואה לקבוצות > some,  some, < some,  some, =some, some > all,  all, < all,  all, =all, all מצא את רשימת הסניפים שערך נכסיהם גדול מערך נכסיו של סניף כלשהו ב ‘Jaffa’ select branch-name from branch where assets > some (select assets from branch where branch-city=‘Jaffa’)

13. הפסוקים containsו not containsמבצעים בדיקת האם קבוצה של ערכים מכילה קבוצת ערכים אחרת. (בניגוד ל ודומיהם, המשווים ערך בודד לקבוצת ערכים) in, > some, > all מצא את שמות הלקוחות שיש להם חשבונות חיסכון בכל הסניפים הנמצאים בעיר ‘Jaffa’ . selectdistinct S.customer-name from deposit S where (select T.branch-name from deposit T where S.customer-name = T.customer-name) contains (select branch-name from branch where branch-city= ‘Jaffa’)

14. בדיקת יחסים ריקים SQLמאפשרת לבדוק האם התוצאה של תת-שאילתה כוללת (לא כוללת) nיות באמצעות האופרטורexists (בהתאמה not exists) מצא את שמות הלקוחות שיש להם חשבון חיסכון וגם חשבון הלוואה בסניף ‘Aviv’. select customer-name from customer C where exists (select * from deposit where deposit.customer-name = C.customer-name and branch-name=‘Aviv’) and exists (select * from borrow where borrow.customer-name = C.customer-name and branch-name=‘Aviv’)

15. מצא את שמות הלקוחות שיש להם חשבונות חיסכון בכל הסניפים ב ‘Jaffa’. selectdistinct S.customer-name from deposit S where not exists ( (select branch-name from branch where branch-city = ‘Jaffa’) minus (select T. branch-name from deposit T where S.customer-name=T.customer-name) )

16. מיון התצוגה [order by [asc/descגורם למיון הnיות בתצוגה. רשימה של שמות הלקוחות שיש להם חשבונות הלוואה בסניף ‘Aviv’ ממוינת בסדר אלפביתי. selectdistinct customer-name from borrow where branch-name=‘Aviv’ order by customer-name ניתן לבצע מיון על שדות אחדים. select* from borrow order by amount desc, loan-number asc

17. הקבצה ופונקציות הקבצה • פונקציות על קבוצות של nיות מתבצעות בעזרת פסוק group by . התכונה/תכונות המופיעות בפסוק group byמגדירות קבוצות של nיות שערכיהן זהים בתכונות המצוינות. • פונקציות הקבצה: • ממוצע: avg • מינימום: min • מקסימום: max • סכום: sum • ספירה: count • פונקציות ההקבצה פועלות על קבוצה של nיות. תוצאת הפעולה היא מספר יחיד לכל קבוצה.

18. מצא את יתרת החיסכון הממוצעת בכל סניף select branch-name,avg(balance) from deposit group by branch-name מצא את מספר בעלי חשבונות החיסכון בכל סניף select branch-name,count( distinct customer-name) from deposit group by branch-name פסוק havingמציין תנאי על קבוצות. מצא שמות הסניפים שממוצע יתרות החיסכון שלהם גדול מ1200 ואת ממוצע היתרות select branch-name,avg(balance) from deposit group by branch-name having avg(balance) > 1200

19. מצא את הסניפים שממוצע יתרות החיסכון שלהם מירבי אין לבצע הרכבה של פונקציות הקבצה!!! having(max(avg…)) select branch-name from deposit group by branch-name having avg(balance) all (select avg(balance) from deposit group by branch-name) במקרה של השמטת פסוק group by , פונקצית ההקבצה תפעל על היחס כולו selectavg(balance) from deposit ממוצע יתרות החיסכון של כל החשבונות

20. כאשר מופיעים פסוקwhereופסוקhaving יחד סדר הביצוע הוא: where group by having מצא את ממוצע יתרות חשבונות החיסכון של כל החוסכים הגרים ב’Haifa’ ושיש להם לפחות 3 חשבונות חיסכון selectavg(balance) from deposit,customer where deposit.customer-name=customer.customer-name and customer-city=‘Haifa’ group by deposit.customer-name having count(distinct account-number)>2

21. עדכונים בבסיס הנתונים 1. מחיקה מבנה פקודת המחיקה delete r where P פעולה זו מוחקת מהיחס rאת הnיות שעבורן הפרדיקט P נותן ערך אמת. מחק את כל הnיות ביחס borrow delete borrow מחק את כל חשבונות החיסכון של ‘Tamir’ delete borrow where customer-name=‘Tamir’

22. 2. הוספה • ניתן להוסיף מידע ליחס בשתי צורות: • הוספה של nיה מפורשת • הוספת קבוצת nיות שהיא תוצאה של שאילתה insert into deposit values (‘hamerkaz’,55,’Cohen’,1500) insert into deposit select branch-name,loan-number,customer-name,200 from borrow where branch-name=‘Aviv’

23. 3. עדכון פקודתupdateמאפשרת לשנות חלק מהתכונות בnיה. ניתן לבחור את הnיות לעדכון כמו במחיקה ובהוספה. הוסף 5% לכל יתרות חשבונות החיסכון update deposit set balance=balance*1.05 הוסף 6% ליתרות חשבונות החיסכון מעל 1000 ו 5% ליתרות החיסכון בסכום מתחת ל 1000 update deposit set balance=balance*1.06 where balance>1000 update deposit set balance=balance*1.05 where balance1000

24. תצפיות פקודתcreate viewמאפשרת להגדיר תצפיות. מבנה הפקודה הוא create view v as query queryהוא ביטוי שאילתה חוקי. vהוא שם התצפית. תצפית של שמות כל הלקוחות והסניפים, בהם יש להם חשבונות. create view all-customer as (select branch-name,customer-name from deposit ) union (select branch-name,customer-name from borrow )

25. עדכון בתצפית עלולה לגרום לאנומלית עדכון (update anomaly). create view loan-info as select branch-name,loan-number,customer-name from borrow מאפשרת לבצע הוספה של nיה Insert into loan-info values (‘Aviv’,89,’Levy’)

26. הגדרת תבניות יחסים • SQL DDLמאפיינת את היחסים בבה”נ: • התבנית של כל יחס. • תחום הערכים המתאים לכל תכונה. • אוסף של אינדקסים לכל יחס. • הרשאות לכל יחס. • אילוצי שלמות. • מבנה האכסון הפיסי של כל יחס על הדיסק. create table r ( A1 D1, A2 D2 ….,An Dn) rהוא שם היחס ו Diהוא תחום התכונה Ai . מחיקת יחס מבה”נ מתבצעת באמצעות drop table drop table r