תרגול זה מסתמך על מאגר הנתונים ACDB. לתרגולים נוספים בנושאים שונים ניתן להשתמש במדריך ה SQL.

חלק א – פונקציות מחרוזתיות

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

2.א. הציגו מתוך טבלת CUSTOMERS את השם הפרטי, את שם המשפחה וכתובת מייל חדשה שאתם תייצרו. כתובת המייל של הלקוחות תורכב מהאות הראשונה של שמם הפרטי משורשרת עם שלושת האותיות הראשונות של שם משפחתם משורשרות עם @mymail.com.

ב. שנו את כתובת המייל של הלקוחות כך שתורכב מהאות הראשונה של שמם הפרטי משורשרת שם שלושת האותיות האחרונות של שם משפחתם משורשרות עם @mymail.com.

3.הציגו מתוך טבלת CUSTOMERS את שמות המשפחה, כמות התווים בשמות המשפחה, עבור הלקוחות אשר שם משפחתם גדול מ- 9 תווים.

4.א. עבור הלקוחות אשר במספר הטלפון העיקרי שלהם מופיע הרצף – 515, הציגו את שמו הפרטי של הלקוח, שם משפחתו, מספר הטלפון העיקרי שלו ומספר הטלפון החדש – במספר החדש כל מספר המכיל את הרצף 515 יוחלף לרצף $$$.

ב. חזרו שוב על התרגיל האחרון, הפעם במספר הטלפון החדש – רק מספר טלפון המכיל קידומת 515 יוחלף לקידומת $$$.

חלק ב – פונקציות מספריות

5.הציגו מתוך טבלת CUSTOMERS את

·שמו הפרטי של הלקוח

·סכום הנחה חודשית

·סכום הנחה חודשית בתוספת 19.7%

·סכום הנחה חודשית בתוספת 19.7% מעוגלת בדיוק של 2 ספרות בעיגול משתנה

·סכום הנחה חודשית בתוספת 19.7% מעוגלת כלפי מטה.

·סכום הנחה חודשית בתוספת 19.7% מעוגלת כלפי מעלה.

חלק ג – פונקציות תאריכיות

6.הציגו מתוך טבלת CUSTOMERSאת שמו הפרטי של הלקוח, תאריך הצטרפותו לחברה, תאריך הצטרפותו לחברה פחות 10 ימים, תאריך הצטרפותו לחברה ועוד חודש, ההפרש בימים בין תאריך הצטרפותו לחברה לתאריך הנוכחי.

7.הציגו מתוך טבלת CUSTOMERS את שמו הפרטי של הלקוח, תאריך הצטרפותו לחברה, ותאריך לשיחת שימור, תאריך זה יחול (או כבר חל) השנה, באותו תאריך בו הלקוח הצטרף לחברה, לדוגמא – אם לקוח הצטרף ב 09-02-2007שיחת השימור תעשה ב 09-02-2013.

* הדוגמא מסתמכת על ההנחה שהשנה הנוכחית היא 2013

8.הציגו מתוך טבלת CUSTOMERSאת שמו הפרטי של הלקוח, תאריך יום הולדתו וגילו עבור הלקוחות אשר גילם מעל 50.

9.הציגו מתוך טבלת CUSTOMERS את כל הלקוחות אשר יום ההולדת שלהם חל היום

10.הציגו מתוך טבלת CUSTOMERS את כל הלקוחות אשר היום חלפו חמש שנים בדיוק מאז הצטרפו לחברה.

חלק ד – פונקציות המרה

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

12.הציגו מתוך טבלת CUSTOMERS את

·שם משפחת הלקוח

·מדינת הלקוח באותיות גדולות משורשרת עם מספרו

·תאריך הצטרפותו לחברה משורשר עם תאריך לידתו

עבור הלקוחות אשר שם משפחתם מתחיל באות D או באות K.
פתרו את התרגיל באמצעות
CONVERT (הפורמט לא משנה)
בתנאי ה
WHERE – השתמשו בפונקציית SUBSTRING במקום האופרטור LIKE
.

חלק ה – פונקציות שונות

13.מתוך טבלת CUSTOMERS, עבור הלקוחות שמספר החבילה היא 27, הציגו את שמם הפרטי, שם משפחתם, תאריך לידתם, מספרי הטלפון שלהם (עקרי ומשני) ומספר פקס.

א.במידה וקיים NULL במספר הטלפון או מספר הפקס הציגו N / A''

ב.הריצו שוב את התרגיל, הפעם עבור הלקוחות ששנת לידתם 1972 בלבד

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

בין 0 – 10 יקבל דירוג A

בין 11 – 20 דירוג B

בין 21 – 30 דירוג C

השאר דירוג D

פתרונות

-- 1
SELECT customer_id, LOWER(first_name)  , UPPER(last_name)
FROM customers
WHERE customer_id BETWEEN 0 AND 150 

-- 2
-- A
SELECT first_name , last_name ,
			  LEFT(first_name , 1) + LEFT(last_name , 3) + '@mymail.com'
FROM customers 

-- B
SELECT first_name , last_name ,
			  LEFT(first_name , 1) + RIGHT(last_name , 3) + '@mymail.com'
FROM customers 

-- 3
SELECT last_name , LEN(last_name)
FROM customers
WHERE LEN(last_name) > 9 

-- 4
-- A
SELECT first_name , last_name , main_phone_num , REPLACE(main_phone_num , '515' , '$$$') AS 'New_Phone_Number'
FROM customers
WHERE main_phone_num LIKE '%515%'

-- B
 SELECT first_name , last_name , main_phone_num ,
				REPLACE(LEFT(main_phone_num, 3) , '515' , '$$$') + SUBSTRING(main_phone_num , 4 , 12) AS 'New_Phone_Number'
FROM customers
WHERE main_phone_num LIKE '%515%'

-- 5
SELECT	first_name ,
				monthly_discount,
				monthly_discount * 1.197 ,
				ROUND(monthly_discount * 1.197 , 2),
				FLOOR(monthly_discount * 1.197 ),
				CEILING(monthly_discount * 1.197 )
FROM customers 

-- 6
SELECT	first_name ,
				join_date,
				DATEADD(dd, -10 , join_date),
				DATEADD(mm , 1 , join_date),
				DATEDIFF(dd , join_date , getdate())
FROM customers 

-- 7
SELECT	first_name ,
				join_date ,
			    DATEADD(yy , DATEDIFF(yy, join_date , getdate()) , join_date)
FROM customers 

-- 8
SELECT first_name , birth_date , DATEDIFF(yy , birth_date , getdate())
FROM customers
WHERE DATEDIFF(yy , birth_date , getdate()) > 50 

-- 9
SELECT first_name , birth_date
FROM customers
WHERE MONTH(birth_date) = MONTH(getdate())
              AND
			  DAY(birth_date) = DAY(getdate())

-- 10
SELECT first_name , join_date , DATEDIFF(yy , join_date , getdate())
FROM customers
WHERE DATEDIFF(yy , join_date , getdate()) = 5
			 AND
			  MONTH(join_date) = MONTH(getdate())
              AND
			  DAY(join_date) = DAY(getdate())

-- 11
SELECT first_name +  ' / '  + CAST(DAY(join_date) AS VARCHAR) ,
              last_name +  ' / '   + CAST(monthly_discount AS VARCHAR)
FROM customers

-- 12
SELECT  last_name,
				UPPER(state) + ' / ' + CONVERT(varchar , customer_id , 102) ,
				CONVERT(varchar , birth_date , 103) + ' / ' + CONVERT(varchar , join_date ,  103)
FROM customers
WHERE SUBSTRING(last_name , 1 , 1) IN ('D' , 'K')

-- 13
-- A
SELECT	first_name ,
				last_name ,
				ISNULL(main_phone_num ,	'N / A'),
				ISNULL(secondary_phone_num,	'N / A'),
				ISNULL(fax,	'N / A')
FROM customers
WHERE pack_id = 27 

-- B
SELECT	first_name ,
				last_name ,
				birth_date ,
				ISNULL(main_phone_num ,	'N / A'),
				ISNULL(secondary_phone_num,	'N / A'),
				ISNULL(fax,	'N / A')
FROM customers
WHERE pack_id = 27
AND
              year(birth_date) = 1972

-- 14
SELECT first_name , last_name , monthly_discount ,
			  CASE WHEN monthly_discount BETWEEN 0 AND 10 THEN 'A'
						WHEN monthly_discount BETWEEN 11 AND 20 THEN 'B'
						WHEN monthly_discount BETWEEN 21 AND 30 THEN 'C'
						ELSE 'D'
			 END AS 'Grades'
FROM customers