SQL interview questions and answers - WAY2IT

Latest Post :

Home Top Ad

SQL interview questions and answers

SQL interview questions and answers

sourcehttp://www.c-sharpcorner.com


Q.1) Difference between WHERE clause and HAVING clause.

Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:

1. The WHERE clause can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.



Q.2) Difference between UNION and UNION ALL.


UNION
UNION ALL
UNION removes duplicate rows.
“UNION ALL” does not remove the duplicate row. It returns all from all queries.
UNION uses a distinct sort
“UNION ALL” does not use a distinct sort, so the performance of “UNION ALL” is slightly higher than “UNION”.
UNION cannot work with a column that has a TEXT data type.
UNION ALL can work with all data type columns.



Q.3) What are the types of Joins in SQL. Explain ?
  • INNER JOIN: Returns all rows when there is at least one match in BOTH the tables.
  • LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
  • RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
  • FULL JOIN: Returns all rows when there is a match in ONE of the table.


Q.4) What is the default join in SQL? Give an example query ?

The default join is INNER JOIN.

Example :

    SELECT column_name(s)  
    FROM table1  
    INNER JOIN table2  
    ON table1.column_name=table2.column_name;  



Q.5) Describe all the joins with examples in SQL ?
  • SQL LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

        SQL LEFT JOIN Syntax
        SELECT column_name(s)
        FROM table1
        LEFT JOIN table2
        ON table1.column_name=table2.column_name;


  • SQL RIGHT JOIN
The right join returns all the rows in the right table i.e. table 2 with the matching ones on the left table (table1).

        SELECT column_name(s)
        FROM table1
        RIGHT JOIN table2
        ON table1.column_name=table2.column_name;

  • SQL FULL OUTER
    The full join returns all rows from the left table (table1) and from the right table (table2).

        SELECT column_name(s)
        FROM table1
        FULL OUTER JOIN table2
        ON table1.column_name=table2.column_name;



Q.6) Differentiate Clustered and Non clustered Index in SQL ?

A clustered index is one in which the index’s order is arranged according to the physical order of rows in the table. Due to this reason there can only be one clustered index per table, usually this is the primary key.

A non clustered index is one in which the order of index is not in accordance with the physical order of rows in the table.

Create Index Syntax


CREATE INDEX [ CLUSTERED | NONCLUSTERED ] PIndex ON Persons (LastName,FirstName)



Q.7) Explain the difference between Stored Procedure and User Defined Function ?
  • Stored Procedure

    Stored procedures are reusable code in database which is compiled for first time and its execution plan saved. The compiled code is executed when every time it is called.

  • Function

    Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only a single value or a table. We can’t use functions  to Insert, Update, Delete records in the database table(s). It is compiled every time it is invoked.
Basic Difference

Function must return a value but in Stored Procedure it is optional (Procedure can return zero or n values).
Functions can have only input parameters for it whereas procedures can have input/output parameters.

Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advanced Differences
  • Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it,  whereas Function allows only SELECT statement in it.

  • Procedures cannot be utilized in a SELECT statement, whereas function can be embedded in a SELECT statement.

  • Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas function can be.

  • The most important feature of stored procedures over function is retention and reusing the execution plan while in case of function it will be compiled every time.

  • Functions that return tables can be treated as another rowset. This can be used in JOINS with other tables.

  • Inline Function can be though of as views that take parameters and can be used in JOINS and other Rowset operations.

  • Exception can be handled by try-catch block in a procedure, whereas try-catch block cannot be used in a Function.

  • We can use transactions in stored procedure but not in functions.


Q.8) Find the 3rd or Nth Highest Salary in a Table via SubQuery .

Step 1: Create a schema of a table named "Employee" in your database as in the following:
create table Employee
(
ID int identity,
Name varchar(20),
Salary float,
Department varchar(20)
)


Note: I am using SQL Server 2008 in this demo.

Step 2: Insert some values in the table according to the column behaviour like Name and Data Type.
Insert into Employee(Name,Salary,Department)values('A',20000,'Finance')
Insert into Employee(Name,Salary,Department)values('B',10000,'Finance')
Insert into Employee(Name,Salary,Department)values('C',28000,'IT')
Insert into Employee(Name,Salary,Department)values('D',15000,'Finance')
Insert into Employee(Name,Salary,Department)values('E',39000,'Finance')
Insert into Employee(Name,Salary,Department)values('F',12000,'Finance')
Insert into Employee(Name,Salary,Department)values('G',40000,'IT')
Insert into Employee(Name,Salary,Department)values('H',32000,'Finance')
Insert into Employee(Name,Salary,Department)values('I',56000,'IT')
Insert into Employee(Name,Salary,Department)values('J',29000,'Finance'



Step 3: Use the following command to see the data from the table.
select * from Employee


Q.9) How can I get the Nth highest salary like 3rd highest or 4th highest ?
Before getting the Nth Highest salary, we will get the salarys of employees in decreasing order.
SELECT  Name,salary  FROM employee ORDER BY salary desc






Explanation: In the preceding query my motive is that first I will get the highest 3 salaries and then get the minimum salary from those 3 salaries.

There are the following 2 parts of the preceding query:
  1. Inner Query: Get the highest 3 salaries
  2. Outer Query: Get the minimum salary from those 3 salaries
1. In the Inner Query I have used the "DISTINCT", "TOP", "ORDER BY" and "DESC" keywords, they mean:

DISTINCT: for removing the duplicity.

TOP: to get the number of upper rows from the set of records like here 3 is used as number.

ORDER BY: to ordering the set of rows.

DESC: used with "ORDER BY" to get the data in decreasing order.

So by the inner query we will get output like this:



2. Now to find the minimum salary from the inner query. To do that I will write the outer query using the "MIN" keyword and aliase the set by "as a" to get the final output like this:

SELECT MIN( salary)FROM (-- INNER Query ) as a


MIN: to get the minimum record from the set.

as a: for the aliasing of the set of records like here the result of the inner query is aliasing "as a".

Final Output: The output will be 39000.





Q.10) What is De-normalization ?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.


Q.11) What are different normalization forms ?
  • 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
  • 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it to a separate table.
  • 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
  • BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.

    Q.12) Primary key or composite key ?

    A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

    A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),       
           PRIMARY KEY (ID)
    );



    Q.13) Foreign key ?

    A foreign key is a key used to link two tables together. This is sometimes called a referencing key.
    Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.


    The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

    CREATE TABLE Orders
    (
    O_Id int NOT NULL PRIMARY KEY,
    OrderNo int NOT NULL,
    P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
    )


    Q.14) SQL commands can be divided into 4 subgroups : DDL , DML , DCL , DTL.

    Data Definition Language : Create , Alter , Drop.

    Data Manipulation Language : Select ,Insert ,Update , Delete.

    Data Control Language : Grant , Revoke.

    Data Transmission Language : Commit , Rollback , Savepoint.



    Q.15) What are the case manipulation functions of SQL ?

    LOWER, UPPER , INITCAP.



    Some Video Tutorials :