Posted over 3 years ago. Visible to the public.

SQL basics

SQL is the language used to talk to a database.

Goals

  • Understand what SQL does
  • Understand what "mySQL" and "PostgreSQL" are
    • Learn about some differences between the two
    • Understand why we use both at different projects
  • Be able to connect to a database
  • Understand the following commands
    • SELECT
      • WHERE
      • ORDER
      • INNER JOIN, LEFT JOIN
      • GROUP BY
      • LIMIT
    • UPDATE
    • INSERT and INSERT ... SELECT
    • DELETE
    • EXPLAIN
  • Understand what a "transaction" is
  • Understand what an "index" is
    • What is its purpose?
    • When can it be used?
    • What is a "multiple column index"? Does the order of the columns matter?
  • Understand how we authenticate to the database
  • Know how to get a dump from a staging system

Resources

Exercises

SQL console

Let someone help you clone the makandracards.com app.

Import the staging database into your local database.

Now do the following things in a database console:

  • Find the user table
  • Which columns does it have? Which indexes?
  • Change the name of your user
  • Add yourself to another deck
  • Find out how many users there are
  • Find out which user has received how many "thanks" ("tributes") with a single query
  • Now do the same query only for the "makandropedia" deck
  • Does this query use an index? If not, can you add one to speed it up?

Owner of this card:

Avatar
Henning Koch
Last edit:
almost 2 years ago
by Henning Koch
Posted by Henning Koch to makandra Curriculum
This website uses cookies to improve usability and analyze traffic.
Accept or learn more