You can add this to your previously constructed CASE - SELECT name, continent, indep_year,ĬASE WHEN (indep_year < 1900) AND (countries.name = countries.capital) THEN 'before 1900 and capital same' There are a handful of entries in the table where the name of country and capital is the same. PostgreSQL CASEs can have multiple conditions. for which no independence year is provided in the table. Upon executing the query, you will see many countries like Palestine, Puerto Rico, etc. (The query is inspired from DataCamp's Joining Data in SQL course.) WHEN indep_year <= 1930 THEN 'between 19' Let's now write the query - SELECT name, continent, indep_year,ĬASE WHEN indep_year < 1900 THEN 'before 1900'

And the independence years' group that you will create.

continent (to which the country belongs).

The final result of the query should contain the following columns. You will create three groups of independence years for this. Let's say you have three groups of independence years of the countries which you need to follow. Independence years are recorded in the indep_year column. You will now write a CASE to group the countries with respect to the independence year. You cannot return a string in result_1 and an integer in result_2 and so on. The data-type of the results has to be uniform. But when you are using CASE in pair with a where clause, you need not to follow this (more on this later). Keep in mind that, the column must be present in the SELECT statement of your query, on which you are specifying the boolean expressions (in CASE). Conditional query constructs like the above are typically done with SELECT statements. In case, you don't specify the ELSE part the query will return null.Ī condition is generally applied on a column that is present in the table you are operating on. If all the expressions corresponding to WHEN are evaluated to be False, then the result respective to the ELSE part is shown. Here are some critical points that you should keep in mind while constructing CASEs in PostgreSQL:Įach condition is a boolean expression and based on its output the result is chosen. The following describes the general form of a PostgreSQL case with WHEN-THEN construct - CASE Let's now see the anatomy of a basic PostgreSQL CASE. You can explore the table with your own quick questions, and along the way, you may find out interesting facts. It turns out that there are 23 different regions covered in this table such as Western Europe, Polynesia, the Middle East and so on. SELECT COUNT(DISTINCT(region)) from countries How many regions exactly? The below query should give you the answer to the question. The table contains details about a total of 206 different countries from various regions of the world. Upon running a simple SELECT statement, you get to know about the columns, their data-types and also the rows the table is containing. Exploring the countries table very brieflyĪfter restoring the database, you can view the table in the pgAdmin interface which comes with the default installation of PostgreSQL.

Let's now explore the countries table very briefly. You should have a table (populated with the details about different countries around the globe) by now if you restored the database backup. Also, you may want to form your own databases and tables. Feel free explore the tables provided in the database backup. This tutorial, however, only uses the countries table.

After you successfully restore the backup, you should be able to see these tables in the database. If you want to know how to do this, you can follow this article. You can use this backup file and restore it in your PostgreSQL server. Intro to SQL for Data Science by DataCamp.Feel free to refresh the basics with the following resources: Note: To be able to follow along with this tutorial you need to know the basics of SQL and PostgreSQL. You will do this in the first section of this tutorial. In this tutorial, you will learn how to do this.īefore you start writing condition queries, it is important that you set up a local PostgreSQL database. You can formulate conditional expressions in PostgreSQL using WHEN-THEN case which is very similar to if-else blocks. Common conditional expressions include if-else blocks and switch cases. Conditional expressions are one of the most fundamental elements of any programming paradigm.

Postgresql like ignore case