SQL, short for ‘Structured Query Language’ is a language used to manage data in a RDBMS, short for ‘Relational Database Management System’. The data in a relational database is ‘structured’, meaning that significant interrelations exist by means of references between ‘entities’ and ‘variables’. Originally developed at IBM in the early 1970s, SQL remains a ubiquitous database querying and manipulation language today. One of its primary advantages consists in that it boasts a ‘human-readable syntax’ via which introduction to and initial grasp of working components and syntax is facilitated rather effectively, as I found out upon meeting SQLLite3 for the first time not so long ago.
Relationships in our data
One of the basic relationships we will describe in our code example is the ‘many-to-one’ or ‘one-to-many’ relationship. In our example, we will see two tables, one of pets and one of guardians. When planning out how we want to structure our data, it is important that we consider how to accurately reflect the real nature of the relations between data. In this data, each pet has only one guardian, while each guardian can have multiple pets.
Imagine we want to reference all the pets that a given guardian is caring for. One way to do this would be to add a pets column on the guardian table. However, this column would have many different pets and perhaps would be messy to maintain. However, we could add a GuardianId on the pets table signifying which of the guardians is the given guardian of the pet. This way, we create a new column with only one entry per row, which makes querying rapid and efficient.
Generally in a one-to-many scenario, it is more logical to make reference to the one from an identifier in the many table, in this case, make reference to the guardian from the pet table. Lets take a look at a representation of our data below.
Looks like We've got some nice data to work with. Time to explore some basic commands with SQLite3!
Viewing a table with SELECT
When used with * SELECT allows us to view all the columns in a table
sqlite> SELECT * FROM pets;
However it is possible to select only one column with the following command
sqlite> SELECT name FROM pets;
Imagine, however, that we want to select the name and species columns from the pet table for all pets whose names end in 'y'?
Here we will use the wildcard % operator to accomplish our task.
sqlite> SELECT name, species FROM pets ...> WHERE name LIKE '%y';
This gives us:
For our last example, let's imagine we want to find out who the guardian of our tiger pet Gary is. Here we use a technique called sub-querying to select the field guardianid on pet, which is a foreign key that refers to id of a guardian in the guardian table. However, we only want the guardianid of the pet who has a species field value of 'tiger'. Thus the sub-query
sqlite> SELECT name FROM guardians ...> WHERE id IN (SELECT guardianid FROM pets ...> WHERE species = 'tiger');
Here we come up with
SQL and sqlite are powerful languages that allow us to interact with and manipulate data in a myriad of ways. Identifying the different relationships between data, such as one-to-many or many-to-one can help us structure our data appropriately with future queries in mind. In the end, it is up to us to properly structure the data and understand which relationships are most effective in designing a certain schema.
Top comments (0)