0:00
This video provides an introduction to the SQL query language.
0:04
SQL, like the relational model,
0:05
has been around for decades and
0:07
supports a many billion dollar market.
0:10
The first thing you might be wondering is how you pronounce it.
0:12
Is it "SQL," or is it "sequel"?
0:14
My friends in industry tell me
0:16
that "sequel" is the in
0:17
pronunciation, so that's the one I'll be using.
0:19
Now SQL is supported by
0:21
all major commercial database systems.
0:23
It has been around a long time and it is a standardized language.
0:27
The standard started out relatively
0:29
simple but over the decades it's really ballooned.
0:32
There are currently thousands of pages in the SQL standard.
0:36
But the essence of the language, which is
0:37
what we'll be learning in these
0:38
videos, is still relatively simple.
0:41
We will be learning primarily the SQL2 standard
0:44
also known as SQL 92 along
0:46
with some constructs from the SQL3 standard.
0:49
When SQL is used, it can
0:51
be used in a database system
0:52
interactively through a graphical
0:54
user interface or a prompt
0:56
so you type SQL queries or
0:57
commands and you get results
0:59
back, or SQL can be embedded in programs.
1:02
So, the most common use is
1:03
to embed SQL in programs but
1:05
for the demos in our videos,
1:06
naturally, we'll be submitting queries
1:08
through a GUI interface.
1:11
The last thing I wanted to
1:12
mention about SQL is that it is a declarative language.
1:15
That means that in SQL, you'll
1:16
write pretty simple queries that
1:18
say exactly what you want
1:20
out of the database, and the
1:21
queries do not need to
1:22
describe how to get the data out of the database.
1:26
The language is also based on
1:27
relational algebra, and I
1:28
hope you've watched the relational algebra videos.
1:31
Now, the declarative nature of
1:33
SQL leads to the
1:35
component of the database system called
1:36
the query optimizer to be extremely important.
1:39
What the query optimizer does is
1:41
it takes a query written in
1:42
a SQL language and it figures
1:44
out the best way, the fastest
1:45
way, to execute that on the database.
1:49
Now let's talk briefly fully about
1:50
some terminology and the commands that are in the SQL language.
1:53
There's two parts of the language,
1:54
the Data Definition Language or DDL,
1:56
and the Data Manipulation or DML.
1:59
The Data Definition Language includes commands to create a table.
2:03
We saw that in a previous video.
2:05
It also includes commands to drop
2:07
table and to create
2:09
and drop other aspects of databases
2:11
that we'll be learning about in later
2:12
videos, such as indexes and views.
2:15
The Data Manipulation Language is the
2:17
language that's used to query and modify the database.
2:19
So in the SQL language the Data
2:21
Manipulation Language includes for
2:23
querying the database, the select
2:25
statement and then for
2:26
modifying the database: an insert
2:28
statement, a delete statement,
2:30
and an update statement.
2:33
There are many other commands
2:35
in SQL for indexes, constraints,
2:37
views, triggers, transactions, authorization, all
2:39
of which we'll be learning about in later videos.
2:43
For now, let's just take a
2:44
look in a little more detail
2:45
at the select statement which is
2:47
really the bread and butter of
2:48
the SQL language and it's what we use to query the database.
2:52
So the select statement consists of
2:55
three basic clauses.
2:57
There's the SELECT clause, the FROM clause and the WHERE clause.
3:00
The best order to think
3:01
of these actually, is first the
3:03
FROM clause, then the WHERE and then the SELECT and just
3:06
the basic idea is that
3:08
the FROM identifies the relations
3:10
that you want to query over,
3:12
the condition is used to
3:14
combine the relations and to filter the relations.
3:18
And finally, the SELECT tells you what to return.
3:22
Now, if you're familiar
3:24
with relational algebra, this expression
3:27
here, this SQL query, is
3:28
equivalent to the relational
3:30
algebra expression that you project
3:33
the set of attributes A1 through AN.
3:35
And then you select and,
3:37
by the way, it's different from this select here.
3:39
In fact, this selection corresponds to the WHERE.
3:42
You select the condition on the
3:45
cross-product of the relations
3:47
that are listed in the from clause.
3:48
So that's the equivalent in relational algebra.
3:53
And the last thing
3:54
I wanted to mention is that,
3:56
as you know, the relational query languages are compositional.
3:59
That means when you run a
4:00
query over relations, you get a relation as a result.
4:03
So the result of this select
4:04
statement is a relation.
4:07
It doesn't have a name
4:08
but the schema of that relation
4:09
is the set of attributes that are returned.
4:12
We'll learn much more about the SELECT statement in future videos.
4:16
In conclusion, the SQL language is very prominent.
4:18
It's supported by all major commercial database systems.
4:21
It's been standardized over time.
4:22
It can be used through programs.
4:23
It can be used interactively and
4:25
it's a declarative high-level language
4:27
whose foundations are based on the relational algebra.