Friday, February 15, 2013

What are NULL Values in SQL Server

SQL NULL Values
If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a Null Value.

NULL values are treated differently from other values.
NULL is used as a placeholder for unknown or inapplicable values.
Note: It is not possible to compare NULL and Blank; they are not equivalent.

Working with NULL Values in SQL Server :

Look at the following "Customers" table:
EMP_ID
F_Name
L_Name
Dept_ID
Address
Salary
1001
Ravi
Kumar
1
Pune
20000
1002
David
Smith
2
NULL
35000
1003
Victory
Venkatesh
1
Bangalore
50000
1004
Tamatam
Reddy
3
NULL
25000
1005
William
Smith
2
Pune
40000
1006
King
Fisher
6
Bangalore
30000
Suppose that the "Address" column in the "Customers" table is optional. This means that if we insert a record with no value for the "Address" column, the "Address" column will be saved with a NULL value.

Testing for NULL values:

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL
To select only the records with NULL values in the "Address" column.
We will have to use the IS NULL operator:
SELECT Emp_Id,F_Name,L_Name,Address FROM Customers WHERE Address IS NULL
The result-set will look like this:
EMP_ID
F_Name
L_Name
Dept_ID
Address
Salary
1002
David
Smith
2
NULL
35000
1004
Tamatam
Reddy
3
NULL
25000

NOT NULL

We can select only the records with no NULL values in the "Address" column using the NOT NULL operator:
SELECT Emp_Id,F_Name,L_Name,Address FROM Customers
WHERE Address IS NOT NULL

The result-set will look like this:

EMP_ID
F_Name
L_Name
Dept_ID
Address
Salary
1001
Ravi
Kumar
1
Pune
20000
1003
Victory
Venkatesh
1
Bangalore
50000
1005
William
Smith
2
Pune
40000
1006
King
Fisher
6
Bangalore
30000

--------------------------------------------------------------------------------------------------------

How to Create a VIEW in SQL Server

What is a VIEW in SQL Server
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view can be defined as nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.
A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.
Views, which are kind of virtual tables, allow users to do the following:
  • Structure data in a way that users or classes of users find natural or intuitive.
  • Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
  • Summarize data from various tables which can be used to generate reports.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a View and present the data Only if the data were coming from one single table.

Creating a View
Syntax :

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name
WHERE Condition
Note: 
A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

Examples:

If you have the Northwind database you can see that it has several views installed by default.
The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the following SQL Statement:
CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName
FROM Products WHERE Discontinued=No

We can query the view above as follows:

SELECT * FROM [Current Product List]

Another view in the Northwind sample database selects every product in the "Products" table with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS SELECT ProductName,UnitPrice
FROM Products WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

We can query the view above as follows:
SELECT * FROM [Products Above Average Price]

Another view in the Northwind database calculates the total sale for each category in 2010. Note that this view selects its data from another view called "Product Sales for 2010":

CREATE VIEW [Category Sales For 2010] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 2010] GROUP BY CategoryName

We can query the view above as follows:
SELECT * FROM [Category Sales For 2010]

We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":

SELECT * FROM [Category Sales For 2010] WHERE CategoryName='Beverages'

Altering a View:

Syntax :
ALTER VIEW view_name AS SELECT column_name(s)
FROM table_name WHERE condition

Now we want to add the "Category" column to the "Current Product List" view. We will Alter the view with the following SQL:

ALTER VIEW [Current Product List] AS SELECT ProductID,ProductName,Category
FROM Products WHERE Discontinued=No

Droping a View :
You need a way to drop the view if it is no longer needed. The syntax is very simple as given below:
Drop View View_Name

Thanks, Tamatam

How to AUTO INCREMENT a Field Value in SQL

How to use AUTO_INCREMENT or IDENTITY Property in SQL
We would like the value of the primary key field to be created automatically every time when a new record is inserted, we can do this with an auto-increment field in a table.
Syntax for MySQL :
The following SQL statement defines the "C_Id" column to be an auto-increment primary key field in the "Customers" table:
CREATE TABLE Customers
(
C_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (C_Id)
)

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
ALTER TABLE Customers AUTO_INCREMENT=50
To insert a new record into the "Customers" table, we will not have to specify a value for the "C_Id" column (a unique value will be added automatically):
INSERT INTO Customers (FirstName,LastName)
VALUES ('King','Fisher')

The SQL statement above would insert a new record into the "Customers" table. The "C_Id" column would be assigned a unique value. The "FirstName" column would be set to "King" and the "LastName" column would be set to "Fisher".

Syntax for SQL Server :
The following SQL statement defines the "C_Id" column to be an auto-increment primary key field in the "Customers" table:
CREATE TABLE Customers
(
C_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
By default, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.
To specify that the "C_Id" column should start at value 10 and increment by 5, change the identity to IDENTITY(10,5).
To insert a new record into the "Customers" table, we will not have to specify a value for the "C_Id" column (a unique value will be added automatically):
INSERT INTO Customers (FirstName,LastName) VALUES ('King','Fisher')

The SQL statement above would insert a new record into the "Customers" table. The "C_Id" column would be assigned a unique value. The "FirstName" column would be set to "King" and the "LastName" column would be set to "Fisher".

Syntax for Access :
The following SQL statement defines the "C_Id" column to be an auto-increment primary key field in the "Customers" table:
CREATE TABLE Customers
(
C_Id PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record.
To specify that the "C_Id" column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5).
To insert a new record into the "Customers" table, we will not have to specify a value for the "C_Id" column (a unique value will be added automatically):
INSERT INTO Customers (FirstName,LastName) VALUES ('King','Fisher')

The SQL statement above would insert a new record into the "Customers" table. The "C_Id" column would be assigned a unique value. The "FirstName" column would be set to "King" and the "LastName" column would be set to "Fisher".

Syntax for Oracle :
In Oracle the code is a little bit more tricky.
You will have to create an auto-increment field with the sequence object (this object generates a number sequence).
Use the following CREATE SEQUENCE syntax:
CREATE SEQUENCE seq_Customer
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

The code above creates a sequence object called seq_Customer, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

To insert a new record into the "Customers" table, we will have to use the nextval function (this function retrieves the next value from seq_Customer sequence):
INSERT INTO Customers (C_Id,FirstName,LastName) VALUES (seq_Customer.nextval,'King','Fisher')

The SQL statement above would insert a new record into the "Customers" table. The "C_Id" column would be assigned the next number from the seq_Customer sequence. The "FirstName" column would be set to "King" and the "LastName" column would be set to "Fisher".


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

What is the SQL ALTER TABLE Statement

SQL ALTER TABLE Statement
The ALTER TABLE statement is used Modify the structure of a Table. In general we can use Alter Table statement  to add, delete, or modify columns in an existing table.
Syntax:
To add a column in a table, use the following syntax:
ALTER TABLE table_name ADD column_name datatype

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
ALTER TABLE table_name DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:
SQL Server / MS Access:
ALTER TABLE table_name ALTER COLUMN column_name datatype
My SQL / Oracle:
ALTER TABLE table_name MODIFY column_name datatype

To add a column named "DateOfBirth" in the "Customers" table :
ALTER TABLE Customers ADD DateOfBirth date
Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. 
  
Change Data Type :
Now we want to change the data type of the column named "DateofBirth" in the "Customers" table.we use the following SQL statement:

 ALTER TABLE Customers ALTER COLUMN DateOfBirth year

Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two-digit or four-digit format.

DROP COLUMN :
Next, we want to delete the column named "DateOfBirth" in the "Customers" table.
We use the following SQL statement:
ALTER TABLE Customers DROP COLUMN DateOfBirth

Thanks, TAMATAM

How to use DEFAULT Constraint in SQL Server

SQL Server DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column.The default value will be added to all new records, if no other value is specified.

Creating DEFAULT Constraint with CREATE TABLE Statement
The following SQL creates a DEFAULT constraint on the "City" column when the "Customers" table is created:

My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
C_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
City varchar(255) DEFAULT 'Hyderabad'
Gender varchar(50)
)

INSERT Statement for Table with DEFAULT Constraint values
When a Field is declared as DEFAULT , it will take the default value specified , we no need to insert this value in the INSERT INTO statement.So we have to ignore or skip it.This we can do as follows :

INSERT INTO Customers values (123,'Tamatam','Reddy',DEFAULT,'Male')

Here , In the INSERT INTO statement we passed DEFAULT as a value for the DEFAULT Value City.,so that it will take the Default value specified(Hyderabad) in the Table Creation.

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
C_Id int,
OrderDate Date DEFAULT GETDATE()
)

Creating SQL DEFAULT Constraint with ALTER TABLE Statement
To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

SQL Server / MS Access:
ALTER TABLE Customers ALTER COLUMN City SET DEFAULT 'Hyderabad'

MySQL:
ALTER TABLE Customers ALTER City SET DEFAULT 'Hyderabad'

Oracle:
ALTER TABLE Customers MODIFY City DEFAULT 'Hyderabad'

To drop a DEFAULT constraint
SQL Server / Oracle / MS Access:
ALTER TABLE Customers ALTER COLUMN City DROP DEFAULT

MySQL:
ALTER TABLE Customers ALTER City DROP DEFAULT

--------------------------------------------------------------------------------------------------------

How to define CHECK Constraint on a Table in SQL Server

SQL Server CHECK Constraint
The CHECK constraint is used to validate and controls the data that we are entering into a column, on which the constraint is defined.
If you define a CHECK constraint on a single column it validate and controls only values for that specific column.
If you define a CHECK constraint on multiple columns then it will limit the values in certain columns based on values in other columns in the row.

Creating CHECK Constraint with CREATE TABLE Statement

The following SQL creates a CHECK constraint on the "C_Id" column when the "Customers" table is created. The CHECK constraint specifies that the column "C_Id" must only include integers greater than 0.
SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
C_Id int NOT NULL CHECK (C_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MySQL:

CREATE TABLE Customers
(
C_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (C_Id>0)
)

Creating a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
C_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Customer CHECK (C_Id>0 AND City='Sandnes')
)

Creating a CHECK Constraint with ALTER TABLE Statement:

To create a CHECK constraint on the "C_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD CHECK (C_Id>0)

To define a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD CONSTRAINT Chk_Customer 
CHECK (C_Id>0 AND City='Sandnes')

To Drop a CHECK Constraint, use the following SQL Statements:
SQL SERVER / ORACLE / MS ACCESS:
ALTER TABLE Customers DROP CONSTRAINT chk_Customer

MySQL:
ALTER TABLE Customers DROP CHECK chk_Customer

Thanks,Tamatam

How to Create Primary Key and Foreign Key Constraints on a Table in SQL

SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database Table.Primary Keys must contain unique values.

A Primary Key column will not allow duplicates and NULL values. 
Each table should have a Primary Key, and each table can have only One Primary Key. One we create a Table with Primary Key, a default Cluster Index will be created for the Table based on that Column.

Defining PRIMARY KEY Constraint  with CREATE TABLE Statement :

The following SQL statement creates a PRIMARY KEY on the "Customer_Id" column when the "Customers" table is created:

SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
Customer_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MySQL:
CREATE TABLE Customers
(
Customer_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Customer_Id )
)

To define a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
Customer_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT PK_CustomerID PRIMARY KEY (Customer_Id,LastName)
)

Note: In the example above there is only ONE PRIMARY KEY (PK_CustomerID). However, the value of the PK_CustomerID is made up of two columns (Customer_Id and LastName).

It is called the Composite Primary Key.

Defining PRIMARY KEY Constraint with ALTER TABLE Statement :

To create a PRIMARY KEY constraint on the "Customer_Id" column of an existing Table, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Customers ADD PRIMARY KEY (Customer_Id)

To define a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

SQL Server / MySQL / Oracle / MS Access:
ALTER TABLE Customers ADD CONSTRAINT PK_CustomerID PRIMARY KEY (Customer_Id,LastName)

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).


To Drop a PRIMARY KEY Constraint :

SQL Server / Oracle / MS Access:

ALTER TABLE Customers DROP CONSTRAINT pk_CustomerID

MYSQL:

ALTER TABLE Customers DROP PRIMARY KEY
-----------------------------------------------------

SQL FOREIGN KEY Constraint
A FOREIGN KEY is a key used to establish a relationship between two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The Table containing the Foreign Key is called the Child table, and the table containing the candidate key is called the referenced or Parent table. The Foreign Key Column will allow the Duplicate and Null values. No default Index will be created on Foreign Key column, however, we can define if needed.

Defining SQL FOREIGN KEY with CREATE TABLE Statement
The following SQL statement creates a FOREIGN KEY on the "CustomerID" column when the "Orders" table is created:

SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    CustomerID int FOREIGN KEY REFERENCES Customers(Customer_ID)
     )

MySQL:
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID ) REFERENCES Customers(Customer_ID)
    )

To define a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerIDREFERENCES                  Customers(Customer_ID)
    )

Defining a FOREIGN KEY with ALTER TABLE :
To create a FOREIGN KEY constraint on the "CustomerID" column when the "Orders" table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD FOREIGN KEY (CustomerID ) REFERENCES Customers(Customer_ID);

To define a FOREIGN KEY constrain on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD CONSTRAINT FK_CustomerOrder
FOREIGN KEY (CustomerID) REFERENCES Customers(Customer_ID);

To DROP a FOREIGN KEY Constraint :
To drop a FOREIGN KEY constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Orders DROP CONSTRAINT FK_CustomerOrder;

MySQL:
ALTER TABLE Orders DROP FOREIGN KEY FK_CustomerOrder;


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog