Relacyjne bazy danych - laboratorium 6

Wprowadzenie

Na dzisiejszych laboratoriach kontynujemy dalej zagadnienia związane z SQL. Do pracy na zajęciach wymagane są gotowe zadania z poprzednich zajęć. Jeśli pracujesz na nowym stanowisku, pobierz swoje repozytorium, uruchom PSQL na Dockerze. Połącz się za pomocą DataGripa z bazą.

SQL part 3

Sortowanie

ORDER BY

Każde zapytanie SQL, możemy skontruować tak, by otrzymać posortowane dane malejąco lub rosnąco po danych kolumnach. W tym celu należy użyć klauzuli ORDER BY. Domyślnie klauzula sortuje dane rosnąco.

Przykład zapytania o imiona oraz nazwiska studentów z sortowaniem rosnącym po nazwisku:

SELECT name, last_name
FROM students 
ORDER BY last_name ASC;

Przykład sortowania malejącego:

SELECT name, last_name
FROM students 
ORDER BY last_name DESC;

Otrzymanie tylko żądanej porcji danych

LIMIT oraz OFFSET

Klauzula LIMIT oraz OFFSET wykorzystywana jest w przypadkach żadania otrzymania tylko odpowiedniej porcji danych np. w page’owaniu. LIMIT definiuje ile wierszy chcemy otrzymać, natomiast OFFSET wskazuje ile pierwszych wierszy tabeli pomijamy aby zobaczyć dane.

Przykład zapytania o 5 wierszy, z pominięciem 6 pierwszych wierszy:

SELECT name, last_name
FROM students 
ORDER BY last_name ASC
LIMIT 5 OFFSET 6;

Subqueries (podzapytania)

Subquery, czyli tzw. podzapytania, pozwalają na wykonanie zagnieżdzonych zapytań w jednym głównym zapytaniu. Subquery może wystąpić w klauzulach WHERE, FROM oraz SELECT.

Przykład query z subquery, które zwróci listę indeksów studentów z tabeli student, którzy zdobyli ocenę większą niż dwa z przedmiotu relacyjne bazy danych.

SELECT student_index
FROM students
WHERE id IN 
  (SELECT id_student FROM student_subject
    WHERE id_subject = (SELECT id FROM subject WHERE grade > 2 and name = 'relational databases'));

Operacja na zbiorach - łączenie danych pionowo

UNION

Klauzula UNION służy łączeniu wyników zapytania o dane w wierszach. Usuwa ona zdublikowane wiersze pomiędzy kilkoma zapytaniami. Każde z zapytanie, które łączymy musi mieć taką samą liczbę kolumn o podobnym typie danych.

Diagram Venn’a obrazujący operacje na zbiorze:

źródło: pl.wikipedia.org/wiki/Diagram_Venna

Przykład otrzymania wszystkich identyfikatorów użytkowników z tabeli students oraz teachers:

SELECT user_id FROM students
UNION
SELECT user_id FROM teachers;

UNION ALL

Operator UNION ALL różni się z kolei od operatora UNION tym że nie usuwa ono duplikatów danych.

Przykład użycia:

SELECT user_id FROM students
UNION ALL
SELECT user_id FROM teachers;

EXCEPT

Klauzula EXCEPT służy odejmowaniu wspólnych rekordów zapytania drugiego od zapytania pierwszego, czyli pokazaniu unikalnych danych zapytania pierwszego w porównaniu do zapytania drugiego.

Diagram Venn’a obrazujący operacje na zbiorze:

źródło: pl.wikipedia.org/wiki/Diagram_Venna

Przykład zapytania o identyfikatory użytkowników, które zwróci nam takich wykładowców, którzy nie byli studentami:

SELECT user_id FROM teachers
EXCEPT
SELECT user_id FROM students;

INTERSECT

Klauzula INTERSECT zwróci nam część wspólną obu zapytań.

Diagram Venn’a obrazujący operacje na zbiorze:

źródło: pl.wikipedia.org/wiki/Diagram_Venna

Przykład zapytania o identyfikatory użytkowników, które zwróci nam takich wykładowców, którzy wcześniej również byli studentami danej uczelni:

SELECT user_id FROM teachers
INTERSECT
SELECT user_id FROM students;

Operacja na zbiorach - łączenie danych poziomo

Operacja JOIN pozwala na łączenie danych z tabel po kolumnach. Poniżej wyszczególnione zostały rodzaje połączeń:

INNER JOIN

INNER JOIN pozwala na zwrócenie rekordów, które występują w obydwu tabelach.

źródło: tutorialrepublic.com/sql-tutorial/

Przykład zapytania, łączącego dane z tabeli student oraz użytkownik:

SELECT * FROM students
INNER JOIN users ON students.user_id = users.id;

LEFT JOIN

LEFT JOIN pozwala na zwracania wszystkich rekordów z lewej tabeli oraz tylko tych z prawej, które mają swój odpowiednik.

źródło: tutorialrepublic.com/sql-tutorial/

Przykład zwracający dane wszystkich wykładowców, również którzy wcześniej też byli studentami, po wspólnym polu user_id:

SELECT * FROM teachers
LEFT JOIN students
ON teachers.user_id = students.user_id

RIGHT JOIN

RIGHT JOIN ma zbliżone działanie do LEFT JOIN. Różnica polega na tym że brane są wszystkie rekordy z prawej tabeli, natomiast z lewej brane są tylko odpowiedniki.

źródło: tutorialrepublic.com/sql-tutorial/

FULL OUTER JOIN

FULL OUTER JOIN łączy wszystkie wiersze z lewej tabeli oraz prawej. Jeśli nie ma połączenia pomiędzy wierszem z lewej i z prawej tabeli, dane są wyświetlane dla danego wiersza jako NULL.

źródło: tutorialrepublic.com/sql-tutorial/

Przykład:

SELECT * FROM teachers
FULL OUTER JOIN students
ON teachers.user_id = students.user_id

Zadania

Pamiętaj by zapisać wykonane skrypty w swoim repozytorium. Na potrzeby dzisiejszych zajęć do folderu migrations dodaj i pracuj na pliku V3__lab06.sql. Kontynuujemy pracę nad bazą danych z poprzedniego laboratorium.

1. Napisz zapytanie które jednoczeście używa filtracji danych, grupuje je, sortuje oraz zwraca wynik jakiejkolwiek funkcji agregującej
2. Dla jakichkolwiek tabel napisz zapytania wykorzystując
   • subquery
   • LIMIT z OFFSET (ograniczenie wyświetlania 10 wierszy, z pominięciem 30 pierwszych wierszy)
   • UNION
   • EXCEPT
   • INTERSECT
   • INNER JOIN
   • LEFT JOIN
   • RIGHT JOIN
   • FULL OUTER JOIN