Interview Questions -- SQL Server -- Part-4

                                   Following are the SQL Server advance interview questions.

Questions:-

• Have you created VIEWs and what are the types of views.
• Is it possible to use ORDER BY with in the view definition
• What is STORED PROCEDURE and its advantages.
• Is it possible to call one stored procedure with in a another procedure.
• What is User Defined Functions(UDF) and its advantages.
• What is SQL SERVER Profiler.
• Have you come across Execution Plan and what is the advantage.
• Have you involved SQL query tuning OR How do you improve query performance.
• How did you handle Error Handling OR Exception Handling.
• What is TRY,CATCH.
• Is it possible to call Function with in a function.
• Have you called function with in a Procedure.
• Have you used CURSOR and Can you explain the situation when you have used your carrier.
• What is alternative for CURSOR.
• What is trigger and its types.
• What is INDEX and its usage.
• How many types of INDEX available.
• what is the difference between CLUSTERED and NON CLUSTERED INDEX.

Please share your answer through comments.

Interview Questions -- SQL Server -- Part-3

                                   Following are the SQL Server basic interview questions.

Questions:-

• What is BIT Data Type and what’s the information that can be stored inside a BIT Column.
• What is CANDIDATE KEY, ALTERNATE KEY and COMPOSITE KEY
• What is Locking.
• What is Normalization and explain different forms of normalization.
• How to change Database Name.
• How to Rename Database Columns.
• What are the System databases available and explain.
• How do you change the character length of the VARCHAR Column from 100 to 200.
• Is it possible to delete records from Foreign Key Column in a table.
• What is SQL Server Agent.
• What is the difference between ISNULL() AND COALESCE() Functions.
• Difference between REPLACE() and STUFF()
• What is the difference between UNION and UNION ALL.

Please share your answer through comments.

Interview Questions -- SQL Server -- Part-2

                      Following are the some of the SQL Server basic interview questions.

Questions:-

• What is JOINs and its types.
• What is Inner join and any real time example please.
• What is the scope of local Temp Tables and Global Temp tables.
• What is the use of RANK() function.
• Difference between RANK() and DENSERANK().
• Have you used ROW_NUMBER() function any time.
• What is System Defined Function OR Built-In Functions.
• Can You list out what are the Built-In Functions available.
• What is CASE Statements and how it is different from If else Statement.
• What is Correlated Subquery and have you used any time.
• Difference between DATEPART() and DATENAME() Functions.
• Difference between IS NULL and ISNULL() function.
• What is STUFF() function.
• What is the SQL Server version you are using in production.
• What is SQL Server Default TCP/IP Port Number
• Have you ever used UPDATE_STATISTICS Command
• Difference between Primary Key and Unique Key
• What ACID Property in Database
• what do you mean by SEED in Identity Column.

Please share your answer through comments.

Interview Questions -- SQL Server -- Part-1

                                              Following are the SQL Server basic interview questions.

Questions:-

• What is the difference between DELETE and TRUNCATE?
• What is MERGE Command and its advantage.
• What is the difference between DROP and TRUNCATE.
• What is HAVING Clause and how it is differentiate from WHERE Clause.
• How to check Table existence/availability, that means if table already available with the given name then DROP and create.
• What are the two authentication modes in SQL Server.
• What is CONSTRAINT and how many typ of Constraints available.
• What is the Prerequisite for Creating FOREIGN KEY Constraint.
• What is IDENTITY Column in a table.
• How many types of Transactions
• Implicit Transaction
• Explicit Transaction
• What is COMMIT and ROLLBACK.
• How to copy data from one table to another table.
• What is Local and Global Temp Tables.
• What is difference between Variable and Table Variable.
• What is CTE
• What is Recursive CTE.
• What is Sub Query and its types.

Please share your answer through comments.

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