Skip to content

Instantly share code, notes, and snippets.

@jaybobo

jaybobo/curriculum.md

Last active February 3, 2018 03:08
Show Gist options
  • Save jaybobo/ca368321a4257a14a9d6a7fa1eabaf17 to your computer and use it in GitHub Desktop.
Save jaybobo/ca368321a4257a14a9d6a7fa1eabaf17 to your computer and use it in GitHub Desktop.
Download ZIP
SQL & Databases 101
Raw
curriculum.md

Here's the curriculum for the course. It's a good idea to bring a laptop to our biweekly sessions.

SQL & Databases Curriculum

  1. SQL basics
  2. Getting Help
  3. Using documentation
  4. Create a github.com account
    • Learn to use git
    • Put your files on your github page
  5. Connecting to databases & using SQLite
  6. Playing with datasets
Raw
final-sql-drill.md

These are the "flags" to complete in order to graduate from the SQL 101 course. Complete the following:

  • Find solutions to the questions below
  • Push your solutions and the SQL queries you used to get them to a github repository that you've created
  • When you're done, send Jay a link to the repository for review

Flags (Show us your queries)

  1. Which Quentin Tarantino movie has the most body counts?
  2. Who are the top 5 movie directors by total body count?
  3. What are the top three most violent movies?
  4. Which movie director has the least violent movies?
  5. What are the three most violent years in film?
  6. Have movies gotten more violent over the past 30+ years? Prove it.
  7. Are R-rated movies more violent compared to films rated G, PG, PG-13? Prove it.
  8. How many R-rated movies with a IMDB Rating greater than 6.5 are in our dataset?
  9. How many Jeopardy questions have been asked about cheese?
  10. How many Jeopardy questions have been asked about the years 1800 - 1965 (use their category titles)?
  11. According to Jeopardy which category of questions have more value - those relating to Ohio or Michigan?
  12. How many times has the same answer been used on Jeopardy?
  13. In what months have questions with the answer 'Dr. Seuss' or 'The Bible' been asked?
  14. Name the top 5 twitter users with the most tweets about #talkpay?
  15. How many talkpay tweeters tweeted from San Francisco? ;)
  16. How many organizations have an ownership stake in a Trump related business?
  17. What is the sum total of companies that have loaned money or own collateralized debt in a Trump affiliated company?
  18. How many organizations in our dataset are named after Trump?
  19. What is the sum total of Marvel characters with white or blond hair?
  20. How many Marvel characters do not have a secret identity?
  21. How many female characters are from Earth-616?
  22. List the top character by number of appearances created between 1980-2005
  23. How many bad characters have more appearances than Bullseye but less than Wilson Fisk?

Advanced (complete these if you dare; optional)

  1. What is the highest salary mentioned in a #talkpay tweet? (best guess)
  2. What is the average FY16 expenditure for rural schools?
  3. What are the top 5 urban school districts by FY16 expenditure?
  4. Which school district has spent the most in FY16?

Extra Credit

Present a few interesting "facts" from one of the datasets you've worked with.

Raw
getting-help.md

Working with software is hard so at some point you're going to need to give or get help. Treat others the way that you'd want to be treated in all spaces including Hipchat. Here's some tips to getting help.

Step 1: Check Documentation

Check documentation. If you're unsure of where key documentation is located or you've found the needed documentation but it's unclear (move to step 3).

REMEMBER: Timebox yourself. If you've investigated the issue for more than 30-45 minutes, it's time to ask someone.

Step 2: Google It

When in doubt use Google. If you still have nagging questions...

Step 3: Prepare your question

It's important to keep a few things in mind.

  • Explain in detail the problem you are experiencing (include a code snippet, stack trace, error logs, screenshot)
  • Explain in detail what you have tried to solve the problem
  • Explain in detail how what you tried did not work

Step 4: Ask Someone

As a developer, there's an ideal hierarchy for receiving helpful and quick responses to your questions.

  • Pair Partner
  • The right Hipchat room?
  • Use TeamDirectory to identify fellow employees
Raw
tasks.md

Tasks

For July 12th

For July 26th

For August 9th

Complete entire SQL Bolt exercises 1-18 & the "Codecademy - Learn The Command Line"

For August 23

Complete the drills below.

September 13th

September 27th

Importing data into SQLite databases.

  1. Install SQLite
  2. Use one of the tools below to work with the database.
  3. Import the filmdeathcounts.csv dataset into your database.
  4. Query your data and answer these questions:
    • Which Quentin Tarantino movie has the most body counts?
    • 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.
  5. Create a GitHub repository for film death counts.

INSTRUCTIONS: http://www.sqlitetutorial.net/download-install-sqlite/
SQLITE: https://sqlite.org/download.html
TOOLS: https://www.navicat.com/en/products/navicat-for-sqlite
TOOLS: https://sqlitestudio.pl/index.rvt
DATASET: https://drive.google.com/open?id=0B3Pt4JWeNUhVeWZ5MTVJdnRZVUE

October 25th

  1. Show & Tell (previous)
  2. Discuss Final Tasks

Final Tasks

Your job is to capture all of the SQL flags

  • Use your previously setup SQLite database or use MySQL
  • Import each dataset into different tables
  • Query the data to find the answers to these questions
  • Publish both your answers and SQL used to find the answers in a GitHub repository

MYSQL Download: https://dev.mysql.com/downloads/mysql/
DATASET: https://drive.google.com/open?id=0B3Pt4JWeNUhVMjdhT0gwdnBVTjg

Last Meetup

Date: November 29th

Show and tell what you've been able to accomplish from our final project.

  • What tools did you use to find your answers
  • What were your results
  • What was the most difficult part of the challenge
@BrigetteBarger
Copy link

Is there a dataset for our October 18th "homework"?

@jaybobo
Copy link
Author

jaybobo commented Oct 18, 2017

@BrigetteBarger Above, the date is when we meet and what we're discussing but its the homework for the following session.

The October 18th homework is the stuff from 9/27. Its a tad bit confusing.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment