MERGE statement

----------MERGE STATEMENT--------------

--if there is an IDENTITY no need to pass ID value

MERGE C AS T USING B AS S ON T.ID=S.ID AND T.NAME=S.NAME
WHEN MATCHED THEN UPDATE SET T.NAME=S.NAME
WHEN NOT MATCHED THEN INSERT VALUES(NAME) OUTPUT inserted.*,deleted.*,$ACTION;

--C target table


-- Update existing, add missing
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
        ON C.CustID = CT.CustID
WHEN MATCHED THEN
    UPDATE SET
      C.CompanyName = CT.CompanyName,
      C.Phone = CT.Phone
WHEN NOT MATCHED THEN 
      INSERT (CustID, CompanyName, Phone)

      VALUES (CT.CustID, CT.CompanyName, CT.Phone);

TRIGGERs

----------------------AFTER TRIGGER----------------

--create TABLE & INSERT records in it

CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)
-- Now Insert records 
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);

Insert into Employee_Demo values ('Riyaz',1400);

--create another TABLE for insert TRIGGER executions  
CREATE TABLE Employee_Demo_Audit
(
 Emp_ID int,
 Emp_Name varchar(55),
 Emp_Sal decimal(10,2),
 Audit_Action varchar(100),
 Audit_Timestamp datetime

)

--create  TRIGGER for executions

CREATE TRIGGER trgAfterInsert on Employee_Demo
FOR INSERT
AS 
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.'; 
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

--Now Insert record
insert into Employee_Demo(Emp_Name,Emp_Sal)values ('suresh',1000);

--select statements

select * from Employee_Demo

select * from Employee_Demo_Audit

SQL SERVER

SQL

DML
DDL
DCL
TRANSACTIONS
DATA TYPES
INDEXES
TSQL
         Joins
         Group by
         Having
         where
         Order by
Functions
Procedures
Views
Operators 
Select Query
Triggers
SubQuery
Correlated query
CTE
TEMP TABLES
SUB QUERIES 
DBCC Commands 
LOOP
          While
CONDITION
           If
BUILT IN FUNCTIONS
TRANSACTIONS 
ERROR HANDLING 
SET COMMANDS 

ADMIN

INSTALLATION 
BACKUP/RESTORE 
CREATING OF DATA BASE
LOG INS
JOBS
      Agent Services
ALERTS 
LOG SHIPPING
FILE GROUPS
MIRRORING 
REPLICATION 
CLUSTERING 
UPGRADE 
MIGRATION 

MSBI
1.  SSIS                                                              2.  SSRS                                       3.  SSAS

                                                                            REPORTS                                    CUBES

1 . PACKAGES                                                 EXPRESSIONS 

          CREATING PACKAGES                        CUSTOM CODE                  

          SOURCES                                                DEPLOYMENT 

          DESTINATIONS                                   

          TASKS 1 .CONTROL FLOW

                        2.DATA FLOW

          CONFIGURATIONS

          DEPLOYMENT 

          DOT NET INTEGRITY(SCRIPT TYPE) 

SQL -STRUCTURED QUERY LANGUAGE

• SQL IS LANGUAGE TO ACCESS AND MANIPULATE THE DATA IN DATABASE.
• MS SQL SERVER USES T-SQL(TRANSACT-SQL ) AND PL/SQL FOR ORACLE.
• SQL IS CASE IN SENSITIVE LANGUAGE.
• DML=> DATA MANIPULATION LANGUAGE. 
• DML QUERIES IN SQL
• SELECT
• SELECT * FROM EMP
• INSERT
• INSERT INTO EMP (ID,NAME,CITY)VALUES(1,'PATIL','BANGALORE')
• DELETE
• DELETE FROM EMP WHERE ID=1
• UPDATE
• UPDATE EMP

                                        SET NAME='MAHESH' WHERE ID=1

• DDL=>DATA DEFINITION LANGUAGE.
• DDL QUERIES
• CREATE DB 
• DROP DB
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• CREATE INDEX
• DROP INDEX
• DCL=> DATA CONTROL LANGUAGE.
• DCL QUERIES
• GRANT
• REVOKE

• MSBI=> MICROSOFT BUSINESS INTELLIGENCE.
• CONVERTING DATA INTO INFORMATION TO MAKE BETTER DECIGENS
• SSIS=> SQL SERVER INTEGRATION SERVICES.
• SSIS USED FOR ETL OPERATION AND DATA MIGRATION.
• SSAS=> SQL SERVER ANALYSIS SERVICES.
• SSAS IS USED FOR DATA ANALYSIS.
• SSRS=> SQL SERVER REPORTING SERVICES.
• SSRS IS REPORTING TOOL IS USED TO CREATE VERITY OF REPORTS. 
•      

JOINS

JOINS ARE USED TO COMBINE ROWS FROM TWO OR MORE TABLES.

T-SQL SUPPORTS 4 TYPES OF JOINS

1. INNER JOIN

          THE INNER JOIN SELECT ALL ROWS FROM BOTH TABLES ,
           ONLY IF THEIR ARE MATCH IN BOTH TABLE.

          

          EX:  SELECT CUST.NAME,ORDERS.TOTALORDERS FROM CUST

                  INNER JOIN ORDERS 

                  ON ORDERS.CID=CUST.CID


     2.  OUTER JOIN

1. LEFT OUTER JOIN
•   THE LEFT JOIN SELECT ALL ROWS FROM LEFT TABLE (FIRST     
    TABLE), WITH MATCHING ROWS IN THE RIGHT TABLE(SECOND   
    TABLE) AND NON MATCHING ROWS IN THE RIGHT TABLE ARE
    
  NULL.
2. RIGHT OUTER JOIN
•   THE RIGHT JOIN SELECT ALL ROWS FROM RIGHT TABLE( 2ND
    TABLE), WITH MATCHING ROWS IN THE LEFT TABLE(1ST     
    TABLE) AND NON MATCHING ROWS IN THE LEFT TABLE ARE
    
   NULL.
3. FULL JOIN
•   
  
  

  THE FULL JOIN SELECT ALL ROWS FROM BOTH TABLES

  
  

    WITH MATCHING ROWS IN BOTH TABLES     
    AND NON MATCHING ROWS IN BOTH TABLES ARE

  
  

     NULL.

     3.  CROSS JOIN OR CARTESIAN JOIN

• 
  

     4.  SELF JOIN.

• 
  

CONSTRAINTS 

CONSTRAINTS AVOID TYPE OF WRONG DATA  ENTER INTO TABLE.

SQL SUPPORTS FOLLOWING CONSTRAINTS

1. PRIMARY KEY
• PRIMARY KEY IS CONSTRAINT ,IT AVOID DUPLICATE DATA ENTER INTO TABLE.
• PRIMARY KEY IS UNIQUE ID FOR EACH ROW IN THE COLUMN.
• PRIMARY KEY DOES NOT ALLOW NULL VALUES.
• EX:
•  CREATE TABLE CUSTOMERS   
•  (
• ID INT PRIMARY KEY,                                                                               
•  NAME VARCHAR(40)                                                                                           
•  )   
•  TABLE CAN HAVE ONLY ONE PRIMARY KEY .                                                           
2. FOREIGN KEY
• FOREIGN IS PRIMARY KEY FOR OTHER TABLE.
• FOREIGN KEY IS USED TO CREATE RELATION BETWEEN THE TABLES.
• EX:
• CREATE TABLE CUSTOMERS
• (
• O_ID INT NOT NULL PRIMARY KEY,
• ORDERNO INT NOT NULL,
• C_ID INT FOREIGN KEY REFERENCES CUSTOMERS(C_ID)
• )
3. UNIQUE KEY
• UNIQUE IS SAME AS PRIMARY KEY BUT IT ALLOW ONE NULL VALUES.
• UNIQUE KEY IS UNIQUE ID FOR EACH ROW IN THE TABLE.
1. WE CAN HAVE MORE THAN ONE UNIQUE KEY IN THE TABLE.
• EX:
• CREATE TABLE CUSTOMERS    
•  (                                                                                                                           
•    ID INT UNIQUE ,      
• NAME VARCHAR(40) 
•   )
4. NOT NULL
5. CHECK
6. DEFAULT

REPLICATION

SQL SUPPORTS 3 TYPES OF REPLICATIONS.

1. SNAPSHOT REPLICATION. 
2. TRANSACTIONAL REPLICATION.
3. MERGE REPLICATION.

VIEWS

• VIEWS ARE MIRROR OF THE TABLE.
• VIEWS NOT WORKS WHEN BASE TABLES ARE DELETED.

SYNTAX:

CREATE VIEW VIEW_NAME
AS
SQL STATEMENTS

• ADVANTAGES OF VIEWS:
• DATA PROTECTION OR SECURITY.
• VIEWS HIDE SOME COLUMNS FROM USERS.
• CAN NOT DELETE,INSERT AND UPDATE VALUES OF VIEW,IF VIEW IS CREATED WITH MULTIPLE TABLE.
• IF VIEWS ARE CREATED WITH SINGLE TABLE THEN WE CAN DELETE,INSERT AND UPDATE VALUES OF VIEW AND DATA IS EFFECTED IN BASE TABLES.

EX:

CREATE VIEW VW_ORDERS
AS
SELECT O_ID,C_NAME,C_CITY,P_NAME FROM ORDERS
INNER JOIN CUSTOMERS
ON CUSTOMERS.C_ID=ORDERS.C_ID
INNER JOIN PROD
ON ORDERS.P_ID=PROD.ID
------------------------------------------------------------------------------------------

STORED PROCEDURES
SQL SUPPORT TWO TYPES OF STORED PROCEDURE

• SYSTEM STORED PROCEDURES

         EX: "SP_RENAMEDB" ,"SP_HELP".

         SP STANDS FOR STORED PROCEDURE. ALL SYSTEM STORED PROCEDURE           

         STARTS WITH "SP_".

• USER DEFINED STORED PROCEDURES
• STORED PROCEDURES ARE SET OF SQL STATEMENTS.
• REPEATEDLY USING RELATED SQL STATEMENTS ARE COMBINED AND FORMED AS STORED PROCEDURE.
•  SYNTAX:
• CREATE PROC PROC_NAME
• (
• //DECLARE PARAMETERS HERE
• )
• AS
• BEGIN
• //STATEMENTS HERE
• END
• ADVANTAGES OF STORED PROCEDURES
• REDUCES NETWORK TRAFFICS.
• RE-USABILITY OF CODE.
• INCREASES THE PERFORMANCE .
• DATA IS SECURE.
• MAINTENANCE IS EASY.

NORMALIZATION
NORMALIZATION IS PROCESS OF ORGANIZING DATA IN THE TABLE.
THEIR ARE 5 TYPES OF NORMALIZATION PROCESS.

1. 1NF
2. 2NF
3. 3NF
4. 4NF
5. 5NF

TRIGGERS

TRIGGERS ARE SPECIAL KIND OF STORED PROCEDURES .TRIGGERS ARE FIRED WHEN EVENTS OCCURS IN THE TABLE.

THEIR ARE TWO TYPES OF TRIGGERS

1. INSTEAD OF TRIGGERS.

• INSTEAD OF TRIGGERS FIRED BEFORE DML OPERATIONS SUCH AS INSERT, DELETE AND UPDATE.
• 
  

EX:

CREATE TRIGGER TR_INSTEADOF_DELETE

ON TT

INSTEAD OF DELETE

AS

BEGIN

PRINT 'CAN NOT DELETED DATA !'

END

--DELETE FROM TT WHERE ID=4

--SELECT * FROM TT

1. AFTER TRIGGERS/FOR TRIGGERS.

EXCEPTIONS
Begin Try
       SQL Statements
END TRY
Begin Catch
     Error Message;
End Catch

FUNCTIONS

SQL SUPPORT TWO TYPES OF FUNCTIONS 

1. SCALAR FUNCTIONS

• SELECT UPPER('upper case') as UPPER_CASE
• SELECT LOWER('lower case') as LOWER_CASE
• SELECT LEN('STRING') AS STRING_LENGTH 
• SELECT ROUND(10.77777,2) AS DECIMAL_ROUND_UP
• SELECT CONVERT(VARCHAR(20),GETDATE(),102) AS DATE_FORMATE

1. AGGREGATE FUNCTIONS. 

• MIN()
• MAX()
• SUM()
• COUNT()
• AVG()

1. Ranking Functions

• ROWNUMBER()
• RANK() 
• DENSE RANK() 
• NTILE()

INDEX

• INDEX HELPS QUERY TO FIND ROW QUICKLY.
• INDEXES ARE SAME AS INDEX IN BOOK.
• INDEX IMPROVE THE PERFORMANCE WITHOUT SCANNING ENTIRE TABLE DATA.
• INDEX STORED IN THE FORM OF B-TREE STRUCTURE.
• INDEX CAN BE CREATED ON SINGLE OR COMBINATION OF COLUMNS.
• 
  

TYPES OF INDEX

1. CLUSTERED INDEX.
• IN CLUSTERED INDEX DATA IN TABLE STORED AS PHYSICALLY STORED.
• TABLE CAN HAVE ONLY ONE CLUSTERED INDEX.
• CLUSTERED INDEX IS DEFAULT CREATED ON PRIMARY KEY.
• THE LEAF NODE OF CLUSTERED INDEX CONTAINS DATA PAGES.
• DATA IN C.I. ARE PHYSICALLY SORTED WHILE INSERTION OR UPDATING DATA.
• 
  
2. NON CLUSTERED INDEX. 
• THE DATA STORED IN TABLE ARE NOT MATCHED WITH PHYSICALLY STORED IN NON-CLUSTERED INDEX.
• DATA IS NOT SORTED IN NON-CLUSTERED INDEX.
• TABLE CAN HAVE MORE THAN ON NON-CLUSTERED INDEX.
• IN SQL SERVER 2008- TABLE CAN HAVE MAXIMUM 999 NON-CLUSTERED INDEX AND IN SQL SERVER 2005 ITS 249.
• LEAF NODE OF NON-CLUSTERED  INDEX CONTAINS INDEX ROWS.
• WE CAN CREATE NON-CLUSTERED INDEX ON FOREIGN KEY AND ALTERNATIVE KEYS.

INDEX TUNING:

• INDEX TUNING FIND RIGHT COLUMNS FOR NON-CLUSTERED INDEX.
• SELECTING NON-CLUSTERED INDEX BASED ON THAT ARE FREQUENTLY REFERENCED IN WHERE CLAUSE , GROUP BY AND JOINS.

SET Operators

• UNION
• UNION ALL
• EXCEPT
• INTERSECT

UNION

• UNION SELECT COLUMNS FROM TWO OR MORE TABLES.
• SELECT STATEMENTS MUST HAVE SAME NUMBER OF COLUMNS AND OF SAME DATA TYPE IN UNION. 
• UNION SELECT DISTINCT RECORD FROM BOTH TABLE.

EX:

SELECT CITY FROM INDIA

UNION 

SELECT CITY FROM US

UNION ALL

UNION SELECT ALL RECORDS FROM BOTH TABLE.

EX:

SELECT CITY FROM INDIA

UNION ALL

SELECT CITY FROM US

DATE TIME

TO GET LOCAL SYSTEM DATE YOU HAVE TO USE GETDATE() FUNCTION

EX 1: SELECT GETDATE() AS LOCAL_DATE

RESULT:

LOCAL_DATE

2016-04-10 20:41:55.710

EX 2:

DECLARE 

@datetime datetime ='12-21-05';

SELECT @datetime AS 'DateTime'

Result:

DateTime

2005-12-21 00:00:00.000

ISOLATION Levels

• Read committed
• Read Uncommitted
• Repeatable Read
• Serializble

BCP

bcp AdventureWorks.dbo.SalesPeople in C:\Data\SalesPerson.csv -c -T -t, -EDBCC

TEMPORARY  TABLES.

• TEMPORARY TABLE ARE SAME AS PERMANENT TABLES. 
• TEMPORARY TABLE ARE STORED IN "TEMPDB " (UNDER SYSTEM DATABASES).
• THESE TABLES CAN BE CREATED AT RUN TIME.
• TEMPORARY TABLE ARE USED TO HOLD RESULT SET IN STORED PRODUCER. 
• THEIR ARE TWO TYPES TEMPORARY TABLE.
• LOCAL TEMPORARY TABLES
• LOCAL TEMPORARY TABLES ARE CREATED SAME AS PERMANENT TABLE BUT STARTS WITH HASH(#).
• EX:
• CREATE TABLE #TEST
• (
• ID INT,
• NAME VARCHAR(45)
• )
• .
• LOCAL TEMPORARY TABLES ARE DROPPED WHEN CONNECTION IS ENDED. LOCAL  TEMPORARY TABLES ARE AVAILABLE ONLY THAT CONNECTION WHO HAS CREATED TEMP TABLES.
• DIFFERENT CONNECTIONS CAN CREATE LOCAL TEMPORARY TABLE WITH SAME NAME.
• LOCAL TEMPORARY TABLE CAN ALLOW ALL DML QUERIES.
• GLOBAL TEMPORARY TABLES
• GLOBAL TEMPORARY TABLES ARE CREATED SAME AS PERMANENT TABLE BUT STARTS WITH HASH(##).
• EX:
• CREATE TABLE ##TEST_GLOBAL
• (
• ID INT,
• NAME VARCHAR(45)
• )
• .
• GLOBAL TEMPORARY TABLES ARE AVAILABLE FOR ALL CONNECTION AND DROPPED WHEN LAST CONNECTION ENDS.
• GLOBAL TEMPORARY TABLES SHOULD BE UNIQUE NAME.
• 
  

CTE- COMMON TABLE EXPRESSION.

• CTE IS TEMPORARY RESULT  SET THAT CAN BE REFERENCED WITHIN DML STATEMENTS.
• TO CREATE CTE WE USE "WITH" KEYWORD.

SYNTAX FOR CTE:

WITH CTE_NAME(COL1,COL2...)

AS

(

CTE _QUERY

 )

EX: HOW TO USE CTE .

• WRITE A QUERY TO GET EMP_NAME AND MANAGER NAME 

EMPLOY TABLE:

QUERY:

WITH TEMP( EMP_NAME, MG_ID)

AS

(

SELECT EMP_NAME,MG_ID FROM EMP WHERE MG_ID IS NOT NULL

)

SELECT TEMP.EMP_NAME,EMP.EMP_NAME AS MG_NAME FROM EMP

INNER JOIN TEMP

ON EMP.ID=TEMP.MG_ID

RESULT:

 
IDENTITY

• IDENTITY IS AUTO INCREMENT FOR ROW ID.
• Auto-increment allows a unique number to be generated when a new record is inserted into a table.

EX:

CREATE TABLE EMP

(

ID INT PRIMARY KEY IDENTITY,

NAME VARCHAR(200)

)

LOG SHIPPING

CURSOR

SQL PROFILER

GROUP BY AND HAVING

ORDER BY

• ODER BY IS USED FOR SORTING  THE DATA.

EX:

SELECT * FROM EMP ORDER BY NAME ASC/DESC

IF AND ELSE IN SQL

SQL SERVER AGENT

@ AND @@

LOCK AND NO LOCK IN SQL

WHILE IN SQL

XML IN SQL

OLTP AND OLAP

• ONLINE TRANSACTION PROCESSING.
• ONLINE ANALYTICAL  PROCESSING.

DELETE AND TRUNCATE

• DELETE ALLOW WHERE CLAUSE TO USE. ALLOW TO DELETE SELECTED ROWS.
• TRUNCATE DELETE ALL THE ROWS FROM TABLE AND DOES NOT ALLOW WHERE CLAUSE.
• TRUNCATE IS FASTER COMPARED TO DELETE  BECAUSE TRUNCATE USES LESS RESOURCE.
• TRUNCATE RESETS IDENTITY.
• SYNTAX: 
• DELETE FROM TABLE_NAME.
• SYNTAX: 
• TRUNCATE TABLE TABLE_NAME.

FIND TABLE NAME FROM SYSOBJECTS

THIS BELOW SQL STATEMENT GIVES U TABLE NAME FROM DATABASE IF EXIST.

SELECT [NAME] FROM SYSOBJECTS WHERE TYPE = 'U' AND [NAME] = 'ACCOUNTS'

TYPE U STANDS FOR USER TABLE

O/P

NAME

ACCOUNTS