Basic SQL Skills for Product Managers

Photo by Liana Mikah on Unsplash

Data is a powerful tool for Product Managers. We all know that corporations should use data to run their business and make the decision process easier. When any type of data is involved in the process, databases are used in every single aspect of it.

You might be wondering, should I become a database expert? I say, you won’t need to. The most popular language used for manipulating data from a database is SQL, and you don’t need to be an expert to manipulate the database to generate reports and analysis.

Keep in mind that around you, everyone is using SQL to drive these databases. But where? I’d say, everywhere from your phone to your computer, from the social networks you use, to softwares. They’re all using SQL when managing data.

Learning SQL will teach you how to manipulate the data to get the information you need. You will learn how to interact directly with your database. I’m sure your team will feel extremely grateful if you gather the data by yourself without bothering them. Don’t forget to ask them for the access and check your permissions.

By Programming with Mosh

What’s SQL?

SQL is an acronym for Structured Query Language, and it’s also referred to as “sequel.” They’re the same thing, and people use both terminologies. SQL is the language that allows communication with the database with the objective of managing the data it contains.

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS) or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data where there are relationships between different entities and variables of the data — Wikipedia.

The acronym CRUD refers to: Create, Retrieve, Update, and Delete data from a database. You will probably be mainly interested in the ability to get the information you need from the database that corresponds to the letter R of CRUD. The other letters (C, U, D) are typically the responsibility of Engineers and DBAs and not as relevant to PMs.

MySQL and PostgreSQL databases

MySQL and PostgreSQL are popular open source databases. Their database type is known as a ‘relational database’, a set of multiple data sets organized by columns, tables, and also records. Each table is known as a ‘relation’, and the data stored as tables has its own number of columns and rows and represents a set of data.

In a relational database, the tables can communicate with each other in the same way an Excel or Google spreadsheet works. In the database, the tables communicate based on specific fields known as ‘keys.’

Both MySQL and PostgreSQL are DB software, and their usage is very similar. They’re open source, which means that the original source code is freely available and publicly accessible.

The main differences between the two are how they organize the data internally and some optimization tools, but their usage of SQL is pretty much identical. One big difference is that PostgreSQL is case sensitive.

How can I use it?

You can measure results and set metrics without needing to involve your engineering team to gather the data you need. This capability can be really handy especially if you work in lean teams.

For PMs, the understanding of where the data lives can lead to a deeper knowledge of your product and its flaws. You can help the team make sure relevant fields are added to the table so you and your team can generate better reports and analyses.

The process of retrieving information from a database is called ‘querying’ a database. After querying the database, a result is known as a ‘result set.’ The result is the answer to your query.

How do queries work?

The whole point of a query is basically to describe exactly what information your application needs, potentially joining together information from several sets of documents, and then being able to generate a response that contains only the exact fields you need.

SQL queries allow you to find things like:

• A list of bank customers eligible for a new credit card;
• The count and the total sum of credit card transactions for each customer aggregated daily;
• The top five merchants used by customers monthly;
• Last month’s 10 largest credit card transactions in descending order;
• Among other things.

Connecting to a database

There are various ways to connect to a SQL database. Remember that before you can connect to a database, you’ll need some information such as username, password, server address, protocol, and port. Check with your engineering team, they’ll be able to grant you access!

SQL SELECT statement

The SELECT statement is used to select data from a database. The data returned is stored in a result table, and it’s called the ‘result-set’.

The basic syntax for the SELECT statement, when used in a SELECT statement, is as follows.

SELECT column1, column2….columnN
FROM table_name;

Check the examples below, they were inspired by sqlzoo.net. The example uses a WHERE clause to show the population of Brazil.

SELECT population FROM world
WHERE name = ‘Brazil’

You can use WHERE name LIKE ‘B%’ to find the countries that start with “B”.

SELECT name FROM world
WHERE name LIKE ‘B%’

How to use WHERE to filter records. Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

SELECT name FROM world
WHERE population = 63206900

List each country name where the population is larger than that of India.

SELECT name FROM world
WHERE population >
(SELECT population FROM world
WHERE name=’India’)

Find the largest country (by area) on each continent, show the continent, the name and the area:

SELECT continent, name, population FROM world x
WHERE population >= ALL
(SELECT population FROM world y
WHERE y.continent=x.continent
AND population>0)

SQL WHERE clause

Every programming language follows a unique set of guidelines termed syntax. Punctuation, spaces, mathematical operators, and special characters have special meaning when used inside of SQL commands and query statements. Check more information about it here.

SELECT * FROM tableName WHERE condition;

The following SQL statement selects all the customers from the country “Pakistan”, in the “Customers” table:

SELECT * FROM Customers
WHERE Country=’Pakistan’;

The following SQL statement selects all the customers from the subscription “Paid”, in the “Subscription” table:

SELECT * FROM Customers
WHERE Subscription=’Paid’

The following DELETE statement removes only those rows from table mytable where the column mycol is either NULL or has a value that is equal to 100.

DELETE
FROM mytable
WHERE mycol IS NULL OR mycol = 100

IN will find any values existing in a set of candidates.

SELECT ename WHERE ename IN (‘Paid’, ‘Free’)

BETWEEN will find any values within a range.

SELECT ename WHERE ename BETWEEN ‘Free’ AND ‘Pro’

SQL Syntax

Every programming language follows a unique set of guidelines termed syntax. Punctuation, spaces, mathematical operators, and special characters have special meaning when used inside of SQL commands and query statements.

SQL SELECT Statement

SELECT column1, column2….columnZ
FROM table_name;

SQL DISTINCT Clause

SELECT DISTINCT column1, column2….columnZ
FROM table_name;

SQL WHERE Clause

SELECT column1, column2….columnZ
FROM table_name
WHERE CONDITION;

SQL AND/OR Clause

SELECT column1, column2….columnZ
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;

SQL IN Clause

SELECT column1, column2….columnZ
FROM table_name
WHERE column_name IN (val-1, val-2,…val-N);

SQL BETWEEN Clause

SELECT column1, column2….columnZ
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;

SQL LIKE Clause

SELECT column1, column2….columnZ
FROM table_name
WHERE column_name LIKE { PATTERN };

SQL ORDER BY Clause

SELECT column1, column2….columnZ
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};

SQL GROUP BY Clause

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;

SQL COUNT Clause

SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

SQL HAVING Clause

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithmetic function condition);

Logical operators

The Logical operators are those that are true or false. They return a true or false value to combine one or more true or false values.

AND

Logical AND compares between two Booleans as expression and returns true when both expressions are true.

OR

Logical OR compares between two Booleans as expression and returns true when one of the expression is true.

NOT

Not takes a single Boolean as an argument and changes its value from false to true or from true to false.

In case you want to dig into this a little deeper, below you’ll find more resources.

Online Learning

SQL

Hour of Code

Additional Resources

SQL Skills for Product Managers - Department of Product

SQL Syntax

SQLZOO

SQL Logical operator - w3resource

A Beginner's Guide to SQL

Connect to a database - Help | DataGrip

How do I learn SQL? What are some good online resources, like websites, blogs, or videos?

👋 Feel Free to Clap and Share your Thoughts!

Find more at our LinkedIn, Instagram, and Twitter. Check our podcast. Follow our LinkedIn page and Newsletter!

Disclosure: At PM101, we strive to provide our readers with valuable and honest information on Product and Program Management. As a way to support the blog and continue providing valuable content, some blog posts may contain affiliate links or promotional content. By clicking on these links and making a purchase, the writer may receive a small commission at no additional cost to you. This commission helps to keep the blog running and allows the writer to continue providing valuable content and increasing her coffee and kombucha consumption. Rest assured, we will always provide honest and informative content and use affiliate links and promotional content only as a means to generate revenue to support the blog.