All right! Great! One thing you’ll notice when studying programming
languages is that the concepts you encounter are interrelated. This means focusing on a single topic can
hardly deliver the content that must be explained in its entirety. To this story, SQL makes no exception. Despite that, we must start from somewhere,
right? My working experience tells me you will be
much faster in learning and writing efficient queries if you go through a brief introduction
to databases. It is the best thing to begin with, so here’s
what I would like to share with you. The table you see here contains data about
the customer sales of a furniture store. And, this is how we should read this information:
Purchase number 1 was registered on the 3rd of September 2016. Then, customer number 1 bought the item with
code A 1. Each of the four elements has a specific meaning. We call each one a data value. All four data values make up one record. A record is each entry that exists in a table. It corresponds to a row of the table. Therefore, these four data values form one
record, and these data values regarding purchase number 2 form another record. You could use the terms row and record interchangeably. Besides the rows, you can see the data is
separated into four columns or fields. A field is a column in a table containing
specific information about every record in the table. This means all the records in a table, regardless
if they are 10, 10 thousand, or 10 million, will have a certain purchase number, date
of purchase, customer ID, and item information. When the data you have is organized into rows
and columns, this means you are dealing with stored tabular data. This is important to mention, because you
will often see in the literature that database management relates to data stored in tabular
form. Great! Here comes the more interesting part. In this table, we know nothing about a customer
besides her ID. The information about customers is stored
in another table, called “Customers”. There are several fields, such as first and
last names, e-mail addresses, and the number of times customers have filed a complaint
in our store. So, what would the logic of that structure
be? Every time we have a customer with an ID number
1 in the “Sales” table, we can refer to the customer with ID number 1 in the “Customers”
table and see her name, email, and number of complaints filed. Same goes for the “Items” table. It contains the item code, product description,
its unit price, ID, and name of the Company that has delivered it, and the company’s
headquarters phone number. Here, the connection between the “Sales”
table and the “Items” table is not the customer ID, but the “item code”. Well, we could stuff this information into
one table, and it would look like this. Ouch! It is huge! I would not want to imagine what the table
would look like if we had registered over 10 rows! My point is – there are too many columns,
and it is hard to understand what type of information is contained in the larger table. Relational algebra allows us to use mathematical
logic and create a relation between a few tables in a way that allows us to retrieve
data efficiently. Namely, these three tables – “Sales”,
“Customers”, and “Items”– are related through the customer ID or the “item code”
columns and form a relational database. And, importantly, each one bears a specific
meaning and contains data characterizing it. One of the tables contains information about
sales, the other about customers, and the third about the items. To recap, remember the data values in a row
form a record in a table, and each column represents a field that carries specific information
about every record. A few related tables form a relational database. And, for those of you who are interested in
slightly more technical definitions, remember the smallest unit that can contain a meaningful
set of data is called an entity. Therefore, the rows represent the horizontal
entity in the table, the columns – its vertical entity. The table is a bigger data entity on its own. It can also be referred to as a database object. A single row of a table, being a single occurrence
of that entity, can be also called an entity instance. Ok. Great! We will gradually build the theoretical preparation
you need before you begin coding. Now that you know what a relational database
is and have an idea about how it works, it is much easier to understand how SQL fits
the whole picture. 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). Ok, as you can see here, the central part
of your screen is where you can create queries or SQL objects. In simple terms, it will be the area where
you will be typing code. For example, I can type a line of code that
will select all records of a table contained in a database. Ok! Please don’t pay too much attention to the
code used in this video! For the moment, however, concentrate on the
interface of MySQL workbench. Here, you can see a small set of icons executing
various functions. By clicking on the first one, you can get
to a window from which you can select and open an existing SQL script. The second icon allows you to save the script
on your computer and so on. The functionalities of most of the remaining
icons in this set will be explored later in the course. An important icon to remember is the one depicting
a lightning. By pressing it, you can execute or run the
code you’ve written. Let’s try this. Bingo! A new block appeared in the middle of the
screen. It is called the “result grid”, although
more often, you’ll hear people referring to its content as the “result set”. Obviously, here, you can see the data obtained
after running the code we’ve written. It is accurate to say that, in the middle
part of the screen, you can see the results obtained after executing your query. Finally, to close the result set, you must
press the cross sign on the tab indicated down here. Alright! At the bottom of the screen, we can see the
output section. It keeps track of all successfully or unsuccessfully
executed operations in MySQL in a given session. For instance, we obtained an output last time,
didn’t we? That’s why we see a little green circle
with a tick mark over here and the number and time of the operation executed. You can see the exact action undertaken, a
message from Workbench regarding this operation, and the time it took the server to reply to
your query with an output. Lovely! On the left part of the screen is the “Navigator”
section. It is relevant for advanced analysis and for
more advanced database maintenance sessions. The subsection we will care about most in
our course is the schemas section. It represents all available databases, their
tables, and other related SQL tools and features. The upper right part of the screen contains
three little squares that will allow you to hide or show the navigator, the output section,
or the SQL Additions section where we can find more advanced features if necessary. You can use these buttons to adjust the program’s
interface according to your preferences. Great! Finally, beneath the section with connections
tabs, we can see a few small icons. They allow us to add various types of files
and objects. When you hover over an icon, workbench displays
an explanation of what it does. As it says here, it will create a new SQL
tab for executing queries. So, let’s press this icon. You see? A new SQL tab opened. This is the place to start a new SQL script
from scratch. Now that you have more than one SQL tab open,
you’ll need a single click over a tab’s name to jump from one SQL script to the other. Easy, right? The second icon takes you to a window that
allows you to select and then open existing SQL scripts. Throughout the course, you will often need
to use this icon. Whenever we ask you to load a certain SQL
script, you must click on that icon, go to the directory where you’ve stored the respective
SQL script, select it, and then press the “Open” button. Amazing! This was an introduction to the main characteristics
of MySQL Workbench. Please, play around with its interface.
