Java Reference
In-Depth Information
Creating
SQL commands are simple, but using them can be complex. For instance, to create a schema, use the keywords create
schema followed by the name of the new schema. For example:
CREATE SCHEMA tntdb
Yep, that's all there is to it. SQL is not case sensitive (yay!) and very forgiving of extra spaces. For instance,
specifying the command as follows is also correct:
CREATE SCHEMA tntdb
However, SQL is not forgiving when you forget a space. The general rule is at least one space between keywords
and/or parameters. So, the following statements would be incorrect:
CREATESCHEMA tntdb
CREATE SCHEMAtntdb
The SQL create schema statement is simple, but executing it is often not so simple. For instance, to create a
schema you usually need a very high level of DBMS authority. Java programmers do not usually have that level of
authority. In addition, simple DBMS such as MS Access do not allow you to create schemas from an application.
So, if you try to use this command within a Java class, don't be surprised if it does not work. In addition, when you
identify a table in an SQL command, you may or may not have to specify the schema. This depends on how the
database connection was defined (which is partially dependent on the DBMS). For instance, MS Access requires the
programmer to identify the schema in SQL statements regardless of the connection definition. An Oracle connection
defined to a particular table, frees the programmer from specifying the schema in every SQL command. All examples
will specify the schema, but if you are having trouble with the commands, try them without specifying the schema.
All DBMS, however, allow applications to create tables. You can probably guess that the first two keywords are
create table . These keywords are followed by the name of the schema, the new table name, and definitions of each
column/field in the table. When defining a column/field, you need to provide at least a name and the type of data
that will be stored in the column. Depending on the data type specified, you may also be required to specify a field
size. Unfortunately, the Java data types, the SQL data types, and the various DBMS data types are not the same. There
is considerable overlap, however, and sticking with the basic types like char , double , and int will allow your Java
applications to work with all DBMS.
The following is an example of a create table statement that will create a table called “employee” in the
TNTDB schema.
CREATE TABLE tntdb.employee
(empnum char(10),
empname char(25),
street char(15),
city char(10),
state char(2),
zip char(5),
payrate double,
exempts int)
Notice that when defining a field as char (this corresponds to a Java String ) a size was specified. This is not a
requirement. If no size is specified, most DBMSs will create the field with a default length. The problem is that each
DBMS can have a different default, so it is best to specify the length of character fields. Fields defined as double and int
can't have a size because the sizes are predefined. The extra spaces and lines placed between the commands, keywords,
 
Search WWH ::




Custom Search