SQL is the programming language you need to execute commands that let you create and manipulate
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.
By typing this, we will create a user frank with password pass.
Say we’d like to allow frank to have access to certain resources in our database, like
the table containing sales data.
Then, this line of code will allow frank to apply nothing but the SELECT statement to
the “Customers” table from our database.
He won’t be able to use DROP, TRUNCATE, ALTER, INSERT, DELETE, or any other command
– just SELECT.
It will be the only manipulation he could carry out.
To prove this, let’s look at the screen.
This is the workbench that opens when frank logs in from his profile.
Don’t worry if it looks unfamiliar.
In a few lectures, we will explain in more detail how the MySQL workbench functions.
What is important here is that, as expected, Frank can only see the “Customers” table
in the “Sales” database.
He can’t get to the “Sales” or the “Items” table, for instance.
If we try to apply the ALTER statement and the ADD clause, we would obtain an error.
When we attempt to run a SELECT command, the output of the operation will appear immediately,
and we will get a sign that the command has been successfully executed.
This code here will allow frank to have complete access to all the tables of the “Sales”
database, as indicated by the star symbol.
Frank can carry out many more operations, including the ones you already know of: CREATE,
DROP, TRUNCATE, SELECT, INSERT, ALTER, and so on.
After this code has been run, we can open frank’s profile and see more tables have
been shown in the “Sales” database, not just the “Customers” table.
The ALTER statement will work too, not just the SELECT statement.
The rest of the DDL and DML statements are available too.
Great!
People who have complete rights to a database are database administrators.
They can grant access to users and can revoke it.
The REVOKE clause is used to revoke permissions and privileges of database users.
It is the exact opposite of the GRANT statement.
The syntax is identical.
Instead of granting permission TO somebody, one can revoke a privilege FROM somebody.
The example we saw a minute ago could grant the user frank the privilege to apply the
SELECT clause to the “Customers” table from the “Sales” database.
To revoke the same permission, we should type the following…
“Revoke Select on “Sales” dot “Customers” from frank, within single quotes, at localhost,
within single quotes”.
We can revoke from frank the permission to apply the SELECT command in the “Customers”
table.
He cannot use SELECT on this table anymore.
There is one final part of the SQL’s syntax we would like to introduce to you.
When working in real-life, not every change you make to a database is saved automatically;
you have to say you wish that to happen.
The statement to use in such cases is COMMIT.
It works only for changes made with the help of the INSERT, DELETE, or UPDATE clauses.
COMMIT will save the changes you’ve made permanently on the database and will let other
users have access to the modified version of the database.
For instance, imagine you have updated a record in the “Customers” table.
Say you wish to change the last name of the 4th customer from Winnfield to Johnson.
If you are an administrator, you can do that with the help of the UPDATE clause, and you
will see “Johnson” appear in the place of “Winnfield”.
The UPDATE statement we saw earlier would allow you to change the last name of the 4th
customer.
However, your work isn’t done just yet.
The rest of the users working with the database system cannot see you have made any changes.
To complete the entire process, you must add a COMMIT statement at the end of the UPDATE
block.
Only then will the updated information be available to all users – they will see Catherine
Johnson, not Catherine Winnfield, as customer number 4 in the “Customers” table.
Committed states can accrue.
If you are a database administrator, you might need to use COMMIT 20 times per day.
The Transaction Control Language clause that will let you make a step back and restore
the database to the last committed state is ROLLBACK.
This allows you to undo any changes you have made but don’t want to be saved permanently.
To apply this command, type ROLLBACK; Be careful when using this statement, because it will
revert to the last committed state, and all the changes made in the meantime will be removed.
There can be multiple statements executed, and they will all be undone.
To recap, COMMIT saves the transaction in the database; once issued, the changes are
saved permanently, and they cannot be undone.
ROLLBACK allows you to take a step back, and the last change, or changes, you’ve made
will not count.
Rollback reverts to the last non-committed state.
If you wonder why we went through the syntax in relative detail, you can think of the following
explanation.
The DDL, DML, DCL, and TCL statements are 4 separate parts of the SQL syntax that represent
what the language can be used for: creation and manipulation of data, assignment and removal
of permissions to use this data, and saving and restoring changes to a database.
These lectures served well to give you a good overall technical idea of why SQL is used
widely across companies and institutions around
the world.