OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P9

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P9: There is high demand for professionals in the information technology (IT) industry, andOracle certifications are the hottest credential in the database world. You have made theright decision to pursue certification, because being Oracle Database 11g certified will giveyou a distinct advantage in this highly competitive market.
Nội dung trích xuất từ tài liệu:
OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P9 Summary 331Now you’ll create the CITY table. Notice the foreign key constraint is created with the ONDELETE 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>SummaryTables are the basic structure of data storage. A table comprises columns and rows, asin a spreadsheet. Each column has a characteristic that restricts and verifies the data itstores. You can use several datatypes to define columns. CHAR, NCHAR, VARCHAR2,CLOB, and NCLOB are the character datatypes. BLOB, BFILE, and RAW are the binarydatatypes. DATE, TIMESTAMP, and INTERVAL are the date datatypes. TIMESTAMPdatatypes can store the time-zone information also. NUMBER, BINARY_FLOAT, andBINARY_DOUBLE are the numeric datatypes. You use the CREATE TABLE statement to create a new table. A table should have at leastone column, and a datatype should be assigned to the column. The table name and columnname 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 TABLE…AS SELECT…(CTAS) statement. You can add, modify, or drop columns from an existing table using theALTER TABLE statement. Constraints are created in the database to enforce a business rule and to specify relation-ships between various tables. NOT NULL constraints can be defined only with a column defi-nition and are used to prevent NULL values (an absence of data). Check constraints are usedto verify whether the data conforms to certain conditions. Primary key constraints uniquelyidentify a row in the table. There can be only one primary key for a table, and the columnsin the primary key cannot have NULL values. A unique key is similar to a primary key, butyou can have more than one unique key in a table, as well as NULL values in the unique keycolumns. You can enable and disable constraints using the ALTER TABLE statement. The constraintcan be in four different states. ENABLE VALIDATE is the default state.332 Chapter 6 N Creating Tables and ConstraintsExam EssentialsUnderstand datatypes. Know each datatype’s limitations and accepted values. Concen-trate on the new TIMESTAMP and INTERVAL datatypes.Know how date arithmetic works. Know the resulting datatype of date arithmetic, espe-cially between INTERVAL and DATE datatypes.Know how to modify column characteristics. Understand how to change datatypes, addand modify constraints, and make other modifications.Understand the rules associated with changing datatype definitions of columns with rowsin a table. When the table is not empty, you can change a datatype only from CHAR toVARCHAR2, and vice versa. Reducing the length is allowed only if the existing data fits inthe new length specified.Understand the DEFAULT clause on the column definition. The DEFAULT clause provides avalue for the column if the INSERT statement omits a value for the column. When modify-ing a column to have default values, the existing rows with NULL values in the table are notupdated with the default value.Know the actions permitted on read-only tables Understand the various actions that arepermitted on a read-only table. Any operation that changes the data in the table is notallowed on a read-only table. Most DDL statements are allowed, including DROP TABLE.Understand constraints. Know the difference between a primary key and a unique keyconstraint, and understand how to use a nonunique index for primary/unique keys.Know how a constraint can be defined. You can use the CREATE TABLE or ALTER TABLEstatement to define a constraint on the table. Review Questions 333Review Questions1. The STATE table has the following constraints (the constraint status is shown in parentheses): Primary key pk_state (enabled) Foreign key COUNTRY table: fk_state (enabled) Check constraint ck_cnt_code (disabled) Check constraint ck_st_code (enabled) NOT NULL constraint nn_st_name (enabled) You execute the following SQL: CREATE TABLE STATE_NEW AS SELECT * FROM STATE; How many constraints will there ...