SQL Realtime Interview Questions

 

1. What is COALESCE in SQL Server?

COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

Example – Select COALESCE(empno, empname, salary) from employee;

2. Can we check locks in database? If so, how can we do this lock check?

Yes, we can check locks in the database. It can be achieved by using in-built stored procedure called sp_lock.

3. What will be the maximum number of index per table?

1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

4. How data can be copied from one table to another table?

INSERT INTO SELECT

This command is used to insert data into a table which is already created.

SELECT INTO

This command is used to create a new table and its structure and data can be copied from existing table.


5. What is Query to find Second highest salary for employee?

This is most asked Real Time Scenarios in SQL in many industries. User can try multiple queries to find out the same result.

Query 1 :

Select distinct Salary from Employee e1 where 2= select count(distinct salary) from Employee e2 where e1.salary<=e2.salary

 

Query 2:

select min(salary) from (select distinct salary from emp order by salary desc) where rownum<=2

 

Query 3:

select * from(Select S.*,DENSE_RANK() OVER (PARTITION BY DNO ORDER BY SALARY DESC) DR from Source) S Where S.DR=2;


Author: Jilpa Gambhir

Software Developer working in IT industry from last 18 yrs. Worked in C#.Net, Sql Server, Biztalk, Sharepoint.


Comments

Post a Comment

Popular posts from this blog

Using CTE Recursively to get the hierachy from Parent child stored in same table (Continent-> Country-> State-> City)

In this article I will explain how to write a query to create parent-child hierarchy ( Continent-> Country-> State-> City ) with levels using recursive common table expression (CTE).  While working with database we often store parent and child id in same table. Let's take the example of  Continents, Countries, State/Province and City. They are stored in same table, and we have to get all of them in hierarchy e.g.  Continent-> Country-> State-> City . Let's begin and understand this via an example:  Create a table using the script below:    IF   OBJECT_ID ( 'tempdb.dbo.#tbHierarchy' ,   'U' )   IS   NOT   NULL    DROP   TABLE  #tbHierarchy ;     GO   CREATE   TABLE  #tbHierarchy    (           Id  INT          , [Name]  VARCHAR ( 20 )      ...
Read more

SQL Interview Questions

  Test yourself with  real  SQL interview questions: Question 1 Given the table below, write a SQL query that retrieves the personal data about alumni who scored above 16 on their calculus exam. alumni student_id name surname birth_date faculty 344 Aishani Lopes 1990-04-26 Med School 345 Robert Roy 1990-03-09 Physics evaluate student_id class_id exam_date grade 344 74 2014-06-19 17 344 87 2014-06-06 20 345 74 2014-06-19 11 curriculum class_id class_name professor_id semester 74 algebra 430 2014_summer 87 calculus 531 2014_summer 46 statistics 626 2014_winter There can be different answers. This is one of them. SELECT a . name , a . surname , a . birth_date , a . faculty FROM alumni AS a INNER JOIN evaluate AS e ON a . student_id = e . student_id INNER JOIN curriculum AS c ON e . class_id = c . class_id WHERE c . class_name = 'calculus' AND e . grade > 16 ; Question 2 There is a beverages  table.  id na...
Read more

Azure Devops - Create a CI / Build Pipeline

Image
    I n this article, we will cover how to add new pipeline to Devops project.  Go to  https://dev.azure.com /. Sign in with your credentials.  Open the Devops Organization and Open the listed project. To understand, how to create Devops Organization and project, Go to my previous article . Now, We will create CI/ Build pipeline for C# project so that developers can work on code and checkin their changes. Pipeline will generate build artifacts, which will be further sent to CD / Deployment pipeline for deployment on various servers like Pre-Prod or Prod servers. Please see the below image for more understanding. CI stands for Continuous Integration and CD stands for Continuous Deployment. Go to Pipelines in the left menu. Click on New Pipeline. This screen will appear.  There are two ways to create pipeline One way is using classic editor and other is using yaml code. classic editor is the way of creating pipelines as was done in TFS earlier....
Read more