My name is Matthew Graham and this is
the fourth in a series of six modules.
This is on SQL, the basics.
In this module and the next module,
we will be considering the, SQL,
the main technology that is used
to work with relational databases which
we introduced in the previous module.
In this particular talk,
we're talking about the basics.
Next talk, we will be talking about
some more advanced features of SQL.
SQL stands for Structured Query Language.
The first appeared in 1974 from IBM.
First standard was, was published in 1986.
And there have been updates since,
with the most recent in 2008.
Each variant of the standard is, is
named by SQL and then the year after it.
And, SQL 92 is taken to
be the default standard.
There are different flavors of SQL even
though they are all supposed to be
attached to the standard.
And those will vary from
the different types of
relational database management
system that you are actually using.
So, Microsoft and
Sybase use a flavor called transact SQL.
MySQL uses MySQL, Oracle uses PL/SQL,
and PostgreSQL uses PL/pgSQL.
The call syntax is the same, but in,
the differences are ones of minor syntax,
or maybe additional functionality, but
they have over that default core standard.
The variant we're talking about here
is mainly featured on the, on the core.
So, the fundamental argument
in a SQL statement.
An SQL statement that you sent
to the database to carry out
operation is the SELECT statement.
And you see here the syntax,
SELECT, a, a list of, variables.
A list of,
column identifiers that you want returned.
From a list of tables in the database
where some condition is satisfied,
some predicate, and
then possibly some ordering criteria.
So in our first example, let's consider
that we have a table of stars.
It has columns called name and
constellation.
The table itself is called star.
There's also a some columns in
there of positional information,
right ascension and declination,
and magnitude information.
So the first statement there.
SELECT name, constellation FROM
star WHERE dec > 0 ORDER by vmag,
will return the name and
constellation of each star, for
those stars which are above a declination
of zero and it will present that
information in, ordered in terms
of increasing v magnitude.
If we want all the columns
from from a table.
We can use the asterisk wild card.
So, that second statement there, will
return all the data from the start table,
where the position variable RA
has a value between not and 90.
So in those two where
condition statements they can
see where we can put in
logical constraints on them.
Or if we want to do between a range we
could say RA is greater than zero and
RA is less than 90 but
there is this word between and an,
that we can use in the rest
statement to put those together.
If we wanted,
if in our table of information we have
information that's repeated, and
we just want the unique values of that,
we can use the distinct key word,
before our selection list.
So in this case we're doing
SELECT DISTINCT constellation FROM star
that will return the list of unique
constellation values from that table.
And, in this particular format if we
want this is my SQL variant syntax.
If we just want the top five from a list,
just to make sure that we want to see
the type of data that's being returned, we
would do SELECT name FROM star LIMIT 5 and
ORDER BY vmag because we still
want it in magnitude case.
So that is the select statement.
I would argue that 90% and probably more
than that of the types of queries you
will send to your database
will be of this format.
Now it may be that you have information
that is in two different tables and
you want to construct
a query across those tables.
That is what's known as a join,
or a number of types of joins.
You have inner joins which combine
related rows, and you have
outer joins where the rows do not neither
matching row between the two tables.
Inner joins, the first example here
will return all the information from
a table star.
Joined with a table called stellarTypes.
And then you give it a constraint,
satisfied to, to specify the nature
of the join, so in our star table,
we have a column called stellarType.
In our stellar types table
we have a column ID and
what we're saying is that we're
joining on those two columns.
So that matching values between those two
columns, between those two tables will
be associated with each other
such that that information and
that information will be joined together.
There are two ways of
specifying the syntax for
this particular type of operation and
all these are given.
One formally uses the inner
join construction.
The other one just says
select from this table and
this table where table S alias
S which is the star table.
The stellar type column there has the same
value as the ID column in table T,
where we've defined T to
be the stellar types table.
In the outer join, we can say that we want
all those which match plus all
those either in the first table.
Which won't have a match on the second
table or, all those entries on
the second table which don't
have a match on the first table.
Or, the join plus all
the missing ones from either and
those are either the left outer join or
the right outer join or a full outer join.
So it may be that you want all the
columns, all the rows from one table, and
the matched information from
the other table as well, but
you, you have to leave out blanks
where there aren't matches.
And in that case you would do,
like the left outer join or
the right outer join as appropriate.
In SQL you have aggregate
functions counting, averaging,
getting the minimum value,
the maximum value, or a summation.
This will work on a group of, the group
of information as defined by the way
a predicate or the, the, particular
column name that you've given it.
So if I want to know how many
rows I have in my database table,
I would do select count, and
either, a column name or
I could just use the wild card,
the asterisk from my table name.
That first operation there.
Second operation there will give me the,
the mean value,
the average value,
of a particular column in my data.
I haven't specified a where predicate so
it'll just be all
the values in the column.
I could restrict that with a where
predicate to limit it to a,
smaller set of that column.
If I have multi value data,
I can group by it,
and then apply the aggregate functions
to those individual groupings.
So, say I have five or six different
values for my stellar type column.
I can group all of those of, of type A,
all of those of type B, all of those of
type K, all of those type M, for
example, and then get the minimum and
maximum magnitude values for
those individual groupings.
And that's the use of the third one,
example given there the GROUP BY keyword.
If I'm using aggregate functions,
then instead of using the where clause,
I might use,
there's a no turner construction, which is
the having clause which applies to data.
So, for this last example,
we'll return the, the stellarTypes.
The average magnitude for
each stellar grouping and also how many
are in each of those groupings
where each grouping is
constrained to have objects where the v
magnitude parameter is greater than 14.
Now that is the construction you'd use for
that.
If I want to create a new database or
create a new table in my database,
I would use the CREATE command,
syntaxes CREATE DATABASE,
databaseName or CREATE TABLE,
the name of my table, and
then the column name and the data type for
the column as a list given.
So the example there,
create table star, so
I'm creating a table called star,
and it's going to have four columns,
the column names are name,
ra, dec, and v magnitude.
And the data types associated with each,
with each of
those are variable character string up to
20 in length and then three float values.
Number of data types are supported.
I can have a billion properties, int
properties, real float, double decimal,.
Those can be both, 32-Bit or 64-Bit,
there are a variety of, string data types.
Depending on the length of my,
of my Data type that I want to support.
And then, there are typically, time and
date Data types as well that I can put in.
I can specify further constraints on
particular columns when I'm using my
CREATE TABLE.
I can do CREATE TABLE star.
I say that the,
the name field has to have a value.
It cannot be nullable.
It has to have a value associated with it.
If a value is not given when I'm
putting data into my database, a,
a constraint will be raised.
I can put a default value for
a particular column.
In this case,
in the RA column, I'm saying that it's
taking into full value of, of zero.
And I had to refer
the constraints I can put on.
Particularly useful
feature is those of keys.
Keys identify important columns
in a particular table, and
they will typically be used to identify
a column in one table that I want to link
to a column in another table, that will
be used for the purpose of doing joins.
A table typically,
normally has what's called a primary key.
This is a unique identifier for
a row and automatically has to be null.
When I'm creating my table I can identify
the column or set of columns that I want
to be used to construct the primary key by
using the particular syntax given here.
So in this case I'm saying that,
the name, column will be the primary key.
This is automatically a clustered
index on this particular table,
it means that when the data is
written to disk on the computer,
the DBMS will make sure
that sequential records
according to the ordering of the primary
key are put next to each other.
So you will always get fastest
retrieval from your primary key or
queries against your primary key.
I can associate, two columns in
two different tables with each other by
creating of, a foreign key constraint.
And saying this is a,
a formal constraint and
that will help for, for doing joints,
involving those two tables.
It also means that if I'm
putting data into the database,
then I need to make sure that I'm
putting the relevant information in.
Because it will check for, it will
check keys if they exist between those
tables to make sure those, that values
are put in for, for those, for that data.
If I want to see how many tables
I have in my relational database,
what the table names are,
I do the SHOW TABLES.
If I want to see if I have indexes,
or, in my, on a particular table,
which columns may be index, what type
of indexes there are on those columns,
I'll do the SHOW INDEXES in,
and the table name.
If I've done something wrong,
by putting data in,
or, or, an operation and
I'm getting a warning message.
I can seal up the warnings
on my SHOW WARNINGS.
If I want to,
if I am looking a a particular table and
I cannot remember what the structure of
the table is, there's this describe and
then the table name, operation which will
give me the table structure then, and
I will be able to see what the columns
are, and what the column data types are.
If I want to put data into my database,
I use the insert keyword insert operation,
so the syntax is insert into table
name and then values, and the values.
So if I'm putting all the values for
a particular row in the first operation
inserted my table star the values and
then the the name the values for
each of the columns in the appropriate
order serious RA deck, magnitude.
If I only want to put information in for
a certain number of columns,
then I need to specify what those columns
are, before the value's key word.
So that second operation chain
there Insert into star, and
it's only the name and the v magnitude.
Columns that I'm putting values into in
this case it's coming up as m-0.72 will go
into that.
I can populate a table with
an insert statement as the result of
having done a select operation on
another table or set of tables.
So, there's a query that I'm going to run,
which is doing several
joins on several tables.
I write that as a select statement.
And then I can by making sure
that the select statement is
returning the correct values, use that as
the input into an insert statement and
combine the two and
run that as a single operation.
If I want to do a bulk load
of data into a table I can,
in my SQL use the load data statement.
And the syntax here is as, as specified
and loading data into the file and
then I specify what the path to
the file on my hard drive is INTO TABLE
with the tableName, which is being
already created with appropriate fields.
FIELDS TERMINATED BY delimiter,
that's saying that the fields in
the table that I've got on my hard drive
as separated by a particular syntax.
So, in this case, LOAD DATA INFILE, I've
got a comma separated variable data file,
INTO TABLE star
FIELDS TERMINATED BY commas.
similarly, I can put data out.
I can dump it onto a file in, on my hard
drive, select a star enter out file,
fields terminated by columns from star
where v magnitude is greater than 16.
So that will select everything from
the table star where v magnitude is
greater than 16 and write it into a comma
separated variable file on my hard drive.
Finally, if i want to get rid of,
of, information I can do a delete
statement and delete from
table name where some condition is
specified that where condition.
Again if I want to get rid of
all information in the table but
keep the table empty,
I would do truncate table, table name.
If I want to completely get rid of
the table and all its contents,
then I drop the table tableName.
So, the first example there DELETE FROM
star WHERE name is Canopus,
we'll just get rid of that row.
If I want to get rid of all stars where
the name begins with the letter C and
there's maybe an N in it,
I would do that second syntax.
This is making use of the like
construction in the way I predicate.
Or if I want to specify a range, Delete
from Star where vmag is greater than
zero or dec is less than zero, brilliant
construction or I can use the between
construction to say, I want to get rid
of that information from the Star table,
where the v magnitude is between
a particular set of constraints.
Finally the UPDATE statement, if I want
to update information in my database,
I can do UPDATE tableName.
And then I SET the column value to
a value name WHERE condition is, is met.
So upstates are,
if the v magnitude is wrong by an offset.
I can do a global update by
saying UPDATE start SET v
magnitude equals magnitude plus 0.5.
I can do an individual entry and
kind of change the v magnitude for
the row where the star is called serious.
UPDATE star SET vmag is one, minus 1.47.
Where a name like Sirius or I can do
an update as the result of a join with
another table to maybe update a column
with information from that other table.
This third example, update star,
I'm doing inner join and when you're
doing the update statement, to use,
you need to use the inner join syntax.
You specify the, the two star,
the two table per,
column instead of being used, men say set.
The v magnitude in the star table equal
to the magnitude value in the temp table.
And that is the end of this module.