Showing posts with label SQL Interview Questions. Show all posts
Showing posts with label SQL Interview Questions. Show all posts

Tuesday 9 April 2013

Difference Between DBMS and RDBMS



DBMS :- DBMS means DataBase Management System. In which we can manage the data.
Example :-  FoxPro, My SQL are DBMS

RDBMS :- RDBMS means relational DataBase Management System. Where we can do the the work as in the DBMS and also put the relations between table. If we set a parent key-foreign key relation between two table it will not allow to delete the child table entry before deleting the parent entry.
Example :-  Oracle, SQL Server are RDBMS

Create table structure from existing table without coping the data and with coping the data.



In oracle we can create a new table from an existing table as:

CREATE TABLE new table name  AS SELECT * FROM existing table name;

The upper SQL will create a copy of existing table and new table will contain all the data of existing table.

CREATE TABLE new table name  AS SELECT * FROM existing table name WHERE 1=2;

The upper SQL will also create the copy of existing table with the new table name but the new table will not contain any data because the where clause condition will never be true. So this will only copy he structure of the table not the data.

Thursday 4 April 2013

How to find the nth salary from employee table

This is a very common but tricky question for all the pl/sql interviews. Basically we have to find the nth highest salary from an employee salary table.

We have two common ways to get the nth salary or value of any column in a table.

1)      Using nested queries: - Follow the below steps to analyses this approach.

·         Create table esalary.
CREATE TABLE esalary (ename VARCHAR2(20), edep VARCHAR2(20), esal NUMBER(10));

·         Insert some dummy values in table esalary.
INSERT INTO esalary VALUES ('EMP1','Engineering',15000);
INSERT INTO esalary VALUES ('EMP2','Engineering',40000);
INSERT INTO esalary VALUES ('EMP3','Engineering',35000);
INSERT INTO esalary VALUES ('EMP4','Engineering',20000);
INSERT INTO esalary VALUES ('EMP5','Engineering',22000);

·         Find the nth number salary from salary table.
SELECT ESAL FROM (SELECT DISTINCT esal FROM (SELECT DISTINCT esal FROM esalary ORDER BY esal DESC) WHERE ROWNUM <= 3 ORDER BY esal ASC) WHERE ROWNUM = 1
Result: - 22000

Note: - Change ROWNUM<=3 in upper query with number for which you want to find the salary.
Example above query will return (22000) as the 3rd highest salary. 

Working Description: - The most inner sub query will return all the salaries from table in descending order then the outer sub query will fetch the first 3 (or the number we put in the query) highest salaries from the inner sub query result and sort them in ascending order and the outer most query will select the first value from the selected values.

2)      Using one nested query in WHERE clause: -
SELECT esal FROM esalary a WHERE 2 = (SELECT COUNT(*) FROM esalary b WHERE a.esal<=b.esal)
                Result: - 35000
       Working Description: - The upper query used only one sub query in the where clause. It also used the self-join to compare the one salary value of a table with all the other salary values of the table. Suppose we use 2 (means second highest salary) in above where clause so now the complete query will return the result only when the inner query in where clause will return 2. The inner where clause sub query  will compare all the salary of outer table alliance (given a name a) by a with all the salaries in the inner table alliance (given a name b) by b and return the number of values equal to or greater than the outer table alliance by a. In this case it will return 2 only when the second highest value of outer table is compared with all the inner table salaries.