5.1 Creating and altering database objects

SQL's Data Definition Language (DDL) lets you create and modify database structures. You'll learn how to make tables, set column types, and add default values. These skills are crucial for building a solid database foundation.

Altering existing database objects is also key. You'll discover how to change tables, rename columns, and even delete entire structures. Understanding these operations helps you adapt your database as needs evolve.

Creating Database Objects

Creating Tables and Databases

Top images from around the web for Creating Tables and Databases

• 

  Chapter 7 The Relational Data Model – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Database Design Create Tables and Relationships with SQL View original

  Is this image relevant?

• 

  mysql - Create Normalized Database Schema in 3rd Normal Form - Stack Overflow View original

  Is this image relevant?

• 

  Chapter 7 The Relational Data Model – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Database Design Create Tables and Relationships with SQL View original

  Is this image relevant?

1 of 3

Top images from around the web for Creating Tables and Databases

• 

  Chapter 7 The Relational Data Model – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Database Design Create Tables and Relationships with SQL View original

  Is this image relevant?

• 

  mysql - Create Normalized Database Schema in 3rd Normal Form - Stack Overflow View original

  Is this image relevant?

• 

  Chapter 7 The Relational Data Model – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Database Design Create Tables and Relationships with SQL View original

  Is this image relevant?

1 of 3

• Use the

  CREATE [TABLE](https://www.fiveableKeyTerm:table)

  statement to define a new table in a database
  • Specify the table name, column names, and data types for each column
  • Example:

    CREATE TABLE employees (id [INT](https://www.fiveableKeyTerm:int), name [VARCHAR](https://www.fiveableKeyTerm:varchar)(50), salary [DECIMAL](https://www.fiveableKeyTerm:decimal)(10,2))

• The

  CREATE DATABASE

  statement creates a new database
  • Specify the name of the database to be created
  • Example:

    CREATE DATABASE company_db

• When creating a table, specify appropriate data types for each column
  • Common data types include INT, VARCHAR, DATE, DECIMAL, and BOOLEAN
  • Choose data types based on the nature of the data stored in each column (numbers, text, dates)

Defining Columns and Default Values

• In the column definition, specify the column name followed by its data type
  • Additional constraints and attributes can be added to each column
  • Example:

    id INT [PRIMARY KEY](https://www.fiveableKeyTerm:Primary_Key), name VARCHAR(50) [NOT NULL](https://www.fiveableKeyTerm:not_null)

• The

  DEFAULT

  keyword allows specifying a default value for a column
  • If no value is provided during an insert, the default value will be used
  • Example:

    CREATE TABLE products (id INT, name VARCHAR(50), price DECIMAL(10,2) DEFAULT 0.00)

• [AUTO_INCREMENT](https://www.fiveableKeyTerm:auto_increment)

  is an attribute used for integer columns to automatically generate sequential numbers
  • Commonly used for primary key columns to ensure unique values
  • Example:

    CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, order_date DATE)

Modifying Database Objects

Altering Tables

• The

  [ALTER TABLE](https://www.fiveableKeyTerm:alter_table)

  statement modifies the structure of an existing table
  • Add, modify, or drop columns
  • Add or remove constraints
  • Example:

    [ALTER](https://www.fiveableKeyTerm:Alter) TABLE employees ADD COLUMN email VARCHAR(100)

• Renaming a column using

  ALTER TABLE

  :
  • Use the

    CHANGE

    keyword followed by the old column name, new column name, and data type
  • Example:

    ALTER TABLE employees CHANGE COLUMN salary emp_salary DECIMAL(10,2)

• Dropping a column using

  ALTER TABLE

  :
  • Use the

    [DROP COLUMN](https://www.fiveableKeyTerm:drop_column)

    keyword followed by the column name
  • Example:

    ALTER TABLE employees DROP COLUMN age

Dropping Tables and Schema

• The

  DROP TABLE

  statement removes an existing table from the database
  • Permanently deletes the table and all its data
  • Example:

    DROP TABLE employees

• Be cautious when using

  DROP TABLE

  as it irreversibly deletes the table and its contents
  • Use with care and ensure proper backups are in place
• A database schema represents the structure and organization of the database
  • Includes table definitions, relationships, constraints, and other database objects
• Modifying the schema involves altering tables, adding or removing constraints, and updating relationships between tables
  • Schema changes should be carefully planned and executed to maintain data integrity and consistency