Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Understanding SQL Basics

By: FYIcenter.com

Part:   1   2  3  4  5  6 

A collection of 28 FAQs on Oracle SQL language basics. Clear answers are provided with tutorial exercises on data types, data literals, date and time values, data and time intervals, converting to dates and times, NULL values, pattern matches. Topics included in this FAQ are:

  1. What Is SQL?
  2. How Many Categories of Data Types?
  3. What Are the Oracle Built-in Data Types?
  4. What Are the Differences between CHAR and NCHAR?
  5. What Are the Differences between CHAR and VARCHAR2?
  6. What Are the Differences between NUMBER and BINARY_FLOAT?
  7. What Are the Differences between DATE and TIMESTAMP?
  8. What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
  9. What Are the Differences between BLOB and CLOB?
  10. What Are the ANSI Data Types Supported in Oracle?
  11. How To Write Text Literals?
  12. How To Write Numeric Literals?
  13. How To Write Date and Time Literals?
  14. How To Write Date and Time Interval Literals?
  15. How To Convert Numbers to Characters?
  16. How To Convert Characters to Numbers?
  17. How To Convert Dates to Characters?
  18. How To Convert Characters to Dates?
  19. How To Convert Times to Characters?
  20. How To Convert Characters to Times?
  21. What Is NULL?
  22. How To Use NULL as Conditions?
  23. How To Concatenate Two Text Values?
  24. How To Increment Dates by 1?
  25. How To Calculate Date and Time Differences?
  26. How To Use IN Conditions?
  27. How To Use LIKE Conditions?
  28. How To Use Regular Expression in Pattern Match Conditions?

Sample scripts used in this FAQ can be executed with SQL*Plus to any Oracle database server.

What Is SQL?

SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.

How Many Categories of Data Types?

Oracles supports the following categories of data types:

  • Oracle Built-in Datatypes.
  • ANSI, DB2, and SQL/DS Datatypes.
  • User-Defined Types.
  • Oracle-Supplied Types.

What Are the Oracle Built-in Data Types?

There are 20 Oracle built-in data types, divided into 6 groups:

  • Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
  • Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
  • Long and Row Datatypes - LONG, LONG RAW, RAW
  • Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
  • Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
  • Row ID Datatypes - ROWID, UROWID

What Are the Differences between CHAR and NCHAR?

Both CHAR and NCHAR are fixed length character data types. But they have the following differences:

  • CHAR's size is specified in bytes by default.
  • NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used.
  • NCHAR stores characters in Unicode.

What Are the Differences between CHAR and VARCHAR2?

The main differences between CHAR and VARCHAR2 are:

  • CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
  • VARCHAR2 stores values in variable lengths. Values are not padded with any characters.

What Are the Differences between NUMBER and BINARY_FLOAT?

The main differences between NUMBER and BINARY_FLOAT are:

  • NUMBER stores values as fixed-point numbers using 1 to 22 bytes.
  • BINARY_FLOAT stores values as single precision floating-point numbers.

What Are the Differences between DATE and TIMESTAMP?

The main differences between DATE and TIMESTAMP are:

  • DATE stores values as century, year, month, date, hour, minute, and second.
  • TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.

What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?

The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are:

  • INTERVAL YEAR TO MONTH stores values as time intervals at the month level.
  • INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level.

What Are the Differences between BLOB and CLOB?

The main differences between BLOB and CLOB are:

  • BLOB stores values as LOB (Large OBject) in bitstreams.
  • CLOB stores values as LOB (Large OBject) in character steams.

(Continued on next part...)

Part:   1   2  3  4  5  6 


Selected Developer Jobs:

More...