PostgresQL: Some Basic SQL Commands

PostgresQL is an object relational database management system, using the language SQL. SQL stands for Structured Query Language, and it is used to communicate with your database, in order to add or remove data, present data, or edit data.

A database can be thought of as a series of tables, each with rows and columns. Each column relates to a category, and each row is an instance of something or someone who has/or relates to each of those categories. Each instance has an identification number. Here is an example of a database table, a contact sheet for freelance software developers that a company uses:

Let’s say the name of this table is freelancecontacts. How might we access different parts of the table?

Selecting All Columns

If we want to select something from the table we use the command select. * is used to mean “all columns”. We also need to specify where we are getting the columns from, using the table name.

Therefore our query would look like:

select * from cd.freelancecontacts

Specifying Columns

What about when we want to specify certain columns instead of all of them? The basic syntax is:

select [some set of columns] from [some table or a group of tables]

So if we wanted just the first and last names of each of the freelancers, we could select them like this:

select firstname, lastname from cd.freelancecontacts

Selecting Columns Based on Information

We can also select all the freelancers that charge under $25/hr. We do this by selecting the columns that match up with a particular column where this is true. We use the query:

select * from cd.freelancecontacts where rate/hr < 25

Selecting Based on Patterns

What if the company wanted to select all the JavaScript developers? They would select based on a pattern. A pattern uses the syntax of '%pattern%'.

select * from cd.freelancecontacts where languages like '%javascript%'

What about if the company wanted the JavaScript developers but only ones that charge under $25/hr? They can use the and keyword, like so:

select * from cd.freelancecontacts 
	where languages like '%javascript%'
	and rate/hr < 25

Selecting Based on Multiple Identifiers

We can select multiple freelancers by their id, using the or keyword like so:

select * from cd.freelancecontacts where id = 2 or id = 4

Or a more concise way of doing it, using in, would be:

select * from cd.freelancecontacts where id in (2, 4)

Adding a Column Based on Information

Say we want to do something (add a column for instance), based upon a current possibility, we can use a case statement. Maybe the company has a budget that means that all freelancers who charge less than $25/hr are “affordable” and all others are “expensive”. They could make a query like this:

select firstname, lastname,
case when (rate/hr < 25 )
	then "affordable"
	else "expensive"
end as relationtobudget
from cd.freelancecontacts

This would return the table:

Selecting In Order, With Limits

To select in a certain order we use the term order by with either asc for ascending, or desc for descending. If asc or desc is not specified then the default will be ascending order. A limit can also be used, so that the top n amount of the category will be returned. To get the first three most expensive freelancers we can:

select firstname, lastname 
from cd.freelancecontacts 
order by rate/hr desc
limit 3;

Selecting a List from Two Tables

The company that employs the freelancers also has some in-house developers, and they would like a list of all of the languages that all possible employees know. The table below is called inhousedevelopers.

To find the languages of each person in both tables we use union:

select languages from cd.freelancecontacts 
union
select languages from cd.inhousedevelopers

Using Aggregates

A quick way to find the largest or smallest price is by using the aggregates max() and min(), like so:

select max(rate/hr) as mostexpensive
from cd.freelancecontacts

or

select min(rate/hr) as mostaffordable
from cd.freelancecontacts

To Use A Subquery?

How might we return the name of the person with the lowest hourly rate? We could use a subquery like so:

select firstname, surname 
from cd.freelancecontacts
where rate/hr =
	(select max(rate/hr)
		from cd.freelancecontacts)

However there are occasionally more concise ways to access information than using subqueries, like so:

select firstname, surname 
from cd.freelancecontacts
order by rate/hr 
limit 1;

If you want to practise these SQL commands by doing you can try the exercises at PostgresQL Exercises.