SQL Query Basics: SELECT, WHERE, ORDER BY, DISTINCT

1. Summary SQL DML

2. SELECT הצורה הבסיסית של שאילתא ב- SQL היא בעלת המבנה: SELECT / FROM / WHERE SELECT - מפרט את העמודות שיופיעו בתוצאה. FROM - מפרט את הטבלאות מתוכן יש ללקט את המידע. WHERE - מפרט את השורות שיופיעו בתוצאה.

3. SELECT ListOfArtists נתונה לנו טבלת האומנים, שמכילה מידע על אומנים שונים.

4. SELECT * FROM ListofArtists WHERE Nationality='American';

5. Order By • אם נרצה למיין את התוצאות נוכל להשתמש ב- Order by • השאילתא הבאה תציג את האומנים ממוינים על-פי ארץ מוצאם. • כל האומנים האמריקאיים יופיעו בהתחלה והספרדים בסוף. SELECT * FROM ListOfArtists ORDER BY Nationality; • ברירת המחדל של Order by היא למיין בסדר עולה. • כדי לשנות זאת עלינו להשתמש במילת המפתח Desc לציין שאנו רוצים סדר יורד. • Asc מציין סדר עולה

6. SELECT * FROM ListofArtists ORDER BY Nationality;

7. SELECT LastName,FirstName, Nationality, DateDeceased - DateOfBirth AS AgeFROM ListofArtists WHERE DateDeceased-DateOfBirth >=80 ORDER BY Nationality ; Display the artists who lived more than 80 years

8. DISTINCT • אם נרצה לקבל את רשימת ארצות מוצאם של האומנים, אם נקליד: SELECT Nationality FROM ListOfArtists; נקבל טבלה עם כפילויות. • כדי לדאוג שכל ערך יופיע פעם אחת בלבד ניתן להשתמש במילת המפתח DISTINCT שתופיע לפני העמודה בה אנו מעונינים. • השאילתא הבאה תציג את חמשת ארצות מוצאם של האומנים ללא כפילויות. SELECT DISTINCT Nationality FROM ListOfArtists;

9. SELECT DISTINCT Nationality FROM ListOfArtists;

10. IN, NOT IN, BETWEEN • ניתן לשלב את מילות המפתח, IN, NOT IN, BETWEEN ב-WHERE CLAUSES • כך למשל, השאילתא הבאה תציג רק את האומנים הספרדים והאמריקאים ממוינים על- פי ארץ מוצאם ושמות משפחתם. SELECT * FROM ListofArtists WHERE Nationality IN ('American', 'Spanish') ORDER BY Nationality, lastname; • המפתח הראשון, ארץ מוצא, הוא מפתח המיון הראשי. כך שנקבל בהתחלה את כל האומנים האמריקאים ממוינם על-פי שם משפחתם וכו".

11. SELECT * FROM ListofArtists WHERE Nationality IN ('American', 'Spanish') ORDER BY Nationality, lastname; Display American & Spanish Artists sorted both by their nationality and their last names

12. ניתן להשתמש במילת המפתח LIKE כדי להתאים תת-מחרוזות. השאילתא הבאה תציג בפנינו את כל האומנים ששמות משפחתם מכילים את האות a SELECT * FROM ListofArtists WHERE LastName LIKE '%a%' ; % Any number of characters ‘a%’ Displays strings that start with a ‘%a’ Displays strings that end with a ‘%a%’ Displays strings that include a

13. SELECT * FROM ListofArtists WHERE LastName LIKE '%a%' ;

14. שימוש בפונקציות ספריה • פונקציות ספריה ב- :SQL • סכום SUM • ממוצע AVG • מינימום MIN • מקסימום MAX • מניה COUNT • ניתן להשתמש בפונקציות האלה וכן להוסיף שימוש במילת המפתח AS כדי להציג תוצאה אלגנטית יותר. • השאילתא הבאה, תציג את האומנים שחיו יותר מ-80 שנה ממוינים על-פי ארצות מוצאם. SELECT LastName,FirstName, Nationality, DateDeceased - DateOfBirth AS Age FROM ListofArtists WHERE DateDeceased-DateOfBirth >=80 ORDER BY Nationality ;

15. SELECT MIN(DateDeceased-DateOfBirth) AS MinAge, MAX(DateDeceased-DateOfBirth) AS MaxAge, Round(AVG(DateDeceased-DateOfBirth) )AS AvgAge FROM ListOfArtists;

16. COUNT, GROUP BY השאילתא הבאה תציג בפנינו את מספר האומנים על-פי ארצות מוצאם. SELECT Nationality,COUNT(*) AS NumberOfArtists FROM ListOfArtists GROUP BY Nationality HAVING count(*) > 1; • HAVING מגביל את הקבוצות שמופיעות בתוצאה. • GROUP BY מבצע הקבצה של ערכים משותפים כך שהתוצאות תהיינה מחולקות על-פי ארץ המוצא. • COUNT סופר את מספר השורות.

17. SELECT Nationality,COUNT(*) AS NumberOfArtists FROM ListOfArtists GROUP BY Nationality HAVING count(*) > 1;

18. SELECT CONCAT(firstName,' ',lastName) as Name FROM listOfArtists

19. Inner Join נניח שנרצה לאחד את השורות של שתי הטבלאות, כלומר לקבל את פרטי העבודות שיש לגלריה ביחד עם שמות האומנים שיצרו אותם. נוכל לכתוב, SELECT LastName, FirstName, Title, Copy, Medium FROM ListOfArtists, Work WHERE ListOfArtists.ArtistID = Work.ArtistID;

20. ListOfArtists Work

21. SELECT lastName, FirstName, Title,Copy, Medium FROM listofartists,work WHERE listofartists.artistid = work.artistid;

22. תקציר פקודות

23. SELECT : SELECT * FROM listOfArtists; תציג בפנינו את כל ערכי העמודות בטבלת listOfartists WHERE: SELECT * FROM listOfArtists WHERE Nationality = ‘American’; תציג בפנינו רק את פרטיהם של האומנים האמריקאים SELECT * FROM listOfArtists WHERE Nationality = ‘American’AND DateDeceased >= 1950; ייתן את האומנים האמריקאיים שנפטרו לאחר שנת 1950. ORDER BY: SELECT * FROM ListOfArtists ORDER BY Nationality; נקבל את פרטי האומנים ממוינים על-פי ארץ מוצאם. SELECT * FROM ListofArtists ORDER BY Nationality ASC ,DateDeceased DESC ; נקבל את האומנים ממוינים על-פי ארץ מוצאם בסדר עולה, ובכל תת-קבוצה המיון המשני יתבצע על-פי שנת פטירתם בסדר יורד. DISTINCT: SELECT DISTINCT Nationality FROM ListOfArtists; נקבל את ארצות מוצאם של האומנים ללא כפילויות.

24. IN: SELECT * FROM ListofArtists WHERE Nationality IN ('American', 'Spanish') ORDER BY Nationality, lastname; נקבל רק את פרטי האומנים הספרדים והאמריקאים ממוינים על- פי ארץ מוצאם ושמות משפחתם. BETWEEN : SELECT * FROM listOfArtists WHERE DateOfBirth BETWEEN 1870 AND 1900; נקבל את פרטי האומנים שנולדו בין השנים 1870 לבין 1900 LIKE: SELECT * FROM ListofArtists WHERE LastName LIKE '%a%' ; נקבל את פרטי כל האומנים ששמות משפחתם מכילים את האות a MIN: SELECT LastName, FirstName, Nationality, DateDeceased-DateOfBirth AS Age FROM ListOfArtists WHERE (SELECT MIN(DateDeceased-DateOfBirth) FROM ListOfArtists) = DateDeceased-DateOfBirth; נקבל את פרטי האומן שחי הכי פחות זמן. COUNT(*), GROUP BY: SELECT Nationality, COUNT(*) AS NumberOfArtists FROM ListOfArtists GROUP BY Nationality; נקבל את מספר האומנים מכל ארץ מוצא (ההקבצה תתבצע על-פי ארצות מוצאם.)

25. HAVING: SELECT (DateDeceased-DateOfBirth) AS Age, COUNT(*) AS NumberOfArtists FROM ListOfArtists GROUP BY Age HAVING Age > 70; נקבל את מספרי האומנים בכל שכבת גיל מעל גיל 70 , מקובצים על-פי גילם. ALLIAS : SELECT MAX(Origins) FROM (SELECT DISTINCT count(*) AS Origins FROM listofartists GROUP BY Nationality) AS MaxOrigins; נקבל את המספר המקסימלי של מספר אומנים מארץ מוצא מסוים INNER JOIN: SELECT LastName, FirstName, Title, Copy, Medium FROM ListOfArtists, Work WHERE ListOfArtists.ArtistID = Work.ArtistID; נקבל את פרטי העבודות שיש לגלריה ביחד עם שמות האומנים שיצרו אותם.

26. UNION: (SELECT lastname FROM ListOfArtists) UNION (SELECT lastname FROM Customer) ; יציג רשימה של כל שמות המשפחה של האומנים והלקוחות. EXISTS: SELECT LastName FROM ListOfArtists AS a WHERE EXISTS(SELECT LastName FROM Customer AS c WHERE a.LastName=c.LastName); מבצע חיתוך בין טבלאות:יציג את שמות המשפחה של האומנים שהם גם לקוחות LIMIT, OFFSET: SELECT * FROM listOfArtists LIMIT 3 OFFSET 4; יציג 3 שורות מהטבלה החל מהשורה ה-5