SQL Server Tutorial - Part 16

Jul 26, 2024

SQL Server Tutorial - Part 16

Data Definition Language (DDL) in SQL

  • DDL consists of SQL commands used to define the database schema.
  • It is used to create and modify the structure of database objects.
  • Key DDL statements: CREATE, ALTER, DROP, TRUNCATE, COMMENT.

Create Statement

  • Used to create databases or objects (tables, indexes, functions, views, stored procedures, triggers).
  • Syntax: CREATE <object type> <object name> (column definitions).
  • Example:
    CREATE DATABASE testdb;
    CREATE TABLE test_table (id INT);
    

Alter Statement

  • Used to add, modify, drop, or rename columns or tables.
  • Syntax: ALTER TABLE <table name> <alteration>.
  • Example:
    ALTER TABLE test_table ADD address NVARCHAR(500);
    

Drop Statement

  • Used to remove databases, tables, indexes, triggers, constraints.
  • Syntax: DROP <object type> <object name>.
  • Example:
    DROP TABLE test_table;
    

Truncate Statement

  • Used to remove all records from a table, freeing space allocated.
  • Syntax: TRUNCATE TABLE <table name>.
  • Example:
    TRUNCATE TABLE test_table;
    

Comment Statement

  • Used to add comments to SQL queries.
  • Types: Single-line, Multi-line, Inline.

Single-Line Comments

  • Single line comments start with -- and do not execute.
  • Syntax: -- Comment text
  • Example:
    -- This is a single-line comment.
    SELECT * FROM test_table;
    

Multi-Line Comments

  • Multi-line comments start with /* and end with */.
  • Syntax: /* Comment text */
  • Example:
    /* This is a 
    multi-line comment. */
    SELECT * FROM test_table;
    

Inline Comments

  • Inline comments are an extension of multi-line comments and appear within statements.
  • Syntax: /* Comment text */ within a statement
  • Example:
    SELECT /* inline comment */ * FROM test_table;
    

Summary

  • DDL in SQL includes commands to create and modify database schemas and objects.
  • Key DDL statements are CREATE, ALTER, DROP, TRUNCATE, and COMMENT.
  • Each statement plays a crucial role in managing the structure and data within SQL databases.

Next session: Discussing Data Query Language (DQL) and the SELECT statement.