Summary and Additional Resources

Summary

Our main goal in this book was to introduce you to the SQL language and to get you ready for our course, SQL and Relational Databases. In order to do that, we wanted to present some of the key concepts related to working with SQL and help you build strong mental models around those concepts.

Getting Started

We started off looking at things from a conceptual level, talking about why data is important, comparing unstrutured and structured data, and finally explaining how databases, RDBMSes, and SQL fit into the picture.

For some of you, this may have been the first time using a RDBMS and possibly even SQL. As a primer to get started with SQL, some initial vocabulary was introduced, installation instructions for PostgreSQL were provided, and we explored the idea of connecting to a database and looked at different ways of interacting with PostgreSQL. We then worked through a brief tutorial to give you a taste of what SQL can do.

Schema

We then moved on to the concept of database schema, looking at how to create a database and the tables within it. We listed some psql console meta-commands and then focused on using CREATE,ALTER, and DROP SQL statements to create or change a schema. As part of our discussion on schema we talked here about various data types and introduced the important topic of keys and constraints.

Data

An important take-away from the section on schema is the way in which schema works to determine the data we can have in our database tables. This data part of the puzzle is what we focused on in the 'Your First Database: Data' section of the book, exploring the CRUD operations which are carried out using INSERT, SELECT, UPDATE, and DELETE statements. These four types of statement form the core of working with data in SQL.

With INSERT we worked with the idea of adding rows of data, and discussed how the structure of our data is determined by the intersection of our rows and table columns. We then revisited and expanded on the topic of constraints, focusing on how these constraints work to determine what data can and cannot be added to a table.

By this point in the book we'd already worked with some simple SELECT statements. Here, we expanded our knowledge of SELECT by exploring some more powerful examples of its usage. We added a WHERE clause to add a filter functionality to our database queries, providing only the specific data we need rather than all of the rows in a table. We also looked at other ways of manipulating data with SELECT, such as determining how results are sorted using an ORDER BY clause, returnig particular sub-sets of a table using LIMIT and OFFSET, and aggregating data with GROUP BY. Along the way we outlined different types of operators such as string matching operators, logical operators, and comparison operators; and also dipped our toes into the topic of SQL functions, looking at some examples of string functions, date/ time functions, and aggregate functions (which we can use with the GROUP BY clause).

The final chapter in this section looked at changing specific data values in existing rows using an UPDATE statement, and removing entire rows altogether using a DELETE statement. Much of the focus here was was on the importance of targetting only the specific rows that we want to update or delete.

Multiple Tables

Even with a diverse set of table attributes and constraints, there is only so much we can only do with one single table of data. In the final section of this book we introduced some of the redundancy and data integrity issue that can occur if we let one of our database tables become too large, and explained how normalization was the solution used to address these issues. We explained that normalization is arranging data in multiple tables and defining relationships between them.

Before looking at how to structure a table's schema to implement relationships between tables, we took a detour into the realm of database design and introduced the idea of using entity relatinshiop diagrams, or ERDs, as a tool used to model relationships between entities. At a practical level, we demonstrated how Primary Keys and Foreign Keys are used to create references between rows in different tables, and then outlined the different types of relationships that can be created between tables by using these references: one-to-one, one-to-many, and many-to-many.

We finished off by putting everything together and looking at how to leverage table relationships to query data across multiple tables, using a SQL JOIN statement. This statement lets us merge data from table rows together, usually by using a foreign key and a primary key. Joining the tables in this way effectively creates a virtual join table that can then be queried just like any other table, for example it could be filtered using a WHERE clause. We looked at some different types of join that exist such as INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN, and saw how the results of a query differed depending on the join type.

Summing Up

When combined together, all of what has been listed above allows us to frame important information within our database using constructive and meaningful representations. Much of what you've learned here may be used in a wide variety of scenarios and with different RDBMSes. Many people start out learning different kinds of languages; Java, Python, Ruby, so SQL may seem like a deviation: it's a declarative language and it isn't object-oriented. But keep in mind that SQL is an absolutely essential language used in many applications, including web applications. Whether you're running an online store or maintaining a game app, you almost always have to store data in some way or form, that's where SQL comes into play.

Next Steps

This book isn't the be all end all of SQL content here at Launch School. As mentioned earlier, if you're reading this book as part of the Launch School curriculum, then it is intended to prepare you for our course on SQL and relational databases. There you'll build further on the key concepts we've covered here, find additional practice materials for what you've already learned, and learn how to apply SQL to a working with a web application.

If you're reading this book independently of the Launch School curriculum then you won't have access to all of the content and practice materials listed above. We would still recommend that you find some way to put into practice the concepts we've covered in this book. Structured practice and repetition is key to properly embedding new knowledge and skills. Below, we've listed a number of resources that might help you.

Thanks for reading!

Resources

  • Official documentation is an important source of knowledge. When putting your SQL skills into practice be sure to refer to the PostgreSQL manual if you get stuck.
  • We offer a variety of exercises on different aspects of SQL. Access to the exercises requires registration (free).
  • If you do find you need more practice, we recommend SQL Bolt, an interactive tutorial that gives real time feedback and lets you run queries from within the SQL Bolt site.
  • Another place you can practice your queries is SQLFiddle, which gives you a place to setup schema and test out queries all in one convenient location.
  • Be sure to check out W3's resource on SQL. It has general information about SQL that can be applied across multiple RDBMs.