Basic SQL Commands

Share this post

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.

idnamelatin_namephylumordername
1Common brushtail possumTrichosurus vulpeculaChordataDiprotodontia
2grey-headed flying foxPteropus poliocephaluChordataChiroptera
3rainbow lorikeetTrichoglossus moluccaChordataPsittaciforme
4laughing kookaburraDacelo novaeguineaeChordataCoraciiformes
5eastern brown snakePseudonaja textilisReptiliaSerpentes
Database of animals in Australia

Let’s look at basic SELECT commands to help you interact with it.


Subscribe to my Newsletter


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
You Might Also Like  Simple SQLite3 Tutorial With Python

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.

animals table
locations table

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;
Joined table

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
Source: w3schools

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.

Enjoyed This Post?