Create, Sp_Help, Alter

Common SQL Query Commands

CREATE

Creating Tables

The create table statement is used to create a new table. To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table, and a closing parenthesis after the end of the last column definition. Make sure you seperate each column definition with a comma. The table and column names must start with a letter and can be followed by letters, numbers, or underscores - not to exceed a total of 30 characters in length. Do not use any SQL reserved keywords as names for tables or column names (such as "select", "create", "insert", etc).

Data types specify what the type of data can be for that particular column. If a column called "Last_Name", is to be used to hold names, then that particular column should have a "varchar" (variable-length character) data type.

Here are the most common Data types:

char(size)

Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.

varchar(size)

Variable-length character string. Max size is specified in parenthesis.

int

Number value with a max number of column digits specified in parenthesis.

datetime

Date and time value

Examples

Creating a Database

CREATE DATABASE MADHUSCO

(For executing select and press F5)

Creating a Table

CREATE TABLE EMP (ENO INT, ENAME VARCHAR(20), DGN CHAR(18), DATE DATETIME, SAL INT

SP_HELP

To Display the Structure of Table

SP_HELP EMP

ALTER

To Change the Structure of the Table

ALTER TABLE EMP ALTER COLUMN ENAME VARCHAR(25)

1. Adding a Column

Adding a column in a table

ALTER TABLE EMP ADD COMMISSION INT

2. Dropping a column

Deleting a Column from a table

ALTER TABLE EMP DROP COLUMN COMMISSION