Pytania rekrutacyjne SQL

Jest to pierwsza część z serii pytań i odpowiedzi na rozmowę kwalifikacyjną Java Developera – zakres bazy danych SQL.

Ich pełne zrozumienie powinno dać obraz podstawowych wymagań wobec developera, dla którego SQL jest dodatkowym językiem programowania.

Zapraszam do dzielenia się swoimi pytaniami z rozmów kwalifikacyjnych tutaj w komentarzach lub na grupie.

Co to jest indeks w bazach danych?

Indeks to specjalna struktura danych mająca na celu zwiększenie prędkości wykonywania operacji na tabeli.

Indeks w bazie danych można porównać do spisu treści w książce. Zamiast przeszukiwać całą książkę kartka po kartce, można sprawdzić, na której dokładnie stronie znajduje się dane zagadnienie i przejść bezpośrednio do niego.

CREATE INDEX UsersAgeIndex ON Users (age);

Jakie są wady i zalety stosowania indeksów?

Ponieważ tworzenie i aktualizacja indeksów wymaga dodatkowych operacji na bazie danych, operacje dodawania (INSERT) i aktualizacji (UPDATE) są wolniejsze. Natomiast operacje pobierania danych (SELECT) mogą być szybsze, jeżeli do ich wykonania zostanie wykorzystany indeks.

Czy można założyć indeks na kilku kolumnach?

Można, robi się to analogicznie jak dla jednej kolumny.

CREATE INDEX UsersIdAgeIndex ON Users (id,age);

Jakie znasz funkcje agregujące i co to jest?

SQL udostępnia kilka funkcji agregujących, działających na całej grupie wartości zamiast na pojedynczym polu.

Funkcje tego typu działają na wszystkich wierszach ograniczonych przez klauzulę WHERE lub na każdej z podgrup osobno, jeżeli w zapytaniu jest klauzula GROUP BY.

COUNT – ilość wierszy

Funkcja COUNT zlicza ilość wierszy zwróconą przez zapytanie.

SELECT COUNT(*)
 FROM Users
 WHERE age > 10

Powyższy przykład policzy użytkowników starszych niż 10 lat.

Funkcja może również przyjąć jako argument nazwę pola, np. COUNT(age), jednak wtedy zliczy tylko wiersze, dla których to pole jest różne od NULL.

SUM – suma wszystkich wartości

Funkcja SUM wylicza sumę wszystkich wartości.

SELECT SUM(age)
 FROM Users

Powyższy przykład policzy łączny wiek wszystkich użytkowników. W tym przykładzie widać również, że klauzula WHERE jest opcjonalna.

AVG – średnia

Funkcja AVG wylicza średnią wartość dla zwróconych rekordów.

SELECT AVG(age)
 FROM Users
 GROUP BY city

To zapytanie wyliczy średni wiek użytkowników z każdego miasta.

Jakie są rodzaje złączeń?

Wyróżniamy dwa główne kategorie złączeń: wewnętrzne (inner) oraz zewnętrzne (outer).

• inner join
  Jest to domyślny typ złączeń. Wyniki tych zapytań zawierają tylko wiersze, które spełniają warunek złączenia.
• outer join
  W przypadku tego złączenia wiersze niespełniające wszystkich warunków są również dołączone do wyniku, a brakujące dane są uzupełnione wartościami pustymi (null’ami).

INNER JOIN – wewnętrzne

Wynikiem tego złączenia są tylko wiersze, które spełniają warunek klauzuli złączenia ON.

SELECT * 
 FROM Users 
 INNER JOIN UserGroups 
  ON Users.group_id = UserGroups.id

W zapytaniu słowo INNER jest opcjonalne, ponieważ jest to domyślny typ. W wyniku tego złączenia pojawią się tylko te wiersze z obu tabel, które spełnią warunki wymienione po klauzuli ON.

Self JOIN – własne

Złączenie typu Self JOIN występuje, jeżeli łączymy ze sobą tę samą tabelę. W takiej sytuacji konieczne jest użycie aliasów dla nazwy tabeli.

SELECT * 
 FROM UserGroups g1
 JOIN UserGroups g2
  ON g1.parent = g2.id

Equi-JOIN

To bardzo często występująca kategoria złączeń i pasują do niej również powyższe przykłady. Nazywana bywa również równozłączeniem. Zawierają się w niej wszystkie zapytania, dla których w warunku złączenia klauzuli ON oraz WHERE występuje znak równości =.

Theta Join

Złączenie Theta Join (nierównozłączenie) jest przeciwieństwem dla typu Equi-JOIN. Do tej kategorii należą wszystkie złączenia, w których występują porównania, inne niż zwykłe „równa się”, czyli np. >, != lub BETWEEN.

Anti Join

Jest to szczególny przypadek Theta Join (nierównozłączenia), w którym wykorzystano operator !=.

NATURAL JOIN – naturalne

Złączenie typu NATURAL JOIN występuje, jeżeli obie kolumny występujące w warunku złączenia mają taką samą nazwę.

SELECT * 
 FROM UserGroups
 NATURAL JOIN Users

W tym wypadku tabele zostały połączone na podstawie pola id, ponieważ tylko ono jest takie same w obu tabelach.

Nie jest to jednak zalecana praktyka, ponieważ w wyniku swojej niejednoznaczności może prowadzić do pomyłek. Poniżej równoznaczne zapytanie z wykorzystaniem klauzuli ON.

SELECT * 
 FROM UserGroups g
 JOIN Users u ON (u.id = g.id)

Semi Join – częściowe

Złączenie częściowe występuje, jeżeli w klauzuli SELECT są wymienione tylko kolumny z jednej tabeli.

SELECT u.id, u.age
 FROM UserGroups g
 JOIN Users u ON (u.group_id = g.id)

CROSS JOIN – krzyżowe

Złączenie CROSS JOIN (krzyżowe) wykonuje iloczyn kartezjański z łączonych tabel. W efekcie czego łączy każdy wiersz z pierwszej tabeli z każdym wierszem z drugiej.

SELECT *
 FROM UserGroups g
 CROSS JOIN Users u

SELECT * FROM UserGroups, Users

Powyższe zapytania zwracają ten sam wynik.

LEFT OUTER JOIN

Złączenia typu OUTER pozwalają uwzględnić w końcowym wyniku wiersze, które nie spełniają wszystkich warunków złączenia. W przypadku LEFT OUTER JOIN wiodąca jest pierwsza tabela, a brakujące dane z drugiej tabeli zostaną uzupełnione pustymi wartościami NULL.

SELECT u.id, u.age, g.name
 FROM Users u
 LEFT OUTER JOIN UserGroups g ON (u.group_id = g.id)

Można w ten sposób uwzględnić opcjonalność danej relacji. Powyższy przykład zwróci wszystkich użytkowników, nawet jeżeli nie będą mieli przypisanej grupy.

Słowo kluczowe OUTER w zapytaniu jest opcjonalne.

RIGHT OUTER JOIN

Złączenia RIGHT i LEFT OUTER JOIN działają bardzo podobnie, ale w przypadku RIGHT w wyniku uwzględnione są wiersze z drugiej tabeli, które nie spełniły warunków złączenia.

SELECT u.id, u.age, g.name
 FROM UserGroups g 
 RIGHT OUTER JOIN Users u ON (u.group_id = g.id)

FULL OUTER JOIN

Jest to złączenie obustronne, które można rozumieć jako sumę złączeń LEFT i RIGHT. W końcowym wyniku uwzględnione są wiersze z obu tabel, również te, które nie spełniły wszystkich warunków złączenia.

SELECT u.id, u.age, g.name
 FROM UserGroups g 
 FULL OUTER JOIN Users u ON (u.group_id = g.id)

Co to jest bulk insert?

Bulk insert jest to alternatywne podejście do dodawania danych do tabeli po jednym wierszu, mające na celu przyspieszenie procesu.

Najprostszym sposobem na dodanie kilku wierszy jednocześnie jest wykorzystanie metody COPY lub INSERT INTO.

COPY UserGroups (id, name) FROM stdin;
1       Admin
2       User
\.

INSERT INTO UserGroups (id, name) VALUES (1, 'Admin'), (2, 'User');

Co to jest trigger na bazie danych?

Trigger, czyli wyzwalacz to procedura wykonywana automatycznie przez bazę danych jako reakcja na pewne zdarzenie, np. dodanie (INSERT), aktualizację (UPDATE) lub usunięcie (DELETE) danych.

Triggery nie przyjmują żadnych argumentów oraz nie mogą zatwierdzać (COMMIT) i anulować (ROLLBACK) transakcji, ponieważ są wywoływane automatycznie w kontekście danej operacji (instrukcji SQL).

Jak dodać trigger?

Ogólna postać wyrażenia:

CREATE TRIGGER nazwa [BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON table FOR EACH ROW instruction

Przykład w PostgreSQL:

CREATE FUNCTION UsersUpdateTrigger() RETURNS trigger AS $$ 
BEGIN 
 NEW.modification = NOW();
 RETURN NEW; 
END; $$ LANGUAGE 'plpgsql';

CREATE TRIGGER UsersUpdate BEFORE UPDATE ON Users
 FOR EACH ROW
 EXECUTE PROCEDURE UsersUpdateTrigger();

Przykładowy trigger wywoływany jest bezpośrednio przed każdą aktualizacją użytkownika i ustawia datę modyfikacji na aktualny czas.

Co to jest transakcja?

Transakcja jest to zbiór operacji wykonywanych na bazie danych, które stanowią jedną spójną całość. Dlatego operacje zawarte w jednej transakcji powinny być wykonane wszystkie lub żadna z nich.

Przykładem operacji wykonywanych w ramach jednej transakcji jest przelew bankowy. Podczas wykonywania jednej transakcji pieniądze są pobierane z jednego konta i przekazywane na drugie. W przypadku niepowodzenia obie operacje muszą zostać wycofane.

Co to jest ACID?

Co to jest save point?

Co to jest autocommit?

Jaka jest różnica między TRUNCATE a DELETE?

Co to jest widok bazodanowy i dlaczego warto z niego korzystać?

Jaka jest różnica między klauzulą WHERE, a HAVING?

Czym różni się operacja UNION od UNION ALL?

Na czym polega stronicowanie rekordów?

Co jest szybsze „=” czy „LIKE”?

Jak zrobić klucz główny o samoczynnie powiększającym się numerze?

Co to jest sekwencja?

Wymień znane Ci typy danych w bazie danych

Najczęściej wystarczy wymienić tylko kilka podstawowych typów, np.: date, time, timestamp, int i text.

Pełną listę typów dla PostgreSQL można znaleźć tu.

Zaprojektuj schemat bazy danych dla…

To bardzo ogólne i częste pytanie. Najczęściej trafiają się: wypożyczalnia, księgarnia, sklep itp. Wszystkie mają podobną strukturę, dlatego warto zaprojektować  przynajmniej jedną przykładową bazę danych.

Przykładowe zapytania

Bardzo często na rozmowach trzeba napisać całe zapytania SQL, korzystając z omawianej struktury bazy danych. Poniżej przykładowe zapytania.

1. Pobierz imiona wszystkich użytkowników

   SELECT name FROM Users

2. Pobierz użytkowników oraz nazwę ich grupy

   SELECT u.*, g.name as groupName 
    FROM Users u 
    JOIN UserGroups g ON (u.group_id = g.id)

3. Policz wszystkich użytkowników

   SELECT COUNT(*) FROM Users

4. Pobierz 3 ostatnio zmodyfikowanych użytkowników

   SELECT * FROM Users ORDER BY modification DESC LIMIT 3

A Ty jakie miałeś pytania na swojej rozmowie kwalifikacyjnej?

Chcesz przygotować fantastyczne CV i zabłysnąć na rozmowie kwalifikacyjnej? Sprawdź  najnowsze e-booki:

CV Programisty oraz Rozmowa kwalifikacyjna Programisty