To the best of my knowledge, Oracle 10g does not support multiple inserts with the syntax JohnO has used above.
Here is the Syntax for an Oracle 10g Insert Statement.
There is way to insert multiple values with one select statement in Oracle 10g; although it seems more like a hack. Use an insert select statement as shown below.
INSERT INTO users.table (field1, field2)
(SELECT 'test value', '1' FROM dual
UNION SELECT 'test value', '2' FROM dual
UNION SELECT 'test value', '3' FROM dual);
About the naming conventions... Aliases help keep complex queries more readable, regardless of the table names.
"SELECT author AS postauthor FROM posts"
Great article John! I've even posted a link to my blog to this article because it is a great introduction to databases.
Might there be a part 2 to look forward to?
I have a feeling that your putting up this article is in some way related to your final question on this article.
Wesley, you astute reader, you! Yes, it partially came out of the observation that introductory topics seem to generate more traffic. But more often than not my topics come from talking to friends or co-workers or poring over comments on previous posts. Issues that they run into when jumping into something become inspiration for stuff to write about (hey, it's a difficult task to come up with interesting stuff to write about all the time). And as DD has requested, a part two just might be in order. :)
On the naming conventions.... I use something similar:
1. All table names are plural
2. I create a 2 o 3 letter prefix for each table based on the name wich then is added to the column names using an underscore.
3. When using a foreign key you also add the name of the original table (that way you know where that key is coming from).
4. Camel Base names (to make it easy to read).
So if I have a table called 'Users' the columns names would be usr_id, usr_fullName, usr_email.
Then the Posts table coul have values like pst_id, pst_title, pst_body, pst_usrId_writer
This way you know exactly what each values means and where it comes from.
You can also do joins solely in the WHERE clause, which I find more fluid. Using the blog example above, you might have a user table:
USERS id username
And a posts table:
POSTS id userid subject content
To join the two we could do the following:
SELECT u.username, p.subject, p.content FROM users u, posts p WHERE u.id = p.userid;
Which would leave us with a resultset showing username, subject and content for each post in the blog.
You may also notice how you can create aliases for table names to save typing the full table name over and over again in the select section of the query.
Jonathan, you write:
"A blog has articles and comments. Both an article and a comment would each be an object. AUTHORS and COMMENTS would be our tables."
Don't you mean ARTICLES and COMMENTS would be our tables?
Sorry if you find posts correcting your articles annoying, feel free to delete this.
This is going to be a great article for a lot of people. Thanks for sharing.
Iota: I'll actually be going into some depth as to why I use the INNER JOIN syntax in a followup post. :)
Geoff: I've fixed the mistake. I love posts pointing out any errors I've made. Every little bit helps!
Indeed, interesting article Jonathan. I'm looking forward to the next one too ;)
Re: Iota
The join using a list of tables (and no JOIN) is an implicit CROSS JOIN.
Snook calls these "Crazy Joins" in the next article. Could become very inefficient, I guess.
JohnO: not all database servers support that multi-insert format (I kept the article database generic) -- MySQL may be the only one (if anyone is aware of Oracle or Postgresql or SQLite support, I'd be glad to hear it)