If you ever need to work with databases - and I believe at some point you definitely will! - you will certainly come across SQL. This is a very nice, intuitive, easy language and also really useful for data purposes.

If you're not familiar with SQL, I'll tell you a bit about it. SQL stands for Structured Query Language and the name helps to clarify which this language is used for. Pretty much every web app, for instance, relies on a system to store, manipulate and retrieve data, and this is where SQL comes into the scene, as a language. Let's put it like this: a database (and its schemas ) is like a menu, and the query is an order. We use the queries to tell the computer what information we want to retrieve, how you want it etc.

One of the best things about SQL, specially if you're not a programmer, is that it's a declarative language rather than a imperative language. In plain English, this means you don't need to describe every bit of the procedure the computer needs to perform in order to insert, manipulate or retrieve data from the database, as you certainly would in languages such as Java or C++, for example. This programming paradigm focus more on the what rather than on how to perform a task, and this is only possible due to something called Query Optimizer , which is, on Oracle's own words:

"(...) built-in database software that determines the most efficient method for a SQL statement to access requested data."

To put it simple: if the schemas are the menu, you just place the order - the query! - and SQL takes care of everything else. You don't need to call the waiter and tell them how they need to toast the bun, chuck the burger on the grill with some cheese, chop the onions, stack all the ingredients inside the bun etc. You just place the order.

Now that you already know a little bit about SQL, how about we dip our toes into the main concepts?

In this post, I do not intend to go through all the installation and setup of your mySQL server and SQL Workbench, but you can easily follow this tutorial:

For this post, my goal is to start a discussion on the basics of SQL syntax and in further posts we'll dive into more and more concepts. There are four main components on SQL syntax and we'll approach each one of them in separate posts. They are:

• DDL - Data Definition Language;

• DML - Data Manipulation Language;

• DCL - Data Control Language;

• TCL - Transaction Control Language;

So, basically, what are them and what's their role on SQL?

DDL - Data Definition Language

This is the component that allows us to define (or create), as well as to modify data structures and objects in your database. The syntax we use on the DDL will lay the foundations of the database and this is why this syntax requires the professional to be extra careful, as minor mistakes made here may cause you serious problems and lead you and/or your company to really messy situations.

The statements of the DDL are:

1. CREATE;

2. ALTER;

3. DROP;

4. RENAME;

5. TRUNCATE;

The CREATE statement will be used to create an object, such as a table. The syntax used is the following:

CREATE object_type object_name;

So, for instance, if we are creating a new table for this database, we must start the query with the CREATE keyword followed by the object type (TABLE) and then, in parenthesis, the name of the column and its corresponding data type. In the snippet below, we are creating a new table called 'purchases' with the columns 'item_no' and 'date_of_purchase', followed by their data types (INT for integers, and DATE for dates:

CREATE TABLE purchases (
    item_no INT,
    date_of_purchase DATE
);

Please note: in SQL is considered a best practice to use capital letters for keywords and commands and lower case for table names, columns etc.

As SQL is pretty intuitive, the ALTER statement is used exactly for what you're thinking: to change or alter objects, such as table. We can use the ALTER statement, for instance, to ADD, REMOVE or RENAME a column in the table. In our example, we can add a column named item_name:

ALTER TABLE purchases
ADD COLUMN item_name VARCHAR(16);

Remember: if we are adding a column, we need to specify the data type assigned for this column, which, in the example above, is VARCHAR. We'll discuss the data types on following posts. If we were removing the column, this requirement does not apply

The next DDL statement is the DROP statement. We can use the DROP statement to delete objects with a single line of code. For instance, if we wanted to delete the purchases table we created, we could simply enter the following code:

DROP TABLE purchases;

With this simple line we can get rid of this whole table. Always remember, the syntax is the statement + the object + the name of the object, such as above.

Now, let's consider we don't find the name purchases appropriate to this table and we want to give it a different name. This is when we use the RENAME statement. What do you think about renaming this table purchase_info? Sounds better? Ok, so let's do it:

RENAME TABLE purchases TO purchase_info;

Important: when renaming an object, we always need to refer the current name in order to create a new name for the relevant object.

Eventually, we changed the name of our table but we found out the data we inserted into this table is not accurate and we need to get rid of the data, but we don't want to delete the whole table and create it from scratch. Can we do it? Of course! Instead of using the DROP statement to delete the table, we can only delete the information contained on the table, keeping the table structure, using the TRUNCATE statement. The relevant syntax is:

TRUNCATE object_type object_name;

So, in our example, we could just wipe out the information from our purchase_info table using the following code:

TRUNCATE TABLE purchase_info;

And then our table would be blank, but preserved with the same definitions we established in our CREATE statement. Isn't it great?

On following posts, I'll be covering the other three components of SQL syntax.

Hope you enjoy and don't forget the semi-colon at the end of your statements! :)