SOAL LATIHAN SQL
1. songs.sql
A. write a SQL query to list the names of all songs in the
database.(tuliskan sintak SQL untuk menampilkan seluruh judul lagu
dalam database songs.sql)
SELECT name FROM songs;
B. write a SQL query to list the names of all songs in increasing order of
tempo.
SELECT name FROM songs ORDER BY tempo;
C. write a SQL query to list the names of the top 5 longest songs, in descending
order of length.
SELECT name FROM songs ORDER BY duration_ms DESC LIMIT 5;
D. write a SQL query that lists the names of any songs that have danceability,
energy, and valence greater than 0.75.
SELECT name FROM songs WHERE danceability > 0.75 AND energy > 0.75 AND
valence > 0.75;
E. write a SQL query that returns the average energy of all the songs.
SELECT AVG(energy)FROM songs;
F. write a SQL query that lists the names of songs that are by Post Malone.
SELECT name FROM songs WHERE artist_id =
(
SELECT id FROM artists WHERE name = 'Post Malone'
);
G. write a SQL query that returns the average energy of songs that are by Drake.
SELECT AVG(energy) FROM songs JOIN artists ON songs.artist_id = artists.id
WHERE artists.name = 'Drake';
H. write a SQL query that lists the names of the songs that feature other
artists.
SELECT name FROM songs WHERE name LIKE '%feat%';
2. movies.sql
A. write a SQL query to list the titles of all movies released in 2008.
(tuliskan sintak SQL untuk menmapilkan seluruh judul film yang
dirilis/diterbitkan di tahun 2008)
SELECT title FROM movies WHERE year = 2008;
B. write a SQL query to determine the birth year of Emma Stone
SELECT birth FROM people WHERE name LIKE 'Emma Stone';
C. write a SQL query to list the titles of all movies with a release date on or
after 2018, in alphabetical order.
D. write a SQL query to determine the number of movies with an IMDb rating of 10.0.
E. write a SQL query to list the titles and release years of all Harry Potter movies, in chronological
order.
F. write a SQL query to determine the average rating of all movies released in 2012
G. write a SQL query to list all movies released in 2010 and their ratings, in descending order by rating.
For movies with the same rating, order them alphabetically by title.
H. write a SQL query to list the names of all people who starred in Toy Story
I. write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by
birth year.
J. write a SQL query to list the names of all people who have directed a movie that received a rating of
at least 9.0.
K. write a SQL query to list the titles of the five highest rated movies (in order) that Chadwick
Boseman starred in, starting with the highest rated.
L. write a SQL query to list the titles of all movies in which both Bradley Cooper and Jennifer
Lawrence starred
M. write a SQL query to list the names of all people who starred in a movie in which Kevin Bacon also
starred
