Oracle assignment on functions
You should write a script to get the emp table/file and the ones related to it that can be found
by searching on the web. I actually gave you the emp script to run in a previous week.
There are four tables that Oracle used: emp, dept, bonus and salgrade.
They are available in many places on the web. One site is:
Set of Oracle tables
- Do a query using single row functions that flip the name so that it reads first middle (if any) last
instead of last/first middle. For example:
- Doe/John M should be flipped to be John M Doe
- Jones/Mary should be flipped to Mary Jones
- Adams-Costa/Susan Ann should be flipped to be Susan Ann Adams-Costa
- Langley/M Richard should be flipped to be M Richard Langley
- Display a number, the number squared, the number cubed and the square root of the number.
- Display a decimal number and then display it rounded and truncated. Use a number where rounded
and truncated will display different values.
- Show me todays date and the date of the next Thursday.
- Show me todays date ad in next_day use a number from 1 to 7 instead of Thursday. What do you get?
Explain how it works.
- Use to_char and extract the month in numbers from the sysdate.
- SELECT TO_CHAR(89-100,pr) is trying to put the result of the subtraction in < > or as my notes
say in parenthesis. It is not working due to multiple errors, please fix it.
- Using the database from the last assignment, pick a field and CODE a decode statement that handles
different values in the field differently.
- Calculate a code number in the following way using the table you created for the last assignment. The
number has to be the third and fourth characters of one of your fields concatenated with the mod
from your numeric field divided by 3, concatenated with the last name from the name field concatenated
with the length of one of your fields.
- Again using the table from last week group on one field and show me the count and the sum of
your numeric field.
- Repeat the query above but exclude one or more records as you form the groups.
- Repeat the query again but now also exclude one of the groups from the list.
- Using emp I want you to eliminate the job of CLERK as you form the groups and I want you to
eliminate department 30 from the groups that you display. I want the display to also be in order
by dept. You should group on dept and you should display the dept and the sum and avg of the salary
for the dept.