DECLARE my_cursor CURSOR  
    FOR SELECT * FROM TableName
OPEN my_cursor   
FETCH NEXT FROM my_cursor; 

DECLARE @find varchar(30); 
DECLARE @fName AS varchar(30);

-- With Initial Value
DECLARE @isAllowed bit = 0;
DECLARE @addedBy varchar(30) = NULL;

-- Multiple Declarations
Declare @lastName varchar(30), 
        @address varchar(50), 
		    @gender varchar(8);

 function getPrintedValues(p_string varchar2)
 return varchar2
 is
  l_new_string varchar2(32700);
  l_redex      varchar2(50) := '[^ -~]';
 begin
  SELECT REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(p_string, l_redex, ''), '[CR-LF]', '' ),chr(9),''),chr(10),'8')
    into l_new_string
    from dual;
   return l_new_string;
 end;

----------------------------

     select  trim('	' FROM trim( '	PC FDC Warehouse' )) from dual -- ok
     /
     select  trim(CHR(9) FROM trim( '	PC FDC Warehouse' )) from dual
     /
     select trim( '	PC FDC Warehouse' ) from dual
     /
     select  ASCIISTR( '	PC FDC Warehouse' ) from dual
     /
     SELECT translate(
           translate(
               translate('	PC FDC Warehouse', CHR(10), '')
           , CHR(13), '')
       , CHR(09), '') as massaged
FROM dual;
/
SELECT REGEXP_REPLACE('	PC FDC Warehouse', '[^ -~]','') from dual
/
select wims_aop_qry_rep_pkg.getPrintedValues('	PC FDC Warehouse') from dual
/
select dump('	PC FDC Warehouse') from dual;
/
select trim(replace(replace(replace('	PC FDC Warehouse',chr(9),''),chr(10),''),chr(13),'')) from dual

-------------------------------------

SELECT email, count(*) FROM some.table group by email having count(*) > 1;

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Show Inserted tables after insertion
INSERT TableName (Col1,Col2)
OUTPUT Inserted.*
VALUES( Val2,Val2)

-- Show New And Old Data during update
UPDATE Table1
SET Col1 = 'NewValue'
    OUTPUT deleted.Col1 AS OldData, 
           inserted.Col1 AS NewData
WHERE SomeCol = 'SomeData'

-- With Table Variables, Sample
DECLARE @DeletedData Table (
     SupplierID UNIQUEIDENTIFIER,
     ProductID UNIQUEIDENTIFIER
);

DELETE Supplier 
    OUTPUT deleted.ProductID, deleted.SupplierID
	INTO @DeletedData
	WHERE ContactID = '5';

SELECT *FROM @DeletedData;

DELETE FROM TableName;

DELETE FROM TableName WHERE <where-condition>;

-- Using Sub query
DELETE FROM dbo.Table1 T1
WHERE EXISTS (SELECT 1 
        FROM Table2 T2
        WHERE T1.Col1 = T2.Col1)
 
-- Using a Join
DELETE Supplier
   FROM Supplier
   JOIN Contact
	 ON Supplier.ContactID = Contact.ContactID
	 WHERE Contact.FirstName= 'Terri';
   

-- Building a foreign key with referential integrity
-- that cascades deletes

-- SAMPLE MAPPING TABLE
CREATE TABLE dbo.BlackListedSuppliers (
   BlackListedSupplierID INT IDENTITY NOT NULL
            PRIMARY KEY NONCLUSTERED,
   SupplierID UNIQUEIDENTIFIER NOT NULL
    FOREIGN KEY REFERENCES dbo.Supplier 
            ON DELETE CASCADE,
   CONTACTID  UNIQUEIDENTIFIER NOT NULL
    FOREIGN KEY REFERENCES dbo.Contact
            ON DELETE CASCADE,
)
 ON [PRIMARY]

-- NOW IF A PRIMARY ROW IS DELETED FROM 
-- THE MAIN TABLE > SUPPLIER, THE FOREIGN KEY
-- WILL ALSO DELETE THE SECONDARY ROW IN 
-- THE SECONDARY TABLE 

-- Sample 1 : Simple Update
UPDATE TableName
  SET Col1 = 'val1',
      Col2 = 'val2'
  WHERE <where-condition>
  
-- Sample 2
Update Table1 SET Col1 = 'val1'
    FROM Table1
	  JOIN Table2
	  ON Table1.Col123 = Table2.Col123

-- With Column Names
INSERT INTO TableName (ColumnNames, ...) 
       VALUES (values, ...);

-- Without Column Names
INSERT INTO TableName 
       VALUES (AllColumnValues, ...);

-- From One Table to Another
INSERT TableName (ColumnNames, ...) 
    SELECT ColumnNames 
    FROM [Database].[Schema].[TableName];
    
-- FROM A STORED PROCEDURE
INSERT TableName (ColumNames, ...) 
    EXEC StoredProcedureName;

-- Select Into
-- Bypasses the transaction log
-- Functions as a bulk logged behavior
-- Creates a table on the fly
ALTER DATABASE databaseName SET RECOVERY FULL
EXEC SP_DBOPTION 'DatabaseName', 
     'select into/bulkcopy', 'TRUE';

SELECT * INTO dbo.NewTable
  FROM OtherTable
  ORDER BY Column1,Column2


-- TO INSERT WITH IDENTITY COLUMNS >>

-- TURN OFF AUTOMATIC IDENTITY
SET IDENTITY_INSERT TableName ON;

INSERT INTO TableName (ID, Columns, ...)
VALUES (1, values, ...);

-- TURN ON  AUTOMATIC IDENTITY
SET IDENTITY_INSERT TableName OFF;

     SELECT
      ARRAY[sum(colAlias),sum(case when colAlias > 0 then 1 else 0 end)] as "Alias"
      FROM
      (SELECT useruuid,
      SUM(case when ${queryCOndition} then 1 else 0 end) "colAlias"
      FROM "database"."table"
      where
      eventname='EVENT_NAME'
      GROUP BY useruuid)