-- 1. SELECT
SELECT ename, sal FROM scott.emp;
SELECT * FROM scott.emp;
SELECT ename, sal, comm, sal+comm FROM scott.emp;
SELECT ename, sal, comm, NVL(comm,0), sal+comm, sal+NVL(comm,0) FROM scott.emp;
SELECT ename, sal AS pensja, comm, NVL(comm,0), sal+comm, sal+NVL(comm,0) wynagr FROM scott.emp;
SELECT job, deptno FROM scott.emp;
SELECT DISTINCT job, deptno FROM scott.emp;
SELECT DISTINCT job, deptno FROM scott.emp;
SELECT ename, sal, deptno FROM scott.emp ORDER BY sal DESC, ename;
-- 1. SELECT [DISTINCT|ALL] * | nazwa_kol_1 [[AS] alias1], [,...,nazwa_kol_N [[AS] aliasN] FROM nazwa_tabeli
-- [ORDER BY nazwa_kol_1 [ASC|DESC], ....];
-- 2. CREATE TABLE nazwa_tabeli AS SELECT .....;
CREATE TABLE emp AS SELECT * FROM scott.emp;
SELECT * FROM emp;
-- Typy danych: INT(n), CHAR(n), NUMBER(m,n), NUMBER(m) (NUMBER(5,2), NUMBER(5)), VARCHAR2(n), DATE
-- Modyfikatory ograniczeń NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT wartość
-- 2. CREATE TABLE nazwa_tabeli (
-- nazwa_kol_1 TYP_KOL_1 [modyfikatory],
-- nazwa_kol_2 TYP_KOL_2 [modyfikatory],
-- ....
-- nazwa_kol_N TYP_KOL_N [modyfikatory]
--);
-- 2. CREATE TABLE nazwa_tabeli (
-- nazwa_kol_1 TYP_KOL_1 NOT NULL | UNIQUE | PRIMARY KEY | DEFAULT wartość
-- nazwa_kol_2 TYP_KOL_2 NOT NULL | UNIQUE | PRIMARY KEY | DEFAULT wartość
-- ....
-- nazwa_kol_N TYP_KOL_N NOT NULL | UNIQUE | PRIMARY KEY | DEFAULT wartość
--);
-- 3. DROP TABLE nazwa_tabeli [PURGE];
CREATE TABLE dane_os (
pesel NUMBER(11) PRIMARY KEY,
nazwisko VARCHAR2(40) NOT NULL,
data_ur DATE,
plec CHAR(1) DEFAULT 'K',
stypendium NUMBER(6,2) UNIQUE
);
SELECT nazwisko, stypendium, 3.7+0.1 FROM dane_os;
-- 4. INSERT INTO nazwa_tabeli [(nazwa_kol_1, ..., nazwa_kol_N)] VALUES (wart_kol_1, ..., wart_kol_N);
INSERT INTO dane_os (NAZWISKO, PESEL, PLEC, STYPENDIUM) VALUES ('Lewandowska', 3, 'K', 299.99);
INSERT INTO dane_os (PESEL, NAZWISKO, data_ur, STYPENDIUM) VALUES (4, 'Piotrowska', '2000-01-01', 299.98);
INSERT INTO dane_os VALUES (5, 'Jankowski', '1992-12-31', 'M', 1.98);
CREATE TABLE dane_os2 AS SELECT pesel, nazwisko, data_ur FROM dane_os;
SELECT * FROM tab;
DROP TABLE dane_os2;
PURGE recyclebin;
-- 4. INSERT INTO nazwa_tabeli [(nazwa_kol_1, ..., nazwa_kol_N)] SELECT ... ;
-- wartości logiczne: true, false, null
-- klauzula WHERE
-- WHERE w-ek
SELECT * FROM emp WHERE deptno=10;
-- 4. klauzula WHERE
-- WHERE w-ek
SELECT * FROM emp
WHERE deptno=10
ORDER BY ename;
SELECT ename, sal, comm, deptno FROM emp
WHERE (NVL(comm,0) <> 300) AND (sal > 1300);
-- w-ek: nazwy kolumn, stale, funkcje (wbudowane, wlasne), procedury (wbudowane, wlasne), nawiasy
-- operatory:
-- arytmetyczne: + - / *
-- równości i porównania: = > < >= <= <> !=
-- logiczne: AND, OR, NOT
-- konkatenacji lancuchów: ||
-- inne występujace w wyrażeniach logicznych: IS NULL, IS NOT NULL, BETWEEN wyr1 AND wyr2, LIKE wzorzec
-- IN (lista), NOT IN (lista)
SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE NOT comm IS NULL;
SELECT * FROM emp WHERE comm IS NOT NULL;
SELECT * FROM emp WHERE comm IS NULL OR comm=0;
SELECT 'Pracownik ' || ename || ' zarabia '|| sal Raport FROM emp;
-- wzorzec, metaznaki: % _
SELECT * FROM emp WHERE ename LIKE 'SMITH';
SELECT * FROM emp WHERE ename='SMITH';
SELECT * FROM emp WHERE ename LIKE '%M%';
SELECT * FROM emp WHERE ename LIKE 'KOWALSK_';
SELECT * FROM emp WHERE ename='KOWALSKI' OR ename='KOWALSKA';
SELECT * FROM emp WHERE deptno IN (10,20);
SELECT * FROM emp WHERE deptno=10 OR deptno=20;
SELECT * FROM emp WHERE deptno NOT IN (10,20);
SELECT * FROM emp WHERE NOT deptno IN (10,20);
SELECT * FROM emp WHERE sal BETWEEN 1250 AND 3000 ORDER BY sal;
-- Zad 1.
-- a) Podaj dane pracowników z oddzialów 20 i 30
SELECT * FROM emp WHERE deptno IN (20,30);
-- b) Podaj dane pracowników zatrudnionych pomiędzy 1 maja 1981 a 4 grudnia 1981
SELECT * FROM emp WHERE hiredate BETWEEN '81/05/01' AND '81/12/04';
-- c) Podaj nazwiska, pensje i nr oddzialu pracowników, którzy zarabiaja więcej niż 2000 i nie sa zatrudnieni w oddziale 30.
SELECT ename, sal, deptno FROM emp WHERE sal > 2000 AND deptno != 30;
-- d) Podaj dane pracowników nie otrzymujacych premii (comm)
-- 1. SELECT [DISTINCT|ALL] * | nazwa_kol_1 [[AS] alias1], [,...,nazwa_kol_N [[AS] aliasN] FROM nazwa_tabeli
-- [WHERE w-ek]
-- [ORDER BY nazwa_kol_1 [ASC|DESC], ...];
SELECT COUNT(*) FROM emp;
SELECT sysdate FROM emp;
SELECT * FROM emp;
-- Funkcje grupowe: count(), count(*), max(), min(), avg(), sum()
-- GROUP BY nazwa_kol1[, ..., nazwa_kol_N] [HAVING w-ki na grupy]
SELECT job, COUNT(*) ilosc_prac, ROUND(avg(sal),2) sr_pensja, MIN(sal), MAX(sal), SUM(sal) FROM emp
GROUP BY job;
SELECT job, COUNT(*) ilosc_prac, ROUND(avg(sal),2) sr_pensja, MIN(sal), MAX(sal), SUM(sal) FROM emp
GROUP BY job HAVING (COUNT(*))>2 AND MIN(sal)<2000;
-- 1. SELECT [DISTINCT|ALL] * | nazwa_kol_1 [[AS] alias1], [,...,nazwa_kol_N [[AS] aliasN] FROM nazwa_tabeli
-- [WHERE w-ek]
-- [GROUP BY nazwa_kol1[, ..., nazwa_kol_N] [HAVING w-ki na grupy]]
-- [ORDER BY nazwa_kol_1 [ASC|DESC], ....];
SELECT job, round(avg(sal),2) FROM emp
GROUP BY job
HAVING job='CLERK';
SELECT job, round(avg(sal),2) FROM emp
WHERE job='CLERK'
GROUP BY job;
SELECT deptno, avg(sal) FROM emp
GROUP BY deptno;
SELECT * FROM emp ORDER BY deptno;
SELECT deptno, avg(sal) FROM emp
WHERE ename<>'KING'
GROUP BY deptno;
-- Zad. 2
-- a) Dla każdego oddzialu posiadajacego co najmniej 2 pracowników podaj jego nr, liczbę pracowników, średnia pensje
-- oraz rożnicę między pensja maksymalna a minimalna
SELECT deptno, COUNT(*) ilosc, avg(sal) srednia, MAX(sal)-MIN(sal) roznica
FROM emp GROUP BY deptno
HAVING COUNT(*)>=2;
SELECT deptno, COUNT(*) AS ilosc_prac, avg(sal) pensja, (MAX(sal)-MIN(sal)) roznica FROM emp GROUP BY deptno HAVING COUNT(*)>=2;
-- b) Podaj sumę wynagrodzeń (z uwzględnieniem premii) wg oddzialów
-- oddzial, suma_wynagrodzen
SELECT deptno, sal, NVL(comm,0), sal+NVL(comm,0) FROM emp ORDER BY deptno;
SELECT deptno, SUM(sal+NVL(comm,0)) FROM emp GROUP BY deptno;
-- b') Podaj sumę wynagrodzeń (z uwzględnieniem premii) wg stanowisk posortowana alfabetycznie
-- oddzial, suma_wynagrodzen
SELECT job, SUM(sal+NVL(comm,0)) FROM emp GROUP BY job ORDER BY job;
-- c) Dla każdego przelożonego podaj jego nr oraz liczbę jego podwladnych
SELECT COUNT(*), mgr FROM emp
WHERE mgr IS NOT NULL
GROUP BY mgr;
-- 5. DELETE FROM nazwa_tabeli
-- [WHERE w-ek];
DELETE FROM emp WHERE job='CLERK';
SELECT * FROM emp ORDER BY job;
DELETE FROM emp;
INSERT INTO emp SELECT * FROM scott.emp;
-- 6. UPDATE nazwa_tabeli SET nazwa_kol_1=wart_kol_1,[...,nazwa_kol_N=wart_kol_N]
-- [WHERE w-ek];
SELECT ename, sal, job FROM emp WHERE job='ANALYST';
UPDATE emp SET sal=sal/2, job='CLERK' WHERE job='ANALYST';
SELECT ename, sal, job FROM emp WHERE job='CLERK';
SELECT * FROM TAB;
DESC TAB;
CREATE TABLE dept AS SELECT * FROM scott.dept;
SELECT * FROM dept;
DESC dual;
SELECT * FROM dual;
SELECT sysdate FROM dual;
SELECT sysdate+1 FROM dual;
SELECT sysdate-1 FROM dual;
SELECT sysdate FROM dept;
SELECT 2+2*4 FROM dual;
SELECT '123 a b c' FROM dual;
-- TO_CHAR(data, lańcuch_formatujacy)
-- TO_DATE(lańcuch_daty, lańcuch_formatujacy)
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD DDD HH24:MI:SS') FROM dual;
SELECT empno, TO_CHAR(hiredate, 'YYYY') FROM emp;
SELECT * FROM dane_os;
SELECT TO_CHAR(data_ur, 'YYYY-MM-DD DDD HH24:MI:SS') FROM dane_os;
SELECT TO_DATE('1999 31 03', 'YYYY DD MM') FROM dual;
SELECT TO_CHAR(TO_DATE('1999 31 03', 'YYYY DD MM'), 'MM') FROM dual;
UPDATE dane_os SET data_ur=TO_DATE('1999 31 03', 'YYYY DD MM') WHERE pesel=3;
UPDATE dane_os SET data_ur='1999-03-31' WHERE pesel=3;
SELECT * FROM dane_os;
--Data:
-- DD - dzień miesiąca 1-31
-- MM - miesiąc 01-12
-- YYYY - rok 4 cyfrowo
-- DDD - dzień roku 1-366
-- Czas:
-- HH - godzina 1-12
-- HH24 - godzina 0-23
-- MI - minuty 0-59
-- SS - sekundy 0-59
-- DAY - nazwa dnia tygodnia
-- Arytmetyka dat
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(sysdate-1, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(sysdate+1, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(sysdate+1/24, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(sysdate+1/24/60, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(sysdate+1/24/60/60, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT (sysdate+1)-sysdate FROM dual;
SELECT TO_CHAR(TO_DATE('2020-09-20', 'YYYY-MM-DD'), 'DDD')-TO_CHAR(TO_DATE('2020-03-10', 'YYYY-MM-DD'), 'DDD') FROM dual;
SELECT sysdate - TO_DATE('1999 31 03', 'YYYY DD MM') FROM dual;
-- MONTHS_BETWEEN(data1, data2)
SELECT MONTHS_BETWEEN(sysdate, TO_DATE('1999 31 03', 'YYYY DD MM')) FROM dual;
SELECT ROUND(MONTHS_BETWEEN(sysdate, TO_DATE('1999 31 03', 'YYYY DD MM'))) FROM dual;
SELECT ROUND(MONTHS_BETWEEN(TO_DATE('1999 31 03', 'YYYY DD MM'), sysdate)) FROM dual;
-- LAST_DAY(data)
SELECT LAST_DAY(sysdate) FROM dual;
SELECT LAST_DAY('2020-01-01') FROM dual;
SELECT LAST_DAY('2020-02-01') FROM dual;
SELECT TO_CHAR(LAST_DAY('2020-02-01'), 'DAY') FROM dual;
-- NEXT_DAY(data, nazwa_dnia_tygodnia)
SELECT NEXT_DAY(sysdate, 'SOBOTA') FROM dual;
SELECT NEXT_DAY(sysdate, 'PONIEDZIAŁEK') FROM dual;
SELECT NEXT_DAY(LAST_DAY('2020-11-01'), 'PIĄTEK') FROM dual;
SELECT NEXT_DAY(LAST_DAY('2020-04-01'), 'PIĄTEK') FROM dual;
-- ADD_MONTHS(data, il_mcy)
SELECT sysdate, ADD_MONTHS(sysdate, 2) FROM dual;
SELECT '2020-02-29', ADD_MONTHS('2020-02-29', 1) FROM dual;
-- EXTRACT
-- EXTRACT (DAY/MONTH/YEAR FROM data)
SELECT EXTRACT(YEAR FROM sysdate) FROM dual;
SELECT EXTRACT(MONTH FROM sysdate) FROM dual;
SELECT EXTRACT(DAY FROM sysdate) FROM dual;
-- zaokraglenie do daty
SELECT ROUND(sysdate) FROM dual;
SELECT ROUND(sysdate, 'MM') FROM dual;
SELECT ROUND(sysdate, 'YYYY') FROM dual;
-- Zad.1
-- a) Wylicz ile dni pozostalo do Świat BN
SELECT ABS(ROUND(sysdate - TO_DATE('2020-12-25', 'yyyy-mm-dd'))) FROM dual;
SELECT ROUND(TO_DATE('2020-12-25','YYYY-MM-DD')-sysdate) FROM dual;
-- b) Wylicz ile godzin żyjesz
SELECT ROUND((sysdate - TO_DATE('2000-02-24', 'YYYY-MM-DD'))*24) FROM dual;
SELECT (sysdate - TO_DATE('1999-03-25', 'YYYY-MM-DD'))*24 FROM dual;
-- c) Zalóżmy, że szkolenia BHP odbywaja się w każdy pierwszy poniedzialek miesiaca po dacie zatrudnienia. Podaj dla każdego pracownika
-- datę jego szkolenia BHP.
SELECT ename, hiredate, NEXT_DAY(hiredate, 'PONIEDZIAŁEK') FROM emp;
SELECT ename, hiredate, NEXT_DAY(LAST_DAY(hiredate), 'PONIEDZIAŁEK') FROM emp;
SQL
-- Operacje teorio-mnogościowe: suma (UNION), przekrój (INTERSECT), różnica (MINUS)
SELECT * FROM emp;
SELECT * FROM emp WHERE deptno=10;
SELECT * FROM emp WHERE deptno=20;
SELECT * FROM emp WHERE deptno=10
UNION
SELECT * FROM emp WHERE deptno=20;
-- SELECT * FROM emp WHERE deptno=10 OR deptno=20;
-- SELECT * FROM emp WHERE deptno IN (10,20);
SELECT ename, sal FROM emp WHERE sal >1500
INTERSECT
SELECT ename, sal FROM emp WHERE sal <3000;
SELECT ename, sal, job FROM emp WHERE sal >1500
MINUS
SELECT ename, sal, job FROM emp WHERE job='MANAGER';
-- Egzaminy(nr-indeksu, kod-p, ocena)
-- Stypendia(nr-indeksu, kwota)
-- [2; 4) 0 zl
-- [4; 4,5] 300 zl
-- (4,5; 5] 500 zl
-- jedno polecenie! wstaw do tabeli Stypendia kazdemu studentowi jego kwote stypendium (pomijajac tych co otrzymaja 0)
-- PODZAPYTANIA (SELECTy użyte wewnatrz innych istrukcji SQL)
-- 1. w klauzuli WHERE
-- CREATE TABLE dept AS SELECT * FROM scott.dept;
SELECT deptno FROM dept WHERE loc='NEW YORK';
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE loc='NEW YORK');
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc='NEW YORK');
SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE loc='NEW YORK');
-- a) wyrażenie [NOT] IN (SELECT ....)
-- b) wyrażenie operator_porównania [ANY|ALL] (SELECT ...)
SELECT * FROM emp WHERE sal>(SELECT avg(sal) FROM emp);
SELECT * FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE JOB='MANAGER');
SELECT * FROM emp WHERE deptno=ANY(SELECT deptno FROM dept WHERE loc='NEW YORK');
-- =ANY <=> IN
-- <>ALL <=> NOT IN <=> !=ALL
-- c) [NOT] EXISTS (SELECT ...)
-- Podzapytania skorelowane
-- Podaj dane oddzialów, w których nikt nie pracuje
SELECT * FROM dept WHERE NOT EXISTS(SELECT * FROM emp WHERE deptno=dept.deptno);
SELECT * FROM dept WHERE NOT EXISTS(SELECT * FROM emp WHERE emp.deptno=dept.deptno);
SELECT * FROM dept D WHERE NOT EXISTS(SELECT * FROM emp E WHERE E.deptno=D.deptno);
SELECT * FROM emp E1 WHERE sal>ANY(SELECT sal FROM emp E2 WHERE E1.deptno=E2.deptno);
-- Zad. 1
-- a) Podaj dane pracowników, którzy sa przelożonymi (kogokolwiek)
SELECT * FROM emp WHERE EMPNO=ANY(SELECT MGR FROM emp);
SELECT * FROM emp WHERE empno IN (SELECT mgr FROM emp);
-- b) Podaj nazwisko i pensje pracownikow, którzy zarabiaja więcej niż średnia pensja pracowników w ICH oddziale.
SELECT ename, sal FROM emp e1 WHERE sal > (SELECT avg(sal) FROM emp e2 WHERE e1.deptno = e2.deptno);
-- c) Podaj dane pracowników z oddzialu 20, którzy zarabiaja więcej niż choć jeden pracownik z oddzialu 30.
SELECT * FROM emp WHERE deptno=20 AND sal>ANY(SELECT sal FROM emp WHERE deptno=30);
SELECT * FROM emp WHERE deptno=20 AND sal>ANY(SELECT DISTINCT sal FROM emp WHERE deptno=30);
SELECT * FROM emp WHERE deptno=20 AND sal>ANY(SELECT sal FROM emp WHERE deptno=30 GROUP BY sal);
-- Podzapytania cd.
SELECT ... FROM ...
WHERE ... (SELECT ...) AND (SELECT ...) OR (SELECT ...)...;
SELECT ... FROM ...
WHERE ... (SELECT ... FROM ... WHERE ...(SELECT ...)...) AND (SELECT ...) OR (SELECT ...)...;
-- 2. Inne miejsca wystepowania podzapytań
INSERT INTO nazwa_tabeli SELECT ...;
CREATE TABLE nazwa_tabeli [(nazw_kol_1,...,nazwa_kol_n)] AS SELECT ...;
-- UPDATE nazwa_tabeli SET nazwa_kol_1=wart_kol_1,[...,nazwa_kol_N=wart_kol_N]
-- [WHERE w-ek];
UPDATE nazwa_tabeli SET nazwa_kol_1=(SELECT ...)[,...]
[WHERE w-ek];
-- WHERE (ko1, ko2, kol3) IN (SELECT a,b,c FROM ...)
UPDATE emp SET sal=(SELECT avg(sal) FROM emp WHERE deptno=20) WHERE deptno=10;
SELECT ...,(SELECT ... FROM nazwa_tabeli_w ...), ... FROM nazwa_tabeli WHERE ....;
SELECT ename, deptno, sal, (SELECT round(avg(sal)) FROM emp E2 WHERE deptno=E1.deptno) sr_pensja, sal-(SELECT round(avg(sal)) FROM emp E2 WHERE deptno=E1.deptno) roznica FROM emp E1;
SELECT ... FROM (SELECT ... FROM nazwa_tabeli ...) alias_tabeli;
SELECT * FROM emp;
SELECT empno, ename, sal, TO_CHAR(hiredate, 'YYYY') rok_zatr FROM emp;
SELECT rok_zatr, SUM(sal) FROM (SELECT empno, ename, sal, TO_CHAR(hiredate, 'YYYY') rok_zatr FROM emp) emp_n
GROUP BY rok_zatr ORDER BY rok_zatr;
SELECT TO_CHAR(hiredate, 'YYYY') rok_zatr, SUM(sal) rok_zatr FROM emp GROUP BY TO_CHAR(hiredate, 'YYYY') ORDER BY TO_CHAR(hiredate, 'YYYY');
SELECT deptno, COUNT(*) liczba, MIN(sal) minsal, MAX(sal) makssal, MAX(sal)-MIN(sal) r, round(avg(sal)) srednia FROM emp
GROUP BY deptno; -- TABELA T
SELECT ename, (SELECT srednia FROM (SELECT deptno, COUNT(*) liczba, MIN(sal) minsal, MAX(sal) makssal, MAX(sal)-MIN(sal) r, round(avg(sal)) srednia FROM emp
GROUP BY deptno) T WHERE emp.deptno=T.deptno) srednia FROM emp;
SELECT deptno, makssal-minsal rozstep FROM (SELECT deptno, MIN(sal) minsal, MAX(sal) makssal FROM emp
GROUP BY deptno) T;
UPDATE nazwa_tabeli SET .... WHERE ... (SELECT ... ) ...;
DELETE FROM nazwa_tabeli WHERE ...(SELECT ...)...;
DELETE FROM studenci S WHERE EXISTS (SELECT * FROM uwagi U WHERE U.nr_indeksu=S.nr_indeksu);
UPDATE oceny O SET ocena=ocena-1 WHERE EXISTS (SELECT * FROM uwagi U WHERE U.nr_indeksu=O.nr_indeksu);
-- Zad. 1.
-- a) Pracownicy z dzialu 10 otrzymuja średnia pensję sprzedawcy. Zmodyfikuj tabelę.
-- b) Każdy pracownik otrzymuje średnia pensję swojego dzialu. Zmodyfikuj tabelę.
-- Wybory z wielu tabel
SELECT ename, deptno, (SELECT loc FROM dept WHERE deptno=emp.deptno) FROM emp;
-- Zlaczenia (JOINs)
-- Iloczyny kartezjańskie (CROSS JOIN)
-- tabela A: A1xA2xA3x...xAn tabela B: B1xB2x...xBm
-- tabela AxB: A1xA2xA3x...xAxB1xB2x...xBm
SELECT * FROM emp CROSS JOIN dept;
SELECT * FROM emp CROSS JOIN dept WHERE emp.deptno=dept.deptno;
SELECT ename, emp.deptno, loc FROM emp CROSS JOIN dept WHERE emp.deptno=dept.deptno;
-- zlaczenia wewnętrzne/zewnętrzne
-- zl. wewnętrzne: równościowe (equijoins), naturalne (NATURAL), inne
-- SELECT ... FROM tab1 JOIN tab2 ON w-ek zlaczenia [WHERE ...] ...
SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno;
SELECT * FROM dept JOIN emp ON emp.deptno=dept.deptno;
SELECT * FROM emp JOIN dept ON 1=1;
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL JOIN dept WHERE deptno=10;
SELECT loc, COUNT(*) il_prac FROM emp JOIN dept ON emp.deptno=dept.deptno GROUP BY loc ORDER BY loc;
-- tab_1 JOIN tab2 ON w-ek-tab_1-tab2 JOIN tab_3 ON w-ek-tab_1-tab2-tab_3
CREATE TABLE salgrade AS SELECT * FROM scott.salgrade;
-- I
SELECT * FROM emp E JOIN dept D ON E.deptno=D.deptno JOIN salgrade S ON E.sal BETWEEN S.losal AND S.hisal;
SELECT * FROM emp E, dept D, salgrade S WHERE E.deptno=D.deptno AND E.sal BETWEEN S.losal AND S.hisal;
-- II
SELECT * FROM emp E JOIN dept D ON E.deptno=D.deptno JOIN salgrade S ON E.sal BETWEEN S.losal AND S.hisal
WHERE E.job='MANAGER';
SELECT * FROM emp E, dept D, salgrade S WHERE E.deptno=D.deptno AND E.sal BETWEEN S.losal AND S.hisal AND
E.job='MANAGER';
-- pracownicy z Nowego Jorku
SELECT * FROM emp WHERE (SELECT loc FROM dept WHERE dept.deptno=emp.deptno)='NEW YORK';
SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno WHERE loc='NEW YORK';
- Zlaczenia zewnętrzne (OUTER) lewostronne (LEFT), prawostronne (RIGHT), obustronne (FULL)
-- tab_1 [LEFT|RIGHT|FULL] [OUTER] JOIN tab_2 ON w-ek
SELECT * FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno;
SELECT * FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno;
SELECT * FROM emp FULL JOIN dept ON emp.deptno=dept.deptno;
SELECT * FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno WHERE empno IS NULL;
SELECT dept.deptno, dname, loc FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno WHERE empno IS NULL;
SELECT * FROM dept D, emp E WHERE E.deptno(+)=D.deptno; -- tylko Oracle! zl. lewostronne
SELECT * FROM dept D, emp E WHERE E.deptno=D.deptno(+); -- tylko Oracle! zl. prawostronne
-- SELECT ... FROM tab1 JOIN (SELECT ...) T ON w-ek
CREATE TABLE trenerzy(
id_trenera NUMBER(4) PRIMARY KEY,
imie VARCHAR2(15),
nazwisko VARCHAR2(40)
);
CREATE TABLE druzyny(
nr NUMBER(6) PRIMARY KEY,
nazwa VARCHAR2(60),
miasto VARCHAR2(30),
id_trenera NUMBER(4)
);
CREATE TABLE mecze(
nr_gosp NUMBER(6),
nr_gosc NUMBER(6),
DATA DATE,
wynik_gosp NUMBER(2),
wynik_gosc NUMBER(2),
CONSTRAINT mecze_pk PRIMARY KEY (nr_gosp, nr_gosc)
);
zadanie domowe:
Tabela Drużyny zawiera dane drużyn piłkarskich. Tabela Trenerzy zawiera dane
trenerów drużyn. Tabela Mecze zawiera dane wszystkich rozegranych jak dotąd
meczy w bieżącym sezonie (w szczególności zawiera ona wyniki każdej z drużyn w
każdym z rozegranych meczy). Zakładamy, że w sezonie żadna z drużyn nie może
grać więcej niż 1 raz u tego samego gospodarza.
Podaj instrukcje SQL realizujące następujące zadania:
a) Utworzenie tabeli Mecze (utwórz również klucz główny)
b) Wylistowanie dat i miejsc meczy, w których przegrała drużyna gospodarzy
c) Wylistowanie dla każdej drużyny jej nazwy oraz liczby rozegranych przez nią meczy.
Lista drużyn w porządku malejącym wg tej wielkości.
d) Wyliczenie ile łącznie zostało strzelonych bramek
e) Wylistowanie danych drużyn, które nie rozegrały żadnego meczu
f) Wylistowanie danych drużyn, które rozegrały przynajmniej 2 mecze w sezonie
g) Zmodyfikowanie tabeli Drużyny tak, aby do nazwy drużyny dołączona została przy
użyciu znaku podkreślenia nazwa jej miasta.
(operator konkatenacji łańcuchów: ||)
h) Wylistowanie nazwiska trenera, którego drużyna nie przegrała żadnego meczu.
CREATE TABLE trenerzy(
id_trenera NUMBER(4) PRIMARY KEY,
imie VARCHAR2(15),
nazwisko VARCHAR2(40)
);
CREATE TABLE druzyny(
nr NUMBER(6) PRIMARY KEY,
nazwa VARCHAR2(60),
miasto VARCHAR2(30),
id_trenera NUMBER(4)
);
CREATE TABLE mecze(
nr_gosp NUMBER(6),
nr_gosc NUMBER(6),
DATA DATE,
wynik_gosp NUMBER(2),
wynik_gosc NUMBER(2),
CONSTRAINT mecze_pk PRIMARY KEY (nr_gosp, nr_gosc)
);
-- Tabela Drużyny zawiera dane drużyn piłkarskich. Tabela Trenerzy zawiera dane
-- trenerów drużyn. Tabela Mecze zawiera dane wszystkich rozegranych jak dotąd
-- meczy w bieżącym sezonie (w szczególności zawiera ona wyniki każdej z drużyn w
-- każdym z rozegranych meczy). Zakładamy, że w sezonie żadna z drużyn nie może
-- grać więcej niż 1 raz u tego samego gospodarza.
-- Podaj instrukcje SQL realizujące następujące zadania:
-- a) Utworzenie tabeli Mecze (utwórz również klucz główny)
-- b) Wylistowanie dat i miejsc meczy, w których przegrała drużyna gospodarzy
SELECT DATA, miasto FROM mecze JOIN druzyny ON NR_GOSP = NR WHERE wynik_gosp < wynik_gosc;
SELECT DATA, (SELECT miasto FROM druzyny WHERE nr=mecze.nr_gosp) miasto FROM mecze WHERE wynik_gosp < wynik_gosc;
-- c) Wylistowanie dla każdej drużyny jej nazwy oraz liczby rozegranych przez nią meczy.
-- Lista drużyn w porządku malejącym wg tej wielkości.
SELECT nazwa, (SELECT COUNT(*) FROM mecze WHERE dr.nr = nr_gosp OR dr.nr = nr_gosc) AS ilosc_meczy
FROM druzyny dr ORDER BY ilosc_meczy DESC;
-- d) Wyliczenie ile łącznie zostało strzelonych bramek
SELECT SUM(wynik_gosp)+SUM(wynik_gosc) FROM mecze;
SELECT SUM(wynik_gosp+wynik_gosc) FROM mecze;
-- e) Wylistowanie danych drużyn, które nie rozegrały żadnego meczu
SELECT nr, nazwa, miasto, imie, nazwisko FROM druzyny JOIN trenerzy ON druzyny.id_trenera = trenerzy.id_trenera
WHERE nr NOT IN (SELECT nr_gosp FROM mecze) AND nr NOT IN (SELECT nr_gosc FROM mecze);
SELECT nr, nazwa, miasto FROM druzyny
WHERE nr NOT IN (SELECT nr_gosp FROM mecze) AND nr NOT IN (SELECT nr_gosc FROM mecze);
SELECT nr, nazwa, miasto FROM druzyny
WHERE nr NOT IN
(SELECT nr_gosp FROM mecze
UNION
SELECT nr_gosc FROM mecze);
-- f) Wylistowanie danych drużyn, które rozegrały przynajmniej 2 mecze w sezonie
SELECT nr, nazwa, miasto, id_trenera FROM (SELECT nr, nazwa, miasto, id_trenera, (SELECT COUNT(*) FROM mecze WHERE dr.nr = nr_gosp OR dr.nr = nr_gosc) AS ilosc_meczy
FROM druzyny dr) T WHERE ilosc_meczy>=2;
-- SELECT nr, nazwa, miasto, id_trenera, count(*) FROM druzyny D JOIN mecze M ON D.nr=M.nr_gosp OR D.nr=M.nr_gosc
-- GROUP BY nr, nazwa, miasto, id_trenera;
SELECT nr, nazwa, miasto, id_trenera FROM druzyny D JOIN mecze M ON D.nr=M.nr_gosp OR D.nr=M.nr_gosc
GROUP BY nr, nazwa, miasto, id_trenera HAVING COUNT(*)>=2;
-- g) Zmodyfikowanie tabeli Drużyny tak, aby do nazwy drużyny dołączona została przy
-- użyciu znaku podkreślenia nazwa jej miasta.
-- (operator konkatenacji łańcuchów: ||)
UPDATE druzyny SET nazwa = nazwa || miasto;
-- h) Wylistowanie nazwiska trenera, którego drużyna nie przegrała żadnego meczu.
SELECT (SELECT imie FROM trenerzy WHERE id_trenera=druzyny.id_trenera), (SELECT nazwisko FROM trenerzy WHERE id_trenera=druzyny.id_trenera) FROM druzyny WHERE nr NOT IN (
SELECT nr_gosp FROM mecze WHERE wynik_gosp<wynik_gosc
UNION
SELECT nr_gosc FROM mecze WHERE wynik_gosc<wynik_gosp);
SELECT imie, nazwisko FROM trenerzy JOIN druzyny dr ON trenerzy.id_trenera = dr.id_trenera
WHERE (SELECT COUNT(*) FROM mecze WHERE (dr.nr = nr_gosp AND wynik_gosp<wynik_gosc)
OR (dr.nr = nr_gosc AND wynik_gosp>wynik_gosc))=0;
SELECT imie, nazwisko FROM trenerzy JOIN druzyny dr ON trenerzy.id_trenera = dr.id_trenera WHERE nr NOT IN
(SELECT nr_gosp FROM mecze WHERE wynik_gosp<wynik_gosc
UNION
SELECT nr_gosc FROM mecze WHERE wynik_gosc<wynik_gosp);
CREATE TABLE nazwa_tabeli AS SELECT [DISTINCT] (nazwa_kolumny,...,nazwa_kolumny)
FROM nazwa_tabeli;
CREATE TABLE nazwa_tabeli AS SELECT * FROM nazwa_tabeli;
CREATE TABLE nazwa_tabeli (
nazwa_kolumny TYP [PRIMARY KEY] [NOT NULL] [UNIQUE],
...
nazwa_kolumny TYP,
CONSTRAINT nazwa FOREIGN KEY (nazwa_kol_lok, ..., nazwa_kol_lok) REFERENCES nazwa_tabeli_obcej
[(nazwa_kolumny_obcej,..., nazwa_kolumny_obcej)] [ON DELETE CASCADE|SET NULL]
);
SELECT * FROM emp;
CREATE TABLE zawody (
nr NUMBER(2),
opis VARCHAR2(30),
CONSTRAINT zawody_pk PRIMARY KEY (nr)
);
CREATE TABLE dane_os2 (
nr NUMBER(2),
imie VARCHAR2(15),
nazwisko VARCHAR2(30),
nr_zawodu NUMBER(2),
CONSTRAINT dane_os_pk PRIMARY KEY (nr),
CONSTRAINT dane_os_fk FOREIGN KEY (nr_zawodu) REFERENCES zawody (nr) -- ON DELETE CASCADE
);
-- lub inaczej:
CREATE TABLE dane_os2 (
nr NUMBER(2),
imie VARCHAR2(15),
nazwisko VARCHAR2(30),
nr_zawodu NUMBER(2) REFERENCES zawody (nr) -- ON DELETE CASCADE,
CONSTRAINT dane_os_pk PRIMARY KEY (nr)
);
INSERT INTO dane_os2 VALUES (1, 'Jan', 'Kowalski', 10);
INSERT INTO zawody VALUES (10, 'nauczyciel');
SELECT * FROM zawody;
INSERT INTO dane_os2 VALUES (1, 'Jan', 'Kowalski', 10);
DELETE FROM zawody WHERE nr=10;
DROP TABLE dane_os2;
CREATE TABLE dane_os2 (
nr NUMBER(2),
imie VARCHAR2(15),
nazwisko VARCHAR2(30),
nr_zawodu NUMBER(2),
CONSTRAINT dane_os_pk PRIMARY KEY (nr),
CONSTRAINT dane_os_fk FOREIGN KEY (nr_zawodu) REFERENCES zawody (nr) ON DELETE CASCADE
);
INSERT INTO dane_os2 VALUES (1, 'Jan', 'Kowalski', 10);
SELECT * FROM dane_os2;
DELETE FROM zawody WHERE nr=10;
SELECT * FROM zawody;
SELECT * FROM dane_os2;
SELECT deptno, job, COUNT(*), SUM(sal) FROM emp
GROUP BY (deptno, job);
-- ROLLUP
SELECT deptno, job, COUNT(*), SUM(sal) FROM emp
GROUP BY ROLLUP(deptno, job);
-- CUBE
SELECT deptno, job, COUNT(*), SUM(sal) FROM emp
GROUP BY CUBE (deptno, job);
SELECT * FROM emp;
-- PIVOT
SELECT * FROM (SELECT empno, job FROM emp)
PIVOT (COUNT(empno) FOR job IN ('SALESMAN', 'CLERK', 'PRESIDENT')
);
-- bez PIVOTa
SELECT job, COUNT(empno) FROM emp
GROUP BY job
HAVING job IN ('SALESMAN', 'CLERK', 'PRESIDENT');
SELECT * FROM (SELECT deptno, empno, job FROM emp)
PIVOT (COUNT(empno) FOR job IN ('SALESMAN', 'CLERK', 'PRESIDENT')
);
SELECT * FROM (SELECT job, sal FROM emp )
PIVOT (SUM(sal) AS suma
FOR job IN ('MANAGER' pensja_managerow, 'ANALYST' pensja_analitykow));
SELECT * FROM (SELECT deptno, job, sal FROM emp )
PIVOT (SUM(sal) AS suma
FOR job IN ('MANAGER' pensja_managerow, 'ANALYST' pensja_analitykow));
SELECT * FROM (SELECT job, deptno, sal FROM emp )
PIVOT (SUM(sal) AS suma
FOR (job, deptno) IN (('MANAGER', 10) pensja_managerow_10, ('MANAGER', 20) pensja_managerow_20));
-- WITH ...
SELECT rok_zatr, SUM(sal) FROM (SELECT empno, ename, sal, TO_CHAR(hiredate, 'YYYY') rok_zatr FROM emp) emp_n
GROUP BY rok_zatr ORDER BY rok_zatr;
WITH emp_n AS (SELECT empno, ename, sal, TO_CHAR(hiredate, 'YYYY') rok_zatr FROM emp)
SELECT rok_zatr, SUM(sal) FROM emp_n
GROUP BY rok_zatr ORDER BY rok_zatr;
SELECT deptno, SUM(sal) suma, round(avg(sal),2) srednia, MIN(sal) minimum, MAX(sal) maksimum FROM emp
GROUP BY deptno;
SELECT ename, sal, srednia FROM emp E JOIN (SELECT deptno, SUM(sal) suma, round(avg(sal),2) srednia, MIN(sal) minimum, MAX(sal) maksimum FROM emp
GROUP BY deptno) S ON E.deptno=S.deptno;
WITH statystyki AS (SELECT deptno, SUM(sal) suma, round(avg(sal),2) srednia, MIN(sal) minimum, MAX(sal) maksimum FROM emp
GROUP BY deptno)
SELECT ename, sal, srednia FROM emp E JOIN statystyki S ON E.deptno=S.deptno;
SELECT nr, nazwa, miasto, id_trenera FROM (SELECT nr, nazwa, miasto, id_trenera, (SELECT COUNT(*) FROM mecze WHERE dr.nr = nr_gosp OR dr.nr = nr_gosc) AS ilosc_meczy
FROM druzyny dr) T WHERE ilosc_meczy>=2;
WITH T AS
(SELECT nr, nazwa, miasto, id_trenera, (SELECT COUNT(*) FROM mecze WHERE dr.nr = nr_gosp OR dr.nr = nr_gosc) AS ilosc_meczy
FROM druzyny dr)
SELECT nr, nazwa, miasto, id_trenera FROM T
WHERE ilosc_meczy>=2;
-- rekurencja
SELECT * FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
SELECT * FROM emp
START WITH empno=7934
CONNECT BY empno = prior mgr;
WITH
rek(empno, mgr, ename) AS (
SELECT empno, mgr, ename
FROM emp
WHERE empno = 7934
UNION ALL
SELECT e.empno, e.mgr, e.ename
FROM emp e JOIN rek ON e.empno = rek.mgr
)
SELECT *
FROM rek;
-- WHERE mgr IS NULL;
-- zagadnienia przykładowego zadania kolokwium
-- HOTEL:
-- pokoje (nr_pok, ile_osob, rodzaj)
-- goscie (nr_rezerwacji, nr_pok, pesel, imie, nazwisko)
-- rezerwacja (nr_rezerwacji, nr_pok, od, do)
-- ceny (rodzaj, cena_podst, cena_wakacyjna)
-- Polecenia:
-- 1) Z tabeli Goscie znajdź te osoby, których nazwisko zaczyna się literami ‘No’. Wyświetl ich pesele oraz połączenie napisów: nazwisko pisane wielkimi literami ‘ ‘(spacja) imie.
-- 2) Wyświetl numery pokoi i ich pojemność dla tego rodzaju, którego cena_wakacyjna jest najwyższa
-- 3) Dla każdego pokoju wyświetl jego numer, cenę podstawową dla jego rodzaju, oraz nazwiska gości do tego pokoju przypisanych. Jeżeli pokój nie jest przypisany do żadnego gościa, nadal ma się pojawić na liście, a w miejscu na nazwisko ma pojawić się null
-- 4) Dla wszystkich rezerwacji z datą ‘od’ po 1.06.2020 policz jednym zapytaniem ilość różnych peseli dla następujących 3 poziomów grupowań: każdej nr_rezerwacji i nr_pokoju; dla każdego nr_rezerwacji; łączną ilość różnych peseli
-- 5) Znajdź te numery rezerwacji, dla których jest podany jest numer pokoju, ale w tabeli gości nie ma pasującego wpisu (porównaj nr_rezerwacji oraz nr_pokoju)
-- 6) Wyświetl ile jest rodzajów pokojów, dla których cena_wakacyjna jest niewypełniona
-- 7) Dla każdego pokoju 4 osobowego wyświetl jego rodzaj i ilość różnych wpisów w kolumnie ile_osob dla wszystkich pokojów o tym samym rodzaju
