Let’s look at the basic SQL commands to interact with a Database.
In my previous post to help you learn SQL, we built a database that looks like this.
|1||Common brushtail possum||Trichosurus vulpecula||Chordata||Diprotodontia|
|2||grey-headed flying fox||Pteropus poliocephalu||Chordata||Chiroptera|
|3||rainbow lorikeet||Trichoglossus molucca||Chordata||Psittaciforme|
|4||laughing kookaburra||Dacelo novaeguineae||Chordata||Coraciiformes|
|5||eastern brown snake||Pseudonaja textilis||Reptilia||Serpentes|
Let’s look at basic SELECT commands to help you interact with it.
Filter the Table Using WHERE
It is easy to filter the table by using the
SELECT * FROM animals WHERE phylum = "Chordata";
AND / OR SQL Boolean Operator
SELECT * FROM animals WHERE name = "rainbow lorikeet" OR name = "grey-headed flying fox";
You could get the same result by using the
IN operator instead.
SELECT * FROM animals WHERE name IN ("rainbow lorikeet","grey-headed flying fox");
Match Part of a String in SQL Using LIKE
The like operator uses
% as a wildcard to match anything.
"%SQL%" pattern would match all these:
- I love SQL
- SQL is the best
- That’s true, SQL is the best.
SELECT name, latin_name FROM animals WHERE latin_name LIKE '%Tricho%';
There are a lot of arithmetic functions that you can use in SQL.
- Aggregate: MAX, MIN, AVG, SUM, COUNT…
- Scalar: UCASE, LCASE, LEN, ROUND, NOW…
Use GROUP BY along with Arithmetic functions to aggregate data of the table.
SELECT phylum, count(*) FROM animals GROUP BY 1;
Work With Multiple Tables
When working with multiple tables, you will need to learn relationship management functions.
JOIN tables Together
JOIN query takes multiple tables and joins them together.
If I take two tables,
locations, and I want to join them together, I will need to have at least one common key.
In the case above, the
animal_id are used as the common key.
To join them together, it goes like this.
SELECT id, name, latin_name, country_id FROM animals JOIN locations ON locations.animal_id = animals.id;
There are different types of JOIN queries that merge data in different ways.
- INNER JOIN (Default)
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
I know this is very basic, and there are a ton of other ways to manipulate the database.
But, it is enough for you to get going using basic SQL commands.
Sr SEO Specialist at Seek (Melbourne, Australia). Specialized in technical SEO. In a quest to programmatic SEO for large organizations through the use of Python, R and machine learning.