SQL Starter kit

In this blog, I wanted to give an overview of SQL with examples so that you are more familiarised with the SQL syntax and what you can accomplish with SQL.

What is SQL?

SQL stands for Structured Query Language, you are able to access and manipulate databases. Basically, SQL allows you to execute and call queries to the database to extract the information you require, these queries can be as simple as trying to extract all the customers in a database who live in a specific town, and as advanced as applying multiple condition and aggregations to extract the data you want.

Selecting Data

with SQL you will often be working with multiple databases that have relationships that connect them to one another. An example of a SQL Schema can look like this:

The lines represent relationships. the arrows refer to the direction of the relationship, which I won't be going into in this blog.

firstly you want to actually access the right database and columns.

SELECT * FROM jobs;

the syntax above is simple

• SELECT → this refers to what columns you are going to be getting. you could limit it as well,

- SELECT max_salary FROM jobs;
- SELECT max_salary, min_salary FROM jobs;
this will only get the max_salary column from the jobs database
in the first syntax '*' refers to all the columns in that database

• FROM → where are we getting the columns from? which database? in the case above its from the job database. this is important as you can see from above because the column job_id for example is in both the database jobs and employees

There is also a way to extract only the unique values in a column.

SELECT DISTINCT(job_title) FROM jobs;

This will only return the unique job_titles in the jobs database.

just to note as well a lot of people like to keep the SQL clean by separating each SQL Syntax by doing things like:

SELECT max_salary
FROM jobs;

both ways work and it just depends on what you prefer.

Conditions

now that we have selected our data we will need to extract what we want based on our needs. For example, do we only want salaries for certain job_titles?? do we want to see which job_title has the highest and lowest salaries?

• WHERE

SELECT * FROM jobs WHERE min_salary > 1000

the above query will look in our jobs database and return all those entries where the min_salary is greater than 1000.

This brings me to the next point. We can use >, <, = for conditional statements like above.

• AND/OR

we use AND/OR syntax when we want to add more conditions.

SELECT * FROM jobs WHERE min_salary > 1000 AND max_salary < 10000

in the above query, it will only return those entries that fulfill both conditions.

SELECT * FROM jobs WHERE min_salary > 1000 OR max_salary < 10000

in this query, it will return entries that meet at least one of the conditions.

• IN/NOT IN

this one is a little bit more tricky than the above, let me show you with an example different to the SQL Schema I showed above

Imagine we have two databases, Customers and Country.

SELECT * FROM Customers WHERE Country IN('UK', 'Italy', 'France')

the above query is selecting those entries where only those customers whose country is the UK, Italy, or France.

you can use the NOT IN in the exact opposite way as above

SELECT * FROM Customers WHERE Country NOT IN('UK', 'Italy', 'France')

it is selecting all those customers whose country is not UK, Italy, or France

There are many more syntax SQL offers to be able to dive in and extract more detailed information such as applying aggregations and even applying multiple Selects. I will be going over more detailed SQL syntax in future blogs as I didn't want to overwhelm you in a single blog but you can find all that information in the references below.

Good luck with your SQL adventures!

SQL - Syntax

SQL Introduction

Advanced SQL Queries, Examples of Queries in SQL List of TOP-50 Items in 2020 - ByteScout