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.
