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
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
Data Control Language (DCL) statements. Some examples:
- GRANT - gives user's access privileges to database
- REVOKE - withdraw access privileges given with the
GRANT command
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
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 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.
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:
|
· 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
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.
- Delete
the records whose marks are more than 250.
- Update
marks and add 25 to each as internals if records exist.
- 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.
