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.