Оператор Select

1.

SQL > DML

2.

author
id
surname
name
birth_year
death_year
author_vs_book
SERIAL
TEXT
TEXT
INTEGER
INTEGER
1
n
author_id
book_id
book_copy
book
INTEGER
INTEGER n
1
id
title
year
cover_image
n
cover_type_id
cover_type
id
SERIAL
name TEXT
1
SERIAL
TEXT
INTEGER
BYTEA
INTEGER
1
n
librarian
user
id
login
password
role
SERIAL
TEXT
TEXT
SMALLINT
id
surname
name
INTEGER
TEXT
TEXT
SERIAL
INTEGER
TEXT
usage
n
n
id
book_id
notes
1
n
n
id
book_copy_id
start_date
start_librarian_id
plan_finish_date
finish_date
finish_librarian_id
reader_id
SERIAL
INTEGER
DATE
INTEGER
DATE
DATE
INTEGER
INTEGER
1
reader
1
id
surname
name
address
INTEGER
TEXT
TEXT
TEXT
1
phone
n
number
reader_id
TEXT
INTEGER
2

3.

SELECT *
FROM "book";
3

4.

SELECT "id", "title", "year"
FROM "book";
4

5.

SELECT "id", "title" AS "t", "year"
FROM "book";
5

6.

SELECT "id", "title", "year"
FROM "book"
WHERE "year" > 1970 AND
"year" <= 2000;
6

7.

SELECT "id", "title", "year"
FROM "book"
WHERE "year" BETWEEN 1970 AND 2000;
7

8.

SELECT *
FROM "author"
WHERE "death_year" IS NULL;
8

9.

SELECT *
FROM "author"
ORDER BY "surname";
9

10.

SELECT *
FROM "book"
ORDER BY "year", "title" DESC;
10

11.

CROSS
JOIN
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
11

12.

CROSS
JOIN
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN
JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
12

13.

CROSS
JOIN
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
13

14.

SELECT *
FROM "book" CROSS JOIN "cover_type";
SELECT *
FROM "book", "cover_type";
14

15.

SELECT *
FROM "book" CROSS JOIN "cover_type"
WHERE "book"."cover_type_id"
= "cover_type"."id";
15

16.

SELECT *
FROM "book" INNER JOIN "cover_type"
ON "book"."cover_type_id"
= "cover_type"."id";
16

17.

SELECT *
FROM "user" INNER JOIN "reader"
ON "user"."id"
= "reader"."id";
17

18.

SELECT *
FROM "user" INNER JOIN "reader"
USING ("id");
18

19.

SELECT *
FROM "user" NATURAL INNER JOIN
"reader";
SELECT *
FROM "user" NATURAL JOIN "reader";
19

20.

SELECT *
FROM "user" LEFT JOIN "reader"
ON "user"."id"
= "reader"."id";
20

21.

SELECT MIN("year") FROM "book";
SELECT MAX("year") FROM "book";
SELECT AVG("year") FROM "book";
SELECT SUM("year") FROM "book";
21

22.

1. Год публикации самой ранней книги
каждого автора
2. Количество экземпляров каждой книги
3. Количество уникальных книг
22

23.

Год публикации самой ранней книги каждого
автора:
SELECT "author"."surname", "author"."name", MIN("book"."year") AS "year"
FROM ("author" INNER JOIN "author_vs_book" ON "author"."id"
= "author_vs_book"."author_id")
INNER JOIN "book" ON "author_vs_book"."book_id" = "book"."id"
GROUP BY "author"."surname", "author"."name";
23

24.

Количество экземпляров каждой книги:
SELECT "author"."surname", "author"."name", "book"."title",
"book"."year", COUNT("book_copy".*) AS "amount"
FROM (("author" INNER JOIN "author_vs_book" ON "author"."id"
= "author_vs_book"."author_id")
INNER JOIN "book" ON "author_vs_book"."book_id" = "book"."id")
LEFT JOIN "book_copy" ON "book"."id" = "book_copy"."book_id"
GROUP BY "author"."surname", "author"."name", "book"."title",
"book"."year";
24

25.

Количество уникальных книг:
SELECT "author"."surname", "author"."name", "book"."title",
"book"."year", COUNT("book_copy".*) AS "amount"
FROM (("author" INNER JOIN "author_vs_book" ON "author"."id"
= "author_vs_book"."author_id")
INNER JOIN "book" ON "author_vs_book"."book_id" = "book"."id")
LEFT JOIN "book_copy" ON "book"."id" = "book_copy"."book_id"
GROUP BY "author"."surname", "author"."name", "book"."title",
"book"."year"
HAVING COUNT("book_copy".*) = 1;
25