frequently asked interview questions and answers in sql server part 1

1) What is SQL?

     SEQUEL(sql) stands for (Structured English Query Language)

     SEQUEL is a Language i.e Designed for Communicating with a RDBMS.Which is designed by IBM in 19974 by adopting mathematical prinicipales relational algebra and         calculas.

    SEQUEL known as SQL is controlled by today ANSI.Which gives the specifications for sql that has to be implemented by the various database vendars.

2) What are the different DML commands in SQL?

   DML (Data Manipulation Language)
        DML delas with data directly.It contains the commands are INSERT,UPDATE,DELETE


3) What are the different DCL(Data Control Language) commands in SQL?

   DCL also delas with data only but security of the data.It contains commands are GRANT,REVOKE,DENY

4) What are the different DQL commands in SQL?

   DQL delas with data but for retriving data from database,It contains commands are SELECT

5) What are the different DDL commands in SQL?

    DDL (Data Definition Language)
         DDL delas with the structure of the data but not with data directly.It contains commands are              CREATE,ALTER,DROP,TRUNCATE

6) What are the different TCL commands in SQL?

   TCL (Transaction Control Language)
       TCL delas with Transaction Management.It conatins commands are COMMIT,ROLLBACK,SAVE

7) What is a primary key?

      A primary key constraint will not allow nulls and duplicates because its combination of not null and unique constraint.while imposing a primary key constraint on       a table make sure the table has only one primary key constraint imposed on it.because we can never impose multiple primary key constraint on a table.where as if        required composite primary key is allowed.

      Create Table Employee(Eno int constraint Eno-pk primary key,Ename varchar(50),Salary Decimal(9,2))

8) What is a unique key?

     if a unique constraint is imposed on a cloumn that column will not allow duplicate values in to it.

     Create Table Employee(Eno int unique,Ename varchar(50),Salary Decimal(9,2))

9) What is a foreign key?

     Foregin key is used for establishing master detail relation ships between the tables so that we can verify the existince of a related row in another table.

     Create Table Employee(EmpNo int constraint EmpNo-Pk primary key,Ename varchar(50),DeptNo int,constraint DeptNo-fk foregin key(Deptno) references Department             (DeptNo))

10) What is a join?

     Joins are used for retriving the data from one or more tables at the same time.

11) What are all the different types of joins ?

       1) Inner Joins

       1) An Equality condition

       2) Equality condition

       3) Self join

   2) Cross joins

   3) Outer joins

       1) Left outer join

       2) Right outer join

       3) Full outer join

12) What is Equijoin or Equality join ?

     If two or more tables are combined together basing on equality condition.we call it as a quijion.In this case the matching rows of first table will go and combine       with matching row's of second table.

      Write a query to retrive data from Employee and Department tables?

     Select E.EmpNo,E.Ename,E.job,E.mgr,E.sal,E.comm,E.DeptNo,D.DeptNo,D.Dname,D.Loc from Employee E inner join Department D on E.DeptNo=DeptNo



13) What is Non Equijoin or Non Equality condition ? Give an Example ?

    If two or more tables are combined to gether basing on any condition other than Equality condition we call it as non equijoin.

     Write a Query to retrive the data from Employee and Salary Grade tables to findout the grade of each employee basing on his salary matching with lowsal & high sal      of sal grade table ?

     Select E.EmpNo,E.Ename,E.sal.S.Losal,S.hisal from Employee E inner join Salgrade s on E.sal between S.Losal and S.hisal

14) What is Self join ? Give an Example ?

    If a table is combined to itself basing on equality or no equality condition we call it as a self join

       Note: Duplicate Records eliminating by using Distinct

     Write a query to get the list of employee's who are having sub ordinates under them ?

    Select Distinct E.Empno,E.Ename ,E.job,E.Mgr,E.sal,E.Deptno from Employee E inner join Employee M on E.Empno = M.Mgr

15) What is Cross join ?Give an Example ?

   These is earlier known as cartision join.if at all two or more tables are combine to gether without any condition we call it as a cross join.In this case each row     of the first table will join with each row of the second table so in the first table has m rows and the second table has n rows.The output will be m*n rows

    Example :

    Select * from Employee E cross join Department D

16) What is outer joins ? Give an Example ?

   This is an extenstion to the existing inner joins .where in case of  inner joins we will be getting only the matching records basing on the condtion between the       tables where as in case of outer join along with the matching records we can aslo retrived the unmathced data from the tables outer joins are of three types.

      a) Left outer join

      b) Right outer join

     c) Full outer join

17) What is Left outer join ? Give an Example ?

     In this case we will be getting the matching records from both the two tables as well as the unmatched records from left hand side table.

    Example

       Select E.Empno,E.Ename,E.Deptno as EDeptNo ,D.Deptno as DDeptNo,D.Dname,D.Loc from Employee E left outer join Department D on E.Deptno=D.Deptno

18) What is Right outer join ? Give an Example ?

   In this case we will be getting the matching records from both the two tables as well as the unmatched records from right hand side table.

    Example

       Select E.Empno,E.Ename,E.Deptno as EDeptNo ,D.Deptno as DDeptNo,D.Dname,D.Loc from Employee E right outer join Department D on E.Deptno=D.Deptno

19) What is Full outer join ?Give an Example ?

   In this case we will be getting the matching records from both the two tables as well as the unmatched records from left hand side and right hand side tables.

    Example

       Select E.Empno,E.Ename,E.Deptno as EDeptNo ,D.Deptno as DDeptNo,D.Dname,D.Loc from Employee E Full outer join Department D on E.Deptno=D.Deptno

20) How to Rename Tables(Objects) ?

      As per the standards of sql we can never rename objects under a database with the help of a built in stored procedure which can be used as following

        SP-Rename  '< Old name >'  , '< New name >'

      Example :  SP-Rename 'Supplier' ,'Suppliers'

21) What is a View?

     A view is a logical or virtual table whose contents are defined by a query like a table.A view consists of a set of named columns and rows of data but does not     exist physically in the database .Views are created basing on a query.

22) What is an Index?

     Sql server without searching into each extent and each page will be first searching in to the index page of the table.if present to locate the address of the      records which have to be retrived and jumps directly to there address locations and retrives the information.if sql server has to use an index scan first the      index must be present for the table.Indexes are created for a table basing on a cloumn or columns of the table.

23) What are all the different types of indexes?

    In sql server we have two types of Indexes

     a) Clustered Index

     b) Non clustered index

     a) Clustered Index

           In this case the arrangement of the data in the index page will be exactly same as the arrangement of the data in the datapage for the table
           Example : An Index in the start of the Book

     b) Non Clustered Index

          In this case the arrangement of the data in the index page will not be same as the arrangement of the data in the actual data page for the table
          Example : An index in the end of a book

24) What is a Cursor?

     A cursor is a memory location which is used for storing the result of query.Cursors are basically used for processing multirows.select satement under T-Sql programs like an annonymous block or subprograms also.To use a cursor under a program we need to learn about the cursor management.where the cursor management invloves in five steps

      a) Declaring a cursor

      b) Opening a cursor

      c) Fetching data from the cursor

      d) Closing the cursor

      e) Deallocating the cursor

25) What is a SubQuery?

     A Sub Query inside another query is known as a subquery.Where in this case first the inner querys executes and returns the result to the outer query for execution.we can nest query up to 32 levels
 
   Write a query to findout the details of the employees whose earning the highest salary.

     select 8 from Employee where sal=(select max(sal) from Employee)

26) What are the types of subquery?

    Two types of subquerys

     a) Corelated sub query

     b) No colrelated sub query

    a) Corelated subquery

         Many querys can be evalvated by executing the subquery and substiting the resulting values in to the where clause of the outer query in querys that includes a corelated sub query also known as repetating subquery.

   b) Non Corelated subquery
 
       A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query.

27) What is a stored procedure?

     Stored Procedure is a block of code that performs an action whenever it is called.

       A procedure is devided into two parts :
      ---------------------------------------

       1) Procedure header

       2) Procedure body

         Syntax :

      Create | Alter Procedure [(@ [size] [=default] [Out | Output ], [........n])]      [with ]

           As

           Begin

            -stmts

           End


    - > Create is used for creating a new procedure where alter is used for modifing an existing procedure.

    - > Name of the procedure is used defined which must be unique under the database.

    - > If required we can pass parameters to a procedure by passing parameters is only optional.

     - > Parameters can be either input or output parameters.where input parameters used for bringing a value into the procedure for execution.Where as output            parameters  are used for taking or carring a value out of procedure ' after ' execution.

        Example : Create Procedure TestSP1

                   As

                    Begin

                    print 'My first-stored procedure'

                   End


28) What is a trigger?

     A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server

     Two types of triggers in sql server

      a) DML triggers

           A DML trigger execute when a user trys to modify data through a DML statement or event.Where all DML events are Insert,Update,Delete statements on a table           or view .These triggers fire when any valid event is fired regardless of whehter or not any rows are effected

      b) DDL triggers

          A DDl triggers are introduced in sql 2005 that executes in response to a variety of DDl events like Create,Alter,Drop, statements

29) What is the difference between Cluster and Non-Cluster Index?

       a) Clustered Index

           In this case the arrangement of the data in the index page will be exactly same as the arrangement of the data in the datapage for the table
           Example : An Index in the start of the Book

           We can create Maximum of 250 indexes on a table in which clustered index

     b) Non Clustered Index

          In this case the arrangement of the data in the index page will not be same as the arrangement of the data in the actual data page for the table
          Example : An index in the end of a book

         We can create Maximum of 1 non clustered index

30) When should we create indexes on a table ?

      If we want to create an index on a table we need to create indexes on those columns which are frequently used in the where condition or order by clause.It's not       suggest to create an index on the columns that are not frequently used for searching or sorting the data because more number of indexes on a table will aslo       degrade the performance of the server because every DML operation we perform on the table should have the sub sequent entery's in the index pages associated with       the table

32) What is CTE?

      A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.
About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

1 comment

  1. Hello There,


    You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide.

    I have a data table with 80M records that looks like any other typical Sales Fact with fields like DateID, ProductID, SupplierID,QuantitySold and I am trying to write a DAX calculation that gives me the QuantitySold in past 30/60/90 days.
    The catch is if the month is part done (example: April 15th), then this measure should show only until that day as the total at the month level instead of as of last day of the month.
    I have tried many ways and finally ended up on this as it was working perfectly
    QtySold_Running30days:=
    VAR MaxDate = LASTDATE(FactSales[SaleDt])

    RETURN
    CALCULATE([QuantitySold],
    FILTER(ALL('Date'),
    'Date'[Date] > DataMaxDate -30 &&
    'Date'[Date] < DataMaxDate))
    Having said that, the reports that have all these 3 measures along with Date / Product and Supplier Attributes is not returning data even with the bare minimum filters.

    Thank you very much and will look for more postings from you.


    Thank you,
    Renina

    ReplyDelete