GAKKO.EDU

I. Zapytania SQL

Wybrać numery departamentów, nazwiska pracowników oraz numery pracownicze ich szefów z tabeli EMP.
SELECT ename, deptno, mgr
FROM emp;
Wybrać wszystkie kolumny z tabeli emp;
SELECT *
FROM emp;
Wyliczyć roczną pensje podstawową dla każdego pracownika.
SELECT ename, sal * 12
FROM emp;
Wyliczyć roczną pensje podstawową dla każdego pracownika, gdyby każdy dostał podwyżkę o 250.
SELECT ename, (sal + 250) * 12
FROM emp;
Wybrane wyrażenie SAL*12 zaetykietować nagłówkiem ROCZNA.
SELECT ename, sal*12 AS roczna
FROM emp;
Wybrane wyrażenie SAL*12 zaetykietować nagłówkiem R PENSJA.
SELECT ename, sal*12 AS ”r pensja”
FROM emp;
Jeżeli chcemy użyć spacji w etykiecie kolumny, to używamy cudzysłowu ”, natomiast w wartościach typu tekstowego używamy apostrofu ‘.
Połączyć EMPNO i nazwisko, opatrzyć je nagłówkiem EMPLOYEE.
SELECT empno || ename AS employee
FROM emp;
W MS SQL istnieje konieczność konwertowania wartości numerycznych do tekstowych (w Oracle nie). Operatorem konkatenacji jest w tym wypadku + a nie ||
SELECT CONVERT(VARCHAR, empno) + ename AS employee
FROM emp;
Utworzyć zapytanie zwracające wynik w postaci np. „Kowalski pracuje w dziale 20”.
SELECT ename || ‘ pracuje w dziale nr ' || deptno
FROM emp;
MS SQL:
SELECT ename + ‘ pracuje w dziale nr ' + CONVERT(VARCHAR,deptno)
FROM emp;
Wyliczyć roczną pensję całkowitą dla każdego pracownika.
SELECT ename, (sal * 12 + NVL(comm,0))
FROM emp;
Użycie funkcji NVL jest niezbędne, gdyż kolumna comm u niektórych pracowników zawiera wartość NULL. Wykonywanie operacji arytmetycznych na wartości NULL daje również NULL np. 2 + NULL = NULL, natomiast 2 + NVL(NULL, 0) = 2.
W MS SQL odpowiednikiem funkcji NVL jest ISNULL:
SELECT ename, (sal * 12 + ISNULL(comm,0))
FROM emp;
Wyświetlić wszystkie numery departamentów występujące w tabeli EMP.
SELECT deptno
FROM Emp;
Wyświetlić wszystkie różne numery departamentów występujące w tabeli EMP.
SELECT DISTINCT deptno
FROM Emp;
Wybrać wszystkie wzajemnie różne kombinacje wartości DEPTNO i JOB.
SELECT DISTINCT deptno, job
FROM Emp;
Posortować wszystkie dane tabeli EMP według ENAME.
SELECT *
FROM emp
ORDER BY ename;
Posortować malejąco wszystkie dane tabeli EMP według daty zatrudnienia począwszy od ostatnio zatrudnionych.
SELECT * FROM emp ORDER BY hiredate DESC;
Posortować dane tabeli EMP według wzrastających wartości kolumn DEPTNO oraz malejących wartości kolumny SAL (bez wypisywania kolumny SAL).
SELECT empno, ename, job, mgr
FROM emp
ORDER BY deptno, sal DESC;
Domyślnie przyjęty jest porządek rosnący i dlatego nie musimy pisać ASC po deptno.
Wybrać nazwiska, stanowiska pracy, płacę i numery departamentów wszystkich zatrudnionych na stanowisku CLERK.
SELECT ename, job, sal, deptno FROM emp
WHERE job = 'CLERK';
Wybrać wszystkie nazwy i numery departamentów o numerze większym od 20.
SELECT deptno, dname
FROM dept
WHERE deptno > 20;
Wybrać pracowników, których prowizja przekracza miesięczną pensję.
SELECT *
FROM emp
WHERE NVL(comm,0) > sal;
MS SQL:
SELECT *
FROM emp
WHERE ISNULL(comm,0) > sal;
Wybrać dane tych pracowników, których zarobki mieszczą się pomiędzy 1000 a 2000.
SELECT *
FROM emp
WHERE sal BETWEEN 1000 AND 2000;
Wybrać dane pracowników, których bezpośrednimi szefami są 7902, 7566 lub 7788.
SELECT *
FROM emp
WHERE mgr IN (7902, 7566, 7788);
Rozwiązanie bez użycia IN:
SELECT * FROM emp WHERE mgr = 7902 OR mgr = 7566 OR mgr = 7788;
Wybrać dane tych pracowników, których nazwiska zaczynają się na S.
SELECT *
FROM emp
WHERE ename LIKE 'S%';
Wybrać dane tych pracowników, których nazwiska są czteroliterowe.
SELECT *
FROM emp
WHERE LENGTH(ename) = 4;
Inne rozwiązanie:
SELECT *
FROM emp
WHERE ename LIKE ‘____’
MS SQL:
SELECT *
FROM emp
WHERE LEN(ename) = 4;
Wybrać dane tych pracowników, którzy nie posiadają szefa.
SELECT *
FROM emp
WHERE mgr IS NULL;
Wybrać dane tych pracowników, których zarobki są poza przedziałem <1000, 2000>.
SELECT *
FROM emp
WHERE NOT sal BETWEEN 1000 AND 2000;
Rozwiązanie bez użycia BETWEEN:
SELECT * FROM emp
WHERE sal < 1000 OR sal > 2000;
Wybrać dane tych pracowników, których nazwiska nie zaczynają się na M.
SELECT *
FROM emp
WHERE NOT ename LIKE 'M%';
Wybrać dane tych pracowników, którzy mają szefa.
SELECT *
FROM emp
WHERE mgr IS NOT NULL;
Wybrać dane tych pracowników zatrudnionych na stanowisku CLERK, których zarobki mieszczą się w przedziale <1000, 2000).
SELECT *
FROM emp
WHERE job = 'CLERK' AND sal >= 1000 AND sal < 2000;
Wybrać dane pracowników albo zatrudnionych na stanowisku CLERK, albo tych, których zarobki mieszczą się w przedziale <1000, 2000).
SELECT *
FROM emp
WHERE job = 'CLERK' OR (sal >= 1000 AND sal < 2000);
Wybrać wszystkich pracowników zatrudnionych na stanowisku MANAGER z pensją powyżej 1500 oraz wszystkich na stanowisku SALESMAN.
SELECT *
FROM emp
WHERE (sal > 1500 AND job = 'MANAGER') OR job = 'SALESMAN';
Wybrać wszystkich pracowników zatrudnionych na stanowisku MANAGER lub na stanowisku SALESMAN z pensją powyżej 1500.
SELECT *
FROM emp
WHERE job = 'MANAGER' OR (job = 'SALESMAN' AND sal >1500);
Wybrać wszystkich pracowników zatrudnionych na stanowisku MANAGER ze wszystkich departamentów wraz ze wszystkimi pracownikami zatrudnionymi na stanowisku CLERK w departamencie 10.
SELECT *
FROM emp
WHERE job = ‘MANAGER’ OR (job = ‘CLERK’ AND deptno = 10)
Wybrać wszystkie dane z tabeli SALGRADE.

SELECT *
FROM salgrade

Wybrać wszystkie dane z tabeli DEPT
SELECT *
FROM dept
Wybrać dane tych pracowników, których zarobki mieszczą się w przedziale <1000,2000>
SELECT *
FROM emp
WHERE sal BETWEEN 1000 AND 2000
Wybrać numery i nazwy departamentów sortując według numerów departamentów
SELECT deptno, dname
FROM dept
ORDER BY deptno
Wybrać wszystkie wzajemnie różne stanowiska pracy
SELECT DISTINCT job
FROM emp
Wybrać dane pracowników zatrudnionych w departamentach 10 i 20 w kolejności alfabetycznej ich nazwisk
SELECT *
FROM emp
WHERE deptno = 10 OR deptno = 20
ORDER BY ename
Wybrać nazwiska i stanowiska pracy wszystkich pracowników z departamentu 20 zatrudnionych na stanowisku CLERK
SELECT ename, job
FROM emp
WHERE deptno = 20 AND job = ‘CLERK’
Wybrać nazwiska tych pracowników, w których nazwisku występuje ciąg „TH” lub „LL”
SELECT ename
FROM emp
WHERE ename LIKE ‘%TH%’ OR ename LIKE ‘%LL%’
Wybrać nazwisko, stanowisko i pensję pracowników, którzy posiadają szefa
SELECT ename, job, sal
FROM emp
WHERE mgr IS NOT NULL
Wybrać nazwiska i całkowite roczne zarobki wszystkich pracowników
SELECT ename, sal*12 + NVL(comm, 0)
FROM emp
MS SQL
SELECT ename, sal*12 + ISNULL(comm, 0)
FROM emp
Wybrać ENAME, DEPTNO i HIREDATE tych pracowników, którzy zostali zatrudnieni w 1980r.
SELECT ename, deptno, hiredate
FROM emp
WHERE hiredate >= ‘01/01/80’ AND hiredate <= ‘31/12/80’
Format daty może zależeć od ustawień serwera. Warto wcześniej sprawdzić, jaki format jest ustawiony na serwerze lub użyć funkcji konwertujących / porównujących daty.
Wybrać nazwiska, roczna pensję oraz prowizję tych wszystkich sprzedawców, których miesięczna pensja przekracza prowizję. Wyniki posortować według malejących zarobków, potem nazwisk
SELECT ename, sal * 12, comm
FROM emp
WHERE sal > NVL(comm,0)
ORDER BY sal DESC, ename
MS SQL
SELECT ename, sal * 12, comm
FROM emp
WHERE sal > ISNULL(comm,0)
ORDER BY sal DESC, ename

Wybieranie danych z wielu tabel

Połącz dane z tabeli EMP i DEPT przy pomocy warunku złączenia w WHERE.

SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno;
Gdyby nie było warunku złączenia (WHERE emp.deptno = dept.deptno), to każdy pracownik zostałby wypisany kilkakrotnie, połączony z każdym działem.

Połącz dane z tabeli EMP i DEPT przy pomocy INNER JOIN.
SELECT *
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
To zapytanie zadziała dokładnie tak samo jak to z punktu 1. Różnica jest taka, że przy pomocy INNER JOIN możemy oddzielić warunki złączenia od pozostałych warunków.
Wybierz nazwiska oraz nazwy departamentów wszystkich pracowników w kolejności alfabetycznej.
SELECT ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno ORDER BY ename
Wybierz nazwiska wszystkich pracowników wraz z numerami i nazwami departamentów w których są zatrudnieni.
SELECT ename, dname, emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno;
Wybierając numery departamentów musimy określić, z której tabeli mają być one wybrane (emp.deptno), ponieważ kolumna deptno występuje zarówno w tabeli emp jak i w dept. Wszystko jedno, z której tabeli wybierzemy, ponieważ jak określa warunek WHERE, numery te są sobie równe.
Dla pracowników o miesięcznej pensji powyżej 1500 podaj ich nazwiska, miejsca usytuowania ich departamentów oraz nazwy tych departamentów.
SELECT ename, loc, dname
FROM emp, dept
WHERE sal > 1500 AND emp.deptno = dept.deptno;
Utwórz listę pracowników podając ich nazwisko, zawód, pensję i stopień zaszeregowania.
SELECT ename, job, sal, grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal;
W tym zapytaniu mamy do czynienia ze złączeniem nie po kluczach (klucz główny = klucz obcy) tylko po wartościach atrybutów.
Wybierz informacje o pracownikach, których zarobki odpowiadają klasie 3.
SELECT ename, sal, job
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal AND grade = 3;
Wybierz pracowników zatrudnionych w Dallas.
SELECT ename, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno AND loc = ‘DALLAS’
Wybierz nazwiska pracowników, nazwy działów i stopnie zaszeregowania
SELECT ename, dname, grade
FROM emp
INNER JOIN dept ON emp.deptno = dept.deptno
INNER JOIN salgrade ON sal BETWEEN losal AND hisal
Oczywiście równie dobre byłoby rozwiązanie bez użycia INNER JOIN (z warunkami złączenia w WHERE).
Wypisz dane wszystkich działów oraz ich pracowników tak, aby dane działu pojawiły się, nawet jeśli nie ma w dziale żadnego pracownika.
SELECT *
FROM dept
LEFT JOIN emp ON emp.deptno = dept.deptno;
LEFT JOIN określa, że warunek złączenia może, ale nie musi być spełniony aby rekord z tabeli dept został wyświetlony. Jeżeli warunek nie jest spełniony (np. dla działu 40 w którym nikt nie pracuje), wyświetlą się dane działu, a pozostałe pola (te z tabeli emp) będą ustawione na NULL.
Wybierz pracowników z działu 20 i 30 (nazwisko i nr działu – wypisz dział 40 bez nazwisk).
SELECT ename, deptno FROM emp WHERE deptno = 20
UNION SELECT ‘’, deptno FROM emp WHERE deptno = 30;
Dwa apostrofy w drugim zapytaniu są potrzebne, ponieważ obydwa zapytania w unii muszą mieć tyle samo pól (pola muszą sobie odpowiadać).
Możemy jednak zauważyć, że wypisał się tylko jeden pracownik z działu 30, a z pewnością jest ich więcej. Jest to spowodowane tym, że UNION usuwa duplikaty podobnie do DISTINCT, więc wszystkie rekordy (‘’, 30) zostają zbite w jeden rekord. Aby pozbyć się tego problemu, musimy dodać jakieś pole, które rozróżni rekordy, np. empno lub użyć UNION ALL.
SELECT ename, deptno, empno FROM emp WHERE deptno = 20
UNION SELECT ‘’, deptno, empno FROM emp WHERE deptno = 30;
Wypisz stanowiska występujące w dziale 10 oraz 30.
SELECT job FROM emp WHERE deptno = 10
UNION SELECT job FROM emp WHERE deptno = 30;
Oczywiście można to też zrobić bez użycia UNION np.:
SELECT DISTINCT job FROM emp
WHERE deptno IN (10, 30);
Wypisz stanowiska występujące zarówno w dziale 10 jak w dziale 30.
SELECT job FROM emp WHERE deptno = 10
INTERSECT SELECT job FROM emp WHERE deptno = 30;
INTERSECT nie występuje w standardzie SQL i nie zadziała również w MS SQL. Aby rozwiązać to zadanie bez INTERSECT, należałoby użyć podzapytań (omówione w dziale 4).
Wypisz stanowiska występujące w dziale 10 a nie występujące w dziale 30.
SELECT job FROM emp WHERE deptno = 10
MINUS SELECT job FROM emp WHERE deptno = 30;
MINUS nie występuje w standardzie SQL i nie zadziała również w MS SQL. Aby rozwiązać to zadanie bez MINUS, należałoby użyć podzapytań (omówione w dziale 4).
Wybierz pracowników, którzy zarabiają mniej od swoich kierowników.
SELECT pracownik.ename, pracownik.sal
FROM emp pracownik, emp kierownik
WHERE pracownik.mgr = kierownik.empno AND pracownik.sal < kierownik.sal
W tym zapytaniu musimy złączyć tabelę emp z samą sobą (mgr jest kluczem obcym z tabeli emp i wskazuje kierownika). Musimy więc użyć dwukrotnie tabeli emp po FROM. Aby wiedzieć, do której z tabel emp się odwołujemy, musimy nadać aliasy (pracownik i kierownik) a następnie odwoływać się do nich tak, jakby były to normalne tabele.
Dla każdego pracownika wypisz jego nazwisko oraz nazwisko jego szefa. Posortuj według nazwiska szefa.
SELECT pracownik.ename, kierownik.ename
FROM emp pracownik, emp kierownik
WHERE pracownik.mgr = kierownik.empno
ORDER BY kierownik.ename

III. Funkcje grupujące

Oblicz średni zarobek w firmie.
SELECT AVG(sal)
FROM emp;
Znajdź minimalne zarobki na stanowisku CLERK.
SELECT MIN(sal)
FROM emp
WHERE job = ‘CLERK’
Znajdź ilu pracowników zatrudniono w departamencie 20.
SELECT COUNT(*)
FROM emp
WHERE deptno = 20;
Obliczyć średnie zarobki na każdym ze stanowisk pracy.
SELECT job, AVG(sal)
FROM emp
GROUP BY job;
Obliczyć średnie zarobki na każdym ze stanowisk pracy z wyjątkiem stanowiska MANAGER.
SELECT job, AVG(sal)
FROM emp
WHERE NOT job = ‘MANAGER’
GROUP BY job;
Obliczyć średnie zarobki na każdym ze stanowisk pracy w każdym departamencie.
SELECT deptno, job, AVG(sal)
FROM emp
GROUP BY deptno, job;
Dla każdego stanowiska oblicz maksymalne zarobki.
SELECT MAX(sal), job
FROM emp
GROUP BY job;
Wybrać średnie zarobki tylko tych departamentów, które zatrudniają więcej niż trzech pracowników.
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 3;
Wybrać tylko te stanowiska, na których średni zarobek wynosi 3000 lub więcej.
SELECT job, AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal) >= 3000;
-------------------------------------------------------------------------------------------------------------
Znajdź średnie miesięczne pensje oraz średnie roczne zarobki dla każdego stanowiska, pamiętaj o prowizji.
SELECT AVG(sal), AVG(sal*12 + NVL(comm,0))
FROM emp
GROUP BY job;
MS SQL
SELECT AVG(sal), AVG(sal*12 + ISNULL(comm,0))
FROM emp
GROUP BY job;
Znajdź różnicę miedzy najwyższą i najniższa pensją.
SELECT MAX(sal) - MIN(sal)
FROM emp
Znajdź departamenty zatrudniające powyżej trzech pracowników.
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
HAVING COUNT (*) > 3;
Sprawdź, czy wszystkie numery pracowników są rzeczywiście wzajemnie różne.
SELECT MAX(COUNT(*))
FROM emp
GROUP BY empno;
Aby sprawdzić, czy numery empno są rzeczywiście wzajemnie różne, grupujemy po nich i liczymy ile jest rekordów w każdej grupie. Następnie bierzemy maksimum z tych numerów. Jeżeli jest 1 to ok., jeżeli np. 2 to znaczy, że istnieje 2 pracowników o takim samym numerze.
Podaj najniższe pensje wypłacane podwładnym swoich kierowników. Wyeliminuj grupy o minimalnych zarobkach niższych niż 1000. Uporządkuj według pensji.
SELECT mgr, MIN(sal) AS najnizsza
FROM emp
GROUP BY mgr
HAVING MIN(sal) > 1000
ORDER BY najnizsza;

-------------------------------------------------------------------------------------------------------------

Wypisz ilu pracowników ma dział mający siedzibę w DALLAS.
SELECT COUNT(*)
FROM emp, dept
WHERE dept.loc = ‘DALLAS’ AND emp.deptno = dept.deptno;
Podaj maksymalne zarobki dla każdej klasy zarobkowej.
SELECT MAX(sal), grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal
GROUP BY grade;
Sprawdź, które wartości zarobków powtarzają się.
SELECT sal, COUNT(*)
FROM emp
GROUP BY sal
HAVING COUNT(*) > 1;
Podaj średni zarobek pracowników z drugiej klasy zarobkowej.
SELECT AVG(sal)
FROM emp, salgrade
WHERE grade = 2 AND sal BETWEEN losal AND hisal;
Sprawdź, ilu podwładnych ma każdy kierownik.
SELECT mgr, COUNT(*)
FROM emp
GROUP BY mgr;
Podaj sumę, którą zarabiają razem wszyscy pracownicy z pierwszej klasy zarobkowej.
SELECT SUM(sal)
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal AND grade = 1;

IV. Podzapytania

Znaleźć pracowników z pensją równą minimalnemu zarobkowi w firmie.
SELECT *
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp);
Znaleźć wszystkich pracowników zatrudnionych na tym samym stanowisku co BLAKE.
SELECT *
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename = ‘BLAKE’);
Znaleźć pracowników o pensjach z listy najniższych zarobków osiągalnych w departamentach.
SELECT *
FROM emp
WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY dept);
Znaleźć pracowników o najniższych zarobkach w ich departamentach.
SELECT *
FROM emp a
WHERE sal = (SELECT MIN(sal) FROM emp b WHERE a.deptno = b.deptno);
Stosując operator ANY wybrać pracowników zarabiających powyżej najniższego zarobku z departamentu 30.
SELECT *
FROM emp
WHERE sal > ANY (SELECT sal FROM emp WHERE dept = 30);
Znaleźć pracowników, których zarobki są wyższe od pensji każdego pracownika z departamentu 30.
SELECT *
FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE dept = 30);
Wybrać departamenty, których średnie zarobki przekraczają średni zarobek departamentu 30.
SELECT DISTINCT deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno = 30);
Znaleźć stanowisko, na którym są najwyższe średnie zarobki.
SELECT job
FROM emp
GROUP BY job
HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp GROUP BY job);
W MS SQL nie można używać MAX(AVG(…)). Możemy jednak to zadanie rozwiązać inaczej:
SELECT job
FROM emp
GROUP BY job
HAVING AVG(sal) >= ALL (SELECT AVG(sal) FROM emp GROUP BY job);
Znaleźć pracowników, których zarobki przekraczają najwyższe pensje z departamentu SALES.
SELECT *
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp, dept WHERE emp.deptno = dept.deptno AND dname = ‘SALES’);
Znaleźć pracowników, którzy zarabiają powyżej średniej w ich departamentach.
SELECT *
FROM emp a
WHERE sal > (SELECT AVG(sal) FROM emp b WHERE a.deptno = b.deptno);
Znaleźć pracowników, którzy posiadają podwładnych za pomocą operatora EXISTS.
SELECT *
FROM emp a
WHERE EXISTS (SELECT * FROM emp b WHERE b.mgr = a.empno);
Znaleźć pracowników, których departament nie występuje w tabeli DEPT.
SELECT *
FROM emp
WHERE deptno NOT IN (SELECT deptno FROM dept);
Wynik ‘No rows selected’ jest normalny, ponieważ w demonstracyjnej bazie (demobld) nie ma takich pracowników.
-------------------------------------------------------------------------------------------------------------
Wskazać dla każdego departamentu ostatnio zatrudnionych pracowników. Uporządkować według dat zatrudnienia.
SELECT *
FROM emp a
WHERE hiredate = (SELECT MAX(hiredate) FROM emp b WHERE a.deptno = b.deptno)
ORDER BY hiredate;
Podać ENAME, SAL i DEPTNO dla pracowników, których zarobki przekraczają średnią ich departamentów.
SELECT ename, sal, deptno
FROM emp a
WHERE sal > (SELECT AVG(sal) FROM emp b WHERE b.deptno = a.deptno);
Stosując podzapytanie znaleźć departamenty, w których nikt nie pracuje.
SELECT *
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp);
Dla każdego stanowiska wypisać maksymalną pensję z dopiskiem „Maksymalna” oraz minimalną z dopiskiem „Minimalna”. Posortować wynik według stanowiska.
SELECT job, zarobki, minmax FROM
(SELECT job, MAX(sal) AS zarobki, ‘Maksymalna’ AS minmax FROM emp GROUP BY job
UNION SELECT job, MIN(sal) AS zarobki, ‘Minimalna’ AS minmax FROM emp GROUP BY job) abc
ORDER BY job;
Zapytania SQL można używać po FROM zamiast tabeli. Trzeba wtedy nadać takiemu podzapytaniu alias (w tym wypadku abc).
Napisz zapytanie zwracające procentowy udział liczby pracowników w każdym dziale.
SELECT deptno, w_dziale / razem * 100
FROM (SELECT COUNT(*) AS razem FROM emp) a,
(SELECT deptno, COUNT(*) AS w_dziale FROM emp GROUP BY deptno) b
W MS SQL istnieje konieczność konwersji wyniku do typu zmiennopozycyjnego, ponieważ COUNT zwraca typ INT, a wynik dzielenia INT / INT jest również typu INT.

Tworzenie tabel

Utworzyć tabelę „miasto”, posiadającą klucz główny (np. id_miasto) oraz atrybut „nazwa”.

CREATE TABLE miasto (
id_miasta INTEGER NOT NULL,
nazwa VARCHAR2(50) NOT NULL,
PRIMARY KEY (id_miasta)
);

Utworzyć tabelę „osoba”, posiadającą atrybuty: id_osoby, nazwisko, wiek, adres oraz id_miasta, które jest kluczem obcym z tabeli miasto.

CREATE TABLE osoba (
id_osoby               INTEGER PRIMARY KEY,
id_miasta              INTEGER NOT NULL,
nazwisko              VARCHAR2(50) NOT NULL,
adres                     VARCHAR2(100) NULL,
wiek                      INTEGER NULL,
FOREIGN KEY (id_miasta) REFERENCES Miasto
);

Modyfikowanie tabeli

Dodać pole „zawod” do tabeli osoba.

ALTER TABLE osoba
ADD (zawod VARCHAR2(20) NULL);

Wstawianie danych

Wstawić kilka przykładowych miast do tabeli „miasto”.

INSERT INTO miasto (id_miasta, nazwa)
VALUES (5, 'Wroclaw');

Wstawić kilka osób do tabeli „osoba”. Sprawdzić, czy da się wstawić id_miasta nie istniejące w tabeli „miasto”.

INSERT INTO osoba (id_osoby, id_miasta, nazwisko, adres, wiek, zawod)
VALUES (1, 1, 'Mlynarczyk', '1 maja 3', 29, 'Informatyk');

Wstawić do tabeli osoba wszystkich pracowników z tabeli emp. Dla brakujących pól (wiek, id_miasta, adres) można przyjąć stałą wartość dla wszystkich rekordów.

INSERT INTO osoba (id_osoby, id_miasta, nazwisko, adres, wiek, zawod)
SELECT empno, 1, ename, NULL, 30, job FROM emp;

Modyfikacja danych

Zmienić dowolnie wiek oraz adres osobom, które mają mniej niż 30 lat.

UPDATE osoba
SET wiek = 50,
adres = 'Koszykowa 86'
WHERE wiek < 30;

Perspektywy

Utworzyć perspektywę „niepełnoletni”, wybierającą osoby mające mniej niż 18 lat. Sprawdzić działanie tej perspektywy wybierając z niej dane i przeprowadzając modyfikację danych.

CREATE VIEW niepelnoletni AS
SELECT id_miasta, nazwisko, adres, wiek, zawod
FROM osoba
WHERE wiek < 18;

Sprawdzanie:

SELECT * FROM niepelnoletni;
UPDATE niepelnoletni
SET adres = 'abc';

UWAGA: Istnieją ograniczenia co do aktualizacji perspektyw. Możemy wykonywać aktualizację tylko wtedy, gdy perspektywa spełnia następujące warunki:

Nie używamy DISTINCT,
Jest tylko 1 tabela w klauzuli FROM,
Na liście SELECT mogą znajdować się tylko nazwy kolumn,
W klauzuli WHERE nie może być podzapytania,
Nie mogą występować klauzule GROUP BY i HAVING.

Usuwanie danych

Usunąć wszystkie osoby, których nazwisko zaczyna się na literę P.

DELETE FROM osoba
WHERE nazwisko LIKE 'P%';

Perspektywa WITH CHECK OPTION

Utworzyć perspektywę zwracającą wszystkich pracowników zarabiających powyżej 1000 z użyciem opcji WITH CHECK OPTION. Sprawdzić, czy da się zmniejszyć pensję o 400 i czy da się zwiększyć pensję o dowolną wartość.

CREATE VIEW powyzej AS
SELECT * FROM emp WHERE sal > 1000
WITH CHECK OPTION

sprawdzenie

UPDATE powyzej SET sal = sal-400;
UPDATE powyzej SET sal = sal+500;

UNIQUE

Do tabeli „osoba” dodać pole „PESEL” z opcją UNIQUE. Sprawdzić wstawiając kilka rekordów, czy unikalność numerów PESEL będzie rzeczywiście sprawdzana.

ALTER TABLE osoba ADD (PESEL VARCHAR2(11) UNIQUE);

CHECK

Do tabeli „osoba” dodać warunek CHECK, który będzie pilnował, aby wiek nie był większy nić 120. Czy da się wstawić osobę starszą niż 120 lat?

ALTER TABLE osoba ADD (CHECK (wiek<=120));

Usuwanie obiektów bazy danych

Usunąć stworzone tabele oraz perspektywy.

DROP VIEW niepelnoletni;
DROP VIEW powyzej;
DROP TABLE osoba;
DROP TABLE miasto;