2015: Intw question 4- SQL

Difference b/w delete, truncate and drop

Delete

• The Delete command is used to delete rows from table
• After Delete operation we need to perform COMMIT or ROLLBACK to make the changes permanent or undo
• Delete operations will cause all the delete triggers on the table to fire
• DML command

Truncate

• The Truncate command is used to delete rows from table
• After Truncate operation data can't be rolled back
• No Delete triggers will be fired 
• Truncate is faster that Delete
• DDL command

Drop

• The Drop command is used to delete table
• All table rows, indexes, privileges will also be removed
• Can't be roll back
• No Delete triggers will be fired 
• DDL Command

DDL Command- Create, Alter, Truncate, Rename, Drop

DML Command- Select, Insert, Delete, Update

DCL Command- Grant, Revoke

                                                   Difference b/w Primary, Unique & Foreign Key

Primary Key

• The PRIMARY KEY constraint uniquely identifies each record in a database table.
• Primary keys must contain unique values.
• A primary key column cannot contain NULL values.
• Most tables should have a primary key, and each table can have only ONE primary key.
• A Primary Key can be comprised of more than one column of a table

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Unique Key

• The UNIQUE constraint uniquely identifies each record in a database table.
• The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
• Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
• A unique key can be comprised of more than one column of a table
• In MS SQL Server, at most a single row for that column can have NULL

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

Foreign Key

• A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

 CREATE TABLE Orders

(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

Indexes, Views & Triggers

Indexes

• Indexes are special lookup tables that the database search engine can use to speed up data retrieval. 
• An index is a pointer to data in a table
• The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name ON table_name;

• A composite index is an index on two or more columns of a table. The basic syntax is as follows:

CREATE INDEX index_name
on table_name (column1, column2);

• Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Views

• A view is a virtual table.
• 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.
• 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.
• Syntax to create view 

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Triggers

• Triggers are stored programs, which are automatically executed or fired when some events occur. 
• Triggers are, in fact, written to be executed in response to any of the following events:
• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• The syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

Difference between Stored Procedure and Functions

• SPs are pre-compiled objects where they are compiled first time and it's compiled format is executed whenever it is called. But Function is compiled and executed every time when it is called
• Function must return a value but in SP it is optional
• Functions can be called from SPs but SPs can't be called from Function