SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Country ( id NUMBER(10) PRIMARY KEY, name VARCHAR2(50) NOT NULL, capital VARCHAR2(50) NOT NULL ); CREATE TABLE Genre ( id NUMBER(10) PRIMARY KEY, name VARCHAR2(50) NOT NULL ); CREATE TABLE Artist( id NUMBER(10), name VARCHAR2(50) NOT NULL, age NUMBER(3), country_id NUMBER(10), city VARCHAR2(50), FOREIGN KEY (country_id) REFERENCES Country(id) ); ALTER TABLE Artist ADD CONSTRAINT students_id_pk PRIMARY KEY (id); CREATE TABLE Song( id NUMBER(10) PRIMARY KEY, name VARCHAR2(100) NOT NULL, artist_id NUMBER(10), genre_id NUMBER(10), FOREIGN KEY (genre_id) REFERENCES Genre(id), FOREIGN KEY (artist_id) REFERENCES Artist(id) ); INSERT INTO Country (id, name, capital) VALUES (1, 'Italy', 'Rome'); INSERT INTO Country (id, name, capital) VALUES (2, 'Spain', 'Madrid'); INSERT INTO Country (id, name, capital) VALUES (3, 'France', 'Paris'); INSERT INTO Country (id, name, capital) VALUES (4, 'USA', 'Washington D.C'); INSERT INTO Country (id, name, capital) VALUES (5, 'Ireland', 'Dublin'); INSERT INTO Artist (id, name, age, country_id, city) VALUES (1, 'Mos Def', 43, 4, 'New York'); INSERT INTO Artist (id, name, age, country_id, city) VALUES (2, 'Salmo', 40, 1, 'Milan'); INSERT INTO Artist (id, name, age, country_id, city) VALUES (3, 'DNash', 41, 2, 'Valencia'); INSERT INTO Artist (id, name, age, country_id, city) VALUES (4, 'GoldFord', 34, 4, 'Charlotte'); INSERT INTO Artist (id, name, age, country_id, city) VALUES (5, 'Giorgia',46 , 1, 'Rome'); INSERT INTO Artist (id, name, age, country_id, city) VALUES (6, 'Giusy Ferreri', 43, 1, 'Florence'); INSERT INTO Artist (id, name, age, country_id, city) VALUES (7, 'Allen Stone', 33, 4, 'San Jose'); INSERT INTO Artist (id, name, age, country_id, city) VALUES (8, 'Lupe Fiasco', 34, 4, 'Chicago'); INSERT INTO Artist (id, name, age, country_id, city) VALUES (9, 'Can', 54, 4, 'Dallas'); INSERT INTO Genre (id, name) VALUES (1, 'hip-hop'); INSERT INTO Genre (id, name) VALUES (2, 'indie'); INSERT INTO Genre (id, name) VALUES (3, 'pop'); INSERT INTO Genre (id, name) VALUES (4, 'rap'); INSERT INTO Song(id, name, artist_id, genre_id) VALUES (1, 'Che ne so', 2, 4); INSERT INTO Song(id, name, artist_id, genre_id) VALUES (2, 'Hip-hop', 1, 1); INSERT INTO Song(id, name, artist_id, genre_id) VALUES (3, 'Loco', 3, 3); INSERT INTO Song(id, name, artist_id, genre_id) VALUES (4, 'Auditorium', 1, 4); INSERT INTO Song(id, name, artist_id, genre_id) VALUES (5, 'Novembre', 6, 3); INSERT INTO Song(id, name, artist_id, genre_id) VALUES (6, 'Come pensi', 6, 3); INSERT INTO Song(id, name, artist_id, genre_id) VALUES (7, 'Mystery', 7, 3); INSERT INTO Song(id, name, artist_id, genre_id) VALUES (8, 'Memory of you', 7, 3); INSERT INTO Song(id, name, artist_id, genre_id) VALUES (9, 'Strong', 7, 3); Select * from Country; Select * from Artist; Select * from Genre; Select * from Song; Select a.name,a.age, c.name From Artist a, Country C Where a.age>35 and a.country_id = c.id; SELECT * FROM Artist WHERE age = 43; SELECT name FROM Country WHERE name IN ('Italy', 'USA'); SELECT name FROM Genre WHERE id NOT IN (1, 3); SELECT name AS Artist_Name, age FROM Artist ORDER BY age DESC; SELECT name AS Song_Name, genre_id FROM Song ORDER BY name ASC; SELECT * FROM Artist WHERE age > ANY (SELECT age FROM Artist WHERE country_id = 1); SELECT name FROM Artist WHERE age BETWEEN 30 AND 40; SELECT * FROM Song WHERE NOT genre_id = 3; SELECT name AS Country_Name FROM Country WHERE EXISTS ( SELECT 1 FROM Artist WHERE Artist.country_id = Country.id AND Artist.age < 40 ); SELECT c.name AS Country_Name, COUNT(a.id) AS Artist_Count FROM Country c LEFT JOIN Artist a ON c.id = a.country_id WHERE NOT EXISTS ( SELECT 1 FROM Artist WHERE Artist.country_id = c.id ) GROUP BY c.name ORDER BY c.name ASC; SELECT * FROM Song WHERE artist_id IS NULL; SELECT Artist.name AS Artist_Name, Country.name AS Country_Name FROM Artist INNER JOIN Country ON Artist.country_id = Country.id; SELECT Artist.name AS Artist_Name, Country.name AS Country_Name FROM Artist LEFT JOIN Country ON Artist.country_id = Country.id; SELECT Artist.name AS Artist_Name, Country.name AS Country_Name FROM Artist RIGHT JOIN Country ON Artist.country_id = Country.id; SELECT Artist.name AS Artist_Name, Country.name AS Country_Name FROM Artist RIGHT JOIN Country ON Country.id BETWEEN 2 AND 4 AND Artist.country_id = Country.id; SELECT Artist.name AS Artist_Name, Country.name AS Country_Name FROM Artist FULL OUTER JOIN Country ON Artist.country_id = Country.id; SELECT Artist.name AS Artist_Name, Genre.name AS Genre_Name FROM Artist CROSS JOIN Genre; SELECT c.name, COUNT(*) AS Artist_Count FROM Artist a, Country c WHERE a.country_id = c.id GROUP BY c.name; SELECT c.name, COUNT(*) AS Artist_Count FROM Artist a, Country c WHERE a.country_id = c.id GROUP BY c.name HAVING COUNT(*) > 2; SELECT MAX(age) AS Oldest_Artist_Age FROM Artist; SELECT MIN(age) AS Youngest_Artist_Age FROM Artist; SELECT c.name AS Country_Name, g.name AS Genre_Name, COUNT(s.id) AS Total_Songs FROM Country c JOIN Artist a ON c.id = a.country_id JOIN Song s ON a.id = s.artist_id JOIN Genre g ON s.genre_id = g.id WHERE g.name IN ('rap', 'pop') GROUP BY c.name, g.name ORDER BY c.name ASC, g.name ASC; WITH ArtistSongCount AS ( SELECT artist_id, COUNT(*) AS Song_Count FROM Song GROUP BY artist_id ) SELECT a.name AS Artist_Name, asc.Song_Count FROM Artist a JOIN ArtistSongCount asc ON a.id = asc.artist_id WHERE asc.Song_Count > 2;