CONNECT BY PRIOR akz_id = akz_id;
CREATE TABLE Avt(
avt_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
avt_name VARCHAR2(50) NOT NULL
);
CREATE TABLE Seria(
seria_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
seria_name VARCHAR2(50) NOT NULL,
kolvo NUMBER(3)
);
CREATE TABLE Book(
book_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
book_name VARCHAR2(50) NOT NULL,
avt_id NUMBER,
seria_id NUMBER,
parent_book_id NUMBER,
CONSTRAINT fk_avt
FOREIGN KEY (avt_id)
REFERENCES avt (avt_id)
ON DELETE CASCADE,
CONSTRAINT fk_seria
FOREIGN KEY (seria_id)
REFERENCES seria (seria_id)
ON DELETE SET NULL,
CONSTRAINT fk_parent_book
FOREIGN KEY (parent_book_id)
REFERENCES book (book_id)
ON DELETE SET NULL
);
CREATE TABLE Akz (
akz_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
book_id NUMBER,
akz_name VARCHAR2(50),
year DATE,
CONSTRAINT fk_book
FOREIGN KEY (book_id)
REFERENCES book (book_id)
ON DELETE CASCADE
);
INSERT INTO Avt (avt_name) VALUES ('Макс Фрай');
INSERT INTO Avt (avt_name) VALUES ('Мэй ЛИ');
INSERT INTO Seria (seria_name, kolvo) VALUES ('Лабиринты Ехо', 8);
INSERT INTO Seria (seria_name, kolvo) VALUES ('Профайлер', 3);
INSERT INTO Book (book_name, avt_id, seria_id, parent_book_id)
VALUES ('Чужак', 1, 1, NULL);
INSERT INTO Book (book_name, avt_id, seria_id, parent_book_id)
VALUES ('Волонтёры вечности', 1, 1, 1);
INSERT INTO Book (book_name, avt_id, seria_id, parent_book_id)
VALUES ('Профайлер', 2, 2, NULL);
INSERT INTO Akz (book_id, akz_name, year)
VALUES (1, 'Чужай1', DATE '2008-12-21');
INSERT INTO Akz (book_id, akz_name, year)
VALUES (1, 'Чужак2', DATE '2009-12-21');
INSERT INTO Akz (book_id, akz_name, year)
VALUES (2, 'Волонтёры вечности1', DATE '2010-12-21');
INSERT INTO Akz (book_id, akz_name, year)
VALUES (3, 'Профайлер1', DATE '2020-12-21');
SELECT * FROM Avt;
SELECT * FROM Seria;
SELECT * FROM Book;
SELECT * FROM Akz;
SELECT * FROM Akz
WHERE year > DATE '2020-01-01';
SELECT * FROM Book
WHERE avt_id IN (2, 3);
SELECT * FROM Book
WHERE avt_id NOT IN (2);
SELECT * FROM Akz
WHERE year BETWEEN DATE '2000-01-01' AND DATE '2010-01-01';
SELECT * FROM Akz
WHERE year > ANY (SELECT year FROM Akz WHERE akz_id = 2);
SELECT * FROM Akz
WHERE year > ALL (SELECT year FROM Akz WHERE akz_id IN (1, 2, 3));
SELECT * FROM Book
WHERE parent_book_id IS NULL;
SELECT * FROM Book
WHERE parent_book_id IS NOT NULL;
SELECT * FROM Avt a
WHERE EXISTS (SELECT 1 FROM Book b WHERE b.avt_id = a.avt_id);
SELECT
SUBSTR(avt_name, 1, INSTR(avt_name, ' ') - 1) AS avt_name,
SUBSTR(avt_name, INSTR(avt_name, ' ') + 1) AS avt_name2
FROM Avt;
INSERT INTO Book (book_name, avt_id, seria_id, parent_book_id)
VALUES ('Профайлер123', NULL, NULL, NULL);
INSERT INTO Avt (avt_name) VALUES ('Нил Гейман');
SELECT a.avt_name AS a_name, b.book_name AS b_name
FROM Avt a
LEFT JOIN Book b ON a.avt_id = b.avt_id;
SELECT a.avt_name AS a_name, b.book_name AS b_name
FROM Avt a
RIGHT JOIN Book b ON a.avt_id = b.avt_id;
SELECT a.avt_name AS a_name, b.book_name AS b_name
FROM Avt a
FULL JOIN Book b ON a.avt_id = b.avt_id;
SELECT a.avt_name AS a_name, b.book_name AS b_name
FROM Avt a
CROSS JOIN Book b;
SELECT Book.book_name, Avt.avt_name
FROM Book
INNER JOIN Avt ON Book.Avt_id = Avt.Avt_id;
SELECT a.avt_name AS a_name, b.book_name AS b_name
FROM Avt a
FULL OUTER JOIN Book b ON a.avt_id = b.avt_id
ORDER BY a_name ASC, b_name ASC;
SELECT a.avt_name AS a_name, b.book_name AS b_name
FROM Avt a
LEFT JOIN Book b ON a.avt_id = b.avt_id
ORDER BY a.avt_name DESC;
SELECT avt_id, COUNT(*) AS BookCount
FROM Book
GROUP BY avt_id
HAVING COUNT(*) > 1;
SELECT * FROM Avt a
WHERE EXISTS (
SELECT 1 FROM Book b WHERE b.avt_id = a.avt_id
);
SELECT book_id, book_name, parent_book_id
FROM Book
START WITH parent_book_id IS NULL
CONNECT BY PRIOR book_id = parent_book_id;
SELECT LEVEL, book_name, SYS_CONNECT_BY_PATH(book_name, '/') AS path
FROM Book
START WITH parent_book_id IS NULL
CONNECT BY PRIOR book_id = parent_book_id
ORDER SIBLINGS BY book_name;
SELECT LEVEL, a.avt_name AS avt_name, b.book_name AS book_name, c.akz_id
FROM Avt a
JOIN Book b ON a.avt_id = b.avt_id
JOIN Akz c ON b.book_id = c.book_id
START WITH a.avt_id = 1
CONNECT BY PRIOR b.book_id = c.book_id
AND PRIOR c.akz_id IS NOT NULL;
SELECT COUNT(*) AS TotalBooks FROM Book;
SELECT MAX(year) AS LatestPublication FROM Akz;
WITH BookCounts AS (
SELECT avt_id, COUNT(*) AS total_books
FROM Book
GROUP BY avt_id
),
RecentReleases AS (
SELECT book_id, MAX(year) AS last_release
FROM Akz
GROUP BY book_id
)
SELECT b.book_name, bc.total_books, rr.last_release
FROM Book b
JOIN BookCounts bc ON b.avt_id = bc.avt_id
JOIN RecentReleases rr ON b.book_id = rr.book_id;
SELECT avt_id, book_name,
FIRST_VALUE(book_name) OVER (PARTITION BY avt_id ORDER BY book_id) AS first_book,
LAST_VALUE(book_name) OVER (PARTITION BY avt_id ORDER BY book_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_book,
COUNT(*) OVER (PARTITION BY avt_id) AS total_books
FROM Book
ORDER BY avt_id, book_id;
DECLARE
l_total_books NUMBER;
BEGIN
SELECT COUNT(*) INTO l_total_books FROM Book;
DBMS_OUTPUT.PUT_LINE('Общее количество книг: ' || l_total_books);
END;
/
Telegram: DB developers channel