Oracle Queries on Functions
1. select the average salaries of each department where deptno
is either 'D001' or 'D002' from employee table.
Q. select avg(basic_sal) from employee where dept_no
in('D001','D002');
2. select the name of the lowest paid employee from
the employee table.
Q. select ename from employee where basic_sal=
(select min(basic_sal) from employee);
3. Select the highest paid employee int the employee table.
Q. select ename from employee where basic_sal=
(select max(basic_sal) from employee);
4. Display the total no.of rows in the employee table.
Q. select count(*) from employee;
5. Display only those jobs where max sal>=3000 in the
employee table.
Q. select job from employee where basic_sal>=
(select max(basic_sal) from employee);
6. List the names and hiredates of the employees in
deptno='D002'display the dateformat as 12/03/03'.
Q. select ename,to_char(hiredate,'dd/mm/yy') from employee
where dept_no='D002';
7. List the name of the employees whose hiredate is in
the month of the December using substr function
Q. select ename from employee where substr(hiredate,4,3)
='DEC';
8. Count the number of people in departnumber 'D003'
Q. select count(*) from employee where dept_no='D003';
9. Produce the following output using employee table
Employee
Kim(Manager)
Bruce(Analyst)
Q. select ename ||'(' ||job||')' as "Employee" from employee
where ename='Kim' or ename='Bruce';
10. Find out the difference between highest and lowest
salaries
Q. select max(basic_sal)-min(basic_sal) from employee;
11. How many months has the manager worked for the company
(Round value)
Q. select count( round(to_date(hiredate),'Month'))
from employee where job='Manager';
12. Display the top three earners with name and salary in
the company
Q.
13. Display the sin and sinh value of 45
Q. select sin(45),sinh(45) from dual;
14. Display the ascii value of 'ORACLE'
Q. select ascii('ORACLE') from dual;
15. Display all job types in lower case
Q. select lower(job) from employee;
16. Select those clients whose bal_due is greater than value
2000 from client_mast table.
Q. select name,bal_due from client_mast where bal_due>2000;
17. Calculate the average price of all the products from
Product_mast table.
Q. select avg(cost_price) from product_mast;
18. calculate the maximum and minimum product prices and
rename the output as max_price and min_price respectively
from product_mast table.
Q. select max(cost_price) as "max_price",
min(cost_price) as "min_price" from product_mast ;
19. Count the number of products having price greater than
or equal to 1200.
Q. select count(*) from product_mast where cost_price>=1200;
20. Display employee number and day on which employee hired
from employee table.
Q. select empno,to_char(to_date(hiredate),'DAY') as Day
from employee;
21. Display the total no.of working days of each employee
from employee table.
Q. select ename as "Name",months_between(sysdate,hiredate) as
"No.of days" from employee;
22. Display the date, 20 days after today's date.
Q. select sysdate+20 from dual;
23. Count all distinct job types from employee table.
Q. select count(distinct job) from employee;
24. Using LPAD function pad the job types with 15 characters
by '*' symbol
Q. select lpad(job,15,'*') job from employee;
25. Display the length of each employee name from employee
table.
Q. select length(ename) from employee;
26. Display the value of 3 raised to 4 using power function
Q. select power(4,3) from dual;
27. Display the square root of 81
Q. select sqrt(81) from dual;
28. Display the number of bytes used by ename from employee
table.
Q. select vsize(ename) from employee;
29. Display the ename those are having sound like Kim from
employee table.
Q. select soundex('Kim') from employee;
No comments:
Post a Comment