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