1.Display the dept information from department table
A)select * from dept;
2.Display
the details of all employees
A)select * from emp;
3.Display
the name and job for all employees
A)select ename,job
from emp;
4.Display
name and salary for all employees
A)select ename,sal
from emp;
5.Display
employee number and total salary for each employee
A)select
empno,sal+comm from emp;
6.Display
employee name and annual salary for all employees
A)select
empno,ename,12*sal+nvl(comm,0) annualsal from emp;
7.Display
the names of all employees who are working in department number 10
A)select ename from
emp where deptno = 10;
8.Display
the names of all employees working as clerks and drawing a salary more than
3000
A)select ename from
emp wher job = 'CLERK' and sal > 3000;
9.Display
employee number and names for employees who earn commission
A)select empno,ename
from emp where comm is not null and comm > 0;
10.Display
names of employees who do not earn any commission
A)select empno,ename
from emp where comm is null and comm = 0;
11.Display
the names of employees who are working as clerk , salesman or analyst and
drawing a salary more than 3000
A)select ename from
emp where (job='CLERK' or job='SALESMAN' or job='ANALYST') and sal>3000;
12.Display
the names of employees who are working in the company for the past 5 years
A)select ename from
emp where sysdate - hiredate > 5*365;
13.Display
the list of employees who have joined the company before 30 th june 90 or after
31 st dec 90
A)select * from emp
where hiredate between '30-jun-1990' and '31-dec-1990';
14.Display
current date
A)select sysdate from
dual;
15.Display
the list of users in your database (using log table)
A)select * from
dba_users;
16.Display
the names of all tables from the current user
A)select * from tab;
17.Display
the name of the current user
A)show user;
18.Display
the names of employees working in department number 10 or 20 or 40 or employees
working as clerks , salesman or analyst
A)select ename from
emp where deptno in (10,20,40) or job in ('CLERK','SALESMAN','ANALYST');
19.Display
the names of employees whose name starts with alphabet S
A)select ename from
emp where ename like 'S%';
20.Display
employee name from employees whose name ends with alphabet S
select ename from emp
where ename like '%S';
21.Display
the names of employees whose names have sencond alphabet A in their names
A)select ename from
emp where ename like '_S%';
22.Display
the names of employees whose name is exactly five characters in length
A)select ename from
emp where length(ename)=5;
or
select ename from emp
where ename like '_____';
23.Display
the names of employees who are not working as managers
A)select * from emp
minus (select * from emp where empno in (select mgr from emp));
or
select * from emp
where empno not in (select mgr from emp where mgr is not null);
or
select * from emp e
where empno not in (select mgr from emp where e.empno=mgr);
24.Display
the names of employees who are not working as SALESMAN or CLERK or ANALYST
A)select job from emp
where job not in ('CLERK','ANALYST','SALESMAN');
25.Display
all rows from emp table. The system should wait after every screen full of
information
A)set pause on;
26.Display
the total number of employees working in the company
A)select count(*)
from emp;
27.Display
the total salary and total commission to all employees
A)select sum(sal),
sum(nvl(comm,0)) from emp;
28.Display
the maximum salary from emp table
A)select max(sal)
from emp;
29.Display
the minimum salary from emp table
A)select min(sal)
from emp;
30.Display
the average salary from emp table
A)select avg(sal)
from emp;
31.Display
the maximum salary being paid to CLERK
A)select max(sal)
from emp where job='CLERK';
32.Display
the maximum salary being paid in dept no 20
A)select max(sal)
from emp where deptno=20;
33.Display
the minimum salary being paid to any SALESMAN
A)select min(sal)
from emp where job='SALESMAN';
34.Display
the average salary drawn by managers
A)select avg(sal)
from emp where job='MANAGER';
35.Display
the total salary drawn by analyst working in dept no 40
A)select
sum(sal)+sum(nvl(comm,0)) from emp where deptno=40;
36.Display
the names of employees in order of salary i.e. the name of the employee earning
lowest salary shoud appear first
A)select ename from
emp order by sal;
37.Display
the names of employees in descending order of salary
A)select ename from
emp order by sal desc;
38.Display
the details from emp table in order of emp name
A)select ename from
emp order by ename;
39.Display
empnno,ename,deptno and sal. Sort the output first based on name and within
name by deptno and witdhin deptno by sal;
A)select * from emp
order by ename,deptno,sal;
40)
Display the name of employees along with their annual salary(sal*12).
the name of the
employee earning highest annual salary should appear first?
A)select
ename,sal,sal*12 "Annual Salary" from emp order by "Annual
Salary" desc;
41)
Display name,salary,Hra,pf,da,TotalSalary for each employee.
The
out put should be in the order of total salary ,hra 15% of salary ,DA 10% of
salary .pf 5% salary Total Salary
will
be (salary+hra+da)-pf?
A)select ename,sal
SA,sal*0.15 HRA,sal*0.10 DA,sal*5/100 PF, sal+(sal*0.15)+(sal*0.10)-(sal*.05)
TOTALSALARY
from emp ORDER BY
TOTALSALARY DESC;
42)
Display Department numbers and total number of employees working in each
Department?
A)select
deptno,count(*) from tvsemp group by deptno;
43)
Display the various jobs and total number of employees working in each job
group?
A)select job,count(*)
from tvsemp group by job;
44)Display
department numbers and Total Salary for each Department?
A)select
deptno,sum(sal) from tvsemp group by deptno;
45)Display
department numbers and Maximum Salary from each Department?
A)select deptno,max(Sal)
from tvsemp group by deptno;
46)Display
various jobs and Total Salary for each job?
A)select job,sum(sal)
from tvsemp group by job;
47)Display
each job along with min of salary being paid in each job group?
A)select job
,min(sal) from tvsemp group by job;
48)
Display the department Number with more than three employees in each
department?
A) select deptno
,count(*) from tvsemp group by deptno having count(*)>3;
49)
Display various jobs along with total salary for each of the job where total
salary is greater than 40000?
A)select job,sum(sal)
from tvsemp group by job having sum(SAl)>40000;
50)
Display the various jobs along with total number of employees in each job.The
output
should contain only those jobs with more than three employees?
A)select job,count(*)
from tvsemp group by job having count(*)>3;
om emp group by deptno;
No comments:
Post a Comment