SQL SERVER for tester...

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

DROP

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

 

CREATE DATABASE	CREATE  DATABASE  database_name
CREATE TABLE	CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,Column_name3 data_type,....)
Select statement	SELECT * FROM table_name, SELECT column_name(s)  FROM table_ name
Distinct values	SELECT DISTINCT column name(s) FROM table name
Where clause	SELECT column_name(s) FROM table_nameWHERE column name operator value
AND & OR operator	SELECT * FROM Persons WHERE FirstName='Tove' ANDLastName='Svendson'	SELECT * FROM Persons WHEREFirstName='Tove' OR FirstName='Ola'			SELECT * FROM Persons WHERELastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola')
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
INSERT INTO	INSERT INTO table_name VALUES (value1, value2, value3,...)
UPDATE	UPDATE table_name SET column1=value WHERE some_column=some_value
DELETE //(Rows only deleted)	DELETE * FROM table_name, DELETE FROM table_name WHERE some_column=some_value
TOP CLAUSE	SELECT TOP number | percent column_name(s) FROM table_name // (top rows returned)
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern (// %,_[CHAR LIST], [^CHARLIST],OR [!CHAR LIST])
IN (to specify multiple Val’s)	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
BETWEEN(Range b/w two Val’s)	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
Alias for tables	SELECT column_name(s) FROM table_name AS alias_name
Alias for columns	SELECT column_name AS alias_name FROM table_name Ex: SELECT po.OrderID, p.LastName, p.FirstName  FROM Persons AS p, Product_Orders AS po  WHERE p.LastName='Hansen' AND p.FirstName='Ola' ----------------------------------------------------------------------------------------------------- Ex: with out aliases above ex: SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons,Product_OrdersWHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola'
JOINS 1.INNER JOIN	SELECT column_name(s) FROM table_name1  INNER JOIN table_name 2 ON table_name1.column_name =table_name2.column_name		SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
2.LEFT (OR) LEFT OUTER JOIN	SELECT column_name(s)FROM table_name1 LEFT JOIN table_name2 ONtable_name1.column_name =table_name2.column_name		SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN  Orders  ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
3.RIGHT (OR) RIGHT OUTER JOIN	SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name= table_name2.column_name		SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
4.FULL JOIN	SELECT column_name(s) FROM table_name1 FULL JOIN table_name2  ON table_name1.column_name=table_name2.column_name
FULL JOIN  Ex:	SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
UNION & UNION ALL	SELECT column_name(s) FROtable_name1     UNION / UNION ALL SELECT column_name(s) FROM table_name2			(union--returns only distinct values) (union all--returns duplicate values also)
SELECT INTO	SELECT * / column name INTO new_table_name [IN externaldatabase]FROM old_tablename        //(to create back up of table or columns)
CONSTRAINTS 1.NOT NULL	CREATE TABLE Persons (P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255),Address varchar(255),City varchar(255))
2.UNIQUE 3. DROP UNIQUE CONSTRAINT	1.CREATE TABLE Persons (P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255),Address varchar(255),City varchar(255)) 2.For already existing table  ALTER TABLE Persons ADD UNIQUE (P_Id) 3.for multiple cols’ ALTER TABLE PersonsADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ALTER TABLE Persons DROP CONSTRAINT uc_PersonID
4.PRIMARY KEY	CREATE TABLE Persons (P_Id int NOT NULL, LastName varchar(255)  NOT NULL, FirstName varchar(255),Address varchar(255),City varchar(255), CONSTRAINT  pk_PersonID PRIMARY KEY (P_Id,LastName)) For Existed table: ALTER TABLE Persons ADD PRIMARY KEY (P_Id)  For multiple PK’s:  ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) To drop PK:  ALTER TABLE Persons DROP CONSTRAINT pk_PersonID
5.FOREIGN KEY	CREATE TABLE Orders (O_Id int NOT NULL PRIMARY KEY,OrderNo int NOT NULL,P_Id int FOREIGN KEY REFERENCES Persons(P_Id)) For Existed table: ALTER TABLE Orders ADD FOREIGN KEY (P_Id)REFERENCES Persons(P_Id) To drop FK :ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders
6.CHECK CONSTRAINT	CREATE TABLE Persons(P_Id int NOT NULL CHECK (P_Id>0),LastNamevarchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255)) //(To check the cond’n) For Existed table: ALTER TABLE Persons ADD CHECK (P_Id>0) For multiple columns : ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') ALTER TABLE Persons To drop CHECK : DROP CONSTRAINT chk_Person
7.DEFAULT	CREATE TABLE Orders( Order­­_Date date DEFAULT GETDATE()) For Existed table:  ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES' drop DEFAULT:ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT
CREATE INDEX UNIQUEINDEXà DROP INDEXà	CREATE INDEX index_name ON table_name (column_name) CREATE UNIQUE INDEX index_name ON table_name (column_name) DROP INDEX table_name.index_name
DROP	DROP TABLE table_name DROP DATABASE database_name
TRUNCATE	TRUNCATE TABLE table_name
ALTER TABLE ALTER COLUMN	ALTER TABLE table_name  ADD column_name datatype ALTER TABLE table_name ATER COLUMN column_name datatype
IDENTITY	CREATE TABLE Persons (P_Id int PRIMARY KEY IDENTITY,LastName  varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))
CREATE VIEW UPDATING VIEW DROP VIEWà	CREATE VIEW view_name AS SELECT column_name(s)FROM table_name   WHERE condition CREATE OR REPLACE VIEW view_name AS SELECT column_name(s)  FROM table_name WHERE condition DROP VIEW view_name
DATE FUNC’S: GETDATE() DATEPART() DATEADD() DATEDIFF() CONVERT()	·  DATE - format YYYY-MM-DD ·  DATETIME - format: YYYY-MM-DD HH:MM:SS Returns the current date and time Returns a single part of a date/time Adds or subtracts a specified time interval from a date Returns the time between two dates Displays date/time data in different formats
SELECTING NULL /NOT NULL COLUMNS	SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL/NOT NULL
IS NULL	NULL is treated as specific value for the calculation purpose
Functions	SELECT AVG(column_name) FROM table_name SELECT COUNT(column_name) FROM table_name SELECT FIRST(column_name) FROM table_name SELECT LAST(column_name) FROM table_name SELECT MAX(column_name) FROM table_name SELECT MIN(column_name) FROM table_name SELECT SUM(column_name) FROM table_name SELECT LEN(column_name) FROM table_name SELECT ROUND(column_name,decimals) FROM table_name
GROUP BY MORE THAN ONE COL’N	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate
HAVING	Instead of Where clause ,for aggregative functions
UPPER case	SELECT UPPER(column_name) FROM table_name
LOWER case	SELECT LOWER(column_name) FROM table_name
MID VALUE	SELECT MID(column_name,start [,length]) FROM table_name

 

SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

August 28, 2008 by pinaldave

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
;
Example:

Let’s create Student Details and StudentTotalMarks and inserted some records.

Student Details:

USE AdventureWorks
GO
CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'SMITH')
INSERT INTO StudentDetails
VALUES(2,'ALLEN')
INSERT INTO StudentDetails
VALUES(3,'JONES')
INSERT INTO StudentDetails
VALUES(4,'MARTIN')
INSERT INTO StudentDetails
VALUES(5,'JAMES')
GO

StudentTotalMarks:

CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
INSERT INTO StudentTotalMarks
VALUES(3,200)
GO

In our example we will consider three main conditions while we merge this two tables.

1. Delete the records whose marks are more than 250.
2. Update marks and add 25 to each as internals if records exist.
3. Insert the records if record does not exists.

Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO

There are two very important points to remember while using MERGE statement.

• Semicolon is mandatory after the merge statement.

When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

There are two very important points to remember while using MERGE statement.

• Semicolon is mandatory after the merge statement.
• When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

After the MERGE statement has been executed, we should compare previous resultset and new resultset to verify if our three conditions are carried out.

Second example

MERGE SQL statement - Part 1

--Create a target table

CREATE TABLE Products

(

ProductID INT PRIMARY KEY,

ProductName VARCHAR (100),

Rate MONEY

) 

GO

--Insert records into target table

INSERT INTO Products

VALUES

(1, 'Tea', 10.00),

(2, 'Coffee', 20.00),

(3, 'Muffin', 30.00),

(4, 'Biscuit', 40.00)

GO

--Create source table

CREATE TABLE UpdatedProducts

(

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Rate MONEY

) 

GO

--Insert records into source table

INSERT INTO UpdatedProducts

VALUES

(1, 'Tea', 10.00),

(2, 'Coffee', 25.00),

(3, 'Muffin', 35.00),

(5, 'Pizza', 60.00)

GO

SELECT * FROM Products

SELECT * FROM UpdatedProducts

GO

MERGE SQL statement - Part 2

--Synchronize the target table with

--refreshed data from source table

MERGE Products AS TARGET

USING UpdatedProducts AS SOURCE 

ON (TARGET.ProductID = SOURCE.ProductID) 

--When records are matched, update 

--the records if there is any change

WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 

OR TARGET.Rate <> SOURCE.Rate THEN 

UPDATE SET TARGET.ProductName = SOURCE.ProductName, 

TARGET.Rate = SOURCE.Rate 

--When no records are matched, insert

--the incoming records from source

--table to target table

WHEN NOT MATCHED BY TARGET THEN 

INSERT (ProductID, ProductName, Rate) 

VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

--When there is a row that exists in target table and

--same record does not exist in source table

--then delete this record from target table

WHEN NOT MATCHED BY SOURCE THEN 

DELETE

--$action specifies a column of type nvarchar(10) 

--in the OUTPUT clause that returns one of three 

--values for each row: 'INSERT', 'UPDATE', or 'DELETE', 

--according to the action that was performed on that row

OUTPUT $action, 

DELETED.ProductID AS TargetProductID, 

DELETED.ProductName AS TargetProductName, 

DELETED.Rate AS TargetRate, 

INSERTED.ProductID AS SourceProductID, 

INSERTED.ProductName AS SourceProductName, 

INSERTED.Rate AS SourceRate; 

SELECT @@ROWCOUNT;

GO

Notes

• The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator.
• When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
• At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
• Of course it’s obvious, but just to mention, the person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
• MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
• MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as we did in previous version of SQL Server.
• For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.