SQL DDL: CREATE TABLE and ALTER TABLE
14 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 is the difference between a clustered index and a non-clustered index?

  • A clustered index physically reorders table data to match the index, while a non-clustered index does not (correct)
  • A clustered index is used for unique indexes, while a non-clustered index is used for non-unique indexes
  • A clustered index is used for indexes on large tables, while a non-clustered index is used for indexes on small tables
  • A clustered index is used for composite indexes, while a non-clustered index is used for single-column indexes

    • What is the purpose of the PRIMARY KEY constraint in a table?

  • To uniquely identify each row in a table (correct)
  • To enforce a condition on a column or set of columns
  • To specify a default value for a column
  • To create an index on a column

    • What is the effect of dropping a table in a database?

  • The table is deleted, but the data is preserved
  • The table is deleted, and all its data is permanently lost (correct)
  • The table is renamed, and all its data is preserved
  • The table is truncated, and all its data is deleted

    • What is the purpose of the ALTER TABLE statement?

    <p>To modify the structure of an existing table</p> Signup and view all the answers

    What is the effect of creating an index on a column?

    <p>It improves query performance</p> Signup and view all the answers

    What is the purpose of the CHECK constraint?

    <p>To enforce a condition on a column or set of columns</p> Signup and view all the answers

    What is the syntax to create a composite index on two columns?

    <p>CREATE INDEX idx_name ON table_name (column1, column2);</p> Signup and view all the answers

    What is the purpose of the FOREIGN KEY constraint?

    <p>To link to the PRIMARY KEY of another table</p> Signup and view all the answers

    What is the primary purpose of the DROP TABLE statement?

    <p>To delete an existing table from a database</p> Signup and view all the answers

    Which of the following data types is used to store dates and times in a table?

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

    What is the purpose of the PRIMARY KEY constraint in a table?

    <p>To ensure that each row has a unique value</p> Signup and view all the answers

    Which statement is used to add a new column to an existing table?

    <p>ALTER TABLE table_name ADD column_name datatype;</p> Signup and view all the answers

    What is the purpose of the FOREIGN KEY constraint in a table?

    <p>To ensure that a value in a column matches a value in another table</p> Signup and view all the answers

    Which of the following is an example of a basic syntax for creating a table?

    <p>CREATE TABLE table_name (column1 datatype, column2 datatype,...);</p> Signup and view all the answers

    Study Notes

    SQL DDL

    CREATE TABLE

    • Used to create a new table in a database
    • Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
    • Example: CREATE TABLE customers (id INT, name VARCHAR(255), email VARCHAR(255));
    • Can specify constraints, such as PRIMARY KEY, FOREIGN KEY, and CHECK
    • Can also specify default values and auto-incrementing columns

    ALTER TABLE

    • Used to modify the structure of an existing table
    • Syntax: ALTER TABLE table_name ADD|DROP|MODIFY column_name datatype;
    • Example: ALTER TABLE customers ADD address VARCHAR(255);
    • Can add or remove columns, modify data types, and change constraints
    • Can also rename columns or tables

    DROP TABLE

    • Used to delete a table from a database
    • Syntax: DROP TABLE table_name;
    • Example: DROP TABLE customers;
    • Deletes the table and all its data
    • Be cautious when using, as it permanently deletes data

    INDEXING

    • Used to improve query performance by creating an index on one or more columns
    • Syntax: CREATE INDEX index_name ON table_name (column_name);
    • Example: CREATE INDEX idx_name ON customers (name);
    • Types of indexes:
      • Unique index: ensures uniqueness of values in a column
      • Composite index: indexes multiple columns
      • Clustered index: physically reorders table data to match index
    • Indexes can be created on columns used in WHERE, JOIN, and ORDER BY clauses

    CONSTRAINTS

    • Used to enforce data integrity and consistency
    • Types of constraints:
      • PRIMARY KEY: uniquely identifies each row in a table
      • FOREIGN KEY: links to the PRIMARY KEY of another table
      • UNIQUE: ensures uniqueness of values in a column
      • CHECK: enforces a condition on a column or set of columns
      • DEFAULT: sets a default value for a column
    • Syntax: CREATE TABLE table_name (column_name datatype CONSTRAINT constraint_name);
    • Example: CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(255));
    • Can be used to enforce business rules and maintain data consistency

    SQL DDL

    Creating Tables

    • The CREATE TABLE statement is used to create a new table in a database
    • The syntax for creating a table is CREATE TABLE table_name (column1 datatype, column2 datatype,...);
    • A table can be created with columns that have different data types, such as integers and strings
    • Constraints can be specified during table creation, including PRIMARY KEY, FOREIGN KEY, and CHECK
    • Default values and auto-incrementing columns can also be specified during table creation

    Modifying Tables

    • The ALTER TABLE statement is used to modify the structure of an existing table
    • The syntax for modifying a table is ALTER TABLE table_name ADD|DROP|MODIFY column_name datatype;
    • Columns can be added to or removed from an existing table
    • The data type of an existing column can be modified
    • Constraints can be added or removed from an existing table
    • Columns or tables can be renamed using the ALTER TABLE statement

    Deleting Tables

    • The DROP TABLE statement is used to delete a table from a database
    • The syntax for deleting a table is DROP TABLE table_name;
    • Deleting a table also deletes all the data stored in the table
    • The DROP TABLE statement should be used with caution, as it permanently deletes data

    Indexing

    • An index is a data structure that improves query performance
    • Indexes can be created on one or more columns of a table
    • The syntax for creating an index is CREATE INDEX index_name ON table_name (column_name);
    • Unique indexes ensure that all values in a column are unique
    • Composite indexes are created on multiple columns
    • Clustered indexes physically reorder table data to match the index
    • Indexes can be created on columns used in WHERE, JOIN, and ORDER BY clauses

    Constraints

    • Constraints are used to enforce data integrity and consistency
    • PRIMARY KEY constraints uniquely identify each row in a table
    • FOREIGN KEY constraints link to the PRIMARY KEY of another table
    • UNIQUE constraints ensure that all values in a column are unique
    • CHECK constraints enforce a condition on a column or set of columns
    • DEFAULT constraints set a default value for a column
    • Constraints can be used to enforce business rules and maintain data consistency

    SQL DDL

    CREATE TABLE

    • Creates a new table in a database
    • Basic syntax: CREATE TABLE table_name (column1 datatype, column2 datatype,...)
    • Includes column definitions with datatypes (e.g., INT, VARCHAR, DATE, TIME, etc.)
    • Example: CREATE TABLE Customers (CustomerID INT, CustomerName VARCHAR(255), ContactName VARCHAR(255), Address VARCHAR(255), City VARCHAR(255), PostalCode VARCHAR(255), Country VARCHAR(255));

    ALTER TABLE

    • Modifies the structure of an existing table
    • Used for:
      • Adding new columns
      • Deleting existing columns
      • Modifying column definitions
    • Basic syntax: ALTER TABLE table_name ADD|DROP|MODIFY column_name datatype
    • Examples:
      • Adding a new column: ALTER TABLE Customers ADD Email VARCHAR(255);
      • Dropping an existing column: ALTER TABLE Customers DROP COLUMN ContactName;
      • Modifying a column definition: ALTER TABLE Customers MODIFY CustomerName VARCHAR(300);

    DROP TABLE

    • Deletes an existing table from a database
    • Basic syntax: DROP TABLE table_name;
    • Example: DROP TABLE Customers;
    • Note: Use with caution, as it permanently deletes the table and its data.

    CONSTRAINTS

    • Define rules for data in a table
    • Types of constraints:
      • PRIMARY KEY: ensures unique values in each row
      • UNIQUE: ensures unique values in a column
      • FOREIGN KEY: ensures values match another table
      • CHECK: ensures conditions are met before inserting or updating
      • NOT NULL: ensures columns cannot be left blank
    • Basic syntax varies by type:
      • PRIMARY KEY: CREATE TABLE table_name (column1 datatype PRIMARY KEY,...);
      • UNIQUE: CREATE TABLE table_name (column1 datatype UNIQUE,...);
      • FOREIGN KEY: CREATE TABLE table_name (column1 datatype, FOREIGN KEY (column1) REFERENCES other_table_name(column1));
      • CHECK: CREATE TABLE table_name (column1 datatype CHECK (condition),...);
      • NOT NULL: CREATE TABLE table_name (column1 datatype NOT NULL,...);
    • Examples:
      • PRIMARY KEY: CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CustomerName VARCHAR(255),...);
      • FOREIGN KEY: CREATE TABLE Orders (OrderID INT, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Description

    Quiz on SQL Data Definition Language, covering CREATE TABLE and ALTER TABLE statements, including syntax, examples, and constraints.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser