Table of content:
- What is PL/SQL?
- What is difference between Delete and Truncate?
- What are the Various Types of SQL Statements?
- What is basic structure of PL/SQL?
- In PL/SQL, describe the day-to-day activities?
- What are the differences between PL/SQL and SQL?
- What is trigger and what is the purpose of triggers in database? Give the syntax of trigger?
- What is view?
- Differentiate between Simple View and Complex View.
- What are SQL functions? Describe in brief different types of SQL functions?
- Difference between view and materialized view?
- What is SQL Loader?
- What is Exception Handling?
- What is “PRAGMA_exception_init”?
- Specify a Few Predetermined Exceptions
- What is “RAISE_application_error”?
- What is cursor? What are the types of cursor?
- What are the Different Parameter Types in PL/SQL?
- Explain cursor attributes?
- What is REF Cursor?
- What are Constraints? How many types of constraints are there?
- What is %TYPE?
- What is the difference between Procedure and Function?
- What Data Types are Present in PL/SQL?
- What is Dual Table?
- Explain the benefits of PL/SQL packages?
- What do you know about the commands commit, rollback and savepoint?
- What is the %ROWTYPE?
- What Are the Benefits of Stored Procedures?
- What are the different types of SQL General Functions?
- Explain what PL/SQL package consists of?
- What is a Sub Query? Describe its types?
- What is the best way to save or place your message in a table?
- What does SYS.ALL DEPENDENCIES stand for?
- How is a DECLARE statement used?
- What is the difference between Sqlcode and Sqlerrm, and why are they significant for Pl/SQL Developers?
- What is the difference between a stored procedure and a stored function in PL/SQL?
- In PL/SQL, how do you see User-defined Functions and Procedures?
- What is the best way to make a function?
- What is a mutating trigger?
- In SQL, what is locking? What are the different types of it?
- Explain the uses of Merge with Syntax in PL-SQL.
- In PL/SQL, what is the difference between the ROLLBACK and ROLLBACK TO statements?
- What are all of the Oracle joins used for?
- What are the components of an Oracle database’s logical database structure?
- ON DELETE CASCADE vs. ON DELETE SET NULL: What’s the difference?
- In a table, what is the DEFAULT option?
- In Oracle, describe the different types of temporal data.
- What exactly is a Discard File?
- What are database triggers used for?
- Difference between triggers and constraints?
SQL knowledge is required since SQL expertise is in great demand and highly appreciated in the industry. Oracle is a well-known safe database that is commonly employed by international corporations. This article will explore some of the most common questions asked by large company recruiters during job interviews. These questions will assist you in acing major interview processes and will put your skills and knowledge to the ultimate test.
50+ Most Asked UNIX Interview Questions And Answers
Q1. What is PL/SQL?
PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools. All the statements of a block are passed to oracle engine all at once which increases processing speed and decreases the traffic. The data definition commands, such as CREATE, are not supported by PL/SQL.
Q2. What is difference between Delete and Truncate?
The difference between DELETE and TRUNCATE is as follows:
Difference Between Primary Key And Unique Key Explained!
Q3. What are the Various Types of SQL Statements?
The five types of SQL statements are as follows:
- DDL: Data definition language (DDL) , which deals with database schemes and descriptions of how the data should reside in the database. The five basic commands are — CREATE, ALTER DROP, TRUNCATE, RENAME.
- DML: Data manipulation language (DML) which deals with data manipulation and includes most common SQL statements such as INSERT, UPDATE, DELETE, SELECT, etc and it is used to store, modify, retrieve, delete and update data.
- DCL: GRANT and REVOKE are the commands in the data control language (DCL) that can be used to grant rights and permissions. It is mostly concerned with rights, permission and other controls of the database system.
- TCL: Transaction control language (TCL) commands that deals with a transaction within a database. Some of the TCL commands are COMMIT, ROLLBACK, and SAVEPOINT.
- DQL: Data query language (DQL) is used to retrieve data from the database. It just has one command, which is SELECT.
Q4. What is basic structure of PL/SQL?
The basic structure of PL/SQL is a block structure. In PL/SQL, anonymous or nested blocks can be used.
1
2
3
[DECLARE]
— declaration of statements (optional)
What Is The Difference Between GUI And CUI?
Q5. In PL/SQL, describe the day-to-day activities?
- Make database objects like as tables, synonyms, and sequences.
- Create business rules, procedures, and functions, among other things.
- Impose business rules, create constraints, triggers, etc.
- Make data manipulation cursors.
Q6. What are the differences between PL/SQL and SQL?
Understanding Data Mining Architecture In Detail
Q7. What is trigger and what is the purpose of triggers in database? Give the syntax of trigger?
A trigger is a pl/sql block structure which is automatically fired when an event occurs in the database. The event can be DDL, DML or system events. A trigger code is automatically fired when an associated event happens in the database. There are five types of trigger.
Purpose of triggers:
- Auditing
- Enforcing complex referential integrity
- Logging
- Enforcing security for transactions
- Data replicating to multiple tables
- Prevent invalid data or transactions
- DDL triggers for auditing and logging of DDL statements
- System triggers mainly for auditing and logging
Syntax
1
2
3
create trigger [trigger-name]
[before | after]
Q8. What is view?
It is a logical table based on other base table or views. Views will not hold data of its own, instead it retrieves the data from base table/view whenever the view is queried for. Views are used to present the same data in multiple formats. View contains no data itself.
50+ Most Asked UNIX Interview Questions And Answers
Q9. Differentiate between Simple View and Complex View.
Q10. What are SQL functions? Describe in brief different types of SQL functions?
SQL Functions are a powerful component of the language. Although SQL functions accept arguments, they always return a value. SQL functions are divided into two categories.
1. Single-Row Functions: These functions operate on a single row and return a single result. Types of Single-row functions:
- Character
- Number
- Date
- Conversion
- General
2. Multiple-Row Functions: These functions act on groups of rows to produce a single result for each group. Types of Multiple-row functions:
- AVG
- COUNT
- MAX
- MIN
- SUM
- STDDEV
- VARIANCE
Difference Between Primary Key And Unique Key Explained!
Q11. Difference between view and materialized view?
Q12. What is SQL Loader?
SQL loader is a utility provided by oracle to load data from external files into database.
Control File: This file defines the structure of flat file. Also, this file contains the information about target to load the data.
SQL Loader reads the information from control file and loads the information into target table. While loading the data into target table, SQL Loader generates below mentioned files.
- Log file (contains information about process log)
- Bad file (contains information about failed data)
- Discard file (contains information about rejected data)
What Is The Difference Between GUI And CUI?
Q13. What is Exception Handling?
Exception handling is a built-in technique for dealing with runtime issues. In PL/SQL, you can change it. The exception block in PL/SQL raises the exception, assisting the programmer in locating and resolving the problem. The program’s error handling code is included when an error occurs. Exception handling is a mechanism that are implemented to deal with runtime error.
There are two different types of exceptions defined in PL/SQL:
- Predefined Exception: Common errors with specified names are called predefined exceptions.
- User-defined Exceptions: These exceptions do not cause a runtime issue, but they do break business rules.
Q14. What is “PRAGMA_exception_init”?
Pragma is a keyword directive that tells the compiler how to continue. This two arguments are passed to the pragma EXCEPTION INIT function.
- exception name
- error number
In your program’s DECLARE BLOCK, you can define the pragma EXCEPTION INIT.
PRAGMA EXCEPTION_INIT(exception name, -error number);
Understanding Data Mining Architecture In Detail
Q15. Specify a Few Predetermined Exceptions
Some examples of predefined exceptions are as follows:
- No DATA FOUND: A SELECT statement with a single row that produces no results.
- TOO MANY ROWS: A SELECT statement with a single row that returns a large number of rows.
- ZERO DIVIDE: A zero division attempt
- INVALID CURSOR: A cursor operation is performed incorrectly.
Q16. What is “RAISE_application_error”?
The procedure RAISE_APPLICATION_ERROR lets us issue user-defined error messages from stored subprograms.
To call RAISE_APPLICATION_ERROR, use the syntax
Raise_application_error(error number, message[, {TRUE | FALSE} ] );
Where error number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the optional third parameter is FALSE, the error replaces all previous errors.
50+ Most Asked UNIX Interview Questions And Answers
Q17. What is cursor? What are the types of cursor?
A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT query or DML statement. Cursors are divided into two categories:
1. Implicit Cursor: When PL/SQL runs a SQL query, it creates a cursor without having to specify one; these cursors are known as implicit cursors.
PL/SQL employs implicit cursors for the following statements:
INSERT UPDATE DELETE SELECT
2. Explicit Cursor: For searches that return more than one row, a programmer specifies and names an explicit cursor. A SELECT statement that is declared explicitly in the current block’s declaration section or in a package specification is known as an explicit cursor.
In PL/SQL, explicit cursors are controlled by the following commands:
OPEN FETCH CLOSE
Q18. What are the Different Parameter Types in PL/SQL?
In PL/SQL, there are three sorts of parameters. The following are the details:
- IN: You can use the IN parameter to send values to the procedure that is being called. The default settings for the IN parameter can be set. It behaves in a predictable manner and cannot be altered.
- OUT: The OUT argument gives the caller a value. The OUT parameter is a variable that hasn’t been initialized and can’t be utilized in expressions.
- IN OUT: The IN OUT parameter delivers the caller’s starting values to a procedure and then returns the changed values. This parameter should be handled like a variable that has been initialized and given a value.
Difference Between Primary Key And Unique Key Explained!
Q19. Explain cursor attributes?
In Oracle, each cursor contains a set of properties that allow an application program to test the cursor’s state. The properties can be used to determine whether the cursor is open or closed, whether it is found or not, and how many rows are present. Examples are:
- %ISOPEN return true if cursor is open, FALSE otherwise
- %FOUND returns true if record was fetched successfully, FALSE otherwise
- %NOTFOUND return TRUE if record was not fetched successfully, FALSE otherwise
- %ROWCOUNT returns number of records fetched from cursor at that point in time.
Q20. What is REF Cursor?
Ref cursor is a datatype. Variable created based on ref cursor type is called cursor variable. It can be opened for different SQL statements. It can be opened dynamically framed SQL statements. The main advantage of ref cursor is, it can be passed as parameter between PL/SQL components. Ref cursor can be used to return result to client application API. There are two types of ref cursors.
- Strong ref cursor
- Weak ref cursor
What Is The Difference Between GUI And CUI?
Q21. What are Constraints? How many types of constraints are there?
If there are dependencies, constraints are employed to prevent improper data entry or deletion. At the table level, constraints impose rules. Constraints are easy way to enforce the business rule at the database layer. At the column or table level, constraints can be defined. The USER-CONSTRAINTS data dictionary table can be used to see the constraints defined for a certain table.
Q22. What is %TYPE?
The %TYPE attribute is used to declare a table column that contains the value of the column. The data type of the variable is the same as the table’s column.
Understanding Data Mining Architecture In Detail
Q23. What is the difference between Procedure and Function?
Q24. What Data Types are Present in PL/SQL?
In PL/SQL, there are a variety of data types to choose from. They are as follows:
- Scalar: CHAR, DATE, LONG, VARCHAR2, and other primitive data types are examples of scalar types.
- Composite: Composite data type are made up of different data kinds and are easy to update. For instance, RECORD, TABLE, and so on…
- Reference: CURSOR is an example of a reference data type.
- Large Object: BLOB, CLOB, and other large object types
50+ Most Asked UNIX Interview Questions And Answers
Q25. What is Dual Table?
All users have access to the dual table, which is held by the user SYS. It has one row with the value X and one column Dummy. When you only want to return a value once, the Dual Table comes in handy. A constant, pseudo-column, or expression that is not derived from a table with user data can be used as the value.
Q26. Explain the benefits of PL/SQL packages?
There are numerous advantages to utilizing PL/SQL packages:
- Separation of implementation and specs in a top-down application design approach (interface)
- Packages provide a container for sub-programs, allowing for better code organization.
- There is no need to modify the dependent objects or recompile the entire package if the subprograms are changed.
- Accessibility can be specified (private/public), ensuring the code’s security.
- The full package is loaded on the initial call of the subprogram, thus subsequent calls do not require disc I/O, resulting in improved performance.
- Without saving data in a database, data can be retained between transactions for the duration of the session.
Difference Between Primary Key And Unique Key Explained!
Q27. What do you know about the commands commit, rollback and savepoint?
COMMIT: During the current transaction, the COMMIT command saves changes to a database permanently.
ROLLBACK: At the end of a transaction, the ROLLBACK statement is used to undo any changes done since the transaction began.
SAVEPOINT: During the processing of a transaction, the SAVEPOINT command saves the current point with a unique name.
Q28. What is the %ROWTYPE?
Declare a variable that contains the structure of the records in a table using the percent ROWTYPE attribute. The data type of the variable is the same as the table’s columns.
What Is The Difference Between GUI And CUI?
Q29. What Are the Benefits of Stored Procedures?
Stored procedures offer a number of benefits that can aid in the development of complex database systems. The following are some of the benefits of stored procedures:
- Enhanced performance
- Productivity gains
- User-friendliness
- Scalability has been improved.
- Interoperability
- Improved security
- Replication
Q30. What are the different types of SQL General Functions?
NVL: This function converts a null value to a valid value. (exp1, exp2) NVL The NVL function returns the value of exp2 if exp1 is null.
NVL2: nvl2 returns exp2 if exp1 is not null, and exp3 if exp1 is null. Any data type can be used in the exp1 argument. NVL2 is a two-letter abbreviation for (exp1, exp2, exp3)
COALESCE: Returns the expression list’s first non-null expression. COALESCE (exp1, exp2… expn). The COALESCE function has an advantage over the NVL function in that it can handle several alternative values.
NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
Conditional Expressions: Conditional Expressions allow you to employ IF-THEN-ELSE logic in your SQL statements. CASE Expression and DECODE Function are two examples.
Understanding Data Mining Architecture In Detail
Q31. Explain what PL/SQL package consists of?
Package specification and package body are the two major sections of PL/SQL.
Package specification: It serves as a public interface for your program, containing procedures, types, and other information.
Package Body: This section contains the code needed to put the Package Specification into action.
Q32. What is a Sub Query? Describe its types?
A subquery is a query within another SQL query. The result returned by the inner query will be used as an input to outer query. Types of Subqueries are:
- Single-row subquery: Subqueries that return only one row from the inner select statement are known as single-row subqueries. =, >, >=,,,=,> are single-row comparison operators.
- Multiple-row subquery: Subqueries that return more than one row from the inner Select statement are known as multiple-row subqueries. Multiple-column subqueries are also available, which return more than one column from the inner select expression. IN, ANY, and ALL are examples of operators.
50+ Most Asked UNIX Interview Questions And Answers
Q33. What is the best way to save or place your message in a table?
To save a message in a table, use the add text procedure to load individual messages or the load from DBMS procedure to load collections of messages from a database table.
Q34. What does SYS.ALL DEPENDENCIES stand for?
SYS.ALL DEPENDENCIES lists all of the dependencies between packages, procedures, functions, and triggers that the current user has access to. Name, type, dependency type, referenced owner, and other columns are displayed.
Difference Between Primary Key And Unique Key Explained!
Q35. How is a DECLARE statement used?
DECLARE is used as the first statement for stand-alone files that consist of anonymous block of code which are not stored procedures, functions or triggers. Example –
1 DECLARE
2
3 num1 NUMBER(3);
4
5 num2 NUMBER(4);
6
7 BEGIN
8
9 — logic goes here
10
11 END;
Q36. What is the difference between Sqlcode and Sqlerrm, and why are they significant for Pl/SQL Developers?
SQLCODE returns the value of the error number for the most recent error found. The SQLERRM function returns the most recent issue’s actual error message. They can be used in exception handling to report or save an error that occurred in the code to a database called the error log. When it comes to the exception WHEN OTHERS, these are especially significant.
What Is The Difference Between GUI And CUI?
Q37. What is the difference between a stored procedure and a stored function in PL/SQL?
The following are the main distinctions between a saved procedure and a stored function:
- In a stored procedure, returning the value is optional, whereas returning the value in a stored function is compulsory.
- A stored procedure can contain both input and output parameters, whereas a stored function can only have one of them.
- In a saved procedure, exception handling is feasible, but it is not possible in a stored function.
Q38. In PL/SQL, how do you see User-defined Functions and Procedures?
User-defined functions and procedures are stored in the table USER SOURCE. The function and method names must be supplied in uppercase to be examined (in select command). To see the source code of a user-defined function or method, run the following command:
Select text from user_source where name=’PROCEDURE_NAME’;
Understanding Data Mining Architecture In Detail
Q39. What is the best way to make a function?
The following is the syntax for creating a CREATE function:
1 CREATE function_name
2
3 RETURN return_datatype
4
5 {IS | AS}
6
7 DECLARE
8
9 VARIABLE DATATYPE;
10
11 BEGIN
12
13 function_body
14
15 END function_name;
Q40. What is a mutating trigger?
Mutating error occurs whenever a row level trigger tries to modify or select data from the table that is already undergoing change. Mutating error get raised from row level trigger only and not statement level trigger.
When we use a statement-level trigger to do DML operations, the data is automatically committed to the database; however, when we use a row-level trigger to perform transactions, the data is not committed, and we read it from the same table again, mutating errors arise.
50+ Most Asked UNIX Interview Questions And Answers
Q41. In SQL, what is locking? What are the different types of it?
Locking keeps concurrent transactions from interacting in a detrimental way. Locks remained in place until Commit or Rollback was performed. The following are examples of locking mechanisms:
- Implicit Locking: Except for SELECT, all SQL statements are subject to implicit locking.
- Explicit Locking: This can be done explicitly by the user.
In addition, there are two locking options:
- Exclusive: Other users can’t utilize it because it’s exclusive.
- Share: Allows other users to see what you’re working on.
Q42. Explain the uses of Merge with Syntax in PL-SQL.
Merge decreases the amount of database scans and, if necessary, parallelizes operations. MERGE conditionally inserts or modifies data from one table to another. As an example,
1 Merge INTO orders p
2
3 Using customer c
4
5 ON (o.custid = c.custid)
6
7 WHEN MATCHED THEN
8
9 UPDATE SET o.del_add = c.add
10
11 WHEN NOT MATCHED THEN
12
13 INSERT(custid, add)
14
15 VALUES(c.empid, c.add);
If a record that meets the matching criterion is found, the address of that record is updated; otherwise, a new row is entered.
Difference Between Primary Key And Unique Key Explained!
Q43. In PL/SQL, what is the difference between the ROLLBACK and ROLLBACK TO statements?
The ROLLBACK command is used to undo all changes made since the transaction began.
The ROLLBACK TO command is used to undo a transaction until a SAVEPOINT is reached. Because transactions cannot be rolled back before the SAVEPOINT, the transaction stays active even if the command has not been supplied.
Q44. What are all of the Oracle joins used for?
- Cartesian Join: When a Join condition is faulty or missing entirely, the output is a Cartesian product, which displays all possible combinations of rows. Include a valid join condition in every “where” clause to avoid a Cartesian product. A minimum of N-1 Join criteria is required to join ’N’ tables together.
- Equi Join: This is a sort of join that involves both primary and secondary key relationships. Simple or Inner Joins are other names for Equi Join.
- Non-Equivalent Join: It is a type of non-equivalent join. A Non-Equi Join condition is one that does not use an equality operator. An operator other than the equal operator (=) is used to obtain the connection. Conditions like = and >= can be used, but BETWEEN is the most straightforward way to represent Non-Equi Joins.
- Outer Joins: It is used to retrieve rows that do not match the join requirement. The plus sign (+) is the outer join operator, and it is used on the information-deficient side of the join. Only one side of the statement, the side with missing data, can use the Outer Join operator. IN and OR operators cannot be used in an Outer Join condition.
- Self Join: It is the process of joining two tables together.
What Is The Difference Between GUI And CUI?
Q45. What are the components of an Oracle database’s logical database structure?
The following are the components of Oracle’s logical database structure:
Tablespaces: Tablespaces are the logical storage units that make up a database. This tablespace is a collection of logical structures that are linked together. To be more specific, tablespace groupings are linked to logical structures.
Database schema objects: A schema is a group of database items that belong to a single user. Tables, indexes, views, stored procedures, and other objects are among the objects. The user is the account in Oracle, and the schema is the object. It is also possible to create a schema on a database platform without specifying a user.
Q46. ON DELETE CASCADE vs. ON DELETE SET NULL: What’s the difference?
ON DELETE CASCADE indicates that if a row in the parent table is deleted, the child table’s dependent rows will be erased as well. In ON DELETE SET NULL When the parent value is removed, convert foreign key values to null. If a row in the parent table is referenced in the child table, it cannot be deleted without the ON DELETE CASCADE or ON DELETE SET NULL options.
Understanding Data Mining Architecture In Detail
Q47. In a table, what is the DEFAULT option?
The DEFAULT option can be used to set a default value for a column. If a row is added without a value for a column, this option prevents null values from entering the column. The value of the DEFAULT column can be a literal, an expression, or a SQL function like SYSDATE or USER, but it can’t be the name of another column or a pseudo column like NEXTVAL or CURRVAL
Q48. In Oracle, describe the different types of temporal data.
Oracle mostly offers the following types of temporal data:
- Date Data Type: Dates in various formats.
- TimeStamp Data Type: Time Stamps come in a variety of formats.
- Interval Data Type: A time interval between two dates.
50+ Most Asked UNIX Interview Questions And Answers
Q49. What exactly is a Discard File?
.dsc is the file extension.
Using the discard file clause, we must provide the discard file within the control file.
The discard file additionally stores reflected records based on the control file’s when clause condition. In the table clause, this condition must be met.
Q50. What are database triggers used for?
Triggers are programs that are fired or run automatically when certain circumstances occur and are used for:
- Complex security authorizations must be implemented.
- To control the column values.
- To keep duplicate tables in order.
- To put complex business regulations into action.
- To make log events more transparent.
Difference Between Primary Key And Unique Key Explained!
Q51. Difference between triggers and constraints?
Summing up
We have covered some of the most crucial pl/sql interview questions in this post, which will help you get through even the most difficult of interviews. These 51 questions will cover the most crucial aspects of pl/sql and will undoubtedly assist you in acing interviews.
You may also want to read:
