1
00:00:00,260 --> 00:00:03,000
This video provides an introduction to the SQL query language.

2
00:00:04,110 --> 00:00:05,510
SQL, like the relational model,

3
00:00:05,810 --> 00:00:07,240
has been around for decades and

4
00:00:07,450 --> 00:00:09,270
supports a many billion dollar market.

5
00:00:10,070 --> 00:00:12,250
The first thing you might be wondering is how you pronounce it.

6
00:00:12,400 --> 00:00:13,710
Is it "SQL," or is it "sequel"?

7
00:00:14,840 --> 00:00:15,980
My friends in industry tell me

8
00:00:16,080 --> 00:00:17,150
that "sequel" is the in

9
00:00:17,510 --> 00:00:18,960
pronunciation, so that's the one I'll be using.

10
00:00:19,680 --> 00:00:20,970
Now SQL is supported by

11
00:00:21,260 --> 00:00:23,050
all major commercial database systems.

12
00:00:23,860 --> 00:00:26,680
It has been around a long time and it is a standardized language.

13
00:00:27,470 --> 00:00:28,630
The standard started out relatively

14
00:00:29,320 --> 00:00:31,240
simple but over the decades it's really ballooned.

15
00:00:32,210 --> 00:00:35,240
There are currently thousands of pages in the SQL standard.

16
00:00:36,310 --> 00:00:37,470
But the essence of the language, which is

17
00:00:37,570 --> 00:00:38,330
what we'll be learning in these

18
00:00:38,460 --> 00:00:40,410
videos, is still relatively simple.

19
00:00:41,490 --> 00:00:43,840
We will be learning primarily the SQL2 standard

20
00:00:44,500 --> 00:00:46,180
also known as SQL 92 along

21
00:00:46,470 --> 00:00:48,580
with some constructs from the SQL3 standard.

22
00:00:49,680 --> 00:00:51,250
When SQL is used, it can

23
00:00:51,370 --> 00:00:52,490
be used in a database system

24
00:00:52,960 --> 00:00:54,440
interactively through a graphical

25
00:00:54,820 --> 00:00:56,140
user interface or a prompt

26
00:00:56,430 --> 00:00:57,710
so you type SQL queries or

27
00:00:57,770 --> 00:00:58,610
commands and you get results

28
00:00:59,050 --> 00:01:01,210
back, or SQL can be embedded in programs.

29
00:01:02,220 --> 00:01:03,610
So, the most common use is

30
00:01:03,850 --> 00:01:05,360
to embed SQL in programs but

31
00:01:05,520 --> 00:01:06,330
for the demos in our videos,

32
00:01:06,790 --> 00:01:08,090
naturally, we'll be submitting queries

33
00:01:08,850 --> 00:01:09,810
through a GUI interface.

34
00:01:11,080 --> 00:01:11,890
The last thing I wanted to

35
00:01:12,030 --> 00:01:14,630
mention about SQL is that it is a declarative language.

36
00:01:15,710 --> 00:01:16,750
That means that in SQL, you'll

37
00:01:16,960 --> 00:01:18,550
write pretty simple queries that

38
00:01:18,730 --> 00:01:19,810
say exactly what you want

39
00:01:20,100 --> 00:01:21,190
out of the database, and the

40
00:01:21,460 --> 00:01:22,650
queries do not need to

41
00:01:22,780 --> 00:01:25,330
describe how to get the data out of the database.

42
00:01:26,130 --> 00:01:27,270
The language is also based on

43
00:01:27,450 --> 00:01:28,670
relational algebra, and I

44
00:01:28,740 --> 00:01:30,400
hope you've watched the relational algebra videos.

45
00:01:31,650 --> 00:01:33,360
Now, the declarative nature of

46
00:01:33,610 --> 00:01:35,620
SQL leads to the

47
00:01:35,820 --> 00:01:36,800
component of the database system called

48
00:01:36,970 --> 00:01:38,960
the query optimizer to be extremely important.

49
00:01:39,880 --> 00:01:41,270
What the query optimizer does is

50
00:01:41,410 --> 00:01:42,470
it takes a query written in

51
00:01:42,770 --> 00:01:43,920
a SQL language and it figures

52
00:01:44,340 --> 00:01:45,410
out the best way, the fastest

53
00:01:45,930 --> 00:01:47,400
way, to execute that on the database.

54
00:01:49,170 --> 00:01:50,300
Now let's talk briefly fully about

55
00:01:50,460 --> 00:01:52,530
some terminology and the commands that are in the SQL language.

56
00:01:53,390 --> 00:01:54,300
There's two parts of the language,

57
00:01:54,770 --> 00:01:56,280
the Data Definition Language or DDL,

58
00:01:56,850 --> 00:01:58,990
and the Data Manipulation or DML.

59
00:01:59,980 --> 00:02:02,750
The Data Definition Language includes commands to create a table.

60
00:02:03,460 --> 00:02:04,620
We saw that in a previous video.

61
00:02:05,600 --> 00:02:07,070
It also includes commands to drop

62
00:02:07,300 --> 00:02:08,890
table and to create

63
00:02:09,480 --> 00:02:10,710
and drop other aspects of databases

64
00:02:11,270 --> 00:02:12,190
that we'll be learning about in later

65
00:02:12,500 --> 00:02:14,110
videos, such as indexes and views.

66
00:02:15,170 --> 00:02:16,830
The Data Manipulation Language is the

67
00:02:17,040 --> 00:02:18,850
language that's used to query and modify the database.

68
00:02:19,720 --> 00:02:20,960
So in the SQL language the Data

69
00:02:21,200 --> 00:02:23,110
Manipulation Language includes for

70
00:02:23,450 --> 00:02:24,820
querying the database, the select

71
00:02:25,130 --> 00:02:26,530
statement and then for

72
00:02:26,980 --> 00:02:28,430
modifying the database: an insert

73
00:02:28,740 --> 00:02:30,040
statement, a delete statement,

74
00:02:30,990 --> 00:02:31,670
and an update statement.

75
00:02:33,090 --> 00:02:34,950
There are many other commands

76
00:02:35,260 --> 00:02:36,490
in SQL for indexes, constraints,

77
00:02:37,110 --> 00:02:39,490
views, triggers, transactions, authorization, all

78
00:02:39,710 --> 00:02:41,800
of which we'll be learning about in later videos.

79
00:02:43,290 --> 00:02:44,270
For now, let's just take a

80
00:02:44,410 --> 00:02:45,390
look in a little more detail

81
00:02:45,820 --> 00:02:47,260
at the select statement which is

82
00:02:47,380 --> 00:02:48,640
really the bread and butter of

83
00:02:48,970 --> 00:02:51,450
the SQL language and it's what we use to query the database.

84
00:02:52,760 --> 00:02:54,730
So the select statement consists of

85
00:02:55,680 --> 00:02:56,690
three basic clauses.

86
00:02:57,570 --> 00:03:00,070
There's the SELECT clause, the FROM clause and the WHERE clause.

87
00:03:00,510 --> 00:03:01,480
The best order to think

88
00:03:01,710 --> 00:03:02,850
of these actually, is first the

89
00:03:03,000 --> 00:03:05,350
FROM clause, then the WHERE and then the SELECT and just

90
00:03:06,310 --> 00:03:07,900
the basic idea is that

91
00:03:08,090 --> 00:03:09,550
the FROM identifies the relations

92
00:03:10,190 --> 00:03:11,390
that you want to query over,

93
00:03:12,410 --> 00:03:13,870
the condition is used to

94
00:03:14,580 --> 00:03:16,920
combine the relations and to filter the relations.

95
00:03:18,160 --> 00:03:21,340
And finally, the SELECT tells you what to return.

96
00:03:22,170 --> 00:03:24,080
Now, if you're familiar

97
00:03:24,400 --> 00:03:26,590
with relational algebra, this expression

98
00:03:27,290 --> 00:03:28,770
here, this SQL query, is

99
00:03:28,970 --> 00:03:30,350
equivalent to the relational

100
00:03:30,980 --> 00:03:32,430
algebra expression that you project

101
00:03:33,930 --> 00:03:35,390
the set of attributes A1 through AN.

102
00:03:35,480 --> 00:03:37,640
And then you select and,

103
00:03:37,750 --> 00:03:39,350
by the way, it's different from this select here.

104
00:03:39,970 --> 00:03:41,910
In fact, this selection corresponds to the WHERE.

105
00:03:42,610 --> 00:03:45,500
You select the condition on the

106
00:03:45,590 --> 00:03:46,820
cross-product of the relations

107
00:03:47,430 --> 00:03:48,520
that are listed in the from clause.

108
00:03:48,850 --> 00:03:51,140
So that's the equivalent in relational algebra.

109
00:03:53,130 --> 00:03:54,360
And the last thing

110
00:03:54,440 --> 00:03:55,380
I wanted to mention is that,

111
00:03:56,210 --> 00:03:58,710
as you know, the relational query languages are compositional.

112
00:03:59,890 --> 00:04:00,740
That means when you run a

113
00:04:00,800 --> 00:04:02,910
query over relations, you get a relation as a result.

114
00:04:03,000 --> 00:04:04,500
So the result of this select

115
00:04:04,820 --> 00:04:06,240
statement is a relation.

116
00:04:07,240 --> 00:04:08,140
It doesn't have a name

117
00:04:08,290 --> 00:04:09,430
but the schema of that relation

118
00:04:09,970 --> 00:04:11,450
is the set of attributes that are returned.

119
00:04:12,690 --> 00:04:15,020
We'll learn much more about the SELECT statement in future videos.

120
00:04:16,040 --> 00:04:17,760
In conclusion, the SQL language is very prominent.

121
00:04:18,260 --> 00:04:20,480
It's supported by all major commercial database systems.

122
00:04:21,070 --> 00:04:22,240
It's been standardized over time.

123
00:04:22,650 --> 00:04:23,380
It can be used through programs.

124
00:04:23,890 --> 00:04:25,400
It can be used interactively and

125
00:04:25,680 --> 00:04:27,120
it's a declarative high-level language

126
00:04:27,910 --> 00:04:29,750
whose foundations are based on the relational algebra.