Advertisement
khuseiN

Untitled

May 19th, 2024
631
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.71 KB | None | 0 0
  1. --books--
  2. DROP TABLE IF EXISTS books cascade;
  3. CREATE TABLE books (
  4.     id SERIAL PRIMARY KEY,
  5.     name VARCHAR(255) NOT NULL
  6. );
  7.  
  8. INSERT INTO books (name) VALUES
  9. ('Война и мир'),
  10. ('Преступление и наказание'),
  11. ('Мастер и Маргарита'),
  12. ('Тихий дон'),
  13. ('Бойцовский Клуб');
  14.  
  15. --book_reg--
  16. DROP TABLE IF EXISTS book_reg cascade;
  17. CREATE TABLE book_reg (
  18.     id INT PRIMARY KEY,
  19.     DATE DATE NOT NULL
  20. );
  21.  
  22. INSERT INTO book_reg (id, DATE) VALUES
  23. (1, TO_DATE('01.05.2006', 'DD.MM.YYYY')),
  24. (3, TO_DATE('05.07.2004', 'DD.MM.YYYY')),
  25. (5, TO_DATE('30.09.2015', 'DD.MM.YYYY'));
  26.  
  27. --genres--
  28. DROP TABLE IF EXISTS genres cascade;
  29. CREATE TABLE genres (
  30.     id SERIAL PRIMARY KEY,
  31.     name VARCHAR(255) NOT NULL
  32. );
  33.  
  34. INSERT INTO genres (name) VALUES
  35. ('Русская классика'),
  36. ('Зарубежные романы'),
  37. ('Драма');
  38.  
  39. --book_genres--
  40. DROP TABLE IF EXISTS book_genres cascade;
  41. CREATE TABLE book_genres (
  42.     book_id INT NOT NULL,
  43.     genre_id INT NOT NULL,
  44.     UNIQUE (book_id, genre_id),
  45.     FOREIGN KEY (book_id) REFERENCES books (id),
  46.     FOREIGN KEY (genre_id) REFERENCES genres (id)
  47. );
  48.  
  49. INSERT INTO book_genres (book_id, genre_id) VALUES
  50. (1, 1),
  51. (1, 3),
  52. (2, 1),
  53. (3, 1),
  54. (4, 1),
  55. (5, 2),
  56. (5, 3);
  57.  
  58. --проверки
  59. SELECT * FROM books;
  60. SELECT * FROM book_reg;
  61. SELECT * FROM genres;
  62. SELECT * FROM book_genres;
  63.  
  64. --запрос
  65. SELECT g.name AS genre_name, COUNT(DISTINCT b.id) AS book_count
  66. FROM book_reg b_r
  67. JOIN books b ON b_r.id = b.id
  68. JOIN book_genres b_g ON b.id = b_g.book_id
  69. JOIN genres g ON b_g.genre_id = g.id
  70. WHERE b_r.DATE > TO_DATE('12.04.2003', 'DD.MM.YYYY')
  71. GROUP BY g.name
  72. ORDER BY book_count DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement