## 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
MBA(Finance),BSc(Statistics).

## 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:
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

SQL IS NOT NULL

How do we select only the records with no NULL values in the "Address" column?
We will have to use the IS 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

### What is the SQL CREATE VIEW Statement Syntax and Examples

MS-SQL CREATE VIEW Statement
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 as if the data were coming from one single table.

SQL CREATE 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.

SQL CREATE 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:
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'

SQL Updating a View-You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE 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 update the view with the following SQL:

CREATE 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 a Field
we would like the value of the primary key field to be created automatically every time a new record is inserted.
We would like to create 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 ('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),
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 ('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),
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 ('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
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,'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

### What is SQL DROP INDEX Statement Syntax and Examples

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 DELETE A TABLE.
DROP TABLE table_name

### What is the SQL Index Syntax and Example

SQL Indexes
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 (and tables) that will be frequently searched against.

SQL CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name ON table_name (column_name)
SQL CREATE UNIQUE INDEX Syntax -Creates a unique index on a table. Duplicate values are not allowed:
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 SQL statement below creates an index named "CIndex" on the "LastName" column in the "Customers" table:
CREATE INDEX CIndex ON Customers (LastName)
If you want to create an 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)

### What is the SQL DEFAULT Constraint Syntax and Example

SQL 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.

SQL DEFAULT Constraint on CREATE TABLE
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),
Address varchar(255),
City varchar(255) DEFAULT 'Hyderabad'
)

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()
)

SQL DEFAULT Constraint on ALTER TABLE
To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:
MySQL:
ALTER TABLE Customers
ALTER City SET DEFAULT 'Hyderabad'

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

Oracle:
ALTER TABLE Customers
MODIFY City DEFAULT 'Hyderabad'
To DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:
MySQL:
ALTER TABLE Customers
ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access:
ALTER TABLE Customers
ALTER COLUMN City DROP DEFAULT

Thanks,
TAMATAM

### What is the SQL CHECK Constraint Syntax and Examples

SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK Constraint on CREATE TABLE

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.

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

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

To allow naming of a CHECK constraint, and for defining 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')
)

SQL CHECK Constraint on ALTER TABLE

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 allow naming of a CHECK constraint, and for defining 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