Today I am pondering, "What is SQL?". I've heard this acronym mentioned by various tech personnel I've worked with over the years, but never bothered looking into it. Now that I'm aspiring to be "tech personnel" myself, I think it's about time that I “bother” checking it out.
Image by alaa kaddour from wikimedia commons
The History
SQL, pronounced "sequel", is an acronym for 'Structured Query Language'. It was created in the 1970s at IBM for IBM by employees Raymond F. Boyce and Donald D. Chamberlin. Mr. Boyce and Chamberlin based their work on Edgar F. Codd’s research on relational models, who was also employed by IBM. Mr. Codd published an article called “A Relational Model of Data for Large Shared Data Banks” in 1970 which “started the era of relational databases in computer science”((learnSQL.com)).
SQL was originally designed to interact with data stored in IBMs System R, its original database management system.
Before SQL, inflexible hierarchical network databases were being used for data storage. Every time you needed to retrieve data, you had to write a custom computer program. Then any time the data needed to be updated in some way, the data access patterns changed, and a new custom program had to be written to access it. Meaning data could only be accessed by programmers.
The Uses
SQL is a programming language used for managing data in relational databases. It specifically interacts with the data by: adding, changing, deleting, searching, and organizing data.
Jobs that regularly involve SQL are: business analysts, data analysts, data modeler, data scientists, data warehouse architect, database administrators, database developer, ETL developer, full-stack developer, hosting technicians, journalist, .Net developer, quality assurance testers, researchers, server management specialists, server engineers, SQL report writer, software consultant, software engineers, tech journalist, web designers-
Wow. Ok, yeah that’s a WHOLE lot of jobs, and not even all of them! So I think it’s pretty safe to assume that you will have bountiful career options to choose from if you learn this language.
But I want to know /specifically/ how it’s used in these careers.
The Details
SQL lets you create a database, which is something that you add tables to, that you add rows & columns to, that you then add data to. Columns can be labeled based on specific details you need to know about a product, rows can be for tracking all those details for an individual item.
For example, say you work in the medical field and need to keep track of certain information about your patients. Columns would have labels such as patient: name, age, height, weight, allergies, etc. And each row would have all that information for a single patient.
So when that patient comes in, you can search for their name to have instant access to their records. Or if you’re restocking inventory, you can search for patients who have the same allergies to estimate how much of a kind of medication to buy. You can also have the search exclude those who have that allergy but are allergic to the medication you’re restocking so that you don’t order what you don’t need. How convenient!
The Commands
SQL is a simple language. The commands, which are keywords called Statements, are all common words in English. This helps make it easy for users to know what they're asking the program to do. These keywords are not case-sensitive. “KEYWORD”, “Keyword”, and “keyword” will all be treated the same.
I will use Statements in all caps because I feel it makes it more noticeable that they’re important keywords.
Naming conventions for the Databases and labeling their contents seems to be varied, and the company you work for will have their own rules. I will be using snake_case, because I personally find it more readable.
Now, time to check out a few of them!
CREATE DATABASE
Used to create a new SQL Database.
EXample:
CREATE DATABASE Bunny_Database
CREATE TABLE
Creates a new table inside your Database & also the columns of the details you need to track.
Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
);
EXample:
CREATE TABLE Bunnies (
bunny_number int,
bunny_name varchar(100),
fur_color varchar(50)
)
Adding ‘int’ lets the code know that column will hold a number.
Adding ‘varchar’ lets the code know that this column will contain characters.
Adding ‘(100)/(50)’ lets the code know the maximum number of characters that can be entered in that column.
This will create an empty ‘Bunnies’ table that looks like this:
INSERT INTO
Adds a new row, record, to a table. You must target the table and columns in a table then give them values.
Syntax:
INSERT INTO table_name (column1, column2, column3)
VALUES (Example 01, Example 02', Example 03');
EXample:
INSERT INTO Bunnies (bunny_number, bunny_name, fur_color)
VALUES (‘001’, ‘Pepper’ ‘Black with White flecks’)
SELECT
Selects data in all or specified columns from your specified table and returns it to you.
Selecting data from only 2 columns.
Syntax:
SELECT column1, column3 FROM table_name;
EXample:
SELECT bunny_number, fur_color FROM Bunnies;
Selecting data in every column. EX:
SELECT * FROM Bunnies;
UPDATE
Used to modify data that already exists.
So say we have this table:
We could select data from the 2nd row and 2nd column and change it.
Syntax:
UPDATE table_name
SET column1 = ‘new value’
WHERE column1 = column1-value-at-the-row-you-want-altered;
EXample:
UPDATE Bunnies
SET bunny_name = ‘Freckles’
WHERE bunny_number = 002;
Which will change our table to:
DELETE
For removing records from a table. Using our table above again-
Syntax:
DELETE FROM table_name WHERE condition;
EXample:
DELETE FROM Bunnies WHERE bunny_name=’Lagombi’;
So now our table looks like:
The End
There are many MANY more commands that are just as straightforward as the examples I used above. A brief search leads me to believe that documentation is abundant and easy to follow. There are even programs that make managing SQL databases even more streamlined, like MySQL & SQLite. Overall the language looks very accessible for those who would want to learn it and the huge number of jobs where it’s used makes it seem worth the effort!
Top comments (0)