ĆWICZENIE 2
Cel pracy: Zapoznanie się w poleceniu SELECT z funkcjami jedno wierszowymi operującymi na datach,
liczbach i tekstach.
Funkcje znakowe:
LOWER - przekształca tekst literowy na „małe" litery;
UPPER - przekształca tekst literowy na „duże" litery;
INITCAP - przekształca tekst literowy na „duże" litery dla pierwszego znaku każdego wyrazu;
LENGTH - zwraca liczbę znaków w wyrażeniu; np. LENGTH('WSTE'); wynik: 4
SUBSTR (kolumna wyrażenie, m [,n]) -
zwraca określony fragment tekstu poczynając od pozycji m, zwracany tekst ma długość n znaków (jeśli m jest ujemne, to zliczanie odbywa się od końca. Jeśli nie ma n to zwracane są znaki do końca tekstu) np. SUBSTR('Witaj w WSTE',1,5); wynik: Witaj
INSTR (kolumna | wyrażenie, 'tekst', [,m ], [n]) - zwraca cyfrowo pozycję podanego tekstu. Opcjonalnie można podać pozycję startową przeszukiwana m oraz liczbę n określającą, które wystąpienie w ciągu cię interesuje, np. INSTR ('WitąjwWSTE','S'); wynik: 8
REPLACE (tekst, szukany ciąg znaków, nowy ciąg znaków) - przeszukuje tekst w celu odnalezienia poszukiwanego ciągu znaków, jeśli ciąg istnieje to zostanie zastąpiony nową podaną wartością.
Działania na datach oraz funkcje przetwarzania dat
SYSDATE - funkcja, która zwraca bieżącą datę i czas.
MONTHS_BETWEEN(datel, date2) - znajduje liczbę miesięcy pomiędzy dwoma datami
ADD_MONTHS(date, n) - dodaje n miesięcy kalendarzowych do daty
NEXT_DAY(date, 'char') - znajduje datę związaną z najbliższym dniem tygodnia, który jest określony w argumencie 'char' (tekst lub liczba); np. NEXT_DAY('01-SEP-95', 'piątek'); wynik: '08-SEP-95'
LAST_DAY(date) - znajduje datę ostatniego dnia miesiąca, który zawiera datę
ROUND(date[,'fmt']) - zwraca datę zaokrągloną do jednostki określonej w formacie fmt. Bez formatu data zostanie zaokrąglona do najbliższego dnia; np.
ROUND(SYSDATE, 'MONTH') dla SYSDATE = 23-JUL-98; wynik: O1-AUG-98
ROUND('25-JUL-95','YEAR') wynik 'O1-JAN-96'
TRUNC(date[,'fmt'l) - zwraca datę obciętą do jednostki określonej w fmt. Bez formatu data zostanie obcięta z pozostawieniem dnia; np. TRUNC(SYSDATE, 'MONTH') dla SYSDATE = 03-MAR-05; wynik: O1-MAR-05
TO_CHAR(number ] datę, [fmt]) konwertuje liczbę lub datę do wartości znakowej określonej w fmt. Użycie TO_CHAR w datach: TO_CHAR(sysdate, 'dd mm yyyy')
Do formatowania sposobu wyświetlania daty można używać:
D, DD, DDD, DY, DAY, MM, RM, MON, MONTH, W, WW. YY, YYYY, YEAR
użycie małych liter w elemencie formatu powoduje wypisanie wyniku małymi literami, dużych dużymi; fin przełącza między występowaniem lub nie dopełniania wyników odstępami. (fmDD Month YYYY)
Zadania do zapytań SQL:
1. Dla każdego pracownika wyświetl jego numer (EMPNO), nazwisko (ENAME) pensję (SAL) oraz pensję powiększoną o 10% i wyrażoną jako liczba całkowita. Nazwij kolumnę „Nowa pensja".
SELECT EMPNO, ENAME, SAL, ROUND((SAL+SAL*10/100),0) AS "NOWA PENSJA" FROM EMP;
2. Do poprzedniego polecenia dodaj kolumnę obliczającą wzrost pensji i nazwij ją „Podwyżka"
SELECT EMPNO, ENAME, SAL, ROUND((SAL+SAL*10/100),0) AS "NOWA_PENSJA", ROUND((SAL+SAL*10/100),0)-SAL AS "PODWYZKA" FROM EMP;
3. Wyświetl numer pracownika (EMPNO), nazwisko (ENAME) i długość nazwiska dla wszystkich pracowników, których nazwiska zaczynają się na „S" lub „A" Utwórz dwa rozwiązania (z OR oraz z IN).
SELECT EMPNO, ENAME, LENGTH(ENAME) FROM EMP WHERE ENAME LIKE 'A%' OR ENAME LIKE 'S%';
4. Wyświetl nazwiska (ENAME) i stanowiska (JOB) wszystkich pracowników, którzy mają literę r w środku nazwiska. Pierwsza litera nazwiska duża, pozostałe litery małe, stanowisko małymi literami.
SELECT INITCAP(ENAME), LOWER(JOB) FROM EMP WHERE INITCAP(ENAME) LIKE'%R%';
lub
SELECT INITCAP(ENAME), LOWER(JOB) FROM EMP WHERE INITCAP(ENAME) LIKE'%R%' AND INITCAP(ENAME) NOT LIKE'R%' AND INITCAP(ENAME) NOT LIKE'%R';
5. Wybierz pracowników których nazwisko zakończone jest literą„D" Utwórz dwa rozwiązania (z LIKE oraz z SUBSTR).
SELECT * FROM EMP WHERE ENAME LIKE'%D';
6. Dla wszystkich pracowników pokaż nazwiska (ENAME) i wylicz liczbę miesięcy pomiędzy dniem dzisiejszym a datą zatrudnienia. Nazwij kolumnę „Miesiące". Uporządkuj wyniki wg liczby miesięcy zatrudnienia.
SELECT ENAME, MONTHS_BETWEEN(SYSDATE,HIREDATE) MIESIACE FROM EMP ORDER BY MIESIACE ASC;
SELECT NEXT_DAY(SYSDATE,'PIĄTEK') FROM DUAL;
7. Wyświetl nazwiska (ENAME), stanowiska (JOB) i liczbę tygodni trwania zatrudnienia (jako „Tygodnie") dla pracowników z oddziału 30
SELECT ENAME, JOB, (SYSDATE - HIREDATE)/7 AS TYGODNIE FROM EMP WHERE DEPTNO = 30;
8. Wyświetl numer pracownika (EMPNO), nazwisko (ENAME), datę zatrudnienia (HIREDATE), liczbę miesięcy zatrudnienia, datę 6 miesięcy po zatrudnieniu, datę pierwszego piątku po zatrudnieniu dla wszystkich zatrudnionych przez mniej niż 36 miesięcy.
SELECT EMPNO, ENAME, HIREDATE, MONTHS_BETWEEN(SYSDATE,HIREDATE) AS MIESIACE, ADD_MONTHS(HIREDATE,6), NEXT_DAY(HIREDATE,'PIĄTEK') FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE) < 36;
9. Wyświetl numer pracownika (EMPNO), nazwisko (ENAME), datę zatrudnienia (HIREDATE) oraz miesiąc rozpoczęcia pracy
SELECT ENAME, TO_CHAR(HIREDATE, 'D DD DAY MONTH YYYY') FROM EMP;
10. Wyświetl nazwisko (ENAME) oraz datę zatrudnienia (HIREDATE) używając funkcji TO_CHAR oraz różnych kombinacji modeli formatu daty:
D, DD, DDD, DY, DAY, MM, RM, MON, MONTH, W, WW, YY, YYYY, YEAR
SELECT ENAME, TO_CHAR(HIREDATE, 'D DDD DAY MONTH MM YYYY RM W WW YEAR') FROM EMP;
SELECT EMPNO, ENAME, HIREDATE, TO_CHAR(HIREDATE, 'DDD') FROM EMP;
SELECT EMPNO, ENAME, HIREDATE, TO_CHAR(HIREDATE, 'MONTH') FROM EMP;
11. Wyświetl nazwiska (ENAME), stanowiska (JOB) i datę zatrudnienia (HIREDATE) pracowników zatrudnionych pomiędzy 9 czerwca 1981 a l stycznia 1982. Uporządkuj wyniki wg rosnącej daty zatrudnienia.
SELECT ENAME, JOB, HIREDATE FROM EMP WHERE HIREDATE > '81/06/09' AND HIREDATE < '82/01/01' ORDER BY HIREDATE ASC;
12. Wyświetl nazwiska (ENAME), stanowiska (JOB) i płacę (SAL) wszystkich pracowników, których stanowisko to SALESMAN lub CLERK i których płaca nie jest równa 1100 i 1500
SELECT ENAME, JOB, SAL FROM EMP WHERE (JOB = 'SALESMAN' OR JOB = 'CLERK') AND SAL NOT IN (1100,1500);
13. Wyświetl nazwiska (ENAME), stanowiska (JOB) i płacę (SAL) wszystkich pracowników, oprócz zatrudnionych na stanowisku SALESMAN i których płaca jest równa 3000 lub 1250
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB NOT IN ('SALESMAN') AND SAL IN (3000,1250);
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB NOT IN ('SALESMAN') AND SAL IN (3000,1250);
1