SQLize Online / PHPize Online  /  SQLtest Online

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 COUNT(*) AS TotalBooks FROM Book; SELECT MAX(year) AS LatestPublication FROM Akz; CREATE OR REPLACE TRIGGER trg_after_book_update AFTER UPDATE ON Book FOR EACH ROW BEGIN IF :OLD.book_name <> :NEW.book_name THEN DBMS_OUTPUT.PUT_LINE('Book title was updated from ' || :OLD.book_name || ' to ' || :NEW.book_name); END IF; END trg_after_book_update;