SQL ALTER TABLE Statement

Today I am going to talk about how to ALTER table in DBMS (Database Management System). It doesn't matter what is  your DBMS I'll do some SQL queries which will match to your requirement.

Simply, statement can be used to do alterations to an existing database table. We can identify those alterations as below;

• Add new column.
• Modify existing column. (Column data type)
• Drop existing column.
• Add Constraints.
• Drop Constraints.

For all demonstrations below please assume that there are two tables, Employee(EmpID, EmpName) and Department(DepID, DepName). Every employee is belong to one of department.

Add New Column

Sometimes we may need to add columns to existing data tables. To do this we have to alter the existing data table. Using SQL queries we can do it in a simple way.

Using above query you can add a column named "DepID" to the table "Employee", but for existing records value of "DepID" will be NULL. 

If you want to set a default value for existing records you can use the query below

Modify Existing Column

You can change data type of an existing column as below;

Drop Existing Column

To delete (drop) column in a table you can user following SQL queries as your DBMS.

Add Constraints

You also can add constraints (Primary Key, Foreign Key, Indexes...)using this statement. Here I am adding foreign key constraint to Employee table linking Department table.

Drop Existing Constraints

This is how you can delete (drop) existing constraints.

Read More

SQL Between Operator

Image from => http://tapoueh.org/images/sql-logo.png

Sometimes you may need to select values thin a range in your query. SQL has a powerful operator for this kind of situations. It is the "BETWEEN" Operator. it has a very simple syntax; let discuss it with an example.

Example 1

Above query will return all customer data whose have ID is in between 1 and 100.

Example 2

This query will return all customer data whose age is not in between 45 and 50.

Example 3

Query has use another condition with between operator. It will return all customer data whose ID is in between 1 and 100 and age is 45.

Similarly you can use Numbers, text or dates as values for between operator.

Example 4

Here query will return all the products with PRODUCTNAME beginning with any of letter between 'A' and 'D'.

Example 5

Finally values used in the between operator has to be same data type as the column data type used to select range. And this operator is inclusive operator so it select the range including the given values.

You can get similar functionality by using >= and <= operator instead of between operator as follows;

Example 6

Read More

SQL Foreign Key Constraint

When we want to establish a relationship between two tables, eventually we have to do it with a help of Foreign Key. It refers one table to another tables Primary Key. To demonstrate it we will need two tables. Its see how to relationship between those two table;

It is so simple, but make sure to name your constants otherwise you may be in trouble if you want to drop constraints.

You can add a Foreign key constraint to a table by Altering it;

How to drop constraint?

Read More

How to get Server Date In MSSQL Server, Oracle and MySQL

I always forget how to retrieve current server date no matter what DBMS environment I am using. It just a simple SQL function but it doesn't resides in my mind. Anyway lets see how do it in MSSQL Server, Oracle and MySQL DBMSs;

Result in MSSQL Server is shown as below; 

MSSQL Server Result

 

Read More

SQL Primary Key Constraint

 

Although this is not a good way to start a blog, I thought that I would document the scenarios which I have to again and again Google. So it would help to and also some one who Google for help. 

Today I researched about Primary Key constraint in database tables, I know that all of you are very much aware about it, but technically I found that when you want to "Drop" a primary key it is much difficult if you haven't named that constraint. If you have name it you can "Drop" it using that name, but if you didn't name it then it will be a crisis if you are in a situation that you need to "Drop" that constraint. We'll discuss about it later.

First question; what is primary key constraint? Answer is so simple, it is the constraint which identifies a row in a database uniquely. So the value in primary key should be unique, it cannot be duplicated and most importantly it cannot contain NULL values. And at last a table can have only one primary key, but it can be a combination of several columns in that table to make it unique. 

Lets see my recommended way of creating a primary key;

It is very easy to do that; the main concern here is to name your constant using what ever unique name, but if it is something relevant to your table it will helpful to remember constraint later. No worries if you have forgot it, simply you can find it using a simple query as mentioned below;

It will list all the details about your given table, so definitely it includes details about the constraints of the table.

You may add Primary key constraint to your table by Altering it;
How to drop a primary key;

As you seen if you name your primary key constraint it is very easy to drop it whenever you want using its name. If you want to drop unnamed primary key constraint how do it? lets find out.

You may need to replace table name as relevant to you.
I hope that I did something helpful to save your time and effort. If you have any questions please mention them in a comment.

Read More