SQL Means Structured Query Language

        SQL is the standard language for relational database management systems. It is used to perform operations on the data present in database. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.
                                 

Most Important SQL Commands:-

CREATE DATABASE:- Creates a new database

Syntax:-CREATE DATABASE databasename;

CREATE TABLE :- Creates a new table

Syntax:- CREATE TABLE table Name(

columnName1 datatype,

columnName2 datatype,

.......

.......

);

SELECT: -  Extracts data from a database

Syntax: - Select  columnName1, columnName2, ... from  table Name;

UPDATE:-  Updates data in a database

Syntax: - Update  table Name  set column Name1=Value1, column Name2=value2  .... where condition;

DELETE:- Remove  rows from table and where clause can be used for conditional statement  commit and rollback can performed  after delete. 

Syntax:- Delete  FROM tableName where condition;

TRUNCATE :- Remove all rows from table truncate operation cannot rolled back.

Syntax:- Truncate TABLE  tableName ;

INSERT INTO:-  Inserts new data into a database

Syntax:- Insert into   tableName (columnName1,columnName2......) values(Value1,Value2.....);

ALTER TABLE:-  Modifies a table

Syntax:- ALTER TABLE tableName ADD columnName datatype;

DROP DATABASE:- Deletes a Database

Syntax:- DROP DATABASE databasename;

DROP TABLE :- Remove table from the database and operation cannot be rolled back.

Syntax:- DROP TABLE tableName;

Order format for SQL:-

SELECT  from [ WHERE], [GROUP BY], [HAVING] and [ORDER BY]

Example:- SELECT State, Count(CustomerID) As NumberOfCustomers FROM Customers WHERE State <> "Delhi" GROUP BY State HAVING Count(CustomerID) >10 ORDER BY Count(CustomerID) DESC

Order of procedure (Not,AND,OR,IN)

Most Important SQL Joins:-

INNER JOIN:-  selects records that have matching values in both tables.

Syntax:- SELECT columnname(s) FROM table A  INNER JOIN table B ON table A.columnname = table B.columnname;

LEFT JOIN:-Match row from both the table and unmatched row from the first table(table A)

Syntax:- SELECT columnname(s) FROM table A  LEFT JOIN table B ON table A.columnname = table B.columnname;

RIGHT JOIN :- Match row from both the table and unmatched row from the second table(table B).

Syntax:- SELECT columnname(s) FROM table A RIGHT JOIN table B ON table A.columnname = table B.columnname;

FULL JOIN:-Return all records when there is a match in either left (table A) or right (table B) table records.

Syntax:-SELECT columnname(s) FROM table A  FULL  JOIN table B ON table A.columnname = table B.columnname;

Self JOIN:- When one table join to it self

Syntax:-SELECT columnname(s) FROM table A T1, table A T2 WHERE condition;

UNION:- Combine the result-set of two or more SELECT statements.

Syntax:- SELECT columnname(s) FROM table A UNION SELECT columnname(s) FROM table B;

GROUP BY :- Used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

Syntax:- SELECT columnname(s) FROM tableName WHERE condition GROUP BY columnname(s)

ORDER BY columnname(s);

HAVING:-The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Syntax:-SELECT columnname(s) FROM tablename WHERE condition GROUP BY columnname(s) HAVING condition ORDER BY columnname(s);

EXISTS:- Used to test for the existence of any record in a subquery.

Syntax:- SELECT columnname(s) FROM tablename  WHERE EXISTS  (SELECT columnname FROM tablename WHERE condition);

Find duplicate Record:-

Select columnname from tablename  GROUP BY  columnname HAVING  count (columnname) >1

Row Number Function:-

ROW_NUMBER:-This function will assign a unique id to each row return from query

FOR Example:-

Rank:-This function will assign a unique number to each  distinct row out, it leave gap between the group.

FOR Example:-

Dense_Rank:-This function is similar to rank with only difference this will not leave gaps between groups.

FOR Example:-

Tips to Improve SQL Query Performance:-

• Don't blindly reuse code This issue is also very common. It's very easy to copy someone else's code because you know it pulls the data you need.
• Don't double-dip
• Don't use UPDATE instead of CASE
• Do pull only the number of columns you need
• Do know when to use temp tables
• Do pre-stage data
• Do delete and update in batches
• Create and use Indexes.
• Return only the rows and column  needed(Avoid * in SELECT statement)
• Use joins instead of sub-queries.
• Use Stored Procedure for frequently used data and more complex queries.

Using MERGE in SQL  to insert, update and delete at the same time:-

MERGE Statement in SQL :- You can perform Insert, Update and Delete operation in a single statement.

Merge statement allows you join a data source with a target table or view and then perform multiple action against the target based.

Syntax:-

MERGE into <Target Table>

Using <Source Table>

ON <Join/Merge Condition>

WHEN Matched<statement run when match found>

THEN <Update Statement>

WHEN NOT MATCHED BY TARGET<statement run when not match fount>

THEN <Insert Statement>

WHEN NOT MATCHED BY SOURCE

THEN <Delete Statement>

            

For Example:-Create a target and source table

•  Create Table  S_Source ( Id INT,  fname  VARCHAR (100),  amt INT);
• Create Table  T_Target ( Id INT,  fname  VARCHAR (100),  amt  INT);

MERGE into T_Target AS T  

  USING  S_Source AS S    

      ON T.Id = S.Id  

  WHEN MATCHED AND (T.fname != S.fname OR T.amt != S.amt THEN

  -- -- Row exists and data is different

        UPDATE SET T.fname = S.fname, T.amt = S.amt  

  WHEN NOT MATCHED THEN

      -- --Row exists in source but not in target

        INSERT VALUES (S.Id, S.fname, S.amt)  

  WHEN SOURCE NOT MATCHED THEN

       -- --Row exists in target but not in source

        DELETE OUTPUT $action, inserted.Id, deleted.Id