## Wednesday, 20 February 2013

### Excel RANK Function Syntax and Example

Excel RANK Function
The RANK Function is one of Excel's Statistical Functions, ranks the size of a number compared to other numbers in a list a data.

This function is very useful to give the ranks to the Students,Employees,Organizations based on their performance samples.

Syntax :
= RANK ( Number, Ref, Order )

Number - the cell reference of the number to be ranked.
Ref - the range of cells to use in ranking the Number.

Order - determines whether the Number is ranked in ascending or descending order.
Type a "0" (zero) to rank in descending order (largest to smallest). Type a 1 to rank in ascending order (smallest to largest).

Example:
Suppose in this example if you want to find the rank of a Number 50 of Sample Data in Ascending and Descending order. We can done this as follows.

Here the rank of a Number 50 of Sample Data in Ascending order is = 5
and  the rank of a Number 50 of Sample Data in Descending order is = 4

Thanks,TAMATAM

## Friday, 15 February 2013

### What are SQL NULL Values and Examples

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 0; they are not equivalent.

SQL WORKING WITH NULL VALUES

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.

SQL IS NULL
To select only the records with NULL values in the "Address" column.
We will have to use the IS NULL operator:
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

SQL NOT NULL

We can select only the records with no NULL values in the "Address" column using the NOT NULL operator:
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

SQL Server VIEW
In SQL, a view is a virtual table based on the result-set of an SQL statement.
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

### How to AUTO INCREMENT a Field in SQL

SQL AUTO INCREMENT
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),
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 ('Lars','Monsen')

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 "Lars" and the "LastName" column would be set to "Monsen".

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),
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 ('TPR','Tamatam')

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 "TPR" and the "LastName" column would be set to "Tamatam".

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),
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 ('TPR','Tamatam')

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 "TPR" and the "LastName" column would be set to "Tamatam".

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
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,'TPR','Tamatam')

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 "TPR" and the "LastName" column would be set to "Tamatam".

### What is the SQL ALTER TABLE Statement Syntax and Examples

SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

SQL ALTER 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 example
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 Example
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

### What is the SQL DROP INDEX Statement Syntax and Example

The DROP INDEX Statement
The DROP INDEX statement is used to delete an index in a table.

DROP INDEX Syntax for MS Access:

DROP INDEX index_name ON table_name

DROP INDEX Syntax for MS SQL Server:

DROP INDEX table_name.index_name

DROP INDEX Syntax for DB2/Oracle:

DROP INDEX index_name

DROP INDEX Syntax for MySQL:

ALTER TABLE table_name DROP INDEX index_name

The DROP TABLE Statement

The DROP TABLE Statement is used to Drop a Table
DROP TABLE table_name

### How to Create an Index in SQL Server

SQL Server Index
An index can be created in a table to find data more quickly and efficiently.The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create Indexes on columns(of a table) that will be frequently searched against or use in the Order by Clause.
To Creates an Index on a table which allows Duplicate values :
Syntax :
CREATE INDEX Index_name ON Table_name (Column_name)

To Creates a Unique Index on a table which does not allows Duplicate values :
Syntax:
CREATE UNIQUE INDEX index_name ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.

Create INDEX Example:
The below SQL statement creates an index named "CIndex" on the "LastName" column in the "Customers" table:
CREATE INDEX CIndex ON Customers (LastName)
If you want to create a Composite Index on a combination of columns, you can list the column names within the parentheses, separated by commas.
CREATE INDEX CIndex ON Customers (LastName, FirstName)

### 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),
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

Thanks,TAMATAM