Wednesday 16 July 2014

Queries asked in Interviews

Question:0 SQL query to delete duplicate row  where no Primery key or unique available.

Answer:       
begin tran
;with CTE1 
As 
( select CompanyName,PID, ROW_NUMBER() over(Partition by CompanyName Order by CompanyName desc) as RnNo from OP_CoInsurance where PID='1517090' and CompanyID='4623') 

 Delete  from CTE1 where rnNo > 1 
Commit

Question 1: SQL Query to find second highest salary of Employee

Answer : There are many ways to find second highest salary of Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is SQL query using Subquery :

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
 

See How to find second highest salary in SQL for more ways to solve this problem.

Question 2: SQL Query to find Max Salary from each department.

Answer :

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.
 

Question 3:Write SQL Query to display current date.

 Ans:SQL has built in function called GetDate() which returns current timestamp.

SELECT GetDate();
 

Question 4:Write an SQL Query to check whether date passed to Query is date of given format or not.

Ans: SQL has IsDate() function which is used to check passed value is date or not of specified format ,it returns 1(true) or 0(false) accordingly.

SELECT  ISDATE('1/08/13') AS "MM/DD/YY";
 

It will return 0 because passed date is not in correct format.

Question 5: Write a SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

Ans:
SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

Question 6:Write an SQL Query find number of employees according to gender  whose DOB is between 01/01/1960 to 31/12/1975.


Answer : SELECT COUNT(*), sex from Employees  WHERE  DOB BETWEEN ‘01/01/1960 ' AND ‘31/12/1975’  GROUP BY sex;

Question 7:Write an SQL Query to find employee whose Salary is equal or greater than 10000.

Answer : SELECT EmpName FROM  Employees WHERE  Salary>=10000;

Question 8:Write an SQL Query to find name of employee whose name Start with ‘M’

Ans: SELECT * FROM Employees WHERE EmpName like 'M%';

Question 9: find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

Answer : SELECT  * from Employees  WHERE  upper(EmpName) like upper('joe%');

Question 10: Write a SQL Query to find  year from date.

Answer :  SELECT YEAR(GETDATE()) as "Year";
_____________________________________________________________________________________________



  • To fetch ALTERNATE records from a table. (EVEN NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
  • To select ALTERNATE records from a table. (ODD NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
  • Find the 3rd MAX salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
  • Find the 3rd MIN salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
  • Select FIRST n records from a table.select * from emp where rownum <= &n;
  • Select LAST n records from a tableselect * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
  • List dept no., Dept name for all the departments in which there are no employees in the department.select * from dept where deptno not in (select deptno from emp);
    alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
    altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
  • How to get 3 Max salaries ?select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
  • How to get 3 Min salaries ?select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
  • How to get nth max salaries ?
    select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
  • Select DISTINCT RECORDS from emp table.select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
  • How to delete duplicate rows in a table?delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
  • Count of number of employees in  department  wise.select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
  •  Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
    select ename,sal/12 as monthlysal from emp;
  • Select all record from emp table where deptno =10 or 40.
    select * from emp where deptno=30 or deptno=10;
  • Select all record from emp table where deptno=30 and sal>1500.
    select * from emp where deptno=30 and sal>1500;
  • Select  all record  from emp where job not in SALESMAN  or CLERK.
    select * from emp where job not in ('SALESMAN','CLERK');
  • Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
    select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
  • Select all records where ename starts with ‘S’ and its lenth is 6 char.
    select * from emp where ename like'S____';
  • Select all records where ename may be any no of  character but it should end with ‘R’.
    select * from emp where ename like'%R';
  • Count  MGR and their salary in emp table.
    select count(MGR),count(sal) from emp;
  • In emp table add comm+sal as total sal  .
    select ename,(sal+nvl(comm,0)) as totalsal from emp;
  • Select  any salary <3000 from emp table. 
    select * from emp  where sal> any(select sal from emp where sal<3000);
  • Select  all salary <3000 from emp table. 
    select * from emp  where sal> all(select sal from emp where sal<3000);
  • Select all the employee  group by deptno and sal in descending order.
    select ename,deptno,sal from emp order by deptno,sal desc;
  • How can I create an empty table emp1 with same structure as emp?
    Create table emp1 as select * from emp where 1=2;
  • How to retrive record where sal between 1000 to 2000?
    Select * from emp where sal>=1000 And  sal<2000
  • Select all records where dept no of both emp and dept table matches.
    select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
  • If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
    (Select * from emp) Union (Select * from emp1)
  • How to fetch only common records from two tables emp and emp1?
    (Select * from emp) Intersect (Select * from emp1)
  •  How can I retrive all records of emp1 those should not present in emp2?
    (Select * from emp) Minus (Select * from emp1)
  • Count the totalsa  deptno wise where more than 2 employees exist.
    SELECT  deptno, sum(sal) As totalsal
    FROM emp
    GROUP BY deptno
    HAVING COUNT(empno) > 2
  • ____________________________________________________________________________________

    What is the difference between inner and outer join? Explain with example.

    Inner Join
    Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).
    Inner join returns rows when there is at least one match in both tables
    If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.
    SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id
    DepartmentEmployee
    HRInno
    HRPrivy
    EngineeringRobo
    EngineeringHash
    EngineeringAnno
    EngineeringDarl
    MarketingPete
    MarketingMeme
    SalesTomiti
    SalesBhuti
    Outer Join
    Outer Join can be full outer or single outer
    Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).
    Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.
    SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
    DepartmentEmployee
    HRInno
    HRPrivy
    EngineeringRobo
    EngineeringHash
    EngineeringAnno
    EngineeringDarl
    MarketingPete
    MarketingMeme
    SalesTomiti
    SalesBhuti
    Logistics
    The (+) sign on the emp side of the predicate indicates that emp is the outer table here. The above SQL can be alternatively written as below (will yield the same result as above):
    SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept LEFT OUTER JOIN EMPLOYEE emp
ON dept.id = emp.dept_id

    What is the difference between JOIN and UNION?

    SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.
    UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.
    SELECT * FROM EMP1
UNION
SELECT * FROM EMP2;

    What is the difference between UNION and UNION ALL?

    UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.
    In the following example, I am choosing exactly the same employee from the emp table and performing UNION and UNION ALL. Check the difference in the result.
    SELECT * FROM EMPLOYEE WHERE ID = 5
UNION ALL
SELECT * FROM EMPLOYEE WHERE ID = 5
    IDMGR_IDDEPT_IDNAMESALDOJ
    5.02.02.0Anno80.001-Feb-2012
    5.02.02.0Anno80.001-Feb-2012
    SELECT * FROM EMPLOYEE WHERE ID = 5
UNION 
SELECT * FROM EMPLOYEE WHERE ID = 5
    IDMGR_IDDEPT_IDNAMESALDOJ
    5.02.02.0Anno80.001-Feb-2012

    What is the difference between WHERE clause and HAVING clause?

    WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.
    To understand this, consider this example.
    Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:
    SELECT * FROM DEPT WHERE ID > 3
    IDNAME
    4Sales
    5Logistics
    Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:
    SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
GROUP BY dept.name
HAVING AVG(emp.sal) > 80
    DEPARTMENTAVG_SAL
    Engineering90
    As you see above, there is only one department (Engineering) where average salary of employees is greater than 80.

    What is the difference among UNION, MINUS and INTERSECT?

    UNION combines the results from 2 tables and eliminates duplicate records from the result set.
    MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.
    INTERSECT operator returns us only the matching or common rows between 2 result sets.
    To understand these operators, let’s see some examples. We will use two different queries to extract data from our emp table and then we will perform UNION, MINUS and INTERSECT operations on these two sets of data.

    UNION

    SELECT * FROM EMPLOYEE WHERE ID = 5
UNION 
SELECT * FROM EMPLOYEE WHERE ID = 6
    IDMGR_IDDEPT_IDNAMESALDOJ
    522.0Anno80.001-Feb-2012
    622.0Darl80.011-Feb-2012

    MINUS

    SELECT * FROM EMPLOYEE
MINUS
SELECT * FROM EMPLOYEE WHERE ID > 2
    IDMGR_IDDEPT_IDNAMESALDOJ
    12Hash100.001-Jan-2012
    212Robo100.001-Jan-2012

    INTERSECT

    SELECT * FROM EMPLOYEE WHERE ID IN (2, 3, 5)
INTERSECT
SELECT * FROM EMPLOYEE WHERE ID IN (1, 2, 4, 5)
    IDMGR_IDDEPT_IDNAMESALDOJ
    522Anno80.001-Feb-2012
    212Robo100.001-Jan-2012

    What is Self Join and why is it required?

    Self Join is the act of joining one table with itself.
    Self Join is often very useful to convert a hierarchical structure into a flat structure
    In our employee table example above, we have kept the manager ID of each employee in the same row as that of the employee. This is an example of how a hierarchy (in this case employee-manager hierarchy) is stored in the RDBMS table. Now, suppose if we need to print out the names of the manager of each employee right beside the employee, we can use self join. See the example below:
    SELECT e.name EMPLOYEE, m.name MANAGER
FROM EMPLOYEE e, EMPLOYEE m
WHERE e.mgr_id = m.id (+)
    EMPLOYEEMANAGER
    PeteHash
    DarlHash
    InnoHash
    RoboHash
    TomitiRobo
    AnnoRobo
    PrivyRobo
    MemePete
    BhutiTomiti
    Hash
    The only reason we have performed a left outer join here (instead of INNER JOIN) is we have one employee in this table without a manager (employee ID = 1). If we perform inner join, this employee will not show-up.

    How can we transpose a table using SQL (changing rows to column or vice-versa) ?

    The usual way to do it in SQL is to use CASE statement or DECODE statement.

    How to generate row number in SQL Without ROWNUM

    Generating a row number – that is a running sequence of numbers for each row is not easy using plain SQL. In fact, the method I am going to show below is not very generic either. This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique. Anyway, here is the query:
    SELECT name, sal, (SELECT COUNT(*)  FROM EMPLOYEE i WHERE o.name >= i.name) row_num
FROM EMPLOYEE o
order by row_num
    NAMESALROW_NUM
    Anno801
    Bhuti602
    Darl803
    Hash1004
    Inno505
    Meme606
    Pete707
    Privy508
    Robo1009
    Tomiti7010
    The column that is used in the row number generation logic is called “sort key”. Here sort key is “name” column. For this technique to work, the sort key needs to be unique. We have chosen the column “name” because this column happened to be unique in our Employee table. If it was not unique but some other collection of columns was, then we could have used those columns as our sort key (by concatenating those columns to form a single sort key).
    Also notice how the rows are sorted in the result set. We have done an explicit sorting on the row_num column, which gives us all the row numbers in the sorted order. But notice that name column is also sorted (which is probably the reason why this column is referred as sort-key). If you want to change the order of the sorting from ascending to descending, you will need to change “>=” sign to “<=” in the query.
    As I said before, this method is not very generic. This is why many databases already implement other methods to achieve this. For example, in Oracle database, every SQL result set contains a hidden column called ROWNUM. We can just explicitly select ROWNUM to get sequence numbers.

    How to select first 5 records from a table?

    This question, often asked in many interviews, does not make any sense to me. The problem here is how do you define which record is first and which is second. Which record is retrieved first from the database is not deterministic. It depends on many uncontrollable factors such as how database works at that moment of execution etc. So the question should really be – “how to select any 5 records from the table?” But whatever it is, here is the solution:
    In Oracle,
    SELECT * 
FROM EMP
WHERE ROWNUM <= 5;
    In SQL Server,
    SELECT TOP 5 * FROM EMP;
    Generic solution,
    I believe a generic solution can be devised for this problem if and only if there exists at least one distinct column in the table. For example, in our EMP table ID is distinct. We can use that distinct column in the below way to come up with a generic solution of this question that does not require database specific functions such as ROWNUM, TOP etc.
    SELECT  name 
FROM EMPLOYEE o
WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5
    name
    Inno
    Anno
    Darl
    Meme
    Bhuti
    I have taken “name” column in the above example since “name” is happened to be unique in this table. I could very well take ID column as well.
    In this example, if the chosen column was not distinct, we would have got more than 5 records returned in our output.
    Do you have a better solution to this problem? If yes, post your solution in the comment.

    What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?

    ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work.
    ROW_NUMBER() is an analytical function which is used in conjunction to OVER() clause wherein we can specify ORDER BY and also PARTITION BY columns.
    Suppose if you want to generate the row numbers in the order of ascending employee salaries for example, ROWNUM will not work. But you may use ROW_NUMBER() OVER() like shown below:
    SELECT name, sal, row_number() over(order by sal desc) rownum_by_sal
FROM EMPLOYEE o
    nameSalROWNUM_BY_SAL
    Hash1001
    Robo1002
    Anno803
    Darl804
    Tomiti705
    Pete706
    Bhuti607
    Meme608
    Inno509
    Privy5010

    What are the differences among ROWNUM, RANK and DENSE_RANK?

    ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set.
    RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no record will be assigned the 3rd rank as no one came in third, according to RANK. See below:
    SELECT name, sal, rank() over(order by sal desc) rank_by_sal
FROM EMPLOYEE o
    nameSalRANK_BY_SAL
    Hash1001
    Robo1001
    Anno803
    Darl803
    Tomiti705
    Pete705
    Bhuti607
    Meme607
    Inno509
    Privy509
    DENSE_RANK, like RANK, does not assign unique numbers, but it does assign contiguous numbers. Even though two records tied for second place, there is a third-place record. See below:
    SELECT name, sal, dense_rank() over(order by sal desc) dense_rank_by_sal
FROM EMPLOYEE o
    nameSalDENSE_RANK_BY_SAL
    Hash1001
    Robo1001
    Anno802
    Darl802
    Tomiti703
    Pete703
    Bhuti604
    Meme604
    Inno505
    Privy505
    This article is to be extended with more questions
    • Posted by at

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)