Data Definition Language (DDL) Overview
26 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does the TRUNCATE TABLE statement do to a table?

  • It removes all rows from a table, leaving the table empty. (correct)
  • It only deletes specific rows based on conditions.
  • It creates a new table with the same structure.
  • It deletes the table structure along with its data.

    • Which SQL command is used to add comments to a table or column?

  • INSERT COMMENT
  • ALTER TABLE
  • ADD COMMENT
  • COMMENT ON (correct)

    • Which statement correctly renames a table in SQL?

  • CHANGE TABLE table-name TO new-table-name;
  • RENAME TABLE table-name to new-table-name;
  • RENAME table-name TO new-table-name; (correct)
  • ALTER TABLE table-name RENAME TO new-table-name;

    • Which type of SQL statement is used for defining the structure of a database table?

    <p>Data Definition Language (DDL)</p> Signup and view all the answers

    Which of the following best describes referential integrity in SQL?

    <p>Rules that maintain consistency and accuracy of data linked across tables.</p> Signup and view all the answers

    What is the purpose of the DROP TABLE statement with the PURGE clause?

    <p>To remove the table and its data completely</p> Signup and view all the answers

    What does the ALTER TABLE statement do when used with READ ONLY?

    <p>It puts the table in a mode that prevents any changes.</p> Signup and view all the answers

    Which statement accurately describes the FLASHBACK TABLE statement?

    <p>It restores the table to a specified point in time.</p> Signup and view all the answers

    Which of the following is true about renaming a column in a table?

    <p>The RENAME COLUMN syntax is required.</p> Signup and view all the answers

    Who is allowed to rename a table using the RENAME TABLE statement?

    <p>Only the database owner or table owner.</p> Signup and view all the answers

    What is the primary purpose of Data Definition Language (DDL)?

    <p>To define and manage database schemas</p> Signup and view all the answers

    Which of the following data types can store variable-length character data?

    <p>CLOB</p> Signup and view all the answers

    Which constraint type is used to ensure that a column cannot contain NULL values?

    <p>NOT NULL</p> Signup and view all the answers

    Which SQL command would you use to remove a table from the database?

    <p>DROP</p> Signup and view all the answers

    What must be done when naming a table or column in SQL?

    <p>It must start with a letter</p> Signup and view all the answers

    What is the maximum length for a column name in SQL?

    <p>30 characters</p> Signup and view all the answers

    When can you create a constraint in SQL?

    <p>After the table has been created</p> Signup and view all the answers

    Which of the following is a valid reason to use referential integrity in a database?

    <p>To enforce relationships between tables</p> Signup and view all the answers

    What does the FOREIGN KEY constraint do in a database table?

    <p>Facilitates the connection between two tables.</p> Signup and view all the answers

    What happens when the ON DELETE CASCADE option is set on a foreign key?

    <p>Dependent rows in the child table are deleted when the parent row is deleted.</p> Signup and view all the answers

    Which of the following accurately describes a composite primary key?

    <p>A primary key formed by multiple attributes.</p> Signup and view all the answers

    What does the ALTER TABLE statement allow you to do?

    <p>Add or drop constraints but not modify their structure.</p> Signup and view all the answers

    What is a domain constraint in a database?

    <p>A rule that enforces the data type and value range for an attribute.</p> Signup and view all the answers

    Which of the following correctly identifies referential integrity?

    <p>It ensures foreign key values match the corresponding primary key values in related tables.</p> Signup and view all the answers

    What is the purpose of the DEFAULT keyword in table definitions?

    <p>To specify a value that will be assigned to a column when no value is provided.</p> Signup and view all the answers

    What does the RENAME COLUMN statement do?

    <p>Changes the name of an existing column in a table.</p> 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.

    Quiz Team

    Related Documents

    Module 6: Data Definition Language (DDL) PDF

    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!

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser