SQL SERVER – Sub Query

          SQL SERVER – Sub Query

SUB QUERY:-

                              SubQuery is nothing but Query within a Query (or) it is a part of a Query.

There are 3 types of Sub queries exists in SQL.

1.SINGLE-VALUED SUB QUERY:-

                                      If we get a single value as an output from a Sub query of a Query then it is called as Single Valued Sub query.

Example:

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

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)

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)

To get info of maximum salary employee:

SELECT * FROM EMP

WHERE SAL= (SELECT MAX(SAL) FROM EMP ) 

2.MULTI-VALUED SUB QUERY

                                       If we get multiple values as an output from a Sub query of a Query then it is called as Multi-Valued Sub query.

Example:-
              To retrieve info of all employees whose sal is more than 50000

SELECT * FROM EMP

WHERE SAL IN (SELECT SAL FROM EMP WHERE SAL>50000)

3. CO-RELATED SUB QUERY

                                     If both Query and Sub query runs parallel that means both Query and Sub query depends on each other’s output then it is called as Co-related Sub query. 

Example:-

-- To retrieve info of second maximum salary employee

SELECT * FROM EMP E1

WHERE 2=(SELECT DISTINCT COUNT(*) FROM EMP E2 WHERE E1.SAL<=E2.SAL)

-- To retrieve info of second minimum salary employee

SELECT * FROM EMP E1

WHERE 2=(SELECT DISTINCT COUNT(*) FROM EMP E2 WHERE E1.SAL>=E2.SAL)

SQL SERVER – Table Variables

             SQL SERVER – Table Variables             

VARIABLES:-

                Variables are used to store data temporarily only for a single session. While declaring a Variable we have to place @ prior to variable name.

SCOPE:- 
         we have to execute from the Declare statement to till end then only commands will execute otherwise we will get error during execution. So we use Variables only with in a created session

Example:-

DECLARE @A INT

SET @A=100

TABLE VARIABLES:-

                         Like a Variable Table Variable is also used to store data temporarily. If you declare a Variable with Table datatype then it is called as Table Variable.

SCOPE:- 
           we have to execute from the Declare statement to till end then only commands will execute otherwise we will get error during execution. So we use Variables only with in a created session

.

Example:-

DECLARE @Table_variable TABLE(ID INT , SAL MONEY, LOC VARCHAR(100))

SELECT * FROM @Table_variable

DML Operations:-

                 We can perform all DML operations on Table Variables.

Example:-

DECLARE @Table_variable TABLE(ID INT , SAL MONEY, LOC VARCHAR(100))

INSERT INTO @Table_variable  Values(23, 23000, 'BANGALORE')

INSERT INTO @Table_variable  Values(24, 23000, 'CHENNAI')

INSERT INTO @Table_variable  Values(25, 23000, 'HYDERABAD')

SELECT * FROM @Table_variable

SQL SERVER -- Temp Tables

SQL SERVER -- Temp Tables

                                                        Temp tables are used to store the data temporarily. We can perform DML operations on temp tables as well. 

There are two types of Temp tables available.

1. LOCAL TEMP TABLES:-

                               If we place single # prior to the table name while defining then it is known as local temp table.

SCOPE:- We can use local temp tables only with in a created session

Example:-

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

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)

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)

CREATE TABLE DEPT

(

 DEPTID INT,

 DEPTNAME VARCHAR(100)

)

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

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

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

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

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

To retrieve employee info like id, name, location, dept name from both tables:

SELECT A.EMPID, A.EMPNAME,B.DEPTNAME, A.LOC INTO #LOCAL_TEMP_TABLE

FROM EMP AS A INNER JOIN DEPT AS B

ON A.DEPTNO = B.DEPTID

SELECT * FROM #LOCAL_TEMP_TABLE

                       

DML OPERATIONS:-

                   We can perform any DML operations on local temp tables.

2. GLOBAL TEMP TABLES

 

                               If we place double # prior to the table name while defining then it is known as Global temp table.

SCOPE:- We can use Global temp tables in a multiple sessions of a single login page.

EXAMPLE:-

SELECT A.EMPID, A.EMPNAME,B.DEPTNAME, A.LOC INTO ##GLOBAL_TEMP_TABLE

FROM EMP AS A INNER JOIN DEPT AS B

ON A.DEPTNO = B.DEPTID

DML OPERATIONS:-

                 We can perform any DML operation on Global table as well.