Which is the valid CREATE [TABLE statement?
A.
CREATE TABLE emp9$# (emp_no NUMBER(4));
B.
CREATE TABLE 9emp$# (emp_no NUMBER(4));
C.
CREATE TABLE emp*123 (emp_no NUMBER(4));
D.
CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);
Explanation:
Schema Object Naming Rules
Every database object has a name. In a SQL statement, you represent the name of an object with
a quoted identifier or a nonquoted identifier.
A quoted identifier begins and ends with double quotation marks (“). If you name a schema
object using a quoted identifier, then you must use the double quotation marks whenever you
refer to that object.
A nonquoted identifier is not surrounded by any punctuation.
The following list of rules applies to both quoted and nonquoted identifiers unless otherwise
indicated:
Names must be from 1 to 30 bytes long with these exceptions:Names of databases are limited to 8 bytes.
Names of database links can be as long as 128 bytes.
If an identifier includes multiple parts separated by periods, then each attribute can be up to 30 bytes long.
Each period separator, as well as any surrounding double quotation marks, counts as one byte.
For example, suppose you identify a column like this:
“schema”.”table”.”column”
Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted
identifiers can be reserved words, although this is not recommended.
Depending on the Oracle product you plan to use to access a database object, names might be
further restricted by other product-specific reserved words.
The Oracle SQL language contains other words that have special meanings. These words include
datatypes, schema names, function names, the dummy system table DUAL, and keywords (the
uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE,
DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in
specific ways. Therefore, if you use these words as names for objects and object parts, then your
SQL statements may be more difficult to read and may lead to unpredictable results.
In particular, do not use words beginning with SYS_ as schema object names, and do not use the
names of SQL built-in functions for the names of schema objects or user-defined functions.
You should use ASCII characters in database names, global database names, and database link
names, because ASCII characters provide optimal compatibility across different platforms and
operating systems.
Nonquoted identifiers must begin with an alphabetic character (ANSWER B – begins with 9)
from your database character set. Quoted identifiers can begin with any character.
Nonquoted identifiers can contain only alphanumeric characters from your database character set
and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain
periods (.) and “at” signs (@). Oracle strongly discourages you from using $ and # in nonquoted
identifiers.
Quoted identifiers can contain any characters and punctuations marks as well as spaces.
However, neither quoted nor nonquoted identifiers can contain double quotation marks or the
null character (\\0).
Within a namespace, no two objects can have the same name.Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted
identifiers are case sensitive. By enclosing names in double quotation marks, you can give the
following names to different objects in the same namespace:
employees
“employees”
“Employees”
“EMPLOYEES”
Note that Oracle interprets the following names the same, so they cannot be used for different
objects in the same namespace:
employees
EMPLOYEES
“EMPLOYEES”
Columns in the same table or view cannot have the same name. However, columns in different
tables or views can have the same name.
Procedures or functions contained in the same package can have the same name, if their
arguments are not of the same number and datatypes. Creating multiple procedures or functions
with the same name in the same package with different arguments is called overloading the
procedure or function.