T-SQL for SQL Server Cheat Sheet

In short T-SQL is an extension of SQL language.

T-SQL(Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables

Databases

-- list all databases
SELECT Name from sys.Databases

-- use a certain db
USE TestDB

CREATE DATABASE TestDB

RESTORE DATABSE TestDB from disk = '\~/Downloads/BackupDB.bak'

BACKUP DATABSE TestDB to disk = '\~/Downloads/TestDB_Full.bak'

DROP DATABASE Testdb

Users

CREATE USER TestUser for login TestLogin

Permission

Grant select on TestTable to TestUser

Tables

CREATE TABLE CUSTOMERS( 
   ID   INT              NOT NULL, 
   NAME VARCHAR (20)     NOT NULL, 
   AGE  INT              NOT NULL, 
   ADDRESS  CHAR (25) , 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID));

DROP TABLE table_name;

CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)

inserting Statement

INSERT INTO Inventory VALUES (1, 'banana', 150);
INSERT INTO Inventory VALUES (2, 'orange', 154);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO first_table_name  
   SELECT column1, column2, ...columnN  
      FROM second_table_name 
      [WHERE condition];

SELECT Statement

-- list column wanted
SELECT column1, column2, columnN FROM table_name;
SELECT ID, NAME, SALARY FROM CUSTOMERS; 


-- all column
SELECT * FROM table_name;
SELECT * FROM Inventory;

UPDATE Statement

-- syntax
UPDATE table_name 
SET column1 = value1, column2 = value2...., columnN = valueN 
WHERE [condition];

-- actual
UPDATE CUSTOMERS 
SET ADDRESS = 'Pune' 
WHERE ID = 6; 


-- modify all 
UPDATE CUSTOMERS 
SET ADDRESS = 'Pune', SALARY = 1000.00;

DELETE Statement

-- syntax
DELETE FROM table_name 
WHERE [condition]; 


-- delete one row that fits condition in where clause
-- actual 
DELETE FROM CUSTOMERS 
WHERE ID = 6;



-- DELETE all
DELETE FROM CUSTOMERS;

WHERE Clause

SELECT column1, column2, columnN  
FROM table_name 
WHERE [condition]


-- actual
-- by int comparison
SELECT ID, NAME, SALARY  
FROM CUSTOMERS 
WHERE SALARY > 2000;


-- actual
-- by string comparison
SELECT ID, NAME, SALARY  
FROM CUSTOMERS 
WHERE NAME = 'Hardik';

LIKE Clause

compare a value to similar values using wildcard operator

There are two wildcards used in conjunction with the LIKE operator −

percent sign (%)
underscore (_)

-- select
SELECT * FROM table_name 
SELECT column-list FROM table_name 

-- then

-- Finds any values that start with 200
WHERE column LIKE 'XXXX%' 
WHERE SALARY LIKE '200%'
 

-- Finds any values that have 200 in any position
WHERE column LIKE '%XXXX%'  
WHERE SALARY LIKE '_00%'


-- Finds any values that have 00 in the second and third positions
WHERE column LIKE 'XXXX_'  
WHERE SALARY LIKE '_00%'

-- Finds any values that start with 2 and are at least 3 characters in length
WHERE SALARY LIKE '2_%_%'



-- Finds any values that have a 2 in the second position and end with a 3
WHERE column LIKE '_XXXX'  
WHERE SALARY LIKE '_2%3'

-- Finds any values that end with 2
WHERE SALARY LIKE '%2'


-- Finds any values that have a 2 in the second position and end with a 3
WHERE SALARY LIKE '_2%3'


-- Finds any values in a five-digit number that start with 2 and end with 3
WHERE column LIKE '_XXXX_' 
WHERE SALARY LIKE '2___3'

Indexes

-- Single-Column Indexes
CREATE INDEX index_name ON table_name
CREATE INDEX singlecolumnindex ON customers (ID)



-- Unique Indexes
CREATE UNIQUE INDEX index_name on table_name (column_name)
CREATE UNIQUE INDEX uniqueindex  on customers (NAME)


-- Composite Indexes
CREATE INDEX index_name on table_name (column1, column2) 
CREATE INDEX compositeindex on customers (NAME, ID)

DROP INDEX Command

DROP INDEX tablename.index_name

alphaolomi/tsql_cheat.md