How can we create privileges in Oracle?<\/strong><\/li>\r\n<\/ol>\r\nA 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.<\/p>\r\n
[sql]GRANT user1 TO user2 WITH MANAGER OPTION;[\/sql]<\/p>\r\n
These\u00a0Oracle Interview Questions<\/strong>\u00a0help you to brush up your knowledge before appearing for an interview.<\/p>\r\n\r\n\r\n \t- Define VArray?<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- How can we obtain field details of a table?<\/strong><\/li>\r\n<\/ol>\r\n
Describe <Table_Name> has been used to obtain the specific table field details.<\/p>\r\n\r\n
\r\n \t- Explain the difference between alias and rename?<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n
These\u00a0Oracle Interview Questions with Answers<\/strong>\u00a0gives a clear idea about what an interviewer may ask during the time of interview.<\/p>\r\n\r\n\r\n \t- What is referred as a View?<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- Define cursor variable<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- What is called as the SET operators?<\/strong><\/li>\r\n<\/ol>\r\n
SET operators have been used with two or more queries. Those operators are Minus, Union All, Intersect, Union.<\/p>\r\n\r\n
\r\n \t- What is known as the cursor attributes?<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- What is the procedure to delete the duplicate rows in a table?<\/strong><\/li>\r\n<\/ol>\r\n
The duplicate rows in a table can be deleted with the use of ROWID.<\/p>\r\n
Prepare these\u00a0Oracle Interview Questions<\/strong>\u00a0well and get placed in a high paid job.<\/p>\r\n\r\n\r\n \t- Explain about the attributes of the cursor<\/strong><\/li>\r\n<\/ol>\r\n
There are three types of Attributes of Cursor. They are<\/p>\r\n
%Found<\/p>\r\n
Returns NULL if the cursor is open and the fetch has not been implemented<\/p>\r\n
Returns TRUE if the cursor fetch has been successfully executed<\/p>\r\n
Returns False if no rows have been returned.<\/p>\r\n
%NOT FOUND<\/p>\r\n
Returns NULL if the cursor is open and fetch is not executed<\/p>\r\n
Returns False if the fetch is implemented<\/p>\r\n
Returns True if no row was returned<\/p>\r\n
%ISOPEN<\/p>\r\n
Returns true if a cursor is open<\/p>\r\n
Returns false if a cursor is closed<\/p>\r\n
%ROWCOUNT<\/p>\r\n
Returns the no of rows fetched. It must be iterated through the entire cursor to provide the exact real count<\/p>\r\n\r\n
\r\n \t- Is it possible to store the pictures in the database and if so, How it is to be done?<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- Define integrity constraint?<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- Define ALERT<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- Explain hash cluster<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- Describe the various constraints that are used in Oracle<\/strong><\/li>\r\n<\/ol>\r\n
The constraints used in the Oracle are listed below<\/p>\r\n
NULL: It specifies a particular column which can have NULL values<\/p>\r\n
NOT NULL: It indicates that the particular column cant have NULL values<\/p>\r\n
CHECK: Evaluate the values that in the provided column in order to reach the specific criteria.<\/p>\r\n
DEFAULT: It indicates the value that has been assigned to the default value.<\/p>\r\n\r\n
\r\n \t- Describe the difference between INSTR and SUBSTR<\/strong><\/li>\r\n<\/ol>\r\n
INSTR gives character position in which the pattern has been found in a string and SUBSTR returns the particular portion of a string.<\/p>\r\n
INSTR returns numeric whereas SUBSTR returns a string.<\/p>\r\n\r\n
\r\n \t- What is referred as the parameter mode which can be passed to a procedure?<\/strong><\/li>\r\n<\/ol>\r\n
IN, OUT, and INOUT are the parameter modes which can be passed to a procedure.<\/p>\r\n\r\n
\r\n \t- Explain the various Oracle Database objects?<\/strong><\/li>\r\n<\/ol>\r\n
There are various data objects in Oracle \u2013<\/p>\r\n
Tables \u2013 it is set of elements organized in horizontal and vertical<\/p>\r\n
Views \u2013 it is the virtual table derived from one or more tables<\/p>\r\n
Indexes \u2013 it is the performance tuning method for generating the records<\/p>\r\n
Synonyms \u2013 Alias name for the tables<\/p>\r\n
Sequences \u2013 Multiple users process unique numbers<\/p>\r\n
Tablespaces \u2013 Logical storage unit in Oracle<\/p>\r\n\r\n
\r\n \t- Describe the differences between List item and LOV<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n
Learn these\u00a0Oracle Interview Questions with Answers<\/strong>\u00a0to get placed in a well-reputed company.<\/p>\r\n\r\n\r\n \t- What is known as Grants and Privileges?<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n
It is one of the commonly asked\u00a0Oracle Interview Questions<\/strong> For Freshers<\/strong> at the time of interview.<\/p>\r\n\r\n\r\n \t- Define the difference between $ORACLE_BASE and $ORACLE_HOME<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- What is known as the fastest query method to obtain data from the tablet?<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n\r\n
\r\n \t- What is referred as the maximum number of triggers which can be applied to a single table?<\/strong><\/li>\r\n<\/ol>\r\n
12 is considered to be the maximum no of triggers which can be applied to a single table.<\/p>\r\n
These\u00a0Oracle Interview Questions with Answers<\/strong>\u00a0help you enhance your career.<\/p>\r\n\r\n\r\n \t- In what way we can display row numbers with records?<\/strong><\/li>\r\n<\/ol>\r\n
For displaying row numbers with the record numbers:<\/p>\r\n
1 Select rownum, <fieldnames> from table;<\/p>\r\n
This query displays field name and row numbers from the table.<\/p>\r\n\r\n
\r\n \t- How the last record added to a table can be viewed by us?<\/strong><\/li>\r\n<\/ol>\r\n
Last record may be added to a table and this can be done by:<\/p>\r\n
1 Select * from (select * from employees order by rownumdesc) where rownum<2;<\/p>\r\n\r\n
\r\n \t- Define the difference between Cross Join and Cartesian Join<\/strong><\/li>\r\n<\/ol>\r\n
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 \u2013 Rows from the first table has been multiplied by another table which is known as a Cartesian product.<\/p>\r\n
Cross join without where the clause provides the Cartesian product.<\/p>\r\n\r\n
\r\n \t- What is known as the data type of DUAL table?<\/strong><\/li>\r\n<\/ol>\r\n
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 \u2018X\u2019.<\/p>\r\n\r\n
\r\n \t- In what way we can display employee records who obtain more salary than the average one in the department?<\/strong><\/li>\r\n<\/ol>\r\n
This can be done by the following query:<\/p>\r\n
1 Select * from employee where salary>(select avg(salary)) from dept, employee where<\/p>\r\n\r\n
\r\n \t- What is known as PL SQL?<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n
Prepare these\u00a0SQL Server Interview Questions<\/strong>\u00a0well before appearing for the interview.<\/p>\r\n\r\n\r\n \t- Describe the difference between TYPE RECORD and % ROWTYPE<\/strong><\/li>\r\n<\/ol>\r\n
The TYPE RECORD has been used when a query gives back the column of various tables or views.<\/p>\r\n
For e.g., TYPE r_emp is RECORD (snosmp.smpno%type,snamesmpsname %type)<\/p>\r\n
e_recsmp %ROWTYPE<\/p>\r\n
Cursor c1 is select smpno, dept from smp;<\/p>\r\n
e_rec c1 %ROWTYPE<\/p>\r\n\r\n
\r\n \t- Describe the uses of the cursor<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n
It is one of the important\u00a0PL SQL Interview Questions<\/strong>.<\/p>\r\n\r\n\r\n \t- Display cursor code for loops<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n
For e.g., FOR smp_rec IN C1 LOOP<\/p>\r\n
Totalsal=totalsal+smp_recsal;<\/p>\r\n
ENDLOOP;<\/p>\r\n
These\u00a0Oracle SQL Interview Questions<\/strong>\u00a0help you to get through the interview easily.<\/p>\r\n\r\n\r\n \t- What are the uses of a database trigger?<\/strong><\/li>\r\n<\/ol>\r\n
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<\/p>\r\n\r\n
\r\n \t- Log events transparently<\/li>\r\n \t
- Maintaining replica tables<\/li>\r\n \t
- Enforce complex business rules<\/li>\r\n \t
- Executing complex security authorizations<\/li>\r\n \t
- Deriving column values<\/li>\r\n \t
- Auditing data modifications<\/li>\r\n \t\r\n<\/ol>\r\n
\r\n- Describe the two various kinds of exceptions<\/strong><\/li>\r\n
Error handling section of PL\/SQL block is known as Exception. They contain two types such as predefined and user-defined.<\/p>\r\n
It is one of the important\u00a0Oracle PL SQL Interview Questions<\/strong>\u00a0which has been asked during the interview.<\/p>\r\n\r\n\r\n \t- Describe Raise_application_error.<\/strong><\/li>\r\n<\/ol>\r\n
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.<\/p>\r\n
It is considered to be the commonly asked\u00a0Basic SQL Interview Questions<\/strong>. So prepare well to crack the interview in a successful way.<\/p>\r\n\r\n\r\n \t- Display how procedures and functions are known in a PL SQL block.<\/strong><\/li>\r\n<\/ol>\r\n
Function is known as the part of an expression.<\/p>\r\n
total:=calculate_sal(\u2018b644\u2019)<\/p>\r\n
Procedure is known as a statement in PL\/SQL.<\/p>\r\n
Calculate_bonus(\u2018b644\u2019);<\/p>\r\nClick Here to know about DevOps Interview Questions For Freshers<\/a><\/li>\r\n \t- \r\n
\r\n \t- Describe two virtual tables that are available during database trigger execution.<\/strong><\/li>\r\n<\/ol>\r\n
Table columns are known as THEN.column_name and NOW.column_name.<\/p>\r\n
For INSERT related triggers, NOW.column_name values are available only.<\/p>\r\n
For DELETE related triggers, THEN.column_name values are available only.<\/p>\r\n
For UPDATE related triggers, both Table columns are available.<\/p>\r\n\r\n
\r\n \t- Describe the rules to be applied to NULLs during comparisons.<\/strong><\/li>\r\n<\/ol>\r\n
Prepare these\u00a0Advanced SQL Interview Questions<\/strong>\u00a0and brush up your knowledge to get placed in a high paid job.<\/p>\r\n\r\n\r\n \t- NULL is never TRUE or FALSE<\/li>\r\n \t
- NULL cannot be unequal or equal to other values<\/li>\r\n \t
- If a value present in an expression is said to be NULL, then the expression itself estimates to NULL except for the concatenation operator (||)<\/li>\r\n \t\r\n<\/ol>\r\n
\r\n- Explain the process of PL SQL compiled<\/strong><\/li>\r\n
The compilation process such as syntax check, bind and p-code generation processes.<\/p>\r\n
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.<\/p>\r\n\r\n
\r\n \t- Explain the difference between Syntax and runtime errors.<\/strong><\/li>\r\n<\/ol>\r\n
A syntax error can easily be detected by a PL\/SQL compiler. For e.g., incorrect spelling.<\/p>\r\n
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.<\/p>\r\n\r\n
\r\n \t- Describe Commit, Rollback, and Savepoint.<\/strong><\/li>\r\n<\/ol>\r\n
COMMIT statement<\/p>\r\n\r\n
\r\n \t- Other users may see the data changes made by the transaction<\/li>\r\n \t
- The locks accomplished by the transaction have been released<\/li>\r\n \t
- The work which was done by the transaction becomes permanent<\/li>\r\n<\/ol>\r\n
ROLLBACK statement: it gets issued while the transaction ends. Hence the following is true.<\/p>\r\n\r\n
\r\n \t- The work done in a transaction has been undone as if it was never issued.<\/li>\r\n \t
- All locks accomplished by a transaction have been released.<\/li>\r\n<\/ol>\r\n
It undoes all the work which was done by the user in a transaction. Using SAVEPOINT, only part of the transaction may be undone.<\/p>\r\n\r\n
\r\n \t- What is ORDBMS? Can we say oracle as ORDBMS?<\/strong><\/li>\r\n<\/ol>\r\n
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\u00a0Oracle interview questions\u00a0<\/strong>which requires constant learning.<\/p>\r\n\r\n\r\n \t