Databases

indexes, primary and foreign keys, tables, functions, queries, joins, schemas

Databases allow us to store and manipulate data. SQL is at the heart of querying data in databases.

Consider using the apply first, study second approach with the resources below: try a problem or exercise from the Apply It section first. If you get stuck, then use a course, book, or lecture in the Reference section. Or if you’re completely new to a topic, then learn just enough and apply right away. The point is: the quicker you get to the “doing,” the better the learning. Make your learning active.

Apply It

SQL on Execute Program is the place to start, no matter the level. If you’re new to SQL, this course is a great introduction to the language. If you’re a veteran, you’ll really nail the fundamentals. Keep in mind that basic JavaScript knowledge is needed for this course.

You’re given just enough information about primary keys, for example, then you’ve got to put that knowledge to use before adding more to it. This course (like their others) is also review-heavy: you’ll get a number of short exercises to complete in order to review previously learned topics before moving on to new lessons.

Tip: Write your SQL query incrementally, instead of writing everything at once. This is a great way to test your assumptions and prevent errors.

PostgreSQL Exercises is a free platform that’ll help you learn and stay sharp with SQL. This exercise-based platform ranges in difficulty, from basic select and where clause queries to more complex queries that involve joins and case statements. It also covers aggregation and recursion.

PostgreSQL also provides the solution and explanation for each exercise. So once you complete an exercise, scroll to the end of the page to learn more. This is a useful website to bookmark and return to periodically, as you progress with SQL.

Disc 12 is a free problem set from Berkeley’s CS 61A course: Structure and Interpretation of Computer Programs. The problems are quality and worth your time. If you’re new to SQL, then you may want to try out the previously mentioned resources first.

Note: For Disc 12, you can test the queries you write. Just click Start SQL Interpreter. The tables you need to complete the queries in Disc 12 have already been added.

Reference

You’ll really gain a solid understanding of SQL by using the above resources. That’s because the SQL course on Execute Program and PostgreSQL blend applying and learning simultaneously. Disc 12 will give you more practice. However, if you get stuck, then reference the PostgreSQL documentation, particularly its tutorial.

If you prefer video lectures, then check out this SQL video lecture from Berkeley’s CS 61A: Structure and Interpretation of Computer Programs course. You may also find these lecture series, also from CS 61A:

• Tables
• Aggregation
• Databases

Note: The above links contain a playlist of lectures. For example, “Tables” links to a playlist of lectures on tables. Use the apply first, study second approach and focus on the precise thing you need. Then go back to your query and apply it. So instead of watching all of the lectures on “tables,” find the lecture that tells you how to drop a table, for instance, then apply what you learn.

There are several free articles by thoughtbot that are useful to keep on hand when working with SQL:

• Understanding SQL GROUP BY
• Understanding SQL SELECT and WHERE
• Understanding SQL JOIN

Or check out this thoughtbot article that offers an overview for writing SQL queries.