Oracle Interview Questions

24. How can we create privileges in Oracle?

A privilege is said to be nothing but the right to implement an SQL query or to access another user object. Privilege may be provided as user privilege or system privilege.

[sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]

These Oracle Interview Questions help you to brush up your knowledge before appearing for an interview.

25. Define VArray?

VArray is defined as an Oracle data type which has been used to have columns that contain multivalued attributes. It holds the bounded array of values.

26. How can we obtain field details of a table?

Describe <Table_Name> has been used to obtain the specific table field details.

27. Explain the difference between alias and rename?

Alias is referred to be a temporary name which is given to a column or table whereas Rename is known as a permanent name that is given to a column or table. Alias is said to be an alternate table name or column name and Rename is considered to be nothing but the replacement of name.

These Oracle Interview Questions with Answers gives a clear idea about what an interviewer may ask during the time of interview.

28. What is referred as a View?

A view is defined as a logical table that based on one or more views or tables. The tables upon that the view is based are known as Base Tables and it does not have data.

29. Define cursor variable

A cursor variable has been associated with various statements that hold different values during the runtime. A cursor variable is said to be a kind of the reference type.

30. What is called as the SET operators?

SET operators have been used with two or more queries. Those operators are Minus, Union All, Intersect, Union.

31. What is known as the cursor attributes?

Each cursor exists in Oracle contain set of attributes that enable the application program to examine the cursor state. Thus the attributes may be used for checking whether a cursor is closed or opened, found or not found and also identify the row count.

32. What is the procedure to delete the duplicate rows in a table?

The duplicate rows in a table can be deleted with the use of ROWID.

Prepare these Oracle Interview Questions well and get placed in a high paid job.

33. Explain about the attributes of the cursor

There are three types of Attributes of Cursor. They are

%Found

Returns NULL if the cursor is open and the fetch has not been implemented

Returns TRUE if the cursor fetch has been successfully executed

Returns False if no rows have been returned.

%NOT FOUND

Returns NULL if the cursor is open and fetch is not executed

Returns False if the fetch is implemented

Returns True if no row was returned

%ISOPEN

Returns true if a cursor is open

Returns false if a cursor is closed

%ROWCOUNT

Returns the no of rows fetched. It must be iterated through the entire cursor to provide the exact real count

34. Is it possible to store the pictures in the database and if so, How it is to be done?

Yes, we are able to store pictures in the database by the Long Raw Datatype. This datatype has been used to store the binary data for 2 gigabytes of length. But the table should have only on the Long Raw datatype.

35. Define integrity constraint?

The integrity constraint is said to be a declaration defined a business rule for the table column. Integrity constraints have been used to make sure the consistency and accuracy of data in a database. There are types such as Domain Integrity, Referential Integrity & Domain Integrity.

36. Define ALERT

An alert is said to be a window that has been appearing in the center of the screen which is overlaying a portion of a current display.

37. Explain hash cluster

Hash cluster is referred as a technique that is used to store the table for rapid retrieval. Apply hash value on the table to retrieve rows from a table.

38. Describe the various constraints that are used in Oracle

The constraints used in the Oracle are listed below

NULL: It specifies a particular column which can have NULL values

NOT NULL: It indicates that the particular column cant have NULL values

CHECK: Evaluate the values that in the provided column in order to reach the specific criteria.

DEFAULT: It indicates the value that has been assigned to the default value.

39. Describe the difference between INSTR and SUBSTR

INSTR gives character position in which the pattern has been found in a string and SUBSTR returns the particular portion of a string.

INSTR returns numeric whereas SUBSTR returns a string.

40. What is referred as the parameter mode which can be passed to a procedure?

IN, OUT, and INOUT are the parameter modes which can be passed to a procedure.

41. Explain the various Oracle Database objects?

There are various data objects in Oracle –

Tables – it is set of elements organized in horizontal and vertical

Views – it is the virtual table derived from one or more tables

Indexes – it is the performance tuning method for generating the records

Synonyms – Alias name for the tables

Sequences – Multiple users process unique numbers

Tablespaces – Logical storage unit in Oracle

42. Describe the differences between List item and LOV

List items are said to be single item whereas LOV is property. List of items has set to be a collection of the list of items. List of an item can contain only one column whereas LOV may have one or more columns.

Learn these Oracle Interview Questions with Answers to get placed in a well-reputed company.

43. What is known as Grants and Privileges?

Privileges are referred as the rights to implement SQL statements which means right to connect. Grants are provided to the object, thus the objects may be accessed accordingly. Grants can be given by the creator or owner of an object.

It is one of the commonly asked Oracle Interview Questions For Freshers at the time of interview.

44. Define the difference between $ORACLE_BASE and $ORACLE_HOME

ORACLE_BASE is referred to be the root or main directory of an Oracle whereas ORACLE_HOME has been located beneath base folder in which all the Oracle products reside.

45. What is known as the fastest query method to obtain data from the tablet?

The row may be obtained from the table with the use of ROWID. The usage of ROW ID is the fastest query method to get data from the table.

46. What is referred as the maximum number of triggers which can be applied to a single table?

12 is considered to be the maximum no of triggers which can be applied to a single table.

These Oracle Interview Questions with Answers help you enhance your career.

47. In what way we can display row numbers with records?

For displaying row numbers with the record numbers:

1 Select rownum, <fieldnames> from table;

This query displays field name and row numbers from the table.

48. How the last record added to a table can be viewed by us?

Last record may be added to a table and this can be done by:

1 Select * from (select * from employees order by rownumdesc) where rownum<2;

49. Define the difference between Cross Join and Cartesian Join

There are no differences that are occurred between the join. Cross and Cartesian joins are considered to be same. Cross joins provides a Cartesian product of two tables – Rows from the first table has been multiplied by another table which is known as a Cartesian product.

Cross join without where the clause provides the Cartesian product.

50. What is known as the data type of DUAL table?

The DUAL table is said to be a one-column table that is present in Oracle database. The table contains a single VARCHAR(1) column known as DUMMY that holds a value of ‘X’.

51. In what way we can display employee records who obtain more salary than the average one in the department?

This can be done by the following query:

1 Select * from employee where salary>(select avg(salary)) from dept, employee where

52. What is known as PL SQL?

PL SQL is considered to be a procedural language that has interactive SQL, as well as the procedural programming language which constructs such as iteration and conditional branching.

Prepare these SQL Server Interview Questions well before appearing for the interview.

53. Describe the difference between TYPE RECORD and % ROWTYPE

The TYPE RECORD has been used when a query gives back the column of various tables or views.

For e.g., TYPE r_emp is RECORD (snosmp.smpno%type,snamesmpsname %type)

e_recsmp %ROWTYPE

Cursor c1 is select smpno, dept from smp;

e_rec c1 %ROWTYPE

54. Describe the uses of the cursor

The cursor is said to be a named private area present in SQL from which the information can be accessed. They are necessary for functioning each row individually for the queries which return multiple rows.

It is one of the important PL SQL Interview Questions.

55. Display cursor code for loops

Cursor states %ROWTYPE as the loop index implicitly. Then it opens a cursor, obtains rows of values from active set in the fields of the record and shuts when all the records are being processed.

For e.g., FOR smp_rec IN C1 LOOP

Totalsal=totalsal+smp_recsal;

ENDLOOP;

These Oracle SQL Interview Questions help you to get through the interview easily.

56. What are the uses of a database trigger?

A PL/SQL program unit has been associated with a specific database table that is known as a database trigger. Its uses are listed below

1. Log events transparently
2. Maintaining replica tables
3. Enforce complex business rules
4. Executing complex security authorizations
5. Deriving column values
6. Auditing data modifications

57. Describe the two various kinds of exceptions

Error handling section of PL/SQL block is known as Exception. They contain two types such as predefined and user-defined.

It is one of the important Oracle PL SQL Interview Questions which has been asked during the interview.

58. Describe Raise_application_error.

It is considered to be a procedure of package DBMS_STANDARD which permits issuing of user_defined error messages from the database trigger or saved sub-program.

It is considered to be the commonly asked Basic SQL Interview Questions. So prepare well to crack the interview in a successful way.

59. Display how procedures and functions are known in a PL SQL block.

Function is known as the part of an expression.

total:=calculate_sal(‘b644’)

Procedure is known as a statement in PL/SQL.

Calculate_bonus(‘b644’);

Click Here to know about DevOps Interview Questions For Freshers
58. 60. Describe two virtual tables that are available during database trigger execution.

    Table columns are known as THEN.column_name and NOW.column_name.

    For INSERT related triggers, NOW.column_name values are available only.

    For DELETE related triggers, THEN.column_name values are available only.

    For UPDATE related triggers, both Table columns are available.

    61. Describe the rules to be applied to NULLs during comparisons.

    Prepare these Advanced SQL Interview Questions and brush up your knowledge to get placed in a high paid job.

    1. NULL is never TRUE or FALSE
    2. NULL cannot be unequal or equal to other values
    3. If a value present in an expression is said to be NULL, then the expression itself estimates to NULL except for the concatenation operator (||)
    62. Explain the process of PL SQL compiled

    The compilation process such as syntax check, bind and p-code generation processes.

    The syntax checking examines the PL SQL codes for compilation errors. When all the errors are said to be corrected, a storage address has been assigned to the variables which hold data. It is known as binding. P-code is considered to be a list of instructions for the PL SQL engine. P-code has been saved in the database for the name blocks and has been used the next time it is implemented.

    63. Explain the difference between Syntax and runtime errors.

    A syntax error can easily be detected by a PL/SQL compiler. For e.g., incorrect spelling.

    A runtime error is managed with the help of exception-handling part in a PL/SQL block. For e.g., SELECT INTO statement, that does not provide back any rows.

    64. Describe Commit, Rollback, and Savepoint.

    COMMIT statement

    1. Other users may see the data changes made by the transaction
    2. The locks accomplished by the transaction have been released
    3. The work which was done by the transaction becomes permanent

    ROLLBACK statement: it gets issued while the transaction ends. Hence the following is true.

    1. The work done in a transaction has been undone as if it was never issued.
    2. All locks accomplished by a transaction have been released.

    It undoes all the work which was done by the user in a transaction. Using SAVEPOINT, only part of the transaction may be undone.

    65. What is ORDBMS? Can we say oracle as ORDBMS?

    The full form of ORDBMS is object relational database management system. This form of database management system is called as a hybrid system as it is used for both objects based database and relational database. It helps to sort and locate files faster. It helps to filter and retrieve the files which share the same characteristics. Yes Oracle is an ORDBMS. Oracle is a vast subject with huge Oracle interview questions which requires constant learning.

    66. What is oracle index?

    Oracle index is created with one or more column of the table which is used to have access to the row of the data base. They are used to speed up the queries which access to the small portion of the data base. Oracle allows much number of indexes in the tables to access the different types of queries. For understanding the database management system learn about Pl SQL Interview questions.

    67. Explain the term grid and cluster in Oracle?

    A grid is the collection of server or collection of the cluster where the server is running the application server software. The grid infrastructure manages the demand and the supply. The grid consists of the application and the respective linked database server to manage the workload manageable. A Cluster is a single server used to create a grid. A Cluster is a place where the database or application resides and it is responsible for the high performance of the database system. The Cluster provides an alternative server in case of server failure. So many layers or components are used to make the application or the database available. Before attending the interview learn the SQL Server Interview questions to sharpen your knowledge.

    68. Explain how view option is useful in Oracle?

    The View is very helpful in seeing the data without storing it in the object, View help to apply the control by hiding some of the columns, it helps to join two or more tables as per the requirement, and ensure the security by controlling the access to tables. A view is a logical table and it does not have data. Start your learning with the Basic SQL Interview questions then learns the advanced level questions for better understanding of the subject.

    69. Explain the different types of queries?

    Normal queries, subqueries, co-related queries, nested queries, and compound queries are some of the types of queries. A query is used to execute the operation to one or more tables or views. A Query is a top-level select statement and subquery is nested query written for another SQL statement. The correlated query is the query that is followed by an outer query. When performing the compound query the control is obtained by using the parentheses matching record to be performed. SQL Interview Questions help you to understand the query language required for the database management.

    70. Differentiate clustered index and a non-clustered index?

    An index contains the key required for the table or view. The keys are stored in a server to find the row or row associated with the key values. Cluster Index uses the key values to store the data. A non-cluster index stores the data in a logical order which does not match the order of the physically stored data. Learning Oracle DBA interview questions for freshers and experienced enhances the confidence level to attend the interview with confidence.

    71. What is tablespace?

    A tablespace is a storage location where it specifies the database storage location logically. It is related to the logical database or database schema. Each object in the database may have different schema. A tablespace is used to group the different schema. Data is stored logically in tablespaces and physically in the data files. Oracle Interview questions with answers provide you the simple and detailed answers with relevant examples. Learning Oracle interview questions with answers would facilitate the knowledge required for the interview.

    72. What is material view? What is the difference between material view and standard view?

    A material view is a database object which shows the results of a query. It can be a data or a subset of the row, column or summary, etc. The standard view uses a query to list the data, whereas material views are used to view the result of a query. The material view is used to reduce the network loads, reduce the computing problems, create an environment for deployment, and group the data.

    73. What is a user-Exit in Oracle?

    A user exit is a C subroutine called from Oracle database to do a special process. You can use SQL statements and PL or SQL blocks in your user exit. A user exit is harder to write than SQL or PL/SQL and used for triggers.

    74. Define Roles in Oracle?

    A role is used to grant privileges to the users. A role is used to save time and effort to the database system. Roles are helpful to give the right to access another user’s object to execute a particular type of SQL statement.

    75. Describe Oracle instances?

    Database instance allocates memory area called as system global area {SGA} and it is used for the background process. It is used for maintaining the internal data which are subject to a processor thread for operations. Store the data blocks from the disk, buffer the redo data, and store the plans to be executed. The database instance can be configured by a single instance configuration and Oracle real application clusters configuration etc.

    76. What are synonyms in Oracle?

    A synonym is an alternative name for the table, view, sequence or schema and it is used to provide access to the users. The users don’t know that which schema owns the object but get the access to the database. It is easy to create a private synonym by using the create option in Oracle. There are two types of synonyms they are a public and private synonym. There is an order in using the synonyms like first access to the local objects, in case if there is no local object, then the private object is used, if there is no object or private synonym then public synonym is used.

    77. Define oracle pre compilers in Oracle database management system?

    An Oracle Precompiler is a tool used to combine the SQL statements and high-level language. Pre-compiler is used for the customized applications and it is compatible with ANSI SQL. It will run in Oracle database or ANSI SQL database management system. It is used for the static and dynamic linking with the Oracle libraries. If it is static linking then the libraries are linked to single executable program and for dynamic linking the code is partly stored in the executable program and partly stored in the libraries which are linked during the runtime. Join Oracle Training in Chennai at FITA Academy and learn more about its data management system under working professionals.

    78. What are the different types of SQL statements? Explain the types of SQL statements?

    SQL statements are divided into five types. They are:

    Data Retrieval: used for SELECT statement

    Data Retrieval {DML}: used for INSERT, UPDATE, DELETE, AND MERGE.

    Data Definition language {DDL}: This statement is used to CREATE, ALTER, DROP, RENAME, TRUNCATE.

    Transaction control statements: This statement is used for COMMIT, ROLLBACK, and SAVEPOINT

    Data control Language {DCL}: This language is used for GRANT, REVOKE.

    Example for the SELECT Statement is as follows. The Select statement appears like below.

    SELECT select_list

    FROM source

    WHERE condition{S}

    GROUP BY expression

    HAVING condition

    ORDER BY expression

    The first line tells about the SQL processor, the second line shows the database table used by the statement, the third line tells about the specific conditions. The last three lines tell about specific conditions. As per the business trend in 2018 PL SQL has a market share of about 2.2 percent. So, learning Pl SQL interview questions is a good opportunity to acquire the dream job of Oracle Pl SQL developer.

    79. What is an Alias in SQL statement and what is the difference between column, table and the alias?

    Column or table is used to get the desired result when writing the statement. Alias is the alternative name for the column or table. Alias is represented with quotation marks {“”} if you want to make it as case sensitive. When writing the code for the SELECT clause “AS” is assumed as the keyword before the alias name. SELECT is the condition, the alias is the place of the data, and “AS” is the key word used. Thus, when writing the statement alias makes the statement easier.

    80. What is a Literal? How literal is expressed in the statement?

    A Literal is a string which represents a character, a number, or a date in the SELECT list. A Literal is not a column name or column alias. The symbol for date and character literals is (”), the number literals is not represented through symbol. Learning the Basic SQL Interview Question is very helpful to understand the basic knowledge about the Oracle Database Management System.

    81. What is SQL and ISQL *plus? What is the difference between SQL and SQL *plus?

    SQL is the language used in relational database management system. SQL is also used for stream processing in the relational data stream management system. Before attending the interview learn SQL Interview Questions which boost up your confidence level. iSQL*plus is used to perform the task on the internet. It is the browser-based implementation to perform the task with the basic command line interface. The difference between the SQL and ISQL*plus are SQL is a language whereas iSQL*plus is an environment. Justification is of two types left justification and right justification whereas ISQL*Plus provides the default justification. It is not represented with short forms whereas iSQL*plus is represented with short forms. If the command is longer than one line then no need to write continuation character in SQL whereas (-) is the continuation character in the command if the command exceeds more than one line. Formatting has been done using the functions in SQL whereas commands are used to format data in iSQL*plus.

    82. What is the order of precedence used in executing SQL statements?

    The arithmetic operators (*,/,+,-), concatenation operators (||), comparison conditions, Is[NOT] NULL, LIKE, [NOT] IN, [NOT] BETWEEN, NOT Logical condition, and OR logical condition is the order used in the SQL statements. This order is called as the order of precedence in the SQL statements. Advanced SQL Interview Questions provides in-depth knowledge about the Oracle Database Management system.

    83. Describe the different type of SQL function?

    SQL functions are of two types. They are single-Row functions and multiple-Row functions. Character, number, date, conversion and general are the different types of single row functions. AVG, COUNT, MAX, MIN, SUM, STDDEV, and VARIANCE are some of the functions which are called as multiple row functions.

    84. Explain the function in detail in the Oracle database management system?

    There are three types of functions in the relational database management system. They are character functions, number functions, and date functions. Character functions accept the character and return both the character and the number. There are two types of functions under this head. They are case-manipulation functions which include the LOWER, UPPER and the INITCAP and character-manipulation functions which include the CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, and REPLACE. The number functions accept numeric input and return the numeric values. The numeric functions are ROUND, TRUNC, and MOD. Date functions are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC.

    85. Explain how the conversion functions work?

    Conversion functions convert the class functions from one type to another type. This function converts the value of the data. There are two types of conversion data types. They are implicit data type conversion and explicit data type conversion. Implicit conversion is of three types and they are VARCHAR2 or CHAR TO NUMBER DATE, NUMBER TO VARCHAR2 AND DATE TO VARCHAR2. The three types of explicit data conversion are TO_NUMBER, TO_CHAR, AND TO_DATE. The format for the TO_NUMBER (char[,’format_model]). fx modifier explains the exact match and the model of TO_NUMBET function. TO_CHAR is used to convert number or date data type to character format. TO_DATE function is used to character string into date format in the Oracle database management system.

    86. What are the different types of the general function used in the SQL? Describe each function in detail?

    NVL, NVL2, NULLIF, COALESCE, and Conditional expressions are the five types of general function of the SQL. NVL converts the value of the data. For example, if a null value is in the place of exp1 then NVL function return value of exp2. In NVL2 exp1 is not null then it returns to exp2, if exp1 is null then nvL2 returns to exp3, NVL2 consists of exp1, exp2, and exp3. NULLIF is about the comparison. It compares two expressions and returns null if they are equal or return to the first expression if they are not equal. COASLESCE is the function which takes the multiple alternative values. It returns the first non-null expression in the expression list and the three COALESCE are exp1, exp2 and expn. IF-THEN-ELSE is used in the SQL statement in conditional expressions.

    87. Explain COUNT(*), COUNT (expression), COUNT (distinct expression)? And how it is written in the SQL statement? Explain the difference between these SQL functions?

    COUNT(*) is an expression which uses the information from any particular column. It returns only the rows in a specified table and counts each row separately. The number of rows includes the rows in the table, the duplicate rows and the rows with null values. Count (expression) is the function which counts the non-null values. This expression is used to sort the records in the result set at the end of the SQL statement. This expression is not encapsulated with the COUNT function and included at the end of the SQL statement as GROUP BY clause. The COUNT DISTINCT function returns the unique values and non-null values and ignores the NULL values. See the example below:

    SELECT expression1, expression2, … expression_n,

    COUNT(aggregate_expression)

    FROM tables

    [WHERE conditions]

    GROUP BY expression1, expression2, … expression_n;

    Parameters:

    expression1, expression2, … expression_n:

    The expressions are included in the GROUP BY clause at the end of the SQL statement and these expressions are not encapsulated.

    Aggregate_expression:

    The null values of the column or expression will be counted.

    Tables:

    Table is listed in the FROM clause and it can be a table which you want to retrieve the data.

    WHERE conditions:

    The WHERE condition is used only if it needed. If the condition met the records then it is used. Before attending the interview learn Basic Oracle Interview Questions for freshers to handle the interview with confidence.

    88. What is an operator? Explain the difference between ‘ANY’ and ‘ALL’ operators in the SQL statement?

    An operator is used to specify conditions and act as a link for multiple conditions in an SQL statement. Any and all are the logical operators in the SQL statement. ANY operator is used to compare the value in the list or sub query. ‘ALL’ operator is used to compare the value in the list or sub query. ANY and ALL are multiple row operators. See the below example for the ‘ANY’ and ‘ALL’ usage;

    >ANY means more than the minimum.

    < ANY means less than the maximum

    = ANY is equivalent to IN operator.

    > ALL means more than the maximum

    < ALL means less than the minimum

    <> ALL is equivalent to NOT IN condition.

    89. Differentiate “VERIFY” and “FEEDBACK” command in the SQL statement?

    VERIFY is the command used to verify the old and new values in the SQL statement. It is defined as SET VERIFY ON/OFF in the SQL statement. Feedback command is the command which shows the records returned by a query.

    90. How Is Double Ampersand (&&) used in SQL queries? Give example?

    If you want to reuse the variable then “&&” is used where no need to prompt the user each time. The two examples are:

    StandardCost < 300 && ListPrice > 500 .

    StandardCost < @SPrice && ListPrice > @LPrice

    The variables are standard cost, list price, and selling price.

    91. What is the difference between Oracle and SQL?

    Oracle and SQL both use structured query language. The SQL uses transact SQL whereas Oracle uses PL/SQL which is known as procedural language. The T-SQL is developed by Sybase and used by Microsoft. There are so many online materials available to help the students for the PL SQL Interview Questions. Learning is required when attending the interview and even after joining the job.

    92. What are the different types of joins in Oracle 8i? Explain all the joins in detail?

    There are five types of joins in Oracle 8i and prior. The joins are Equijoin, outer join, self-join, non-equi join and cartesian product.

    When a join condition is omitted then the result is called as cartesian product. The cartesian product or cross join produces two types of a result set. Cross join multiply the number of rows in the first and second table if there is no WHERE clause. The cross join works like an INNER JOIN if there is WHERE clause.

    Equijoin:

    Equi is also called an inner join or simple joins. This join contains the equality operator or comparison operator. This join returns the rows that have equivalent values in the specified columns. An inner join uses two or more tables and then return to the rows.

    Non-Equi joins:

    A non-equijoin is a condition which consists of join other than an equality operator. This join matches the column and tables with an inequality. It uses comparison operator instead of equal sign.

    Outer join:

    The outer join is used to retrieve data from multiple tables. This join is represented with plus sign{+}. There are three types of outer join left outer join, right outer join and full outer join.

    Self-join:

    This join is used to join the same table to it. Learn the Basic SQL Interview Questions for freshers experienced and keep yourself updated regarding the advance level questions in the Oracle to handle the difficult task on job.

    93. What are the joins used in Oracle 9i?

    The joins used in the Oracle are cross join, natural joins, join with the USING clause, and joins with the ON clause. Cross join produces the product from two tables. Natural joins are used to join two tables with matching data. It is similar to equijoin. Join with the using clause is used where the column name is the same but the data type is not matching. The Natural join clause and the USING clause are used internally. The natural join clause can be modified by using the USING clause to join the matching data. Joins with the ON clause are used to make the code easy and it is also used for the column with different names.

    94. Explain what is a transaction? What are the common errors that can occur when executing any transaction?

    The transaction is the logical unit which consists of a collection of a data manipulation language. The common error happens during the transaction are a mismatch of data type, size of column mismatch, server down  problem, the session is over when it comes to the concern page, and locking problem etc.

    95. What are implicit locking and explicit locking in the database?

    Implicit locks are placed by the developer and the application issue locks which are referred to as implicit locks. If the operation is read type then it applies the read lock and if the operation is written type then it applies the write lock. Some applications need access in advance. Explicit lock freezes the object and uses the object. So, it is unexpected lock from the database to provide access and the security.

    96. What is the naming rule when creating a table in the Oracle database management system?

    The naming rules are the table must begin with a letter, the character length is 1 to 30, the name can contain only alphabets, numbers from 0 to 9, _, $, #, the name should not be the duplicate name {for example the same user should not own any other object with the same name}, the table name cannot be reserved word from the Oracle server.

    97. Explain about the ‘Default option’ in a table?

    If default option is used then the column can be given default value and null value cannot be entered. The column value and the row value comes by default and the row value cannot be inserted for this particular column as the default option is used. The default value is the expression, SQL function like the date in the system and the user of the system. The value of the column cannot be the name to the pseudo column. Pseudo column of Oracle is NEXTVAL or CURRVAL. Plan your interview with reading Oracle Interview Questions.

    98. Differentiate USER TABLES and DATA DICTIONARY?

    User table consists of user information and data dictionary consist of database information. User information is stored in various systems whereas the database information is stored in the Oracle server. The data dictionary is a read-only set of tables and owned by the sys user. SQL Interview Questions and PL/SQL Interview questions are the basics for working in the Oracle database Management system.

    99. Describe the data type in Oracle?

    There are 15 types of data types according to the Oracle database system. They are CHARACTER or CHAR[length}, VARCHAR[Length}, BOOLEAN, SMALLINT, INTEGER or INT, DECIMAL{P{S} OR DEC [(P)S], NUMERI[(P[(S)], REAL, FLOAT(P), DOUBLE PRECISION, DATE, TIME, TIMESTAMP, CLOB[(length)], and BLOB [(length)]. CHARACTER data type is string type data and it is declared before itself. The minimum size of this type is 1 and the maximum size is called as CLOB. There is CHARACTER size which is larger than the table and sometimes the size of the CHARACTER is not mentioned in the database. If the size is not mentioned then it is assumed as 1.

     The minimum size of the VARCHAR is 1 and the maximum size is up to the size of the table. If the declared value and the real value differs then it results in truncation which is raised an error in the Oracle database. The BOOLEAN data type is true or false. SMALLINT shows the numeric values in between 2^-15 and 2^15-1 and values more than this range is stored in INTEGER data type. INTEGER is numeric value with 2^-31 and 2^31-1. In the DECIMAL data type the precision and the scale is declared by the user. The precision is the number of digit of the number and the scale is the number of digits to the right of the decimal point. The three ways of the declaration are DECIMAL, DECIMAL (P) and DECIMAL(P,S). NUMERIC is also the same as like the DECIMAL in the point base. The REAL data type is numeric value with a precision of 64. FLOAT data type is defined with a maximum of 64. If no value is declared then it is assumed as 64. The FLOAT data type default range is 126 binary or 38 decimal.

     The double precision range is 126 binary. The DATE data type is in the format of year-month and day. The values for the month is 1 to 12, the value for the day ranges from 1 to 31 and the value of the year starts from 0 to 9999. The date is written within the single quotes like DATE ‘2018-04-16. The format for the time is HH:MM:SS. This is also written in single quotes and optional fraction value can be used to explain the nanoseconds. The minute value range is 00 and 59, the second’s value range is 00 and 61.999999, and the hour values range is 0 and 23. TIMESTAMP is the data type with values of date and time. Date and time are mentioned with space for clear understanding. CLOB data type is the data with longer character than CHARACTER OR VARCHAR. BLOB shows the binary values. BLOB data type is 2 gigabytes in length.

    100. Is it possible to modify a table in a column? Explain how?

    The different types of modifications which are possible in the oracle are the numeric column change with increase in the width or precision, the numeric or character column change with increase in the width, the column or table with null values or no rows can be decreased, data type change is possible if the column value is null, in case of conversion for the CHAR column or VARCHAR2 data type the column should contain null values. Thus the column, table and data type can be modified only with certain conditions in the Oracle database. PL/SQL Interview Questions deal with the practical scenarios and it is difficult to clear the interview without preparation.

    101. What is long data set? Is it possible to use the long dataset in the oracle?

    One long column can be used for one table and long column is copied during the usage of subquery for the table. The long column is not included in the group or order by clause in the Oracle database system. The column is defined with a ratio to the length as 12 whereas long column is defined with dimension more than 12.

    Oracle database executive should know about the database design, database implementation, backup, and recovery. In all the phases of the project development communication skills and the technical skills are given paramount importance.

    The above-provided oracle interview questions are very helpful for the interview preparation. Most of the candidates focus on the latest questions for the interview preparation. We would like to help the students to win over the interview and excel in their professional life.