Oracle Certification - SQL Fundamentals I - DESCRIBE
The DESCRIBE Command
With the advent of GUI tools to investigate the database, such as TOAD and Enterprise Manager, the DESCRIBE command has become one I rarely use. However the Oracle exam covers it's usage so I'm going to review it's functionality.
The syntax of the command is:
This will return the tables metadata or definition. The SCHEMA is implied for the schema that you are connected to. So for example connecting to the HR example schema, and issuing the following command gives:
Or using the fuller syntax:
Common Oracle Datatypes
The "Null?" column is self explanatory, but the "Type" column needs more careful examination and will certainly feature in the exam at some point. The common types are as follows:
NUMBER(p,s)
Precision (p) defines the maximum number of digits that the column can hold, including everything before and after to decimal point (if there is one).
Scale (s) defines how many digits can appear to the right of the decimal point.
Note: This is a favourite exam question, i.e. which values can be stored in a NUMBER(8,2) column. The fractional part of the number will be rounded up to the maximum digits allowed, but the integer portion cannot exceed the precision.
CHAR(size)
CHAR is an anachronism from older databases, and contains pre-allocated fixed length character data of size (size). Any unused characters are padded with spaces.
VARCHAR2(length)
VARCHAR2 contain variable length alphanumeric character data, up to a maximum length of Length (length).
DATE
DATE stores a time/date down to seconds. The default date format is DD-MON-YY.
TIMESTAMP
TIMESTAMP is more accurate version of DATE introduced with Oracle 9i, capable of representing time/dates down to fractions of a second (up to 9 digits).
These data-types are the most commonly used. There are of course many others, but for now these cover the main categories that will form part of the exam with reference to the DESCRIBE command.
The next topic is SQL Operators and Expressions.