SQL basics [2d]
SQL is the language used to talk to a database.
- 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
INSERT ... SELECT
- 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
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?
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.