Our main goal in this book was to introduce you to the SQL language and to get you ready for our course, Database Foundations. 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.
We started off looking at things from a conceptual level, talking about why data is important, comparing unstructured 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.
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.
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, returning particular subsets 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 on the importance of targeting only the specific rows that we want to update or delete.
Even with a diverse set of table attributes and constraints, there is only so much we can do with a single table of data. In the final section of this book we introduced some of the redundancy and data integrity issues 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 relationship 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 can be thought of as the SQL creating a transient table that can then be queried like any other table. For example, the result of the join 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.
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 a programming language like Java, Python, or Ruby, so SQL might seem like something very different: 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.
This book isn't the totality 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 use SQL in 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!