Beginner friendly intro to SQL
My suggestion is to watch this 100 seconds clip before you read the tutorial. You'll listen some new words and might go "Woah, what was that?". The intention is to have the right context before you start with the actual stuff.
When you finish this tutorial, you should re-watch it again, hopefully things will be better.
You must have worked with tables, either in Pandas or Excel. Any table you imagine is well structured in rows and columns and SQL is similar to using Ctrl+F to find the records that you want. This process is called querying, which is why it’s known as Structured Query Language (SQL). Important to note that it is the domain language for relational databases. This means that SQL lets us work with more than 1 table. We can have a database where there are links between multiple tables. More on this later.
Every field in the database has a data type:
-
VARCHAR: Treats everything as text. you can place numbers in here but adding them together would just attach them which is what you would expect with text. Example:a + b = ab,1 + 2 = 12 -
INTEGER: Whole Numbers only. No text. Like,1 + 2 = 3 -
FLOAT: Numbers with decimal point values. Like,1.1 + 2.2 = 3.3 -
BOOLEAN: True or False value -
DATE: Date/Time value
This is helpful because the way we create a table in SQL goes like this:
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255));
The PersonID column is of type int and will hold an integer.
The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.
🔑 Coming to the brief mention of relational databases earlier, we now understand these two important concepts on Keys. Data in a database is spread out across multiple tables we need ways of bringing all that data back together. This is accomplished by using Primary and Foreign Keys.
- Primary Key: A unique identifier for each record in a table. No two rows can have the same primary key value.
- Foreign Key: A field in one table that links to the primary key of another table.
You just started your new job as a travel agent with the travel agency Born To Fly. As a travel agent, one of your main tasks is to extract relevant records that match your clients’ requirements by filtering out relevant data from the SQL database, stored as flights. These exercises introduce to you the key terms and the logic behind SQL queries. Images show what outputs look like. In practice, you will get much more rows than what's in the image.
In SQL, we use the main term which is SELECT to choose the columns. If you want to choose all columns, you use the asterisk *
Example:
SELECT d_from, d_to, price FROM flights;
SELECT * FROM flights;
You got a new client Jess, who is on a tight budget. You have to filter out rows for flights with a price below 200.
SELECT * FROM flights WHERE price < 200;
You add more conditions to the statement using AND, OR.
Sam, who live in Los Angeles, is planning his next family vacation and is considering Tokyo, Sydney or Honolulu. Filter out flights going to those three destinations. The = operator is not the ideal operator in this case!. Select only the flight number, destination, departure time, arrival time and price columns.
The output should look like this:
The skeleton code is: SELECT column_names FROM dataset WHERE condition 1 AND condition 2;
SELECT flno, d_to, departs, arrives, price
FROM flights
WHERE
d_from = 'Los Angeles'
AND d_to IN('Tokyo', 'Sydney', 'Honolulu');
BETWEEN: To filter records within a range.
LIKE: To filter records matching a pattern.
On w3schools, you can find the shortest examples for each term. There are output images too for the SQL queries.
Also, found this song by a gentleman who sings the order of these SQL terms Don't judge. It's catchy the second time