Databases and SQL

Lesson 1

What a relational database is

A database is an organized way to store information so a computer can find, filter, and combine it quickly and reliably. Software Carpentry compares the common choices this way: text files are easy to create but leave you to build your own search tools, spreadsheets are handy for small jobs but struggle with large or tangled data, and databases include powerful tools for search and analysis. When your information matters and keeps growing, a database is the steady choice.

A relational database stores information in tables that are related to each other. In this course our example is a small library. One table lists books, another lists members, and another records loans that connect a book to the member who borrowed it. Because the tables are linked by shared values, you can ask questions that cross tables, such as which members have books due this week, without copying the same details into many places.

You talk to the database with SQL, a language made for asking questions of tables. A program called a database manager does the work: it stores the data, runs your query, and returns the rows that match. SQLite is a widely used database manager that understands most of standard SQL, and it is the reference for this course. You describe the answer you want, and SQL and the manager find it for you.

Lesson 2

Tables, rows, and keys

Every table in a relational database has a fixed set of columns and a variable number of rows. A column names one kind of fact, such as title or author in our books table, and every row in that table follows the same columns. A row is one record: one book, one member, or one loan. As the library grows, the books table gains rows, but its columns stay steady, which is what makes the data predictable to query.

Columns also have types that say what kind of value belongs there. SQLite documentation and Software Carpentry list common types such as INTEGER for whole numbers, REAL for numbers with decimals, and TEXT for character strings. Our books table might use an integer for the book identifier, text for the title and author, and an integer for the year published. Keeping types consistent helps the database store data cleanly and helps you sort and calculate correctly.

Keys are the links that make the word relational real. A primary key is a column, or a set of columns, whose value identifies each row uniquely: no two books share the same book identifier. A foreign key is a column in one table that refers to the primary key in another. The loans table holds a book identifier and a member identifier, so each loan points to exactly one book and one member. With keys in place, you can connect facts without repeating them.

Lesson 3

SELECT: asking your first question

Your first SQL tool is the SELECT statement, which asks the database to return values from a table. SQLite documentation describes SELECT as the statement used to query the database, and notes that a SELECT does not change the database. That makes it a safe place to start: you can explore freely, and nothing you write with SELECT will alter the stored data.

The basic shape is simple. You write SELECT, then the column names you want, then FROM, then the table name. To see the title and author of every book in the library, you would write SELECT title, author FROM books. If you want every column, you can use a star in place of the list, as in SELECT * FROM books. Many people write SQL keywords in capital letters to make the structure easy to read, though SQL itself is not case sensitive for keywords.

Take a moment to read a query as a sentence. SELECT title, author FROM books says: give me the title and author, taken from the books table. Each row the database returns is one book, and the columns appear in the order you listed them. Start every new question by naming the table and the columns first. Once that habit is solid, the next lessons add filtering, sorting, and calculations to the same simple shape.

Lesson 4

Filtering and sorting

Most questions are not about every row. Filtering lets you keep only the rows that match a condition, using a WHERE clause. To find books by one author in our library, you would write SELECT title FROM books WHERE author = 'Austen'. The database checks each row against the condition and returns only the rows where it is true. Software Carpentry teaches filtering the same way: WHERE selects records that satisfy the conditions you give.

You can combine conditions with AND and OR, and compare numbers as well as text. For example, SELECT title, year FROM books WHERE year > 2000 AND author = 'Austen' would look for recent books by that author, while an OR would widen the search instead of narrowing it. Text values go in single quotes, numbers do not need quotes, and getting that detail right helps the database understand exactly what you mean.

Sorting puts the answer in a useful order. Add ORDER BY followed by a column name to sort by that column, and add DESC if you want largest or latest first instead of smallest first. A full example is SELECT title, year FROM books ORDER BY year DESC, which lists the newest books first. Filtering and sorting together turn a raw table into a short, readable answer: the right rows, in the right order.

Lesson 5

Calculating and aggregating

SQL can do arithmetic as it answers. You can calculate a new value for each row inside your SELECT, such as adding a fee or working out a price change, and give the result a clear name with AS. For example, in a shop table you might select a price and price * 2 AS double_price, but in our library you might calculate how long a loan has lasted from its dates. The stored data does not change: the calculation appears only in the answer.

Aggregating is different: instead of one result per row, you get one result for a whole set of rows. The common aggregate functions are COUNT, which counts rows, SUM, which adds values, AVG, which finds an average, plus MIN and MAX for the smallest and largest values. To count every book in the library you would write SELECT COUNT(*) FROM books. To find the average loan length or the total fees collected, SUM and AVG do the adding for you.

Read aggregate queries carefully, because they summarize rather than list. SELECT COUNT(*) FROM loans tells you how many loans exist, not which ones they are. That summary power is exactly what reporting needs: totals, averages, and counts that would be slow and error prone to work out by hand. In the next lesson you will learn to produce those summaries separately for each group in your data, such as per member or per author.

Lesson 6

Grouping results

Grouping lets you summarize your data category by category. The GROUP BY clause gathers rows that share the same value in a column, and your aggregate functions then work once per group instead of once for the whole table. To count how many books the library holds by each author, you would write SELECT author, COUNT(*) FROM books GROUP BY author. The answer has one row per author, with that author count beside the name.

The pattern matters. When you use GROUP BY, the columns you select should be either the grouping column or an aggregate such as COUNT, SUM, or AVG. Selecting a plain column that varies inside the group would give a confusing answer, because the database would have many values to choose from for one group row. A library report might group loans by member to show loans per member: SELECT member_id, COUNT(*) FROM loans GROUP BY member_id.

You can filter groups after grouping with HAVING, which works like WHERE but for groups. For example, you could group loans by member and keep only members with more than a set number of loans by adding a HAVING clause after GROUP BY. You can also sort grouped results with ORDER BY, perhaps by the count from largest to smallest. Grouping turns detail into insight: it is how a long loan list becomes a short table of your most active members.

Lesson 7

Joining tables

The most useful questions often need facts from more than one table. Our loans table knows which member borrowed which book, but it holds only identifiers, not the member name or the book title. Joining tables solves this: a JOIN combines rows from two tables, and the ON clause says which columns link them. Software Carpentry puts it plainly: use a JOIN to combine data, and ON to specify which columns link the tables.

A first join in the library might read SELECT members.name, books.title FROM loans JOIN members ON loans.member_id = members.id JOIN books ON loans.book_id = books.id. Read it slowly. Start from loans, match each loan to its member by comparing member_id with the member primary key, then match it to its book the same way. The result reads like a single wide table with names and titles side by side, even though the data lives in three tables.

A plain JOIN returns only rows that have a match on both sides. A LEFT JOIN keeps every row from the left table and fills in missing values from the right table with NULL when there is no match, which is how you would list all members including those with no loans yet. Joins reward clear keys and calm reading. Name your tables, state your ON conditions carefully, and build up one join at a time until the answer matches the question you meant to ask.

Lesson 8

Handling missing data

Real data has gaps. A book may have no recorded return date because it is still on loan, or a member record may lack a phone number. SQL uses a special value called NULL to mark missing or unknown data. NULL is not zero, and it is not an empty piece of text. It means the value is not there, and SQL treats it differently from every ordinary value, which you need to know to avoid quiet mistakes.

You cannot find missing values with an equals sign. A comparison with NULL does not come out true or false, it comes out unknown, so a WHERE clause will leave those rows out. Instead you test for missing data directly: WHERE return_date IS NULL finds loans still open, and WHERE return_date IS NOT NULL finds loans that have been returned. Software Carpentry gives missing data its own lesson for this reason: forgetting NULL is one of the most common sources of wrong answers.

Aggregates also treat NULL with care. COUNT of a column counts only rows where that column is not NULL, while COUNT(*) counts every row, and SUM and AVG skip NULL values rather than treating them as zero. Keep this in mind when a total looks lower than you expected: the database may be skipping gaps, not making an error. Handle missing data on purpose: check for it, decide what it means for your question, and say what you did in your report.

Lesson 9

Creating and changing tables

So far you have only read data. SQL can also build and change the tables themselves. You create a table with CREATE TABLE, giving the table name and, in parentheses, each column name with its type. SQLite documentation describes CREATE TABLE as specifying the name of the new table, the name of each column, and the declared type of each column, with an optional primary key. For our library, CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER) creates an empty books table ready for rows.

You add rows with INSERT, naming the table and columns and then the values. An example is INSERT INTO books (title, author, year) VALUES ('Emma', 'Austen', 1815), leaving the identifier to be handled as your design requires. You can change the structure later in limited ways, and you can update values in existing rows with care. The safe habit for any change is the same: know exactly which table and which rows you intend to touch before you run the statement.

Warning: DROP TABLE deletes a whole table and its data, and there is no gentle undo in ordinary use, so never run DROP TABLE unless you are certain the table and everything in it can go. Warning: DELETE without a WHERE clause deletes every row in the table, which is almost never what you intend. Build the habit of SELECT before DELETE: first run a SELECT with the same WHERE clause to see exactly which rows match, check that list with your own eyes, and only then run the DELETE with that same clause.

Lesson 10

Designing a simple database

Good design happens before you create a single table. Start from the questions the database must answer and the facts you need to store. For our library, the facts fall into natural groups: facts about books, facts about members, and facts about loans. Each group becomes a table. Storing each fact once, in its proper table, prevents the confusion that comes from copying the same title or name into many places and then updating only some of them.

Next, give each table a clear primary key and decide the type of every column. Books get an integer identifier, a text title, a text author, and an integer year. Members get an integer identifier and text for name and contact details. Loans get their own identifier plus a book identifier and a member identifier as foreign keys, and dates for when the book went out and came back. Those foreign keys are the design: they state, in the structure itself, how the tables connect.

Keep the design simple and honest. Use clear table and column names that a new person can guess, avoid storing a value you can calculate from other values, and do not squeeze several facts into one column, such as a whole address in a single field if you need its parts separately. Sketch the tables on paper, walk through two or three real questions, and check that your tables can answer them with the SQL you have learned. A small, clear design beats a clever, tangled one every time.

Lesson 11

A real-world reporting project

This final lesson pulls the whole course together in one reporting project for the library. Imagine the librarian asks three questions at the end of the month: how many books are currently on loan, which members have the most loans, and which books have never been borrowed. Each question sounds simple in plain words, and your job is to turn each one into careful SQL, check the answer, and present it clearly.

You already own every tool you need. For books currently on loan, you filter the loans table with WHERE return_date IS NULL and count the rows. For the busiest members, you join loans to members, group by member, count the loans, and order the result from largest to smallest. For books never borrowed, you use a LEFT JOIN from books to loans and keep the rows where the loan side IS NULL. Writing each query in small steps, and checking a few rows by eye against the tables, keeps the work honest.

A good report does more than paste query results. State each question in plain words, show the SQL you used, give the answer in a small clear table, and note any choices you made, such as how you handled missing return dates. That habit makes your work checkable and useful to the person who asked. You began this course reading single tables. You finish it answering real questions across a whole database, which is the daily work SQL was made for.

Sources

The guides behind this course

This course is built from the official SQLite documentation and the Software Carpentry Databases and SQL lesson, which is licensed under Creative Commons Attribution (CC BY) and credited here. SQLite documentation supplies the precise SELECT and CREATE TABLE language, and Software Carpentry supplies the teaching sequence for selecting, filtering, aggregating, joining, and handling missing data.

• SQLite Documentation, SQL Language (sqlite.org), sqlite.org/lang.html
• SQLite Documentation, SELECT (sqlite.org), sqlite.org/lang_select.html
• SQLite Documentation, CREATE TABLE (sqlite.org), sqlite.org/lang_createtable.html
• Software Carpentry, Databases and SQL (swcarpentry.github.io), swcarpentry.github.io/sql-novice-survey

Graduation

You finished Databases and SQL

You learned what a relational database is, how tables, rows, and keys work, and how to ask questions with SELECT. You filtered and sorted, calculated and grouped, joined tables, handled missing data with care, created tables safely, designed a simple database, and finished a real reporting project for the library.

Back to Starvard University