Normally, when it comes to preparation of Interview, we do search online materials for study. I decided to optimize the material all at a place.
We start with SQL & PL/SQL Study guide, in a most easy way. Lets start with SQL.
SQL - Structured Query Language. I will start with Data Types.
SQL supports varied number of Data Types, as follows -
SQL Data Types - Data Type defines what type of data, a column can contain Character(n) or Char(n), where n defines the length. Default value of 'n' is 1, when length is not specified. Character(0) or char (0) i.e when n= 0, will lead to an error. If you try to assign a data of length , which is more than the length specified, it will also cause error. But, if you try to assign a data of smaller length, the character will display the value with padded spaces. The following example will clear the confusion
Defined : emp_name char(10) , emp_name is a variable.
emp_name := 'Johny Jonardan', will lead to an error ( numeric or value error ), as length of the variable is more than 10.
emp_name := 'Johny', will not lead to an error , however, total length will be 10, padded spaces will be 5.
Note - I found some Interview questions on this length concept, eg - what will be the total length of the variable , when it is defined as a character of a fixed length. Answer would be, it will always give you the fixed length defined, irrespective of the actual characters present. If its more, it will lead to error, if its less, take account of the padded spaces.
Note - Minimum length ( default ) would be 1 and Maximum length can be size of the page ( i.e. page size ).
Varchar(n) , where 'n' defines the maximum allowable length of the variable. Here, the concept is almost the same as char(n), the only difference is it will be be padded with spaces, if length of the actual parameter is less than what is defined. Hence, if you query the length of the actual value, this will give you the actual length.
Note - If the length is more than the defined maximum value, it will lead to the error. Minimum length of varchar(n) is 1, which is default, and maximum would be the size of the page.
Boolean - Boolean data type supports only 'TRUE' or 'FALSE' . Its pretty easy to understand. If you try to assign '1','0', yes, no, etc value, it will lead to an error.
SMALLINT - SmallInt data types behaves the same was, as we already know about the Integer data type, except the range is pretty less compare to Integer. The only thing that we need to remember is the range , which is (2^-15) to (2^15 -1 ).
Integer or INT - This is most commonly used data type in SQL, between the range ( 2^-31) to (2^31-1). This the difference with the smallint.
Decimal(p,s) or Dec(p,s) , where 'p' defines the precision and 's' defines the scale.
Concept behind the Precision & Scale : 'p' or precision holds the total length of the number, while 's' or scale would be the length after the decimal point. For example - dec(10,5) can hold the max value 99999.99999, where total length would be 10, as defined in 'p'. Some important points are as follows -
Default value of 'p' is 38, and default value of 's' is 0. 's' is optional. If you exceed the number of digits in left of the decimal point, it will lead to an error, however, extra digits will get truncated in the right of your decimal point. 's' value cannot be greater than 'p' value.
Numeric(p,s) - Numeric Data types works in the same way as Decimal, pretty simple, no need of bragging.
Real - Real Data type do not need any parameter and accepts numeric values. Default precision range is 64. Any exceeding value will lead to an error.
Float(p) - Maximum precision value would be 64, and accepts Numeric values. Pretty simple to understand.
Double Precision - Double Precision do not require any parameter, only remember the range, its 64 max. Exceeding this will lead to an error.
Date - Its a very commonly used data type in Oracle. Remember its Range - 1st January, 0001 to 31st December, 9999. You can manipulate the format using to_date function. We will discuss the details of Date functions and different formats on a later stage.
Time- Time accepts the Time values in the format HH:MM:SS.
DateTime - Its a combination of Date and Time. eg - dd-mon-yyyy hh:mi:ss pm. We will discuss later on the formats, at a later stage.
Timestamp - Its again a combination of Date and Time, behaves in a similar way to DateTime data type.
CLOB(n) , where 'n' represents the length of the Character Large Object. This is ideally used when we want to store the Character of length, which is larger than that can be hold in simple char(n) or varchar(n).
Maximum length that can be used in CLOB is 2 GB and Minimum is 1 Byte. Default length is also 1 Byte.
BLOB(n) , where 'n' represents the length of Large Binary Object. Its basically behaves the same way as CLOB, except it store the Binary Data.
BLOB data type is not usable with SQL Scalar functions.
This are all the SQL Data Types, you need to know before going to an Interview. However, I will list down all other Data Types of SQL Server, in this post, later on.
Good Luck !
Comments are appreciable, for improvement of the Posts !!
We start with SQL & PL/SQL Study guide, in a most easy way. Lets start with SQL.
SQL - Structured Query Language. I will start with Data Types.
SQL supports varied number of Data Types, as follows -
SQL Data Types - Data Type defines what type of data, a column can contain Character(n) or Char(n), where n defines the length. Default value of 'n' is 1, when length is not specified. Character(0) or char (0) i.e when n= 0, will lead to an error. If you try to assign a data of length , which is more than the length specified, it will also cause error. But, if you try to assign a data of smaller length, the character will display the value with padded spaces. The following example will clear the confusion
Defined : emp_name char(10) , emp_name is a variable.
emp_name := 'Johny Jonardan', will lead to an error ( numeric or value error ), as length of the variable is more than 10.
emp_name := 'Johny', will not lead to an error , however, total length will be 10, padded spaces will be 5.
Note - I found some Interview questions on this length concept, eg - what will be the total length of the variable , when it is defined as a character of a fixed length. Answer would be, it will always give you the fixed length defined, irrespective of the actual characters present. If its more, it will lead to error, if its less, take account of the padded spaces.
Note - Minimum length ( default ) would be 1 and Maximum length can be size of the page ( i.e. page size ).
Varchar(n) , where 'n' defines the maximum allowable length of the variable. Here, the concept is almost the same as char(n), the only difference is it will be be padded with spaces, if length of the actual parameter is less than what is defined. Hence, if you query the length of the actual value, this will give you the actual length.
Note - If the length is more than the defined maximum value, it will lead to the error. Minimum length of varchar(n) is 1, which is default, and maximum would be the size of the page.
Boolean - Boolean data type supports only 'TRUE' or 'FALSE' . Its pretty easy to understand. If you try to assign '1','0', yes, no, etc value, it will lead to an error.
SMALLINT - SmallInt data types behaves the same was, as we already know about the Integer data type, except the range is pretty less compare to Integer. The only thing that we need to remember is the range , which is (2^-15) to (2^15 -1 ).
Integer or INT - This is most commonly used data type in SQL, between the range ( 2^-31) to (2^31-1). This the difference with the smallint.
Decimal(p,s) or Dec(p,s) , where 'p' defines the precision and 's' defines the scale.
Concept behind the Precision & Scale : 'p' or precision holds the total length of the number, while 's' or scale would be the length after the decimal point. For example - dec(10,5) can hold the max value 99999.99999, where total length would be 10, as defined in 'p'. Some important points are as follows -
Default value of 'p' is 38, and default value of 's' is 0. 's' is optional. If you exceed the number of digits in left of the decimal point, it will lead to an error, however, extra digits will get truncated in the right of your decimal point. 's' value cannot be greater than 'p' value.
Numeric(p,s) - Numeric Data types works in the same way as Decimal, pretty simple, no need of bragging.
Real - Real Data type do not need any parameter and accepts numeric values. Default precision range is 64. Any exceeding value will lead to an error.
Float(p) - Maximum precision value would be 64, and accepts Numeric values. Pretty simple to understand.
Double Precision - Double Precision do not require any parameter, only remember the range, its 64 max. Exceeding this will lead to an error.
Date - Its a very commonly used data type in Oracle. Remember its Range - 1st January, 0001 to 31st December, 9999. You can manipulate the format using to_date function. We will discuss the details of Date functions and different formats on a later stage.
Time- Time accepts the Time values in the format HH:MM:SS.
DateTime - Its a combination of Date and Time. eg - dd-mon-yyyy hh:mi:ss pm. We will discuss later on the formats, at a later stage.
Timestamp - Its again a combination of Date and Time, behaves in a similar way to DateTime data type.
CLOB(n) , where 'n' represents the length of the Character Large Object. This is ideally used when we want to store the Character of length, which is larger than that can be hold in simple char(n) or varchar(n).
Maximum length that can be used in CLOB is 2 GB and Minimum is 1 Byte. Default length is also 1 Byte.
BLOB(n) , where 'n' represents the length of Large Binary Object. Its basically behaves the same way as CLOB, except it store the Binary Data.
BLOB data type is not usable with SQL Scalar functions.
This are all the SQL Data Types, you need to know before going to an Interview. However, I will list down all other Data Types of SQL Server, in this post, later on.
Good Luck !
Comments are appreciable, for improvement of the Posts !!
