1) Write a query to display alternate records from the employee table?
Ans:SELECT * FROM emp WHERE (rowid,1) IN (SELECT rowid,mod(rownum,2) FROM emp);
Or
--odd number query
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.rn,2) = 1
--even number query
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.rn,3) = 0
2) Write a query to display employee records having same salary?
Ans:
select * from emp where sal in(
select sal from emp where rowid not in(
select max(rowid) from emp group by sal))
3)How do you view the last record added to a table?
Ans:select * from test where rowid = ( select max(rowid) from test);
4)What is the main difference between the IN and Exists in subquery?
5) Write a query for displaying the departments along with number of employees in each department
6) What is an integrity constraint?
7)Is it possible to index a table through stored procedure? If Yes, How?
Ans:Yes.. Using execute immediate which executes dynamic sql and pl/sql
8)What is the difference between Inner joins and Outer joins?
Ans: Inner joins return rows from both joined tables where the join keys are equal. Outer joins return all rows from the "outer" table (which can be on the right or left side of the join) and only those rows from the other join table where the keys are equal.
9)Different types of joins?
Ans: Different type of joins are 1) self join 2) equijoin 3) non equijoin 4) cross join 5) natural join 6) full outer join 7) outer join 8) left outer join 9) right outer join
types of join:
1.Self Join-when u need to join a table to itself
2.Equi Join:-The relation between the employee table and dept table is an equi join that both tables must be equal
3.Non equi Join:-The relationship is obtained using operator other than = operator.
4.Outer Join:-The missing rows can be obtained is outer join operation is used.It returns rows from one table that have no direct match in other table.
5.Cross Join:-produces cross product of two tables.
6.Natural Join;-It is based on all columns in two tables that have same name or same column.
7.Inner Join-To find out only matched columns.
8.Full outer join:-inner join+left outer join+right outer join.
10) What is Cartesian Join / Cross Join?
Ans: A Cartesian product results from a faulty query. It is a row in the results for every combination in the join tables.
11) How does an index work? I know it uses rowid but want to know the exact process.
Ans: This is how the index works in simple terms.
When you add a row to a table, the rowid of that row plus a few other info is store in a separate area which is dedicated to keep it. Lets say you define a unique index. When you insert a valid row, the rowid + the key of the index is stored in the index region ( i forgot what it's called physically ). So a repository which says for this key, so and so is the rowid, is created. When you try to fetch out the row containing that key, SQL engine looks into this region, finds out the rowid and moves to that location.
Things work pretty much the same way for all the type of indexes. For a non unique index, it will be a single key and a number of rowids. That means, for this key(or group of keys), all these rowids are possible. SQL engine takes this info, and goes to find out those particular rowids.
12) How to find Nth largest or Nth smallest data from oracle table, for ex..5th highest salary from employees
Ans:
To find the nth higest salary:
select min(sal) from (select distinct sal from emp order by sal desc) where rownum<=&n;
To find the nth smallest salary:
select max(sal) from (select distinct sal from emp order by sal) where rownum<=&n;
Dont get confused... its min(sal) for highest and max(sal) for lowest. Becos:
Ex: Top 3 salaries - 5000,3000,2975 for 3rd higest min(sal) i.e 2975...
13)How to choose between a procedure and a function?
Ans: The main difference is DML (Data Manipulation). Through Stored procedure you can manipulate data i.e u can insert,update,delete records from table but in function u can't manipulate data. then comes secondary differences like function returns a value and stored procedures does not until and unless u specify an output parameter.stores procedure is a precomplied function i.e it has to be complied oly once but a function when called is complied again and again.
14) Give a scenario and put a query and ask to come up with the DB design with tables and relationships Restaurants, Localities, Addresses, Food Items and their availability.
15) Write a query for displaying the departments along with number of employees in each department
Ans:SELECT * FROM emp WHERE (rowid,1) IN (SELECT rowid,mod(rownum,2) FROM emp);
Or
--odd number query
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.rn,2) = 1
--even number query
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.rn,3) = 0
2) Write a query to display employee records having same salary?
Ans:
select * from emp where sal in(
select sal from emp where rowid not in(
select max(rowid) from emp group by sal))
3)How do you view the last record added to a table?
Ans:select * from test where rowid = ( select max(rowid) from test);
4)What is the main difference between the IN and Exists in subquery?
5) Write a query for displaying the departments along with number of employees in each department
6) What is an integrity constraint?
7)Is it possible to index a table through stored procedure? If Yes, How?
Ans:Yes.. Using execute immediate which executes dynamic sql and pl/sql
8)What is the difference between Inner joins and Outer joins?
Ans: Inner joins return rows from both joined tables where the join keys are equal. Outer joins return all rows from the "outer" table (which can be on the right or left side of the join) and only those rows from the other join table where the keys are equal.
9)Different types of joins?
Ans: Different type of joins are 1) self join 2) equijoin 3) non equijoin 4) cross join 5) natural join 6) full outer join 7) outer join 8) left outer join 9) right outer join
types of join:
1.Self Join-when u need to join a table to itself
2.Equi Join:-The relation between the employee table and dept table is an equi join that both tables must be equal
3.Non equi Join:-The relationship is obtained using operator other than = operator.
4.Outer Join:-The missing rows can be obtained is outer join operation is used.It returns rows from one table that have no direct match in other table.
5.Cross Join:-produces cross product of two tables.
6.Natural Join;-It is based on all columns in two tables that have same name or same column.
7.Inner Join-To find out only matched columns.
8.Full outer join:-inner join+left outer join+right outer join.
10) What is Cartesian Join / Cross Join?
Ans: A Cartesian product results from a faulty query. It is a row in the results for every combination in the join tables.
11) How does an index work? I know it uses rowid but want to know the exact process.
Ans: This is how the index works in simple terms.
When you add a row to a table, the rowid of that row plus a few other info is store in a separate area which is dedicated to keep it. Lets say you define a unique index. When you insert a valid row, the rowid + the key of the index is stored in the index region ( i forgot what it's called physically ). So a repository which says for this key, so and so is the rowid, is created. When you try to fetch out the row containing that key, SQL engine looks into this region, finds out the rowid and moves to that location.
Things work pretty much the same way for all the type of indexes. For a non unique index, it will be a single key and a number of rowids. That means, for this key(or group of keys), all these rowids are possible. SQL engine takes this info, and goes to find out those particular rowids.
12) How to find Nth largest or Nth smallest data from oracle table, for ex..5th highest salary from employees
Ans:
To find the nth higest salary:
select min(sal) from (select distinct sal from emp order by sal desc) where rownum<=&n;
To find the nth smallest salary:
select max(sal) from (select distinct sal from emp order by sal) where rownum<=&n;
Dont get confused... its min(sal) for highest and max(sal) for lowest. Becos:
Ex: Top 3 salaries - 5000,3000,2975 for 3rd higest min(sal) i.e 2975...
13)How to choose between a procedure and a function?
Ans: The main difference is DML (Data Manipulation). Through Stored procedure you can manipulate data i.e u can insert,update,delete records from table but in function u can't manipulate data. then comes secondary differences like function returns a value and stored procedures does not until and unless u specify an output parameter.stores procedure is a precomplied function i.e it has to be complied oly once but a function when called is complied again and again.
14) Give a scenario and put a query and ask to come up with the DB design with tables and relationships Restaurants, Localities, Addresses, Food Items and their availability.
15) Write a query for displaying the departments along with number of employees in each department