I Know Nothing About SQL

Getting Started

SQL stands for “structured query language”. It is the language used to communicate with a database. If you don’t know much about databases, I recommend reading the I Know Nothing About Databases article first.

SQL has two pronunciations: you can either spell it out, “S-Q-L”, or you can say “sequel”, like a follow-up movie in a series.

An executable or “runnable” chunk of SQL code is referred to as “a SQL statement” or simply “SQL code”. When the purpose of the SQL statement is to retrieve data/information from the database, we call it a “query”. For example, one may ask a SQL developer, “Hey, Bobby, can you please write a query to retrieve all of the sales transactions for the past two years?” Remember to say “please”. Developers do bite. Hard.

Types of SQL Statements

For the most part, SQL statements are labeled as one of the two: DDL or DML. Both are pronounced by saying the letters: “Dee-Dee-Elle”… LOL! Ok, that wasn’t necessary. We’re going too slow.

Data Definition Language (DDL)

As you read in the I Know Nothing About Databases article, databases are comprised of tables and other objects within them. When a SQL statement is written to create, delete, or update an object in the database, we categorize that as “DDL”. For example, a SQL statement that creates a new table named “Sales” is considered DDL. Also, a SQL statement which creates a new user in the database is also DDL. Used in a sentence, we may say, “Hey, Bobby, why are you biting hamster chewing sticks? That’s weird. While you’re at it, can you please write the DDL to create a table that will contain the sales information at a transaction level? I’ll write you an e-mail with all of the columns the table will require.” Remember to say please. We now know that Bobby chews on hard wood and must have very strong masseter (jaw) muscles. We do not want to get bitten.

DDL stands for “data definition language”. Creating database objects, such as tables and users, is considered DDL because you are writing scripts that define the objects needed to work with the data. Thus the name: data definition language.

The only analogy I can think for this at the moment is related to working out. If I decide to do dumbbell presses, I must obtain a workout bench (DDL to create the table in the database) and then I must adjust the workout bench. These two actions are related to working out (or databases), but you cannot say I actually worked out. These actions support the act of working out and that is why in this example it would be considered DDL.

Data Manipulation Language (DML)

As the title states, DML stands for Data Manipulation Language. When we “query a database or table” (meaning, run a SQL script to retrieve data), we categorize that type of SQL script as DML. The name is fitting because in a query you can add logic to aggregate and present the data from the database in different ways. Data manipulation language.

“Hey, Bobby. How in the blue hell are you doing chin-ups by biting onto the bar? That’s not even logically possibly……. Never mind. Can you please e-mail me that DML you wrote to retrieve the company sales for the last two years? Thank you, kindly.” If you haven’t already, I suggest you overcompensate with friendliness to get on Bobby’s good side.

While that last request we made to Bobby makes sense and may be heard in IT groups, it is not the most common way to refer to DML. We would usually say “Bobby, I’m extremely happy to see you and hope your family is well. Can you please, only if you have time, send me the query to retrieve the company’s sales for the last two years?” Use your intuition to assess Bobby’s mood. Adjust your physical location accordingly.

Anatomy of a SQL Query

By now we know that a “SQL query” is a SQL statement with intentions of retrieving data from a database table. In it’s most basic form, a SQL query consists of three major components: the SELECT clause, the FROM clause, and the WHERE clause.

SELECT
     Column_1
    ,Column_2
    ,Column_3
FROM
    SampleTable
WHERE
    Column_4 = 'Test'

In the sample query above, we are looking for data from the table aptly named “SampleTable”. We are going to filter the data using Column_4. We are only going to retrieve data where the value in Column_4 is equal to “Test”. Finally, we are going to present (or show) the columns Column_1, Column_2, and Column_3. Although SampleTable contains more columns, those are the only ones we chose to show. We know that SampleData has more than the three columns because we filtered using Column_4. Interestingly, we filtered on a column which is not being presented. That is normal in the SQL world.

Visually, a SQL query is seen in the order of: SELECT, FROM, WHERE. Logically, the system reads it as: FROM, WHERE, SELECT. This is because the FROM clause contains the source of the data. The WHERE clause filters the data collected from the source. The SELECT clause finally displays or outputs the data that was retrieved from the FROM and filtered by the WHERE. This make sense in a real world scenario. Logically, you narrow down your options, you don’t narrow up. If you’re trying to choose what clothes to wear, you go to your dresser (FROM clause/the data source), think about what color shirt you want (the WHERE clause/filtering), and finally pick out a specific shirt (the SELECT clause/showing the final result).

SQL for You

I believe SQL has a fairly quick ramp up period and many people can benefit from learning the basics. If you use any type of reporting (Excel, PowerBI, Tableau), or n=ever need to ask others for data, I’d recommend learning a bit of SQL. Know the basics of the language will allow you to help yourself, and in turn, make you more available to the company. Check out the book below if you care to learn the SQL language.

Further Reading