SQL
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.
SQLite
SQLite differs from SQL in that it is not a ‘client-server database engine’. It is a RDBMS intended to work on the database of a file system of an application. Another key difference is that SQLite is ‘weakly-typed’ meaning that type conversion might automatically occur while the code is being interpreted. For example if we give a string in the place of an integer, SQLite will try to perform a data-type conversion. This is comparable to the way the javascript interpreter will try to convert types before evaluating an expression. For example console.log(‘1’ + 1) will result in ‘11’ being printed to the console. Javascript converts the number type to a string and evaluates the + as string concatenation.
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.
pets
| Name | Species | GuardianId |
|---|---|---|
| Barry | Zebra | 1 |
| Larry | Bear | 2 |
| Gary | Tiger | 1 |
| Leonard | Dog | 3 |
| Randy | Cat | 3 |
| Anastasia | Horse | 2 |
guardians
| Name | Id |
|---|---|
| Lisa | 1 |
| Bob | 2 |
| Frederick | 3 |
Looks like We've got some nice data to work with. Time to explore some basic commands with SQLite3!
Commanding
Viewing a table with SELECT
When used with * SELECT allows us to view all the columns in a table
sqlite> SELECT * FROM pets;
will display
| name | species | guardianid |
|---|---|---|
| Barry | Zebra | 1 |
| Larry | Bear | 2 |
| Gary | Tiger | 1 |
| Leonard | Dog | 3 |
| Randy | Cat | 3 |
| Anastasia | Horse | 2 |
However it is possible to select only one column with the following command
sqlite> SELECT name FROM pets;
will produce
| name |
|---|
| Barry |
| Larry |
| Gary |
| Leonard |
| Randy |
| Anastasia |
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:
| name | species |
|---|---|
| Barry | Zebra |
| Larry | Bear |
| Gary | Tiger |
| Randy | Cat |
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
| Name |
|---|
| Lisa |
Conclusion
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)