select rows with a certain value using SQL

Estimated reading time: 2 minutes

You have now started to understand your database structure and want to extract data from it.

Data analytics projects can have lots of data, you need a way to get to what you need quickly and easily.

The question now becomes how would you achieve this?

The most easiest way is to add a where clause into your select statement.

On the below SQLite database, we are going to retrieve certain records from the name column, using our sql query.

The objective is that when we run the SQL code , it only returns the records we asked it to show us.

Writing the SQL code to return all rows where name = Ireland

Using the below code to extract our data

select * from error_dbase.country
where name = "Ireland"

it returns the following from the database

But what if we want to return more than one value?

Then you would just use an “in” statement, the SQL code would be changed as follows:

select * from error_dbase.country
where name in ("Ireland","Italy")

yielding a result as follows:

and this would also work if we had more than one of the same value as follows:

So to recap, we can choose one value on its own , using the “where” clause

or

we can use the “in” clause to ask the code to return records of multiple selections.

On our YouTube channel you can subscribe to find out more information about SQL, SQLite database and many more tips and tricks to help you with your Data Analytics.

To see a video tutorial explaining the concepts above, see below:

how to select columns with SQL

Estimated reading time: 2 minutes

Building on how to select all records with SQL , you may want to learn how to get specific columns of data from your table.

Using SQLite again , we will now look to select two columns from the table below, namely name and iso_code.

The purpose of completing this, is to allow the user to start to only choose the data they want.

As a database becomes bigger and more complex, so does the ability to retrieve the data.

Furthermore the speed and turnaround time becomes more critical.

What is more if these queries are been used for real time applications:

  • These results need to be precise to what is needed.
  • Additionally the speed of them returning back the records needs to be quick.

Subsequently below is the query run off the above table that we will use:

As you can see it has returned the two columns we asked for, excluding the description column.

To conclude, one thing to note this returns all records, that currently exist on the table for those two columns.

To see a video tutorial on this, watch the full video below:

how to select all records with SQL

Estimated reading time: 2 minutes

As part of working as a data analyst, in order to retrieve your data you will most likely need to connect into a database, and look at the database records and then start analysing, but how?

At the start of any data analytics project you will need to understand the structure of your data and what are the issues within it.

First of all you will need to connect to your database, and then open an SQL editor.

In this example we are going to use SQLite, it is very popular and can be downloaded from here .

Once you have this downloaded, you will need to create your database, tables and records.

Today we are going to retrieve records from a table that I have already created, as per the screenshot below

So that is the able above, but how would I use SQL to retrieve all its records?

In esseence it is very straight forward, all you need to do is run the following code:

select * from error_dbase.country

and it will provide you with the following output

and there you go, now you have retrieved all the records from the database table!

To see a video tutorial on this, watch the full video below:

How to data cleanse a database table

In Data Analytics, that is a very relevant question, and something I look to implement in most projects, sometimes it is too easy to click the shortcut icon to your spreadsheet application!

Here we are looking to bring a little automation into these videos. Building on How to import data from files and Removing characters from an imported CSV file this video connects to a Microsoft Azure cloud database table, brings in the data with errors on it, fixes the errors and displays the correct output on the screen.

What can this do for organisations?

There are several benefits to automating this step:

  • Less manual intervention if there is a need to fix data issues.
  • Better productivity.
  • Better data flows with no errors and quicker reporting.

 

Moving away from files

The process of moving away from files and into automation has several steps:

  • Be clear on your data needs.
  • Understand what you are trying to achieve.
  • Build a process that is repeatable but can be updated easily.
  • Ensure that you build in data quality checks, helps deliver the better output to the users.

Thanks for stopping by!

Data Analytics Ireland