[thelist] SQL basic questions

[rudy]

>Question 2.
>
>Is there a way to detect if a table is present and has X, Y, and Z
>fields setup? If not, to create the table and/or add/delete fields?

hi ted

you're making it sound harder than it is

  select x,y,z from yourtable

and if you get errors, you need to drop/recreate

>Question 3.
>
> When adding new rows to the database, is there a quick way or
> function to auto assign a new ID # to that row?

yes, in oracle they're sequences, in sql/server they're identity fields, in
msaccess they're autonumbers, and in mysql they're called auto_increments

another difference is that you have to insert NULLs into them

check the docs

> Also what would be the advantage of this? Would this help in
> say linking a table teacher with the ID# of a student?

yes, exactly

these types of fields are called surrogate keys --well, only by us data
modelling geeks -- because they "stand in" for the real keys

they are very useful because, as integers, they are very efficient to index

they are also more compact and less problematic than using composite keys
(which consist of more than one column)


rudy
http://rudy.ca/