Posted about 6 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 how we connect to a database:
    • The database runs on a database server, usually on another machine. For local development, the server is your PC.
    • To speak to the server, you need a local client for MySQL or PostgreSQL installed on your system.
    • To connect to a server, the client needs the server's hostname, a username and a password.
    • You Rails app includes a gem that serves as a database client. It also needs a hostname, username and password to connect to. It takes this information from config/database.yml.
    • If you would like to open a database shell without re-entering the credentials in your database.yml, you can run rails dbconsole.
  • 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?
  • 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. (find geordi documentation)

Start the server and sign up as a new user.

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:
7 months ago
by Klaus Weidinger
Posted by Henning Koch to makandra Curriculum
This website uses short-lived cookies to improve usability.
Accept or learn more