Part 2: SQL Query Language

Loading...

En provenance du cours de Arizona State University

Core Database Concepts

31 note

Arizona State University

Core Database Concepts

31 note

Cours 1 sur 5 dans Specialization Data Systems

This course introduces the world of database systems. It provides the foundation that will enable learners to master skills in data modeling and information, as well as extract information using existing database management systems. The following main topics are covered: database design/modeling, data storage and indexing, query processing/optimization, transaction management, database security, and data analytics.

À partir de la leçon

Basic Database Concepts

Part 1: SQL Query Language9:01

Part 2: SQL Query Language10:42

Assignment Directions: SQL Query for Movie Recomendation5:19

Assignment Solution: SQL Query for Movie Recommendation4:29

Rencontrer les enseignants

Mohamed Sarwat
Assistant Professor
Computer Science and Engineering

Hello, welcome back. In this video,

we'll continue talking about SQL.

So, as a reminder, so this is

just the same example we're going to use where we have the sailors,

two tables for the sailors and another table for

the reservations and we also might have

another table for the boats and the details of the boats.

And as a reminder of what SQL looks like?

We talked about the Select, From,

Where, and the Distinct.

These are the basic SQL constructs and we have the relation lists in here, in the From,

target list, in the Select,

and the qualification lists or the set of

qualifications in the Where and Distinct is just to remove duplicates if any.

And this is again, has to be specified by the user,

or the user who writes a query.

So if I want to find the SID's of sailors who have reserved a red or a green boat.

So one way we looked at it is to do this large joint here.

When you joined Sailors,

Boats and Reservations or Reserves and he did the equijoin on

this three way joint and you selected here the boat ID can be red or green.

Another way to do it is to use the union operator,

that's another operation you can do in SQL.

And it does basically it's union

the two sets of tuples in the first relation with the second relation.

In that case, you can do,

the first query will select all the sailors who have reserved a red boat.

And the second one will select all sailors that have reserved

a green boat and the union will just combine that together.

This whole union operation two SQL

up union together will return the same answer as you read the one up there.

The question here is that what if we replace the "or" like instead of

finding some sailors are reserved red or green.

If we say "and" reserved a red and the green boat. So what we need to do?

So in that case,

you can use the intersect operator.

So in that case, you can select red,

green and then instead of doing a union as we did before, we use intersect.

And intersect applies a set intersection on

the relation that as a result of the first query with the second query.

So this is like again,

between "or" and "and" we just made that use to the slight change.

However, to do this without the intersect it's a little bit

more like complex here because you

need to join the sailors with reserves and

then with the first version of boats and then another version of

boats so that to make sure that you have a sailor that reserved both red and green.

Okay, not just red or green.

So this is why it's a little bit more complex you're going to do it with

all these joins as opposed to doing it "or" and "and".

The difference between "or" and "and" it's just an intersection or a union.

And again, this is in the SQL standard like

intersect or union are in the SQL standard for long now.

What if there is another something else we can utilize a SQL,

which are like nested queries.

So what if I want to find names of sailors who have reserve boat 103?

So in that case, one way to do what you can do through a join for sure but

an easier way to specify it like SQL provide that for use that you can say,

select the names of the sailors from sailors where the sailor ID is in another set.

And this other set, you run another SQL query that returned only

from the reservations boats 103 with ID 103.

So in that case, this query will check whether it's a nested query.

And here, in the qualification you say,

the sid must be in the set of sid's returned from

the nested query and the set of sid's from the return in

the nested query are the set of sailors in

the reserves table that have reserved boat 103.

So you can do it also that way or you can do it using a join as we have said before.

But it makes it easy for the SQL query,

the application developer to write it using the "in" or instead of doing a join.

If you want to find sailors who have not reserved boat 103,

a simple way to do that instead of just using the "in",

you can replace this "in" with "not in".

So in that case, you return everything,

all the sailors that have not reserve boat 103.

Again, this is an easy way between the negation

and the actual qualification is just very easy switch,

while if you do the join it can be a little more complex to write the query.

Again, one way if your familiar with programming if you think

of like semantics of a nested query think of nested loop evaluation.

So for each sailor tuple,

you check the qualification by computing the subquery. Excuse me.

Why? Let's look at other examples.

So here, we select from sailors,

Where and we use the key word "exists".

And exist return true or false based

on whether there is some in the nested query return anything.

Then as you can see in the nested query,

you can use attributes from relations and

relation list in the query.

So in that case, you can say if there is a reservation for both 103 and

sid in the outside is equal to sid in the reserves in the nested query.

So this will return the same result of the previous query would we used "in".

However, "exists" it does not check for containment of the set.

It checks, it returns true or false based on whether there is something that

exists with these conditions or not.

It can be very useful for so many comp because to do this,

you can be very complicated again with the choice.

In nested queries we have seen "in", "exists",

there is also another keyword like another operation called "unique".

But also you can say "not in",

"not exist" or "not unique".

There is also "any" and "all",

which is very important when it comes to nested queries.

So here, if you want to find sailors who's rating is

greater than some sailor called Horatio.

So Horatio basically is a sailor and I want to

find any kind of sailors that have a rating larger than this guy.

So in this case, you can select everything like all sailors with name of Horatio.

And then in the upper like in the outside the nested query,

the main queries you say,

on the Select the sailor such that the rating is larger than "any" one named Horatio.

So this will simplify this kind of query.

Again, if you're going to do to join,

it's going to be way more complex than that.

Okay, one example, find the sailor ID's of

sailor who have with reserve both a red and a green boat.

We've seen how to do this using the intersect operation sequel.

But how can we do it using a nested query?

With the "in" specifically, the "in" keyword.

So to do that you want to Select,

you do the join on boats reserves and sailors.

You want to make sure that the sailor,

this is the equijoin condition in the qualification.

The other equijoin condition between the boats and the reserves.

And then color equal red.

Besides that, so again,

you got the color equal red.

So far, you have got this parts to get the green

also like all the sailors returned here needs to

be also in the set up sailors that return

the same inquiry but instead of color equal red it does color equal green.

So this is why you use the "in" that way.

So this way you've got the whole result of the query.

And to find the names not the sid's who have reserved a red and a green,

you have to replace the sid here with the s.sny.

So do it again, in the target list you have to specify what you need.

You can also select the boat ID here,

you can return also all the other operations and you can decide whether Distinct or not.

So this just to give you a little bit of a flavor of how SQL's are

very powerful language and it allows you to do the same exact operation in so many ways.

But the key point here,

is that you don't need to think

about the sequence of operation or all that you need to think is that how

can you declaratively use these different rich of

keywords to return the data that you want.

And then the database system will take that and

figure out the better way to optimize these.

And again, "in" and "exists" and all these nested queries

it would be replaced inside the database usually with a join.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play