Working with SQL in an existing project

Get connected and look around

When you join a project with an existing database, your first job isn't writing SQL. It's understanding what's already there. Someone else has already decided what the tables are, what columns they have and how they relate. Together, those decisions are the project's data model, and the tables and columns that hold it in the database are its schema. Connect with whatever tool you like (the command line, a GUI or your IDE's database panel). Then list the tables and open the most important ones to see their columns. Table names like books, authors, loans and members already tell you a lot about what the application does.

Not everything in that list is necessarily a table. Some may be views: saved queries that you can use as if they were tables. A view called books_with_authors, for example, might combine books and authors with a join, so that nobody has to write that join again. Your database tool usually marks views differently from tables, and can show you the query behind each one. Views are worth looking at. Someone wrote them to answer questions the project asks often, so they show you how the data is actually used. A view doesn't hold any data of its own. It runs its query every time you use it. If something in a view looks wrong, the fix belongs in the tables behind it.

Foreign keys tell you how the tables connect

When you inspect a table, look for its foreign keys. A foreign key is a column that holds the id of a row in another table. Here the books has a column author_id. The value 1 in that column means "this book was written by the author whose id is 1 in the authors table":

authors

books

Each foreign key is an arrow from one table to another, and if you sketch those arrows on paper you've drawn the data model. They're also what you'll use to combine tables in queries.

SELECT is 80% of the job

A query says which columns you want, which table they come from, and optionally which rows:

SELECT title, year
FROM books
WHERE year < 2015;

That's the core shape, and almost everything else builds on it. Add ORDER BY title to sort, or LIMIT 10 to get only the first few rows (a good habit when exploring big tables). Text values go in single quotes, as in WHERE title = 'Station Eleven'. To combine tables, follow a foreign key with a JOIN:

SELECT books.title, authors.name
FROM books
JOIN authors ON authors.id = books.author_id;

This lists every book next to the name of its author. It works because books.author_id points at authors.id, which is exactly the arrow from the previous section.

Reading data never changes it

A SELECT gives you a new table as its answer, and leaves the stored table as it was. You can run as many queries as you like, wrong ones included, and the data is the same afterwards. That makes it safe to explore a database you don't know yet, however big it is.

Ask for a few rows at a time

When a table is bigger than you can read, ask for less of it. All of these only read, so you can try them freely:

• LIMIT takes only the first rows: SELECT * FROM books LIMIT 3;
• Naming columns instead of * makes each row shorter: SELECT title, year FROM books;
• WHERE keeps only the rows you're interested in: SELECT title FROM books WHERE author_id = 3;
• count(*) tells you how many rows there are, before you fetch any of them: SELECT count(*) FROM books;

Change data in the playground

Changing data takes a different kind of statement: INSERT adds rows, UPDATE changes them and DELETE removes them. These make actual changes to the stored table, and in a real project a change is permanent, at least until someone changes it back. The playground is a safe place to practice making changes and wreak havoc with the data.

Start by changing a row. The Dispossessed was first published with a subtitle. Look at the row first:

SELECT * FROM books WHERE id = 2;

Then change its title:

UPDATE books
SET title = 'The Dispossessed: An Ambiguous Utopia'
WHERE id = 2;

Run the same SELECT again, and the row has the new title:

The UPDATE changed only one row because of its WHERE clause. The condition after WHERE is called a predicate, and it picks rows the same way in every statement. WHERE id = 2 matched one row in the SELECT, so it changed one row in the UPDATE. Without a WHERE, the UPDATE would have changed every book.

Next, add a book that Ursula K. Le Guin wrote:

INSERT INTO books (id, title, year, author_id)
VALUES (7, 'The Word for World Is Forest', 1972, 1);

Then look at her books, and the new one is there:

SELECT *
FROM books
WHERE author_id = 1
ORDER BY year;