SQL SERVER -- Query Basics-- Part-15

Query Basics:-

                                                    Lets see how to pull data from Tables using basic SQL Query

SELECT :-  
                 Used to Retrieve data from the Table OR View

How to Retrieve all the columns from the Table:- 

SELECT * FROM EMP

How to Retrieve Specific(Selected) columns from the Table:- 

SELECT EMPNAME

      ,SAL

      ,LOC

FROM EMP

WHERE :- 
                Where Clause is Used to filter the data based any column.

SELECT *

FROM EMP

WHERE LOC='Bangalore'

GROUP BY :-   

                    Group By Clause is Used to Group the data and perform       Aggregate Operations like Totals and sub totals.

SELECT LOC,COUNT(EMPID) AS EmpCount

FROM EMP

GROUP BY LOC

ORDER BY :-   
                     Order by Clause is Used to Sort the data either in ascending OR Descending order based on any column.

SELECT *

FROM EMP

ORDER BY EMPNAME DESC

SELECT *

FROM EMP

ORDER BY EMPNAME ASC

HAVING :-   
                     Having Clause is Used filter Aggregated Data.

  SELECT LOC,COUNT(EMPID) AS EmpCount

FROM EMP

GROUP BY LOC

HAVING COUNT(EMPID)=1

SQL SERVER -- Table BackUp-- Part-14

Description:-

                                               When we working with Production Data Base Tables always good practice to take Table backup and do the manipulations.
If any things goes wrong then we can re-populate the data from backup table to actual tables.

How to Take Table Backup along with Data :- 

SELECT *

INTO EMP_20160325_BKP  /* New Table Name */

FROM EMP              /* Actual(Old) Table Name */

WHERE 1=1

-- Verify

SELECT * FROM EMP_20160325_BKP

How to Take Table Backup without Data, Only Table Structure :- 

SELECT *

INTO EMP_TBL_STRUCT /* New Table Name */

FROM EMP            /* Actual(Old) Table Name */

WHERE 1=2

-- Verify

SELECT * FROM EMP_TBL_STRUCT

How to Insert data from one Table to Another table :- 

INSERT INTO EMP_TBL_STRUCT

SELECT * FROM EMP

-- Verify

SELECT * FROM EMP_TBL_STRUCT

SQL SERVER -- What is NULL -- Part-13

What is NULL:-

                                               NULL Value nothing but a Unknown Value.

The Value of an NULL is different from am empty OR Zero value.

No two NULL values are equal.

Comparisons between two NULL values will return unknown because the value of each NULL is different.

USE TEMPDB

GO

-- How to Create New Table

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT
      ,RelievedDate DATE
      

)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(1,'Santhosh',35000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO EMP

VALUES(2,'Kumari',30000,'11-05-2013','Chennai','F',3,104,'2015-01-01')

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO EMP(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)

VALUES(4,'Arun',44000,'Hyderabad','m',5,103)

INSERT INTO EMP(EMPID, EMPNAME, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(5,'Aishwarya','01-12-2014','Bangalore','f',3,103)

How to Display all the Records in a Table:- 

How to Display Only NULL Records:- 

How to Display Other Than NULL Records:- 

                   

SQL SERVER -- JOINS -- Part-12

JOINS:-

                          Joins are used to retrieve data from two or more tables or views based on some common columns.

Types Joins:-

•   Inner Join
•   Left Outer Join
•   Right Outer Join
•   Full Outer Join
•   Cross Join
•   Self Join

            -- How to Create New DataBase

CREATE DATABASE SQLSERVER

GO

-- How to go inside to the Database

USE SQLSERVER

GO

-- How to Create New Table

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

-- INSERT Sample Data Into the Table.

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(1,'Santhosh',10000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO EMP

VALUES(2,'Kumari',8000,'11-05-2013','Chennai','F',3,104)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO EMP(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)

VALUES(4,'Arun',44000,'Hyderabad','m',5,103)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(5,'Aishwarya', 15000, '01-12-2014','Bangalore','f',3,103)

-- Creaeting Dept Table

CREATE TABLE DEPT

(

  DEPTID INT PRIMARY KEY

 ,DEPTNAME VARCHAR(50)

)

-- INSERT SOME SAMPLE DATA INTO Dept Table.

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(101,'HR')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(102,'TESTING')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(103,'DEVOLOPMENT')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(104,'ADMIN')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(105,'FINANCE')

-- Verify the data from the EMP and DEPT Tables

SELECT * FROM EMP

SELECT * FROM DEPT

INNER JOIN :- 

                         Inner Join is used to retrieve matching records from both the tables based on common column.

--INNER JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

        FROM EMP AS E INNER JOIN DEPT AS D

        ON E.DEPTNO=D.DEPTID

LEFT OUTER JOIN :-

                                 Left Outer Join is used to retrieve all the records from Left Side table and matching records from right side table based on common column.

--LEFT OUTER JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

        FROM EMP AS E LEFT OUTER JOIN DEPT AS D

        ON E.DEPTNO=D.DEPTID

RIGHT OUTER JOIN :-

                                              Right Outer Join is used to retrieve all the records from Right Side table and matching records from Left side table based on common column.

--RIGHT OUTER JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

        FROM EMP AS E RIGHT OUTER JOIN DEPT AS D

        ON E.DEPTNO=D.DEPTID

FULL OUTER JOIN :-

                                         Full Outer Join = Left Outer Join + Right Outer Join

--FULL OUTER JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

        FROM EMP AS E FULL OUTER JOIN DEPT AS D

        ON E.DEPTNO=D.DEPTID

SELF JOIN :-

                           Self Join is nothing but joining the same table more than once to get required information.

--SELF JOIN

     SELECT E1.EMPNAME AS 'ManagerName'

              ,E2.EMPNAME AS 'EmployeeName'

        FROM EMP AS E1 LEFT JOIN EMP AS E2

        ON E1.EMPID=E2.MAGRID

CROSS JOIN :-

                               If we are receiving data from more than one table with out any join condition then called as Cross Join OR Cartesian Product.
   If a Table contains M record and another Table contains N records, then Cartesian Product will retrieve M*N records.

--CROSS JOIN

     SELECT E.EMPNAME

              ,D.DEPTNAME

     FROM EMP AS E,DEPT AS D