Powtrzenie wykadu 4 SQL Zoone zapytania SBD L

Powtórzenie wykładu 4 SQL - Złożone zapytania SBD, L. Banachowski 1

Operatory algebraiczne instrukcja_SELECT operator instrukcja_SELECT • UNION, UNION ALL - sumowanie (odpowiednio z eliminacją bądź nie - powtórzeń wierszy), • INTERSECT - przecięcie, • EXCEPT, MINUS - różnica. SBD, L. Banachowski 2

Zastosowanie (instrukcja warunkowa) • Wypisać nazwiska pracowników, zamieszczając przy pracownikach działu numer 10 gwiazdkę: ENAME||'*' SELECT Ename||'*' FROM Emp WHERE Deptno= 10 UNION SELECT Ename FROM Emp WHERE Deptno <> 10 ORDER BY 1; SBD, L. Banachowski -----ADAMS ALLEN BLAKE CLARK* FORD JAMES JONES KING* MARTIN MILLER* SCOTT SMITH TURNER WARD 3

Złączenie - klucz obcy klucz główny • Wypisz wszystkich pracowników i dla każdego z nich podaj nazwę jego działu. SELECT Empno, Ename, Dname FROM Emp, Dept WHERE Emp. Deptno= Deptno; EMPNO ENAME -----7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 SBD, FORD L. Banachowski DNAME ----RESEARCH SALES ACCOUNTING RESEARCH ACCOUNTING SALES RESEARCH klucz obcy = klucz główny 4

Zasady wykonywania zapytania z listą tabel w klauzuli FROM 1. FROM 2. WHERE - True (nie False i nie Null) 3. SELECT 4. UNION, INTERSECT, EXCEPT 5. ORDER BY SBD, L. Banachowski 5

Zapytanie sumaryczne (podsumowujące) Statystyka zarobków pracowników pracujących w dziale Sprzedaży. SELECT MIN(Sal) AS "Min zarobki", MAX(Sal) AS "Max zarobki", MAX(Sal) - MIN(Sal) AS "Rozp zarobków", AVG(Sal) AS "Śred zarobki" FROM Emp, Dept WHERE Emp. Deptno= Deptno AND Dname = 'SALES'; Min zarobki Max zarobki Rozp zarobków Śred zarobki ----------- ------950 SBD, L. Banachowski 2850 1900 1566. 66667 6

Zapytanie grupujące SELECT Deptno Id, Dname Nazwa_działu, COUNT(*) AS Liczba, SUM(Sal) AS Suma FROM Dept, Emp WHERE Deptno= Emp. Deptno GROUP BY Deptno, Dname; ID NAZWA_DZIAŁU LICZBA SUMA --------------10 ACCOUNTING 3 8750 20 RESEARCH 5 10875 30 SALES 6 9400 SBD, L. Banachowski 7

Zasady wykonywania zapytania grupującego 1. FROM 2. WHERE - True (nie False i nie Null) 3. GROUP BY 4. HAVING 5. SELECT 6. UNION, INTERSECT, EXCEPT 7. ORDER BY SBD, L. Banachowski 8

Podzapytanie Wypisz osoby, które zarabiają najwięcej ze wszystkich pracowników. SELECT Ename, Sal FROM Emp WHERE Sal = (SELECT Max(Sal) FROM Emp); ENAME SAL -----KING 5000 SBD, L. Banachowski 9

Podzapytanie skorelowane Dla każdego działu wypisz osobę, która zarabia najwięcej w tym dziale. SELECT a. Deptno, a. Ename, a. Sal FROM Emp a WHERE Sal = (SELECT Max(b. Sal) FROM Emp b WHERE b. Deptno= a. Deptno); DEPTNO ----30 20 10 20 SBD, L. Banachowski ENAME SAL -----BLAKE 2850 SCOTT 3000 KING 5000 FORD 3000 10

Podzapytania w innych instrukcjach UPDATE Emp SET Sal = Sal * 1. 1 WHERE 'DALLAS' = (SELECT Loc FROM Dept WHERE Emp. Deptno=Deptno); CREATE TABLE Urzędnicy (Empno, Ename, Sal) AS SELECT Empno, Ename, Sal FROM Emp WHERE Job = 'CLERK'; INSERT INTO Emp_new SELECT * FROM Emp WHERE Sysdate - Hiredate < 10; SBD, L. Banachowski 11

Klauzula FROM instrukcji SELECT %udziałów w liczbie pracowników i zarobkach w firmie SELECT a. Deptno "Dział", Trunc(100*a. Liczba_Prac/b. Liczba_Prac, 1) AS "%Pracowników", Trunc(100*a. Suma_zarob/b. Suma_zarob, 1) AS "%Zarobków" FROM Dzial %Pracowników %Zarobków (SELECT Deptno, COUNT(*) AS Liczba_Prac, ------------ SUM(Sal) AS Suma_zarob 10 21. 4 30. 1 FROM Emp 20 35. 7 37. 4 30 42. 8 32. 3 GROUP BY Deptno ) a, (SELECT COUNT(*) AS Liczba_Prac, SUM(Sal) AS Suma_zarob 12 FROM Emp SBD, L. Banachowski

Złączenie zewnętrzne Wypisz wszystkie działy dla każdego z nich podając sumaryczne zarobki zatrudnionych w nim pracowników. SELECT Deptno, Dname, SUM(Sal) FROM Dept, Emp WHERE Deptno = Emp. Deptno(+) Rozszerzenie o pseudowiersze w tabeli Emp. GROUP BY Deptno, Dname; DEPTNO DNAME SUM(SAL) ----------------- 10 ACCOUNTING 8750 20 RESEARCH 10875 30 SALES 9400 40 OPERATIONS SBD, L. Banachowski W dziale 40 nie ma zatrudnionych pracowników. 13