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

How to Initialize Objects By Using Object Initializer (C#)

 

 There are many ways to initialize an object. But using this method you can initialize your object from one line of code. So this is efficient and easy for the developer. No need to write repeating object names to set the properties of the initializing object. As my experience this way it is easy to track and trace errors of your code. No more talking lets see how to do it;

I will you a class called student, then I will initialize it in the later code;

Read More

How to Format a DateTime Object Using ToString() Method

It is very important that how we display dates and time to the user. Fortunately .Net framework facilitates us to convert DateTime objects to its equivalent string using a specific format. To do this we can use DateTime objects' ToString() method. The format should be mention within the brackets of the method. 

There are lot of formats we can use to format DateTime objects, here I have mention some commonly useful formats. First we will see standard formats;

Then some custom formats;

Above examples are working on and above .Net Framework 1.0.

Read More

What is the CSS Equivalent for align = "center"

You may already know or you may not that some HTML attributes are now outdated. Since we have a powerful styling language called CSS some HTML attributes are not in use now. The most important thing is most updated web browsers may not render those HTML attributes properly. 

If you have a HTML page with old markup, it good to update it with proper CSS styling. Here I am mentioning a equivalent for a old HTML attribute it is "align"

Read More

How to Close or Exit From a WPF Application Programmatically Correctly

I recently figured out that a WPF Application cannot close as we suppose to do in windows applications. So I Googled it, I have found several ways to do it and I am in a mood to share those with you guys.

The best and proper way to close WPF application is by calling Shutdown() method;

Calling it may give you several advantages, First is that method will call "Exit" event. Using "Exit" event you can handle the necessary processing that suppose to be done right before the application stop running. You just do not have to worry about the windows you opened and hide before this method is called. All those windows will be closed automatically.

And also this method would call implicitly if you have set the "ShutdownMode" to "OnLastWindowClose", "OnMainWindowClose" or by ending the session.

This has support from framework 3.0 and above.

Anyway do not use following to close your application;

 
Because it will kill your application right that moment and will not call the "Exit" event. I think that is not a good idea.

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