1 / 25

Oracle SQL

Oracle SQL. Number Functions Miscellaneous Functions Date and Conversion Functions Date Interval Calculations Extracting, Overlapping and Casting. Number Functions. Number Functions. Number Functions. Number Functions continued. Miscellaneous Functions. Miscellaneous Functions.

althea
Download Presentation

Oracle SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle SQL Number Functions Miscellaneous Functions Date and Conversion Functions Date Interval Calculations Extracting, Overlapping and Casting

  2. Number Functions Number Functions

  3. Number Functions Number Functions continued

  4. Miscellaneous Functions Miscellaneous Functions

  5. Miscellaneous Functions Miscellaneous Functions continued

  6. Date-related Data Types • Datetime data types • Keeps track of both date and time • Interval data types • Express differences between dates and times

  7. Datetime Data Types Data Type

  8. Interval Data Types Supported Time Differences Data Type

  9. Date Formatting • When using SQL to retrieve table data, you will often access DATE values • Oracle’s DATE data type consists of a date and time that are stored in an internal format that keeps track of • Century • Year • Month • Day • Hour • Minute • Second • The normal default display is of the form • DD-MON-YY

  10. Date Formatting • Select col_date • From date_exampleTry it! • Select col_date, • to_char(col_date, ‘MM/DD/YYYY’) As “Formatted” • From date_exampleTry it! • TO_DATE does just the opposite of TO_CHAR • It converts a text literal into a DATE data type • To change the default display of DATE data type values, use the TO_CHAR function • Include a format mask

  11. Date Formatting • Date formatting is a large, complex subject • We will be focusing on the basics • Web sites that may be helpful • http://techonthenet.com/oracle/functions/to_char.php • http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

  12. Performing a Date Search • Sometimes you need to do a query based on certain date criteria • Two approaches • Use your knowledge of the default value • Tools  Preferences  Database  NLS • Supply the proper format mask

  13. Performing a Date Search SELECT last_name, registration_date Try it! FROM student WHERE registration_date = to_date('22-Jan-2007', 'DD-MON-YYYY') SELECT last_name, registration_date Try it! FROM student WHERE registration_date = '22-Jan-2007’ SELECT last_name, registration_date Try it! FROM student Why does it work? WHERE registration_date = '22-Jan-07’ Based on how the default date is defined within Oracle

  14. The Time Component SELECT student_id, to_char(enroll_date, 'DD-MON-YYYY HH24:MI:SS') FROM enrollment WHERE trunc(enroll_date) = To_date('07-FEB-2007', 'DD-MON-YYYY') Try it! What happened? Remove trunc. Execute it. What happened? Why? You can query rows for a specific time or ignore the time completely

  15. The SYSDATE Function • In some cases you will need to determine the differences between two date values or calculate dates in the future or the past • Once again Oracle provides multiple functions • SYSDATE • Returns the operating system’s current date and time • Does not take any parameters • The time zone depends on where your database server is located that you connect to

  16. Find the Number of Days between Dates Select To_Date(‘01-JAN-2015’, ‘DD-MON-YYYY’) – TRUNC(Sysdate) int, To_Date(‘01-JAN-2015’, ‘DD-MON-YYYY’) – Sysdate dec From dual Before entering this SQL statement, determine what it will do In particular: What does the To_Date function do? What does the TRUNC function do? What is the int used for? What happens if you leave it off? What is the dec used for? What happens if you leave it off? What is the dual table? Why use it? Try it. What happened? Why?

  17. Find Future Dates and Times Select To_Char(Sysdate, ‘MM/DD HH24:MI:SS’) now, To_Char(Sysdate + 3/24, ‘MM/DD HH24:MI:SS’) As now_plus_3hrs, To_Char(Sysdate + 1, ‘MM/DD HH24:MI:SS’) tomorrow, To_Char(Sysdate + 1.5, ‘MM/DD HH24:MI:SS’) As “36 hrs from now” From dual What happened? Why? Try 1/8 instead of 3/24. Did it work? Why or why not?

  18. Extracting Date Values Select To_Char( start_date_time, ‘DD-MON-YYYY’) “Start Date”, EXTRACT( MONTH FROM start_date_time) “Month”, EXTRACT( YEAR FROM start_date_time ) “Year”, EXTRACT( DAY FROM start_date_time ) “Day” From section WHERE EXTRACT( MONTH FROM start_date_time ) = 4 ORDER BY start_date_time What happened? Why?

  19. TIMESTAMP Data Type • The TIMESTAMP data type • Allows the storing of optional fractional seconds with a precision of up to 9 digits • The default is 6 digits • The format mask for a time stamp • ‘DD-MON-RR HH.MI.SS.FF AM’ • ‘28-JAN-11 10.17.35.300784 AM’ • Why would you ever want to use a date/time value so exact?

  20. Other Topics • EXTRACT • The EXTRACT function can be used to extract specific components from date and time values • The day or hour or second, for example • OVERLAPS • Used to determine whether two time periods overlap • Used to determine if two meetings may conflict with other, for example

  21. Casting • SQL does not allow values or columns to be compared if they are not of the same or compatible data type • In some cases, Oracle can implicitly convert one data type to another • It is preferable to explicitly specify any data type conversions using a function • Why would this be preferable?

  22. Number Functions Practice • Write a query to display the signs of -14, 3 and 0 • Use the Dual table • Special table, containing only one row of one column containing ‘X’ • Used for non-specific queries • Manipulating text literals, numbers, today’s date, etc. • Always returns one row • Write one query to display the truncation, mod and full division of 7/3 • What is the difference? Why?

  23. Miscellaneous Functions Practice SELECT 60 + 60 + NULL FROM dual Now try this one: SELECT 60 + 60 + NVL(NULL, 1000) FROM dual Execute the following SQL statement and notice the result

  24. SYSDATE Practice Select SYSDATE, To_Char( Sysdate, ‘dd-mon-yyyy HH24:MI’ ) From dual; What happened? Why? Select SYSDATE, To_Char( Sysdate, ‘dd-MON-yyyy HH24:MI’ ) From dual Notice the month value. What happened? Why?

  25. For March 12th • Aggregate Functions • Quiz • Project 11 is due on March 26th • Project 12 is due on April 2nd • Exam #2 on April 7th • Computer-based

More Related