The purpose of this challenge is to learn and/or improve your SQL skills. SQL is an important tool used for creating, querying and manipulating data. It's used often by software developers, product owners, data analysts, data scientists, database administrators and scores of other roles.
For those new to SQL you have a number of online courses and tutorials to select from in order to level up your knowledge.
- https://www.pgexercises.com
- https://sqlbolt.com
- https://khanacademy.org/computing/computer-programming/sql
- http://sqlzoo.net/
The challenge is to capture a number of "flags" by importing the datasets we've provided and finding answers to the questions below. You'll need to do the following.
- Clean up your data (if needed)
- Create a database (we recommend SQLite as a first step)
- Import your dataset
- Find the flags!
- How to install SQLite
- Where to download SQLite
- Use one of the database viewers below to make life easier or find your own with Google
You'll find all datasets for this drill at the following link:
Feel free to hop around. Some questions are much harder to answer then others. Be sure to save your queries so you can show us how you got your answer.
- Which Quentin Tarantino movie has the most body counts?
- Who are the top 5 movie directors by total body count?
- What are the top three most violent movies?
- Which movie director has the least violent movies?
- What are the three most violent years in film?
- Have movies gotten more violent over the past 30+ years? Prove it.
- Are R-rated movies more violent compared to films rated G, PG, PG-13? Prove it.
- How many R-rated movies with a IMDB Rating greater than 6.5 are in our dataset?
- How many Jeopardy questions have been asked about cheese?
- How many Jeopardy questions have been asked about the years 1800 - 1965 (use their category titles)?
- According to Jeopardy which category of questions have more value - those relating to Ohio or Michigan?
- How many times has the same answer been used on Jeopardy?
- In what months have questions with the answer 'Dr. Seuss' or 'The Bible' been asked?
- Name the top 5 twitter users with the most tweets about #talkpay?
- What is the highest salary mentioned in a #talkpay tweet? (best guess)
- How many talkpay tweeters tweeted from San Francisco? ;)
- How many organizations have an ownership stake in a Trump related business?
- What is the sum total of companies that have loaned money or own collateralized debt in a Trump affiliated company?
- How many organizations in our dataset are named after Trump?
- What is the average FY16 expenditure for rural schools?
- What are the top 5 urban school districts by FY16 expenditure?
- Which school district has spent the most in FY16?
- What is the sum total of Marvel characters with white or blond hair?
- How many Marvel characters do not have a secret identity?
- How many female characters are from Earth-616?
- List the top character by number of appearances created between 1980-2005
- How many bad characters have more appearances than Bullseye but less than Wilson Fisk?
Present a few interesting "facts" from one of the datasets you've worked with today.