Monday, August 5, 2013

T-SQL Queries

T-SQL Queries


1. EMPLOYEE


--CREATING TABLE
CREATE TABLE Employee (EID INT PRIMARY KEY, EName VARCHAR(10),Salary INT)

--INSERTING RECORDS
INSERT INTO Employee (EID, EName, Salary)
VALUES (1, 'John', 20000)
,(2, 'Rob', 2000)
,(3, 'Bob', 10000)
,(4, 'Robert', 20300)
,(5, 'Bruce', 40000)
,(6, 'Williams', 5000)
,(7, 'Megan', 900)
,(8, 'Steven', 9000)
,(9, 'George', 12500)
,(10, 'Alex', 14000)

A.) List employees who are earning more than 10000

SELECT  EName
FROM    Employee
WHERE   Salary > 10000

B.) List employees whose name starts with ‘R’

SELECT  EName
FROM    Employee
WHERE   EName LIKE 'R%'

C.) List employees whose length of the name is more than 4

SELECT  EName
FROM    Employee
WHERE   LEN(EName) > 4

D.) List employees who are earning between 8000 and 20000 and whose name starts with G or A

SELECT  EName
FROM    Employee
WHERE   Salary BETWEEN 8000 AND 20000
        AND (EName LIKE 'G%' OR EName LIKE 'A%')

E.) List employees who born in 1985

SELECT  EName
FROM    Employee
WHERE   DATEPART(YYYY,DOB) = 1985

F.) List employees who born in March moth

-- Option : 1
SELECT  EName
FROM    Employee
WHERE   DATEPART(MM,DOB) = 3 -- MARCH

-- Option : 2
SELECT  EName
FROM    Employee
WHERE   DATENAME(MM,DOB) = 'March'


G.) List employees who born after 1990 or earning more than 20000

SELECT  EName
FROM    Employee
WHERE   DOB > '1990-12-31'

        OR Salary > 20000

-------------------------------------------------------------------------------------------


2. EMPLOYEE - PHONE


--CREATING TABLE
CREATE TABLE Phones (PhoneID INT PRIMARY KEY IDENTITY, EID INT,Phone VARCHAR(10) )

-- INSERTING RECORDS

INSERT INTO Phones (EID, Phone)
VALUES
 (1, '123456789')
,(2,'4597861356')
,(3,'6556454656')
,(2,'7878979899')
,(6,'7845454656')
,(1,'1235465654')
,(3,'5446556656')
,(4,'1215455465')
,(4,'7894521656')
,(7,'5464212664')
,(5,'6564646565')
,(5,'7854554546')
,(4,'1244544794')


A.) List of employees who has at least one phone

-- Option : 1
SELECT  EName
FROM    Employee
WHERE   EID IN (SELECT EID FROM Phones)

-- Option : 2
SELECT  EName
FROM    Employee E
WHERE   EXISTS (SELECT 1 FROM Phones P WHERE E.EID = P.EID)

-- Option : 3
SELECT  DISTINCT EName
FROM    Employee E
        INNER JOIN Phones P ON E.EID = P.EID

B.) List of employees who does not have phone

-- Option : 1
SELECT  EName
FROM    Employee
WHERE   EID NOT IN (SELECT EID FROM Phones)

-- Option : 2
SELECT  EName
FROM    Employee E
WHERE   NOT EXISTS (SELECT 1 FROM Phones P WHERE E.EID =P.EID)

-- Option : 3
SELECT  EName
FROM    Employee E
        LEFT JOIN Phones P ON E.EID = P.EID
WHERE   P.EID IS NULL


C.) List of total number of phones per employee. If employee does not have phone then display 0

-- Option : 1
SELECT  E.EID, EName, ISNULL(TotalPhones,0) TotalPhones
FROM    Employee E
        LEFT JOIN
        (
            SELECT  EID, COUNT(Phone) TotalPhones
            FROM    Phones P
            GROUP BY    EID
        ) X ON E.EID = X.EID

-- Option : 2
SELECT  E.EID, EName, ISNULL(COUNT(Phone),0) TotalPhones
FROM    Employee E
        LEFT JOIN Phones P ON E.EID = P.EID
GROUP BY    E.EID, EName

D.) List employees (who born after 1985) with total number of phones. If employee does not have phone then it should display 0

SELECT  E.EID, EName, DOB, ISNULL(COUNT(Phone),0) TotalPhones
FROM    Employee E
        LEFT JOIN Phones P ON E.EID = P.EID
WHERE   DOB > '1985-12-31'
GROUP BY    E.EID, EName, DOB

-----------------------------------------------------------------------------------------------------


3. CRICKETER - MANAGER


-- Creating table
CREATE TABLE Cricker (CID INT IDENTITY(1,1), CName VARCHAR(10),ManagerID INT)

-- Inserting records

INSERT INTO Cricker (CName, ManagerID)
VALUES ('Dhoni',NULL)
,('Sehwag',1)
,('Raina',1)
,('Virat',2)
,('Yuvi',3)
,('Yusuf',2)
,('Zaheer',1)
,('Bhajji',7)
,('Nehra',7)
,('Ashwin',8)
,('Vijay',2)


A.) List of cricketers along with their respective manager. If cricketer does not have manager then ‘No Manager’ should be displayed

SELECT  C.CName, ISNULL(M.CName,'No Manager') ManagerName
FROM    Cricker C
        LEFT JOIN Cricker M ON C.ManagerID = M.CID

B.) List of cricketers along with total number of direct subordinates. If no subordinate then 0 should be displayed

SELECT  C.CName, ISNULL(TotalSubordinates ,0) TotalSubordinates
FROM    Cricker C
        LEFT JOIN   (
                        SELECT  ManagerID, COUNT(*)TotalSubordinates
                        FROM    Cricker
                        GROUP BY ManagerID
              ) X ON C.CID = X.ManagerID

C.) List of cricketer(s) who has/have highest number of direct subordinates

CREATE VIEW vwCricketerSubordinates
AS
    SELECT  ManagerID , COUNT(*) TotalSubordinates
    FROM      Cricker
    GROUP BY ManagerID
Go

SELECT  CName
FROM    vwCricketerSubordinates V
        INNER JOIN Cricker C ON V.ManagerID = C.CID
WHERE   TotalSubordinates = (
                                SELECT MAX(TotalSubordinates)
                                FROM vwCricketerSubordinates
                        )

------------------------------------------------------------

GET THE FIRST DAY OF THE MONTH

SELECT DATENAME(DW, DATEADD(DD,-(DATEPART(DD,GETDATE()))+1,GETDATE()))

GET COUNTRIES IN SORTED ORDER WITH INDIA AS FIRST COUNTRY


SELECT * FROM COUNTRY ORDER BY CASE WHEN CountryName = 'India' THEN'-1' ELSE CountryName END


CHANGE GENDER (REPLACE "Male" WITH "Female" AND VICE VERSA)


UPDATE UserProfile SET Gender = CASE WHEN 'Male' THEN 'Female' WHEN'Female' THEN 'Male' ELSE NULL END


GIVE THE RANK BASED ON TOTAL MARK






Using Rank Function

Using Co-related Subquery
Result
Now, give the rank in alphabetical order in case of same TotalMarks


REMOVE DUPLICATES FROM A TABLE

CREATE TABLE DuplicateCheck (int, b varchar(10))


INSERT INTO DuplicateCheck (a,b) VALUES
(1,'a'),(1,'a'),(1,'a'),
(2,'b'),(2,'b'),
(3,'c'),(3,'c'),
(4,'d')

-- APPROACH 1 (USING Identity COLUMN, co-related query)
ALTER TABLE DuplicateCheck ADD IdentityColumn INT IDENTITY

SELECT * FROM DuplicateCheck

DELETE      DC
FROM  DuplicateCheck DC
WHERE NOT EXISTS
            (
                  SELECT      1
                  FROM  (SELECT MIN(D.IdentityColumn)MinIdentityColumn,A,FROM DuplicateCheck D GROUP BY A,B) X
                  WHERE DC.IdentityColumn = X.MinIdentityColumn
            )

ALTER TABLE DuplicateCheck DROP COLUMN IdentityColumn

SELECT * FROM DuplicateCheck

-- APPROACH 2 (USING TEMP table)

TRUNCATE TABLE DuplicateCheck
INSERT INTO DuplicateCheck (a,b) VALUES
(1,'a'),(1,'a'),(1,'a'),
(2,'b'),(2,'b'),
(3,'c'),(3,'c'),
(4,'d')

CREATE TABLE #TEMP (INT, B VARCHAR(10))

INSERT INTO #TEMP (A,B) SELECT DISTINCT A,FROM DuplicateCheck

TRUNCATE TABLE DuplicateCheck
INSERT INTO DuplicateCheck (A,B) SELECT DISTINCT A,FROM #TEMP

DROP TABLE #TEMP
SELECT * FROM DuplicateCheck


-- APPROACH 3 (USING Identity column, SELF JOIN)

TRUNCATE TABLE DuplicateCheck
INSERT INTO DuplicateCheck (a,b) VALUES
(1,'a'),(1,'a'),(1,'a'),
(2,'b'),(2,'b'),
(3,'c'),(3,'c'),
(4,'d')

ALTER TABLE DuplicateCheck ADD IdentityColumn INT IDENTITY

SELECT * FROM DuplicateCheck

DELETE      DCMain
FROM  DuplicateCheck DCMain
            INNER JOIN DuplicateCheck DCRunning
                  ON DCMain.= DCRunning.a
                  AND DCMain.= DCRunning.b
                  AND DCMain.IdentityColumn >DCRunning.IdentityColumn

ALTER TABLE DuplicateCheck DROP COLUMN IdentityColumn
                 
SELECT * FROM DuplicateCheck

-- APPROACH 4 (USING CTE)

TRUNCATE TABLE DuplicateCheck
INSERT INTO DuplicateCheck (a,b) VALUES
(1,'a'),(1,'a'),(1,'a'),
(2,'b'),(2,'b'),
(3,'c'),(3,'c'),
(4,'d')

WITH del (A,B,DuplicateCount)
AS
(
      SELECT A,B,ROW_NUMBER() OVER (PARTITION BY A,ORDER BY A)DuplicateCount FROM DuplicateCheck
)
DELETE FROM del WHERE DuplicateCount > 1

SELECT * FROM DuplicateCheck
                 

FIND FACTORIAL 




-- APPROACH 1 (Recursive Function)

CREATE FUNCTION Facto (@number INT)
RETURNS DECIMAL(38,0)
BEGIN
      DECLARE @result DECIMAL(38,0)
      SET @result = @number
     
      IF @number = 0
            SET @result = 1
      ELSE
            SET @result = @result * dbo.Facto(@number-1)
     
      RETURN @result
END

select dbo.Facto(-4)


-- APPROACH 2 (CTE)

CREATE FUNCTION FactCTE (@number INT)
RETURNS DECIMAL(38,0)
BEGIN
      DECLARE @result DECIMAL(38,0);
      WITH CTE AS
      (
            SELECT 0 number, CONVERT(DECIMAL(38,0),1) FactResult
            UNION ALL
            SELECT number+1, FactResult * (number+1) FROM CTE WHEREnumber < @number
      )
      SELECT @result = FactResult FROM CTE
     
      RETURN @result
END


select dbo.FactCTE(4)