ToCode

סדרת SQL בסיסי - פוסט 21 - סיכום מידע לפי קבוצות ראינו איך להפעיל פונקציות סיכום כדי לקבל ערך בודד מתוך אוסף של שורות. הרבה פעמים נרצה להפעיל את הסיכום רק על חלק מהשורות ובשביל זה יש לנו ב SQL את הפקודה GROUP BY. ארגון העובדים לפי מחלקות הפקודה GROUP BY ב SQL מאפשרת לחלק את השורות לקבוצות. באופן רגיל בתוך select בלוק זה יגרום להצגה רק של השורה הראשונה מכל קבוצה. לדוגמה השאילתה:

select * from employees group by department;

מחזירה את שלושת השורות:

id name city department salary
11 Diane London hr 70
21 Emma London it 84
31 Cindy Berlin sales 96

שילוב group by עם פונקציות סיכום הכח של Group by הוא ביכולת לשלב את החלוקה לקבוצות עם פונקציות סיכום, וכך במקום לקבל רק את השורה הראשונה לכל קבוצה אנחנו יכולים לקבל סיכום אחר של ערכים. לדוגמה השאילתה הבאה תחזיר את שם המחלקה ואת המשכורת הממוצעת באותה מחלקה:

select department, avg(salary) from employees group by department;

באופן כללי בסיסי נתונים (חוץ מ SQLite) לא אוהבים שאתם מנסים להציג ב select עמודה שלא מופיעה ב group by, לכן הפקודה הבאה לא תעבוד:

select name, department, avg(salary) from employees group by department;

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

select department, avg(salary), count(*) from employees group by department;

אפשר גם להפעיל מספר group by באותה פקודה לדוגמה הפקודה הבאה מציגה את המשכורת הממוצעת לפי עיר ומחלקה:

select *, avg(salary) from employees group by city, department;

הפקודה having כמו שהיה לנו where כדי לבחור רק חלק מהשורות בפקודות select, כך כשהשורות מסודרות בקבוצות נשתמש בפקודת having כדי לבחור רק כאלה שפונקציית הסיכום מתאימה לתנאי מסוים. אפשר לחשוב על having כמו where של פונקציות סיכום. לדוגמה אני יכול לעדכן את השאילתה הקודמת כדי שתחזיר רק את הקבוצות שמכילות יותר מעובד אחד (ובשביל לוודא שהכל עובד אני מוסיף גם את מספר העובדים לתוצאות):

select *, avg(salary), count(*) from employees group by city, department having count(*) > 1;

סדרת SQL בסיסי - פוסט 20 - פונקציות לסיכום מידע עד עכשיו השתמשנו בבסיס הנתונים בתור מחסן לטבלאות אקסל - אנחנו כותבים לשם מידע ויכולים למשוך שורות שמעניינות אותנו. בחלק זה נעבור ללמוד על הפונקציות לסיכום מידע ונפעיל אותן על כל השורות או על קבוצות של שורות. הפונקציה sum פונקציה ראשונה לשיעור היא sum, ותפקידה לחשב סכום של ערכים. בניגוד לפונקציות שראינו עד עכשיו בקורס, פונקציות סיכום מידע אינן פועלות על ערך בודד אלא על אוסף של שורות והן מסכמות את המידע בשורות אלה. כך הפונקציה sum לא תסכום ערכים מכמה עמודות (בשביל זה נוכל להשתמש בסימן + בצורה רגילה) אלא תסכום את הערכים של אותה עמודה בין כמה שורות. בשביל הדוגמה אני אקח את נתוני הדוגמה של SQLime ועל בסיס הנתונים אפעיל:

select *, SUM(salary) from employees;

התוצאה עשויה להפתיע:

id name city department salary SUM(salary)
11 Diane London hr 70 942

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

select SUM(salary) from employees;

הפונקציה COUNT הפונקציה count סופרת ערכים. יש שתי דרכים להשתמש בה - אנחנו יכולים להשתמש בה כדי לספור כמה שורות יש בקלט:

select count(*) from employees;

או כדי לספור כמה ערכים שאינם NULL יש בעמודה מסוימת בקלט:

select count(name) from employees;

בשני המקרים אפשר להשתמש בה ליד sum כדי לקבל גם סכום וגם מספר הערכים:

select count(*), sum(salary) from employees;

הפונקציה AVG הפונקציה avg מחשבת את הממוצע של עמודה מספרית:

select count(*), sum(salary), avg(salary) from employees;

count(*) sum(salary) avg(salary)
13 942 94.2

אם תפעילו אותה על עמודה שאינה מספרית תקבלו 0 או שגיאה, תלוי בבסיס הנתונים. הפונקציות min ו max נסיים עם שתי הפונקציות min ו max שמחזירות את הערך הקטן ביותר והגדול ביותר בקלט:

select count(*), sum(salary), avg(name), min(salary), max(salary) from employees;

סדרת SQL בסיסי - פוסט 18 - פונקציות לעבודה עם תאריכים עבודה עם תאריכים היא אחד האתגרים הגדולים של מערכות מחשב. בסיסי נתונים מנסים לעזור לנו בזה ומאפשרים לנו לשמור עמודה מסוג תאריך וגם מספקים מספר פונקציות לחישובים על עמודות תאריכים. שימו לב שבסיסי נתונים לא יודעים להתמודד עם אזורי זמן ולכן הפונקציות שנראה תמיד מקבלות רק תאריך ושעה, וזו תהיה אחריות של מי שמשתמש בבסיס הנתונים "לסדר" את השעונים כשהמערכת צריכה לשמור מידע על שעות באזורי זמן שונים. התאריך והשעה הנוכחיים ב SQLite שלושת הפונקציות הראשונות שארצה להראות הן date, time ו datetime. הן מחזירות את התאריך הנוכחי, השעה הנוכחית והתאריך והשעה הנוכחיים. בואו נראה אותן בפעולה. תחילה הפונקציה date שמחזירה את התאריך בפורמט שנה-חודש-יום:

select date();

הפונקציה time מחזירה את השעה הנוכחית:

select time();

והפונקציה datetime מחזירה מחרוזת שמתאימה לתאריך ולשעה הנוכחיים:

select datetime();

שימוש ראשון בפונקציות אלה יהיה כדי להכניס מידע חדש לבסיס נתונים. שימו לב לטבלת המשימות הבאה:

create table tasks(id integer primary key, text text, due_date datetime);

נכניס מספר משימות:

insert into tasks(text, due_date) values
('eat breakfast', datetime()),
('learn sql', datetime()),
('have a party', datetime());

select * from tasks;

וקיבלנו:

id text due_date
1 eat breakfast 2024-08-05 10:09:15
2 learn sql 2024-08-05 10:09:15
3 have a party 2024-08-05 10:09:15

התאריך הוכנס אוטומטית להיות התאריך והשעה הנוכחיים. אבל האמת שביצירת משימות עדיף לתת לבן אדם כמה שעות או ימים כדי לסיים את המשימה. ניצור מחדש את הטבלה הפעם עם שתי עמודות תאריך, אחת לתאריך בה נוצרה המשימה ושנייה לתאריך היעד:

create table tasks(
  id integer primary key,
  text text,
  created_at datetime,
  due_date datetime);

בשביל להכניס נתונים אני רוצה לקבל מ datetime את התאריך של מחר בבוקר. זה נראה ככה:

select datetime('now', '+1 day');

כמובן שאתם יכולים להעביר מספר ימים שונה או אפילו שעות, דקות, חודשים או שנים. המילה now מתיחסת לזמן עכשיו, ואפשר במקומה להעביר מחרוזת שתייצג את התאריך ממנו מתחילים, לדוגמה:

select datetime('2024-01-01', '+1 day')

בחזרה לטבלת המשימות שלנו - כך אני יכול להוסיף משימות שתאריך היצירה שלהן הוא עכשיו ומועד הסיום שלהן מוגדר לשבוע הבא:

insert into
    tasks(text, created_at, due_date)
    values('task 1', datetime(), datetime('now', '+7 days'));

התאריך והשעה הנוכחיים ב SQL Server סיפרתי כבר שיש לנו בעיה של תאימות? נו, אז יש לנו. בשביל לקבל את התאריך והשעה הנוכחיים ב SQL Server נשתמש בביטוי CURRENT_TIMESTAMP בלי סוגריים באופן הבא:

select CURRENT_TIMESTAMP;

ובשביל להוסיף לו יומיים אני מפעיל פונקציה נפרדת בשם DATEADD:

SELECT DATEADD(day, 2, CURRENT_TIMESTAMP);

התאריך והשעה הנוכחיים בפוסטגרס פוסטגרס כולל את הערך CURRENT_TIMESTAMP של SQL Server ובנוסף את הערכים CURRENT_TIME ו CURRENT_DATE כדי לקבל את הזמן או התאריך הנוכחי. בנוסף יש פונקציה בשם now() שמחזירה את אותו ערך. בשביל חישובים יש אופרטור בשם interval שעובד כך:

SELECT now() + INTERVAL '2 days'

עכשיו אתם 1. השתמשו באתר https://sqliteonline.com ובנו את טבלת המשימות עם הנתונים לדוגמה ב SQL Server ובפוסטגרס. 2. קראו על הפונקציה strftime של SQLite בקישור https://www.sqlitetutorial.net/sqlite-date-functions/sqlite-strftime-function/. 3. קראו על הפונקציה format של SQL Server בקישור https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver16

סדרת SQL בסיסי - פוסט 17 - פונקציות לעבודה עם מספרים ביטויים חשבוניים היו רק ההתחלה ול SQL יש עוד כמה פונקציות שיכולות לעזור לנו בעבודה עם מספרים. עיגול לשלמים - round, ceil, floor קטגוריה ראשונה של פונקציות היא העיגול לשלמים ופה יש לנו בחירה בין שלוש פונקציות: הפונקציה floor מחזירה את המספר השלם הגדול ביותר שקטן מהשבר שהעברנו לה, הפונקציה ceil מחזירה את המספר השלם הקטן ביותר שגדול מהשבר שהעברנו לה והפונקציה round תחזיר את המספר השלם הקרוב ביותר לערך שהעברנו לה. כמה דוגמאות: 1. מחזיר 2, בעצם מוריד את החלק שאחרי הנקודה:

select floor(2.6)

2. מחזיר 3, כי לוקח את המספר השלם הקטן ביותר שגדול מהערך שהעברנו:

select ceil(2.6)

3. מחזיר 3, כי השבר גדול או שווה לחצי:

select round(2.6)

4. מחזיר 2, כי השבר קטן מחצי:

select round(2.2)

שימו לב שב SQL Server הפונקציה ceil נקראת ceiling. שורש וחזקה הפונקציה sqrt מחזירה שורש ו power מחזירה את המספר בחזקה. 1. לדוגמה שורש 9 הוא 3:

select sqrt(9);

2. דוגמה נוספת, 2 בחזקת 3 נותן 8:

select pow(2, 3);

אנחנו יכולים להשתמש בפונקציות אלה בכל חלק של השאילתה - גם בהצגת עמודות, גם בעדכון עמודות וגם ב where. שימו לב SQL Server משתמש בשם power בשביל פונקציית החזקה. מספר אקראי הפונקציה random מחזירה מספר אקראי למשל:

select random()

החזירה לי את הערך -823852169001874814. בעזרת קצת מתמטיקה אני יכול להפוך את המספר לערך אקראי בכל טווח שאני רוצה, לדוגמה כך אני מקבל מספר אקראי בין 0 ל 99 כולל:

select MOD(abs(random()), 100);

הפונקציה abs מחזירה ערך שלם, כלומר מחזירה את המספר בלי סימן המינוס (אם היה בו), והפונקציה mod מחזירה את שארית החלוקה. לכן אנחנו לוקחים מספר אקראי, מורידים לו את המינוס, מנסים לחלק ב 100 ולוקחים את השארית וזה מספר אקראי בין 0 ל 99 כולל. אנחנו יכולים למצוא שימוש מעניין למספרים אקראיים כשנחבר אותם עם order. פקודת order by מאפשרת לנו לקבוע את מיון התוצאות שנקבל, ואם נבחר שהמיון יהיה לפי מספר אקראי נקבל תמיד תוצאות אקראיות:

select * from customers order by random();

שימו לב הפונקציה random נקראת ב SQL Server בשם rand.

concat(upper(substr(last_name, 1, 1)), substr(last_name, 2)),
    ' ',
    upper(substr(first_name, 1, 1))) as name
from customers;

הפונקציה substr עובדת ברוב בסיסי הנתונים אבל ב MS SQL היא נקראת substring. החלפת טקסט במחרוזת הפונקציה replace מקבלת טקסט, מחרוזת שצריך להחליף וטקסט לרשום במקום ומחליפה את הטקסט בערך שצריך לרשום במקומו. בדוגמה של הלקוחות אנחנו יכולים לבחור את כתובות האימייל:

select id, email from customers;

ולקבל:

id email
5 david.wilson@example.com
4 emily.brown@example.com
2 jane.smith@example.com
1 john.doe@example.com
3 mike.johnson@example.com

ואז להשתמש ב replace כדי לעדכן את כל כתובות האימייל מהסיומת example.com לסיומת gmail.com באופן הבא:

update customers set email = replace(email, 'example.com', 'gmail.com');

עכשיו אתם 1. עדכנו את טבלת הלקוחות והוסיפו לשם המשפחה של כל לקוח את המספר 5. 2. מחקו את הלקוח david wilson מהטבלה. 3. הדפיסו מעמודת אימייל רק את הטקסט שמופיע לפני הסיומת gmail.com. 4. הציגו רק את השורות בהן אורך כתובת האימייל קצר יותר מ 10 תווים. אין כאלה שורות? הכניסו אחת.

סדרת SQL בסיסי - פוסט 16 - פונקציות לעבודה עם מחרוזות פונקציות ב SQL הן דרך להגדיר חישוב על הערך ששמור בטבלה כדי שנוכל להשתמש בו להצגת הנתונים, לעדכון שלהם או לשליפת שורות מתאימות. ראינו כמה דוגמאות בשיעורים קודמים עם ביטויים חשבוניים. בחלק זה של הקורס נראה את הפונקציות שקשורות לסוגי נתונים נוספים - מחרוזות, מספרים ותאריכים. כן, יש לנו בעיה של תאימות לאותן פונקציות ב SQL יש לפעמים שמות שונים בבסיסי נתונים שונים. אני אראה כאן את הפונקציות על SQLite ואת השמות שלהן בבסיסי נתונים אחרים. בשביל להיות בטוחים שאתם כותבים שאילתה שמתאימה לבסיס הנתונים איתו אתם הולכים לעבוד אני ממליץ להשתמש באתר: https://sqliteonline.com ולנסות את הפקודות על בסיס הנתונים שמתאים לכם. אורך מחרוזת נתחיל עם הצגת אורך המחרוזת ואני ממשיך את הדוגמה עם טבלת המהדקים מהשיעור הקודם. אני מזכיר שיצרתי אותה עם:

CREATE TABLE paper_clips (
    id INTEGER PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    size VARCHAR(20),
    color VARCHAR(20),
    quantity INTEGER
);

INSERT INTO paper_clips (type, size, color, quantity) VALUES
('standard', 'medium', 'silver', 100),
('binder', 'large', 'black', 50),
('mini', 'small', 'red', 200),
('jumbo', 'extra_large', 'blue', 30),
('colored', 'medium', 'green', 150);

הפונקציה length מחזירה אורך של מחרוזת. אני יכול להפעיל אותה בלי טבלה בכלל באופן הבא:

SELECT LENGTH('hello');

ולקבל 5, או עם טבלה כדי לקבל אורך של אחת העמודות:

select id, type, size, color, quantity, length(size) from paper_clips;

והתוצאה כוללת עכשיו עמודה שישית שמכילה את האורך של עמודת הגודל. נשים לב שב SQLite אני יכול להפעיל גם length על עמודה מספרית כדי לקבל את מספר הספרות במספר, אבל בבסיסי נתונים אחרים זה לא עובד. אפשר להשתמש ב length גם ב where של השאילתה, למשל לחפש את כל השורות בהן האורך של הצבע של הסיכה גדול מ-4:

select id, type, size, color, quantity from paper_clips where length(color) > 4;

שימו לב להבדל בין length(color) ל length('hello'). כשאני מסתכל על אורך של תוכן בבסיס הנתונים שרשום בעמודה מסוימת אני כותב את שם העמודה בלי גרש סביבו. כשאני רוצה לקבל אורך של מילה קבועה אני חייב להקיף אותה בגרש. הפקודה length עובדת בבסיסי נתונים SQLite, PostgreSQL, MariaDB. ב SQL Server היא נקראת len. חיבור מחרוזות ניצור טבלה חדשה של לקוחות עם התוכן הבא:

create table customers (
    id integer primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    email varchar(100) unique not null
);

insert into customers (first_name, last_name, email) values
('john', 'doe', 'john.doe@example.com'),
('jane', 'smith', 'jane.smith@example.com'),
('mike', 'johnson', 'mike.johnson@example.com'),
('emily', 'brown', 'emily.brown@example.com'),
('david', 'wilson', 'david.wilson@example.com');

הפקודה הבאה תחבר את השם הפרטי ושם המשפחה של הלקוח לעמודה אחת, ולידו תדפיס גם את האורך הכולל של השם הפרטי ושם המשפחה:

select
  concat(first_name, ' ', last_name) as name,
  length(concat(first_name, ' ', last_name)) as len
from customers;

שימו לב שה as לא מאפשר לי להשתמש במילה name בהמשך השורה, אבל אני כן יכול להשתמש בפונקציה בתוך פונקציה ולשלב את concat עם length. הפקודה concat מקבלת מספר מחרוזות ומחברת אותן יחד לעמודה אחת. חיתוך חלק ממחרוזת הפקודה substr מקבלת מחרוזת, נקודת התחלה ואורך וחותכת רק את החלק שביקשנו מתוך המחרוזת. לדוגמה נוכל להפעיל:

select substr('hello', 2, 2)

ונקבל את הפלט el כי זאת המילה שמתחילה באות השנייה ואורכה שתי אותיות מתוך המחרוזת הגדולה יותר hello. בחזרה לטבלת הלקוחות נוכל לקחת רק את האות הראשונה של השם הפרטי ולחבר אותה לשם המשפחה באופן הבא:

select id, concat(last_name, ' ', substr(first_name, 1, 1)) from customers;

הפונקציות upper ו lower מאפשרות להפוך טקסט לאותיות גדולות או קטנות, וכך נוכל לעדכן את אותה אות ראשונה שתופיע באות גדולה:

select id, concat(last_name, ' ', upper(substr(first_name, 1, 1))) from customers;

ומיטיבי לכת יוכלו לעדכן גם את שם המשפחה כך שיתחיל באות גדולה:

select
  id,
  concat(

סדרת SQL בסיסי - פוסט 15 - משפט WHERE פקודות select ו update יכולות להסתיים ב where כדי לדייק את השורות עליהן עובדים. ראינו בשיעורים קודמים איך להשתמש ב where כדי למצוא שורות עם ערך ששווה לערך קבוע מסוים או שורות עם ערכי NULL. בשיעור זה נראה עוד כמה אפשרויות לשימוש במשפטי where. שונה, גדול מ, קטן מ אחרי השווה כמובן שצריכים להמשיך לשונה גדול וקטן. בואו ניצור קצת נתונים:

CREATE TABLE paper_clips (
    id INTEGER PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    size VARCHAR(20),
    color VARCHAR(20),
    quantity INTEGER
);

INSERT INTO paper_clips (type, size, color, quantity) VALUES
('standard', 'medium', 'silver', 100),
('binder', 'large', 'black', 50),
('mini', 'small', 'red', 200),
('jumbo', 'extra_large', 'blue', 30),
('colored', 'medium', 'green', 150);

ולשאילתות: 1. נציג את כל השורות שצבען שונה מכסף:

SELECT * FROM paper_clips WHERE color != 'silver';

2. נציג את כל השורות שהכמות שלהן גדולה מ 100:

SELECT * FROM paper_clips WHERE quantity > 100;

3. כמות קטנה מ 100:

SELECT * FROM paper_clips WHERE quantity < 100;

4. כמות גדולה או שווה 50:

SELECT * FROM paper_clips WHERE quantity >= 50;

5. גודל גדול יותר מ hello:

SELECT * FROM paper_clips WHERE size > 'hello';

השאילתה האחרונה נשמעת מוזרה - מה זה אומר שהגודל גדול יותר מהמילה hello? הרי גודל הוא מחרוזת? התשובה שב SQL אנחנו משווים מחרוזות לפי המיקום שלהן בסדר מילוני, לדוגמה מילה שמתחילה ב d תהיה גדולה יותר ממילה שמתחילה ב a. בין המילה between מאפשרת לבחור ערכים שנמצאים בטווח מסוים לדוגמה כל השורות בהן הכמות היא בין 20 ל 50:

SELECT * FROM paper_clips WHERE quantity between 20 and 50;

תנאי between כולל את שני הערכים שבקצה ולכן השאילתה תחזיר גם שורות בהן ה quantity היא 20, 50 וכל מה שביניהם. אחד מ המילה in מאפשרת לבחור שורות בהן הערך שאנחנו מחפשים הוא אחד מכמה ערכים קבועים. לדוגמה הצגת כל השורות בהן הגודל הוא large או small:

select * from paper_clips where size in ('large', 'small');

חיבור תנאים (או, וגם, לא) ניתן לחבר מספר תנאים בפקודת WHERE באמצעות המילים AND, OR ו NOT. המילה OR תחזיר תנאי שיצליח אם אחד מהתנאים שמרכיבים אותו הוא אמיתי, המילה AND דורשת שכל התנאים שמרכיבים את התנאי הגדול יהיו אמיתיים והמילה NOT הופכת תנאי. כמה דוגמאות: 1. נציג את כל השורות בהן הגודל הוא בינוני או גדול וגם הכמות מעל 100:

select * from paper_clips where size in ('large', 'small') AND quantity > 100;

2. נציג את כל השורות בהן הכמות קטנה מ 50 או גדולה מ 150:

select * from paper_clips where quantity < 50 OR quantity > 150;

3. הצגת כל השורות בהן הגודל הוא "קטן" או "בינוני" והכמות קטנה מ 50, או שהכמות גדולה מ 150:

select * from paper_clips where
  (quantity < 50 AND size in ('small', 'medium'))
  OR quantity > 150;

4. הצגת כל השורות בהן הצבע אדום והכמות גדולה מ 100:

select * from paper_clips where color = 'red' AND quantity > 100;

5. הצגת כל השורות מלבד אלה שהתאימו ל-4:

select * from paper_clips where NOT (color = 'red' AND quantity > 100);

שימו לב שאני משתמש בסוגריים כדי לחבר תנאים כשהחיבור מסובך כדי להבהיר לקורא ולמחשב מה סדר הפעולות לביצוע - בדוגמה האחרונה כדי להבהיר שה NOT מופעל על כל התנאי שאחריו, וה AND מופעל רק על שני התנאים שצמודים אליו.

סדרת SQL בסיסי - פוסט 14 - ערכים ריקים הערך המיוחד NULL מייצג ב SQL מצב בו אנחנו לא יודעים מה הערך בעמודה או שערך בעמודה לא קיים. בשיעור זה אראה מספר דגשים לעבודה איתו. הכנסת ערכים ריקים פקודת INSERT שלא מציינת עמודה אחת או יותר תגרום להכנסת הערך NULL לאותה עמודה שלא הופיעה בפקודה, אלא אם כן לאותה עמודה הוגדר ערך ברירת מחדל. לדוגמה:

create table demo(id integer primary key,
a integer default 0,
b integer,
c varchar(20),
d varchar(50) default 'Unknown');

insert into demo(c) values('hello');
select * from demo;

מחזירה לי טבלה עם השורה הבודדת:

id a b c d
1 0  hello Unknown

העמודה c קיבלה את הערך hello מפקודת ההכנסה, העמודה d קיבלה את הערך Unknown כי זה היה ערך ברירת המחדל שלה, העמודה id קיבלה את הערך 1 שזה id שנוצר אוטומטית והעמודה a קיבלה את הערך 0 כי זה היה ערך ברירת המחדל שלה. העמודה b לעומת זאת נשארה ריקה, כלומר מכילה את הערך המיוחד NULL. אפשר גם להכניס NULL כשאנחנו מבצעים הכנסה של מספר שורות, או אם יש כבר הגדרת ערך ברירת מחדל לעמודה לדוגמה הפקודה הבאה שומרת את הערך NULL בעמודה a, למרות שלעמודה מוגדר ערך ברירת מחדל:

insert into demo(a) values(null);

והפקודה הבאה מוסיפה מספר שורות בשלוש מהן יש ערך לעמודה b ובאחת העמודה תישאר ריקה, כלומר עם הערך NULL:

insert into demo(b) values (5), (8), (null), (9);

בשביל להוסיף שורה שמורכבת רק מערכי ברירת המחדל לטבלה אני יכול להפעיל:

insert into demo default values;

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

SELECT * FROM demo WHERE b = NULL;

במקום זה אנחנו צריכים להשתמש בפונקציה IS_NULL. הדרך הנכונה להציג את כל השורות עבורן יש ערך ריק בעמודה b היא:

SELECT * FROM demo WHERE b IS NULL;

אפשר גם לחפש את כל השורות בהן הערך של b אינו NULL עם הפקודה:

SELECT * FROM demo WHERE b IS NOT NULL;

הפונקציה COALESCE בעבודה עם NULL יהיו שאילתות בהן כן נרצה לראות איזשהו ערך ברירת מחדל במקום הערך הריק בעמודה. במצבים כאלה נוכל להשתמש ב COALESCE. פונקציה זו מקבלת מספר ערכים ומחזירה את הראשון ביניהם שאינו NULL. בשביל להשתמש בה נעביר כפרמטר ראשון לפונקציה עמודה שאולי יש בה ערך NULL וכפרמטר שני ערך ברירת מחדל שיוצג אם הערך הראשון הוא NULL. לדוגמה אם במקום ערכים ריקים מהטבלה אני רוצה שיוצג המספר 0 אני כותב:

SELECT id, a, coalesce(b, 0), c, d FROM demo;

והתוצאה:

id a coalesce(b, 0) c d
1 0 0 hello Unknown
2  0  Unknown
3 0 5  Unknown
4 0 8  Unknown
5 0 0  Unknown
6 0 9  Unknown
7 0 0  Unknown

עכשיו אתם 1. נסו להכניס NULL לכל אחת מהעמודות בטבלה (כולל id). מה קורה? 2. מחקו את כל השורות עבורן הערך של b הוא NULL. 3. מחקו את כל השורות עבורן הערך של d אינו NULL. 4. צרו מחדש כמה שורות עם הערך NULL בעמודה b ואז עדכנו את כל השורות בהן הערך של b הוא NULL להחזיק את הערך 5.

סדרת SQL בסיסי - פוסט 13 - תרגול עבודה עם מידע תרגול מערכת עובדים צרו בסיס נתונים חדש ובו טבלה בודדת בשם employees עם העמודות: 1. id 2. first_name 3. last_name 4. email 5. hire_date 6. salary חשבו איזה סוג מידע יתאים לכל עמודה. אחרי יצירת הטבלה הוסיפו עמודה בשם role שמתארת את התפקיד של העובד בחברה, ואז הכניסו מספר שורות לטבלה. עדכנו את כל העובדים שהצטרפו לחברה אחרי שנת 2020 והעלו להם את המשכורת ב 10%. לסיום מחקו את הטבלה. השאלת ספרים צרו בסיס נתונים חדש ובו טבלה בודדת בשם books עם העמודות: 1. id 2. title 3. author 4. publication_date 5. genre הוסיפו עמודה בשם status שצריכה לשמור האם הספר נמצא כרגע בהשאלה או שהוא על המדף ועמודה בשם return_date שתחזיק את התאריך בו צריכים להחזיר את הספר (אם הוא לא בהשאלה העמודה תהיה ריקה). 1. הוסיפו מספר שורות ולאחר מכן עדכנו את אחת מהן לעבור להשאלה וציינו גם תאריך החזרה. 2. הציגו את כל הז'אנרים של הספרים ללא כפילויות. 3. מחקו את כל הספרים שנמצאים בהשאלה 4. מחקו את הטבלה.

סדרת SQL בסיסי - פוסט 12 - עוד כמה יכולות של SELECT הנה עוד כמה יכולות של פקודת SELECT שיעזרו לנו בכתיבת שאילתות. כותרות לעמודות מה קורה כששם העמודה בבסיס הנתונים ארוך מדי או לא מספיק אינפורמטיבי? הפקודה SELECT מאפשרת לנו להגדיר כותרת אחרת לעמודה באמצעות הוספת המילה as אחרי שם העמודה. זה נראה ככה (נסו את זה על בסיס הנתונים לדוגמה של SQLime):

SELECT id, name, city as City, salary as 'Hourly Wage' from employees;

שימו לב שאת City כתבתי כמילה בודדת אחרי as, אבל את Hourly Wage כבר הקפתי בגרש בודד כי מדובר בכמה מילים. ערכים מחושבים בשיעור על עדכון ראינו שאנחנו יכולים לעדכן ערך להיות תוצאה של ביטוי חשבוני. מעניין לשים לב שגם ב SELECT ניתן לרשום ביטוי חשבוני ולקבל את ערך העמודה אחרי הפעלת הביטוי. הדוגמה הבאה מציגה את המשכורת בתוספת 10%:

SELECT *, salary * 1.1 as 'After Raise' from employees;

id name city department salary After Raise
11 Diane London hr 70 77
12 Bob London hr 78 85.80000000000001
21 Emma London it 84 92.4
22 Grace Berlin it 90 99.00000000000001
23 Henry London it 104 114.4
24 Irene Berlin it 104 114.4
25 Frank Berlin it 120 132
31 Cindy Berlin sales 96 105.60000000000001
32 Dave London sales 96 105.60000000000001
33 Alice Berlin sales 100 110.00000000000001

בחירה בלי טבלה פקודת SELECT יכולה גם לעזור לנו בניסויים עם SQL אפילו כשאין טבלה, לדוגמה בשביל פעולות חשבון נוכל לכתוב:

select 2 + 5;

ונקבל את התוצאה 7. או בשביל לקבל מספר עמודות נוכל לכתוב:

select 2 as "A", 3 as "B", 5 as "C";

הצגת ערכים ייחודיים נתבונן בתוצאות של:

select id, name, city, department from employees;

אנחנו רואים שהעובדים מחולקים בין המחלקות hr, it ו sales. אפשר לנסות להדפיס רק את שמות המחלקות ואז נקבל:

select department from employees;

department
hr
hr
it
it
it
it
it
sales
sales
sales

שזה בדיוק הרשימה הנכונה אבל למה כל השורות הכפולות? התשובה היא שזה שבחרתי עמודה מסוימת לא אומר כלום על העמודות האחרות, ומבחינת SQL הוא מציג את הערך של עמודת department לכל אחת מהשורות. אני יכול לבקש מ select לצמצם ערכים כפולים בעזרת המילה distinct. זה נראה ככה:

select distinct department from employees;

והתוצאה:

department
hr
it
sales

סדרת SQL בסיסי - פוסט 11 - עדכון טבלאות בדוגמאות ובתרגילים בקורס הזה החיים שלנו קלים כי אנחנו יודעים מראש איזה מידע יישמר בטבלאות. בחיים האמיתיים לפעמים אנחנו מגלים ששכחנו עמודה או שטעינו באחד השמות הרבה אחרי שיצרנו את הטבלה ואפילו אחרי שכבר הכנסנו מידע אליה. הוספת עמודה אם יצרתם טבלה ואתם צריכים להוסיף לה עמודה, ולא רוצים למחוק את הטבלה וליצור אותה מחדש (כי כבר יש בה מידע חשוב) תוכלו להפעיל את הפקודה alter table עם בלוק add. ניצור מחדש את דוגמת כוכבי הלכת שלנו מהשיעור הקודם:

CREATE TABLE planets (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    diameter_km FLOAT,
    distance_from_sun_mkm FLOAT,
    number_of_rings INTEGER
);

INSERT INTO planets (id, name, diameter_km, distance_from_sun_mkm, number_of_rings) VALUES
(1, 'Mercury', 4879.4, 57.9, 0),
(2, 'Venus', 12104, 108.2, 0),
(3, 'Earth', 12742, 149.6, 0),
(4, 'Jupiter', 139820, 778.5, 4),
(5, 'Saturn', 116460, 1434, 7);

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

alter table planets add moons integer default 0;

מבנה הפקודה: 1. מתחילים עם alter table 2. לאחר מכן שם הטבלה 3. אחרי זה המילה add 4. שם העמודה להוספה 5. סוג המידע בעמודה והרחבות נוספות על סוג המידע (כמו ערך ברירת מחדל או UNIQUE או NOT NULL). אפשר גם לוותר על ערך ברירת המחדל ואז הערכים בעמודה יישארו ריקים עד שנעדכן אותם עם update. לדוגמה:

update planets set moons = 1 where name = 'Earth'

מחיקת עמודה בשביל למחוק עמודה מטבלה אני משתמש בבלוק drop column אחרי התחילית alter table, לדוגמה בשביל למחוק את עמודת הירחים החדשה שיצרתי אני מפעיל:

alter table planets drop column moons;

שינוי שם עמודה בשביל לשנות שם של עמודה אני משתמש בבלוק rename column אחרי התחילית alter table. לדוגמה בשביל לשנות את שם העמודה name ל planet_name אני יכול לכתוב:

alter table planets rename column name to planet_name;

מחיקת טבלה ואם נמאס לכם לגמרי מהטבלה אתם לא צריכים למחוק את כל בסיס הנתונים. הפקודה drop table תמחק טבלה ואת כל הנתונים שבה:

drop table planets;