Geek Culture
Published in

Geek Culture

Getting your feet wet with postgresql

Understanding the basic queries of SQL

Creator: mirsad sarajlic | Credit: Getty Images/iStockphoto

Introduction:

This article discusses and introduces you to basic, yet essential SQL queries that we use in our day to day work. Though most of the projects today use ORMs like SQLAlchemy it is good to know how to write basic raw SQL queries.

Understanding raw SQL queries could also come in handy when the ORM's inherent methods or filters are limited and you need to construct a complex query.

Though most of the SQL query is syntactically similar across databases, few built-in function names may differ. In this post, we have taken postgresql as the database and we will execute queries for it.

Requirements:

  • homebrew
  • mac osx

Setting up postgresql:

We will use homebrew to install postgresql on mac.

Install postgresql

$brew install postgresql 
==> Downloading https://ghcr.io/v2/homebrew/core/krb5/manifests/1.20 ######################################################################## 100.0% ==> Installing dependencies for postgresql: krb5 ==> Installing postgresql dependency: krb5 ==> Pouring krb5--1.20.monterey.bottle.tar.gz 🍺 /usr/local/Cellar/krb5/1.20: 162 files, 5.1MB ==> Installing postgresql ==> Pouring postgresql--14.4.monterey.bottle.tar.gz ==> /usr/local/Cellar/postgresql/14.4/bin/initdb --locale=C -E UTF-8 /usr/local/var/postgres ==> Caveats ==> postgresql To migrate existing data from a previous major version of PostgreSQL run: brew postgresql-upgrade-database This formula has created a default database cluster with: initdb --locale=C -E UTF-8 /usr/local/var/postgres For more details, read: https://www.postgresql.org/docs/14/app-initdb.html To restart postgresql after an upgrade: brew services restart postgresql

Start postgresql

$brew services start postgresql 
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)

Stop postgresql

$brew services start postgresql

Create Database:

The below query creates a database and connects to it. The \c is the command to connect to a database. Just giving \c will connect to the default database as a default user. The \dt will show all the tables in that database just like show tables in mysql.

$psql postgres psql 
(14.4) Type "help" for help. postgres=#create database expenses; CREATE DATABASE postgres-# \c expenses dinesh; You are now connected to database "expenses" as user "dinesh".

Now let’s assume a table is already created and we have data in it. The table looks like below.

user_id |  name  |  product  | price 
---------+--------+-----------+-------
1 | Dinesh | Microwave | 9000
2 | user2 | Table | 9000
3 | user1 | Table | 3000
4 | user4 | TV | 60000
6 | John | Chair | 3000
7 | Tim | Chair | 300
8 | Tom | Wardrobe | 3000
9 | Jim | Cot | 2000
10 | user3 | toys | 900

SELECT Query:

The SELECT query is used to retrieve data from the database table. This is quite prevalent and most of the developers definitely would have executed this at least once.

expenses=> select * from myexpense;
user_id | name | product | price
---------+--------+-----------+-------
1 | Dinesh | Microwave | 9000
2 | user2 | Table | 9000
3 | user1 | Table | 3000
4 | user4 | TV | 60000
6 | John | Chair | 3000
7 | Tim | Chair | 300
8 | Tom | Wardrobe | 3000
9 | Jim | Cot | 2000
10 | user3 | toys | 900
(9 rows)

The character * retrieves every column from the specified table. As we haven't specified a WHERE clause, this will return every row as well.Using * is acceptable when you are working on a terminal. However, if you are using this query as part of your code, it is recommended to use the individual column names for better readability.

expenses=> select user_id,name,product,price from myexpense;
user_id | name | product | price
---------+--------+-----------+-------
1 | Dinesh | Microwave | 9000
2 | user2 | Table | 9000
3 | user1 | Table | 3000
4 | user4 | TV | 60000
6 | John | Chair | 3000
7 | Tim | Chair | 300
8 | Tom | Wardrobe | 3000
9 | Jim | Cot | 2000
10 | user3 | toys | 900
(9 rows)

We could also retrieve only a subset of columns by specifying the column names in the select command.

WHERE Clause:

If you have to retrieve a subset of rows, you could use a WHERE clause to do so.

expenses=> select user_id,name,product,price from myexpense where product='Chair';
user_id | name | product | price
---------+------+---------+-------
6 | John | Chair | 3000
7 | Tim | Chair | 300
(2 rows)

Applying multiple conditions to queries:

Let’s say we need to retrieve queries satisfying multiple conditions, we could use AND and OR with the WHERE clause.

For instance, if we need to retrieve all records of chair and the records that have price lesser than 3000 we should use the OR operator.

expenses=> select user_id,name,product,price from myexpense where product='Chair' or price <= 3000;
user_id | name | product | price
---------+-------+----------+-------
3 | user1 | Table | 3000
6 | John | Chair | 3000
7 | Tim | Chair | 300
8 | Tom | Wardrobe | 3000
9 | Jim | Cot | 2000
10 | user3 | toys | 900
(6 rows)

Similarly, if we need to retrieve all records of chair that has price greater than 300 we can use the AND condition.

expenses=> select user_id,name,product,price from myexpense where product='Chair' and price > 300;
user_id | name | product | price
---------+------+---------+-------
6 | John | Chair | 3000

Note: Please note that when using OR records satisfying either of the conditions will be retrieved and while using AND both the specified conditions should be satisfied.

Renaming columns:

If you wish to provide a different name to the columns retrieved, we could do that using AS keyword.

expenses=> select user_id,name as user,product as item,price as amount from myexpense;
user_id | user | item | amount
---------+--------+-----------+--------
1 | Dinesh | Microwave | 9000
2 | user2 | Table | 9000
3 | user1 | Table | 3000
4 | user4 | TV | 60000
6 | John | Chair | 3000
7 | Tim | Chair | 300
8 | Tom | Wardrobe | 3000
9 | Jim | Cot | 2000
10 | user3 | toys | 900
(9 rows)

Concatenating columns:

Now that we have learnt all the basic queries, let’s get our hands dirty with a little complex ones.

Let’s say we need data from multiple columns as a single column, we could use the || pipe operator. The output we need is user has purchased product as a single column named result. user1 has purchased table and so on.

expenses=> select name|| ' has purchased ' ||product as msg from myexpense;
msg
--------------------------------
Dinesh has purchased Microwave
user2 has purchased Table
user1 has purchased Table
user4 has purchased TV
John has purchased Chair
Tim has purchased Chair
Tom has purchased Wardrobe
Jim has purchased Cot
user3 has purchased toys
(9 rows)

Caveat 1: Ensure that the AS clause is mentioned before the FROM statement. If not, the FROM statement will be executed first and a temporary column name is given by the time the query executes AS statement.

Let’s see what happens if we place AS after the from statement.

expenses=> select name|| ' has purchased ' ||product from myexpense as msg;
?column?
--------------------------------
Dinesh has purchased Microwave
user2 has purchased Table
user1 has purchased Table
user4 has purchased TV
John has purchased Chair
Tim has purchased Chair
Tom has purchased Wardrobe
Jim has purchased Cot
user3 has purchased toys
(9 rows)

You see that? The AS has no effect here. We will discuss more about this in inline queries.

Using conditional logic in queries:

Now , we need to display Overpriced if the price > 3000 and Economical if the price is < 3000 in a column named budget. We will use case when..then statements to achieve this.

expenses=> select user_id,name,product,price,case when price > 3000 then 'Overpriced' when price <= 3000 then 'Economical' end as budget from myexpense;
user_id | name | product | price | budget
---------+--------+-----------+-------+------------
1 | Dinesh | Microwave | 9000 | Overpriced
2 | user2 | Table | 9000 | Overpriced
3 | user1 | Table | 3000 | Economical
4 | user4 | TV | 60000 | Overpriced
6 | John | Chair | 3000 | Economical
7 | Tim | Chair | 300 | Economical
8 | Tom | Wardrobe | 3000 | Economical
9 | Jim | Cot | 2000 | Economical
10 | user3 | toys | 900 | Economical
(9 rows)

Limit and Random records:

The limit and random() in conjunction with order by functions are used to limit and retrieve random records respectively.

Limit:

expenses=> select user_id,name,product,price from myexpense limit 5;
user_id | name | product | price
---------+--------+-----------+-------
1 | Dinesh | Microwave | 9000
2 | user2 | Table | 9000
3 | user1 | Table | 3000
4 | user4 | TV | 60000
6 | John | Chair | 3000
(5 rows)

Random:

expenses=> select user_id,name,product,price from myexpense order by random() limit 5;
user_id | name | product | price
---------+-------+---------+-------
7 | Tim | Chair | 300
4 | user4 | TV | 60000
10 | user3 | toys | 900
3 | user1 | Table | 3000
9 | Jim | Cot | 2000
(5 rows)

Searching for Patterns:

To retrieve rows matching a specific pattern or substring we will use the LIKE operator in conjunction with the wildcard operator %.

We will retrieve all product names starting with C.

expenses=> select user_id,name,product,price from myexpense where product like 'C%';
user_id | name | product | price
---------+------+---------+-------
6 | John | Chair | 3000
7 | Tim | Chair | 300
9 | Jim | Cot | 2000
(3 rows)

We can also mix and match conditional operators with patterns. Now, let’s repeat the same query with an OR condition.

expenses=> select user_id,name,product,price from myexpense where product like 'C%' or name like 'D%';
user_id | name | product | price
---------+--------+-----------+-------
1 | Dinesh | Microwave | 9000
6 | John | Chair | 3000
7 | Tim | Chair | 300
9 | Jim | Cot | 2000
(4 rows)

Inline Queries:

We have already discussed that we can rename the columns with names of our choice. What if we want to use the alias column names to filter the rows.

expenses=> select user_id,name as user,product as item,price as amount from myexpense where amount > 3000;
ERROR: column "amount" does not exist
LINE 1: ...duct as item,price as amount from myexpense where amount > 3...

Yeah that’s what I am talking about. So how do we handle these scenarios?

We could form inline queries in such scenarios.

expenses=> select * from (select user_id,name as user,product as item,price as amount from myexpense)x where amount > 3000;
user_id | user | item | amount
---------+--------+-----------+--------
1 | Dinesh | Microwave | 9000
2 | user2 | Table | 9000
4 | user4 | TV | 60000
(3 rows)

The alias query will become a sub-query for our main query. Our sub-query is named as x here. The where clause is applied on the results of the inline query.

Caveat 2: Why do we have to do this?As discussed earlier, the where clause is evaluated before the select. So the alias names like user, item and amount columns are unavailable when the where clause is executed.However, the from clause is evaluated before the where clause.Therefore the inline query gets executed first and then we are able to use the alias column names in the where clause.

Summary:

All the SQL queries discussed here are the most elemental ones that can be expanded to write more complex queries. The fundamentals of these are significant to slice and dice the data from a database.

Though all these queries are written for postgresql most of them could be used for other databases like MySQL, DB2, Oracle etc.

Originally published at https://dock2learn.com on July 14, 2022.

--

--

A new tech publication by Start it up (https://medium.com/swlh).

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store