Skip to content

Instantly share code, notes, and snippets.

@ezego1

ezego1/CS50.sql

Last active June 20, 2020 19:26
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ezego1/f5dfc4acea8a8d1ea102c0dc52edebd6 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ezego1/f5dfc4acea8a8d1ea102c0dc52edebd6 to your computer and use it in GitHub Desktop.
Download ZIP
Write SQL queries to answer questions about given database
Raw
Console_Game_Sales.sql
What percentage of global sales were made in North America?
SELECT round((sum(NA_Sales)/(sum(NA_Sales) + sum(EU_Sales) + sum(JP_Sales) +
sum(Other_Sales)) * 100), 2) Percentage_Of_NA_Glober_Sale
FROM ConsoleGames;
Show console game titles ordered by platform name in ascending order and year of release in descending order.
SELECT name
FROM ConsoleGames
ORDER BY Platform ASC, year DESC;
For each game title, extract the first four letters of the publisher's name.
SELECT name, substr(Publisher, 1, 4) Publisher
FROM ConsoleGames;
Show all console platforms released just before Black Friday or just before Christmas in any year.
SELECT Platform
FROM ConsoleDates
WHERE substr(FirstRetailAvailability, 6, 2) < 11 OR 12;
Order the platforms by longevity in ascending order (i.e platform that was available the longest at the bottom)
SELECT Platform
FROM ConsoleDates
ORDER BY substr(FirstRetailAvailability, 1, 4) DESC
Raw
Consumer_Complaints.sql
How many complaints were sent and received on the same day?
SELECT count(*)
FROM ConsumerComplaints
WHERE DateSenttoCompany = DateReceived;
Complaints received in the state of New York.
SELECT *
FROM ConsumerComplaints
WHERE StateName IS "NY";
Complaints received in the states of New York and California
SELECT *
FROM ConsumerComplaints
WHERE StateName IN ("NY", "CA");
Rows with the word "Credit" in the product field.
SELECT ProductName
FROM ConsumerComplaints
WHERE ProductName like "Credit %";
Rows with the word "Late" in the issue field.
SELECT Issue
FROM ConsumerComplaints
WHERE Issue Like "Late %";
Raw
CS50.sql
In 1.sql, write a SQL query to list the titles of all movies released in 2008.
select title
FROM movies
WHERE year = 2008;
In 2.sql, write a SQL query to determine the birth year of Emma Stone.
select birth
FROM people
WHERE name = 'Emma Stone';
In 3.sql, write a SQL query to list the titles of all movies with a release date on or after 2018, in alphabetical order.
select title
FROM movies
WHERE year >= 2018;
In 4.sql, write a SQL query to determine the number of movies with an IMDb rating of 10.0.
select count(*)
FROM movies
JOIN ratings
on movies.id =
ratings.movie_id
WHERE rating = 10;
In 5.sql, write a SQL query to list the titles and release years of all Harry Potter movies, in chronological order
select title, year
FROM movies
WHERE title like 'Harry Potter %'
ORDER by year ASC;
In 6.sql, write a SQL query to determine the average rating of all movies released in 2012
select round(avg(rating))
FROM ratings
JOIN movies
on ratings.movie_id =
movies.id
WHERE year = 2012;
In 7.sql, 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.
select title, year, rating
FROM ratings
JOIN movies
on ratings.movie_id =
movies.id
WHERE year = 2010
ORDER BY rating DESC, title;
In 8.sql, write a SQL query to list the names of all people who starred in Toy Story
WITH actor_name AS (SELECT name, movie_id
FROM people
JOIN stars
on people.id =
stars.person_id
)
SELECT actor_name.name
FROM actor_name
JOIN movies
on actor_name.movie_id =
movies.id
WHERE movies.title = "Toy Story";
In 9.sql, write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year
WITH actor_name AS (SELECT name, movie_id,
birth
FROM people
JOIN stars
on people.id =
stars.person_id
)
SELECT actor_name.name
FROM actor_name
JOIN movies
on actor_name.movie_id =
movies.id
WHERE movies.year = 2004
GROUP BY actor_name.name
ORDER BY actor_name.birth;
In 10.sql, 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.
WITH actor_name AS (SELECT name, movie_id
FROM people
JOIN directors
on people.id =
directors.person_id
)
SELECT actor_name.name
FROM actor_name
JOIN ratings
on actor_name.movie_id =
ratings.movie_id
WHERE rating >= 9.0;
In 11.sql, 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.
WITH actor_info AS (SELECT movie_id, person_id, title, name
FROM movies m INNER JOIN stars s
on m.id = s.movie_id INNER JOIN people p
on p.id = s.person_id
)
SELECT actor_info.name
from actor_info
JOIN ratings
on actor_info.movie_id =
ratings.movie_id
WHERE actor_info.name = "Chadwick Boseman"
ORDER BY rating DESC
LIMIT 5;
In 12.sql, write a SQL query to list the titles of all movies in which both Johnny Depp and Helena Bonham Carter starred
WITH actor_info AS (SELECT movie_id, person_id, title, name
FROM movies m INNER JOIN stars s
on m.id = s.movie_id INNER JOIN people p
on p.id = s.person_id
)
SELECT actor_info.title
from actor_info
JOIN ratings
on actor_info.movie_id =
ratings.movie_id
WHERE actor_info.name in ("Johnny Depp", "Helena Bonham Carter");
In 13.sql, write a SQL query to list the names of all people who starred in a movie in which Kevin Bacon also starred
SELECT DISTINCT(name)
FROM people
WHERE name IS NOT "Kevin Bacon"
AND id IN (
SELECT person_id
FROM stars
WHERE movie_id IN (
SELECT movie_id
FROM stars
WHERE person_id IN (
SELECT id
FROM people
WHERE name IS "Kevin Bacon"
and birth = 1958
)
)
);
Raw
Pets.sql
Information on pet names and owner names side by side.
SELECT Owners.Name Owner, Pets.Name Pet
from Owners
JOIN Pets
ON Owners.OwnerID =
Pets.OwnerID;
Match up all procedures performed to their descriptions
SELECT PetID, ProceduresHistory.Date, ProceduresHistory.ProcedureType ProcedureType,
Description, Price
from ProceduresHistory
JOIN ProceduresDetails
ON ProceduresHistory.ProcedureSubCode =
ProceduresDetails.ProcedureSubCode;
Most popular procedures and how much they cost.
SELECT PetID, ProceduresHistory.Date, count(ProceduresHistory.ProcedureType) Most_Occuring,
Description, Price
from ProceduresHistory
JOIN ProceduresDetails
ON ProceduresHistory.ProcedureSubCode =
ProceduresDetails.ProcedureSubCode
GROUP BY PetID
ORDER BY Most_Occuring DESC
LIMIT 4;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment