Basic Keywords,Funtions,Joins in SQL

Hi Friends .. Take a look ..Hope you will like it


Refrence Northwind database

--Select
--------------
select * from Categories
select * from Suppliers
Select * from Products

--InnerJoin (inner join is same as Join )
------------------------------------------
Select prod.ProductID,prod.ProductName,prod.UnitPrice,sup.CompanyName,sup.City From Products as prod
inner join   Suppliers as sup 
on  prod.productId = sup.SupplierId

--Left Join 
------------------------------------------
Select prod.ProductID,prod.ProductName,prod.UnitPrice,sup.CompanyName,sup.City From Products as prod
left join   Suppliers as sup 
on  prod.productId = sup.SupplierId
--Right Join 
------------------------------------------
Select prod.ProductID,prod.ProductName,prod.UnitPrice,sup.CompanyName,sup.City From Products as prod
Right join   Suppliers as sup 
on  prod.productId = sup.SupplierId
--Full Join 
------------------------------------------
Select prod.ProductID,prod.ProductName,prod.UnitPrice,sup.CompanyName,sup.City From Products as prod
Full join   Suppliers as sup 
on  prod.productId = sup.SupplierId
--Self Join 
--A self-join is a query in which a table is joined (compared) to itself. 
-----------------
Select emp.EmployeeId ,emp.FirstName ,emp.ReportsTo from Employees as emp
Select emp.EmployeeId ,emp.FirstName ,emp.ReportsTo ,emp1.FirstName as Mgr from Employees as emp
 Left outer join  employees as emp1
on  emp1.EmployeeId =emp.ReportsTo 

--UNION
--The SQL UNION Operator
--The UNION operator is used to combine the result-set of two or more SELECT statements.
--Notice that each SELECT statement within the UNION must have the same number of columns. 
--The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
------------------------------------------
Select CategoryId,CategoryName from Categories
Union 
Select SupplierId,CompanyName from Suppliers

--Select Into
-- use to Create Temp data in temp Table 
-- Temp table is started By '# ' Keyword Like #TableName
--------------------------------------------------------
Select * Into Categories in anilTest
from Categories
drop Table #Categories1

--Select Into
-- use to Create Temp data in temp Table 
-- Temp table is started By '# ' Keyword Like #TableName
--------------------------------------------------------
Select * Into Categories in anilTest
from Categories
drop Table #Categories1

select * Into #Categories 
From Categories
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
select * from #Categories 
--Constraint
--------------------------------------------------------
Alter Table #Categories
Add Unique (Categoryid)

Alter Table #Categories
Drop Constraint Unique Categoryid
ALTER TABLE #Categories
DROP PRIMARY KEY
--Nth Highest salary
------------------------
SELECT distinct emp1.Extension from Employees emp1
Where 3 =(Select Count (Distinct(emp2.Extension))From Employees emp2 Where  emp2.Extension<= emp1.Extension)
---
Select distinct Top 1 Extension From (Select distinct Top 3 Extension From Employees Order by  Extension Desc) Employees Order by Extension Asc

--SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions
--------------------------------------------------------
Select * from Suppliers
SELECT CompanyName,ContactName*(City+ISNULL(Country,0))FROM Suppliers
--DateTime Functions
--Function         Description
--GETDATE()     Returns the current date and time
--DATEPART()     Returns a single part of a date/time
--DATEADD()     Adds or subtracts a specified time interval from a date
--DATEDIFF()     Returns the time between two dates
--CONVERT()     Displays date/time data in different formats
---------------------------------------------------------------

--Some other Funtion 
-----------------------
SELECT FORMAT(column_name,format) FROM table_name