query

SQL VIEWS + Complex Queries, Cross Joins, Unions, and more! |¦| SQL Tutorial



Sharing buttons:

I have dissected many hard drives, and let me tell you - databases can be a sticky and

complicated mess!

The number of tables... the unusual column names... the linking of tables.

It can feel overwhelming!

What’s an engineer to do!?

Well, do I have a solution for you.

VIEWS…

These virtual tables make it easy to search your database and avoid those

super-long queries.

It probably sounds too good to be true, right!

Well, it’s not…

It’s TRUE.

It is!

… It is!

It is!

It is!

It is!!

The date?

In the future.

The place?

Mars…

The mission?

Learn about views.

To start, suppose we have a table called “Martian Confidential”.

This table contains information on every person who is living on the planet.

Let’s take a look at the data in the “Martian Confidential” table...

Two of its columns contain sensitive information.

The salary in Solar credits should not be available to everyone.

And the dna_id is extremely sensitive.

Unauthorized cloning is a growing problem in the remote colonies.

How do we give engineers access to SOME of this data, but not these two columns?

By creating a view.

The first step in making a view is to write a query returning precisely the rows and columns

you want in your view.

We have been instructed that it is OK to share all data except the salary and dna_id columns.

Execute…

To create a view from this data, insert a line above the query.

Write “CREATE VIEW” and give your view a name.

We will call this view “Martian public”.

Then write AS…

That’s all there is to it!

Run this query…

The view is created.

You can now treat “Martian_public” as a table.

A quick select shows that the confidential data is not present.

And look what happens if you try to select the confidential data…

You would never know it was there…

That data is now a rumor, recognizable only as deja vu and dismissed just as quickly.

This is an important benefit of views: Security.

By giving engineers access to views instead of all the underlying tables, you control

the data they can see.

Let us introduce another table to our database: “visitor”.

This table holds a list of all people currently visiting Mars.

Here is our task: create a view that contains a list of all people on Mars.

This view should contain their first name, last name, a unique ID, and their status:

Martian or Visitor.

We start by writing the query to generate this list.

Our first SELECT gives us the information we need from the Martian table.

Each person in this table is a “Martian”, so in the SELECT clause include a “Martian”

string and call the column containing this string “status.”

Execute…

This gets us part way there.

We also want the names, status, and IDs of the visitors, too.

One way to combine two queries is with the “UNION” command.

This will combine the results of two SELECT queries into one result set.

You only need to be sure to line up columns of the same type in your two SELECT queries.

The two SELECTS need to have the same number of columns…

and the types must be the same.

Execute…

We are closer, but one problem remains.

The id.

Some visitors and Martians have the same numeric ID.

One solution is to prefix each Martian ID with the letter “m” and call this column

simply “id”.

We do this using the CONCAT function.

And in the visitor SELECT query, we will put a “v” in front of each id.

Now execute once more…

We now have a list of ALL people on the Red Planet.

Their names… status… and a unique ID.

We can now turn this into a VIEW.

At the top insert “CREATE VIEW” with the view name.

We will call this “people_on_mars”.

End it with “AS” and execute.

Engineers can now easily query the list of people on Mars by using this view.

Execute…

This is another benefit of views: Simplicity.

Instead of requiring people to query two tables or know how to use UNION, they can simply

write a SELECT using this view.

For our next example, we will use three tables in the Martian database:

Base...

Inventory... and Supply

The “Base” table holds information on all the different habitats on Mars.

The “Supply” table lists all items available in the central Martian distribution center.

And the “Inventory” shows what supply items are available at each base,

and in what quantity.

Here are the data for the three tables.

Look closely at the inventory data.

If a base does not have a supply item in stock, it is not listed with a quantity of zero...

Instead, it is not in the table at all!

One might debate this design choice.

Regardless, we have to play the cards we are dealt.

Especially if we want to be ready when the Olympus Mons Spa & Casino opens later this sol.

Our task is to create a VIEW called “base_storage” showing the quantity of all supply items

in stock at each base.

As a first step towards building the query, SELECT the base_id… supply_id… and supply_name

for all possible combinations of Base and supply item.

This is done with a “CROSS JOIN”.

This type of join performs a cross product between the two tables.

That is, it connects each row from the first table with each row in the second table.

This gives us all possible base/supply combinations.

Since there are 5 bases and 10 supply items, there should be 50 rows in this cross join…

And there are.

What remains is the quantity of each supply available at a given base.

We can obtain this value with a subquery.

In the SELECT clause, embed a SELECT to get the quantity from the inventory for this row’s

base_id and supply_id.

Execute…

Unfortunately, some of the values are null.

We do not want engineers to be concerned about nulls, so we will replace all nulls with zero.

We can do this with the “COALESCE” function.

This function returns the first non-null value it encounters.

If the inventory table does not contain the quantity, it returns null.

In this case, the quantity is actually zero.

Execute…

Grumble.

The column name is called “coalesce”.

Not a terribly descriptive name.

So we alias the output of the “coalesce” function as “quantity.”

Double execute…

Now we are talking…

This is the query we are looking for.

All that remains is to turn this into a “view”.

Return to the top and write the “CREATE VIEW” clause.

As instructed, we will call this view “base_storage”.

Run…

Success.

Let us now admire this view…

And what a wonderful view this is…

Our users can now query this view while the database designers continue to debate.

Thank you for VIEWING our video on VIEWs.

I hope you learned something NEW.

If not, maybe it was a good reVIEW.

It was, after all, a nice overVIEW… for YOU…

Subscribe…...