Tema nr.
6
Observație!
Scrieți rezolvarea direct în acest document!
1. What is the length of the string “Oracle Internet Academy”?
SELECT LENGTH (‘Oracle Internet Academy ‘)
FROM dual
LENGTH('ORACLEINTERNETACADEMY')
23
1 rows returned in 0.00 seconds
2. What’s the position of “I” in “Oracle Internet Academy”?
SELECT instr('Oracle Internet Academy','I')
FROM dual
INSTR('ORACLEINTERNETACADEMY','I')
8
1 rows returned in 0.00 seconds
3. Write a query to return the first character of the first name concatenated to the last_name,
the salary and the department id for employees working in department 20. Give the first
expression an alias of Name. Use the EMPLOYEES table. Change the query to use a substitution
variable instead of the hard coded value 20 for department id. Run the query for department 30
and 50 without changing the original where-clause in your statement.
SELECT SUBSTR('first_name',1,1)||last_name AS "Name",SALARY
AS "salary",DEPARTMENT_ID AS "department_id" FROM EMPLOYEES WHERE
department_id=:department
Name salary department_id
fHartstein 13000 20
fFay 6000 20
2 rows returned in 0.00 seconds
4. Write a query that returns all the employee data depending on the month of their hire date.
Use the EMPLOYEES table. The statement should return the month part of the hiredate which is
then compared to an abbreviated month (JAN, FEB, MAR) passed into the query via a substitution
variable.
SELECT *
FROM EMPLOYEES
WHERE TO_CHAR(hire_date,'MON')=:hire_date
EMPLO FIRST_N LAST_N PHONE_NU HIRE_D SALAR COMMISSI MANAGE DEPARTME
YEE_ID AME AME EMAIL MBER ATE JOB_ID Y ON_PCT R_ID NT_ID
DLOREN 07/Feb/19 IT_PRO
107 Diana Lorentz 590.423.5567 4200 - 103 60
TZ 99 G
MHART 17/Feb/19 MK_M
201 Michael Hartstein 515.123.5555 13000 - 100 20
STE 96 AN
2 rows
returned
in 0.01
seconds
5. Display employee last_name and salary for those employees who work in department 80.
Give each of them a raise of 5.33% and truncate the result to two decimal places.
6. Divide each employee’s salary by 3. Display only those employees’ last names and salaries
who earn a salary that is a multiple of 3.
7. Your next appointment with the dentist is six months from today. On what day will you go to
the dentist? Name the output, “Appointment.”
SELECT ADD_MONTHS(SYSDATE,6)
FROM DUAL
ADD_MONTHS(SYSDATE,6)
08/Oct/2014
1 rows returned in 0.00 seconds
8. The teacher said you have until the last day of this month to turn in your research paper.
What day will this be? Name the output, “Deadline.”
SELECT LAST_DAY(SYSDATE)
FROM dual
LAST_DAY(SYSDATE)
30/Apr/2014
1 rows returned in 0.00 seconds
9. How many months between your birthday this year and January 1 next year?
SELECT MONTHS_BETWEEN('1/jan/2015','18/aug/2014')
from dual
MONTHS_BETWEEN('1/JAN/2015','18/AUG/2014')
4.4516129032258064516129032258064516129
1 rows returned in 0.00 seconds
10. Write a statement that will return the Oracle database employee’s employee ID and their
starting hire dates between January 1, 1997 and today. Display the result ordered from most recently
hired to the oldest.
11. Convert January 3, 2011, to the default date format 03-JAN-11.
12. Ellen Abel is an employee who has received a $2,000 raise. Display her first name and last
name, her current salary, and her new salary. Display both salaries with a $ and two decimal places.
Label her new salary column AS New Salary.
13. Create one query that will convert 25-DEC-04 into each of the following (you will have to
convert 25-DEC-04 to a date and then to character data):
December 25th, 2004
DECEMBER 25TH, 2004
25th december, 2004
14. The manager of Global Fast Foods has decided to give all staff that currently does not earn
overtime an overtime rate of $5.00. Construct a query that displays last names and overtime rate
shown as $5.00.
15. Not all Global Fast Foods staff members have a manager. Create a query that displays the
employee last name and 9999 in the manager ID column for these employees.
