OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P9: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market. | Summary 331 Now you ll create the CITY table. Notice the foreign key constraint is created with the ON DELETE CASCADE clause SQL CREATE TABLE city 2 city_code VARCHAR2 6 3 city_name VARCHAR2 40 NOT NULL 4 country_code NUMBER 4 NOT NULL 5 state_code VARCHAR2 3 NOT NULL 6 population NUMBER 15 7 created DATE DEFAULT SYSDATE 8 constraint pk_city PRIMARY KEY city_code 9 constraint fk_cigy FOREIGN KEY 10 country_code state_code 11 REFERENCES state ON DELETE CASCADE Table created. SQL Summary Tables are the basic structure of data storage. A table comprises columns and rows as in a spreadsheet. Each column has a characteristic that restricts and verifies the data it stores. You can use several datatypes to define columns. CHAR NCHAR VARCHAR2 CLOB and NCLOB are the character datatypes. BLOB BFILE and RAW are the binary datatypes. DATE TIMESTAMP and INTERVAL are the date datatypes. TIMESTAMP datatypes can store the time-zone information also. NUMBER BINARY_FLOAT and BINARY_DOUBLE are the numeric datatypes. You use the CREATE TABLE statement to create a new table. A table should have at least one column and a datatype should be assigned to the column. The table name and column name should begin with a letter and can contain letters numbers or special characters. You can create a new table from an existing table using the CREATE SELECT. CTAS statement. You can add modify or drop columns from an existing table using the ALTER TABLE statement. Constraints are created in the database to enforce a business rule and to specify relationships between various tables. NOT NULL constraints can be defined only with a column definition and are used to prevent NULL values an absence of data . Check constraints are used to verify whether the data conforms to certain conditions. Primary key constraints uniquely identify a row in the table. There can be only one primary key for a table and the columns in the primary key cannot have NULL values. A unique key is similar to a primary .
