Sunday 18 June 2017

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.








  

Thursday 29 December 2016

SQL SERVER -- Conversion Functions-- Part-21

SQL SERVER -- Conversion Functions-- Part-21

                   Conversion Functions are used to convert the data from one data type to another.

There are 2 types of CONVERT Functions available.

1. CAST:-  

-- CAST
-- CAST Function is used to convert the data from one data type to another data type
-- Example -1
SELECT CAST('56543.7897' AS FLOAT) AS [Float_Val],
       CAST('56543.7897' AS DECIMAL) AS [Decimal_Val],
       CAST('56543.7897' AS DECIMAL(7,2)) AS [Decimal_Value_with_length],
       CAST('01/13/2012' AS DATETIME) as [Date_Val]

 -- Example -2
DECLARE @A INT
DECLARE @B VARCHAR(100)
SET @A=100
SET @B='The Given Number is: '
--SELECT @B+@A AS TOTAL

SELECT @B+CAST(@A AS VARCHAR) AS TOTAL



2. CONVERT:-  

-- CONVERT
-- CONVERT Function is used to convert the data from one data type to another data type
-- Example -1
SELECT CONVERT(FLOAT, '56543.7897') AS [Float_Val],
       CONVERT(DECIMAL, '56543.7897') AS [Decimal_Val],
       CONVERT(DECIMAL(7,2), '56543.7897') AS [Decimal_Value_with_length],
       CONVERT(DATETIME,'01/13/2012') AS [Date_Val]


 -- Example -2
DECLARE @A INT
DECLARE @B VARCHAR(100)
SET @A=100
SET @B='The Given Number is: '
--SELECT @B+@A AS TOTAL

SELECT @B+CONVERT(VARCHAR,@A) AS TOTAL






Thanks for Looking into this.....