In my last post, I talked about why as product managers, we need to learn about databases and how we can go about it. In this part 2 of the series, I want to talk about basic SQL commands that are used widely for queries. I have already installed my Postgres DB, Pgadmin, and have a sample database (DVD rental) ready for queries. If you want to use a similar setup for learning, refer to my last post for instructions.
Let's get to it and start with the basic syntax of the SQL queries. These queries can be run in most of the SQL databases although there might be few subtle differences in syntaxes. The structure of a query looks like this
SELECT [this data element(s)/column(s)]
FROM [this table(s)]
Where [meeting these criteria]
That's it. This is all you need to know. Well, while that may be the case, you will be surprised how these 3 things can get super complicated based on your table structure and the information you are looking for.
There are 15 tables in this sample DVD Rental database. You can go to Databases/dvdrental/schemas/table to see the tables present in this database.
actor – stores actors data including first name and last name.
film – stores film data such as title, release year, length, rating, etc.
film_actor – stores the relationships between films and actors.
category – stores film’s categories data.
film_category- stores the relationships between films and categories.
store – contains the store data including manager staff and address.
inventory – stores inventory data.
rental – stores rental data.
payment – stores customer’s payments.
staff – stores staff data.
customer – stores customer data.
address – stores address data for staff and customers
city – stores city names.
country – stores country names.
Let's Start. We will start with a basic query and then keep refining it to learn new keywords and commands.
The queries I want to cover in this post are:-
Retrieve everything from the country table
Limit the number of results returned
Retrieve selected columns in the output
Retrieve results based on a condition
Retrieve unique values when duplicates are present
Retrieve count of a particular column
Sort the output in ascending or descending order
Do a wildcard search
Use Between Operator
Use IN Operator
Query 1- I want to retrieve everything from the country table in Dvd Rental DB. This is the simplest query of all. You can just use "*" to retrieve all columns of a table and since we are not looking for any specific criteria for our search to work, we don't need to give any "Where" Clause
SELECT * FROM country;
Keep in mind, we generally don't need to retrieve all the rows in the table as the number of rows can be in thousands/millions and we don't want the query to crash our servers.
Query 2-There are ways to limit the number of results by using the keyword LIMIT. For example, if we want to limit the above query to display only 10 results for the countries, we can write it like this
SELECT * FROM country
LIMIT 5;
Query 3- Let's further refine this query to only display country_id and country in the search result.
SELECT country_id, country FROM country
LIMIT 5;
We can give comma-separated column names that we want to show up in the results.
Query 4-Let's further refine this query to only display country_id and country but only for the country whose country_id is 5. Essentially we want to use the WHERE clause. We can use many operators such as (=, <, >, >=, <=, !=) to frame our condition. We can use operators such as AND, OR & NOT to combine multiple conditions
Here in the below example, we want to retrieve country_id and country where the country_id is 5.
SELECT country_id, country FROM country
WHERE country_id = 5;
Please note that we will have to use parenthesis for strings if we want to pass them in WHERE Statement such as
Here is another example.
Get us the phone number for the customer who lives at this address 1531 Sal Drive
SELECT phone FROM address
WHERE address = '1531 Sal Drive'
Query 5- There will be situations where some columns will have duplicate values and you may want to just look at unique values. Here is one example from Dvd Rental DB- The film table has a column for rental rates and it seems like there are few different rental rates but a lot of rows have the same rental values
SELECT * FROM film
We can use the keyword DISTINCT
SELECT DISTINCT rental_rate FROM film;
Query 6- There may be scenarios where you need to count the number of rows based on some criteria. For example, I want to know how many customers I have in the Dvd Rental DB. We can simply use the keyword COUNT for these kinds of scenarios
SELECT COUNT (*) FROM customer
Query 7- Sometimes we just want to sort the results in either ascending or descending order. We can do it using the ORDER BY Keyword. By default, ORDER BY sorts in ascending order but we can use ORDER BY DESC for sorting in descending order. Let's look at an example from our Dvd Rental DB. Here is the challenge
What are the customer I.D. of the first 10 customers whoever created a payment?
Let's explore the payment table
So, we need to get the ids and then sort them by payment_date in ascending order and then just show the top 10 results. Here is the query
SELECT customer_id FROM payment
ORDER BY payment_date
LIMIT 10
Query 8- Let's talk about scenarios where we need to do a wildcard search. We can use 'Like' for case-sensitive searches and 'ILIKE' for non case sensitive searches. Let's look at an example where we want to search for names from the actor table where first_name starts with 'A' and last_name has 'D' somewhere in it
SELECT First_Name, Last_Name FROM Actor
WHERE First_Name LIKE 'A%'AND Last_Name LIKE '%d%'
The % can be used before or after the character where you want to do the wild card search. There is a variation of the wildcard search where you want to replace just one character. We can use '_'. For example, if we want to search for all the films that have 'Mission Impossible' and you want to retrieve all the series like Mission Impossible 1, Mission Impossible 2, etc.
SELECT title FROM film
WHERE title LIKE 'Mission Impossible _'
Query 9- When we need to search for a result that is between some given ranges, we should use the BETWEEN keyword. For example, let find out how many payments are between $2 and $4 from the payment table. What we can do is
SELECT COUNT (*) FROM payment
WHERE amount between 2 AND 4
Query 10- When we want to check for multiple possible value options, for example, try to find all the data from the payment table where the amount is between a set of values.
SELECT * FROM payment
WHERE amount IN ('.99', '1.99', '2.99')
Well, it was fun to write and execute these queries. Please do practice running these queries as it will give you a lot of confidence.