Thursday, 25 August 2011

What are Cursors?

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
There are two types of cursors in PL/SQL:

Implicit cursors:

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors:

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

Implicit Cursors:

When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected.
When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
The status of the cursor for each of these attributes are defined in the below table. 
Attributes
Return Value
Example
%FOUND
The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row.
SQL%FOUND
The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row.
%NOTFOUND The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row.
SQL%NOTFOUND
The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row.
%ROWCOUNT Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT SQL%ROWCOUNT

For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:
DECLARE  var_rows number(5);
BEGIN
UPDATE employee 
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF; 
END; 
In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table

Tuesday, 23 August 2011

ACID properties of databases

ACID stands for
     Atomicity
    Consistency
    Isolation
    Durability


Atomicity
Modification on the data in the database either fail or succeed. The beginning of such a modification starts with a transaction and ends when a transaction finishes (either by a commit or a rollback). A software crash entails an implicit rollback.

Consistency
Only valid data (valid according to integrity constraints) may be commited.

Isolation
One transaction does not interfere with another. The 'executor' of a transaction has the feeling that he has the entire database for himeself.

Durablity
A commited transaction will not be lost.

Sunday, 21 August 2011

VIEWS

A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.

The Syntax to create a sql view is

CREATE VIEW view_name
AS
SELECT column_list
FROM table_name [WHERE condition]; 


For Example: to create a view on the product table the sql query would be like

CREATE VIEW view_product
AS
SELECT product_id, product_name
FROM product;



Updating a VIEW

You can update a VIEW without dropping it by using the following syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;

For example:
CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';

Dropping a VIEW

The syntax for dropping a VIEW is:
DROP VIEW view_name;
For example:
DROP VIEW sup_orders;



Question:  Can you update the data in a view?

Answer:  A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view.
So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.

Question: Does the view exist if the table is dropped from the database?
Answer: Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.
If you recreate the table (that you had dropped), the view will again be fine.
 

TRIGGER

The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed. The statements specified in the SQL trigger are executed each time an SQL insert, update, or delete operation is performed. An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed.

Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system, when the SQL table, that the trigger is defined on, is modified.

Advantages of using SQL trigger

  • SQL Trigger provides an alternative way to check integrity.
  • SQL trigger can catch the errors in business logic in the database level.
  • SQL trigger provides an alternative way to run scheduled tasks. With SQL trigger, you don’t have to wait to run the scheduled tasks. You can handle those tasks before or after changes being made to database tables.
  • SQL trigger is very useful when you use it to audit the changes of data in a database table.

Disadvantages of using SQL trigger

  • SQL trigger only can provide extended validation and cannot replace all the validations. Some simple validations can be done in the application level.  For example, you can validate input check in the client side by using javascript or in the server side by server script using PHP or ASP.NET.
  • SQL Triggers executes invisibly from client-application which connects to the database server so it is difficult to figure out what happen underlying database layer.
  • SQL Triggers run every updates made to the table therefore it adds workload to the database and cause system runs slower.
 Creation of Triggers 

Triggers are created with the CREATE TRIGGER statement. This statement specifies that the on which table trigger is defined and on which events trigger will be invoked.

To drop Trigger one can use DROP TRIGGER statement.

Code: SQL
CREATE TRIGGER [owner.]trigger_name ON[owner.] table_name FOR[INSERT/UPDATE/DELETE] AS IF UPDATE(column_name) [{AND/OR} UPDATE(COLUMN_NAME)...] { sql_statements }

Trigger rules and guidelines

A table can have only three triggers action per table : UPDATE ,INSERT,DELETE. Only table owners can create and drop triggers for the table.This permission cannot be transferred.A trigger cannot be created on a view or a temporary table but triggers can reference them. A trigger should not include SELECT statements that return results to the user, because the returned results would have to be written into every application in which modifications to the trigger table are allowed. They can be used to help ensure the relational integrity of database.On dropping a table all triggers associated to the triggers are automatically dropped .

The system stored procedure sp_depends can be used to find out which tables have trigger on them. Following sql statements are not allowed in a trigger they are:-
Code: SQL
ALL CREATE statements,ALL DROP statements,ALTER TABLE AND ALTER DATABASE,TRUNCATE TABLE,GRANT AND REVOKE,UPDATE STATISTICS,RECONFIGURE,LOAD DATABASE AND LOAD TRANSACTION, ALL DISK statements,SELECT INTO

INSERT trigger

When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time. The inserted table is a logical table that holds a copy of rows that have been inserted. The inserted table can be examined by the trigger ,to determine whether or how the trigger action are carried out.

The inserted table allows to compare the INSERTED rows in the table to the rows in the inserted table.The inserted table are always duplicates of one or more rows in the trigger table.With the inserted table ,inserted data can be referenced without having to store the information to the variables.

DELETE trigger

When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table.

UPDATE trigger

When an UPDATE statement is executed on a table that has an UPDATE trigger,the original rows are moved into deleted table,While the update row is inserted into inserted table and the table is being updated.

Syntax
Code: SQL
IF UPDATE<column_name> CREATE TRIGGER trigger1 ON member FOR UPDATE AS IF UPDATE(last_name) BEGIN RAISEERROR('Transaction cannot be processed') ROLLBACK TRANSACTION END

Multi-row trigger

A multi-row insert can occur from an INSERT with a SELECT statement.Multirow considerations can also apply to multi-row updates and multi-row deletes.
Code: SQL
CREATE TRIGGER adult_insert ON adult FOR INSERT AS DECLARE @rcnt int SELECT @rcnt = @@rowcount IF(SELECT COUNT(*) FROM member,inserted WHERE member.mem_num = inserted.mem_num)=0 BEGIN PRINT 'Transaction not processed' PRINT 'No entry for this member' ROLLBACK TRANSACTION END IF(SELECT COUNT(*) FROM member,inserted WHERE member.mem_num = inserted.mem_num)<> @rcnt BEGIN PRINT 'Not all adults have an entry in the member table' PRINT 'Multi-row insert transaction has been rolled backed' ROLLBACK TRANSACTION END

Denormalization of Database!

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly.

A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.


Denormalization of Database! Why?

Only one valid reason exists for denormalizing a relational design - to enhance performance. However, there are several indicators which will help to identify systems and tables which are potential denormalization candidates. These are:



* Many critical queries and reports exist which rely upon data from more than one table. Often times these requests need to be processed in an on-line environment.

* Repeating groups exist which need to be processed in a group instead of individually.

* Many calculations need to be applied to one or many columns before queries can be successfully answered.

* Tables need to be accessed in different ways by different users during the same timeframe.

* Many large primary keys exist which are clumsy to query and consume a large amount of DASD when carried as foreign key columns in related tables.

* Certain columns are queried a large percentage of the time. Consider 60% or greater to be a cautionary number flagging denormalization as an option.


Be aware that each new RDBMS release usually brings enhanced performance and improved access options that may reduce the need for denormalization. However, most of the popular RDBMS products on occasion will require denormalized data structures. There are many different types of denormalized tables which can resolve the performance problems caused when accessing fully normalized data. The following topics will detail the different types and give advice on when to implement each of the denormalization types.

What Is the Difference Between DBMS and RDBMS?

A database is a software application that enables the storage of data. Within database architecture there are different theories and implementations for storing the physical data. A relation database management system (RDBMS) is a database that stores data in tables with relationships to other tables. In comparison, a database management system (DBMS) does not require the definition of relationships between tables. Both DBMS and RDBMS are models for storing information in a physical database.
Information is a critical factor for competitive advantage in business today. Typically large organizations use multiple databases to perform business operations. These databases can include both large DBMS and RDBMS. Typically large sophisticated sets of data require an RDBMS solution, whereas smaller data collections can be managed with a DBMS.
A logical data model is a schematic that is used to express data for an organization. Today there are multiple techniques for creating a data model. A relational data model includes tables, keys, indexes, and foreign key relationships to other tables. Both DBMS and RDBMS support the concept of foreign keys, only RDBMS enforces those rules.
Cardinality is a mathematical term that explains the number of elements in a set. The cardinality relationship in a database explains the number of elements that can belong to a table with relationships. An example of cardinality is easily described with a person and phone numbers. Typically people have multiple phone numbers. This would be a one-to-many relationship in relational database theory.

Relational database theory was first introduced by Edgar Frank Codd in 1970. Within his theory Mr. Codd defined 13 rules, often termed “Cod’s 12 rules” because he numbered them zero through 12, on satisfying a relational model. The relational model requires a RDMS be built based on relationships to other data within the data model.
Before a software application can be considered a database it must have a mechanism to save data to a physical device. This will allow other software to retrieve the saved data later. The DBMS and RDBMS both allow this to happen.
A flat file is data that is 'flat' in logical nature. This data has no relationship to other data and requires programming logic to retrieve records from a physical device. These types of files are good for simple lists but are not recommended for large complex operations. The flat file is allowed in DBMS design, but not accepted in RDBMS design.
Relational database design is the next generation forward from database management system design. The relational database used the fundamentals of DBMS as the base model for storing data. DBMS and RDBMS are supported by most database vendors. Typically, RDBMS is used for more complex business applications.
 
DBMS:
A DBMS is a storage area that persist the data in files. To perform the database operations, the file should be in use.
Relationship can be established between 2 files.
There are limitations to store records in a single database file depending upon the database manager used.
DBMS allows the relations to be established between 2 files.
Data is stored in flat files with metadata.
DBMS does not support client / server architecture.
DBMS does not follow normalization. Only single user can access the data.
DBMS does not impose integrity constraints.
ACID properties of database must be implemented by the user or the developer


RDBMS:
RDBMS stores the data in tabular form.
It has additional condition for supporting tabular structure or data that enforces relationships among tables.
RDBMS supports client/server architecture.
RDBMS follows normalization.
RDBMS allows simultaneous access of users to data tables.
RDBMS imposes integrity constraints.
ACID properties of the database are defined in the integrity constraints.

Monday, 15 August 2011

SQL Index

Index in sql is created on existing tables to retrieve the rows quickly.

When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

Syntax to create Index:
CREATE INDEX index_name
ON table_name (column_name1,column_name2...); 



Syntax to create SQL unique Index:

CREATE UNIQUE INDEX index_name
ON table_name (column_name1,column_name2...); 



In Oracle there are two types of SQL index namely, implicit and explicit.

Implicit Indexes:

They are created when a column is explicity defined with PRIMARY KEY, UNIQUE KEY Constraint.

Explicit Indexes:

They are created using the "create index.. " syntax.
NOTE:

1) Even though sql indexes are created to access the rows in the table quickly, they slow down DML operations like INSERT, UPDATE, DELETE on the table, because the indexes and tables both are updated along when a DML operation is performed. So use indexes only on columns which are used to search the table frequently.


2) Is is not required to create indexes on table which have less data. 


3) In oracle database you can define up to sixteen (16) columns in an INDEX. 


Index in Sql Server

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that
Each SELECT statement within the UNION must have the same number of columns. 
The columns must also have similar data types. 
Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
  

Note:

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
 

PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

SQL UNION Example

Look at the following tables:

"Employees_Norway":
E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

"Employees_USA":
E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen

Now we want to list all the different employees in Norway and USA.
We use the following SELECT statement:

   SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

The result-set will look like this:
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

Note: This command cannot be used to list all employees in Norway and USA. In the example above we have two employees with equal names, and only one of them will be listed. The UNION command selects only distinct values.


SQL UNION ALL Example

Now we want to list all employees in Norway and USA:

 SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA
  

Result
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen

FULL JOIN

The FULL JOIN keyword return rows when there is a match in one of the tables.

SQL FULL JOIN Syntax

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name


SQL FULL JOIN Example

The "Persons" table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:
    SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove  
    34764

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.

RIGHT JOIN

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
  

PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL RIGHT JOIN Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:
   SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName


The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
    34764


The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).

LEFT JOIN

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
 

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN Example

The "Persons" table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:
    SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
  

The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove  

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).

INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL INNER JOIN Example

The "Persons" table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Now we want to list all the persons with any orders.
We use the following SELECT statement:

  SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
    




The result-set will look like this:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678

The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

Look at the "Persons" table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.
  • JOIN: Return rows when there is at least one match in both tables
  • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
  • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
  • FULL JOIN: Return rows when there is a match in one of the tables

SQL Alias

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names.
An alias name could be anything, but usually it is short.

SQL Alias Syntax for Tables

SELECT column_name(s) FROM table_name AS alias_name

SQL Alias Syntax for Columns

SELECT column_name AS alias_name FROM table_name

Alias Example

Assume we have a table called "Persons" and another table called "Product_Orders". We will give the table aliases of "p" and "po" respectively.

Now we want to list all the orders that "Ola Hansen" is responsible for.

We use the following SELECT statement:

SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p,
Product_Orders AS po
WHERE p.LastName='Hansen' AND p.FirstName='Ola'
     


The same SELECT statement without aliases:

SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName
FROM Persons,
Product_Orders
WHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola'


As you'll see from the two SELECT statements above; aliases can make queries easier to both write and to read.

TOP Clause


The TOP clause is used to specify the number of records to return.
The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Note: Not all database systems support the TOP clause.

SQL Server Syntax

SELECT TOP number|percent column_name(s)FROM table_name


Sunday, 31 July 2011

Difference Between Where & Having Clause?

The difference between a "where" clause and a "having" clause is a "Where" clause is applied to enforce condition on select statement and a single row function and is applied prior to GROUP BY clause where as HAVING clause is applied to enforce condition of GROUP Function and is applied after GROUP BY clause in the inquiry.

When you are carrying out a summary query, you can also state a Having clause. This is similar to a Where clause, the only difference is it involves a summary value, in place of a column value.

The most basic way to understand the difference among a "Where" clause is a "Where" clause denotes the rows in your tables which have to be integrated in the summation. The "Having" clause operates after the summation has been carried out. It denotes which of the summary row are returned in your result-set


Answer in one line is : HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(titles.price) > 10


Crosstab Query

A cross tab query is a transformation of rows of data to columns.It is easier review any data from rows.

Two examples of a cross-tab is to take the above data and show how much your shops stocked in each product across. This allows for easy comparison of stock by product.

This is source data from dbo.Shop's table.


Example 1 :


Move data in column of ProductNo to column of MLR,MLB and MLC.

SELECT ShopNo,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLR') AS MLR,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLB') AS MLB,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLC') AS MLC
FROM dbo.Shop S GROUP BY ShopNo

Result :


Example 2 :

This is example of summary stock by product.

SELECT ShopNo,

SUM(CASE WHEN ProductNo ='MLR' THEN Stock ELSE 0 END) AS MLR,

SUM(CASE WHEN ProductNo ='MLB' THEN Stock ELSE 0 END) AS MLB,

SUM(CASE WHEN ProductNo ='MLC' THEN Stock ELSE 0 END) AS MLC,

SUM(Stock) AS Total

FROM Shop

GROUP BY ShopNo

Result :

SELECT INTO statement

The SELECT INTO statement creates the new table by copying schema and other information that is not specific to the actual algorithm.

Syntax :
SELECT [column]INTO [new table]FROM [existing table]

Example :




select * into dbo.newContacts from [OBXKites] .[dbo] .Contact;


SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes":

SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'


SQL SELECT INTO - Joined Tables

Selecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id

How to create function

CREATE FUNCTION owner_name.function_name ( @parameter_name AS data_type ,...n )
RETURNS data_type
AS
BEGIN
function_body
RETURN value
END


Examples :

CREATE FUNCTION dbo.GetNoWeek (@var Varchar(10))
RETURNS varchar(10)
BEGIN
DECLARE @result varchar(10)
IF @var = 'Ok'
SET @Result = 'Ok'
ELSE
SET @Result = 'Not Ok'
RETURN @Result
END 


Execute

Select dbo.GetNoWeek('Ok');

Stored procedures vs. functions

1. In functions you  cant use DDL , DML smts (like Insert and Update) which is possible in SPs
2. You can use function output in select smts (like Select fnTest(), a from test a)
3  You can use the function output in joins (like test a LEFT OUTER JOIN fnTest() b )
4. Sp can return multiple result-set, function can return only one resultset
5. Function can accept less parameter than procedure
6. Function can not return XML output
.

Stored Procedures are basically compiled which results in faster execution. Once executed the results are cached thus resulting in better performance  more you use it. This is not the result in case of User functions.


Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.

Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures






create procedure sp_GetAllUsers
@ContactCode Char(12)
As
Select * from [OBXKites] .[dbo] .Contact Where  [OBXKites] .[dbo] .Contact.ContactCode=@ContactCode

exec sp_GetAllUsers 102






create function dbo.getContactName(@ContactCode char(15))
returns varchar(50)
Begin
Declare @name varchar(50)

 Select @name = FirstName +LastName  from [OBXKites] .[dbo] .Contact Where  [OBXKites] .[dbo] .Contact.ContactCode=@ContactCode
return @name
end


select dbo.getContactName('102');

Saturday, 30 July 2011

Stored procedures

Stored procedures are essentially functions that you can create in the database and reuse. What's neat about them from a usability standpoint is that they can take input parameters and then return a result.

Stored procedures have many benefits, including:
  • Speed - Stored procedures are pre-compiled, so the execution plan doesn't have to be figured out each time they're called. This can result in a significant performance improvement.
  • Code reuse and abstraction - Stored procedures often involve complex code. It's nice that this code doesn't have to be rewritten over and over again and that even entry-level SQL programmers can make use of it with a simple stored procedure call. 
  • Security - Permissions can be granted for stored procedures while being restricted for the underlying tables. This allows the DBA to provide a method for SQL programmers and report writers to access and/or manipulate data in a safe way. 
  • Reduced traffic between client and server - Since the bulk of the query is already stored on the server and only the relatively short stored procedure call has to get sent, traffic to the server is decreased.
 CREATE PROCEDURE procedure_name
                @param data_type = default_value,
                @param data_type = default_value,
                @param data_type = default_value
                 AS
                -- statements for procedure here

Code Sample: StoredProcedures/Demos/StoredProc.sql

CREATE PROCEDURE SalesByCategory
 @CategoryName nvarchar(15),
 @OrdYear int = 1998
AS

SELECT ProductName, SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice) AS TotalPurchase
FROM "Order Details" od, Orders o, Products p, Categories c
WHERE od.OrderID = o.OrderID
 AND od.ProductID = p.ProductID
 AND p.CategoryID = c.CategoryID
 AND c.CategoryName = @CategoryName
 AND DATEPART(year,OrderDate) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName

--Call stored procedure
EXEC SalesByCategory 'Seafood', 1997
 
Must Read

Recursion is one of the most elegant programming constructsin my opinion. I have used it in dozens of situations and in severalprogramming languages. The basic concept of recursion is straightforward: agiven chunk of code calls itself until some boundary condition is reached. I'lldemonstrate you how to use recursion in T-SQL, using one of the classicexamples of recursion: the factorial calculation.

A factorial is the multiple of any number by all the lessernumbers down to two. For example, factorial(10) isequal to 10 * 9 * 8 * 7 * 6 * 5 * 4 * 3 * 2. (You could add * 1, but whybother?)

The following script implements the factorial algorithm:

CREATE PROCEDURE [dbo].[Factorial_ap]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Factorial_ap @In, @Out OUTPUT
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN
GO

Assuming that you want to calculate factorial(n), the procedure will call itself (n-2) times. SQL Server permits recursionto a depth of 32 calls, but at 13, you'll suffer arithmetic overflow. If youexpect to be calculating factorials for large numbers, then you should declarethe variable as BigInt rather than Integer. This willallow you to calculate factorial(20), whichincidentally is 2,432,902,008,176,640,000. The results increase in size soquickly that factorial(21) breaks this implementationas well.

As pretty as the factorial algorithm is, it's unlikely thatyou'll have much use for it in your everyday programming. Nevertheless, itconcisely illustrates the principle of recursion.

Thursday, 28 July 2011

Sorting

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC


Order by Two Columns

It is also possible to order by more than one column. When ordering by more than one column, the second column is only used if the values in the first column are equal:
SELECT column_name(s)  FROM table_name ORDER BY column1, column2

cast-and-convert




Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )


expression
Is any valid expression.
data_type
Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used. For more information about available data types, see Data Types (Transact-SQL).
length
Is an optional integer that specifies the length of the target data type. The default value is 30.
style
Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type. For more information, see the Remarks section.

String Functions

1. CHARINDEX 
string function takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.
Example:
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6


2. LEFT 
string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.
Example:
Select LEFT ('MICROSOFT SQL SERVER 2000',4)
Result: MICR


3. RIGHT 
string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.
Example:
Select RIGHT ('MICROSOFT SQL SERVER 2000',4)
Result: 2000


4. LEN 
string function takes 1 argument as string value and returns the length of entered string.
Example:
Select LEN ('MICROSOFT SQL SERVER 2000')
Result: 25


5. REPLACE
  string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string entered as 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.
Example:
Select REPLACE ('MICROSOFT SQL SERVER 2000','MICROSOFT','MS')
Result: MS SQL SERVER 2000




6. STUFF 
string function takes 4 arguments. It is used to replace specified length of characters with provided pattern.
1st argument as string value.
2nd argument as integer value specifying the starting point of characters to be replaced.
3rd arguments as integer value specifying the length of characters.
4th argument as string value specifying the new pattern of characters.
Example:
Select STUFF ('MICROSOFT SQL SERVER 2000', 11, 3,'S.Q.L.')
Result: MICROSFT S.Q.L. SERVER 2000


7. SUBSTRING 
string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length
Example:
Select SUBSTRING ('MICROSOFT SQL SERVER 2000', 11, 3)
Result: SQL

 
8. LOWER 
string function returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.
Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING’)
Result: microsoft asp .net web hosting


9. UPPER
  string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.
Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

 
10. REVERSE 
string function returns the string in reverse order. It takes 1 argument as string value.
Example:
select REVERSE(‘ASP.NET’)
Result: TEN.PSA




11. LTRIM 
function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.
Example:
select LTRIM (‘ ASP ’)
Result: ASP-----
blanks at the right side not removed.

 
12. RTRIM 
function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.
Example:
select RTRIM (‘ ASP ’)
Result: -----ASP
blanks at the left side not removed.


13. PATINDEX 
function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.
Example:
select PATINDEX('%RO%','MICROSOFT')
Results: 4


14. STR
function returns character data converted from numeric data. It takes 3 arguments.
1st argument as float data
2nd argument as integer value specifying the length of the string including decimal that is to be retrieved.
3rd argument as integer specifying the number of places to the right of the decimal point.
Example:
select STR(140.15, 6, 1)
Result: 140.2


15. ASCII  
function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.
Example:
select ASCII('A')
Result: 65

Friday, 15 July 2011

Server Environment Server Environment Functions

  • SELECT GETDATE()

  • SELECT GETDATE() AS 'Date',DB_NAME() AS 'Database'

  • SELECT @@SERVERNAME 

  • SELECT   SERVERPROPERTY ('ServerName') AS ServerName,SERVERPROPERTY ('Edition') AS Edition, SERVERPROPERTY ('EngineEdition') AS EngineEdition, SERVERPROPERTY ('ProductLevel') AS ProductLevel

-- User Information Functions
  • SELECT   USER_NAME() AS 'User',  SUSER_SNAME() AS 'Login',  HOST_NAME() AS 'Workstation',  APP_NAME() AS 'Application'

---Date/Time Functions

  • SELECT DATENAME(YEAR, GETDATE()) AS YEAR

  • UPDATE Guide   SET DateOfBirth = '9/4/58'  WHERE lastName = 'Frank'

  • SELECT LastName,  DATENAME(yy,DateOfBirth) AS [Year],   DATENAME(mm,DateOfBirth) AS [Month],    DATENAME(dd,DateOfBirth) AS [Day],   DATENAME(WEEKDAY, DateOfBirth) AS BirthDay  FROM Guide  WHERE DateOfBirth IS NOT NULL

  • SELECT DATEPART(DayofYear, GETDATE()) AS DayCount
  • SELECT DATEPART(dw, GETDATE()) AS DayWeek

-- Date Math

  • SELECT DATEDIFF(yy,'2005/10/22', GETDATE()) AS MarriedYears,  DATEDIFF(dd,'2005/10/22', GETDATE()) AS MarriedDays

  • SELECT DATEADD(hh,100, GETDATE()) AS [100HoursFromNow]
  •  SELECT DATEDIFF(dd,getdate(),dateadd(yy,10,getdate()))

Dealing with null( is null) (is not null) and COALESCE

SELECT CASE
  WHEN Null = NULL THEN 'True'
  ELSE 'False'
  END as '=null'

SELECT CASE
  WHEN Null IS NULL THEN 'True'
  ELSE 'False'
  END as ' IS null'

SELECT FirstName, LastName, Nickname  FROM Customer  WHERE NickName IS NOT NULL ORDER BY LastName, FirstName


select COALESCE(customersince, companyname) from  OBXKites.dbo.Contact where contactcode=106

Case Statement

 /*
CASE
  WHEN BooleanCondition THEN Expression
  WHEN BooleanCondition THEN Expression
    ...
  ELSE Expression
END AS Alias
 */

SELECT CustomerTypeName, [Default],
    CASE [Default]
      WHEN 1 THEN 'default type'
      WHEN 0 THEN 'alternate'
      ELSE '-'
    End as AssignStatus
  From dbo.CustomerType

Where Clause

/*
SELECT *
  FROM data source(s)
  WHERE condition
*/

-- a boolean condition
SELECT *  FROM dbo.Tour  WHERE 1 = 1;

-- Comparison Operator
SELECT *  FROM dbo.Tour  WHERE TourID <= 3;

-- Between
SELECT *  FROM dbo.Tour  WHERE TourID BETWEEN 2 AND 4;

SELECT *  FROM dbo.Tour  WHERE TourID >= 2    AND TourID <= 4;

-- In Condition
SELECT *  FROM dbo.Tour  WHERE TourID IN (1, 3);

UPDATE dbo.BaseCamp SET Region = 'NC' WHERE Name = 'Ashville';
UPDATE dbo.BaseCamp SET Region = 'NC' WHERE Name = 'Cape Hatteras';
UPDATE dbo.BaseCamp SET Region = 'BA' WHERE Name = 'Freeport';
UPDATE dbo.BaseCamp SET Region = 'FL' WHERE Name = 'Ft Lauderdale';
UPDATE dbo.BaseCamp SET Region = 'WV' WHERE Name = 'West Virginia';

SELECT *  FROM dbo.BaseCamp  WHERE Region IN ('NC', 'WV');

-- Like Condition
/*
multiple characters    %
single character        _
match in range        [ ]
match not in range    [^ ]
*/

SELECT *  FROM dbo.BaseCamp  WHERE Name LIKE 'F%';

SELECT *  FROM dbo.Customer  WHERE LastName LIKE '%son';

SELECT *  FROM dbo.Customer  WHERE FirstName LIKE '[ABC]%';

SELECT *  FROM dbo.Customer  WHERE FirstName LIKE '[^ABC]%';

-- Multiple Conditions
SELECT *  FROM dbo.Customer  WHERE   CustomerTypeID = 1     OR        FirstName LIKE  'Dav%'     AND        CustomerID = 14;

SELECT *  FROM dbo.Customer  WHERE       (CustomerTypeID = 1         OR        FirstName LIKE 'Dav%')      AND        CustomerID = 14;

Simple SQL statement

 /*
SELECT *
  FROM
    tables(s)
    subqueries (as derived tables) AS NamedRangeAlias
    common table expressions (CTE)
    views
    table-valued user-defined functions
    distributed data sources (OpenQuery) 
    XML data sources (XQuery)
*/

SELECT 'SQL Rocks';

SELECT *   FROM Tour;

-- Fully Qualified - Four Part Name

-- SELECT * --   FROM Server.Database.Schema.Object;

SELECT *   FROM dbo.Tour;
SELECT *   FROM dbo.Tour;

varchar and nvarchar

  • nvarchar stores unicode data while varchar stores ascii data. They function identically but nvarchar takes up twice as much space.


An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.
All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.
If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you're having to maintain, even while enjoying some of the benifits of full Unicode storage.

Wednesday, 13 July 2011

Basic

Toggle Result Pane - ctrl + R
-- Switch Default Database
                                       USE databasename;

-- Statement Terminator   ;
-- Batch Terminator        GO

-- Fully Qualified - Four Part Name
                                                   Server.Database.Schema.Object

Managing Optional Data

  1. Missing rows: poor performance hard to manage(super type sub type relationship)
  2. Null-able Columns: Efficient
  3. Surrogate nulls: In-consistence

Patterns in a database

  • One to many:   use of foreign key
  • Many to Many: Mapping table
  • Super-type subtype Relationship:Generalization
  • Reflexive pattern(hierarchy pattern):Self join type pattern

Constraints on Database Items

You can place constraints to limit the type of data that can go into a table. Such constraints can be specified when the table when the table is first created via the CREATE TABLE statement, or after the table is already created via the ALTER TABLE statement.
Common types of constraints include the following:

Check Constraints

A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy an arbitrary expression. For instance, to require positive product prices, you could use:


CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);
 

As you see, the constraint definition comes after the data type, just like default value definitions. Default values and constraints can be listed in any order. A check constraint consists of the key word CHECK followed by an expression in parentheses. The check constraint expression should involve the column thus constrained, otherwise the constraint would not make too much sense.

You can also give the constraint a separate name. This clarifies error messages and allows you to refer to the constraint when you need to change it. The syntax is:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);
So, to specify a named constraint, use the key word CONSTRAINT followed by an identifier followed by the constraint definition.
A check constraint can also refer to several columns. Say you store a regular price and a discounted price, and you want to ensure that the discounted price is lower than the regular price.
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);
 

The first two constraints should look familiar. The third one uses a new syntax. It is not attached to a particular column, instead it appears as a separate item in the comma-separated column list. Column definitions and these constraint definitions can be listed in mixed order.

We say that the first two constraints are column constraints, whereas the third one is a table constraint because it is written separately from the column definitions. Column constraints can also be written as table constraints, while the reverse is not necessarily possible. The above example could also be written as

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);
 
or even

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);
 
It's a matter of taste.

 not-null constraint
A not-null constraint simply specifies that a column must not assume the null value. A syntax example:
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);
 

A not-null constraint is always written as a column constraint. A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient. The drawback is that you cannot give explicit names to not-null constraints created that way.

Of course, a column can have more than one constraint. Just write the constraints after one another:
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);
The order doesn't matter. It does not necessarily affect in which order the constraints are checked.
The NOT NULL constraint has an inverse: the NULL constraint. This does not mean that the column must be null, which would surely be useless. Instead, this simply defines the default behavior that the column may be null. The NULL constraint is not defined in the SQL standard and should not be used in portable applications. (It was only added to PostgreSQL to be compatible with other database systems.) Some users, however, like it because it makes it easy to toggle the constraint in a script file. For example, you could start with

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);
and then insert the NOT key word where desired.



Unique Constraints

Unique constraints ensure that the data contained in a column or a group of columns is unique with respect to all the rows in the table. The syntax is

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);
when written as a column constraint, and
 
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);
when written as a table constraint.
If a unique constraint refers to a group of columns, the columns are listed separated by commas:
 
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

It is also possible to assign names to unique constraints:
 
CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);
 
 

In general, a unique constraint is violated when there are (at least) two rows in the table where the values of each of the corresponding columns that are part of the constraint are equal. However, null values are not considered equal in this consideration. That means, in the presence of a multicolumn unique constraint it is possible to store an unlimited number of rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases may not follow this rule. So be careful when developing applications that are intended to be portable.


Primary Keys

Technically, a primary key constraint is simply a combination of a unique constraint and a not-null constraint. So, the following two table definitions accept the same data:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
 

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Primary keys can also constrain more than one column; the syntax is similar to unique constraints:
 
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);
 

A primary key indicates that a column or group of columns can be used as a unique identifier for rows in the table. (This is a direct consequence of the definition of a primary key. Note that a unique constraint does not, in fact, provide a unique identifier because it does not exclude null values.) This is useful both for documentation purposes and for client applications. For example, a GUI application that allows modifying row values probably needs to know the primary key of a table to be able to identify rows uniquely.
A table can have at most one primary key (while it can have many unique and not-null constraints). Relational database theory dictates that every table must have a primary key. This rule is not enforced by PostgreSQL, but it is usually best to follow it.

Foreign Keys

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.
Say you have the product table that we have used several times already:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
 
Let's also assume you have a table storing orders of those products. We want to ensure that the orders table only contains orders of products that actually exist. So we define a foreign key constraint in the orders table that references the products table:
 
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);
 
Now it is impossible to create orders with product_no entries that do not appear in the products table.