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 CountrySongCount AS ( SELECT a.country_id, COUNT(s.id) AS Total_Songs FROM Artist a JOIN Song s ON a.id = s.artist_id GROUP BY a.country_id ) SELECT c.name AS Country_Name, csc.Total_Songs FROM Country c LEFT JOIN CountrySongCount csc ON c.id = csc.country_id; WITH ArtistSongCount AS ( SELECT s.artist_id, COUNT(*) AS Song_Count FROM Song s GROUP BY s.artist_id ) SELECT a.name AS Artist_Name, ascc.Song_Count FROM Artist a JOIN ArtistSongCount ascc ON a.id = ascc.artist_id WHERE ascc.Song_Count > 1; WITH ArtistGenres AS ( SELECT artist_id, COUNT(DISTINCT genre_id) AS Genre_Count FROM Song GROUP BY artist_id ), ArtistsByCountry AS ( SELECT a.id AS Artist_ID, a.name AS Artist_Name, c.name AS Country_Name FROM Artist a JOIN Country c ON a.country_id = c.id ) SELECT abc.Artist_Name, abc.Country_Name FROM ArtistsByCountry abc JOIN ArtistGenres ag ON abc.Artist_ID = ag.artist_id WHERE ag.Genre_Count > 1; SELECT c.name, a.name, CONNECT_BY_ISCYCLE AS Is_Cycle FROM Country c JOIN Artist a ON c.id = a.country_id CONNECT BY NOCYCLE PRIOR c.id = a.id START WITH c.id IS NOT NULL; ALTER TABLE Song ADD (release_date DATE); UPDATE Song SET release_date = TO_DATE('2021-01-01', 'YYYY-MM-DD') WHERE id = 1; UPDATE Song SET release_date = TO_DATE('2022-06-15', 'YYYY-MM-DD') WHERE id = 2; UPDATE Song SET release_date = TO_DATE('2023-05-12', 'YYYY-MM-DD') WHERE id = 3; SELECT name, release_date FROM Song WHERE release_date > TO_DATE('2022-06-01', 'YYYY-MM-DD'); ALTER TABLE Artist ADD full_name VARCHAR2(100); UPDATE Artist SET full_name = 'Dante Terrell Smith' WHERE id = 1; UPDATE Artist SET full_name = 'Maurizio Pisciottu' WHERE id = 2; UPDATE Artist SET full_name = 'David Alvarez' WHERE id = 3; UPDATE Artist SET full_name = 'Gregory GoldFord' WHERE id = 4; UPDATE Artist SET full_name = 'Giorgia Todrani' WHERE id = 5; UPDATE Artist SET full_name = 'Giusy Ferreri' WHERE id = 6; UPDATE Artist SET full_name = 'Allen Stone' WHERE id = 7; UPDATE Artist SET full_name = 'Wasalu Muhammad Jaco' WHERE id = 8; UPDATE Artist SET full_name = 'Cem Karaca' WHERE id = 9; SELECT full_name, SUBSTR(full_name, INSTR(full_name, ' ', -1) + 1) AS last_name FROM Artist; SELECT full_name, SUBSTR(full_name, 1, INSTR(full_name, ' ') - 1) AS first_name FROM Artist; SELECT full_name, INSTR(full_name, 'Gold') AS position FROM Artist WHERE INSTR(full_name, 'Gold') > 0; ALTER TABLE Artist ADD first_name VARCHAR2(50); ALTER TABLE Artist ADD last_name VARCHAR2(50); UPDATE Artist SET first_name = SUBSTR(full_name, 1, INSTR(full_name, ' ') - 1); UPDATE Artist SET last_name = SUBSTR(full_name, INSTR(full_name, ' ', -1) + 1); SELECT id, full_name, first_name, last_name FROM Artist; --agr SELECT AVG(age) as avg_age FROM Artist; --analytical SELECT name, age, RANK() OVER (ORDER BY age DESC) AS age_ranks FROM Artist; SELECT name, genre_id, COUNT(*) OVER(PARTITION BY genre_id) AS songs_in_g FROM Song; SELECT name, release_date, FIRST_VALUE(name) OVER (PARTITION BY genre_id ORDER BY release_date ASC) AS first_song, LAST_VALUE(name) OVER (PARTITION BY genre_id ORDER BY release_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_song FROM Song; SELECT song_id,name, artist_id, FIRST_VALUE(name) OVER (PARTITION BY artist_id ORDER BY song_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_song, LAST_VALUE(name) OVER (PARTITION BY artist_id ORDER BY song_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_song FROM Song;