More

    Summary of Important DDL Commands

    DDL stands for Data Definition Language, and these queries are used to create or manipulate the structure of the database and schemas. 

    CREATE DATABASE

    To create a database

    CREATE DATABASE myDB;

    CREATE TABLE

    To create a table mytable in a DB mydb, with two columns col1 of VARCHAR(20), col2 of INT, where col2 is the Primary Key.

    CREATE TABLE mydb.mytable(

    col1 VARCHAR(20), col2 INT PRIMARY KEY);

    or

    USE mydb;

    CREATE TABLE mytable(

    col1 VARCHAR(20), col2 INT PRIMARY KEY);

    You can also create a new table by copying an old one:

    SELECT * INTO Staff FROM Employees;

    Some databases like MySQL and Oracle uses a different syntax for this:

    CREATE TABLE Staff AS SELECT * FROM Employees;

    Use check constraint along with create table to make sure the values stored in the length field are positive.

    CREATE TABLE Items (Length INTEGER CHECK(length>0)));

    CREATE DISTINCT TYPE

    To create a user defined type

    CREATE DISTINCT TYPE Euro AS DECIMAL(10,2);

    ALTER TABLE

    To modify the type of a column:

    ALTER TABLE mytable MODIFY col1 VARCHAR(20) NOT NULL;

    To rename a column:

    ALTER TABLE tablename RENAME COLUMN old_name TO new_name;

    To add column to a table:

    ALTER TABLE mytable ADD( col2 VARCHAR(50), col3 INT);

    To delete a column:

    ALTER TABLE table_name DROP COLUMN column_name;

    To rename a table:

    ALTER TABLE table_name RENAME TO new_table_name; 

    DROP TABLE

    To delete a table

    DROP TABLE table_name

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox