SQL Maintaining Tables

Table Creation

When creating a table, you should normalize the data attributes you want to store in the table. See SQL - Data Base Basics of SQL to find out about normalization.

Decide on a table name, column names, primary key, and data types.

Create a query to track the employees in your company. You need to track employee birthdays, age, and salary. Create a table to store employee name, department, and phone numbers.

Table name: Employees
Column names: EmployeeID, FirstName, LastName, Department, HomePhone, WorkPhone

Next, go through the normalization steps to make sure you don't include data that is better suited for another table.

Create a table:

CREATE TABLE Employees
(
EmployeeID CHAR (50) Primary Key NOT NULL,
FirstName CHAR (50) NOT NULL,
LastName CHAR (50) NOT NULL,
HomePhone CHAR (20),
WorkPhone CHAR (20),
Department CHAR (20)
);

Results:

Employees

FirstName	LastName	EmployeeID	HomePhone	WorkPhone	Department

ALTER TABLE: Lets you modify a table.

DROP TABLE: Lets you delete a table.

DROP COLUMN: Lets you remove a column from a table.

ADD COLUMN: Lets you insert a column into a table.

note: In SQL Server, you use the keyword COLUMN to insert a new column. Only use the ADD keyword in the ALTER statement.

Add a column to the Employee's table (the employee's title). Columns are added to the right of existing columns. Add the Title column to the right of the Department column.

note: Columns are added automatically to the end of a table, after the last column.

ALTER TABLE Employees

ADD COLUMN Title CHAR (20);

Results:

Employees

EmployeeID	FirstName	LastName	HomePhone	WorkPhone	Department	Title
123456789	Jane	Doe	516-644-2668	516-801-9897	BSD	Technical Assistant

Remove a Table. Remove the Employee table.

DROP TABLE Employees;

Create a new table like the one just deleted, but add employee address information.

CREATE TABLE Employees

(

EmployeeID CHAR (50) Primary Key NOT NULL,

FirstName CHAR (50) NOT NULL,

LastName CHAR (50) NOT NULL,

Address CHAR (50),

City CHAR (30),

State CHAR (02),

PostalCode CHAR (05),

HomePhone CHAR (20),

WorkPhone CHAR (20),

Title CHAR (20),

Department CHAR (20)

);

Results:

Employees

EmployeeID	FirstName	LastName	Address	City	State	PostalCode	HomePhone	WorkPhone	Title	Department
123456789	Sharon	Tate	12 Sandy Dr	Belaire	TX	74201	516-664-2668	516-662-8970	Technical Assistant	BSD

http://www.devguru.com/technologies/jetsql/16881

http://www.techonthenet.com/sql/tables/alter_table.php