Let’s create a new database user account to manipulate tables and data in the database. To create a new user, you must be logged in under a database administrator account, in our case, the default system account created during database installation.

This command creates a new user jim with the password mypassword . The default tablespace is users and the allocated space is unlimited.

In real life, a database administrator creates custom roles and fine tunes privileges for each role. However, for the purpose of our tutorial, we can use a predefined role, such as CONNECT . For more information about roles and privileges, see Oracle Database Security Guide.

A tablespace is a logical database storage unit of any Oracle database. In fact, all of the database’s data is stored in tablespaces. You create tables within allocated tablespaces. If a default tablespace is not explicitly assigned to a user, the system tablespace is used by default (it is better to avoid this situation)

For more information about the tablespace concept, see Oracle FAQ: Tablespace

There are several ways to create a table in the database through the NetBeans IDE. For example, you can run an SQL file (right-click the file and choose Run File), execute an SQL Command (right-click the connection node and choose Execute Command) or use the Create Table dialog box (right-click the Tables node and choose Create Table). In this exercise you will recreate a table by using the structure of another table.

In this example, you want the user jim to create a copy of the Departments table in his schema by recreating the table from the hr database. Before you create the table you will need to disconnect from the server and log in as user jim .

When you created the user jim , the Select privilege was limited to the Departments table.

When you click OK, the new DEPARTMENTS table is created and appears under the JIM schema node. If you right-click the table node and choose View Data you will see that the table is empty.

If you want to copy the data from the original Departments table to the new table, you can enter the data manually in the table editor or run an SQL script on the new table to populate the table.

For example, you can enter the following values taken from the original DEPARTMENTS table.

To populate the table using an SQL script, perform the following steps.

The following script will populate the first row of the new table with the data from the original table.

You can retrieve the SQL script for populating the table from the original table by performing the following steps.

You can then copy the script and modify it as necessary to insert the data in your table.

See Tips for more information about working in the SQL Editor.

To work with table data, you can make use of the SQL Editor in NetBeans IDE. By running SQL queries, you can add, modify and delete data maintained in database structures.

At first, create the second table named Locations in the jim schema (stay logged under the jim’s user account). This time, we will simply run the ready-to-use SQL file in the IDE:

To open the Favorites window, click Window > Favorites in the main menu (press Ctrl-3). The USER_HOME directory is listed in the Favorites window by default.

You can see that the Locations table with data was added to the JIM schema.

In our case, we will use a simple "natural join", because both tables have the same "location_id" column that holds values of the same data type. This join selects only the rows that have equal values in the matching location_id column.

Open the SQL Command window (right-click the Tables node under the JIM schema and choose Execute Command), enter the following SQL statement, and click the Run SQL icon.

This SQL query returns the rows from the Departments table whose location_id values are equal to the values in the matching column in the Locations table, with the results being ordered by the Department name. Note that you cannot insert new records directly in the results of this query, as you could do in the representation of a single table.

You can save the SQL join query as a View (right-click the View node and choose Create View) and run it conveniently whenever you want. For this, the database user should be granted the privilege to Create View that our sample user does not have. You can log in under the system account, grant jim the Create View privilege (with this SQL statement: "grant create view to jim;") and try creating your own view.

If you were following this tutorial, you already used the capabilities of the NetBeans IDE SQL Editor. Here we list several other capabilities of the NetBeans IDE SQL Editor that might be useful to you.