Database Management System (DBMS) - 2130703, (GTU) Gujarat Technological University Lab Manual Solution - 4

Practical -4

To study Single-row functions

1.     Write a query to display the current date. Label the column Date

SELECT sysdate "Date" FROM dual;

2.     For each employee, display the employee number, job, salary, and salary increased by 15% and expressed as a whole number. Label the column New Salary.

SELECT emp_n,emp_sal ,emp_sal+ROUND (emp_sal * 0.15) As New Salary FROM employee;

3.     Modify your query no 4.(2) to add a column that subtracts the old salary from the new salary. Label the column Increase.

SELECT emp_n,emp_ sal, ROUND (emp_sal+emp_sal * 0.15 emp_sal)"Increase" FROM employee;

4.     Write a query that displays the employee’s 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(emp_name) "Name", LENGTH(emp_name) "Length" FROM employee WHERE emp_name LIKE 'J%' OR emp_name LIKE  'M%' OR emp_name LIKE 'A%' order by emp_name.

5.     Write a query that produces the following for each employee:<employee last name> earns <salary> monthly.

SELECT emp_name ||' earns ' || emp_sal ||’monthly’ from employee.

6.     Display the name, hire date, number of months employed and day of the week on which the employee has started. Order the results by the day of the week starting with Monday.

SELECT cname,months_between(sysdate,adate) as months  ,to_char(adate,’day’) as day from deposit order by to_char(adate -  1,‘day’)

7.     Display the hiredate of emp in a format that appears as Seventh of June 1994 12:00:00 AM.

SELECT TO_CHAR(adate,'fmDdspth "of" Month YYYY fm HH:MI:SS AM') Result FROM employee;

8.     Write a query to calculate the annual compensation of all employees (sal+comm.)

SELECT EMP_SAL + EMP_COMM FROM EMPLOYEE