Oracle assignment on functions

Use the table you created for the last assignment, or the emp table or use dual to test depending on the question. You can get emp and related tables by going to my section on installation and links and following the directions in: Sample tables yu can import Thanks to Eric Oldford and Andrew Downing.

  1. 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:
  2. Display a number, the number squared, the number cubed and the square root of the number.
  3. Display a decimal number and then display it rounded and truncated. Use a number where rounded and truncated will display different values.
  4. Show me todays date and the date of the next Thursday.
  5. 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.
  6. Use to_char and extract the month in numbers from the sysdate.
  7. 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.
  8. Using the database from the last assignment, pick a field and CODE a decode statement that handles different values in the field differently.
  9. 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.
  10. Again using the table from last week group on one field and show me the count and the sum of your numeric field.
  11. Repeat the query above but exclude one or more records as you form the groups.
  12. Repeat the query again but now also exclude one of the groups from the list.
  13. 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.