# SQL FOR LIBRARIANS ### An introduction to basic SQL and Metabase --- ## Lesson 1 ### Relational databases and basic queries --- ## WHAT IS A RELATIONAL DATABASE? <p class="fragment">A relational database stores and provides access to data points in separate tables that are related to each other.</p> <p class="fragment">The data in a relational database is organized into tables, which consists of rows and columns. <p class="fragment">Each row in a table represents a record and each column represents a data field.</p> <p class="fragment">Each table has a unique key field which is used to connect it to other tables, hence the term "relational".</p> <p class="fragment">Relational databases use Structured Query Language (SQL) for data manipulation and queries.</p> --- ## TABLES  This is a table. <p class="fragment">This table contains data about books. Let's call it the books table.</p> --- ## WAIT A MINUTE . . .  The books table looks a lot like a spreadsheet. --- ## SPREADSHEETS ARE TABLES?  Not really, but sort of? Just like a spreadsheet, the books table has rows and columns. Each row represents a record and each column represents a data field. --- ## IN OTHER WORDS In the books table:  +++ In the books table:  a row is a book +++ In the books table:  a column is the information available in our table about each book --- ## COLUMNS HAVE NAMES The columns in the books table are: ID           PRINT_ISBN           TITLE     AUTHOR_NAME           IMPRINT_ID  --- ## COLUMNS HAVE NAMES The columns in the books table are: ID: <p class="fragment">Even though a print isbn can be a unique identifier, in our table it's not, so every book is assigned an id for use throughout the database.</p> PRINT_ISBN: <p class="fragment">Prints isbns aren't unique in our table, but they help link our data to the rest of the world.</p> TITLE, AUTHOR_NAME: <p class="fragment">The title and author of the book and can be repeated throughout the table.</p> IMPRINT_ID: <p class="fragment">This is the unique identifier used by our database to represent the imprint of the book.</p> <p class="fragment">Information about the imprint is stored in a separate table in the database.</p> --- ## HOW ARE WE RELATED? <div class="fragment"> This is where the "relational" part comes in. If a table is like a spreadsheet, a relational database is like a workbook with multiple spreadsheets. </div> <div class="fragment"> Instead of storing all of the data about our books in one spreadsheet, we store different data points in different spreadsheets. Then we do something like a vlookup to link all the data together. </div> <div class="fragment"> A database is a group of tables that are linked together. Each row, in each table, has an identifier that links it to data in other tables. </div> <div class="fragment"> For example, the imprint_id in our books table links to the id in a table with more information about imprints. Each book in the book table has an id. In other tables in the database, the book_id is used to refer to the book and the data in the book table. </div> --- ## WHAT'S IN OUR TABLE? Let's look at the books table again.  The table only contains a few data points about the books in our system. These are the essential data points that anyone or anything utlizing the data might need to know about each book. In a "real" database, we'd probably have a lot more columns, but we're keeping it simple. --- ## BUT WHAT ABOUT IMPRINT NAMES?  This is the imprints table. +++ It stores the name, type, and parent id of the imprints in our system. Instead of storing this information in the books table, which would mean duplicating data over and over and over, we store it in a separate table. <div class="fragment"> There are probably places where we want to see information about imprints, but we don't want to have process or look at all the book data. Because this is a relational database, we only link the data in the imprints table to the data in the books table when we need to see the information together. </div> --- ## OUR DATA LINKED  The data in the imprints table is linked to data in the books table using the imprint id. In SQL and Metabase's question builder, this is called a JOIN. <div class="fragment"> If we want to see the company name when we look at book data, we need to join the tables together when we ask for data. When we join the tables, we create a new table that contains the data from both tables. </div> --- ## JOINS <div class="fragment current-visible"> ### Wait. </div> <div class="fragment current-visible"> #### Not yet . . . </div> <div class="fragment"> About that vlookup analogy — it's not quite right. In Excel, a vlookup does a few things:  </div> +++ it looks for a given value  +++ in a particular range of cells  +++ and, when it finds a match, it returns a new value based on proximity to the match  --- ## STUCK IN THIS ANALOGY FOR A MINUTE <div class="fragment"> A vlookup is a nice way to move data around in Excel — from one spreadsheet or column to another. </div> <div class="fragment"> If we were working in Excel, we could create a new column on our books spreadsheet and do a vlookup, matching the values in the imprint id column to the values in the id column on the imprints spreadsheet. Then we'd return the values from the matching company name column. </div> <div class="fragment"> If we needed to add more than one column to our spreadsheet, we would write a vlookup formula for each new column. </div> <div class="fragment"> Really, we need a vlookup for every cell we add to our spreadsheet, but Excel makes it easy to replicate etc etc you know what I mean. It's a lot of formulas. </div> --- ## THE ANALOGY FALLS APART <div class="fragment"> A vlookup is a nice way to move data around in Excel. Don't forget about them because we're still going to spend a lot of time with our data in Excel and writing vlookups and making pivot tables and all that fun Excel stuff. </div> <div class="fragment"> What we're interested in now is getting data out of our database so that we can manipulate it in Excel or some other system. </div> <div class="fragment"> To do that we use queries. A query is a question, duh. But more specifically it's a set of instructions for creating a new table. </div> <div class="fragment"> Writing a query isn't really akin to a vlookup. A query is more like a series of Excel formulas, and the vlookup is just a small part of that. It's kind of like comparing a shovel to a bulldozer — they're doing something similar but not exactly. </div> <div class="fragment"> When we write a query, we're telling the database what data we want to see and how we want to see it. We're also telling the database where to find all the data and how it all links together. </div> --- ## QUERIES Actually, it's more complicated than that, but for our purposes . . . <div class="fragment"> A query tells the database: </div> <div class="fragment"> what data to return </div> <div class="fragment"> where to find the data </div> <div class="fragment"> how to link the data together <div class="fragment"> how to filter the data </div> <div class="fragment"> how to sort the data </div> +++ We write queries using SQL, or <div class="fragment"> STRUCTURED QUERY LANGUAGE </div> <div class="fragment"> Metabase's question builder writes SQL for you, so you don't *have to* know SQL. But understanding how SQL is structured will help to make sure you're getting the data you want out of the database. </div> <div class="fragment"> A query uses keywords, or STATEMENTS, to structure a request from the database. Metabase gives you access to the essential statements needed to build basic queries. </div> <div class="fragment"> SELECT : what data to return </div> <div class="fragment"> FROM : where to find the data </div> <div class="fragment"> JOIN : how to link the data together </div> <div class="fragment"> WHERE : how to filter the data </div> <div class="fragment"> ORDER BY : how to sort the data </div> --- ## QUERIES Let's write a basic query. <div class="fragment"> We want to see all the data in the books table. To do this, first we say what data we need. ``` SELECT * ``` </div> <div class="fragment"> SELECT is where we list our data points, or the columns, we want in our new table. The asterisk * means EVERYTHING. As in, return all the columns available. </div> <div class="fragment"> Next, we tell the database where to find the data. ``` SELECT * FROM books ``` </div> <div class="fragment"> FROM is where we specify which table where the data we want is stored. "books" is the name of our table. </div> --- ## QUERIES Now we have a complete query that we can send to the database. ``` SELECT * FROM books ``` When we run this query, the database will return a new table with all the data in the books table. <div class="fragment"> But maybe we don't want to see all the data in the books table. For example, what if we only want to see data about books from a particular publisher? </div> +++ To do this, we use the WHERE statement to create filters. ``` SELECT * FROM books WHERE imprint_id = 1 ``` WHERE tells the database what rows to add to our table. imprint_id is the name of the column we're filtering on. We've limited our table to only include rows where the imprint_id is 1. --- ## QUERIES Now let's step back to the SELECT statement. We don't want to see all the columns in the books table. The id is only useful to the database, and we've already limited the data to rows where the imprint id equals 1. <div class="fragment"> We only want to see the print isbn, title, and author name, so we'll list those columns in the SELECT statement. ``` SELECT print_isbn, title, author_name FROM books WHERE imprint_id = 1 ``` </div> +++ The results from that query would look something like:  --- ## QUICK BREATH Ok. Cool. Easy enough, so far? <div class="fragment"> If you're keeping up and feel like you understand the basic concepts as much you can by listening to me talk, you're pretty much ready to start building queries in Metabase using the question builder. </div> <div class="fragment"> The basic format of the question builder, with a few word changes, is the same as the query we wrote, and intuitive enough to use without knowing SQL. +++ At this point, you could pull some columns and rows out of a table, but for most of the data we want to interact with, there are better sources available to us. <div class="fragment"> Your reports won't be very useful unless you can aggregate data from multiple tables. Otherwise, you're only seeing a small part of the data, and you're looking at id numbers instead of names or other useful information. </div> <div class="fragment"> To do that, we need to join tables, and that's where things get complicated. But understanding joins is essential to making the most of Metabase and minimizing the postprocessing you'll need to do in Excel. </div> --- ## BEFORE YOU JOIN Not to make it sound scary or intimidating. Joins can be fairly simple and straightforward. Understanding how joins work is important. <div class="fragment"> Quite often even experienced query writers will choose the wrong type of join for their data. It's an easy mistake to make and can be hard to notice without careful scrutiny, depending on the data. </div> <div class="fragment"> A danger of gathering your own data is assuming you've captured a dataset, when you actually used the wrong join and you're missing a chunk of relevant data. </div> <div class="fragment"> Does that sound scary enough? Grrr. Boo! Be careful! The essential thing here is to evaluate and scrutinize your data. Whether it's a bad join or some other error, never assume you got it right without review. </div> --- ## FIRST, A SIMPLE JOIN Once more to the books table. We want to use the data in the books table, but we want the imprint name in our table, not the id number. <div class="fragment"> We'll start with our original query, but change the filter to search by author name. ``` SELECT print_isbn, title, author_name FROM books WHERE author_name ilike '%Barthleme' ``` Don't worry too much about what's happening in the WHERE statement. The question builder will write your WHERE statement, and we'll talk about those options later. </div> --- ## FIRST, A SIMPLE JOIN II The JOIN statement joins our tables together. We need two things to join tables: the name of the table we're joining to and the column we're joining on +++ We're joining the books table to the imprints table. To do that, we match the values in the imprint_id column in the books table to the values in the id column in the imprints table. <div class="fragment"> ``` SELECT print_isbn, title, author_name FROM books JOIN imprints ON books.imprint_id = imprints.id WHERE author_name ilike '%Barthleme' ``` JOIN states the name of the database to join and ON states where the table intersects with the first table. For joining purposes, the table in the FROM statement is referred to as the LEFT table and the table in the JOIN is referred to as the RIGHT table. </div> --- ## FIRST, A SIMPLE JOIN III Now that the imprints table is joined to books, we can add columns from imprints to our SELECT statement. ``` SELECT print_isbn, title, author_name, company_name FROM books JOIN imprints ON books.imprint_id = imprints.id WHERE author_name ilike '%Barthleme' ``` +++ And now we send our query to the database and get:  Our results, showing company name instead of imprint id. --- ## JOINS There are four types of joins: INNER JOIN: Returns records that have matching values in both tables LEFT OUTER JOIN: Returns all records from the left table, and the matched records from the right table RIGHT OUTER JOIN: Returns all records from the right table, and the matched records from the left table FULL OUTER JOIN: Returns all records when there is a match in either left or right table --- ## JOINS So far we haven't specified what type of join to use. When writing SQL, an unspecified join defaults to INNER. In Metabase, the question builder uses LEFT OUTER by default. But what does all this mean?! --- ## JOINS LEFT or RIGHT? <div class="fragment"> The LEFT table is the table we're joining to. For our query, this the table in our FROM statement. </div> <div class="fragment"> The RIGHT table is the table being joined. </div> <div class="fragment"> In SQL, the order you write things in the JOIN statement DOES NOT determine RIGHT or LEFT. ``` SELECT print_isbn, title, author_name FROM books JOIN imprints ON books.imprint_id = imprints.id /*same*/ WHERE author_name ilike '%Barthleme' ``` ``` SELECT print_isbn, title, author_name FROM books JOIN imprints ON imprints.id = books.imprint_id /*same*/ WHERE author_name ilike '%Barthleme' ``` </div> --- ## JOINS ### the diagrams <div style="display: grid;grid-template-columns: auto auto;gap: 5px"> <div style="display: grid;grid-template-columns: 200px 200px;background-color: #fff;"> <span> <img src="img/diagram-inner-join.png"> <p>INNER</p> </span> <span> <img src="img/diagram-full-join.png"> <p>FULL</p> </span> <span> <img src="img/diagram-left-join.png"> <p>LEFT</p> </span> <span> <img src="img/diagram-right-join.png"> <p>RIGHT</p> </span> </div> This is the classic set of Venn diagrams demonstrating how each join works. In the diagrams, Table A is the LEFT table, and Table B is the RIGHT table. </div> --- ## JOINS ### inner <span> <img src="img/diagram-inner-join.png" width="200rem"> </span> An INNER join is the intersection of the two tables. The table our join creates will only contain rows which contain data in both tables. +++ For instance: <div class="fragment"> if a row in our books table doesn't have an imprint id or it's imprint id isn't in the imprints table </div> <div class="fragment"> using an inner join will exclude that row from our results. If we're searching for books by a particular publisher, we don't need to include rows without an imprint id. </div> --- ## JOINS ### inner ``` SELECT print_isbn, title, author_name, company_name FROM books INNER JOIN imprints ON books.imprint_id = imprints.id WHERE books.imprint_id = 1 ``` For this query, we're filtering to books with an imprint id of 1. In this case, an INNER join works well because we only want rows that appear in both the books and imprints tables. --- ## JOINS ### inner ``` SELECT print_isbn, title, author_name, company_name FROM books INNER JOIN imprints ON books.imprint_id = imprints.id WHERE author_name ilike '%Barthleme' ``` But in this query, we're filtering to books by the author's name. In this case, an INNER join will exclude any books that don't have an imprint id. We want to see all books by Barthelme even if the book doesn't have an imprint id or the imprint is not in our imprints table. Using an INNER join here might prevent us from adding relevant data to our table. --- ## JOINS ### left <span> <img src="img/diagram-left-join.png" width="200rem"> </span> A LEFT OUTER join returns all the rows in the LEFT table, and the matching rows in the RIGHT table. Our resulting table will contain all the rows from the LEFT table that match our filter conditions, as well as any rows from the RIGHT table that match our join conditions. +++ For instance: <div class="fragment"> if a row in our books table doesn't have an imprint id, or it's imprint id isn't in the imprints table </div> <div class="fragment"> the imprint_name column in our new table will simply be blank, or in SQL terms, NULL. </div> --- ## JOINS ### left ``` SELECT print_isbn, title, author_name, company_name FROM books LEFT JOIN imprints ON books.imprint_id = imprints.id WHERE author_name ilike '%Barthleme' ``` Using a LEFT join this query, our new table will have a row for every book with author_name Barthelme. If the book doesn't have a matching imprint in the imprints table, the company_name column in our new table will be NULL. --- ## JOINS ### right and full <div style="display: grid;grid-template-columns: 200px 200px;background-color: #fff;text-align: center; width: 420px;"> <span> <img src="img/diagram-full-join.png"> </span> <span> <img src="img/diagram-right-join.png"> </span> </div> For the most part, you'll be using INNER and LEFT joins. A RIGHT join, unsurprisingly, is the opposite of a LEFT join. A FULL join is a combination of LEFT and RIGHT. There are definitely times we these joins are useful, but since INNER and LEFT joins will be more relevant at the moment, we're going to skip getting into the details of RIGHT and FULL joins. --- ## JOINS ### multiple tables Sometimes we need to join multiple tables. In our database, some of our books have been tagged. A tag can be reused, and we don't won't to store the value of the tag over and over again in our database. +++ So we have a table called tags, which stores the values of tags. When a new tag is created, it's added to this table. But if a tag already exists, we just refer back to the value using the tag's unique id number.  The tags table. --- ## JOINS ### multiple tables We don't need to see tag data in the books table because tags are only used by a few applications, and we want to keep the books table as small as possible. +++ Instead, we have a books_tags table, which contains a column with a book_id and a tag_id.  The books_tags table. --- ## JOINS ### multiple tables If we want to connect the text value of a tag to a book: first we join the books_tags table to the books table, matching on book id numbers then we join the tags table to the books_tags table, matching on tag id numbers ``` SELECT print_isbn, title, author_name, company_name, tags.value FROM books LEFT JOIN imprints ON books.imprint_id = imprints.id LEFT JOIN books_tags ON books.id = books_tags.book_id LEFT JOIN tags ON books_tags.tag_id = tags.id WHERE tags.value like 'Fiction' ``` --- ## CONCLUSION Hopefully at this point you have a basic understanding of relational databases and how queries work to fetch data from relational databases. You might not be able to write SQL queries, but fortunately, Metabase does a lot of the work for you. The best way to learn about the Question Builder is getting hands on with it.