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
To Display the Structure of Table
SP_HELP EMP
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