Basic SQL Commands

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.

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.

Jean-Christophe Chouinard

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.