Normal base
tables are created and worked with. They are permanent and stick around until
they are dropped. You can generate a new table with a CREATE TABLE statement
(DDL). But you can also create a table using the output from a SQL query. To do
this you issue a SELECT INTO myNewTable. This will result
in creation of table myNewTable. Its contents will be the resulting values and
data type from the SELECT query.
There are
also tables you can generate on the fly called derived tables. These are also
created from SELECT statements that are enclosed within parentheses in the FROM
clause. You need to assign an alias for the derived table. The table only
exists while the outer query is executing. In Oracle, they call these inline
views. There are some limitations on the type of SQL you can put into a derived
table. For example, you cannot have an ORDER BY.
When making changes to data within tables, you
can group operations into units called transactions. You can explicitly control
when these groups are created and when their changes out made permanent. You
start this up with a BEGIN TRANSACTION. Then you issue DML statements such as
INSERT, UPDATE, and/or DELETE. When done, you can COMMIT TRANSACITON to make
all changes final. Or you can ROLLBACK to undo everything in the transaction.
