a relational database.

We will not delve into strict and detailed technical definitions to explain how it works.

What you need to know is there are a few types of programming out there – procedural (imperative),

object-oriented, declarative, and functional.

Although with some procedural elements, SQL is mainly regarded as a declarative programming

language, it is nonprocedural.

This means, while coding, you will not be interested in how you want the job done.

The focus is on what result you want to obtain.

An abstract example would best clarify what we mean here.

When using a procedural language, such as C or Java, you must explicitly divide the

solution of a certain problem into several steps.

For instance: 1.

Please, open the door.

2.

Go outside.

3.

Take the bucket I forgot there.

4.

Bring it back to me.

In a declarative language, this would sound like:

1.

Fetch the bucket, please.

And you wouldn’t have to go through the process step by step.

The algorithms are built-in, and there is an optimizer, which will separate your task

into smaller steps and do the magic to bring the desired output.

Why is this important?

When using SQL, you should concentrate on what you want to retrieve from your database.

Unless you are an advanced user, don’t bother with the algorithms explaining how your data

can be obtained.

Acknowledging SQL is principally a declarative language, now we can go through the main components

of its syntax.

It comprises a data definition language (known as DDL), a data manipulation language (abbreviated

DML), a data control language (DCL), and a transaction control language (TCL).

All right!

Good!

Let’s go over the components of SQL’s syntax.

It comprises several types of statements that allow you to perform various commands and

operations.

The Data Definition Language, DDL, is one type.

It is called a language, but you can think of it as a syntax, a set of statements that

allow the user to define or modify data structures and objects, such as tables.

One DDL statement is the CREATE statement.

As its name suggests, it is used for creating entire databases and database objects as tables.

For example, if we want to create a table, we must abide by the following syntax:

CREATE TABLE, object name, column name and data type in parentheses, and semi-column.

As expected, the CREATE statement creates an object with a certain name.

In this case, we are creating a table.

Given we have to assign columns to the table, after specifying the table name within parentheses,

we must also indicate the names of the columns and the type of data contained in each column.

Look at this example.

With this line of code, you can create a table with the name “Sales”, containing one

column, called “purchase_number”, that will store only integer values.

Easy, right?

Don’t worry if the table name coincides with the name we’ve assigned to the database.

This is a common practice.

On one hand, the syntax will make it clearer when you refer to the database and to the

table.

On the other hand, it is logical – it makes sense for a database to bear the same name

as its main table.

Ok.

Great!

Observe how simple the syntax of the CREATE statement is – you will have to write it

at the beginning of the line and then directly point out the type and name of the object.

Awesome!

And why did we start with an example where we created a table and not some other object?

Because it helps us understand better how the ALTER statement works, which is the operation

to be carried out when altering existing objects.

For example, we can use ALTER to modify a table by adding, removing, or renaming one

of the columns in the table.

To add a column, called “date of purchase”, you should begin with the same structure as

the one used for the CREATE statement.

After ALTER, you will be able to designate the object type and its name.

After that, you are supposed to write down the precise modification.

This means a new column containing the date of the purchase will be added to our table.

In addition, the values in this column will be of the DATE type.

Intuitive, right?

What if you want to delete a database object?

In that case, you can use the DROP statement to do that.

For instance, we can delete the entire “Customers” table with a single line of code: DROP TABLE

“Customers”.

As you can see, the DDL statement was accompanied by the object type and its name.

Another valuable tool when using the Data Definition Language is RENAME.

This piece of syntax allows you to rename an object like a database table.

If we hadn’t dropped the “Customers” table, RENAME TABLE “Customers” TO “Customer

Data” would have changed the name of the table to “Customer data”.

Ok!

Finally, instead of deleting an entire table through DROP, we can also remove its data

and continue to have the table as an object in the database.

In such a situation, TRUNCATE is the DDL statement that would allow us to do that

After executing this clause, all the records from the “Customers” table will be deleted

but, although empty, the table will continue to exist.

As a quick recap of this lesson, remember the Data Definition Language constitutes the

part of the SQL syntax that allows you to create, alter, drop, rename, and truncate

databases and their elements.

Once you have created a database with its elements and have loaded it with information,

you are ready to step up to the next level and manipulate the data to obtain various

statistics or other tables that will help you solve your business analytical problems.

To do this, you will have to get acquainted with the syntax of the Data Manipulation Language.

Let’s stop for a quick side note.

ADD is a keyword, and so are CREATE and ALTER, for instance.

Keywords in SQL cannot be variable names.

Think of them as words that constitute the language.

The SQL software you are using will understand which commands and operations you wish to

carry out, namely through these words.

To avoid any confusion for the software, objects or databases cannot have names that coincide

with SQL keywords.

To exemplify, think of all one-word statements, like CREATE and ALTER.

They are keywords.

So, you can’t call your table “alter”, since this is a name that can trigger a certain

process – the modification of the content of a designated table.

So, back to our example, ADD is the keyword that will add a column, called “Date of

Purchase”, in the “Sales” table.

It is one of the keywords frequently used with the ALTER statement.

Finally, don’t get confused if you hear the term reserved words.

It is a synonym for keywords, which indicates precisely what we explained; these words cannot

be used when naming objects.

Ok.

So that’s about it for keywords.

Now that we know more about DDL and some of its arguments, we are ready to focus on DML,

the Data Manipulation Language.

In particular, we will learn about some statements that allow us to manipulate the data in the

tables of a database.

We will start with SELECT, a statement used to retrieve data from database objects, like

tables.

The star after the SELECT statement in the following line will deliver the entire content

of the “Sales” table, meaning all records and fields contained in the table.

You can use the same structure to extract specific records from the table.

But at this point, one could rightly ask: What for?

We can see the entire table here.

Why are we going to need just a piece of it?

Well, imagine a table with two million rows of data.

You can only see a small part of it on the screen, right?

This is why it can be helpful if you could extract only a portion of the table that satisfies

given criteria.

This is crucial and is one of the most powerful tools available in SQL.

You should know how to use SELECT perfectly well, so you designate the precise area of

the table you would like to extract information from with ease.

In this course, many lessons and exercises include this statement, so by the end, you

will feel confident when using it and selecting a certain part of a database.

INSERT is used to insert data into tables.

It enables you to add more records or rows while you are working with the table.

This clause goes hand in hand with the keywords INTO and VALUES.

In the parentheses, after the table name, you will have to specify the columns you are

adding information to, unless you want to insert data in all columns.

For the moment, our “Sales” table contains only two fields, so if we want to add purchase

number and date values, we could omit the part with column names and parentheses, and

our statement would look like this: … The two versions are identical.

Syntactically, such statement makes sense, because it allows you to insert values right

into a table.

This line of code allows you to register the first record of the dataset.

It will appear as the first row of the “Sales” table.

This code will add another record - purchase number 2 and the date 27th of October 2017.

Amazing!

So far, we saw how to select and insert information in SQL.

Remember, you can also update information.

UPDATE allows you to renew existing data of your tables.

Its syntax is slightly different and is best understood with an example: …

This piece of code will allow us to substitute the previously inserted date of purchase number

1, 11th of October 2017, with the one specified here, 12th of December 2017.

And how do we get to this date, 11th of October?

The update is based on the number of the purchase - purchase number 1.

This way, the entire row is updated, and the date will be 12th of December 2017.

There is one additional DML clause I would like to show you in this lesson.

It is DELETE.

It functions similarly to the TRUNCATE statement from the Data Definition Language with one

substantial difference.

TRUNCATE allows us to remove all the records contained in a table; with DELETE, you can

specify precisely what you would like to be removed.

For instance, the following phrase… will remove all the records from the “Sales”

table, just as if we had written TRUNCATE TABLE Sales.

If we take advantage of the optional WHERE clause, instead, the following statement…

will delete the record with a purchase number of one, and the other one we have in our miniature

table will be left intact.

Our table will remain only with the second record – the one with purchase number two

and date of purchase 27th of October 2017.

To summarize, the data manipulation language syntax is composed mainly of the following

four elements containing statements and keywords: SELECT FROM, INSERT INTO…

VALUES, UPDATE SET WHERE…, and DELETE FROM, and optionally WHERE.

The Data Definition and the Data Manipulation languages are probably the ones you will use

more often.

The following two lectures are rather informative.

Some terms we will use will not be used later in the course; however, please pay attention

as they will widen your view about the activity domain of SQL.

The Data Control Language is a syntax containing only two statements – GRANT and REVOKE.

As you probably guessed, these statements allow us to manage the rights users have in

a database.

The thing is, a database is typically used by tens or hundreds, sometimes even thousands

of people, all members of the institution, organization, or company to which the database

belongs.

These people are called users, and they don’t necessarily have the same rights of access

to the information in the database.

This is where GRANT and REVOKE come into play.

GRANT gives (or grants) certain permissions to users.

The syntax to comply with is the following: …

Now, let’s do an elementwise breakdown of the structure.

With this syntax, one can grant a specific type of permission, like complete or partial

access to the resources from a designated table from a certain database.

These rights will be assigned to a person who has a username registered at the local

server that MySQL has provided with the workbench.

The server is based on our machine, and it’s commonly known as “localhost”, which is

the domain name for the local IP address of our machine – 127.0.0.1.

Naturally, big companies and corporations don’t use this type of server, and their

databases lay on external, much more powerful servers specifically designed to store big

amounts of data.

Those servers are not our “localhost” and are accessible on different IP addresses.

To illustrate this activity, granting privileges, we will show you how usernames are usually

created in SQL.

Then, we will grant certain rights to a user.