What is SQL, and why should I care; or, An exercise in herding cats
This article will look at the history and modern day importance of Structured Query Language (SQL), and provide a brief example of its usefulness.
A Brief History
Structured Query Language, more commonly known as SQL, is a Domain Specific language developed by computer scientists at IBM in the early 1970s. It was developed as a tool to manipulate and retrieve data from within the IBM company database. Therein lies its domain specificity; SQL was created, and is used for the sole purpose of communicating with databases. It had the original acronym SEQUEL, but changed to SQL because of a trademark dispute. Both pronunciations of SEQUEL [ ˈsiːkwəl ] or S-Q-L [ ˈɛs kjuː ˈɛl ] remain in use to this day. In the late 1970s the U.S. government saw its use and SQL became commercially available in 1979. By 1986, The American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) adopted the standard “Database Language SQL”, and new standard versions have been released with regularity to keep up with the ever evolving structure of data.
Why should I care?
This is all well and good, but why does a 50 year old database specific computer language matter?
When broken down, SQL is quite a simple language. I preforms the basic CRUD operations of Create, Read, Update, and Delete with SQL commands eg. CREATE, SELECT, UPDATE, INSERT and DELETE or DROP. These commands, combined with search parameters result in simple/easy to understand, yet powerful search and manipulation language.
As a result of its usefulness and simplicity, most companies use SQL in some form. Data is getting more and more complex over time, and companies need ways to access and utilize the complex data they are storing. The standardization and frequent updates behind SQL ensures that it is a solid yet progressive language that can meet all data needs.
Because most companies use SQL, job seekers who are able to proficiently use it tend to have higher salaries. Companies need employees who know how to work with complex databases, and are willing to pay to keep them.
Finally, from a less employment focused standpoint, many general-purpose programming languages (Ruby, Python, Java, etc) utilize SQL , and knowing what is happening under the hood of your code is a good way to create powerful and flexible applications.
Digging a little deeper
Now that we are motivated to learn, let’s dig a little deeper into the fundamental structure of SQL. SQL is divided into five types of commands: DDL, DML, DCL, TCL, and DQL, each with specific goals.
1. Data Definition Language (DDL)
Used for changing the structure of a table
CREATE — — — — — Create a table or database
ALTER — — — — — — Modify values in tables
DROP — — — — — — Delete a table from a database
2. Data Manipulation Language (DML)
Used for modifying a relational database
INSERT — — — — — Insert new rows in a table
UPDATE — — — — — Update values of existing rows in a table
DELETE — — — — — Delete a row or entire table
3. Data Control Language (DCL)
Used to protect information in a database from unauthorized access
GRANT — — — — — Provide easy access to users
REVOKE — — — — Take back access privileges from users
4. Transaction Control Language (TCL)
Related to the transactions within a database. Used with DML commands, and are auto-committed in the database
COMMIT — — — — Deploy/apply /save changes in the database
ROLLBACK — — — Cancel/undo changes made in the database
SAVEPOINT — — — Temporarily save data in the database
5. Data Query Language (DQL)
Used for retrieving data from a relational database
SELECT — — — — — Fetch data from tables/database
Please help me find Kitty
Now that we are more familiar with our SQL commands and their purposes, let’s get some practice.
Our mission, should we choose to accept it, is to find Kitty. Kitty is missing. She was last seen by her twin sister Mimmy near her home in the London suburbs. She is a white kitty, and her birthday is Nov, 1. All we can use to find her in the database of missing kitties, is SQL.
The following will create an example of SQL commands to be used with an imagined SQLite database.
Step 1: Create kitty table
CREATE TABLE Cats (
id INTEGER PRIMARY KEY,
name TEXT,
color TEXT,
neighborhood TEXT,
birthday INTEGER
);The above creates a table called “Cats” that contains five columns: id, name, color, neighborhood, and birthday.
The id is an identifying number that is auto-incrementing, so our database can always keep track of our specific kitty.
The name, color and neighborhood columns will hold text characters.
The birthday column will hold an integer that we can format by Month and Date eg. 1101 for Nov, 1.
We now have a place to store information on our missing kitties when they are spotted!!
Step 2: Add Kitty
Someone has spotted Kitty! They add her to the database.
INSERT INTO cats (name, color, neighborhood, birthday) VALUES
('Kitty', 'white', 'London suburbs', 1101); Step 3: Look at our data!
To see what is saved in our table we run a basic SELECT statement.
SELECT * FROM cats;This returns us all (*) instances that have been stored in our table for found cats.
Unfortunately the rest of the world found out about our very important database and added their found cats… there are too many to look through ☹️
Step 4: Cats named Kitty!
Thankfully, SQL allows us to get specific with our searches. Let’s look for cats named Kitty.
SELECT * FROM cats WHERE name = "Kitty";Eep! Kitty is an extremely popular name. We got back far too many instances of cats named “Kitty” to find the Kitty we are looking for.
Step 4: AND
Thankfully for us, we can add more than one WHERE clause with an AND condition
SELECT * FROM cats WHERE name = "Kitty"
AND color = "white";Not so thankfully for us, we still have far too many kitties to sort through.
Step 5: ORDER BY
Hope remains! We know Kitty’s birthday. SQL gives us the power to order data in ascending or descending order, so rather than taking the easy way and searching for her birthday, let’s look at all of our options, but sort them in descending order by birthday (largest to smallest).
SELECT * FROM cats WHERE name = "Kitty"
AND color = "white"
ORDER BY birthday DESC;A quick look through birthdays, and we found her!! Thank you SQL.
In summary
SQL is an extremely simple yet powerful language that gives us the ability to organize and manipulate large amounts of data stored within multiple tables. It also gives us the ability to create a simple missing kitty database, should the need arise.
