SQL 101 for Product Managers
The following presentation was transcribed from “SQL and Python for Product Managers” webinar, presented by Colorado Product Meetup and senior software engineer, Brian Schiller. It has been edited for clarity.
The following presentation was transcribed from “SQL and Python for Product Managers” webinar, presented by Colorado Product Meetup and senior software engineer, Brian Schiller. It has been edited for clarity.
What is SQL?
SQL is a family of programming languages used to communicate with a database. These languages are different, but they’re similar enough that we can call them a family. One thing to note is that pronouncing it “sequel” or “SQL” are both totally valid options so continue saying it the way you prefer!
What does SQL look like?
We’re going to walk through some examples of what SQL looks like and what it can do. We’ll look at:
- Select example (most common for product managers)
- Insert example
- Update example
- Crazy CTE example
Select: a select query allows you to extract specific data from your database using set parameters. Here’s an example.
SELECT firstName, familyName, joinDate
FROM mailing_list
WHERE joinDate < '2020-03-01';
In this simple query, we can assume that there is a table in the database called mailing list and we want to see first name, family name, and join date. There may be additional columns, but these are the only ones we’re asking for.
While this is the most common SQL query for product managers, there are a few other examples worth understanding.
INSERT INTO books(isbn, title, author_id)
VALUES
('9780780797086',
'Harry Potter and the Philosopher''s Stone', 123),
('9781732102200',
'A Philosophy of Software Design', 345);
Insert: this is an example of an insert query. Developers use this for inserting and updating existing records in the database.
UPDATE users u SET
u.last_active_date = CURRENT_TIMESTAMP,
u.accepted_tos = TRUE
WHERE u.id = 510292;
Update: You can also do an update (for example, update users with the alias “U”). This allows us to change the data associated with one or more users depending on the conditions we specify in the query. In the example above, we’ve specified their active date and that they’ve accepted our terms of service.
Finally (just for a little bit of variety) here is a somewhat-more-complex example.
Even though the first couple of SQL examples are fairly simple, it can get pretty crazy. The feature I want to point out here is this idea of a join. What this basically does is allow different tables to be related to one another via one column.
This is a little over a 101 course, but if you want to learn more about this, watch the video (starting at 3:00).
What’s So Great About SQL?
Huge community – SQL has been around so long and it is the most common and popular way to put together a web app. There is also a big community – people who understand SQL and how to use it. They can give you pointers.
Excellent tools – Because of the huge community, there are excellent tools for every operating system for every database you’re interacting with. Our personal favorite is one called Postico (Mac OS).
Well-understood operational needs – The other thing that’s nice about SQL is that it has well-understood operational needs. Even among newer technologies, none of them are as well understood as plain old relational databases.
If you can ask a question about your data, you can ask it in SQL – One point that’s incredibly useful when a product is evolving (which is true of every product) is: if you can ask a question about your data, you can ask it in SQL. Data foresight is not required.
“Normalization” (guidance on how to layout your data) – Normalization patterns were developed at the same time as ID of databases were developed. They turn out to be really useful and powerful in making sure that that you can ask increasingly complex questions about your data.
Structure up front, figure out query patterns later – This gives you the freedom to figure out queries later. Compared to some other database technologies where you need to know all of the queries and questions at the beginning. This is important because it allows us to change our minds as our product develops.
What’s Not So Great About SQL
Different dialects – Remember how SQL is a family of programming languages? These families each have different dialects which means that MS SQL server won’t work quite the same as MySQL, which won’t work quite the same as PostgreSQL.
Friend of a friend – It’s a bit awkward for “friend-of-a-friend” queries (though they’re possible with “recursive common-table-expressions”).
Big data – When your data gets REALLY BIG (no, bigger than that. Bigger. Still bigger), you can run into scaling problems. However, by the time you reach this point, you understand the problem better than at first. You’re ready to move to a column-oriented or distributed NoSQL database. You also have the money to hire people who can help you set up.
Is SQL outdated?
Yes, SQL is an old technology, but it’s still the most popular database technology used today. The NoSQL folks, especially MongoDB, spent a lot of marketing dollars to plant the idea that newer databases=better. However, we still think that relational databases, accessed via SQL, have many benefits over newer NoSQL DBs.
Why Developers Love SQL
To summarize, SQL forces developers to think about how domain entities relate to one another. You can make changes, but you don’t end up with new data structured one way and old data another.
In return for this bit of discipline, you are free to query your data any way you like. That’s easy to underestimate, so I’ll give a brief example.
Sarah Mei describes a project she worked on as “the best use case [she’s] ever seen for a document database.” It was a web app displaying information about tv shows. Each TV show has many seasons, each season had many episodes, and so on (see diagram below).
The SQL DB way of storing this would be to put everything in different tables, with foreign keys linking an episode to a season, a season to a show. The resulting structure required joining all of these tables together to pull out all the information we care about.
In MongoDB, we can store the data nested exactly as we would use it when rendering the webpage. Seems like a win, right? But Mei’s article goes on to describe a later feature request: “We want to be able to click on an actor’s name and see that person’s entire television career.” Uh oh. Accomplishing this with the existing MongoDB structure would require examining every show in the database, every season in each show, every episode in each season, looking for appearances of the requested actor.
This request eventually prompted the conversion of the entire project to a PostgreSQL database. NoSQL databases like MongoDB can be made extremely fast for specific queries, but SQL databases are better when you don’t yet know the types of questions you’ll need to ask of the data.
Interested in Learning More About SQL?
Here are our favorite resources for product managers wanting to learn beyond SQL 101.
