101.who are not working
under any manager ?
SELECT EName
FROM Emp
WHERE Mgr IS
NULL;
102.Employees in dept 10 or
30 ,grade is not 4 and joined before 30-dec-
82 ?
SELECT * FROM
(SELECT
EName,Deptno,Grade,HireDate
FROM Emp E,SalGrade S
WHERE Sal BETWEEN LoSal AND HiSal
AND
Deptno IN (10,30)
AND HireDate < '30-dec-82') EE
WHERE
EE.Grade <> 4
103.Update salary of those
employee who are not eligible for commission to 20% increment ?
UPDATE Emp
SET Sal=Sal+Sal*.2
WHERE Comm IS NULL
104.Employees joined
before and location is newyork
or Chicago ?
SELECT
EName,LOC Location,HireDate
FROM Emp
E,Dept D
WHERE
E.Deptno=D.Deptno
AND LOC NOT IN ('NEW YORK','CHICAGO')
AND HireDate < '31-dec-82'
105.Empno,name,job,departmet
,location of managers ?
SELECT
Empno,EName,Job,DName,LOC Location
FROM Emp
E,Dept D
WHERE
E.Deptno=D.Deptno
AND
EXISTS ( SELECT
EName
FROM Emp M
WHERE E.Empno=M.Mgr)
106.Employees working under
jones ?
SELECT EName
EmpName
FROM Emp
WHERE
EName<>'JONES'
START WITH
EName='JONES'
CONNECT BY
PRIOR Empno=Mgr;
107.Display name of ford if
his salary is hisal of his grade ?
SELECT EName
|| '''S salary is equal to HighSalary of his Grade' Employee
FROM Emp
E,SalGrade S
WHERE
EName='FORD'
AND
E.Sal =HiSal
108.Employee name,Job,
Department name,manager name and grade in order of department ?
SELECT E.EName EmpName,E.Job Designtion
,D.DName Department,S.Grade EmpScale
,M.EName
Manager
FROM Emp
E,SalGrade S,Dept D,Emp M
WHERE E.Sal BETWEEN S.LoSal AND S.HiSal
AND
E.Deptno=D.Deptno
AND
E.Mgr=M.Empno(+)
109.Employee
name,job,department,salary,grade ?
SELECT E.EName EmpName,E.Job Designtion
,D.DName Department,Sal Salary,S.Grade
EmpScale
FROM Emp
E,SalGrade S,Dept D
WHERE E.Sal BETWEEN S.LoSal AND S.HiSal
AND
E.Deptno=D.Deptno
110.salary of clerks in
decending order ?
SELECT EName EmpName,Job Designtion,Sal Salary
FROM Emp
WHERE
Job='CLERK'
ORDER BY Sal
DESC
111.Employee name,job and
manager name .Also employees don’t have manager ?
SELECT
E.EName EmpName,M.EName MgrName
FROM Emp
E,Emp M
WHERE
E.Mgr=M.Empno(+)
112.Top 5 earner of company
?
SELECT
LEVEL,MAX(Sal)
FROM Emp
WHERE LEVEL
< 6
CONNECT BY
PRIOR Sal > Sal
GROUP BY
LEVEL
113.Employees getting
highest salary ?
SELECT
EName,Sal
FROM Emp
WHERE
Sal=(SELECT MAX(Sal) FROM Emp);
SELECT
LEVEL,MAX(Sal)
FROM Emp
WHERE LEVEL
< 2
CONNECT BY
PRIOR Sal > Sal
GROUP BY
LEVEL;
114.Employees whose salary
is avg of max and min salary ?
SELECT
EName,Sal salary
FROM Emp
WHERE Sal=(SELECT (MAX(Sal)+MIN(Sal))/2
FROM Emp )
115.Count employee of
each ?
SELECT Deptno
, COUNT(*) No_Of_Employee
FROM Emp
GROUP BY
Deptno
116.Display only department
no. where more than 3 worker ?
SELECT *
FROM (SELECT
Deptno , COUNT(*) No_Of_Employee
FROM Emp
GROUP BY Deptno )
WHERE
No_Of_Employee > 3
117.Managers whose salary
is more than avg salary of his company?
SELECT EName,Sal
Salary
FROM Emp
WHERE Job='MANAGER'
AND
Sal > (SELECT AVG(Sal)
FROM Emp)
118. Managers whose salary
is more than avg salary of his employee ?
SELECT
EName,Empno,Sal,AvgSal
FROM
Emp,(SELECT Mgr,AVG(Sal) AvgSal
FROM
Emp
GROUP
BY Mgr) M
WHERE Empno =
M.Mgr
AND
Sal
> M.AvgSal
119.Employee
name,sal,commission,net pay whose net pay is greater than any other employee ?
SELECT EName,Sal Sal,Comm Commission
,Sal+NVL(Comm,0) NetSalary
FROM Emp
WHERE Sal+NVL(Comm,0) > ANY (SELECT Sal+NVL(Comm,0)
FROM Emp)
120.Employee name with
total alary of company ?
SELECT EName ,(SELECT SUM(Sal) FROM Emp )
ComTotalSal
FROM Emp
121.Last 5 earner of
company ?
SELECT LEVEL Rank,MIN(Sal)
FROM Emp
WHERE LEVEL
< 6
CONNECT BY
PRIOR Sal < Sal
GROUP BY
LEVEL
ORDER BY 1
122.Employees salary
greater than their manager ?
SELECT E.EName Name,E.Sal EmpSalary,M.Sal MgrSalary
FROM Emp E,Emp M
WHERE
E.Mgr=M.Empno
AND
E.Sal > M.Sal
123.Department where no
worker working ?
SELECT
D.Deptno || ' has no worker ' Department
FROM Emp E,Dept d
WHERE
E.Deptno(+)=D.Deptno
AND
E.Deptno IS NULL
124.Employees whose salary
is odd ?
SELECT
EName,Sal salary
FROM Emp
WHERE
MOD(Sal,2) <> 0
125.Employees salary
contain at least 3 digit ?
SELECT
EName,Sal salary
FROM Emp
WHERE
LENGTH(Sal) >=3
126.Employees joined on
December ?
SELECT
EName,HireDate
FROM Emp
WHERE TO_CHAR(HireDate,'MON')=UPPER('&MOn')
Ans: Mon=Dec
127. Employees name contain
‘a’ ?
SELECT EName
FROM Emp
WHERE EName
LIKE(UPPER('%&Char%'))
Ans: Char=a
128. Employees whose deptno
is available in salary ?
SELECT EName,Deptno Department,Sal Salary
FROM Emp
WHERE Sal LIKE('%'||Deptno||'%')
129. Employees first
character of hiredate is equal to last two character of salary ?
SELECT
EName,HireDate,Sal Salary
FROM Emp
WHERE
TO_CHAR(HireDate,'D')=SUBSTR(Sal,-2,1)
130. Employees 10%
salary equals to year of join date ?
SELECT
EName,HireDate,Sal Salary
FROM Emp
WHERE
TO_CHAR(HireDate,'YY') =Sal*.1
131. Employees working in
sales or research ?
SELECT
EName || ' is working in Sales Or
Research Department ' Name
FROM Emp
WHERE Deptno IN (SELECT Deptno FROM Dept
WHERE DName IN ('SALES','RESEARCH'))
132.Grade of Jones ?
SELECT EName
,Grade
FROM
Emp,SalGrade
WHERE Sal
BETWEEN LoSal AND HiSal
AND
EName='JONES'
133.Employees joined before
15 of that month ?
SELECT EName
,Hiredate
FROM Emp
WHERE
TO_CHAR(HireDate,'dd') < 15
134. Delete the recodes
where particular department has less than 3 employee ?
DELETE FROM
Emp
WHERE
Deptno IN (SELECT Deptno
FROM(SELECT Deptno,COUNT(*) Employees
FROM Emp
GROUP BY Deptno)
WHERE Employees < &Num)
Ans: Num=3
135.Department where no
worker is working ?
SELECT
D.Deptno
FROM Emp
E,Dept D
WHERE
E.Deptno(+)=D.Deptno
AND
E.Deptno IS NULL
136.Employees working as
manager ?
SELECT EName
FROM Emp E
WHERE EXISTS
( SELECT EName FROM Emp M
WHERE
E.Empno=M.Mgr)
137.Employees grade equals
to any no. of his salary but not 1st digit ?
SELECT
EName,Sal,Grade
FROM Emp E,SalGrade S
WHERE Sal BETWEEN LoSal AND HiSal
AND
Sal LIKE('%'||Grade||'%')
AND
Sal NOT
LIKE(Grade||'%')
138. Employees sub-ordinate
to Blake ?
SELECT EName
FROM Emp
WHERE Ename <>
'BLAKE'
START WITH
EName='BLAKE'
CONNECT BY
PRIOR Empno=Mgr
139.Employees salary
greater than highest average salary of departments ?
SELECT
EName,Sal Salary
FROM Emp
WHERE Sal
> ALL(SELECT TRUNC(AVG(Sal))
FROM Emp
GROUP BY Deptno)
140.10th record of employee
table (with out rowid) ?
SELECT
ROWNUM,EName
FROM Emp
GROUP BY
ROWNUM,EName
HAVING
ROWNUM=10;
141.Half of employees name
in upper case and remaining in lower case ?
SELECT
ROWNUM,CASE
WHEN ROWNUM
BETWEEN 0 AND (Couter/2)
THEN
UPPER(Ename)
WHEN ROWNUM
BETWEEN (Couter/2) AND Couter+1 THEN
LOWER(EName)
END
FROM
Emp,(SELECT COUNT(*) Couter from Emp)
142. 10th record of
employee table with out using group by
and rowid ?
SELECT Ename
FROM (SELECT ROWNUM RecodeNo
,Ename
FROM Emp)
WHERE
RecodeNo=&Num
Ans:Num=10
143.Create a copy of
Employee table ?
CREATE TABLE
Employee
AS
SELECT * FROM
Emp
144.Select Ename if name
exists more than once ?
SELECT EName
FROM Emp
GROUP BY
EName
HAVING
COUNT(*) > 1
145.All employees name in
reverse order ?
SELECT
REVERSE(Ename) FROM Emp;
146.Employees joining month
same as his grade ?
SELECT
EName,TO_CHAR(HireDate,'MM') MOnth,Grade
FROM
Emp,SalGrade
WHERE Sal
BETWEEN LoSal AND HiSal
AND
TO_CHAR(HireDate,'MM')=Grade
147.Employees joining date
is available in deptno ?
SELECT
EName,TO_CHAR(HireDate,'DD') Day,Deptno
FROM Emp
WHERE Deptno
LIKE ('%'||TO_CHAR(HireDate,'DD')||'%')
148. Display employees 1st
character in upper case ?
SELECT
INITCAP(EName) FROM Emp;
149.Employee
name,sal,pf(20% sal) ?
SELECT
EName,Sal Salary,Sal*.2 PF
FROM Emp;
150.Create a table with
only one column Empno ?
CREATE TABLE
Employee
(Empno
NUMBER(4) );
151.Add
a column EName with Varchar2(20) ?
ALTER TABLE Employee
ADD
(EName VARCHAR2(20))
152.Add
Primark key constrain ?
ALTER TABLE Employee
MODIFY
(CONSTRAINT Employee_Empno PRIMARY KEY(Empno))
153.Increase
length of EName to 30 character ?
ALTER TABLE Employee
MODIFY
(Ename VARCHAR2(30))
154.Add
salary column to emp table ?
ALTER TABLE Employee
ADD
(Sal NUMBER(4))
155.Add
validation to salary can’t more than 10,000 ?
ALTER TABLE Employee
MODIFY
(CONSTRAINT Employee_Sal_CHK
CHECK(Sal < 10000))
156.Delete
the constraint for salary ?
ALTER TABLE Employee
DROP CONSTRAINT Employee_Sal_chk;
157.Again
salary not more than 10,000 ?
ALTER TABLE Employee
ADD
( CONSTRAINT Employee_Sal_CHK
CHECK(Sal< 10000 ) );
158.Add
Mgr Column ?
ALTER TABLE Employee
ADD
( Mgr NUMBER(4) )
159.Mgr
should related with empno ?
ALTER TABLE Employee
ADD
(CONSTRAINT Employee_Mgr_FK
FOREIGN KEY(Mgr)
REFERENCES Employee(Empno)
ON DELETE SET NULL)
160.
Add Deptno to table ?
ALTER TABLE Employee
ADD
(Deptno NUMBER(2))
161.Deptno
column relate with dept table ?
ALTER TABLE Employee
ADD
(CONSTRAINT Employee_Deptno_FK
FOREIGN KEY(Deptno)
REFERENCES Dept(Deptno))
162.Create
newemp table with emp data ?
CREATE TABLE NewEmp
AS
SELECT * FROM Emp;
163.
Create newemp that should contain only empno,ename,deptno ?
CREATE TABLE NewEmp
AS
SELECT Empno,EName,Deptno
FROM Emp;
164.Delete
employees who are working for more than 2 year ?
DELETE FROM Emp
WHERE
TRUNC(MONTHS_BETWEEN(SYSDATE,Hiredate)/12) > &Experience;
Ans: Experience=2
165.provide
commission of 10% of sal to those don’t have comm ?
UPDATE Emp
SET Comm=Sal*.1
WHERE Comm IS NULL
166.Increase
comm. 10% of their comm to those have comm. ?
UPDATE Emp
SET Comm=Comm*.1
WHERE Comm IS NOT NULL
167.Employees
name,department name ?
SELECT EName,Dname Department
FROM Emp E,Dept D
WHERE E.Deptno=D.Deptno
168.Emlpoyee
name,dept,dept name,location ?
SELECT EName,E.Deptno DeptNum,Dname Department,LOC Location
FROM Emp E,Dept D
WHERE E.Deptno=D.Deptno
169.
Display employee name and deptno even there is no employee in dept ?
SELECT D.Deptno DeptNum
FROM Emp E,Dept D
WHERE E.Deptno(+)=D.Deptno
AND
E.Deptno IS NULL
170.Employee
name and his manager name ?
SELECT E.Ename EmpName,M.EName
MgrName
FROM Emp E,Emp M
WHERE E.Mgr=M.Empno
171. Department
no. and no. of employee working on that dept ?
SELECT Deptno,COUNT(*) Num_Of_Emps
FROM Emp
GROUP BY Deptno
172.Department
name with total salary of each department ?
SELECT DName,SUM(sal) DeptTotalSal
FROM Emp E,Dept D
WHERE E.Deptno=D.Deptno
GROUP BY DName
173.Delete
repeted rows in emp ?
SELECT EName
FROM Emp
GROUP BY EName
HAVING COUNT(*) > &Num
Ans: Num=1
174.Display
5 to 7 rows of data ?
SELECT * FROM
(SELECT Ename,sal,Job,Deptno, ROWNUM RecodeNum
FROM Emp)
WHERE RecodeNum &Oprator
Ans: Operatop=BETWEEN 5 AND 7
175.Display
top n rows from table ?
SELECT * FROM
(SELECT Ename,sal,Job,Deptno, DENSE_RANK() OVER(ORDER BY Sal) Position
FROM Emp
GROUP BY Ename,sal,Job,Deptno)
WHERE Position &Oprator
176.Top
3 salary of emp ?
SELECT * FROM
(SELECT Ename,sal,Job,Deptno, DENSE_RANK() OVER(ORDER BY Sal DESC) Position
FROM Emp
GROUP BY Ename,sal,Job,Deptno)
WHERE Position &Oprator
177.Display
9th employee from emp ?
SELECT * FROM
(SELECT ROWNUM
Position,Ename,sal,Job,Deptno
FROM Emp
GROUP BY
ROWNUM,Ename,sal,Job,Deptno)
WHERE Position &Oprator
Ans:
Oprator =9
178.Emlpoyees
working as clerk ?
SELECT * FROM Emp
WHERE Job=’CLERK’;
179.Employees
working as clerk or manager with salary
mor than 4000?
SELECT * FROM Emp
WHERE Job IN (‘CLERK’,’MANAGER’)
AND
SAL > 4000
180.Employees
having experience more than 4 year ?
SELECT * FROM Emp
WHERE
TRUNC(MONTHS_BETWEEN(SYSDATE,Hiredate)/12) > 4;
181.Employees hired in last seven year ?
SELECT * FROM Emp
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,Hiredate)/12)
>= 7;
182.Employees
salary with in range of 5000 to 10000 ?
SELECT * FROM Emp
WHERE Sal BETWEEN 5000 AND 10000;
183.Employees joined on month of march ?
SELECT * FROM Emp
WHERE
TO_CHAR(HireDate,’MONTH’)=’MARCH’;
184.Employees
salary not in range of 10000 and 13000 ?
SELECT * FROM Emp
WHERE Sal NOT BETWEEN 10000 AND 13000;
185.Employees
getting salary 1000,2000,3000 ?
SELECT * FROM Emp
WHERE Sal IN(1000,2000,3000);
186.Employees
joined on 11-sep-2001,31-mar-2001,30-jan-2001 ?
SELECT * FROM Emp
WHERE TO_CHAR(HireDate,’DD-mon-YYYY’)
IN (‘11-sep-2001’,’31-mar-2001’,’30-jan-2001’)
187.Employees
having ‘o’ in second place ?
SELECT * FROM Emp
WHERE UPPER(EName) LIKE(‘__O%’);
188.Employee
having ‘r’ as last character ?
SELECT * FROM Emp
WHERE UPPER(EName) LIKE(‘%R’);
189.Employee
name starts with ‘r’ and ends with ‘o’ ?
SELECT * FROM Emp
WHERE UPPER(EName) LIKE(‘R%O’);
190.Employees
join month same as his grade ?
SELECT Ename,TO_CHAR(HireDate,'mm') Month,Grade
FROM Emp E,SalGrade S
WHERE Sal BETWEEN LoSal AND HiSal
AND
TO_CHAR(HireDate,'mm')=Grade
191.Dispaly
half of name in upper case and half in lower case ?
SELECT ROWNUM,CASE
WHEN ROWNUM BETWEEN 0 AND (Couter/2)
THEN UPPER(Ename)
WHEN ROWNUM BETWEEN (Couter/2) AND Couter+1 THEN LOWER(EName)
END
FROM Emp,(SELECT COUNT(*) Couter from Emp)
192.Employees
earning more than $30,000 yearly ?
SELECT Ename,Deptno,Job,TO_CHAR(Sal*12,'$99G999') AnnualSal
FROM Emp
WHERE Sal*12 > TO_NUMBER('$30,000','$99,999')
193.How
many managers are there with out listing them ?
194.Find average salary and average remuneration for each job ?
SELECT Job,AVG(Sal) AvgSalary,AVG(Comm) AvgCommission
FROM Emp
GROUP BY Job
195.Jobs
filled in first half of 1983 and same jobs in same half of 1984 ?
SELECT DISTINCT Job
FROM Emp
WHERE Job IN (SELECT DISTINCT Job
FROM
Emp
WHERE
TO_CHAR(HireDate,'MM') < 7
AND
TO_CHAR(HireDate,'YYYY') = 1983)
AND
TO_CHAR(HireDate,'MM')
< 7
AND
TO_CHAR(HireDate,'YYYY')
= 1984
196.Employees
joined before their manages ?
SELECT E.EName EmpName,E.HireDate EmpHired
,M.EName
MgrName,M.HireDate MgrHired
FROM Emp E,Emp M
WHERE E.Mgr=M.Empno
AND
E.HireDate < M.HireDate
197.Employees name,no. along with their manager name,no. (King also) ?
SELECT E.Empno EmpNO,E.EName EmpName
,M.Empno MgrNO,M.EName MgrName
FROM Emp E,Emp M
WHERE E.Mgr=M.Empno(+)
198.Employees
getting highest salary in their job ?
SELECT E.Job,E.Sal,EE.Job ,EE.MaxSal
FROM Emp E,(SELECT
Job,MAX(Sal) MaxSal
FROM Emp
GROUP BY Job)
EE
WHERE E.Job=EE.Job
AND
E.Sal = EE.MaxSal
199.Most
recent employee in each job ?
SELECT *
FROM Emp
WHERE HireDate = (SELECT
MAX(HireDate) FROM Emp)
200.
Employees earning greater than average of their department along with their
department order by department ?
SELECT E.Ename Name,E.Deptno,E.Sal
FROM Emp E,(SELECT Deptno,AVG(Sal ) DeptAvgSal
FROM Emp
GROUP BY Deptno) EE
WHERE E.Deptno=EE.Deptno
AND
E.Sal > EE.DeptAvgSal
No comments:
Post a Comment