Collections of complex SQL queries I

Collections of complex SQL queries I

we have the table:

id name department 'id of manager', doj (date of joining)

insert into employee values(1,'John','IT',9,'05-08-2010');

1. list the employees who are not managers

2. manager with only one reportee

3. what is the month with most hiring?

4.what is the experience gap between the first employee and the latest?

5. name the manager with most reportees?

6.list managers who joined after the reportees

7.department with most managers and how many?

8.Delete duplicate rows using a single statement and no table creation.
9.Identify the second highest salary from emp table having salary related data.
10.Statement to determine the oracle version used .
11. Query to accept a date and return the fist date of the quarter in which the date exists.
12.Table A has 100 rows, Table B has Zero rows so number of rows returned from  below query
select a.* from a, b;
13. Set of statements executed in the below sequence.
select count(*) from a;

Count(*)
-------
100

Then 1 row inserted in table a.

rollback;
again 3 rows inserted in the table a;
select count(*) from a;
count(*)
--------
103

create table b using select * from a;

rollback;

so what would be the o/p of below select statement
select count(*) from a; 
?
14 .Statement to print numbers from 1 to 100 in sequence.
15.Create a table with no rows structure similar to existing table using an SQL statement.
16. Few queries related to analytical functions. lead, lag, rank, dense_rank
17. Table Employees

NAME  GENDER
----  ------ 
A    MALE
B    FEMALE
BB   FEMALE
CC   FEMALE
CD   MALE
DD   MALE
DE   FEMALE


Write a query to give the o/p like below

MALE  FEMALE
----  ------
3     4