Podcast Beta
Questions and Answers
What does the TRUNCATE TABLE statement do to a table?
Which SQL command is used to add comments to a table or column?
Which statement correctly renames a table in SQL?
Which type of SQL statement is used for defining the structure of a database table?
Signup and view all the answers
Which of the following best describes referential integrity in SQL?
Signup and view all the answers
What is the purpose of the DROP TABLE statement with the PURGE clause?
Signup and view all the answers
What does the ALTER TABLE statement do when used with READ ONLY?
Signup and view all the answers
Which statement accurately describes the FLASHBACK TABLE statement?
Signup and view all the answers
Which of the following is true about renaming a column in a table?
Signup and view all the answers
Who is allowed to rename a table using the RENAME TABLE statement?
Signup and view all the answers
What is the primary purpose of Data Definition Language (DDL)?
Signup and view all the answers
Which of the following data types can store variable-length character data?
Signup and view all the answers
Which constraint type is used to ensure that a column cannot contain NULL values?
Signup and view all the answers
Which SQL command would you use to remove a table from the database?
Signup and view all the answers
What must be done when naming a table or column in SQL?
Signup and view all the answers
What is the maximum length for a column name in SQL?
Signup and view all the answers
When can you create a constraint in SQL?
Signup and view all the answers
Which of the following is a valid reason to use referential integrity in a database?
Signup and view all the answers
What does the FOREIGN KEY constraint do in a database table?
Signup and view all the answers
What happens when the ON DELETE CASCADE option is set on a foreign key?
Signup and view all the answers
Which of the following accurately describes a composite primary key?
Signup and view all the answers
What does the ALTER TABLE statement allow you to do?
Signup and view all the answers
What is a domain constraint in a database?
Signup and view all the answers
Which of the following correctly identifies referential integrity?
Signup and view all the answers
What is the purpose of the DEFAULT keyword in table definitions?
Signup and view all the answers
What does the RENAME COLUMN statement do?
Signup and view all the answers
Study Notes
Data Definition Language (DDL)
- DDL commands create, modify, and delete database structures.
- Key DDL commands: CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT.
- Tables are the basic unit of storage, made up of rows.
Naming Conventions
- Table and column names must start with a letter and be 1-30 characters long.
- Valid characters include A-Z, a-z, 0-9, _, $, #.
- Names cannot be duplicates of other objects owned by the same user.
- Names cannot be Oracle server reserved words.
Data Types
- VARCHAR2(size): Variable-length character data.
- CHAR(size): Fixed-length character data.
- NUMBER(p,s): Variable-length numeric data.
- DATE: Date and time values.
- LONG: Variable-length character data (up to 2 GB).
- CLOB: Character data (up to 4 GB).
- RAW and LONG RAW: Raw binary data.
- BLOB: Binary data (up to 4 GB).
- BFILE: Binary data stored in an external file (up to 4 GB).
- ROWID: Unique address of a row in its table, represented as a base-64 number.
Creating Tables
- Steps for creating a table:
- Identify data types for attributes.
- Determine nullable and unique columns.
- Identify primary and foreign key relationships.
- Define default values.
- Specify constraints on columns.
- Execute the CREATE TABLE command.
Constraints
- Constraints enforce rules at the table level and prevent data inconsistencies.
- Types of constraints:
- NOT NULL: Ensures a column cannot be empty.
- UNIQUE: Ensures a column has unique values.
- PRIMARY KEY: Uniquely identifies each row in a table.
- FOREIGN KEY: Enforces relationships between tables, ensuring foreign key values match primary keys in related tables.
- CHECK: Enforces custom conditions on data values.
CREATE TABLE Syntax
- CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint],...[table_constraint][,...]);
- Column-level constraint syntax: column [CONSTRAINT constraint_name] constraint_type.
- Table-level constraint syntax: column,...[CONSTRAINT constraint_name] constraint_type (column,...).
Primary Keys
- Primary keys uniquely identify records in a table.
- Primary keys can be made up of single or multiple attributes (composite keys).
Referential Integrity
- Referential integrity ensures foreign key values in a child table match primary key values in the parent table.
- Actions to enforce referential integrity:
- Restricting Deletes: Prevent the deletion of primary records if dependent rows exist in the child table.
- Restricting Updates: Prevent updating primary records if dependent rows exist in the child table.
- Restricting Inserts: Prevent inserting records in the child table if the foreign key value doesn't exist in the parent table.
Foreign Keys
- Foreign keys define relationships between tables.
- FOREIGN KEY: Specifies the column in the child table.
- REFERENCES: Identifies the table and column in the parent table.
- ON DELETE CASCADE: Deletes dependent rows in the child table when a row in the parent table is deleted.
- ON DELETE SET NULL: Sets foreign key values to NULL when a row in the parent table is deleted.
ALTER TABLE
- Used to modify table structures, such as adding or dropping columns, constraints, or changing data types.
- Example: ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(manager_id) REFERENCES emp2(employee_id);
RENAME Column
- Allows renaming existing columns in a table.
- Example: ALTER TABLE employee RENAME COLUMN manager to supervisor;
ALTER TABLE Read-Only/Read-Write
- Allows putting a table in read-only mode to prevent DDL or DML changes.
- Use ALTER TABLE table_name READ ONLY; to set read-only mode.
- Use ALTER TABLE table_name READ WRITE; to set read-write mode.
DROP TABLE
- Used to remove tables from a schema.
- Syntax: DROP TABLE table_name [PURGE];
- PURGE: Permanently deletes the table and its data.
FLASHBACK TABLE
- Used to recover tables to a specific point in time.
- Syntax: FLASHBACK TABLE[schema.]table[, [schema.]table ]...TO { TIMESTAMP | SCN } expr [ { ENABLE | DISABLE } TRIGGERS ];
- SCN: System Change Number, a unique identifier for a point in time.
RENAME TABLE
- Allows renaming existing tables.
- Example: RENAME employees to emp;
TRUNCATE TABLE
- Removes all rows from a table, leaving only the table structure.
- Example: TRUNCATE TABLE copy_emp;
COMMENT
- Allows adding comments to tables or columns.
- Example: COMMENT ON TABLE employees IS 'Employee Information';
Conclusion
- This module covered how to define databases, establish referential integrity, and manage tables using SQL DDL commands.
- Key topics included creating tables, adding constraints, modifying table structures, and deleting tables.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Related Documents
Description
This quiz covers the fundamentals of Data Definition Language (DDL) in databases. You'll learn about key DDL commands like CREATE, ALTER, and DROP, as well as naming conventions and various data types used for effective database management. Test your knowledge on the structure and characteristics of SQL databases!
