SQL - Foundation of Databases

Every great coding language has evolved from another language that allows it to grow and gain the greatness that it has today. Many great modern day coding languages like React, Swift, Kotlin, Sinatra, Ruby, Python, etc. have risen from other foundational languages like Vanilla JavaScript, C++, Fortran, BASIC, and of course, SQL. These foundational coding languages set the scene for the development of newer languages that allow for more functionality, user comfort, cross-language compatibility, and so much more. SQL has led to the development of numerous database apps that use SQL either directly or indirectly. As directly coding with SQL may be slowly become phased out by other more user friendly applications, its value in database construction can not be understated. To fully appreciate the depth to which SQL has influenced the development of databases globally, let's take a look at its inner workings and functionality.

A Brief History of SQL
In June 1970, Dr. E.F. Codd published the paper "A Relational Model of Data for Large Shared Data Banks in the Association of Computer Machinery journal. Dr. Codd is credited for creating the definitive model for relational database management systems. After Dr. Codd published his findings, IBM developed the coding language Structured English Query Language (SEQUEL or SQL for short). After SQL's development, it has been the long standing relational database management language. From this language, many databases, applications, and coding languages have been developed.

SQL Keywords
One important thing to note when using SQL are the "keywords." The keywords allow us to tell SQL exactly what we need it to do. For example, if we want SQL to update an existing row, we would use the "UPDATE" keyword. There are many keywords and for a full list, please see the "SQL Keywords Citation" in the "Works Cited" section of this article. Here are a few keywords to give you an idea of what there is:

• CREATE DATABASE (produces a new database)
• CREATE TABLE (produces a new data table)
• DELETE (deletes a row from a table)
• DROP COLUMN (deletes a column in a table)
• JOIN (it joins together two or more tables)
• SELECT (selects data from a database/table)
• UPDATE (it updates an existing row in the table)

SQL in Code
Keep in mind that SQL is centered around a database. SQL may not be the code that makes a website look "pretty" but it most certainly serves as the backbone to support the database features available on the website. Let's take a look first at the data types accepted by SQL:

• Null: This represents "no value" (ex: null)
• Text: Any alphanumeric character used to represent text (ex: hello)
• Integer: A whole number value (ex: 4,5)
• Real: A number value that includes a decimal (ex: 2.45, 3.4).
• Blob: Generally used for holding binary data.

These data types are important for adding data to database table. Lets take a look at an example:

Let's say that the United Federation of Planets has requested our help in transferring their old spreadsheet table into a database table in SQL. In our example, we have this table of data we want to add to SQL:

Using VS Code, let's develop our SQL database table using the SQLite extension and add this command into our terminal:
$ sqlite3 federation_database.db

Now, let's create our table in the SQLite prompt:
Note: when we add the table, we need to include the name of the column, the data type (in call capital letters), and we need to specify the id as an integer and as the primary key so that the table can be connected to other tables.

CREATE TABLE alien_species (
  id INTEGER PRIMARY KEY,
  name TEXT,
  home_planet TEXT,
  federation_member TEXT, 
  light_years_from_earth REAL
)

From here, SQLite will create the table but it will not populate it with data. To do that, we need to use this SQL command to add an individual line in our database table:
INSERT INTO federation_database (name, home_planet, federation_member, light_years_from_earth) VALUES ("Humans", "Earth", "Yes", 0.0);

That's a lot of code! And keep in mind that that is only for just one line of code, what if we need to add more lines? Let's see how that will look:

INSERT INTO federation_database (name, home_planet, federation_member, light_years_from_earth) VALUES ("Humans", "Earth", "Yes", 0.0);
INSERT INTO federation_database (name, home_planet, federation_member, light_years_from_earth) VALUES ("Vulcans", "Vulcan", "Yes", 16.5);
INSERT INTO federation_database (name, home_planet, federation_member, light_years_from_earth) VALUES ("Romulans", "Romulus", "No", 1.3);
INSERT INTO federation_database (name, home_planet, federation_member, light_years_from_earth) VALUES ("Klingons", "Kronos", "Yes", 90.1);
...

You can start to see why other coding languages/apps have been developed to handle this monotonous coding. Keep in mind, those other coding languages/apps don't replace SQL but instead use SQL coding in the background while making the coding for the user easier. Hence SQL still serves as the backbone for the application even without the developer's knowledge.

Now that we've added the data, let's take a look at using CRUD on the data. We've already seen the "Create" side of CRUD, but what about the Reading, Updating, and Deleting the inputs?

SQL CRUD: Reading
Let's start with "Reading" by using Select.

In our example, let's say we want to look up a species by their name and want to get other data like their homeworld, we would need to use the following command to grab the data:
SELECT id, name, home_planet, FROM federation_database;

We will get this output:

1|Humans|Earth
2|Vulcans|Vulcan
3|Romulans|Romulus
4|Klingons|Kronos
5|Borg|Null
6|Cardassians|Cardassia

Note: you don't have to add the id number but it helps for demonstration purposes to include it

A faster way to retrieve all the data would be to use the "*" selector to retrieve all the data in our database table:
SELECT * FROM federation_database;
Outputs:

1|Humans|Earth|Yes|0.0
2|Vulcans|Vulcan|Yes|16.5
3|Romulans|Romulus|No|1.3
4|Klingons|Kronos|Yes|90.1
5|Borg|Null|No|1000.1
6|Cardassians|Cardassia|No|15.2

Another handy tool is to use the "WHERE" keyword to find a specific value. For example:

• example 1: SELECT * FROM federation_database WHERE name="Klingons"; Outputs this:

4|Klingons|Kronos|Yes|90.1

• example 2: SELECT * FROM federation_database WHERE light_years_from_earth > 80;

Outputs this:

4|Klingons|Kronos|Yes|90.1
5|Borg|Null|No|1000.1

SQL CRUD: Updating
From our example, let's say we messed up on the location of the homeworld of the Borg. We can change this by using:

UPDATE federation_database SET home_planet="Null" WHERE home_planet="Borg Alpha";
Outputs this:

1|Borg|Borg Alpha|No|1000.1

So instead of the Borg homeworld being "Null," we now changed it to "Borg Alpha."

SQL CRUD: Deleting
Finally, we can delete data from the database. In our example, let's say the Romulan homeworld was destroyed by a supernova, we will want to remove them from the database. To do so, we would use this code:
DELETE FROM federation_database WHERE id = 3;
Outputs this:

1|Humans|Earth|Yes|0.0
2|Vulcans|Vulcan|Yes|16.5
4|Klingons|Kronos|Yes|90.1
5|Borg|Null|No|1000.1
6|Cardassians|Cardassia|No|15.2

Conclusion
SQL, in our modern day coding, doesn't cut it as an efficient coding language but its ability to create and organize databases has become the cornerstone for most (if not all) electronic databases.

Discussion Questions

• What other coding languages use SQL under the hood?
• How do other coding languages make it easier to use SQL?

Sources Cited
Cover Photo

SQL History

SQL Keywords Citation

SQL Creating Tables and Adding Inputs