Database SQL Concepts & key concepts to learn #inprogress

1. Constraints

2. Data Types

3.Diff between where and having clause

1.	WHERE Clause is used to filter the records from the table based on the specified condition.	HAVING Clause is used to filter record from the groups based on the specified condition.
2.	WHERE Clause can be used without GROUP BY Clause	HAVING Clause cannot be used without GROUP BY Clause
3.	WHERE Clause implements in row operations	HAVING Clause implements in column operation
4.	WHERE Clause cannot contain aggregate function	HAVING Clause can contain aggregate function
5.	WHERE Clause can be used with SELECT, UPDATE, DELETE statement.	HAVING Clause can only be used with SELECT statement.
6.	WHERE Clause is used before GROUP BY Clause	HAVING Clause is used after GROUP BY Clause
7.	WHERE Clause is used with single row function like UPPER, LOWER etc. SELECT COUNT(CustomerID), Country FROM Customers where Country='Brazil' or Country!='France' GROUP BY Country HAVING COUNT(CustomerID) > 5;	HAVING Clause is used with multiple row function like SUM, COUNT etc. Below result first group by Country then filter from group data. SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

4.Temp table Vs CTE (common table expression) Vs Table variable

Temp tables: are created in tempdb, it contains two types #local & ##Global

These tables act as the normal table and also can have constraints, an index like normal tables.

#local temp tables available only for within session alone.

CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Shailendra','Noida');

##Global temp tables can access to all sessions.

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from ##GlobalTemp

CTE: CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than the Tempdb database. we cannot create an index on CTE 

CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with a semicolon.

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME

Table Variable: This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of the batch. This is also created in the tempdb database but not the memory. This also allows you to create a primary key, identity at the time of Table variable declaration but not non-clustered index.

GO
 DECLARE @TProduct TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 Qty INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID,Qty)
 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
 --Select data
 Select * from @TProduct
 
 --Next batch
 GO
 Select * from @TProduct --gives error in next batch

5.Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

6.ACID: properties

A- Atomicity : fail or success it should apply for all transaction not partial.

7. Joins

    i. Inner Join : retrieve only matched rows between tables

    ii. Outer Join : retrieve only Not matched rows between tables

    iii. Left Join : Table A Left join Table B => Retrieve all from Table A and Only Matched rows                     From  Table B.

    iv. Right Join: Vice versa of Left join

8. Union(without duplicate rows) Vs Union All (include duplicate rows)