Skip to content

Instantly share code, notes, and snippets.

@coker-deca

coker-deca/ConsoleGame.sql

Last active July 9, 2020 08:36
Show Gist options
  • Save coker-deca/6a10803a12ff48db94285e3162604e91 to your computer and use it in GitHub Desktop.
Save coker-deca/6a10803a12ff48db94285e3162604e91 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Task
Raw
ConsoleGame.sql
SELECT round(sum(NA_Sales)/(sum(NA_Sales)+sum(EU_Sales)+sum(JP_Sales)+sum(Other_Sales))*100)
AS 'NA_Sales_Percentage'
FROM 'P9-ConsoleGames';
SELECT Name
FROM P9ConsoleGames
ORDER BY Platform ASC, Year DESC;
SELECT Platform, Year
FROM 'P9-ConsoleGames'
ORDER BY Name, Year DESC;
SELECT substr(Publisher, 1, 4)
AS "Publisher Name"
FROM 'P9-ConsoleGames';
SELECT Platform
FROM 'P9-ConsoleDates'
WHERE (strftime("%m",FirstRetailAvailability) = '11' AND strftime("%d",FirstRetailAvailability) >= '27')
OR (strftime("%m",FirstRetailAvailability) = '12' AND strftime("%d",FirstRetailAvailability) < '25');
SELECT Platform
AS 'Platform-Longevity'
FROM "P9-ConsoleDates" ORDER BY (Discontinued - FirstRetailAvailability) ASC;
Raw
customer_complaints.sql
SELECT count(*) FROM consumerComplaints
WHERE DateSenttoCompany = DateReceived;
SELECT * FROM ConsumerComplaints
WHERE StateName = "NY";
SELECT * FROM ConsumerComplaints
WHERE StateName = "NY" or StateName = "CA";
SELECT * FROM ConsumerComplaints
WHERE ProductName like "%credit%";
SELECT * FROM ConsumerComplaints
WHERE Issue like "%late%";
Raw
movies.sql
SELECT title FROM movies WHERE year=2008;
SELECT birth AS "Born In:" FROM people WHERE name="Emma Stone";
SELECT title FROM movies WHERE year >= 2018 ORDER BY title;
SELECT COUNT(*) AS "Higest Rated Movies" FROM ratings WHERE rating=10.0;
SELECT title, year FROM movies WHERE title = 'Harry Potter%' ORDER BY year;
SELECT AVG(rating) As "2012 Movies Ratings" FROM ratings
JOIN movies ON ratings.movie_id = movies.id
WHERE movies.year = 2012;
SELECT title, rating FROM movies
JOIN ratings ON movies.id=ratings.movie_id
WHERE movies.year=2010 AND ratings.rating
NOTNULL ORDER BY rating DESC, title ASC;
SELECT name FROM people
JOIN stars, movies ON stars.movie_id=movies.id AND stars.person_id = people.id
WHERE movies.title="Toy Story";
SELECT DISTINCT name from people
JOIN stars, movies ON stars.movie_id=movies.id AND stars.person_id=people.id
WHERE movies.year=2004
ORDER BY people.birth;
SELECT DISTINCT name FROM people
JOIN directors, ratings
ON directors.movie_id= ratings.movie_id
AND directors.person_id = people.id
WHERE ratings.rating>=9.0;
SELECT title FROM movies
JOIN stars, ratings, people
ON movies.id=stars.movie_id AND stars.person_id = people.id AND movies.id = ratings.movie_id
WHERE people.name="Chadwick Boseman"
ORDER BY ratings.rating DESC
LIMIT 5;
SELECT title FROM movies
JOIN stars, people
ON movies.id=stars.movie_id AND stars.person_id = people.id
WHERE people.name="Johnny Depp"
OR people.name = "Helena Bonham Carter"
GROUP BY title HAVING count(title)=2;
SELECT name FROM people
WHERE 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 = "Kevin Bacon" AND birth = 1958)))
AND name <> "Kevin Bacon";
Raw
Pets.txt
SELECT Pets.Name AS 'Pet Name', Owners.Name AS 'Owners Name', Owners.Surname AS 'Owner's Surname'
FROM Pets
JOIN Owners
ON Pets.OwnerID = Owners.OwnerID;
SELECT 'P9-ProceduresHistory'.ProcedureType, 'P9-ProceduresDetails'.Description
FROM 'P9-ProceduresHistory'
JOIN 'P9-ProceduresDetails'
ON 'P9-ProceduresHistory'.ProcedureSubCode = 'P9-ProceduresDetails'.ProcedureSubCode
SELECT 'P9-ProceduresHistory'.ProcedureType AS Procedure, 'P9-ProceduresDetails'.price
FROM 'P9-ProceduresHistory'
JOIN 'P9-ProceduresDetails'
ON 'P9-ProceduresHistory'.ProcedureSubCode = 'P9-ProceduresDetails'.ProcedureSubCode
GROUP BY Procedure
ORDER BY count(Procedure) DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment