פונקציות סקלריות

CEILING, FLOOR, ROUND

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

דוגמה זו מחזירה את העמודה UnitPrice מטבלת Products כולל מע"מ – 15% כאשר התוצאה מעוגלת למקום 1 אחרי הנקודה העשרונית .

SELECT ROUND (UnitPrice*1.155,1) FROM Products

String Functions

פונציות שעובדות על מחרוזות כמו len לאורך, rtrim ltrim לקיצוץ רווחים לבנים. substring לקבלת תתי מחרוזות.

השאילתא הזאת למשל תחזיר את האורך של המחרוזת.

SELECT LEN(‘ SQLSERVER’)

פונקציות זמן ותאריך

SELECT DATEDIFF(hh, '2000-01-01', '2001-01-01')

פונקציות המרה

המרת ביטוי מסוג אחד לסוג אחר.

מבנה פונקציית cast: CAST(expression AS data_type)

בדוגמא המרת תאריך למחרוזת.

SELECT CAST (GETDATE() AS char(12))

מבנה פונקציית PARSE: PARSE ( string_value AS data_type [ USING culture ] )

הםונקציה מאפשרת להמיר תאריך טקסטואלי לתאריך תקני.

SELECT PARSE('31/12/2012' AS DATETIME2 USING 'en-GB' )

ISNULL

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

SELECT CompanyName, ISNULL(Fax,'Unknown') AS Fax
FROM Suppliers

פונקצית CASE

ה- Simple Case מאפשר בדיקת ערך של משתנה בודד והצגת ערך אחר במקומו.

מבנה:

CASE input_expression 
WHEN when_expression THEN result_expression 
[...n] 
[ ELSE else_result_expression ] 
END

דוגמא

SELECT CustomerID, 
CASE 
WHEN ShippedDate IS NULL 
    THEN 'Call for schedule shipping' 
WHEN DATEDIFF(dd,ShippedDate,RequiredDate) < 0 
    THEN 'Call and apologize' 
ELSE 'Shipped O.K' 
END AS 'Shipping Status' 
FROM Orders

תרגול פונקציות סקלריות – שאלות

את שמו הפרטי של העובד באותיות קטנות , שם משפחתו באותיות Employees 1. הצג מתוך טבלת . גדולות עבור העובדים אשר מספר העובד שלהם בין 3 ל 5 'a' את שמו הפרטי של העובד , ובעמודה נוספת את מיקום האות Employees 2. א. הצג מתוך טבלת בתוך שמו בשם 'a' ב. בצע סינון לשאילתה והצג רק את העובדים אשר אין להם את האות את שם הקטגוריה , את תיאור הקטגוריה Categories 3. הצג מתוך טבלת בתוך תיאור הקטגוריה 4 ספרות מתחילת המילה. 'i' ואת מיקום התו את שמו הפרטי של העובד, שם משפחתו ושם משתמש. Employees 4. הצג מתוך טבלת שם המשתמש יורכב מ 3 אותיות ראשונות של שמו הפרטי יחד )שרשור( עם האות הראשונה של שם משפחתו את מספר המוצר, שם המוצר , Products 5. הצג מתוך טבלת ובעמודה נוספת שוב את שם המוצר כאשר כל תו '?' יוחלף בתו '-' 6. הצג את התאריך של היום. את מספר הלקוח , Orders 7. ע"מ להציג את התאריך האחרון להוצאת כל הזמנה , הצג מתוך טבלת מספר ההזמנה, תאריך ההזמנה , ואת תאריך ההזמנה פלוס 45 יום )השתמש בפונקצית תאריך על מנת לבצע את החישוב( . את שמו הפרטי של העובד ואת גילו Employees 8. הצג מתוך טבלת )ההפרש בשנים בין התאריך העכשווי ובין תאריך לידתו( את שמו הפרטי של העובד, Employees 9. הצג מתוך טבלת ואת השנה בה הוא ,) sunday, monday … את היום בו התחיל לעבוד )לדוגמא התחיל לעבוד. כפול 0.12 UnitPrice – ו ProductID את העמודות Products 10 . הצג מטבלת יש לעגל את התוצאה למספר שלם )בעיגול משתנה לפי המספר(. תן שם מתאים לעמודה. את מספר העובד ושם המשפחה משורשרים יחדיו כשביניהן רווח )תן כינוי Employees 11 . בחר מטבלת מתאים לעמודה זו(, ואת תאריך הלידה בעמודה נפרדת. את שמות המשפחה באותיות גדולות בעמודה אחת ואת תאריך הלידה Employees 12 . הצג מטבלת בעמודה שנייה, DD/MM/YY בפורמט של .D או K עבור העובדים אשר שם משפחתם מתחיל באות .LIKE במקום האופרטור WHERE בתנאי ה SUBSTRING יש להשתמש בפונקציה © SCALAR FUNCTIONS את שמו הפרטי של העובד באותיות קטנות , שם משפחתו באותיות Employees 1. הצג מתוך טבלת . גדולות עבור העובדים אשר מספר העובד שלהם בין 3 ל 5 'a' את שמו הפרטי של העובד , ובעמודה נוספת את מיקום האות Employees 2. א. הצג מתוך טבלת בתוך שמו בשם 'a' ב. בצע סינון לשאילתה והצג רק את העובדים אשר אין להם את האות את שם הקטגוריה , את תיאור הקטגוריה Categories 3. הצג מתוך טבלת בתוך תיאור הקטגוריה 4 ספרות מתחילת המילה. 'i' ואת מיקום התו את שמו הפרטי של העובד, שם משפחתו ושם משתמש. Employees 4. הצג מתוך טבלת שם המשתמש יורכב מ 3 אותיות ראשונות של שמו הפרטי יחד )שרשור( עם האות הראשונה של שם משפחתו את מספר המוצר, שם המוצר , Products 5. הצג מתוך טבלת ובעמודה נוספת שוב את שם המוצר כאשר כל תו '?' יוחלף בתו '-' 6. הצג את התאריך של היום. את מספר הלקוח , Orders 7. ע"מ להציג את התאריך האחרון להוצאת כל הזמנה , הצג מתוך טבלת מספר ההזמנה, תאריך ההזמנה , ואת תאריך ההזמנה פלוס 45 יום )השתמש בפונקצית תאריך על מנת לבצע את החישוב( . את שמו הפרטי של העובד ואת גילו Employees 8. הצג מתוך טבלת )ההפרש בשנים בין התאריך העכשווי ובין תאריך לידתו( את שמו הפרטי של העובד, Employees 9. הצג מתוך טבלת ואת השנה בה הוא ,) sunday, monday … את היום בו התחיל לעבוד )לדוגמא התחיל לעבוד. כפול 0.12 UnitPrice – ו ProductID את העמודות Products 10 . הצג מטבלת יש לעגל את התוצאה למספר שלם )בעיגול משתנה לפי המספר(. תן שם מתאים לעמודה. את מספר העובד ושם המשפחה משורשרים יחדיו כשביניהן רווח )תן כינוי Employees 11 . בחר מטבלת מתאים לעמודה זו(, ואת תאריך הלידה בעמודה נפרדת. את שמות המשפחה באותיות גדולות בעמודה אחת ואת תאריך הלידה Employees 12 . הצג מטבלת בעמודה שנייה, DD/MM/YY בפורמט של .D או K עבור העובדים אשר שם משפחתם מתחיל באות .LIKE במקום האופרטור WHERE בתנאי ה SUBSTRING יש להשתמש בפונקציה את שתי העמודות הבאות: products 13 . הצג מטבלת מופיעה ביניהן.תן את 'AND' מספר המוצר ומספר הספק באותה העמודה כאשר המילה ,)data types לעמודה )שים לב להתאמת ה "PRODUCT" השם כפול 1.165 כאשר יש לעגל את התוצאה למספר שלם כלפי מטה. )unitprice( מחיר ליחידה לעמודה המחושבת. "FULL PRICE" תן את השם . יש להציג רק את המוצרים שמחירם החדש גדול מ- 40 את העמודות הבאות: employees 14 . הצג מטבלת שם המשפחה משורשר עם אורך שם המשפחה, שם פרטי משורשר עם אורך השם הפרטי . יש לתת לכל עמודה בתצוגה כינוי מתאים. את העמודות הבאות: employees 15 . הצג מטבלת שם המשפחה, שם המשפחה מהסוף להתחלה )תן כינוי מתאים לעמודה(. .)ReportsTo הצג את הנתונים רק עבור העובדים אשר יש להם מנהל )על פי עמודת את מספר ההזמנה, תאריך ההזמנה ותאריך הדרישה עבור כל ההזמנות אשר Orders 16 . הצג מטבלת . שווה ל – 1 )RequiredDate( לתאריך הדרישה )OrderDate( מספר הרבעונים בין תאריך ההזמנה עבור כל )CompanyName( 17 . הצג מטבלת הלקוחות את ארבעת האותיות הראשונות של שם החברה .'a' שלהם מתחיל באות CompanyName הלקוחות שה שאלת אתגר את העמודות הבאות: Employees 18 . הצג מטבלת

• שם משפחה משורשר עם תאריך הלידה
• תאריך תחילת עבודה בפורמט 104 'No להציג את המחרוזת NULL במידה ובעמודה זו מופיע ,)ReportsTo( – קוד המנהל Manager' יש לכלול בתוצאות רק עובדים ששם המשפחה שלהם ארוך או שווה לשמם הפרטי

תרגול פונקציות סקלריות – תשובות

-- ex1
select 
    lower (FirstName),
    upper (LastName)
from Employees
where EmployeeID Between 3 and 5

-- ex2
select
    FirstName,
    CHARINDEX ('a', FirstName) as [indexOfA]
from Employees

-- ex3
select
    CategoryName,
    [Description],
    CHARINDEX ('i', [Description], 4) as [indexOfi]
from Categories

-- ex4
select
    FirstName,
    LastName,
    SUBSTRING (FirstName, 1, 3) + SUBSTRING (LastName, 1, 1) 
  as UserName
from Employees

-- ex5
select
    ProductID,
    ProductName,
    REPLACE (ProductName, '?', '-') as Replaced
from Products

-- ex6
select GETDATE() as today

-- ex7
select 
    CustomerID,
    OrderID,
    OrderDate,
    DATEADD (d, 45, OrderDate) as SupplyDate
from Orders

-- ex8
select
    FirstName,
    DATEPART(yy, BirthDate) as BirthYear,
    DATEDIFF (yy, BirthDate, GETDATE()) as Age
from Employees

-- ex9
select
    FirstName,
    DATENAME (WEEKDAY, HireDate) as HireDay,
    DATENAME (Year, HireDate) as HireYear
from Employees

-- ex10
select
    ProductID,
    ROUND (UnitPrice * 0.12, 0) as RoundedPrice
from Products

-- ex11
select
    Cast (EmployeeID AS varchar) + ' ' + LastName 
    as EmploeeDetails,
    BirthDate
from Employees

-- ex12
select
    upper (LastName),
    LastName,
    format (birthdate, 'dd/MM/yy') as BirthDateFormat
from Employees
where SUBSTRING (LastName, 1, 1) = 'K' or SUBSTRING (LastName, 1, 1) = 'D'

-- ex13
select
    Cast (ProductID AS varchar) + ' AND ' + Cast (SupplierID AS varchar) 
        as PRODUCT,
    FLOOR (unitprice*1.165) as [FULL PRICE]
from products
where FLOOR (unitprice*1.165) > 40

-- ex14
select
    LastName + ' ' + cast (LEN (LastName) AS varchar) as LastNameLen,
    FirstName + ' ' + cast (LEN (FirstName) AS varchar) as FirstNameLen
from employees

-- ex15
select
    LastName,
    REVERSE (LastName) as LastNameRev
from employees
where ReportsTo is not null

-- ex16
select
    OrderID,
    OrderDate,
    RequiredDate
from Orders
where DATEDIFF (q, OrderDate, RequiredDate) = 1

-- ex17
select
    SUBSTRING (CompanyName, 1, 4) as CompanyName
from Customers
where SUBSTRING (CompanyName, 1, 1) = 'a'