15 lines
485 B
SQL
15 lines
485 B
SQL
-- Pobieranie autorów wraz z ilością książek:
|
|
SELECT a.first_name, a.last_name, COUNT(DISTINCT b.id) AS writed_books
|
|
FROM public.authors a
|
|
JOIN public.books b ON b.author_id = a.id
|
|
GROUP BY a.id
|
|
|
|
-- Pobieranie książek wraz z ilością recenzji:
|
|
SELECT b.title, COUNT(DISTINCT r.id) AS reviews_count, AVG(r.grade) AS avg_grade
|
|
FROM public.books b
|
|
JOIN public.reviews r ON r.book_id = b.id
|
|
GROUP BY b.id
|
|
|
|
-- Pobranie danych za pomoca widoku:
|
|
SELECT * FROM public.average_grades
|