Archive for December, 2012

MySQL for Beginners Training on Demand course, Day 3

December 14, 2012

It’s been a while since I posted about Day 1 and Day 2 of the MySQL for Beginners online Training on Demand course offered by Oracle (  Yesterday was 90 days from the first posting, so that’s how long you would have had to use this course!

Day 3 is a whirlwind tour of some further aspects of SQL.  Here’s what it covers:

Introduction to Data Manipulation Language (DML) in SQL
– introduces INSERT, REPLACE, UPDATE and DELETE statements and their syntax and uses

Intro to Functions
– string functions, temporal, numeric, control flow and aggregate functions

Intro to Joins
– describes cartesian, inner, and outer joins – their syntax and uses

Intro to Subqueries
– types and placements of subqueries
– how and when to convert to joins

This is intended for beginners, and if you know the material, you might look at the headings and think “I know this” and skip it.  Personally, I always like to review, and try to see what I do and don’t know.  While some of this material was covered at a high level, there was enough detail that I learned some things, and found several stepping-off points to research and learn more.  Here are some of my own notes on things I learned more about:

First, when I read about REPLACE, I did some tests to make sure I understood how it worked.  From the course, we know that “it is possible for a single row to replace more than one old row if the table contains multiple unique constraints.”  I haven’t used REPLACE in too much detail, so I created some tables with a primary key and two other unique, multi-column constraints, and did some playing around.  For example, I used a single-row REPLACE to get three rows replaced by the one new row.  (I also was able refresh my memory on how REPLACE works, by deleting then inserting entire rows, as opposed to UPDATE.)

I enjoyed the introduction to functions – I always find it good to review the built-in functions, and always find some functions I’d forgotten about, never really used, or didn’t even know existed.  I got practice on the syntax and usage of some of the less common functions.

For one of my stepping-off points, I did some more reading on ROUND() and how it deals with numbers ending in .5, depending on whether the datatype is an exact-value datatype, or an approximate-value datatype.  (You may notice a trend in my interests, if you read Day 2.)

Then, in the intro to joins, I found out, for example, that joining with “ON” is not exactly the same as joining with “USING”.  “USING” treats the join column as one, whereas “ON” treats it as two.  Here’s an example.  Table j1 has (1,’j1.1′),(2,’j1.2′) and table j2 has (1,’j2.1′),(2,’j2.2′).

mysql> SELECT * FROM j1 JOIN j2 USING (a);
| a    | b    | b    |
|    1 | j1.1 | j2.1 |
|    2 | j1.2 | j2.2 |
2 rows in set (0.00 sec)

mysql> SELECT * FROM j1 JOIN j2 ON (j1.a=j2.a);
| a    | b    | a    | b    |
|    1 | j1.1 |    1 | j2.1 |
|    2 | j1.2 |    2 | j2.2 |
2 rows in set (0.00 sec)

In the intro to subqueries, I reviewed correlated and uncorrelated subqueries, and where subqueries can be used (in the SELECT, the FROM or the WHERE).  Also, the possible result types (scalar, row, column, and table) and how the different result types can be used differently in queries (eg, for comparisons, in functions, etc).  These are fairly simple concepts, but somehow they keep slipping around in my mind, and reviewing them is very helpful to me.

So, the course covers a wide ground, but also gives enough detail to chew on and learn from.  Having it available for 90 days give a lot of good time for chewing.  I also find it useful in this regard that you get the streaming video of the classroom course, with captioning available, and the pdf student guide and activity guide – sometimes I prefer using one or the other, and for reviewing and “chewing”, the guides are particularly helpful.  This course covers a lot of material, and I’m glad I get to review it!  Stay tuned for day 4…