Ć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;
EMPNO ENAME SAL NOWA PENSJA
---------- ---------- ---------- -----------
7839 KING 5000 5500
7698 BLAKE 2850 3135
7782 CLARK 2450 2695
7566 JONES 2975 3273
7654 MARTIN 1250 1375
7499 ALLEN 1600 1760
7844 TURNER 1500 1650
7900 JAMES 950 1045
7521 WARD 1250 1375
7902 FORD 3000 3300
7369 SMITH 800 880
7788 SCOTT 3000 3300
7876 ADAMS 1100 1210
7934 MILLER 1300 1430
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;
EMPNO ENAME SAL NOWA_PENSJA PODWYZKA
---------- ---------- ---------- ----------- ----------
7839 KING 5000 5500 500
7698 BLAKE 2850 3135 285
7782 CLARK 2450 2695 245
7566 JONES 2975 3273 298
7654 MARTIN 1250 1375 125
7499 ALLEN 1600 1760 160
7844 TURNER 1500 1650 150
7900 JAMES 950 1045 95
7521 WARD 1250 1375 125
7902 FORD 3000 3300 300
7369 SMITH 800 880 80
7788 SCOTT 3000 3300 300
7876 ADAMS 1100 1210 110
7934 MILLER 1300 1430 130
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%';
EMPNO ENAME LENGTH(ENAME)
---------- ---------- -------------
7499 ALLEN 5
7369 SMITH 5
7788 SCOTT 5
7876 ADAMS 5
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 ENAME LIKE'%R%';
INITCAP(EN LOWER(JOB
---------- ---------
Clark manager
Martin salesman
Turner salesman
Ward salesman
Ford analyst
Miller clerk
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';
select * from emp where Substr(ename,-1,1)='D';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7902 FORD ANALYST 7566 81/12/03 3000 20
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;
ENAME MIESIACE
---------- ----------
ADAMS -883,18848
SCOTT -882,0917
MILLER -871,54332
JAMES -869,89816
FORD -869,89816
KING -869,34977
MARTIN -867,70461
TURNER -867,05945
CLARK -864,0917
BLAKE -862,83364
JONES -861,8659
WARD -860,51106
ALLEN -860,44654
SMITH -858,34977
SELECT NEXT_DAY(SYSDATE,'PIĄTEK') FROM DUAL;
NEXT_DAY
--------
09/06/12
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;
ENAME JOB TYGODNIE
---------- --------- ----------
BLAKE MANAGER -3751,6917
MARTIN SALESMAN -3773,1202
ALLEN SALESMAN -3741,6917
TURNER SALESMAN -3770,2631
JAMES CLERK -3782,5488
WARD SALESMAN -3741,9774
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;
EMPNO ENAME HIREDATE MIESIACE ADD_MONT NEXT_DAY
---------- ---------- -------- ---------- -------- --------
7839 KING 81/11/17 -869,34968 82/05/17 81/11/21
7698 BLAKE 81/05/01 -862,83355 81/11/01 81/05/02
7782 CLARK 81/06/09 -864,09162 81/12/09 81/06/13
7566 JONES 81/04/02 -861,86581 81/10/02 81/04/04
7654 MARTIN 81/09/28 -867,70452 82/03/28 81/10/03
7499 ALLEN 81/02/20 -860,44646 81/08/20 81/02/21
7844 TURNER 81/09/08 -867,05936 82/03/08 81/09/12
7900 JAMES 81/12/03 -869,89807 82/06/03 81/12/05
7521 WARD 81/02/22 -860,51097 81/08/22 81/02/28
7902 FORD 81/12/03 -869,89807 82/06/03 81/12/05
7369 SMITH 80/12/17 -858,34968 81/06/17 80/12/20
7788 SCOTT 82/12/09 -882,09162 83/06/09 82/12/11
7876 ADAMS 83/01/12 -883,18839 83/07/12 83/01/15
7934 MILLER 82/01/23 -871,54323 82/07/23 82/01/30
9. Wyświetl numer pracownika (EMPNO), nazwisko (ENAME), datę zatrudnienia (HIREDATE) oraz miesiąc rozpoczęcia pracy
SELECT EMPNO, ENAME, TO_CHAR(HIREDATE, 'D DD DAY MONTH YYYY') FROM EMP;
EMPNO ENAME TO_CHAR(HIREDATE,'DDDDAYMONTHYYYY'
---------- ---------- ----------------------------------
7839 KING 1 17 PONIEDZIAŁEK LISTOPAD 2081
7698 BLAKE 4 01 CZWARTEK MAJ 2081
7782 CLARK 1 09 PONIEDZIAŁEK CZERWIEC 2081
7566 JONES 3 02 ŚRODA KWIECIEŃ 2081
7654 MARTIN 7 28 NIEDZIELA WRZESIEŃ 2081
7499 ALLEN 4 20 CZWARTEK LUTY 2081
7844 TURNER 1 08 PONIEDZIAŁEK WRZESIEŃ 2081
7900 JAMES 3 03 ŚRODA GRUDZIEŃ 2081
7521 WARD 6 22 SOBOTA LUTY 2081
7902 FORD 3 03 ŚRODA GRUDZIEŃ 2081
7369 SMITH 2 17 WTOREK GRUDZIEŃ 2080
7788 SCOTT 3 09 ŚRODA GRUDZIEŃ 2082
7876 ADAMS 2 12 WTOREK STYCZEŃ 2083
7934 MILLER 5 23 PIĄTEK STYCZEŃ 2082
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;
EMPNO ENAME HIREDATE TO_
---------- ---------- -------- ---
7839 KING 81/11/17 321
7698 BLAKE 81/05/01 121
7782 CLARK 81/06/09 160
7566 JONES 81/04/02 092
7654 MARTIN 81/09/28 271
7499 ALLEN 81/02/20 051
7844 TURNER 81/09/08 251
7900 JAMES 81/12/03 337
7521 WARD 81/02/22 053
7902 FORD 81/12/03 337
7369 SMITH 80/12/17 352
7788 SCOTT 82/12/09 343
7876 ADAMS 83/01/12 012
7934 MILLER 82/01/23 023
SELECT EMPNO, ENAME, HIREDATE, TO_CHAR(HIREDATE, 'MONTH') FROM EMP;
EMPNO ENAME HIREDATE TO_CHAR(HIR
---------- ---------- -------- -----------
7839 KING 81/11/17 LISTOPAD
7698 BLAKE 81/05/01 MAJ
7782 CLARK 81/06/09 CZERWIEC
7566 JONES 81/04/02 KWIECIEŃ
7654 MARTIN 81/09/28 WRZESIEŃ
7499 ALLEN 81/02/20 LUTY
7844 TURNER 81/09/08 WRZESIEŃ
7900 JAMES 81/12/03 GRUDZIEŃ
7521 WARD 81/02/22 LUTY
7902 FORD 81/12/03 GRUDZIEŃ
7369 SMITH 80/12/17 GRUDZIEŃ
7788 SCOTT 82/12/09 GRUDZIEŃ
7876 ADAMS 83/01/12 STYCZEŃ
7934 MILLER 82/01/23 STYCZEŃ
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);
ENAME JOB SAL
---------- --------- ----------
MARTIN SALESMAN 1250
ALLEN SALESMAN 1600
JAMES CLERK 950
WARD SALESMAN 1250
SMITH CLERK 800
MILLER CLERK 1300
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);
ENAME JOB SAL
---------- --------- ----------
FORD ANALYST 3000
SCOTT ANALYST 3000
5