What are the types of functions available in oracle.
-Single Row Function
Case MAnipulation Function
Character Manipulation
Genaral funtion
Number Function
Date Function
-Multiple Row Function
Group Functions
Difference between single row function and multiple row function.
Single Row Function
Input Sinlge Row
Output Single Row
Multiple Row Function
Input Multiple Rows
Output Single Rows
List out all the case and character functions.
CASE MANIPULATION FUNCTION
UPPER()
LOWER()
INITCAP()
CHARACTER MANIPULATION FUNCTION
SUBSTR() INSTR()
LTRIP() RTRIM()
LPAD() RPAD()
REPLACE() TRANSLATE()
LENGTH()
CONCAT()
REVERSE()
Display first three characters from first name.
SELECT substr(First_name,1,3)"First_Name" FROM employees;
Display last two character from last name.
SELECT substr(last_name,-2,2)"LAST_NAME" FROM Employees;
Display all the first name and position of a in that name (first occurrence of a).
SELECT First_name, instr(first_name,'a',1) FROM employees;
Display all the first name and position of a in that name (second occurrence of a)
SELECT First_Name,instr(First_name,'a',1,2) FROM employees;
Display all the name which contain two or more number of a 's in the first name.
SELECT First_Name,instr(First_name,'a',1,2) from employees where instr(First_name,'a',1,2)<>0
Difference between SUBSTR and INSTR function.
SUBSTR :
It returns a specified portion of a string
INSTR
It returns a character position(ie occurances of the character).
Difference between REPLACE and TRANSLATE function.
Replace:The Replace Function replaces single character with multiple characters.
Translate: Translate Function replaces single character with single character only.
Difference between LPAD and RPAD.
LPAD : To adjust the left hand side padding of charecters,numbers.
RPAD : To adjust the right hand side padding of charecters, numbers.
Difference between LTRIM and RTRIM.
LTRIM: Trim the charecters and numbers in left hand side.
RTRIM: Trim the charecters and numbers in Right hand side.
Display all the first name and its length.
SELECT First_name, length(first_name) from employees;
List out all the number functions in oracle.
ROUND();
MOD();
TRUNC();
POWER();
List out all the Date functions in oracle?
ADD_MONTHS();
MONTHS_BETWEEN();
NEXT_DAY();
LAST_DAY();
Display all the first name and their total year of experience. rename first name column name as name and second column name as Year of Exep.
SELECT First_Name "Name" ,round(months_between(sysdate,hire_date)/12)"Year of Experiance" from employees;
How to display months between two given date.
select months_between('12-jan-2016','12-dec-2016')from dual;
Write a query to display today's date.
Select sysdate from dual;
Write a query to display the date after 3 months from today.
Select add_months(sysdate, 3) from dual;
Display last date of the current month.
Select last_day(sysdate)from dual;
Display the up coming Wednesday date.
Select next_day(sysdate,'wednesday') from dual;
Which date function return number as output.
To_Number()
What are all the type conversion functions available.
To_Char()
To_Date()
To_number()
How to convert date into character.
TO_CHAR ( Value, [format])
Value : A Number or Date that will be converted to a string
Format : This is the format it will be used to convert value to a string.
How to convert character in to date.
TO_DATE( string, format)
String : The string that will be converted to a date.
Format : The format that will be used to convert string to date.
What is the use of general function.
Its Mainly used to process the NULL Values.
Explain NVL, NVL2 , NULLIF and COALESCE function with example.
NVL() : Its accept two arguments if the first arguments is null and its display the second arguments else its display the first arguments.
consider one example
select first_name,commission_pct,nvl(commission_pct,1)from employees;
NVL2( ): Its accept three arguments if the first arguments is null and its display the third arguments else its display the second arguments.
select first_name,commission_pct,nvl2(commission_pct,1,3)from employees;
Null If (): It accepts two arguments return null if both arguments are equal else it will display the first arguments.
COALESCE(): It accept N Number of arguments returns the first not null values from the expression list. and Instead of NVL() we can use the COALESCE() .
select first_name,commission_pct,coalesce(commission_pct,null,null,1,null)from employees;
SQL> select nullif(5,10) from dual;COALESCE
What are all the aggregate functions available in oracle.
Aggregate functions means group function
MIN()
MAX()
SUM()
AVG()
COUNT()
Write a query to select maximum salary from employees table.
SELECT MAX(SALARY) FROM EMPLOYEES;
Write a query to select second maximum salary from employees table.
SELECT max(salary) FROM Employees WHERE salary NOT IN (SELECT max(salary) FROM Employees) ;
Display average salary in the department 90.
select avg(salary) from employees where department_id=90;
Display number of employees working in department 90 and 60.
select count(*) from employees where department_id in( '90','60');
Display all the department id and its maximum salary.
Select department_id,max(salary) from employees group by department_id order by 1;
Display all the department id and number of employees working in that department.
select department_id, count(*) from employees group by department_id;
Display all the department id and salary allocated for that department.
select department_id,sum(salary) from employees group by department_id order by 1;
Display all the department id and number of employees working in that department. Total no employees working for the particular department must be greater than 30.
select department_id, count(*)from employees group by department_id having count(*)>30;
Difference between WHERE clause and HAVING clause.
Where
Its followed by from clause
Having
Its followed by group by clause
Write a query to display the current date. Label the column Date.
Select sysdate "Date" from dual;
For each employee, display the employee number, last_name, salary, and salary increased by 15% and expressed as a whole number. Label the column New Salary. Place your SQL statement in a text file named lab3_2.sql.
Select Employee_id,Last_name, Salary,Round(( Salary*(15/100)+salary)) as "New Salary" From
Employees;
Modify your query lab3_2.sql to add a column that subtracts the old salary from the new salary. Label the column Increase. Save the contents of the file as lab3_4.sql. Run the revised query.
Select Employee_id,Last_name, Salary,Round(( Salary*(15/100)+salary)) as "New Salary",Round(( Salary*(15/100)+salary)) - Salary as "Increment" From Employees;
Write a query that displays the employee’s last names with the first letter capitalized and all other letters lowercase, and the length of the names, for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.
Select Initcap(Last_name) Last_name ,Length(Last_name) Length From Employees Where Last_Name like 'J%' or Last_Name like 'A%' or Last_Name like 'M%'
order by Last_name desc;
For each employee, display the employee’s last name, and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.
Select Last_Name, Round (Months_between(sysdate ,Hire_date)) "Months Worked" from employees;
Write a query that produces the following for each employee: <employee last name> earns <salary> monthly but wants <3 times salary>. Label the column Dream Salaries.
Select Last_Name ||' Earns ' || salary ||'Monthly But Wants '|| Salary*3 "Dream Salaries" from employees;
Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with $. Label the column SALARY.
select last_name, Lpad(salary,'15','$') from employees;
Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”
select Last_name ,Hire_date , add_months(Hire_date,6) as saldate from employees;
SELECT last_name,hire_date,TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), 'Monday'),'DAY,"THE" DDSP "OF" MONTH YYYY') "REVIEW"
FROM employees;
Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday.
Select last_name, hire_date, to_char(To_date(Hire_date),'Day') "DAY" from employees order by to_char(hire_date-1,'d');
Select last_name, hire_date, to_char((Hire_date),'Day') from employees;
Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, put “No Commission.” Label the column COMM.
Select last_name,NVL(NULL,'No Commission') from employees;
Select last_name,commission_pct, NVL(Null,'No Commission') from employees;
Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, put “No Commission.” Label the column COMM.
SELECT Last_Name,NVL(TO_Char(Commission_pct),'No Commision') "COMM" FROM Employees;
Create a query that displays the employees’ last names and indicates the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES.
SELECT last_name||' '|| rpad(' ', (salary*12)/1000, '*') EMPLOYEES_AND_THEIR_SALARIES FROM employees ORDER BY salary DESC;
Using the DECODE function, write a query that displays the grade of all employees based on the value of the column JOB_ID, as per the following data:
Job Grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
None of the above 0
SELECT job_id, decode (job_id, 'ST_CLERK', 'E',
'SA_REP', 'D',
'IT_PROG', 'C',
'ST_MAN', 'B',
'AD_PRES', 'A', '0')GRADE FROM employees;
No comments:
Post a Comment