What is SQL?

What is SQL and where did it come from?
SQL (which is short for “Structured Query Language”) is a querying language that can alter and retrieve data from the archives of a computer database. It was invented in the early 1970’s by two employees at IBM named Raymond Boyce and Donald Chamberlain, who had originally called the computer language “SEQUEL” (Structured English Query Language) before changing it to its current name. It was based off of a relational data model made by Edgar Codd.

What is a Querying Language?
SQL is a querying language, which is a kind of programming language; but what is a programming language? A programming language is the language used to communicate and give orders to a computer. A querying language is a type of programming language that specializes in sending requests/queries to a database in order to retrieve, change, or give the database the data specified in said requests (CRUD).

How to Create using SQL
Creating a new table and records in a database using SQL is simple. A table is a representation of a collection of data. A record is an instance of data contained within a table. First up will be the syntax for creating a new table in SQL:

CREATE TABLE TableName (
ColumnName datatype,
ColumnName datatype,
ColumnName datatype,
…
);

To create a table, you must type the statement “CREATE TABLE” followed by whatever name you want to call the table. You then put a pair of parentheses after the table’s name. Within these parentheses is where you put a list of all of the columns that you wish to add to the table. Each column consists of what name it has, what kind of data it can hold, and a space separating the two. There are lots of specific syntax used to define what type of data can be put into a column (a list of SQL data types can be found here); but some examples include “varchar”, “int”, and “date”. Here is the syntax for creating and inserting a new record:

INSERT INTO TableName (ColumnNameX, ColumnNameY, ColumnNameZ, …)
VALUES (valueX, valueY, valueZ, …);

To insert new records into a table, you must type the statement “INSERT INTO” followed by the name of an existing table. Going to the next line of code now will result in all columns in a table being given a new value based on the order you put the values into the next line of code, but if you want to specify which columns to give values to, then you should put a pair of parentheses after the table’s name. Within these parentheses is where you put the names of all of the columns that you wish to add values to. Go to the next line and type “VALUES” followed by a pair of parentheses that will contain each value you wish to give to each column. When providing values, make sure that the order and quantity of values match the order and quantity of the columns that you previously provided so that each column gets a corresponding new value.

How to Retrieve using SQL
Retrieving data from a table can be done by using the “SELECT” statement:

SELECT ColumnNameX, ColumnNameY, … FROM TableName;

To retrieve columns of data from a table, type “SELECT” followed by a list of the columns that you want to retrieve. After this, type “FROM” followed by the name of the table that you wish to pull data from. This command will retrieve entire columns from a table, but different modifications can be made to get different results. Replacing the list of columns with the “*” symbol will result in all columns being returned. Replacing “SELECT” with “SELECT DISTINCT” will result in the retrieved columns leaving out any duplicate info. Adding another line of code stating “ORDER BY ColumnName” will result in the retrieved records (which are the rows made up from the retrieved columns) being sorted from the smallest value to the largest value seen in the specified column. The “WHERE” clause can be used to retrieve certain records (rows) from a table that fulfill an if-statement-like condition:

SELECT * FROM TableName
WHERE ColumnName=value;

This statement will return the specified columns from a table, but if a record’s value that is checked by the “WHERE” clause is not equal to the expected value, then all of the values associated with that record will be left out from the result. There are also “AND”, “OR”, and “NO” operators that can be used to change how the “WHERE” clause works, but this section is long enough already. … :)

How to Update using SQL
Changing a record’s values in a table can be done by utilizing the “UPDATE” statement:

UPDATE TableName
SET ColumnNameX = valueX, ColumnNameY = valueY, ...
WHERE insert_condition_here;

To update a record, Type “UPDATE” followed by the name of your table. On the second line, type “SET” followed by the name of the column that you want to change, an equals sign, and what you want to change the value in that column to. Additional changes/reassignments of column values can be added to this line by separating each change with a comma. On the third line, type “WHERE” followed by a condition that will result in the column changes being made for each record that passes the condition.

How to Delete using SQL
Deleting records in a table can be done by utilizing the “DELETE” statement:

DELETE FROM TableName WHERE insert_condition_here;

To delete a record, type “DELETE FROM” followed by the name of the table that you want to delete a record from. After the table name, type “WHERE” followed by a condition that results in true for the records that you want to delete.

Analogy
A perfect example of a real-life data table that is commonly used is a company’s paper timesheet for one of its employees. The company’s printer is like SQL’s “CREATE TABLE” statement because it can be used to create new tables with columns that can have rows of data applied to them. An eraserless pencil is like SQL’s “INSERT INTO” statement because it can be used to insert a new record into a table by writing down a specific value for each column within one row. A company’s timesheet turn-in basket is like SQL’s “SELECT” statement because it is used to retrieve the timesheet table, which will allow whoever fills out the paychecks to retrieve the records needed from the table to complete the paycheck information. A pencil with an eraser is like SQL’s “UPDATE” statement because it can be used to change a record’s data within the timesheet table when the person who filled it out realizes that they had made a mistake. An eraser is like SQL’s “DELETE” statement because it can be used to remove a record from a timesheet table when the person who filled it out realizes that they had in fact not gone to work on Wednesday. Now let’s move on to a more technical example.

Example

The following examples will utilize the five main pieces of SQL syntax used to create, retrieve, update, and delete tables and/or table data. Each example will consist of a code snippet and the resulting table. The table in each example will be built/based upon the same table.

Example 1, creating a table:

CREATE TABLE CoolTanks (
TankName varchar(255),
MainGunCaliber varchar(255),
YearMade varchar(255),
);

SELECT * FROM CoolTanks

Example 1’s resulting table:

Example 2, inserting a new record:

INSERT INTO CoolTanks
VALUES
(‘M1A2 Abrams”, ‘120 mm’, ‘1985’),
(‘Leopard 1’, ‘105 mm’, ‘1965’);

SELECT * FROM CoolTanks

Example 2’s resulting table:

Example 3, retrieving a specific record:

SELECT * FROM CoolTanks
WHERE TankName=’M1A2 Abrams’;

Example 3’s resulting table:

Example 4, updating a record to fix an error:

UPDATE CoolTanks
SET YearMade =’1986’
WHERE TankName=’M1A2 Abrams’;

SELECT * FROM CoolTanks

Example 4’s resulting table:

Example 5, deleting a record from a table:

DELETE FROM CoolTanks WHERE TankName=’Leopard 1’;

SELECT * FROM CoolTanks

Example 5’s resulting table:

Conclusion
SQL is a querying language used to interact with archives in a computer. SQL is capable of creating, retrieving, updating, and deleting tables and their records within a database. SQL’s syntax makes it easy to create code/commands that carry out these functions. Despite being made in the 1970’s, SQL is still one of the top ways to manage and alter data.

Links to Sites Used