Posted about 4 years ago. Visible to the public.

SQL basics [2d]

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?

PostgreSQL conversion

Your MovieDB application probably uses SQLite, which is the default database chosen by Rails. However, SQLite is rarely used in production apps.

Change your MovieDB application so it uses PostgreSQL instead.

Owner of this card:

Avatar
Henning Koch
Last edit:
19 days ago
by Hannes Randow
Posted by Henning Koch to makandra Curriculum
This website uses cookies to improve usability and analyze traffic.
Accept or learn more