1. IntroductionIntroduction| 00:01 | I find databases terribly interesting,
whatever the application. Calendar,
| | 00:05 | email, websites, bookkeeping, sales,
inventory, even the lowly to-do list,
| | 00:11 | it's all about the data and that
usually means some sort of database.
| | 00:15 | (Music playing.)
| | 00:19 | Hi, I'm Bill Weinman and welcome to SQL
Essential Training. I have been working
| | 00:24 | with databases since the early 80s,
when I started working with George Tate on
| | 00:27 | dBase II, the first relational database
system available to the general public.
| | 00:32 | Most computer applications are about
data on some level and database technology
| | 00:37 | holds the key to making them work well.
SQL is the language you need to use to
| | 00:42 | talk to most database systems today,
and 90% of all SQL uses a few simple
| | 00:47 | variations of a few simple
statements and that's a scientific fact.
| | 00:52 | This course aims to give you the
knowledge you need to perform these basic
| | 00:56 | functions, so you can read most of the
SQL you see and even write your own SQL
| | 01:00 | for the most common uses. You will
also get guidance in designing for
| | 01:05 | flexibility in your databases, so that
you can get the most out of your data
| | 01:09 | after your application is designed and deployed.
| | 01:12 | Finally, you will gain skills in
designing and planning the most common form of
| | 01:16 | a database application called CRUD.
CRUD stands for Create, Read, Update, and
| | 01:21 | Delete, the four basic functions
of a database application. The CRUD
| | 01:26 | application is by far the most common
type of database application and that's
| | 01:30 | another scientific fact.
| | 01:32 | Now, let's get started
with SQL Essential Training.
| | Collapse this transcript |
| What is SQL?| 00:00 | SQL is a language for describing and
operating on relational databases.
| | 00:05 | What is a relational database? A relational
database is a database that's capable of
| | 00:10 | representing relationships
between different sets of data.
| | 00:13 | For example, a database may have a
table of customers and a table of addresses.
| | 00:19 | A single customer may have more than
one address. Also, a single address may be
| | 00:24 | used for more than one customer.
A relational database has facilities for
| | 00:28 | handling these relationships and SQL
was designed to use these features.
| | 00:33 | SQL is commonly used in two contexts.
SQL can be used to directly control the
| | 00:38 | Database Management System, to enter
data, query results, or to otherwise
| | 00:42 | manipulate the database.
| | 00:43 | For example, if you have a database
that's used by an application, say to
| | 00:47 | manage customers and addresses, and
you need a report that's not provided by
| | 00:51 | the application, you may type SQL
statements directly into your Database
| | 00:55 | Management System. This would give you
the results that you need without having
| | 00:58 | to modify the application program.
| | 01:01 | SQL is also commonly used by a program
to perform operations on the database as
| | 01:05 | part of an overall application.
| | 01:07 | For example, your customer management
program actually uses SQL to communicate
| | 01:12 | with the database and the programmers
who wrote the program put SQL statements
| | 01:16 | in the code along with the programming
language used to create the logic and
| | 01:20 | flow of the application.
| | 01:21 | You will see examples of this later in
the course when I show you the CRUD application.
| | Collapse this transcript |
| Understanding the terminology| 00:00 | SQL is a technical topic and whenever
you are dealing with a technical topic
| | 00:04 | it's a good idea to agree on the
terminology involved in the discussion.
| | 00:09 | I'll cover some of the more general terms
here in this movie and I'll cover more
| | 00:12 | specific terms as they come up. Also,
at the end of each movie you will find a
| | 00:16 | slide recapping terms covered in that movie.
| | 00:20 | A database is a store of tables and
tables are made up of rows and columns.
| | 00:26 | DBMS is a Database Management System.
This is the software package that manages
| | 00:31 | the data in the database. The DBMS
stores, retrieves, searches, updates, and
| | 00:37 | deletes data in the database. It also
typically controls access to the data.
| | 00:42 | A DBMS may be used directly by users or
may be used programmatically by another
| | 00:47 | computer program that
requires the use of a database.
| | 00:50 | SQL is Structured Query Language. SQL
is a language for interacting with the DBMS.
| | 00:57 | It's by far the most popular
language used for this purpose. SQL is used
| | 01:03 | both by users and by
programs to interface with the DBMS.
| | 01:07 | For example, here is a query in SQL,
and this query retrieves the Name,
| | 01:14 | Continent, and Capital of
countries and cities from a world database.
| | 01:21 | A database is usually segregated
into tables. Each table has its own
| | 01:25 | configuration and usually
carries its own class of data.
| | 01:29 | For example, a country table or an
album table. This is an example of a country
| | 01:35 | table and you can see it carries
information about countries.
| | 01:39 | This is an example of an album table that
carries information about record albums.
| | 01:44 | Tables are typically organized in rows
and columns. A row usually represents
| | 01:50 | all the information in a given record of data.
| | 01:52 | For example, for a given country in the
country table, a single row would have
| | 01:58 | the Name, Continent, and Region for
that single country. For an album in the
| | 02:06 | album table, a single row would have
information about the Title, Artist, and
| | 02:12 | Label for a particular album.
| | 02:15 | A column represents all like items
across multiple rows. For example, in the
| | 02:22 | country database, the Name column has
the names of all the countries in the
| | 02:27 | database. The Continent column has
the names of all the continents in the database.
| | 02:34 | In the album table, the Title column
has the names of all of the titles and
| | 02:40 | the Artist column has all the artists
across all the rows of the table.
| | 02:45 | Columns are sometimes called fields, a
more general term that applies also to
| | 02:50 | non-relational databases. Likewise,
rows are sometimes called records.
| | 02:55 | An individual item of data is sometimes
called a cell. A cell is one place in a
| | 03:00 | table that can hold one value.
It is one column in one row.
| | 03:05 | CRUD stands for Create, Read, Update,
Delete, the basic four functions of a
| | 03:10 | database application. The CRUD
application that is distributed with this course
| | 03:16 | is an application that creates, reads,
updates, and deletes album and track
| | 03:21 | information from the album database.
| | 03:23 | These are the major terms that will be
used during this course. More specific
| | 03:27 | terms will be covered in individual
lessons as they come up along the way.
| | 03:31 | Also, at the end of each movie you'll find
a slide recapping terms used in that movie.
| | Collapse this transcript |
| About the examples| 00:00 | In the Exercise Files folder, you will
find the CRUD application and the SID
| | 00:05 | application and a folder called SQL
that includes these files. These three
| | 00:11 | files contain the definitions and
the data for these three databases:
| | 00:15 | the album, test and world databases.
Now, let's go ahead and take a look at
| | 00:19 | the databases themselves.
| | 00:26 | The world database contains this
Country table, which has a lot of information
| | 00:30 | on 239 different countries around the
world, and it has a City table with over
| | 00:38 | 4000 rows of data on over 4000
different cities and different countries and
| | 00:44 | districts around the world. So, this
is lot of fun and it is very useful for
| | 00:48 | working with some larger amounts of data.
| | 00:51 | The album database includes albums and
tracks from a few different albums and
| | 00:56 | this is the database that is used by
the CRUD application that we'll be using
| | 01:00 | in the course and is also
included in the Exercise Files.
| | 01:03 | Finally, the test database includes a
simple customer sales database, not a lot
| | 01:09 | of data in it but it is useful for
inserting and deleting and testing silly
| | 01:13 | test data. Most of the SQL that we
show here in this course is what I call
| | 01:18 | platform agnostic SQL, wherever possible.
That means that the examples here will
| | 01:22 | generally work no matter what DBMS
you are using. Of course, there are
| | 01:26 | significant differences between
database management systems. So there will be
| | 01:31 | differences and there will be some
code that we'll need to change in order to
| | 01:34 | work on something other than MySQL.
| | 01:37 | The examples here are demonstrated
with the MySQL Database Management System
| | 01:41 | but the knowledge should apply to any
SQL Database Management System. I have
| | 01:46 | created this interface called SID for
use with the exercises for demonstrating
| | 01:51 | and for experimenting with the
examples that I show during the course.
| | 01:55 | SID requires MySQL, PHP and a web
server, typically Apache, which are all
| | 02:01 | available for Windows, Mac OS X, Linux
and most other operating systems.
| | 02:05 | For Windows and Mac OS X environments,
I suggest the XAMPP platform, which has all
| | 02:12 | the necessary modules installed and it
is easy to install and there is a movie
| | 02:17 | here in the course and I'll show you how
to install it both on Windows and on Mac OS X.
| | 02:21 | Most Linux installations already
have the required packages already installed.
| | Collapse this transcript |
| Setting up the test environment on a PC| 00:00 | Now, I'm going to show you how to
install a test environment on a Windows
| | 00:04 | machine so that you can test the
examples that come with the SQL Essential
| | 00:08 | Training course. We'll be installing
the XAMPP web server. It is Apache web
| | 00:14 | server with PHP and MySQL, which is
what we need, and we'll be installing the
| | 00:19 | Notepad ++ text editor.
| | 00:21 | The reason you need a text editor
is because we'll be editing some
| | 00:24 | configuration files and unfortunately,
the text editor that comes with Windows
| | 00:29 | doesn't do a very good job with files
that don't have Window's very special
| | 00:33 | line ending characters in them. And so
this is a good quality text editor.
| | 00:37 | You don't want to edit these files with
a word processor because they tend to
| | 00:40 | insert a lot of other information
into a file, which is great for word
| | 00:44 | processing, but it really messes up
flat text files like configuration files.
| | 00:48 | So, you need a good text editor.
This is a good one, it is free. We have
| | 00:51 | already downloaded these packages and
so we'll go ahead and install them now.
| | 00:56 | This is the Notepad ++ Installer.
Just double-click on that and say OK and
| | 01:02 | follow the bouncing ball and that is
pretty much all there is to it. Install it
| | 01:07 | in its default location, all of these
options look fine and there we go. There
| | 01:14 | is Finish and it brings up the Notepad
++ editor with its little change log and
| | 01:20 | we'll just go up and close
that. We'll be using that later.
| | 01:23 | Now, we are going to install XAMPP.
This installs Apache, MySQL, PHP and a
| | 01:28 | bunch of other stuff. Select English
and just say OK to this. Now, we are going
| | 01:35 | to press Next. We'll go ahead and
install it in this folder. The default folder
| | 01:41 | makes everything easy. It will have to
edit a lot of configuration files and we
| | 01:44 | are going to leave all this exactly
the way that it is. We are not going to
| | 01:48 | install these things as a service. This
is a test server. You don't necessarily
| | 01:51 | want it running all the time and click Install.
| | 01:55 | Now, when we press Finish, we'll
probably still get a couple of little dialog boxes.
| | 01:59 | That happens sometimes. There we go,
pressing Yes and the Control Panel
| | 02:05 | is here. We can start up MySQL first.
I like to start MySQL first because
| | 02:10 | Apache uses that and then go and start
Apache. Now, both of these are running.
| | 02:15 | We don't need the Control Panel
after they are running. Close that.
| | 02:18 | Now, we'll go ahead and start up the
Firefox web browser and type in localhost
| | 02:26 | and we can see that the Apache web
server is running. I'll go ahead and
| | 02:30 | maximize this to full screen, click on
English. Now, we are running a PHP file
| | 02:35 | so we know that that's working.
There is our phpinfo. We know that that's
| | 02:40 | working. These little demos here, you
can just click on each of these real quick.
| | 02:44 | These test some of the features
that are installed with XAMPP. Make sure
| | 02:48 | that they are all working. And finally,
click on phpMyAdmin and there we are.
| | 02:55 | That means that MySQL is also working.
This is a test server. This is not
| | 03:00 | running on a public web server. So, we
are going to do things a lot differently
| | 03:03 | here than we would do if we were
running on a web server that was accessible
| | 03:06 | for the world. Here, we are looking
for convenience. We are looking for the
| | 03:10 | test server to get out of the way so
that we can learn about the things we need
| | 03:14 | to learn about, running
the exercises for the course.
| | 03:17 | So, the first thing we are going to
do is go over here to Privileges and
| | 03:22 | install the MySQL user that will be
used by the example programs. That user is
| | 03:27 | called web. So, go ahead and type web
here and it will be used from localhost
| | 03:36 | and it will not use a password. Again,
this is not for a public facing web server.
| | 03:42 | This is for your own personal
computer that's locked up in your own
| | 03:45 | personal ways. Go ahead and Grant
all privileges. Check All down here and
| | 03:50 | scroll down and press Go and this will
install the web user @ localhost that
| | 03:56 | will be used by the CRUD program and
by the SID program and in whatever other
| | 04:02 | ways we want to use it.
| | 04:03 | Now, we are going to go over to
Databases and I'm going to get rid of the test
| | 04:09 | database that is installed by default
and we are going to recreate that in the
| | 04:14 | way that we need to recreate it
along with a couple of other ones.
| | 04:18 | DROP DATABASE 'test', Yes and go ahead and create
a new database. We'll start with the test database.
| | 04:24 | Now, under Collation, I want to select
the latin1_bin, the default, believe it
| | 04:32 | or not, for phpMyAdmin is latin1_swedish.
I want latin1_bin there, the default latin1.
| | 04:40 | Create that. Go back and
create a couple of more databases.
| | 04:47 | I'm going to create the album database
and in the same way, select latin1_bin.
| | 04:56 | And I'll go and create the world
database also with latin1_bin.
| | 05:08 | There we go and now before we go any
further, I'm going to close this.
| | 05:14 | Okay, let's open up Windows Explorer and
find the file that we need to edit. It is on
| | 05:19 | the C drive and it's under the XAMPP
directory and in phpMyAdmin. It is called
| | 05:25 | config.inc.php. Just right-click on the
file and click on Edit with Notepad ++.
| | 05:34 | There we go.
| | 05:35 | Right between this i++ and
Authentication type and info or really anywhere
| | 05:42 | where there isn't a comment. It
doesn't really matter that much. I'm going to
| | 05:45 | type this $cfg['UploadDir'] = 'c:\
Files';. I'm going to save this file.
| | 06:13 | I will press File>Save. I pressed the
Ctrl+S key so that's already grayed out.
| | 06:18 | The file is saved. We can close the
editor. Now, what we want to do is we want
| | 06:22 | to make sure that the files exist
where we want them to be. What these files
| | 06:26 | are and I have already the directory
here under the C drive. The Files with
| | 06:30 | capital F and it has these three files
in it. Let's change the view so that's
| | 06:34 | easier to see.
| | 06:35 | These are the files out of the
Exercise Files folder and you just copy those
| | 06:41 | over into this directory. The purpose
of these is to populate the databases
| | 06:46 | that we are creating and if we drop
these in this folder and we make that edit
| | 06:52 | to the phpMyAdmin configuration file.
phpMyAdmin will now pick this up and you
| | 06:57 | will know where these files are and it
will make it really easy to import them.
| | 07:00 | Let's show you how that's done.
| | 07:04 | Go back into phpMyAdmin.
| | 07:09 | And we'll select a database.
| | 07:11 | This is the album
database and click on Import.
| | 07:15 | This dropdown here, web server upload
directory, that's the directory that we
| | 07:19 | had edited in the configuration file.
See the album-mysql.sql file.
| | 07:25 | In this dropdown here, you want to select
the latin1 character set. That's really
| | 07:29 | important, otherwise you get funny
characters. And press Go and that goes ahead
| | 07:34 | and imports that file. We know have
the two tables in the album database and
| | 07:39 | there they are.
| | 07:40 | Go ahead and do the same thing to the
other two databases. I have the test database.
| | 07:46 | Import, test-mysql.sql, latin1 and Go.
| | 07:58 | And then finally, the world database.
| | 08:03 | I'm going to Import and world,
latin1 and this one takes a little bit
| | 08:12 | longer because it's a much
bigger database. There we go.
| | 08:15 | Now, these databases are installed,
they are populated and have all the data in them
| | 08:22 | and we are ready to
install the SID and CRUD applications.
| | Collapse this transcript |
| Setting up the example data on a PC| 00:00 | In this lesson, I'll show you how
to install the CRUD and SID example
| | 00:03 | applications in a Windows environment.
This movie presumes that you have
| | 00:08 | already installed your test web server
and MySQL and your test web server has
| | 00:13 | PHP running. There is another movie
that shows how to install the XAMPP test
| | 00:17 | environment on a Windows PC and if
you would like to do it that way, that's
| | 00:22 | what this environment is but of course,
this lesson applies to any web server
| | 00:27 | with PHP and MySQL running.
| | 00:29 | I have the htdocs folder for XAMPP and
that would correspond to wherever it is
| | 00:34 | that you put your PHP files to run them
on your server environment and up here
| | 00:38 | I have the Exercise Files folder with
the CRUD and SID folders showing there.
| | 00:43 | So we'll start with SID and we simply
drag a copy of it over and I'm holding
| | 00:47 | down the Ctrl key on the Windows keyboard
to copy the directory rather than moving it.
| | 00:53 | I will go ahead and do that. Now we
have a copy of that and I'm going to ahead
| | 00:58 | and open up the sid.php file with my
text editor. I'm using Notepad++ which is
| | 01:04 | a good quality free text editor from
Windows. You don't want to use a word
| | 01:08 | processor for this because that will
mess with the file. You want to use a
| | 01:11 | plain text editor and unfortunately it
probably will not work with the default
| | 01:17 | Notepad that comes with Windows so
that's why we are using Notepad++.
| | 01:20 | So it's a better editor and it works with line
endings other than the default Windows line endings.
| | 01:25 | Let's go ahead and open that up.
Here's the file and this is the only thing
| | 01:30 | you may need to change. In this case,
we can leave it alone because we have
| | 01:33 | installed a user called web in our
installation of MySQL. But if you have a
| | 01:38 | user in MySQL that's different, this is
the MYSQLUSER that will need sufficient
| | 01:43 | permissions to be able to run this application.
| | 01:45 | So we'll just leave that alone here,
and now we'll go ahead and test it in the
| | 01:51 | web browser, Firefox, and go ahead
and type in localhost/SID/sid.php.
| | 02:02 | And there it is. Just test it by
running a query in the world database.
| | 02:12 | Press Go and if you get this far, it's working
just fine. And there is 4079 rows in this city
| | 02:20 | table in the world database. Go ahead and
bookmark that because we'll want to use that again.
| | 02:26 | Go ahead ad close it. Now we'll
install the CRUD application that goes in
| | 02:33 | the same place. So here is the XAMPP
directory, and make that a view that works.
| | 02:41 | htdocs, there it is. And copy the
CRUD application over. We'll do the same thing.
| | 02:48 | We'll go in and edit the CRUD.php
file and right there is where you want
| | 02:54 | to put your MySQL user. In this
case we'll leave it as web and go ahead
| | 03:00 | and test it in the web browser.
| | 03:11 | There we have it. Get this far,
that's working because this is accessing
| | 03:15 | the database and bringing up the
information from the albums database.
| | 03:22 | So there we have it. Go ahead and bookmark
this one as well. We installed SID and CRUD on our
| | 03:30 | local environment for testing purposes.
| | Collapse this transcript |
| Setting up the test environment on a Mac| 00:00 | So in this lesson, we are going to show
you how to install the environment for
| | 00:04 | running the test examples on your
Macintosh. To do that we are going to install
| | 00:09 | a package called XAMPP and what XAMPP
does is it sets up an Apache web server
| | 00:18 | with PHP, with the MySQL on your
local Macintosh. And this will be the test
| | 00:24 | environment for running the CRUD and
SID applications that you've seen in the
| | 00:29 | previous movies.
| | 00:31 | We are also going to install a text
editor. This one is called TextWrangler.
| | 00:35 | It's a free text editor that's
available for the Macintosh and why do you need
| | 00:39 | a text editor, you might ask?
You will need a text editor to edit some
| | 00:42 | configuration files and these files
need to be edited with a proper text editor
| | 00:47 | and not with a word processor because
a word processor tends to add a lot of
| | 00:51 | invisible stuff that you like to have
in your word processing documents that
| | 00:55 | make them pretty and such, but get in
the way and make things not work so well
| | 00:59 | when you are editing text files for
configuration files or program source files
| | 01:03 | and things like that. You're going to need
to do a little bit of editing so we are
| | 01:06 | going to install TextWrangler for that purpose.
| | 01:09 | I have already downloaded these
packages. We have them right here, so
| | 01:13 | we'll start by installing TextWrangler
which is very quick and easy to do.
| | 01:17 | Just double-click on it and that opens
the package and you simply grab the
| | 01:22 | TextWrangler icon and drag it over
to the Applications icon and let go.
| | 01:28 | So before we install the XAMPP package,
I want to make sure that Apache is not
| | 01:33 | running. The reason for this of
course is that XAMPP has a web server;
| | 01:38 | it's actually also Apache and if Apache
were running it would get into the way of
| | 01:44 | installing the XAMPP package. So
what you want to do here is you click on
| | 01:49 | Sharing in the System Preferences and
you check the Web Sharing checkbox.
| | 01:54 | You just want to make sure that this is
not checked and here it's not, so this is
| | 01:58 | what it needs to look like. If it's
check you need to uncheck it and then
| | 02:01 | you want to wait for the little light to
go off when the web server stops and then
| | 02:05 | you can go ahead and
close your System Preferences.
| | 02:08 | Now we'll install the XAMPP or as I
call it XAMPP (ZAMP) because that's how I would
| | 02:13 | pronounce those letters. This one
takes a little bit more doing. We have
| | 02:18 | mounted the XAMPP DMG package and
now we'll run the package installer by
| | 02:24 | double-clicking on it. Hit the little
follow-with-the-bouncing-ball thing
| | 02:27 | going on here. Select the hard disk
to install it to and press Install.
| | 02:33 | Type in the password here.
| | 02:35 | What this does as it installs XAMPP
in the Applications folder on your
| | 02:40 | Macintosh hard disk. Again, it's a test
environment; it's not designed to be a
| | 02:44 | production server so it's not
installing it in the root file system,
| | 02:49 | it's not installing it as something running
in the background. It's for the purpose of
| | 02:53 | testing your own code locally which is
exactly what we want here. We don't want
| | 02:57 | to run this code on our production server.
| | 02:59 | So that install is done and now I'll
eject this package here. Now we are going
| | 03:03 | to need to do a little bit of
configuration in order to get this stuff all
| | 03:07 | working. Right now I'm just going to
close Firefox real quick here and now
| | 03:14 | we'll drag TextWrangler to
the dock. That would be under T.
| | 03:26 | Now we are going to edit a
configuration file here in XAMPP and I'll explain
| | 03:32 | why as we do this. phpMyAdmin is going
to import some SQL files as to populate
| | 03:39 | the databases and this stuff makes
that actually a lot easier.
| | 03:45 | So here's the file and we are going to open up
with TextWrangler. You want to make sure that
| | 03:49 | you don't open it with a word
processor or an HTML Editor or something like
| | 03:52 | that because that tends to mess
with these kinds of files. This is a
| | 03:56 | configuration file; it says .php but
really does need to be edited with a text
| | 04:02 | editor. So this is actually
why we installed TextWrangler.
| | 04:06 | So this goes where you see $cfg[
'UploadDir'] and that's what you put in.
| | 04:12 | If you do not see this line in this file
anywhere go ahead and add the line and just
| | 04:17 | put it down at the end there. $cfg[
'UploadDir']= and then this file path in
| | 04:30 | single quotes and a semicolon at the end.
| | 04:32 | If you don't see this line in the file,
just go ahead and add it,. It should be
| | 04:35 | there but it may not be. We'll go ahead
and we'll save and we'll go ahead and
| | 04:41 | quit TextWrangler. Now we are going
to copy some files over because we've
| | 04:46 | already edited that file, the config
file for phpMyAdmin, and told it this is
| | 04:51 | where the files are going to be. So go
ahead and create this directory if you
| | 04:53 | don't already have it and we are
going to copy some files in there from our
| | 04:57 | Exercise Files.
| | 04:58 | In your Exercise Files folder, you will
find a folder called SQL and just drag
| | 05:04 | these files into the file folder over here.
You can hold the Option key down to
| | 05:09 | copy them instead of moving them and
those are going to be used by phpMyAdmin
| | 05:14 | to populate the databases. We are
going to get to that in just a moment here.
| | 05:20 | Now we'll go ahead and find the XAMPP
Control Panel which is right there and
| | 05:27 | we are going to drag that to the dock
because you are going to use this to start up
| | 05:31 | the services on your Mac. So now I'm
going to go ahead and close this file browser
| | 05:36 | and now we'll want the XAMPP
Control Panel. Now I'll enter in my admin
| | 05:45 | password and now our XAMPP Control
Panel is open. We'll start MySQL first and
| | 05:52 | start Apache. It may or may not be necessary
to start MySQL first. I like to start things first
| | 05:56 | that are used by something else
and I know that the PHP module in
| | 06:00 | Apache uses MySQL so I tend to start
things in that kind of an ordering.
| | 06:04 | It just avoids problems. So start
MySQL first and then start Apache.
| | 06:09 | Now we can run the web browser and make
sure it's working. I'll just up here at
| | 06:13 | the title bar type localhost and there
it is. That means that the Apache web
| | 06:19 | server is running and the PHP is
running because it's a PHP file. Click on
| | 06:24 | English and then we get a little
control panel and click on these demos just to
| | 06:28 | make sure that everything is kind of
working. And we know that it is because
| | 06:32 | all these demos work. phpinfo actually
tells you a lot of useful information
| | 06:38 | about your PHP installation and
it's there in case if you need it.
| | 06:43 | Now we are going to go ahead and run
phpMyAdmin, which will start in a new tab.
| | 06:48 | So first thing we are going to do here
in phpMyAdmin is we are going to delete
| | 06:52 | this test database because we want to
create it again in slightly different way.
| | 06:57 | phpMyAdmin for some reason defaults
to a Swedish character set in all the
| | 07:04 | databases that it creates. I'm not sure
why they do that. Probably because they
| | 07:08 | are Swedish. I'm not really sure, but
we need to delete these databases first.
| | 07:13 | Delete just the one that we are going
to use, which is test, and then create it again.
| | 07:20 | So what we need to do now is we need
to setup MySQL for use with CRUD and SID
| | 07:25 | and there is two things that we need.
One is we need to create the user that is
| | 07:29 | used by these applications for
accessing the database with its proper
| | 07:32 | privileges and two, we need to create
the databases and the tables and
| | 07:38 | fill them with the data that we are
going to use for the examples.
| | 07:40 | So first let's go and create the user.
We are going to add in our user and
| | 07:45 | this user is called web. You can call it
something different if you want to but
| | 07:49 | if you do, you will have to edit to
the CRUD and SID applications to change
| | 07:53 | the user that's in there, so it's
best to just go ahead and use web.
| | 07:58 | And it's going to be on the localhost
and it's going to be no password.
| | 08:01 | Again this is a test environment.
In a real production environment on a real
| | 08:06 | publicly accessible web server you would
never want to use an account without a password.
| | 08:11 | But you are not going to put
this on the web, you are not going to
| | 08:13 | put this on a publicly accessible
computer and so this is the way that you can
| | 08:17 | do it for your own private use. I'll
go ahead and grant all privileges on a
| | 08:22 | wildcard name and what that does is it
allows this user use any database in the system.
| | 08:30 | We are going to go ahead and check all
of the privileges. So what you are doing
| | 08:34 | is you are creating a very, very
powerful user. Again, you don't want to do this
| | 08:37 | on a publicly accessible server.
Press Go and we have now created this user.
| | 08:42 | Now let's go ahead and create the
databases. First database that we create is
| | 08:47 | the test database. We'll go ahead and set
that up with the latin1_bin character set,
| | 08:54 | latin1_bin and press Create. Now we'll
go ahead and do that for the other two databases.
| | 09:03 | We want the album database.
Collation, latin1_bin and Create and
| | 09:15 | finally the world database and latin1_
bin and Create. We'll go ahead while
| | 09:27 | we are here and we'll import the data
for the world database. This is the data
| | 09:31 | we use a lot in the examples.
| | 09:32 | Now if you remember we edited the
configuration file for phpMyAdmin and we gave it
| | 09:39 | a location where we can drop those
files. We did it, we dropped those files
| | 09:42 | and this is what that does. They show
up in this little menu, which makes it
| | 09:45 | very easy for us to import those files
into the database. And what those files
| | 09:50 | do is they create the tables and they
load the tables with the data that's
| | 09:54 | going to be used in the examples.
| | 09:56 | The character set for these files is
latin1. It's very important to switch this,
| | 10:01 | otherwise the characters will
look all funny on the screen and go ahead
| | 10:04 | and press Go. This one takes a minute
because it's a pretty big file, there's a
| | 10:08 | lot of data in that world database.
It's very useful for experimenting with and
| | 10:12 | that's the world database.
| | 10:13 | I will go ahead and select the album
database, there we go and press Import and
| | 10:20 | album database here, latin1 here and
press Go. That went quickly. Not much to
| | 10:28 | that one. And finally the test database,
which is the smallest of them all.
| | 10:34 | So the test database is there and latin1
and Go. And so the next step is to set up
| | 10:43 | the SID and CRUD Applications.
| | Collapse this transcript |
| Setting up the example data on a Mac| 00:00 | So now I'm going to show you how to
install the SID and CRUD applications for
| | 00:05 | your test environment, so that you can
follow along with the examples in this
| | 00:08 | course. This lesson assumes that
you have already installed your test
| | 00:12 | environment whether it's the XAMPP
environment that I demonstrated in another
| | 00:16 | movie or whatever environment that you
have chosen to install. There is a lot
| | 00:20 | of different ways to do it, XAMPP is
very easy to install but it's not the only way,
| | 00:25 | so this just assumes that you have
installed some web server with PHP and MySQL
| | 00:30 | and the minimum requirements
for running these exercise files.
| | 00:33 | On the desktop, I have got the Exercise
Files folder and I have opened that in
| | 00:36 | this top window and at the bottom
window I have the htdocs folder for our test
| | 00:41 | environment. This is the location where
you would find it if you have installed
| | 00:44 | the XAMPP environment as demonstrated
in this course. If you have installed a
| | 00:49 | different environment or done it a
different way, it will still work.
| | 00:52 | You just need the folder where you install
your web files for the web server.
| | 00:57 | So now I'm going to take the SID folder,
we are going to install SID first and
| | 01:02 | simply drag that over and I'm pressing
the Option key on my Macintosh keyboard
| | 01:06 | and that copies it instead of moving it
and let go the mouse button and that's copied over.
| | 01:12 | Now I'm going to open the SID folder
and edit the sid.php file. The reason
| | 01:19 | I'm going to edit this file is to change
that MySQL user that the SID program uses
| | 01:24 | to connect to the database. If your
MySQL environment is installed with a user
| | 01:30 | named web that has sufficient
permissions to run the queries then
| | 01:36 | you don't need to edit this file at all
because that's the way it is as the default.
| | 01:39 | But I'm going to show you how to do it
in case if you have a different user.
| | 01:42 | So I'm going to open up with
TextWrangler and here it is and right here where
| | 01:47 | it says MYSQLUSER, that's all you
need to change. That already says web and
| | 01:52 | our example is setup with web already,
so I don't need to change it, but if you do,
| | 01:57 | this is how. And you want to use
a text editor. In this case I'm using
| | 02:01 | TextWrangler which is a wonderful
little text editor for the Macintosh.
| | 02:05 | If you have some other text editor that's
fine but it cannot be a word processor.
| | 02:09 | It must be a plain text editor
that you edit these files with.
| | 02:14 | That's all there is to it. Now we'll go
ahead and open our web browser and type
| | 02:19 | in to the location bar localhost/SID/sid.php
and there is the SID application.
| | 02:33 | If this installed correctly, we'll see
a list of databases over here: album,
| | 02:36 | test, and world. We can go ahead and
run a query and make sure that that's all working.
| | 02:42 | Select * From Country and press
the Go button and there we go. We have
| | 02:52 | the country database listed here on the screen.
| | 02:55 | So this is all working well. I can
take this and bookmark it. There it is and
| | 03:00 | SID is now installed on this computer.
Go ahead and do the same thing for CRUD.
| | 03:08 | Copy it over to the htdocs folder,
holding down the Option key. Go in and edit
| | 03:15 | the CRUD.php file, change the MySQL
user variable, quit TextWrangler and
| | 03:28 | bring up the web browser and
type in localhost/CRUD/CRUD.php
| | 03:40 | and there is the CRUD application.
| | 03:42 | We can see already that it's working
because it's read the database and has
| | 03:46 | pulled up these records. So that's it,
simple to do. That's how you can install
| | 03:51 | the SID and CRUD applications for the Macintosh.
| | Collapse this transcript |
|
|
2. SQL Quick StartQuick-start introduction| 00:00 | In this chapter, you will learn a
few basic SQL statements. These few
| | 00:03 | statements actually cover a large
amount of the SQL that you will encounter.
| | 00:07 | Many useful applications are written
with no more sophisticated SQL than what
| | 00:10 | you will see in this brief
introduction. Here you will learn how to use the
| | 00:14 | Select statement, the basis for most
SQL that you will run into. Most of the
| | 00:18 | SQL that you will write. It's the
basic statement of the SQL query.
| | 00:23 | You'll learn how to select specific columns
and specific rows from a table and how to
| | 00:27 | use the Count function.
| | 00:29 | There is a lot more detail on all of
these statements in the rest of the course.
| | 00:32 | So this section is not meant
to be a detailed tutorial. Rather it is
| | 00:36 | here like the title says
just to get you started quickly.
| | Collapse this transcript |
| Using the basic SELECT statement| 00:00 | The most common SQL statement is the
Select statement. This is often referred
| | 00:04 | to as a query, you will use this one a
lot. This simple select statement will
| | 00:09 | return all the data in the country table.
| | 00:17 | The statement is Select. Select is
| | 00:20 | used to ask the database center to return
a set of values or sometimes just one value.
| | 00:32 | This is the simplest form of Select,
it selects one value, a string with the
| | 00:36 | words 'Hello, World'. This query
doesn't actually query a database. It's simply
| | 00:41 | displaying a literal string Hello, World.
The more common form of Select is to
| | 00:46 | select data from the database. This
statement selects everything from the
| | 00:54 | country table. The FROM clause
specifies a table to get the data from.
| | 00:59 | The asterisk is a special
character that means all the columns.
| | 01:03 | So, this will get all the data from
the country table. Select can also use
| | 01:08 | functions. This uses the Count function
to find out how many rows there are in
| | 01:16 | the country table. This lesson was
about the Select statement, what it is? How
| | 01:21 | it's used to read data from a table?
And how to use a function with Select?
| | 01:25 | These basic concepts will be used
in almost everything you do with SQL.
| | Collapse this transcript |
| Selecting columns| 00:00 |
In this lesson, you will learn how to
select specific columns from a table and
| | 00:04 |
also how to manage the names of the
columns as they are returned. This query
| | 00:08 |
should be familiar by now. It means to
select all of the data from the country table.
| | 00:13 |
So when we press Go, you will
see all the rows and all of the columns
| | 00:17 |
from the country table in the world database.
| | 00:20 |
Here we have selected the world
database up here in our database selector.
| | 00:24 |
But what if we just want to see a list
of countries under LifeExpectancy?
| | 00:29 |
All this data is confusing. Well it's
not exactly like a needle in the haystack,
| | 00:33 |
but you get the idea.
| | 00:34 |
There is the Name column and then
LifeExpectancy is over here someplace.
| | 00:38 |
There we go. And there is a lot of data
that we are not using and if we are using
| | 00:42 |
this query in a programmatic context
as opposed to just displaying it on the
| | 00:46 |
screen, if it we are in a program
that we are writing, we would be carrying
| | 00:49 |
around all of this data and using
resources for no purpose wasting those
| | 00:54 |
resources in effect.
| | 00:56 |
So it would be useful to be able to
select which columns to return in the
| | 01:00 |
query. So instead of the asterisk,
which you remember means everything, all of
| | 01:04 |
the rows and all of the columns, we can
just select the particular columns that
| | 01:09 |
we are looking for by putting their names
in the Select clause instead of the asterisk.
| | 01:14 |
So I'm going to type here Name and
LifeExpectancy and then when I run the query
| | 01:24 |
we'll get just those two columns. There
you go. We now have the Name column and
| | 01:29 |
the LifeExpectancy column and nothing
else. So we are not wasting any space in
| | 01:34 |
memory or whatever resources we are
using in a programmatic context. And we
| | 01:40 |
don't have to hunt for it with our eyes
on the screen. We have just the columns
| | 01:44 |
that it is that we are looking for.
| | 01:45 |
The column headings are taken from the
names of the columns as they are in the
| | 01:50 |
database and as we specify them in the
Select statement. So we have Name and we
| | 01:55 |
have LifeExpectancy up here in the
Select statement and down here in the
| | 02:01 |
results there is the name of
the columns and they are the same.
| | 02:05 |
What if we wanted to name them
something else? Instead of name, country might
| | 02:10 |
be a useful heading for this one. So we
use the AS clause, AS Country and over
| | 02:16 |
here instead of LifeExpectancy, we
might want to have it in a readable form
| | 02:20 |
like with a space. So we
say, AS 'Life Expectancy'.
| | 02:25 |
You will notice that I put quotes
around the word Life Expectancy, we used
| | 02:33 |
these before for a literal string
and in this context they are necessary
| | 02:36 |
because otherwise that space would
tell the SQL system that the AS clause is over.
| | 02:44 |
So what this does is it takes these two
words and it makes them into one token.
| | 02:49 |
In computer languages a token is a
part of speech when you are speaking in a
| | 02:53 |
human language. That would be a token
in a computer language and so tokens
| | 02:58 |
typically are terminated by what they
call whitespace, which would be the Space
| | 03:02 |
character here or a Carriage Return
or a Tab or something like that. And so
| | 03:06 |
when the parser sees the space, it thinks
the token is over and it would get confused.
| | 03:12 |
Let's go ahead and run it with the
quotes and without the quotes, so that you
| | 03:15 |
can see what happens. When I run it
with the quotes like this, we get Life
| | 03:20 |
Expectancy here and now the heading of
this column is Country because we named
| | 03:23 |
it Country over here. So we have
Name as Country, LifeExpectancy as
| | 03:26 |
Life Expectancy with a space.
| | 03:29 |
Now, if we take out the quote marks,
we can create an error. Sometimes it is
| | 03:35 |
very useful to create errors on purpose.
You can learn from this what the error
| | 03:39 |
messages look like, so that later when
you see those error messages and you are
| | 03:42 |
not expecting them you get an idea of
what to look for in your code, so that
| | 03:47 |
you know how to fix it easier without
having to spend a lot of time trying to
| | 03:51 |
figure it out.
| | 03:52 |
So, let's go ahead and create this
error on purpose and now instead of the
| | 03:57 |
happy green message that said how many
rows and columns we got back, we have a
| | 04:02 |
red error message and it says, You have
an error in your SQL syntax; check the
| | 04:07 |
manual that corresponds to your MySQL server.
| | 04:10 |
So we are using the MySQL server here.
This is the message from the MySQL
| | 04:13 |
server that says it did have a problem
with the way that we put this query to it.
| | 04:17 |
And it says near 'Expectancy FROM
Country' at line 1. Well, we only have
| | 04:22 |
the one line so that's easy to find.
And Expectancy is right here. So that is
| | 04:27 |
where it got confused.
| | 04:28 |
Everything up to that point made
perfect sense. SELECT Name AS Country,
| | 04:32 |
LifeExpectancy AS Life and then the
word Expectancy confused it because it
| | 04:37 |
doesn't speak English. It speaks SQL.
And at that point the token for the alias
| | 04:42 |
for Life Expectancy is Life and then
it is expecting something else here
| | 04:46 |
instead of this word Expectancy. If we
had put just the from there, it would
| | 04:50 |
have been fine.
| | 04:50 |
But if we wanted to take all of this
with the space as one token, we put in
| | 04:56 |
these quote marks, single quotes, SQL
likes single quotes just as a note here,
| | 05:02 |
a lot of SQL servers including MySQL
are perfectly fine with double quotes, but
| | 05:06 |
some are not. Single quotes are correct
for SQL and you will find that some SQL
| | 05:11 |
servers, in particular Microsoft SQL
Server don't like the double quotes. So
| | 05:16 |
just always use a single quotes and you
will be happy when you are writing SQL.
| | 05:19 |
Now when we press Go, we'll get what we
expect the two columns Country because
| | 05:25 |
it is named Country here which is the
Name column of the database named Country
| | 05:28 |
and Life Expectancy which we have
over here, just like we expected.
| | 05:34 |
The ability to select which columns to
read with a query is key. A simple query
| | 05:38 |
that uses column names in the Select
clause allows you to select which columns
| | 05:43 |
to read with your query and use of the
AS clause allows you to display those
| | 05:47 |
columns with a more readable heading.
| | 05:54 |
| | Collapse this transcript |
| Selecting rows| 00:00 | In this lesson, you will learn how to
specify which rows the SELECT statement
| | 00:03 | will return. SELECT always returns rows
and columns. You can specify which rows
| | 00:08 | you get by using a WHERE filter.
| | 00:10 | For example, consider this query. This
query will select three columns, Name,
| | 00:16 | Continent and Region from the Country
table. Let's go ahead and execute it and
| | 00:21 | see what happens. Here we have the entire
table, all 239 rows and we have three columns:
| | 00:28 | Name, Continent and Region.
Those are the same three columns that
| | 00:32 | we specified in our SELECT statement as
we expected, Name, Continent, and Region
| | 00:37 | from the Country Table in the world database.
| | 00:40 | Now suppose we don't want all of
those rows. Suppose we want only the rows
| | 00:44 | where the country is in Europe. So we
can add a WHERE clause to our query to
| | 00:49 | specify which rows we want.
WHERE Continent = 'Europe'.
| | 01:00 | Now when we run this query, we should get only those
rows where the Continent says Europe and there we are.
| | 01:07 | We now have just 46 rows and all of
them have Europe in the Continent column.
| | 01:14 | So let's take a look at how this works.
The WHERE clause is specified with the
| | 01:18 | keyword where. The WHERE clause takes
a condition as its argument. In this
| | 01:23 | case, the condition is, Continent equal
to Europe. So Continent is the name of
| | 01:28 | the column, Europe is the literal
string here, you notice it has single quotes
| | 01:34 | and that makes it a literal string
and the comparison operator is the equal sign.
| | 01:39 | So this condition means where
Continent equals to Europe. This literal string
| | 01:45 | you will notice has single quotes and
that is what makes it a literal string
| | 01:50 | and it has the letters in it that
make up the word, Europe. The equals sign
| | 01:53 | means that this condition will only
evaluate to true and the SELECT statement
| | 01:58 | will only return those rows where the
Continent column has these exact letters
| | 02:03 | in it, no more no less. So if the
Continent column has Europe with a space at
| | 02:07 | the end, for example, it's not going to
satisfy this condition and it will not
| | 02:12 | be listed here. Only those rows that
exactly satisfy the condition will be
| | 02:17 | returned and in this case, that was 46 of them.
| | 02:20 | In this lesson you learnt how to
select specific rows from a table using the
| | 02:24 | WHERE clause. This is useful for
getting a part of the table when there may be
| | 02:27 | a lot more rows that you are
interested in working with.
| | Collapse this transcript |
| Counting things| 00:00 | The COUNT function is probably the
most common function in SQL. Let's take a
| | 00:04 | look at how it works. This form of
COUNT will count all the rows in the Country
| | 00:09 | table in the world database. So let's
go ahead and run that and we should get a
| | 00:13 | number 239 and we know that that's the
number of rows in the Country database.
| | 00:18 | This is a special form of COUNT because
it uses the asterisk. The asterisk is
| | 00:23 | a special character and in this context,
it means count everything, count all
| | 00:28 | the rows no matter what's in them.
| | 00:29 | So here we have a count, all the rows
in the Country database. If we put the
| | 00:35 | name of a column in here, now COUNT
will just count those rows where there is a
| | 00:43 | value in that column. So we are going
to get a different number. We are going
| | 00:46 | to get a smaller number. Go ahead and
run this. Now the number is 192.
| | 00:52 | That's just the rows where there is a value
in the IndepYear column. Let's go ahead
| | 00:57 | and look at just that column without account.
| | 01:00 | If we put this in the query, now
we'll see all of the rows with just the
| | 01:06 | IndepYear column. Now, we'll notice
something about this. See this row here and
| | 01:11 | this row here, they say NULL. What that
means is it there is no value in that cell.
| | 01:18 | NULL is a special value in SQL
and it means there is nothing there.
| | 01:23 | It's different than zero; it's different
than a zero like string. It's a special
| | 01:28 | value that means it's empty.
| | 01:30 | So when you use an aggregate function
like COUNT, it will ignore rows that have
| | 01:39 | no value, that are NULL and so in
this case, it's counting this column
| | 01:44 | IndepYear and it gives us a number that
is just the number of rows that have a
| | 01:49 | value that's not NULL in that column.
| | 01:53 | Because COUNT is an aggregator, it
works nicely with the GROUP BY clause.
| | 01:57 | Thus largely answer questions like, how
many countries are there in each continent
| | 02:02 | in the database? So here's how that
works. Let me go ahead and run this,
| | 02:06 | you can see the result,
while we talk about it.
| | 02:08 | You'll notice that it displays the
name of the continent and the number of
| | 02:12 | countries in that continent. So we'll
look at our SELECT statement, Continent,
| | 02:17 | which is the continent column, COUNT
(Name) AS Countries. So that's this column
| | 02:23 | here, which we see that As Countries
puts the heading Countries at the top of it.
| | 02:27 | From the Country table, GROUP BY
Continent. So GROUP BY works with the
| | 02:33 | aggregator functions. COUNT is an
aggregator function which means that it works
| | 02:37 | on a group of rows at a time and
what GROUP BY does is it takes that
| | 02:43 | expression, which in this case is the
column named Continent, and it groups
| | 02:47 | together all of the rows that have the
same value in that column and then it
| | 02:52 | applies the aggregator function which
in this case is COUNT. So it counts the
| | 02:57 | main column in those groups, which is
grouped by places where the continent is the same.
| | 03:02 | So that makes sound very, very
complicated, but it's really very, very simple.
| | 03:06 | All this does is it takes all of the
rows that have Asian continent and it
| | 03:10 | counts them, all the rows that have
European continent and it counts them and
| | 03:15 | it gives us this very, very useful
result very quickly and very efficiently.
| | 03:20 | So GROUP BY works with aggregators,
COUNT is the most common of the aggregator
| | 03:25 | functions that allows you to do useful
stuff like answer a question, how many
| | 03:29 | of these are there and rows that are
like that. The COUNT function is one of
| | 03:34 | the more common functions used in SQL queries.
| | 03:38 | It's a simple, fast and effective way of
counting the number of rows returned by a query.
| | Collapse this transcript |
|
|
3. SQL EssentialsOrganization: Databases and tables| 00:00 | A database is a collection of tables.
A table is a set of data organized in
| | 00:05 | rows and columns.
| | 00:06 | Because data in a table is defined in
rows and columns, a table is usually a
| | 00:11 | list of related things, for example,
countries in a Country table. So, we have
| | 00:16 | an example of a Country table with
information about countries in the table.
| | 00:20 | Or cities in a City table.
We have a City table where it just took
| | 00:24 | the cities in and the population of
the city. Or customers in a Customer table
| | 00:29 | like this one with the name and
address of the customers. Or record albums
| | 00:33 | in an Album table. Here's a table with
record albums in it and artist and label
| | 00:39 | and released dates.
| | 00:40 | Likewise, the tables in the database
are generally related to each other.
| | 00:44 | For example, here we have the Country
database and the cities in the country of
| | 00:48 | Canada. This information is in two
different tables, the Country table and
| | 00:53 | the City table, or this one
with the tracks in an Album.
| | 00:57 | The tracks are in the Track table
and the album is in the Album table and
| | 01:00 | here's how the data is related. These
tracks belong to that album. The database
| | 01:06 | is organized as a set of tables,
where each table contains a common set of data.
| | 01:11 | Tables in a database tend to be related
to each other like countries and cities
| | 01:14 | or customers and addresses. Understanding
how a database is organized can help you
| | 01:20 | to understand your application
or the application you need to work with.
| | Collapse this transcript |
| SQL syntax overview| 00:00 | Before I start talking about SQL syntax,
there is something you need to know,
| | 00:03 | standard SQL isn't. Well, there is
such a thing as standard SQL, there is
| | 00:08 | significant differences between
Database Management System implementations.
| | 00:12 | Significant to the degree that the SQL
you write for one DBMS will often not
| | 00:17 | work on another DBMS. The reason for
this is not as fair as it may seem. In the
| | 00:23 | nutshell, most of the modern DBMS
that has existed long before SQL was
| | 00:27 | standardized and there is a real need
to continue to support the legacy SQL
| | 00:31 | that was written for each of these
distinct environments before the standards
| | 00:35 | were enacted. So, you just need to
accept it, whatever SQL you write or read
| | 00:39 | will almost certainly have some vendor
specific aspects to it and will almost
| | 00:43 | certainly need to be changed if
it's going to work on a different DBMS.
| | 00:47 | SQL statements consist of clauses and
expressions. For example, consider this
| | 00:52 | simple SQL statement. In this SQL
statement, 'Hello, World' is an expression.
| | 00:59 | Now, we learned before that this is a
literal string and that is also true.
| | 01:03 | This literal string is a simple
expression and that satisfies the requirement
| | 01:08 | that the Select clause has an
expression. The expression in this case is the
| | 01:13 | literal string, 'Hello, World'. This
is a somewhat more complex statement.
| | 01:18 | It is a Select statement and it has
a From clause and a Where clause.
| | 01:24 | The From clause specifies what table
to read from for the query and the Where
| | 01:31 | clause specifies the condition that
must be met in order for a row to be
| | 01:37 | retrieved from the database and in this
case, displayed on the screen. In this
| | 01:43 | SQL statement, the expression is a
Function call. The function here is the
| | 01:47 | Count function that we have seen
before and this also qualifies as an
| | 01:51 | expression in SQL syntax. An SQL
statement consists of clauses, expressions and
| | 01:57 | functions. You will apply this
knowledge throughout the course as you learn to
| | 02:01 | use more in the SQL language.
| | Collapse this transcript |
| Formatting SQL| 00:00 | It is good practice to format your SQL
for readability. So, when you or someone
| | 00:05 | else has to read it later, it is
easier to understand. White space is ignored
| | 00:10 | in SQL so it is convenient to format.
For example, in this SQL statement,
| | 00:15 | we have three clauses, a Select clause,
a From clause and a Where clause.
| | 00:20 | I tend to put each of these on a
separate line. So, I'm just going to move
| | 00:25 | along there and I indent them under
the parts that they relate to. In this
| | 00:32 | case, both the From clause and the
Where clause are operating on the Select
| | 00:37 | clause and so I indent them under the
Select. Now, we can just run this so that
| | 00:44 | you can see that it actually does work.
SQL ignores the white space and space
| | 00:49 | characters as well as newline
characters are all considered white space.
| | 00:55 | So this statement operates exactly
the same as if it were on one line;
| | 00:59 | functionally it is no different. The
formatting just makes it easier to read.
| | 01:04 | Go ahead and type out this other SQL
statement. Here is a more complex example.
| | 01:41 | Now, here is a more complex example
and I understand that this course has not
| | 01:45 | yet covered all of these different
parts. We'll soon but for the purpose of
| | 01:49 | understanding the importance of
formatting, it is good to take a look at this.
| | 01:54 | We can see from the indentation that
this From clause relates to the Select
| | 01:59 | clause. This Join clause also relates
to the Select clause and the On clause
| | 02:05 | relates to Join, Order relates to
Select. By virtue o the indentation and the
| | 02:12 | formatting, it is much easier to read
and much easier to understand this more
| | 02:18 | complex Select statement.
| | 02:21 | One additional important note,
technically according to the SQL standard and we
| | 02:26 | know that not all the vendors support
the standard, every SQL statement must be
| | 02:31 | terminated by a semicolon, and that
is just according to the standard.
| | 02:35 | In practice, you will rarely see the semicolon
when there is only one SQL statement involved.
| | 02:42 | If there is more than one SQL
statement in the same context, you will usually
| | 02:46 | see a semicolon separating them or
sometimes terminating them and that is a
| | 02:50 | very picky distinction. But in practice
when there is only one statement, which
| | 02:54 | is most of the time, you
will not see the semicolon.
| | 02:58 | So, you just need to know that
sometimes you will see a semicolon and there is a --
| | 03:02 | very rarely, you will find an
environment where it is actually required
| | 03:06 | and you will know when you run across
in one of those, but most of the time you
| | 03:09 | won't see it. You just need to know
that the standard is as it's supposed to be there.
| | Collapse this transcript |
| Creating a database and tables| 00:00 | So we are going to talk about how to
create the database and how to create a
| | 00:03 | table using SQL. It's important to
note that you will probably not do it this
| | 00:09 | way in practice. So don't follow along.
| | 00:12 | How this is done differs dramatically
from one database management system to
| | 00:17 | another and most of the time when you
need to create a database or create a
| | 00:21 | table, you won't be using SQL, you
will either use the interface that comes
| | 00:26 | with your Database Management System,
if you are creating the database and the
| | 00:30 | tables for your own use or if you
are creating them for the purpose of an
| | 00:34 | application that you require, you have
downloaded it from the internet or you
| | 00:37 | have purchased it, there is usually
scripts that will create the databases and
| | 00:42 | the tables for you in the specific
ways that they need to be create in that
| | 00:46 | specific environment.
| | 00:47 | So this is for informational purposes
only. You don't need to follow along but
| | 00:52 | here is generally speaking what the SQL
looks like for creating a database and
| | 00:56 | for creating a table. A database
is created using the Create Database
| | 01:01 | statement. Create Database and the name
of the database. We'll go ahead and run it.
| | 01:08 | So, database has been created.
The way SID works because the web is a
| | 01:14 | context free environment, we need to
load it again, so that you can see the
| | 01:18 | database show up here in the list and
there it is and we can create a table.
| | 01:23 | Create table is a bit more complex.
| | 02:13 | Now this Create Table statement is a bit
| | 02:15 | larger than our textbox and that's okay.
You can see that it's indented so we
| | 02:19 | know what all relates to what. Let's
take a look at the different parts of it.
| | 02:23 | Create Table and the name of the table
and then within these parenthesis is the
| | 02:29 | definition of the table.
| | 02:31 | It's called the Table Definition. Each
line here is one column so this is the
| | 02:35 | ID column, the Name column, the
Address column etcetera. The ID column is
| | 02:42 | what's called the Primary Key and so it
says PRIMARY KEY here. The type of the
| | 02:46 | column is Integer. NOT NULL means that
null values are allowed in this column.
| | 02:53 | So if somebody inserts a null value
an error will occur. AUTO_INCREMENT is
| | 02:59 | MySQL's specific feature. This
particular syntax will not work in other
| | 03:04 | databases but other database systems
will have an equivalent feature. What this
| | 03:09 | does is automatically inserts a value
that's the next available integer for
| | 03:15 | this column.
| | 03:16 | So when you insert rows into this
table, you don't insert an ID column, the
| | 03:22 | database takes care of that for you
and it automatically gives you the next
| | 03:26 | incremental value. So the first row
will have a one in this column, the second
| | 03:30 | row will have a two and on and on and
on and that can be used as an ID for
| | 03:37 | linking the tables together with Joins
and we'll learn about that in a later lesson.
| | 03:41 | The NAME column is of type Varchar, is
how I pronounce it and again, this is
| | 03:47 | another place where this is vendor
specific. In different databases this will
| | 03:51 | be called something else.
Unfortunately, this is one of the areas where
| | 03:56 | databases are the most different and
again this is one of the reasons you won't
| | 04:00 | be writing this SQL yourself very often.
| | 04:03 | This is an area where databases are
very, very different, the names of the
| | 04:08 | column types, how they are spelled,
what their limitations are, what their
| | 04:12 | options are, how this Create Table
statement is constructed tends to be very
| | 04:18 | different between databases. So these
three columns are the Variable Character
| | 04:23 | type and these last two
are Fixed Character types.
| | 04:27 | You will notice that there are commas
after all of these columns except the
| | 04:34 | last one. That's because the comma in
this context is not a terminator, it's a
| | 04:40 | separator. So you have commas in
between the columns, but not between the last
| | 04:46 | column and the closing parenthesis.
I included a semicolon here because the
| | 04:52 | Create Table statement tends to usually
be in a context where there is multiple
| | 04:56 | statements. It's usually followed by
another Create Table or a set of inserts.
| | 05:01 | So they tend to have semicolons but
again it's just going to depend a whole lot
| | 05:05 | on the context. So that's how you
use Create Database and Create Table.
| | 05:10 | It's good to know generally how the
statements work even though you will probably
| | 05:13 | create your databases and your tables
in other ways. This is generally what
| | 05:17 | the SQL looks like.
| | Collapse this transcript |
| Inserting data| 00:00 | The Insert statement is used to
create a row in a table. That is, to insert
| | 00:05 | data into a table. So let's
take a look at how this is done.
| | 00:43 | So this is an Insert statement, INSERT INTO customer.
Customer is the name of the table and
| | 00:50 | then a list of columns inside a
parenthesis followed by the word Values.
| | 00:56 | This is the Values clause and this is the
actual data that will be inserted into the
| | 01:00 | columns. So I can scroll just enough there.
| | 01:03 | So for each of these columns, there
is a value and in this case, they all
| | 01:10 | happened to be Literal Strings. As you
remember, they were defined either as
| | 01:15 | Variable Characters, Variable Length
Strings or Fixed Length in the case of
| | 01:19 | state and zip. So each of these String
Literals is enclosed in single quotes
| | 01:24 | just like we have seen before and they
are in the same order as they appear in
| | 01:30 | the list above.
| | 01:32 | So when we go ahead and execute this
statement, Statement affected 1 row(s).
| | 01:38 | Now that data has been inserted into
the table and we can go ahead and select it.
| | 01:46 | We'll see the data that we just
added. That would be this fourth one,
| | 01:52 | John Smith, Liberty, California. You'll notice
that the ID field, which we did not insert,
| | 01:58 | got incremented to the next value.
| | 02:00 | This is the most common form of an
Insert statement. There are other ways to do
| | 02:05 | it and you will see other ways like so
many things in SQL. But this one tends
| | 02:10 | to work in most platforms. So when
you have the opportunity to construct it
| | 02:14 | yourself, you want to use this form.
| | Collapse this transcript |
| Reading tables with JOIN| 00:00 | So we have seen how Select can be used
to get the data from a table. Sometimes
| | 00:04 | your data maybe spread out amongst more
than one table. You will need a way to
| | 00:10 | get that data in one query. This is
called a Join query and it uses Select
| | 00:15 | with a Join clause to get data from more than
one table. Let's take a look at an example.
| | 00:56 | Now this is a Join query. Let's go
ahead and run it. Now, we'll see that this
| | 01:01 | is a list of countries with their
continent and their capital city. Now the
| | 01:07 | city is actually in a different table.
It's not in the Country table; it's in
| | 01:12 | the City table. So, let's take a look
at the query. I have the Select statement
| | 01:16 | and the list of fields.
| | 01:18 | Now you will notice that each one of
these columns has something before it.
| | 01:22 | Here it says c.Name AS Country, so that
would be this column here. c.Continent,
| | 01:30 | that would be this column and ct.Name
AS Capital, so that would be this column
| | 01:35 | with the capital.
| | 01:36 | From Country AS c, so every place you
see c. that means that's the column from
| | 01:43 | the Country table because country is
being used and it's being called c.
| | 01:47 | remember our AS clause. It's just
like AS here, only it's being used for a
| | 01:52 | table name. Ct as in ct.Name is the
City table and that's being joined as ct.
| | 02:02 | So whenever you see ct. something, it's
that column name out of the city table.
| | 02:09 | The ON clause is the condition whereby
the two tables are joined. So this ON
| | 02:15 | clause says ct.ID = c.Capital. So
there is a column in the City table called
| | 02:22 | ID, which has a number in it. We have
seen that already. It's auto incremented
| | 02:28 | and it is an ID number that
identifies a particular row in that table.
| | 02:34 | In the Country table, there is a
field called Capital and that column has a
| | 02:40 | number in it which corresponds to a
particular row in the City table that
| | 02:45 | contains the data for that city. What
this ON clause does is it joins the two
| | 02:51 | tables at the point where this
condition is satisfied. So when you perform this
| | 02:57 | Select, for each row in the Country
table, you will find the corresponding row
| | 03:04 | in the City table where this condition
is met and use that data in this query.
| | 03:09 | ORDER BY Country simply means that
this query will be returned in the
| | 03:14 | alphabetical order of the column named
Country, which is the Name column out of
| | 03:20 | the Country table. So that is the Join
query and that is generally how a Join works.
| | 03:27 | Now sometimes you will see this query
written a different way. I'm going to
| | 03:32 | just show you what that looks like.
This is the correct way. This is the way
| | 03:37 | according to the standard and all the
modern Database Management Systems will
| | 03:42 | accept this sort of syntax. Some SQL
was written before the standard was
| | 03:49 | instituted which does joins in I think
more confusing way. But you will see it
| | 03:53 | sometime, I'm going to
show you what it looks like.
| | 03:56 | Instead of using the Join clause at all,
it will put both of the tables in the
| | 04:00 | From clause. So I'll just delete this
Join here and put in a comma. So it will
| | 04:05 | look like that. Where this says ON,
we'll put Where. This is what a query would
| | 04:12 | look like in the old way. We'll go
ahead and run it and make sure that it works.
| | 04:16 | It gives us exactly the same result.
It doesn't even look like anything
| | 04:20 | happened. But believe me it did.
| | 04:22 | So you will sometimes see Joins written
this way. Either the code is old or the
| | 04:26 | programmer learned it this way and
never learned the new way. This is the same
| | 04:30 | thing, it's the same join query. It's
just more confusing because it doesn't
| | 04:34 | have the Join keyword. So it's not
obvious that that's what's going on.
| | 04:38 | The clause that would be in the ON is in
the WHERE and the two tables are in the
| | 04:43 | FROM clause. So you will see it done that way.
| | 04:46 | This is what a Join is and what a Join
does. It's very useful. You will see it
| | 04:50 | a lot and hopefully you will use it a
lot. When you organize your tables in a
| | 04:54 | way that each piece of related
information is in a separate table you have got
| | 04:58 | all your cities in one table, you have
got all your languages in one table and
| | 05:01 | they are all joined
together based on these ID fields.
| | 05:04 | This is what a relational database is
and this is the power of a relational
| | 05:08 | database. This is how you type into
that power with the Join statement.
| | Collapse this transcript |
| Filtering data with WHERE, LIKE, and IN| 00:00 | The WHERE clause is used for filtering
which rows will be returned from a query.
| | 00:03 | There are rich set of operators to
choose from in constructing your
| | 00:07 | WHERE clause. These operators are
sometimes called predicates in the literature
| | 00:11 | so you will know what
they mean when you see that.
| | 00:13 | In our first example, we'll construct a
query that only returns rows where the
| | 00:17 | city is in the GBR country code, which
is Great Britain or the United Kingdom
| | 00:21 | for those of you who are
following along at home.
| | 00:38 | Before we run it, let's take a look
at this query and see what we expect to happen.
| | 00:41 | This is a SELECT query and in
the SELECT clause, we have CountryCode,
| | 00:46 | Name, and Population. We are selecting
from the City table in world database.
| | 00:53 | We have a WHERE clause, the expression
is CountryCode equals GBR, where GBR is
| | 00:58 | a literal string. You see the single
quotes there and that makes this a literal string.
| | 01:02 | Now what this means is we are looking
for rows where the CountryCode will have
| | 01:06 | to use exact letters and no more no less,
GBR, which stands for Great Britain.
| | 01:11 | Go ahead and run the query and we see
our three columns: CountryCode, Name, and
| | 01:16 | Population. This is exactly what
we expected from our SELECT clause.
| | 01:20 | The CountryCode, all these rows have
country code equals GBR. See we have 81
| | 01:25 | rows and that sounds about right. So
this is exactly what we expected to have
| | 01:29 | happened. The WHERE clause directs the
database engine to go through the result
| | 01:34 | set and only return the results where
this condition is true. In this case,
| | 01:39 | the condition expression is equals
expression. CountryCode equal with the literal
| | 01:44 | string, GBR, and that's exactly what we got.
| | 01:47 | Now let's construct a different query
in a different WHERE clause that return
| | 01:52 | cities with the population greater than
or equal to five million and this looks like this.
| | 02:01 | So here we have our WHERE clause as
population is greater than or equal to five
| | 02:05 | million. So we are using a comparison
operator, greater than or equal to, with
| | 02:09 | a literal number that says five million.
So this should return rows where the
| | 02:14 | Population column has a number in it
that's greater than five million. There we go;
| | 02:21 | 24 rows and all of these populations
are greater than or equal to five million.
| | 02:27 | Now sometimes you might want a query
that matches part of a string and for this
| | 02:31 | there is a special operator called
LIKE. This is how LIKE works and in this
| | 02:40 | LIKE expression, we are matching the
Name column with the string that starts
| | 02:45 | with the Z and has this percept symbol
which is a wild card and it means any
| | 02:50 | characters after that are okay. And so
this should return rows where the Name
| | 02:54 | column starts with Z and has anything
at all after that, and here we have all
| | 03:00 | of our cities that start with the letter, Z.
| | 03:02 | Similarly, you could put the percent
sign at the beginning and match any rows
| | 03:08 | where the Name column ends in Z and
there we have name columns that end in Z.
| | 03:14 | If you a percent symbol on either of
side of the Z, you can get name columns
| | 03:20 | that have a Z anywhere in them
whatsoever. And here you have one that's got a Z
| | 03:25 | in the middle some place, Z at the
beginning, Z at the end. So now we are
| | 03:30 | matching anything with the Z anywhere
in it. And that's how the LIKE clause works.
| | 03:35 | Now finally, there is the IN operator
and that one works like this.
| | 03:53 | The IN operator selects from a list of
values and compares that list to the column or
| | 03:59 | very well that you provide for it. In
other words, this will return rows where
| | 04:04 | the CountryCode is USA or Canada or
Mexico or anything that you put in this list.
| | 04:13 | So here we have our result set. We
have CountryCode Canada and then there are
| | 04:19 | some CountryCode Mexico and some
CountryCode USA and this is what we expected.
| | 04:28 | So the IN operator is very useful for
selecting from a list of possible choices.
| | 04:35 | Finally you can combine WHERE clauses
using the Boolean operators. And and Or,
| | 04:42 | these are logical operators and this
how that works. So, this query will return
| | 04:51 | rows where the CountryCode is one of USA,
Canada or Mexico, and the population
| | 04:56 | is greater than a million. So we would
expect a smaller result set. We have 496
| | 05:01 | rows in this results set, we would
expect a smaller the number, all of these
| | 05:05 | population to be greater than the
million. So we would have Montreal but we
| | 05:08 | would not have any of these other Canadian ones.
| | 05:10 | Let's go ahead and see what happens here.
Ah! There we go, we got Montreal all
| | 05:14 | and then we have Mexico and we have
USA and we have only 20 rows returned. So
| | 05:20 | this is what we expected. So both of
these conditions are in operation here and
| | 05:24 | the And operator means
both of them needs to be true.
| | 05:28 | So combining your WHERE clauses like
this, combining your comparisons, allows
| | 05:33 | you to drill down into your data and
find just what you are looking for.
| | 05:36 | Nothing more and nothing less. WHERE,
LIKE, and IN are powerful tools for
| | 05:40 | searching and retrieving data. A Lot
more tools are likely available for your
| | 05:43 | platform so be sure to check the
documentation to find out what's there.
| | Collapse this transcript |
| Removing duplicates with DISTINCT| 00:00 | Sometimes you might want a list of data
out of your database that otherwise has
| | 00:04 | lot of duplicates in it, but without
the duplicates, you can see what different
| | 00:07 | kinds of some thing there are. Let's
take a look at one of those examples.
| | 00:28 | This will give us a list of all the
different government forms where the head
| | 00:32 | of state is Queen Elizabeth.
| | 00:36 | There we go.
So we can see there is Dependent
| | 00:38 | Territory, there is Constitutional
Monarchy, there is Federation, there is
| | 00:43 | Nonmetropolitan Territory of New
Zealand. This would be easier to look at
| | 00:48 | without the duplicates and then I could
see what those different forms are in a
| | 00:52 | shorter list. So I'm going to
put the word DISTINCT in here.
| | 00:55 | What SELECT DISTINCT does is it does
the select but it only shows me those
| | 01:01 | records that are distinct. In other
words, it shows me the list without the
| | 01:05 | duplicates. The default is SELECT ALL.
So if I were to put the keyword ALL in
| | 01:11 | here instead of DISTINCT, we would
see the list that we got before. SELECT
| | 01:17 | DISTINCT gives us just those
rows from our query that are unique.
| | 01:24 | So SELECT DISTINCT is a useful tool
for removing duplicates from your result set.
| | Collapse this transcript |
| Sorting with ORDER BY| 00:00 | Results returned by your query are not
guaranteed to be in any particular order,
| | 00:04 | unless you use an ORDER BY. Let's go
ahead construct a query and see what we
| | 00:08 | are talking about.
| | 00:23 | Here is a query that would return that
would Name and District columns from the
| | 00:27 | City table, where the CountryCode is
equal to USA. So this will return names in
| | 00:33 | the United States and districts. The
districts tend to be states within the
| | 00:37 | United States and these names are
here and it does not appear to be in any
| | 00:41 | particular order. Now if want it to
be in a particular order, we supply an
| | 00:44 | ORDER BY clause to accomplish that.
| | 00:50 | Now there should be an order to the
Name column and because the Name column is
| | 00:56 | a text type, these are in
alphabetical order. We are going to see Abilene,
| | 00:59 | Akron, Albany, Albuquerque. This is
exactly what we would expect to have
| | 01:04 | happen. The ORDER BY clause is capable
of sorting by more than one column.
| | 01:08 | For example, if you want your output to be
by district and then by city name within
| | 01:12 | the district, you could do something
like this. Now our results will be in
| | 01:18 | order of state and within each state,
there will be an order of the city name.
| | 01:25 | There we have Alabama, Alaska, Arizona
and within Alabama, you have Birmingham,
| | 01:30 | Huntsville, Mobile, Montgomery.
| | 01:33 | The ORDER BY clause can use an
expression as well. Let's construct a new query
| | 01:37 | to demonstrate this.
| | 02:09 | This query will return
a Region column, Average Life
| | 02:13 | Expectancy column with the title
AvgLE at the top of it. From the Country table
| | 02:18 | where there is a life expectancy,
because we don't actually have life
| | 02:22 | expectancy data on every row, GROUP BY
Region which means that the average will
| | 02:27 | be taken from a group of rows that
have a common region and they will be
| | 02:32 | grouped together in the results.
| | 02:34 | ORDER BY AVG(LifeExpectancy). So we
would expect the order of the results to be
| | 02:40 | in order of life expectancy with the
smallest numbers at the top, and there we
| | 02:46 | have it. We have the regions; there is
our list of regions. We have AvgLE, the
| | 02:51 | average life expectancy starting with
the Southern Africa, you don't want to
| | 02:55 | live there, all the way to Australia and
New Zealand and I just might want to move there.
| | 02:59 | Now, notice that the ORDER BY clause
here has an expression and that expression
| | 03:04 | is a function call. In fact, it's
better written like this. Using the name
| | 03:12 | that's in the AS clause here which
we can call it Alias, and this better
| | 03:17 | because if you change this expression,
you don't have to remember to change it
| | 03:21 | in two places. There might be a
tendency to change it here and forget to change
| | 03:24 | it down here and then you would have
a discrepancy, you would have a subtle
| | 03:28 | error probably and it will be often
some subtle way that you might not notice
| | 03:32 | right away and might take a long
time to remember, why is that wrong.
| | 03:36 | Debugging could be difficult.
| | 03:38 | So let's go ahead and run this and
we'll see we get exactly the same results.
| | 03:41 | If we use the Alias for the ORDER BY,
any changes you make in the SELECT clause
| | 03:45 | are automatically picked up by the
ORDER BY and you don't have that problem.
| | 03:50 | The data returned by your queries
will not necessarily be in any particular
| | 03:53 | order. If you need it to be in a
particular order, you must use an ORDER BY
| | 03:57 | clause. ORDER BY can sort your query by
any column or set of columns or by any
| | 04:02 | valid expression.
| | Collapse this transcript |
| Updating data| 00:00 | One of the four basic operations that
you will be performing on your database
| | 00:04 | is an Update operation. So let's look
at how to do it. We'll start by taking a
| | 00:07 | look at a single row in the track table.
| | 00:16 | You will notice that this row has
| | 00:17 | a typo in the title as this extra
character and we want to be fixing that.
| | 00:22 | First of all, when you are doing an
update manually, now this is important,
| | 00:26 | it is imperative that you test your Where
clause with the Select statement before
| | 00:31 | you do an Update. Now we just did that.
We isolated one row in the table with
| | 00:36 | this Where clause id = 16. Now we'll
use that same Where clause to construct
| | 00:42 | our Update statement.
| | 00:53 | Now when we execute this statement,
it will update this row and it will set
| | 00:58 | this column title to be equal to this
literal string, Blue Suede Shoes, without the typo.
| | 01:06 | Our statement ran. It affected
one row which is exactly what we intended.
| | 01:11 | Now let's go and take a look at that
row again and see if it worked. There is
| | 01:20 | our row and it's been updated, the
title no longer has the typo in it.
| | 01:26 | The Update statement is used to change
the values of data in a table, it uses a
| | 01:30 | Where clause to specify which row or
rows will be updated. Be careful, test
| | 01:35 | your Where clause with the Select
statement before you use Update, where you
| | 01:39 | may run the risk of destroying
data that you didn't mean to affect.
| | Collapse this transcript |
| Deleting data| 00:00 | One of the four basic operations of
any database application is Delete.
| | 00:04 | So let's look at the SQL that deletes a
row from a table. We'll start by selecting
| | 00:08 | the row that we want to delete.
| | 00:16 | This is one row from our track table in
| | 00:19 | the album database and you notice the
title is Fake Track, so that's the one that
| | 00:23 | we want to delete.
| | 00:24 | This is important, whenever you are
doing a manual delete, it's imperative that
| | 00:28 | you test your Where clause with a
Select statement before you execute the
| | 00:32 | Delete. That's what we just did. We
used the Select statement and we have this
| | 00:36 | Where clause id = 70. That isolates
just the row that we want to delete.
| | 00:41 | We verify that visually and then we can
construct our Delete using the same Where clause.
| | 00:49 | The Delete statement itself is very
simple, Delete From, the name of your
| | 00:53 | table, which in this case is track
and the Where clause that we just tested
| | 00:57 | with the Select statement. When we
execute this statement, it will delete
| | 01:00 | that one row from the track table.
Statement affected one row.
| | 01:05 | That's what we expected.
| | 01:07 | Now we can use our Select statement
again to verify that indeed that one row
| | 01:13 | from the track table was deleted. The
query returned no data. What that means
| | 01:19 | is that there is no row in the table
that matches this condition where id = 70,
| | 01:25 | and that's what we except.
| | 01:26 | Now just to make sure we didn't
delete everything, let's go ahead and do a
| | 01:29 | Select from the entire table. And we
have 62 rows and that's exactly what we expected.
| | 01:38 | There is all of our track table.
| | 01:40 | One more important note, if you omit
the Where clause entirely from a Delete
| | 01:45 | statement, you will delete every row in
the table. That's probably not what you
| | 01:49 | intend to do. So watch out for that.
Make sure you have a Where Clause and make
| | 01:53 | sure that you tested it
with the Select statement.
| | 01:55 | The Delete statement is used to remove
a row or more than a one row from a table.
| | 01:59 | It uses a Where clause to specify
which rows will be deleted. Be careful
| | 02:03 | to test your Where clause with the
Select statement before you use Delete,
| | 02:07 | where you run the risk of destroying
data that you didn't mean to delete.
| | Collapse this transcript |
|
|
4. IDs and IndexesCreating relationships between tables| 00:00 |
The major advantage of a
relational database over something more
| | 00:03 |
two-dimensional like a flat file
database or hash list is that you can create
| | 00:08 |
efficient useful relationships between
tables. Typically unique id fields are
| | 00:13 |
use to create relationships. These
ids are use to identify specific rows in
| | 00:18 |
separate tables that are related to each other.
| | 00:21 |
Indexes are used to make the
relationships efficient. Indexes are special data
| | 00:26 |
structure called B-trees that are
designed to be small and facilitate fast
| | 00:31 |
searches. Finally, joins are used
to make it convenient to use the
| | 00:35 |
relationship. Joins are special queries
that allow you to read related records
| | 00:40 |
from multiple tables in one query.
| | 00:42 |
This illustration shows how the
tables are typically laid out. The customer
| | 00:46 |
table has columns for its own id field
and the sale table has a column for its
| | 00:51 |
own id field. The sale table also has a
column for the customer_id so that you
| | 00:57 |
know which customer each sale belongs to.
When you query the sale you can join
| | 01:01 |
on the customer table to read the
customer data in the same query.
| | 01:06 |
| | Collapse this transcript |
| Accessing related tables with JOIN| 00:00 | The test database has three tables in it:
sale, item and customer. The sale table
| | 00:05 | has data on individual sales,
and uses id fields to refer to the item
| | 00:10 | table for data on the items, and the
customer table for data on the customers.
| | 00:15 | So if you want to know how many of each
item was sold you can use a query like this.
| | 00:47 | So this query will show us the
sum of the Quantity column and the name as Item.
| | 00:53 | From the sale table joined
on the item table with the item_id and
| | 01:00 | grouped by the item_id. So the sum,
it's grouped. Remember the aggregator sum
| | 01:06 | is affected by the Group By clause. And
so for each of the items, it will group
| | 01:12 | all of the sales of a particular item,
and add up the quantities to show us the
| | 01:17 | total quantity sold for each item, and
give us the item name. So there we have it.
| | 01:22 | We have five items with the name
of Box of 64 Pixels, one Sense of Humor,
| | 01:27 | and two Bar Codes have been sold.
| | 01:29 | Now this is useful information but what
if we want to know in addition to this
| | 01:33 | which items have not been sold. So we
have to do a different kind of a join.
| | 01:37 | There is something called a right join.
The default join, which is the one
| | 01:40 | without a keyword before it, is called
an inner join. The outer joins like a
| | 01:45 | right join or a left join will also
show items which have no values. So if we
| | 01:50 | do a right join here, we can see which
items have not sold, in addition to the
| | 01:56 | ones that have. And then we can do an
Order By to put the smaller numbers at the top.
| | 02:05 | Now we can see that nobody is buying
our Beauty and we sold one of this and two
| | 02:10 | of that and five of those. But using
the right join, we are able to see the
| | 02:14 | items that didn't sell as
well as the items that did sell.
| | 02:18 | Now we might want to create a more
complete report that will show us not just
| | 02:22 | what's selling, but how many you are
selling to which customer, and at what
| | 02:25 | price. For this we are going to need
to join both of the other tables to our
| | 02:30 | sale table. Let's take a look at this query.
| | 03:13 | Now we can see who is buying what and
how many and at what price. This is a
| | 03:18 | little bit more complex of a query,
because it joins two tables on the one
| | 03:23 | table. So we have the sale table, which
is getting joined to the item table on
| | 03:28 | the item_id, and also being joined to
the customer table on the customer_id. So
| | 03:34 | we can see the date of the sale,
the customers name, the item, and
| | 03:39 | the quantity, and the price.
| | 03:42 | There are many types of joins, and we
have just covered a couple of the more
| | 03:45 | common ones here. If your application
has any significant complexity, or even
| | 03:49 | just carries information of more than
one type as this application does here,
| | 03:53 | you will probably need to use joins.
| | Collapse this transcript |
| About indexes| 00:00 | In the database world, an index is
essentially a quick lookup table for finding
| | 00:04 | things you would need to search more
frequently. They are small, fast and
| | 00:08 | optimized for fast lookups, very
useful for connecting relational tables,
| | 00:13 | especially when your tables get large.
| | 00:16 | Indexes primarily a performance tool
so they really only apply if you expect
| | 00:20 | your database to get large. You will
notice in our Create Table definition for
| | 00:23 | the customer table the Primary Key at
the end of the id column definition.
| | 00:28 | This creates an index that's especially
optimized to get used a lot. So when you use
| | 00:34 | your joins, the database can lookup
the ids in the index quickly and easily
| | 00:38 | instead of searching sequentially
through a potentially large table.
| | 00:41 | Indexes are fast, partly because they
don't have to carry all the data for
| | 00:45 | every row in the table, just the data
you are looking for, at a small point or
| | 00:50 | into the full table. This makes it easy
for the operating system to cache a lot
| | 00:53 | of the index and memory for faster
access. And for the file system to read many
| | 00:58 | more records at a time when it
has to go to the disc for data.
| | 01:02 | You can create additional indexes by
using the Index keyword in your table
| | 01:05 | definition. This is useful if there
are more than one column that will be
| | 01:09 | searched often in your table.
| | Collapse this transcript |
|
|
5. StringsAbout the string functions| 00:00 | SQL string functions operate on strings.
And a string is a set of characters.
| | 00:05 | It can be as few as one character or
as many characters that will fit in
| | 00:08 | the given space.
| | 00:09 | In standard SQL, a string literal,
that is a string that you type whether it
| | 00:13 | appears in an expression, is
enclosed in single quotes like this.
| | 00:25 | Here's the string literal and as you can see
it's enclosed in single quote marks. Now if
| | 00:30 | you want to include a single quote in
the string literal, you type it as two
| | 00:35 | single quotes next to each other like this.
| | 00:46 | So here we see in the resulting string,
there is a single quote mark being used
| | 00:51 | as an apostrophe in this part of the
string here Alfonzo's. And up here in the
| | 00:55 | string where we typed it that appears
as two single quote marks next to each other,
| | 01:00 | and that's how that works.
| | 01:02 | String handling tends to be platform
specific in SQL. A simple thing like
| | 01:06 | putting two strings together can be
very different on different platforms.
| | 01:10 | As an example, let's look at how you can
concatenate two strings on two popular platforms,
| | 01:14 | MySQL and Microsoft SQL Server.
| | 01:25 | Here we use the Concat function, which
is a string function, to concatenate two
| | 01:30 | strings, to put them together. So our
result will say, Hello, World, like this.
| | 01:35 | This example works in MySQL which is
the database server being used by our SID
| | 01:41 | application. In Microsoft SQL Server
that doesn't work at all and concatenating
| | 01:46 | two strings is done entirely differently.
| | 01:58 | So here in Microsoft SQL Server,
| | 02:00 | we can see that we can concatenate two
strings by using the plus operator. We have
| | 02:04 | one literal string here, which is
Hello with the comma and the space, and
| | 02:07 | another literal string here, which is
World enclosed in single quotes. And our
| | 02:12 | result has a Hello, World, because
we've put these two strings together,
| | 02:17 | we have concatenated them with the plus
operator. So that's how you do it in
| | 02:21 | Microsoft SQL Server, which is very
different than how you do it in MySQL.
| | 02:26 | In the next few lessons we'll look at
some of the more common string functions
| | 02:29 | and how they work. There are many
platform specific string functions available
| | 02:33 | in each SQL environment and it's beyond
the scope of this course to show all of
| | 02:38 | them here. Be sure to check with the
documentation to find out what's available
| | 02:41 | for your particular environment.
| | Collapse this transcript |
| Finding the length of a string| 00:00 | The Length function reports the number
of characters in a string. For example...
| | 00:17 | this query reports the length of the
literal string, Rock and Roll Medley,
| | 00:21 | which has 20 characters in it. You can
also use the Length function in a query.
| | 00:37 | So this query retrieves the title from
each row in the database, and computes
| | 00:43 | the length of that title and reports it
here. So here is the title and here is
| | 00:48 | the Length function, and we use an As
clause to rename it to say title length
| | 00:54 | from the album table.
| | 00:55 | It's worth noting that the length of a
string is not necessarily the number of
| | 00:59 | bytes in a string. Strings are
generally considered text in the database so the
| | 01:03 | number of bytes may be
different than the number of characters.
| | Collapse this transcript |
| Finding part of a string| 00:00 | The Substring function extracts a portion
of a string and returns it as a new string.
| | 00:04 | For example...
| | 00:15 | In this query we have
a literal string Hello, World and
| | 00:20 | the Substring function takes that
literal string, where you could put a column
| | 00:25 | name here, and it takes a Substring
beginning at the first character which
| | 00:28 | would be the H. And it takes five
characters that would be the H, E, two Ls,
| | 00:34 | and the O, and returns it As String.
And here we see in our result, we have
| | 00:40 | those five characters under the heading String.
| | 00:43 | Two related functions are called Right
and Left. For example, Right like this
| | 00:50 | starts at the right side of the string
and takes the rightmost five characters
| | 00:56 | and returns those as string. Here we go;
we have the rightmost five characters,
| | 01:01 | World. Likewise the function
called Left would take the leftmost five
| | 01:07 | characters, which would be these five,
and return that as the string. And there
| | 01:11 | we have it, those five
characters which spell the word, Hello.
| | 01:16 | Substring functions are very common.
You may need to display just the first so
| | 01:19 | many characters of a string, or you
may need to search for just a part of a
| | 01:22 | string in a table. Use these functions
whenever you need to operate on part of
| | 01:26 | a string rather than the entire string.
| | Collapse this transcript |
| Removing spaces with TRIM| 00:00 | Sometimes you end up with unwanted
spaces at the beginning or the end of a string.
| | 00:03 | This is very common when taking
input from a user, as most people just
| | 00:08 | don't think about spaces and they have
no idea that they can be problematic,
| | 00:11 | especially when entering an email
address or some other data that will be used
| | 00:15 | entirely in the computing domain.
| | 00:17 | Spaces at the beginning or at the
end of the string can be problematic.
| | 00:21 | You will notice that we have up on the
screen here at the MySQL Command line
| | 00:25 | interface. The reason for this is that
SID, how we have been demonstrating all
| | 00:30 | of the rest of our lessons, is a web
based interface and web based interfaces,
| | 00:35 | web browsers in particular tend to
eliminate spaces when displaying things,
| | 00:40 | we want to be able to see the spaces clearly.
| | 00:42 | So we are using that command line
interface just for that purpose, so that we
| | 00:46 | can demonstrate the spaces and you can
actually see the spaces on the screen as
| | 00:50 | they get magically eliminated by the Trim
functions. So let's go ahead and type a query.
| | 01:05 | Now you notice at the end of the query
I have typed the semicolon. This is one
| | 01:09 | of those very instances where the
semicolon is actually required. The MySQL
| | 01:13 | command line interface requires the
semicolon, so that it knows where the end
| | 01:18 | of a query is. Well it will accept it,
it won't actually run the query until
| | 01:22 | you typed the semicolon. So I have
typed it at the end of the line here.
| | 01:25 | I'm going to go ahead and press
Enter and there is our query.
| | 01:28 | Now you see there is four spaces to
the left and to the right of the string.
| | 01:32 | Now we are going to go ahead and put
in the Trim function and watch those
| | 01:35 | spaces magically disappear. I'm going
to type the Up Arrow to get the last
| | 01:40 | query so that I can edit it and I'm
using the Left Arrow to go the beginning of
| | 01:43 | the literal string and type Trim and
open parenthesis and go to the, just after
| | 01:51 | the quote, type a closed parenthesis
and I'll just press Enter and you see that
| | 01:56 | the spaces are now gone.
| | 01:58 | So this is what the Trim function does.
It's very, very useful for any kind of
| | 02:03 | input from a user that will require
that there are not be spaces at the
| | 02:08 | beginning and the end and this is going
to be used entirely in the computing domain.
| | 02:12 | You just run the Trim function
on it and the spaces are gone.
| | Collapse this transcript |
| Making strings UPPERCASE and lowercase| 00:00 | The Upper and Lower functions are
common in SQL and what they do is they
| | 00:04 | convert a string to all uppercase in
the case of the Upper function and all
| | 00:08 | lowercase in the case of the Lower
function. Let's look at our example.
| | 00:18 | So here we have all the titles from our
album table and if we use the Upper function here,
| | 00:28 | see all the titles in uppercase.
And if we use the Lower function,
| | 00:35 | see all the album titles in lowercase.
| | 00:39 | So the Upper function converts a
string to all uppercase and then the Lower
| | 00:43 | function converts the string to all lowercase.
| | Collapse this transcript |
|
|
6. NumbersWhen to use numeric functions| 00:00 | The Numeric functions are functions
that deal with numeric data. That is data
| | 00:03 | that has numeric type. It's important
to know that while they may work with strings
| | 00:07 | that have numeric content,
their behavior is not guaranteed for
| | 00:11 | strings that may have non-numeric content.
| | 00:13 | For example...
| | 00:21 | This is the function that
returns the absolute value of a number.
| | 00:24 | So we entered the number -12 and the
absolute value is 12 and that's the result
| | 00:29 | that we expect. Now let's make that
argument a literal string with numeric content.
| | 00:35 | Go ahead and run the query again.
| | 00:38 | The result is the same. This time we
have the literal string. It's got single
| | 00:42 | quotes around it. The content inside is
entirely numeric -12. The result is 12.
| | 00:47 | Database was able to figure out what it
was that we intended. Even though that
| | 00:51 | strictly speaking a
string and not a numeric type.
| | 00:55 | Most databases will work that way. In
fact, all of them that I have tried do
| | 00:59 | work that way. On the other hand, if
I put a character in here that is not
| | 01:05 | numeric inside the literal string. The
result is undefined. In case of MySQL,
| | 01:10 | it will give us a zero, which is
polite way of saying not a number.
| | 01:15 | Some other databases will give you
special value that means not a number or
| | 01:19 | actually return a string
that says NAN for not a number.
| | 01:23 | The point is that with non-numeric
data the result is undefined. You want to
| | 01:29 | make sure when you are using numeric
functions that the data that you pass to
| | 01:33 | them is guaranteed to be numeric.
| | Collapse this transcript |
| Finding the absolute value| 00:00 | The Absolute value function returns the
absolute value of a number. For example
| | 00:08 | the absolute value of -12 is 12 and
there is our result, 12. Likewise,
| | 00:13 | the absolute value of 12 is also 12.
And there we have our result there.
| | 00:20 | This can be useful if you need to make
sure that your numeric data is positive,
| | 00:23 | or if you need to operate on it as
an absolute value. For example...
| | 00:53 | Here we have a query that shows us a list of
countries with the delta of their GNP versus
| | 00:58 | their old GNP even though the GNP
maybe larger or smaller than the old GNP,
| | 01:03 | this still gives us the Delta
in form of a positive number.
| | 01:08 | Keep in mind that it's often faster to
do arithmetic operations in the database
| | 01:12 | engine than in an interpreted language
like Perl or PHP, especially if you need
| | 01:16 | to operate on a large column of data.
So that's how ABS works. Lats and
| | 01:21 | deltoids are left as an exercise for the reader.
| | Collapse this transcript |
| Rounding numbers| 00:00 | The Round function rounds the number
up or down depending on its value by
| | 00:03 | common rules where anything where the
fractional part less than .5 gets rounded down;
| | 00:08 | anything with .5 or
higher, it's rounded up.
| | 00:11 | For example, 5.49 gets rounded down to 5,
5.5 gets rounded up to 6, -5.49 gets
| | 00:30 | rounded to -5 and -5.5 gets rounded to -6.
| | 00:36 | Let's look at a practical example of Round.
| | 01:16 | Here is a query that shows us
| | 01:17 | the average life expectancy grouped by
Region and with the Round of that number
| | 01:22 | because we don't necessarily need all
of these extra digits and they are not
| | 01:26 | really significant.
| | 01:27 | So for 68.08571, 68 is really your main
information. 70.56667, that's 71. 44.82
| | 01:40 | is only 45 years. So this is a
practical example where you might actually want
| | 01:45 | to show some rounded data rather than
all of the significant digits that you
| | 01:49 | would get back from the database.
| | Collapse this transcript |
| Integer division and remainders| 00:00 | Sometimes you may be interested in the
integer result of division along with
| | 00:05 | its remainder rather than the fractional result.
| | 00:07 | For example, from the album database...
| | 00:35 | In our album database we keep
| | 00:37 | the duration of a track as the number of
seconds. So we can see here the numbers of
| | 00:42 | seconds. We can display that using
a built-in function in MySQL called Sec_To_Time.
| | 00:54 | And this will show it in the
built-in time format that MySQL uses,
| | 00:59 | which is hours, minutes, and seconds.
But I don't really like that.
| | 01:02 | I don't want to show hours, minutes, and
seconds, I want to show just minutes and
| | 01:06 | seconds and MySQL does not
have a built-in function for that.
| | 01:10 | So we'll create one. Here is how we do
that using DIV and MOD. DIV and MOD are
| | 01:17 | functions that return the integer
result and the remainder result respectively
| | 01:23 | from a division operation.
So here is what this looks like.
| | 01:49 | Now we have just minutes and seconds. Like for
instance, Red House way down here is 13:06 or
| | 01:56 | up here we have Bright Lights Big
City 5:20, Basin Street Blues 4:56.
| | 02:02 | The way this works, break this down a
little bit. We use the Concat function
| | 02:07 | which we have seen before and it begins
here with this parenthesis and it ends
| | 02:11 | here with this parenthesis. And what
Concat does as it concatenates strings.
| | 02:16 | It puts them together. So we have the
first part of it right here, which is the
| | 02:20 | minutes part, and then we have a
literal string for the colon and then this
| | 02:25 | LPAD function for the seconds part.
| | 02:27 | Now the minutes part is simple it's
the duration divided by 60 but this is
| | 02:33 | integer division with the DIV function.
We used a slash that would give us the
| | 02:37 | floating point division. That's not
what we want. I want the integer division,
| | 02:41 | which gives us just the division part
and not the remainder. Then we have our
| | 02:45 | literal string for the colon,
separates the minutes from the seconds.
| | 02:49 | Now the seconds uses this MOD operator
to give us the remainder. MOD stands for
| | 02:55 | modulo which is a mathematical term
that means the remainder part of an integer
| | 03:00 | division. And it wraps that into an
LPAD function. Now what LPAD does is it
| | 03:06 | pads the left side of a string and so
in this case we are padding it so that
| | 03:10 | it's two characters long and we are
padding it with zeros, if it's not two
| | 03:14 | characters already.
| | 03:15 | For example, in this row here the
remainder part of that division is 44. That's
| | 03:20 | already two characters, so it doesn't
pad it. If we look down here at Stardust,
| | 03:25 | that's 5:08. So the 8 seconds part
has to get padded with that leading 0 so
| | 03:31 | that it looks right. This is what you
expect to see in minutes and seconds,
| | 03:35 | 5:08, not just 5:8, which would be
technically correct. But we are used to see
| | 03:42 | it with leading 0 on the seconds part.
| | 03:45 | So this is a practical application of
using DIV and MOD integer division.
| | 03:50 | DIV and Mod are integer division operators
and they are very useful. DIV performs
| | 03:55 | integer division and returns the
integer result without the remainder.
| | 04:00 | MOD performs integer division
and returns the remainder part.
| | Collapse this transcript |
|
|
7. DatesDates and times| 00:00 | SQL handles dates and times as a
distinct type of data. This allows the system
| | 00:05 | to do date and time based arithmetic
and comparisons. Typically, dates and
| | 00:09 | times are processed in a format like
YYYY-MM-DD, HH:MM:SS, where each part is
| | 00:15 | represented numerically. This is
designed to make sorting and comparisons
| | 00:19 | natural, as the more significant
digits are always to the left of the less
| | 00:23 | significant digits.
| | 00:24 | Like so much else in SQL, there is
little commonality between platforms on how
| | 00:28 | dates and times are represented and
processed. The names of the date related
| | 00:32 | column types and the names and
functionality of the date and time related
| | 00:36 | functions are significantly
different on each platform.
| | 00:40 | For example, MYSQL has four date and
time related types; DATE, which represents
| | 00:47 | the Date only and stores date values
from the year 1000 to the year 9999, TIME,
| | 00:54 | which represents just Time values and
not just the time of day, but elapse time
| | 00:59 | as well. This type has a range of
plus and minus 840 hours. DATETIME, which
| | 01:05 | stores both date and time of day as one
unit, and TIMESTAMP, which is a special
| | 01:11 | column type, which is like a DATETIME
column with a few important distinctions.
| | 01:16 | TIMESTAMP columns are specifically
designed to keep time for events.
| | 01:20 | Time stamp values are always stored in UTC,
which is the time zone formerly known as
| | 01:25 | Greenwich Mean Time. They are converted
to and from local time zone for storage
| | 01:30 | and retrieval operations, but keep in
mind in this case, local time zone means
| | 01:35 | the time zone of the server, which may
or may not be the same time zone as the user.
| | 01:40 | Blink TIMESTAMP column for table, can
be set to automatically update with the
| | 01:44 | current time and date when a row is
inserted into the table. This is convenient
| | 01:49 | for storing the time of certain events.
| | Collapse this transcript |
| Date- and time-related functions| 00:00 | Let's take a look at some date and time
related functions that are available on
| | 00:03 | the MYSQL platform.
| | 00:09 | CURDATE() gives you the current date in
year, month and day format, so this is the date
| | 00:14 | as I record this, 2009, February 12th.
| | 00:22 | Likewise CURTIME() gives you the current time
| | 00:25 | in HH:MM:SS format. The hour is
always in 24-hour format so this is
| | 00:31 | the 10 o'clock that happens before
noon, 28 minutes and 53 second.
| | 00:38 | The NOW() function gives you the date
and time of right now. So we have the
| | 00:43 | date and the time in the same
format as CURDATE() and CURTIME().
| | 01:00 | The DATE_ADD() function will add an
interval to a date and time. In this case,
| | 01:06 | we have NOW() and we are adding two
weeks and here is the keyword INTERVAL to
| | 01:11 | specify that it's an interval, and the
number 2 for the number of weeks we are
| | 01:16 | adding, and the interval type of WEEK.
| | 01:19 | You can use day, week, month, year, and
we give it an alias, Later, so that it
| | 01:25 | shows up with the Later heading and
here is the date and time of two weeks
| | 01:30 | later than right now. So we have, today
is February 12, 02-12, two weeks later
| | 01:35 | it's February 26, 02-26.
| | 01:44 | Likewise the DATE_SUB() function
allows you to subtract an interval from a date.
| | 01:49 | Here we are subtracting two weeks,
naming it Earlier, and if today is
| | 01:53 | February twelfth, 02-12 then two
weeks earlier it's January 29, 01-29.
| | 01:58 | Date and time functions tend to vary a
lot by platform. The functions presented
| | 02:02 | here is just a sampling of what's
available on the MYSQL platform. Be sure to
| | 02:06 | check the documentation for your platform
to find out what's available in your environment.
| | Collapse this transcript |
|
|
8. AggregatesHow aggregates work| 00:00 | Aggregate functions are an extremely
powerful and useful feature of SQL.
| | 00:04 | They are called Aggregate functions
because they provide a result based on data
| | 00:08 | accumulated from a set of rows
rather than just one row of the table.
| | 00:13 | For example...
| | 00:20 | This simple query tells us
how many rows there are in the Country
| | 00:23 | table because the COUNT(*) function
operates on the aggregate of all the rows
| | 00:29 | in the Country table rather
than just one row at a time.
| | 00:52 | This query uses GROUP BY to group sets
of rows together and operate on those
| | 00:59 | groups separately. Aggregate functions
are most powerful when they are applied
| | 01:03 | to groups rather than the whole table.
So in this case, we have counts of how
| | 01:09 | many rows there are in each region
rather than just all the counts in the table.
| | 01:14 | GROUP BY breaks the query into groups
and the Aggregate functions then operate
| | 01:19 | on the groups of rows separately.
Let's take a look at another example,
| | 01:22 | this one out of the album database.
| | 02:00 | This query aggregates the number of
tracks per album, so it counts the number
| | 02:06 | of rows in this joint query where the
album is the same. This technique is
| | 02:12 | really common. When you are combining a
JOIN with the Aggregate function, it's
| | 02:17 | important to realize how this works.
| | 02:20 | The JOIN happens first and then the
GROUP BY, and then the Aggregate function.
| | 02:26 | So in this case, from the JOIN results,
the Count, it counts the number of
| | 02:32 | track_number columns in the joint query
grouped by the album_id. So you get all
| | 02:39 | of the ones with this album_id. It's 14,
all of the ones with this album_id,
| | 02:44 | there is 10. This is a very
common and useful technique.
| | 02:48 | Now let's add a new clause to this.
HAVING. HAVING is to group by as where
| | 02:59 | would be it to select. So in this
case, because the tracks result is
| | 03:04 | aggregated, we use HAVING to select
only the results where the tracks column is
| | 03:11 | greater than or equal to 10.There we
just have the albums with ten or more
| | 03:17 | tracks in them.
| | 03:18 | Aggregate functions operate on groups
of rows rather than individual rows.
| | 03:22 | They are useful on joint queries as well
as straight queries. The HAVING clause is
| | 03:26 | to group by as where it is to select.
You use HAVING when you need to filter
| | 03:31 | your results on an aggregated value.
| | Collapse this transcript |
| Removing duplicates with DISTINCT| 00:00 | The distinct operator works in a
different special way on aggregate functions.
| | 00:16 | So this query we are familiar with.
It counts the number of values in the
| | 00:20 | HeadOfState column in the Country table.
| | 00:23 | If we add the word DISTINCT right
here, it will do something a little bit
| | 00:28 | different. Notice the number 238 is
now 179. What this does is it counts the
| | 00:36 | number of distinct values in the
HeadOfState column in the Country table.
| | 00:41 | So one query gives you the number of
rows that have a head of state and this
| | 00:46 | query gives you the number of
different heads of state in the table.
| | 00:50 | The distinct operator can be
used in all aggregate functions.
| | Collapse this transcript |
| Using aggregate functions| 00:00 | So let's take a look at some of the
more useful aggregate functions.
| | 00:10 | This shows us the total number of seconds of all
the tracks, all added together in our track table.
| | 00:16 | If we want to view that in
hours, minutes and seconds,
| | 00:25 | we can combine it with
| | 00:26 | the SEC_TO_TIME function and what that
does is it takes this value which is the
| | 00:32 | value returned by the Sum function
and converts it into hours, minutes and
| | 00:36 | seconds because our duration column is
kept in second. Let's look at a little
| | 00:41 | more useful example.
| | 01:08 | This shows us the sum of the
durations per album. So this is the number of
| | 01:13 | seconds of all the tracks on the
Apostrophe album. This is the number of
| | 01:17 | seconds of all the tracks added
together on the Birds of Fire album etcetera.
| | 01:21 | And likewise we can display this
in hours, minutes and seconds.
| | 01:30 | That will feed the result of the SUM
calculation into the SEC_TO_TIME function.
| | 01:36 | Now we have it in duration in a
format that we are familiar with: hours,
| | 01:40 | minutes and seconds. There is some
other useful aggregates we can use in this
| | 01:44 | context. There is average. This will
give us the average duration per album of
| | 01:49 | all the tracks in an album. Here we go,
we can see it's Kind of Blue, Miles
| | 01:54 | Davis has a long average.
| | 01:57 | And the minimum, this will give us the
minimum duration, the shortest track on
| | 02:02 | an album. We can see Birds of Fire has
one which is 24 seconds and here is the
| | 02:09 | Max, or maximum. It's the longest track
on an album. Hendrix in the West has a
| | 02:17 | 13-minute one. I believed
that's Red House. I could be wrong.
| | 02:22 | So these are the most common aggregate
functions and they are available on most
| | 02:25 | platforms. Your platform likely
has others. So be sure to check your
| | 02:29 | documentation to see what you have available.
| | Collapse this transcript |
|
|
9. The CRUD ApplicationAbout the CRUD application| 00:00 | Now we are going to look at the CRUD
application. The CRUD application comes with
| | 00:03 | the examples that come with this
course and CRUD stands for Create, Read,
| | 00:07 | Update, Delete. The purpose of the
CRUD application in this course is to show you
| | 00:12 | how it's done.
| | 00:14 | The CRUD application is a very common
form of database application; it's really
| | 00:18 | the basis of most of the database
applications that you will write,
| | 00:22 | even the ones that are more complicated than this.
The purpose of having it here and
| | 00:27 | the purpose of going over it here is so
that you can see how the SQL is used within
| | 00:31 | the context of an application. So
this is an application that uses SQL to
| | 00:37 | perform all of the database functions.
| | 00:39 | So first of all, let's look at the
application and how it works. This is the CRUD
| | 00:43 | application. This is the page that you
get when you start it up and it lists
| | 00:47 | the albums that are in the database.
This application keeps information on
| | 00:51 | record albums and the tracks on the
albums. So it uses two tables, the Album
| | 00:56 | table and the Track table that we
have been using within the context of the
| | 01:00 | rest of the course, and those tables
are joined together on the Album ID.
| | 01:04 | So it's a very simple
example of two joined tables.
| | 01:08 | So let's look at how the CRUD
application works. This screen is the opening screen.
| | 01:13 | This is what you get when you
start up the application. It lists
| | 01:16 | the albums that are in the database and
it allows you to add an album to the database.
| | 01:21 | So, go ahead and add one.
| | 01:38 | So there is the album data and we add that,
| | 01:42 | we now get this screen that has the album
data here and on the tracks for the album here.
| | 01:47 | There aren't any tracks yet, it says
right there that we may add some now and
| | 01:52 | why don't we go ahead and do that?
| | 01:59 | Here is our first track. You will notice it
| | 02:01 | goes ahead and puts the cursor there.
That's very convenient for me to enter
| | 02:04 | the second track.
| | 02:11 | We'll go ahead and add one more.
| | 02:22 | Now we have added an album
and we have added three tracks.
| | 02:26 | So this is the Create function in Create,
Read, Update, Delete. Let's go ahead
| | 02:30 | and bring up SID and look at the data
that we have added. We'll start by looking
| | 02:36 | at the album data. Select the album
database. So, this is all of the albums in
| | 02:44 | the database and here with ID number 23
is the greatest album ever made, by Bill Weinman.
| | 02:49 | It's got all the information that we
entered on that first album screen.
| | 02:53 | I want to look at the track data.
Go ahead and enter a query for that.
| | 03:06 | Track number's a short little column, so
I like to have a short title at the top of it.
| | 03:39 | We are going to use a Where clause with
the album ID = 23 because we know that
| | 03:45 | that's the album that we are looking for.
| | 03:47 | So there is our Select and if I
haven't made me typos, this will work.
| | 03:52 | Look at that. S there is our three
tracks: first track, another track and
| | 03:56 | the greatest track ever recorded in the
album. So this is the data that was entered
| | 04:01 | over here in CRUD. Go ahead and click
Done and we'll see our album here.
| | 04:07 | You will notice this is in order by albums
so The comes between Rubber Soul and Two
| | 04:12 | Men With the Blues. And there is the
album. I can press Edit and we get back over
| | 04:16 | to the Edit screen that has
all of this information on it.
| | 04:20 | Now we can change things up here,
like for instance I can put this in title case.
| | 04:32 | And press Update and that information
gets updated in the database.
| | 04:38 | We'll go and look at it with our query in SID and
we'll see that the title has changed in the database.
| | 04:44 | So that's the Update function in Create,
Read, Update and Delete. We can update
| | 04:50 | individual tracks if we want to, with
that one in title case and again over here.
| | 04:57 | You can see it's got a lowercase there,
we run our query and it's got a capital T.
| | 05:01 | And we can delete if we want to
delete an individual track. I can just
| | 05:06 | delete this third track here. Notice
the confirmation screen. Whenever you are
| | 05:11 | creating an application that does a
delete function, you are usually going to
| | 05:15 | want to have a confirmation screen for delete.
| | 05:18 | Please make sure that you don't
accidentally delete data that you don't mean to.
| | 05:21 | You make the user actually confirm
they are deleting an action. Go ahead
| | 05:25 | and press Confirm and we have deleted
that third track. Again we'll test it
| | 05:30 | with our query in SID. SID is a useful
tool. You can use this tool when you are
| | 05:35 | actually developing an application
to experiment with your SQL or just to
| | 05:40 | confirm the things are happening
the way that you expect them to.
| | 05:44 | If we want to delete an entire album,
we come back to the first screen,
| | 05:49 | find the album that we want to delete,
there it is that one, we press the Delete
| | 05:54 | button for that row. Again we get a
confirmation screen and we'll see that this one
| | 05:59 | confirms that we want to delete
the album and all its tracks.
| | 06:03 | That's because we need to delete
both the album and the tracks.
| | 06:05 | If we don't delete the tracks, then we
have orphaned data in the database with
| | 06:10 | no way to get to it, because the only
way that we can access tracks in our user
| | 06:15 | interface is through the album. So
if we delete an album, we need to also
| | 06:20 | delete the tracks. So, we press Confirm
here and the album that we just added
| | 06:24 | is now gone from the database. If I go
and run this query in SID, we should get
| | 06:30 | nothing at all. The query returned no data.
| | 06:35 | So those are the functions. We have
Create, we added an album, Read.
| | 06:40 | This front screen here reads from the database
and when we go and look at album in the database,
| | 06:46 | it reads the tracks. So that's
the Read function. Update, we showed
| | 06:50 | the update. And Delete. Those are
the functions of the CRUD application.
| | 06:54 | One final note. This application comes
with the example data for the course.
| | 07:00 | You want to make sure that you do
not put this on a public web server.
| | 07:03 | This or SID for that matter. These
applications are designed for your personal use.
| | 07:09 | They don't have any mechanism for
segregating data by different users,
| | 07:13 | there's no user management whatsoever and
they are not designed with security in mind.
| | 07:17 | These are programming tools. These
are designed for development tools.
| | 07:21 | They're designed for your own personal use.
| | 07:23 | So try to avoid putting them on a web
server that's not behind the firewall,
| | 07:27 | a web server that would be available to
the general public. You want to keep
| | 07:31 | these applications behind a firewall.
They are designed for public use.
| | 07:35 | So that's the CRUD application. In the
rest of the movies in this chapter we'll be
| | 07:39 | looking at the actual code that makes
this up and looking at the SQL and how
| | 07:44 | that's used within the
context of a programming language.
| | Collapse this transcript |
| The SELECT functions| 00:00 | So now we are looking at the code that
makes up the CRUD application. This is
| | 00:04 | the application code that makes the
CRUD application work. The reason we are
| | 00:09 | doing this is I want to demonstrate
how SQL is used within the context of an
| | 00:15 | Application Programming Language.
| | 00:17 | SQL is a language that is specific for
interacting with a database. And it's
| | 00:22 | not really an application programming
language, you couldn't write an entire
| | 00:26 | application just in SQL.
| | 00:28 | So the purpose of these lessons is
to show how the SQL is used within the
| | 00:33 | context of an application programming
language. In this case, the application
| | 00:38 | is written in PHP, which is a common
language for use on the web. You will
| | 00:41 | probably see a lot of it if you look at
these sorts of things. And here you see
| | 00:45 | a function called get_albums_sql and
inside this function you will see here is
| | 00:51 | some SQL. The rest of this around here
is all PHP. And here is some SQL, which
| | 00:56 | is actually being used in a PHP
variable, and you see it's got the single
| | 01:01 | quotes much like you would do in SQL,
and the SQL is inside those quotes.
| | 01:07 | And what this does is it assigns this SQL
statement to a variable so that it can
| | 01:12 | then be used in the interface into the
database. So this line here passes the
| | 01:17 | query and to the database, and
this line here executes the query.
| | 01:22 | So this query is a very simple one,
SELECT * FROM album ORDER BY title, and
| | 01:28 | what that does is it selects all of
the data associated with the album table,
| | 01:33 | and it returns it in ORDER BY title,
and that allows this screen to happen.
| | 01:40 | Here we have the front page of the
application when you start up the
| | 01:43 | application. This is what you get, and
it lists all the albums in the table,
| | 01:48 | and in this case here they are, they
are in Title order and they are displayed
| | 01:53 | in this format with these little
buttons next to them. So the code that does that.
| | 01:57 | That takes the data once it's been
returned from the query that's the PHP code.
| | 02:02 | The query itself is a very
simple query. Nothing magical about it,
| | 02:07 | just like we have been learning
in the rest of the course.
| | 02:09 | Here is a more complicated query. This
is the query that gets the track data.
| | 02:14 | Let's go take a look at that in the
application. Here we have the Edit Album
| | 02:21 | page and it's this album here Hendrix
in the West. And the tracks are listed
| | 02:25 | here they are in little Edit boxes, so
you can edit them if you want to, but
| | 02:29 | these come from that query, it's a
straightforward select a query. The only
| | 02:33 | thing special about it is, it has
this code for the duration because as we,
| | 02:39 | remember the duration is kept in the
database in seconds, and we want to
| | 02:43 | display it in minutes and seconds
with the colon in the way that we would
| | 02:46 | expect time to be displayed.
| | 02:49 | So if we look at the code, here is the
query, here is SELECT id, album, title,
| | 02:55 | track_number and this is a MySQL
function called CONCAT with separator.
| | 03:01 | So the first argument is a separator that's
the colon and then after that we have the
| | 03:05 | strings that are to be concatenated,
and when they are concatenated the
| | 03:08 | separators are put between them, very
convenient and very specific to MySQL.
| | 03:12 | I have not seen this function or
anything like it, in any other database.
| | 03:17 | But it's a good idea to use the features
of the database that you are using if you can.
| | 03:21 | And if you know that your application
is always going to be run with this
| | 03:25 | particular database engine, go ahead
and use these features. They are usually
| | 03:29 | efficient, they are optimized, and they
make the rest of the code easier to do.
| | 03:34 | This way I don't have to do all of this
in PHP. I can just to do it right here,
| | 03:38 | it happens quickly within the database
engine, it's actually faster than if I
| | 03:42 | were run it in an interpreted language like PHP.
| | 03:45 | So here we have the two arguments: are,
duration DIV 60. So the DIV operator
| | 03:50 | you all remember does an integer
division, leaving a remainder, and then we use
| | 03:55 | duration MOD 60 with the MOD operator.
That gets the remainder of the division.
| | 04:01 | And this is what gives us the
minutes and the seconds. LPAD function,
| | 04:05 | we covered this before, it pads the left-
side of a string with 0, so here it is
| | 04:11 | looking for a length of 2, and so if
there is less than two characters it will
| | 04:15 | use zeros to fill it up into two
characters. All of that gets concatenated with
| | 04:20 | the colon in between and we have what
we saw over here, minutes and seconds and
| | 04:25 | here you see one that's padded
with zero 13 minutes and 6 seconds.
| | 04:30 | So I'll select all of that stuff
FROM Track, WHERE album_id = ?.
| | 04:37 | What the question mark does is it's a
placeholder and this is very common in the
| | 04:42 | interface between an application
programming language and SQL. You will use a
| | 04:48 | placeholder in your SELECT, or in
your query, or in your SQL, and that
| | 04:53 | placeholder will get replaced with data
from the application programming language.
| | 04:58 | So in this case, we are looking for
album_id. We can use this same SELECT
| | 05:02 | statement for all of our queries no
matter what album it is that we are
| | 05:06 | querying, and that album_id gets
filled in later when these statement is
| | 05:11 | executed. So here we have the code
prepare; that's typically how the SQL is
| | 05:16 | sent to the database engine, and then
execute is a separate step, and at the
| | 05:21 | execute time the data that replaces
the placeholders is actually entered.
| | 05:27 | So in this case we have the album_id
variable in the database interface that is
| | 05:32 | replaced in here to create the SQL
that will actually be presented to the
| | 05:37 | database engine to execute. So that's
how a query works within the context of
| | 05:43 | an application programming language like PHP.
| | Collapse this transcript |
| The INSERT functions| 00:00 | Now let's take a look at how the insert
statements are used within the context
| | 00:03 | of the PHP code that makes up the CRUD
application. Here we have the function
| | 00:08 | insert_album_sql and this is where the
data is inserted into the album table
| | 00:14 | and here is the SQL right here.
INSERT INTO album (title, artist, label, released),
| | 00:18 | VALUES and then four question marks.
| | 00:22 | Those four question marks, just like
in the select statement, will be replaced
| | 00:27 | with data from the execute call. So
this query is passed to the prepare call,
| | 00:33 | and then execute call actually executes
the SQL. And before it can execute it,
| | 00:39 | it has to put in the data, it has to
fill in these placeholders and here is the
| | 00:43 | data that gets passed to execute for
that purpose. We have, title, artist,
| | 00:47 | label and if we scroll over we have
released and that corresponds to the four
| | 00:53 | columns specified in the INSERT clause,
title, artist, label, released.
| | 00:57 | The VALUES clause has the four question
marks and those question marks get
| | 01:01 | replaced with the data
in the execute call.
| | 01:04 | So that's how that's done and the
track insert is almost the same. Of course,
| | 01:11 | it uses the track table and it has
album_id, which is how it connects to
| | 01:16 | the album table, track_number, title and
duration. And here in the execute call
| | 01:22 | we have album_id, track_number, title and
duration, and those get passed into the
| | 01:28 | execute call which then replaces the
question marks with the data that's
| | 01:32 | supposed to go there. And we have
another INSERT statement that will get executed
| | 01:37 | whenever we insert into the track table.
So that's how the INSERT functions are
| | 01:43 | integrated into the PHP
code for the CRUD application.
| | Collapse this transcript |
| The UPDATE functions| 00:00 | Now we'll look at how and an update
statement is used within the context of
| | 00:03 | the PHP code that makes up the CRUD
application. Here's update_album_SQL and within
| | 00:10 | this function is the update
statement in SQL. Update album, SET title = ?,
| | 00:17 | artist = ?, label = ?, released = ?,
Where id = ?. So all these question marks,
| | 00:26 | we have five of them this time, will
be replaced by data in the execute call,
| | 00:31 | just this we have done before.
| | 00:33 | In this case we have a Where clause
with the id and that's important because
| | 00:37 | update needs to know which records to
update in the database. You'll recall that
| | 00:44 | in update you want to make sure that
you have a Where clause because otherwise
| | 00:48 | it will simply update every row in the
table. So in this case we are isolating
| | 00:53 | a particular row in the table with an
id column. The id column is the primary
| | 00:59 | key for the table.
| | 01:01 | We have the query variable. It's got
the SQL in it. It gets set to the prepare
| | 01:06 | function call for the database and
then the execute call has title, artist,
| | 01:13 | label, released and id and these are in
the same order that the question marks
| | 01:17 | appear within the SQL. title, artist,
label, released and id. So when the query
| | 01:25 | gets executed those variables will
be replaced into the question mark
| | 01:30 | placeholders and the SQL statement
will be updated properly and the database
| | 01:37 | will get updated in the right
row and everything will be happy.
| | 01:41 | update_track_SQL is pretty much the
same. The difference is it's using
| | 01:45 | the track table of course.
| | 01:46 | It has three places for the data and
one for the id and in this case it's
| | 01:52 | the track id and that isolates the
particular row in the track table and it has
| | 01:59 | the data here in the execute track _
number, title, duration and id and those
| | 02:03 | correspond to these four questions marks:
track_number, title, duration and id.
| | 02:08 | So there we have our SQL for the
update functions, in the context of the PHP code
| | 02:13 | that makes up the CRUD application.
| | Collapse this transcript |
| The DELETE functions| 00:00 | Now let's look at the delete functions
within the PHP code that makes up the
| | 00:04 | CRUD application. Here is the delete_
album_SQL function and we have to two
| | 00:09 | queries in here. One is Delete From
track Where album_id = ?. The second is
| | 00:15 | Delete From album WHERE id = ?.
Remember when we looked at the application,
| | 00:21 | when we delete an album we have to
delete all the tracks along with it and
| | 00:25 | that's because the interface for the
CRUD application does not have a way of
| | 00:29 | looking at tracks that are not attached
to an album. So if you would delete
| | 00:34 | the album and not delete the tracks you
would have orphaned data that there's no way to
| | 00:40 | get at it, to look at it or delete it,
and that would just be sitting there
| | 00:44 | taking up space and never getting used.
| | 00:46 | So it's important whenever you
delete an album that you delete all of
| | 00:50 | the associated tracks that are connected
to the album to that particular row in
| | 00:54 | the album table. So here we delete
the tracks first. That way if somehow
| | 01:00 | the operation is interrupted we don't have
any orphaned data. If we were to delete
| | 01:04 | the album first, somehow the operation
were to be interrupted before the tracks
| | 01:08 | got deleted, we would also end up with
orphaned data. So we delete the tracks
| | 01:13 | first with this very simple SQL, Delete
From track Where album__id = ? and then
| | 01:20 | we delete the album from the album from
the album table, Delete From album Where id = ?.
| | 01:26 | So first we prepare and execute
the first query. This is query1.
| | 01:31 | Here's the variable query1 and there is where
it corresponds there. query1 is the one
| | 01:36 | that deletes the tracks and we pass it
the id, which is the album_id, and
| | 01:42 | this Where clause for the delete will
delete just those rows in the track table
| | 01:47 | where the album_id is the album that
we are deleting the tracks for. And then
| | 01:52 | we run the second query over here,
query2, Delete From album Where id = ? and
| | 01:59 | in the execute call. We pass at the
album_id again and that way this question
| | 02:05 | mark here will be replaced with the
album_id and the row will be deleted from
| | 02:09 | the album table.
| | 02:11 | So that's how we delete an album.
Tracks are simpler. In this case,
| | 02:15 | we're deleting a single track at a time.
This is the part of the application where,
| | 02:20 | if you are looking at a particular
album and you have got tracks, you have
| | 02:25 | these Delete buttons, if we press one of
these buttons, it's going to isolate a
| | 02:29 | particular track in the track
table. And here's where that happens.
| | 02:35 | delete_track_SQL, Delete From track
Where id = ?. Now that's the track id.
| | 02:41 | So we have a prepare statement with
the SQL getting passed to it and
| | 02:46 | the execute statement that replaces the
question mark with the track id. So that's
| | 02:52 | how we delete a track and how we delete
an album and its associated tracks with
| | 02:57 | the SQL embedded in the PHP
code for the CRUD application.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | In this course, my goal was to give
you enough knowledge of SQL to perform
| | 00:03 | most of the simple tasks that SQL is
commonly used for. I have shown you how to
| | 00:07 | use the most common SQL statements
and functions and how those features are
| | 00:11 | used manually and from within an
application. I have also provided you with
| | 00:15 | some useful tools in the SID and
CRUD applications and you have the source code
| | 00:19 | for both of them so you can use
them to learn how to create your own
| | 00:22 | useful tools and applications. I have
really enjoyed creating this course for you,
| | 00:26 | and I sincerely hope that all of this is
as useful for you as it has been fun for me.
| | Collapse this transcript |
|
|
