SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Author ( SSN int, name varchar(30), age int, country varchar(30) ); CREATE TABLE Book ( ISBN int, title varchar(30), sold_copies int, price int, publisher varchar(30) ); CREATE TABLE Writes ( SSN int, ISBN int, year int, income int ); INSERT INTO Author VALUES (0, 'Ege', 5, 'Turkey'); INSERT INTO Author VALUES (1, 'Selen', 10, 'Arnavut'); INSERT INTO Author VALUES (2, 'Nira', 3, 'Turkey'); INSERT INTO Author VALUES (3, 'Demir', 4, 'America'); INSERT INTO Author VALUES (4, 'Yağız', 12, 'Germany'); INSERT INTO Author VALUES (5, 'Bahar', 30, 'Turkey'); INSERT INTO Author VALUES (6, 'Eda', 27, 'Russia'); INSERT INTO Author VALUES (7, 'Turgut', 33, 'Turkey'); INSERT INTO Author VALUES (8, 'Atahan', 7, 'Budapeste'); INSERT INTO Author VALUES (9, 'Ata', 62, 'Germany'); INSERT INTO Book VALUES (0, 'Huzursuzluk', 10, 62, 'Springer'); INSERT INTO Book VALUES (1, 'Cehennem', 5, 53, 'Prentice-Hall'); INSERT INTO Book VALUES (2, 'Cennet', 9, 32, 'Springer'); INSERT INTO Book VALUES (3, 'Ataturk', 12, 14, 'Springer'); INSERT INTO Book VALUES (4, 'Erdogan', 13, 15, 'Prentice-Hall'); INSERT INTO Book VALUES (5, 'Camlik', 17, 18, 'Prentice-Hall'); INSERT INTO Book VALUES (6, 'Social Sciences', 19, 25, 'Springer'); INSERT INTO Book VALUES (7, 'Mathematics', 18, 30, 'Prentice-Hall'); INSERT INTO Book VALUES (8, 'Physics', 20, 40, 'Springer'); INSERT INTO Book VALUES (9, 'Computer Sciences', 21, 70, 'Prentice-Hall'); INSERT INTO Book VALUES (10, 'Genetics', 32, 60, 'Springer'); INSERT INTO Book VALUES (11, 'Business', 53, 50, 'Prentice-Hall'); INSERT INTO Writes VALUES (0, 0, 2020, 400); INSERT INTO Writes VALUES (0, 1, 2020, 500); INSERT INTO Writes VALUES (0, 2, 2020, 600); INSERT INTO Writes VALUES (1, 2, 2013, 300); INSERT INTO Writes VALUES (1, 3, 2003, 200); INSERT INTO Writes VALUES (2, 4, 2020, 100); INSERT INTO Writes VALUES (3, 5, 2021, 1000); INSERT INTO Writes VALUES (4, 6, 2008, 700); INSERT INTO Writes VALUES (5, 6, 2008, 800); INSERT INTO Writes VALUES (6, 7, 2005, 900); INSERT INTO Writes VALUES (6, 8, 2001, 600); INSERT INTO Writes VALUES (6, 9, 2002, 500); INSERT INTO Writes VALUES (7, 9, 2012, 400); INSERT INTO Writes VALUES (8, 10, 2017, 200); INSERT INTO Writes VALUES (8, 11, 2016, 300); INSERT INTO Writes VALUES (9, 6, 2020, 350); INSERT INTO Writes VALUES (9, 5, 2020, 120); INSERT INTO Writes VALUES (9, 4, 2020, 540); with T(SSN, name, cnt) as ( select SSN, name, count(ISBN) as cnt from ((Author inner join Book) natural join Writes) where country = 'Turkey' and year between 2010 and 2020 group by SSN, name ) select SSN, name from T where cnt >= 3; with T(ISBN, title, cnt) as ( select ISBN, title, count(SSN) as cnt from ((Author inner join Book) natural join Writes) group by ISBN, title ) select ISBN, title from T where cnt = 1; select avg(price) as avgPrice from ((Author inner join Book) natural join Writes) where age < 30 and year = 2020 group by ISBN; with T(SSN, cnt, totalIncome) as ( select SSN, count(ISBN) as cnt, sum(income) as totalIncome from ((Author inner join Book) natural join Writes) where year = 2020 group by SSN having cnt >= 3 and totalIncome > 100 ) select SSN, name, age, country, totalIncome from Author natural join T;