SQLize Online / PHPize Online  /  SQLtest Online

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_book_name NUMBER; BEGIN SELECT COUNT(*) INTO l_book_name FROM Book; DBMS_OUTPUT.PUT_LINE('Общее количество книг: ' || l_book_name); END;