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

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

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

2.      הציגו מתוך טבלת EMPLOYEES  את השם הפרטי ואת שם המשפחה עבור כל העובדים אשר שם המשפחה שלהם הוא KING, כאשר אין וודאות כיצד שם המשפחה הוזן (באותיות גדולות או קטנות).

3.      א. הציגו מתוך טבלת EMPLOYEES את השם הפרטי, את שם המשפחה וכתובת מייל חדשה שאתם תייצרו. כתובת המייל של העובדים תורכב מהאות הראשונה של שמם הפרטי משורשרת עם שלושת האותיות הראשונות של שם משפחתם משורשרות עם @oracle.com.

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

ג. הציגו את כתובת המייל כך שהאות הראשונה תהיה באותיות גדולות והשאר באותיות קטנות.

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

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

5.      הציגו מתוך טבלת EMPLOYEES  את שמות המשפחה של העובדים אשר שם משפחתם גדול מ- 8 תווים.

6.      א. הציגו מתוך טבלת EMPLOYEESאת שמו הפרטי של העובד, שם משפחתו, מספר הטלפון שלו ומספר הטלפון החדש שלו – במספר החדש כל מספר המכיל את הרצף 515 יוחלף לרצף 815.

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

הטלפון החדש שלו – במספר החדש כל מספר המתחיל בקידומת 515 יוחלף לקידומת 815.

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

7.      הציגו מתוך טבלת EMPLOYEES  את שמו הפרטי של העובד , משכורתו, משכורתו החודשית (משכורת 12) , משכורתו החודשית מעוגלת בדיוק של 2 ספרות בעיגול משתנה ומשכורתו החודשית מעוגלת בדיוק של 2 ספרות כלפי מטה.

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

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

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

10.  הציגו מתוך טבלת EMPLOYEESאת שמו הפרטי של העובד, תאריך העסקתו, ותאריך לשיחת שכר אשר יחול בעוד שנה מיום העסקתו (הציגו את עמודה זו בעזרת אחת מפונקציות התאריך שנלמדו).

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

11.  הציגו מתוך טבלת EMPLOYEES  את שמם הפרטי, שם משפחתם, משכורתם, והעמלה של כל אחד מן העובדים.

א.      אם לעובד אין עמלה במקום שיופיע NULLהציגו 0.

ב.      הריצו שוב את התרגיל, הפעם אם לעובד אין עמלה במקום שיופיע  NULL הציגו No Commission.

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

בין 0 – 5000 דירוג A

בין 5001 – 15000 דירוג B

בין 15001 – 20000 דירוג C

השאר דירוג D

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

פתרונות

 

פתרונות SQL Server


הפתרונות הבאים תקפים לפלטפורמת מיקרוסופט, לפתרונות עבור פלטפורמת אורקל לחץ כאן.

1. -- LOWER / UPPER
1. -- LOWER / UPPER
SELECT LOWER(first_name) , UPPER(last_name) 
FROM employees 
WHERE employee_id BETWEEN 80 AND 150 
2. -- LOWER / UPPER
-- Note that in SQL Server, by default, the search is not case sensitive
SELECT first_name , last_name 
FROM employees 
WHERE UPPER(last_name) = 'KING'
-- or 
SELECT first_name , last_name 
FROM employees 
WHERE LOWER(last_name) = 'king'
3. -- SUBSTR / LENGTH
-- a 
SELECT first_name , last_name , 
    SUBSTRING(first_name , 1 , 1) + SUBSTRING(last_name , 1 , 3) + '@oracle.com'
FROM employees 
-- b 
SELECT   first_name , last_name , 
         SUBSTRING(first_name , 1 , 1) 
       + SUBSTRING(last_name , LEN(last_name)-2 , 3) + '@oracle.com'
FROM employees 
4.-- CONCAT
-- a
SELECT CONCAT (first_name , last_name)
FROM employees 
-- b 
SELECT CONCAT(first_name , CONCAT(last_name , hire_date))
FROM employees 
5. -- LENGTH
SELECT last_name 
FROM employees 
WHERE LEN(last_name) > 8 
6. -- REPLACE
-- a 
SELECT first_name , last_name , REPLACE(phone_number , '515', '815')
FROM employees 
-- b 
SELECT first_name , last_name , SUBSTRING(phone_number , 1 ,3) , '515', '815' +SUBSTRING(phone_number , 4, LEN(phone_number))
FROM employees 
7. -- ROUND / TRUNC 
SELECT first_name , salary , salary / 12 , ROUND(salary / 12 , 2) , FLOOR(salary / 12 * 100) / 100 
FROM employees 
8. -- DATEADD / DATEDIFF
SELECT first_name , hire_date , DATEADD(DAY, -10, hire_date) , DATEADD(MONTH, 1, hire_date) , DATEDIFF(DAY, hire_date, getdate()) 
FROM employees 
9. -- DATEDIFF
SELECT first_name , last_name , hire_Date , 
 DATEDIFF(MONTH, hire_date, getdate()) , 
 DATEDIFF(YEAR, hire_date, getdate()) 
FROM employees
10. -- DATEADD
SELECT first_name , hire_date ,  DATEADD(YEAR, 1, hire_date)
FROM employees 
11. -- ISNULL 
-- a 
SELECT first_name , last_name , salary , commission_pct , 
       ISNULL(commission_pct , 0)
FROM employees
-- b 
SELECT first_name , last_name , salary , commission_pct , ISNULL(CONVERT(VARCHAR, commission_pct) , 'No Commission')
FROM employees 
12. -- CASE
SELECT first_name , last_name , salary , 
       CASE WHEN salary BETWEEN 0 AND 5000 THEN 'A'
            WHEN salary BETWEEN 5001 AND 15000 THEN 'B'
            WHEN salary BETWEEN 15001 AND 20000 THEN 'C'
            ELSE 'D'
       END AS "SAL_RANKS"
FROM employees 
13. -- REPLICATE
SELECT first_name , salary , 
       REPLICATE('*' , LEN(CONVERT(VARCHAR, CAST(salary AS INT))))
FROM employees

פתרונות Oracle

1. -- LOWER / UPPER
SELECT LOWER(first_name) , UPPER(last_name) 
FROM employees 
WHERE employee_id BETWEEN 80 AND 150 
2. -- LOWER / UPPER
SELECT first_name , last_name 
FROM employees 
WHERE UPPER(last_name) = 'KING'
-- or 
SELECT first_name , last_name 
FROM employees 
WHERE LOWER(last_name) = 'king'
3. -- SUBSTR / LENGTH
-- a 
SELECT first_name , last_name , 
    SUBSTR(first_name , 1 , 1) || SUBSTR(last_name , 1 , 3) || '@oracle.com'
FROM employees 
-- b 
SELECT first_name , last_name , 
       SUBSTR(first_name , 1 , 1) 
       || SUBSTR(last_name , LENGTH(last_name)-2 , 3) || '@oracle.com'
FROM employees 
4.-- CONCAT
-- a
SELECT CONCAT (first_name , last_name)
FROM employees 
-- b 
SELECT CONCAT(first_name , CONCAT(last_name , hire_date))
FROM employees 
5. -- LENGTH
SELECT last_name 
FROM employees 
WHERE LENGTH(last_name) > 8 
6. -- REPLACE
-- a 
SELECT first_name , last_name , REPLACE(phone_number , '515', '815')
FROM employees 
-- b 
SELECT first_name , last_name , REPLACE(SUBSTR(phone_number , 1 ,3) , '515', '815') || SUBSTR(phone_number , 4)
FROM employees 
7. -- ROUND / TRUNC 
SELECT first_name , salary , salary / 12 , ROUND(salary / 12 , 2) , TRUNC(salary / 12 , 2) 
FROM employees 
8. -- ADD_MONTHS
SELECT first_name , hire_date , hire_date - 10 , ADD_MONTHS(hire_date , 1) , sysdate - hire_date 
FROM employees 
9. -- MONTHS_BETWEEN
SELECT first_name , last_name , hire_Date , 
MONTHS_BETWEEN(sysdate , hire_date ) , 
MONTHS_BETWEEN(sysdate , hire_date ) / 12 
FROM employees
10. -- ADD_MONTHS
SELECT first_name , hire_date ,  ADD_MONTHS(hire_date , 12)
FROM employees 
11. -- NVL / TO_CHAR
-- a 
SELECT first_name , last_name , salary , commission_pct , 
       NVL(commission_pct , 0)
FROM employees
-- b 
SELECT first_name , last_name , salary , commission_pct , NVL(TO_CHAR(commission_pct) , 'No Commission')
FROM employees 
12. -- CASE
SELECT first_name , last_name , salary , 
       CASE WHEN salary BETWEEN 0 AND 5000 THEN 'A'
            WHEN salary BETWEEN 5001 AND 15000 THEN 'B'
            WHEN salary BETWEEN 15001 AND 20000 THEN 'C'
            ELSE 'D'
       END AS "SAL_RANKS"
FROM employees 
13. -- LPAD / RPAD
SELECT first_name , salary , 
       LPAD('*' , LENGTH(TO_CHAR(salary)) , '*')
FROM employees