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.
id | name | latin_name | phylum | ordername |
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 WHERE
statement.
SELECT * FROM animals WHERE phylum = "Chordata";
AND / OR SQL Boolean Operator
SELECT * FROM animals WHERE name = "rainbow lorikeet" OR name = "grey-headed flying fox";
IN Operator
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.
The "%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%';
Arithmetic Functions
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
- MERGE
JOIN tables Together
The JOIN
query takes multiple tables and joins them together.
If I take two tables, animals
and locations
, and I want to join them together, I will need to have at least one common key.
In the case above, the id
and 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
Conclusion
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.
SEO Strategist at Tripadvisor, ex- Seek (Melbourne, Australia). Specialized in technical SEO. Writer in Python, Information Retrieval, SEO and machine learning. Guest author at SearchEngineJournal, SearchEngineLand and OnCrawl.