You can create new table based the following select statement

WHERE emp.deptno=dept.deptno;

The last topic for this section concerns temporary tables, used for storing data on a per-session basis. The data you put in can be seen only by your session, at the end of which the data goes away. Other sessions can use the same temporary table at the same time and your session won’t see their data. Temporary tables are often used in complex queries for which you need to grab a subset of data. They have limited usefulness in conjunction with XSQL pages because of the short duration of an XSQL session. How-ever, there may be some situations in which you’ll find it much easier and efficient to process a subset from a temporary table rather than to repeatedly requery the database or load the data set into memory. Here is how a temporary table is created:

Altering Tables

The alter table statement allows you to change aspects in a table that has already been created. In many cases, you can make changes to a table that already has data in it. This section looks at how the alter table statement works, what it is usually used for, and what it can’t be used for. Before beginning the discussion, it’s important to note that you can modify the storage characteristics of tables. These characteristics won’t be itemized here, but most can be altered at any time. In this section, most of the empha-sis is on working with columns, but it also covers moving a table to a new tablespace. Some discussion of constraints is given, but the “Constraints” section provides the greatest discussion of that topic.

The good news is that adding a basic column is easy. It’s even easy to add a column that has a default value, as follows:

ALTER TABLE emp ADD

If you run this against the emp table, which has data in it, you will get the following error message:

ORA-01758: table must be empty to add mandatory (NOT NULL) column

■■ Create the column without the NOT NULL constraint, add the data, and apply the NOT NULL constraint afterwards.

The second option leads directly to the discussion about altering existing columns. To make these alterations, you would use the modify keyword and a similar expres-sion that you would use if you were creating the column from scratch.

home_zip NOT NULL);

You don’t need to include anything about the data type, since that would remain the same. However, if you want to change the data type, you can. But there are restrictions. Anything goes if the column has no data in it. If you want, you can even change the data type entirely from varchar2 to date, date to number, number to varchar2, and so on. If, however, there is any data in the column, this kind of modification isn’t allowed. Table 8.17 lists the rules for modifying columns that already contain data.