Pl Sql Developer Interview Questions And Answers Pdf
File Name: pl sql developer interview questions and answers .zip
Cursor is private sql area which is used to execute sql statements and store processing information.
- PL/SQL Interview Questions
- Top PL/SQL Interview Questions and Answers
- Oracle PL SQL Interview Questions
Now the client wants to insert a record after the identity value 7 with its identity value starting from Write a single query to calculate the sum of all positive values of x and he sum of all negative values of x. Although there are 4 customers not referred by Jane Smith including Jane Smith herself , the query will only return one: Pat Richards.
PL/SQL Interview Questions
Welcome to Mindmajix - The leading global online training platform. There are a lot of opportunities from many reputed companies in the world. This course will help you to achieve excellence in this domain. SQL is a language used to communicate with the server to access, manipulate, and control data.
Ans: Alias is a user-defined alternative name given to the column or table. By default column, alias headings appear in upper case. Ans: A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Character and date column headings are left-justified and number column headings are right-justified. SQL functions can take arguments but always return some value. There are two distinct types of SQL functions:.
Ans: Character functions: accept character input and return both character and number values. Types of character function are:. Number Functions: accept Numeric input and return numeric values. Date Functions: operates on values of the Date data type.
Ans: Dual table is owned by the user SYS and can be accessed by all users. It contains one column Dummy and one row with the value X. The Dual Table is useful when you want to return a value only once. The value can be a constant, pseudocolumn, or expression that is not derived from a table with user data. Ans: Conversion Functions converts a value from one data type to another.
Conversion functions are of two types:. The format model must be enclosed in single quotation marks and is case sensitive. Ans: General functions are of the following types:. Ans: ANY Operator compares value to each value returned by the subquery. ALL Operator compares value to every value returned by the subquery. It is useful in data warehousing applications. Feedback Command: Displays the number of records returned by a query.
Ans: Joins are used to retrieve data from more than one table. For exp: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row. Equi Join: This type of Join involves primary and foreign key relations. Equi Join is also called Simple or Inner Joins. Outer Joins: Outer Join is used to fetch rows that do not meet the join condition.
The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. Cross Join clause produces the cross-product of two tables. This is the same as a Cartesian product between the two tables. It is equal to the Equi-Join. If the columns have the same names but different data types, then the Natural Join syntax causes an error.
Do not use a table name or alias in the referenced columns. For ex: Select a. Use the ON clause to specify a join condition.
The ON clause makes code easy to understand. ON clause is equals to Self Joins. The ON clause can also be used to join columns that have different names. Ans: Entity: A significant thing about which some information is required. Attribute: Something that describes the entity. For exp: empno, empname, emp address columns. Tuple: A row in a relation is called Tuple. Ans: Transaction consists of a collection of DML statements that forms a logical unit of work.
The common errors that can occur while executing any transaction are:. Ans: Locking prevents destructive interaction between concurrent transactions.
Locks held until Commit or Rollback. Types of locking are:. Explicit Locking: This can be done by the user manually. Ans: Naming rules to be considered for creating a table is:. This option prevents null values from entering the column if a row is inserted without a value for that column.
Contain USER information. It contains database information. All data dictionary tables are owned by the SYS user. Ans: Data Types is a specific storage format used to store column values. Few data types used in SQL are:. Ans: During modifying a column:. Only one LONG column can be used per table. No constraint can be defined on a LONG column. After a column has been marked as unused, you have no access to that column.
In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column. Ans: The main difference between Truncate and Delete is as below:. Ans: Constraints are used to prevent invalid data entry or deletion if there are dependencies. Constraints enforce rules at the table level. Constraints can be created either at the same time as the table is created or after the table has been created.
Constraints can be defined at the column or table level. There are 5 types of constraints:. There can be more than one Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Key automatically creates an index on the column it is created. Download Now! Ans: A View logically represents subsets of data from one or more tables.
A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed.
The tables on which a view is based are called Base Tables. Ans: The main differences between the two views are:. It is the reason, we are employing each row clause and internally stored DML transaction in trigger specification, these qualifiers: old, new, are also called records type variables. Ans: If we want to perform multiple operations in different tables then we must use triggering events within the trigger body.
These are inserting, updating, deleting clauses. These clauses are used in the statement, row-level triggers. These triggers are also called as trigger predicate clauses. The discard file also stores reflected records based on when clause condition within the control file.
This condition must be satisfied in the table clause. Oracle 7. In static cursor database servers execute only one select statement at a time for a single active set area wherein ref cursor database servers execute a number of select statements dynamically for a single active set area that's why those cursors are also called a dynamical cursor. Generally, we are not allowed to pass static cursor as parameters to use subprograms whereas we can also pass ref cursor as a parameter to the subprograms because basically precursor is a user-defined type in oracle we can also pass all user-defined type as a parameter to the subprograms.
Generally, the static cursor does not return multiple records into the client application whereas the ref cursor is allowed to return multiple records into the client application Java,. This is a user-defined type so we are creating it in 2 steps process i.
Ans: In all databases having 2 ref cursors. A strong ref cursor is a ref cursor that has a return type, whereas a weak ref cursor has no return type. Overloading procedure having the same name with different types or different numbers of parameters. Flashback query is handle by the Database Administrator only flashback queries along allows the content of the table to be retrieved with reference to the specific point of time by using as of clause that is flashback queries retrieves accidental data after committing the transaction also.
Flashback queries generally use undo file that is flashback queries retrieve old data before committing the transaction oracle to provide two methods for flashback queries. Package specification: it acts as a public interface for your application which includes procedures, types, etc.
Top PL/SQL Interview Questions and Answers
It is a block-structured language having logical blocks which are made up of three sub-blocks i. It includes procedural language elements like conditions and loops and allows declaration of constants and variables, procedures and functions. It also helps the users to develop complex database applications using control structures, procedures, modules, etc. Each constant, value and parameter has a data type that tells us the type of data used and their associated operations in the program. It is an attribute which is used for anchoring.
Oracle PL SQL Interview Questions
From these interview questions, you.. Here is a list of these interview questions and answers:. Explain the uses of a database trigger.
It supports procedural features of programming language and SQL both. It is invented to overcome the limitations of SQL. Arrays are like temporary tables in memory that are processed very quickly.
Submit an interview question
PL Procedural language is an extension to SQL Structured Query Language where a developer can write complex database interactions using procedures, control structures like branching and iteration, modules and functions. A nswer: A procedural language where code can be executed in blocks. It is an extension of SQL. The cursor is used to hold records returned by the SQL query. There are 2 types of cursors — implicit and explicit. Answer: The trigger is a stored procedure that is automatically invoked when an event happens. The event could be: insert, update, delete, etc… Syntax —.
Welcome to Mindmajix - The leading global online training platform.