File Name: advanced oracle sql interview questions and answers .zip
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. This will return the same faulty set as the original.
Answer: Cursor is a sql private work area. It opens an area of memory where the query is parsed and executed. Answer: When referencing the current row from an explicit cursor,use the where current of clause. This allows you to apply updates and delete to the row currently being addressed,without the need to explicitly reference the rowid. You must include the for update clause in the cursor query on open. Syntax: Where current of cursor;. You can select from pseudocolumns, but you cannot insert, update, or delete their values.
The lists of pseudocolumns are:. Answer: You can create table from procedure using Execute immediate command. Answer: Without parameters you can get the more than one out values in the calling program. It is recommended not to use out parameters in functions. If you need more than one out values then use procedures instead of functions. Answer: Data integrity allows defining certain data quality requirements that the data in the database needs to meet.
Constraint types: There are five integrity constraints in Oracle. Not Null: A column in a table can be specified not null. The default is null. If the unique constraint encompasses two or more columns, no two equal combinations are allowed. Primary Key: On a technical level, a primary key combines a unique and a not null constraint.
Additionally, a table can have at most one primary key. After creating a primary key, it can be referenced by a foreign key. Foreign key: A foreign key constraint also called referential integrity constraint on a column ensures that the value in that column is found in the primary key of another table. If a table has a foreign key that references a table, that referenced table can be dropped with a drop table. Cascade constraints.
It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA attempt to create referential integrity constraint on temporary table. Check constraints: A check constraint allows stating a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used. Disadvantage: No such of disadvantage of cursor.
When we use cursor one thing is necessary. When I have select only one record then we should use implicit cursor and when more record then we should use explicit cursor.
TYPE can be used with the column name preceded with table name to decide the datatype and length of the variable at runtime. These two provides data independence and allows you to adopt database changes due to new business requirements. You need not know datatype and size in advance. Answer: Commit in this context will not do anything except the committing the changes into database, done using DML statements.
A trigger is triggered automatically when an associated DML statement is executed. Function can be called as an part of an expression. Function must have at least one return statement. Answer: As with explicit cursor there are four attributes for obtaining status information about a cursor. When appended to the cursor variable name these attribute return useful information about the execution of a data manipulation statement.
Answer: There are three modes of parameters: In default : Passes a constant value from the calling environment to the procedure. Formal parameter acts as a constant. Actual parameter can be literal, expression, constant or initialized variable. Can be assigned a default value. Out: Passes a value from the procedure to the calling environment. Initialized variable, must be a variable, cannot be assigned a default value. In Out: Passes a value from the calling environment in to the procedure and a possibly different value from the procedure back to the calling environment using the parameter.
Initialized variable must be a variable cannot be assigned a default value. Answer: Package body: A package body usually has a specification and a body stored separately in the data base.
The specification is the interface to your applications. It declares the types, variables, constants and subprograms available for use. The body fully defines cursors and subprograms and to implement the specification. You can report errors to your application and avoid returning unhandled exceptions. It is used in two different places executable section and exceptional section.
It is a character string up to bytes. Answer: A cursor is a private sql work area where the statements are executed and the processing information is stored.
Answer: A cursor for loops processes rows in an explicit cursor. It is a shortcut because the cursor is opened, rows are fetched once for each iteration in the loop, the loop exists when the last row is processed, and the cursor is closed automatically. The loop itself is terminated at the end of the iteration where the last row is fetched. End loop;. There are two types of exceptions: Implicitly raised. That allows you to refer to any internal exception by name and to write a specific handler for it.
Answer: Sqlcode returns the numeric value for the error code or error message by using two functions. Based on the value of the code or message,you can decide which subsequent action to take based on the error.
Sqlerrm returns character data containing the message associated with the error number. Answer: It is not possible. Answer: Scalar: Single values with no internal components. Composite: Data items that have internal components that can be accessed individually. Reference: Pointers to other data items. Large Object LOB : Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.
Data auditing, implementing complex business rules, security are main uses of database triggers. Answer: Mutation of table occurs. By this the procedure will be treated as a separate identity. Answer: The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
By this, the procedure will be treated as a separate identity. The data type of the variable is the same as the columns in the table. The data type of the variable is the same as the column in the table. Some of the Syntax error are incorrect spelling, missing semicolon etc.
This error occurs after executing the programs. Answer: The anonymous blocks are not stored into the database and it is executed without naming the blocks or unsaved. The subprograms are saved with a specific name and stored into the database. Some of the subprograms are functions, procedure. Packages have two parts 1. Package Specification 2. Package Body. Package Specification is a must whereas Package Body is optional. First, we need to create a Package Specification then Package Body.
Package Specification contains only the definition of a package such as declare a variable, constant and define the functions and procedure inside the package. The Package body contains the actual implementation of the functions or procedure.
Package includes constants, variables, Exception, function, and procedures, the packages are compiled and stored in the database. The main use of package is to manage authorization easily, Improve application performance and make code more modular. This is used to monitor the changes happening in the tables.
The syntax to create a Create function as below. A stored procedure can have both input and output parameter but stored function will have only input parameter. In the Stored procedure, we can use exception handling and it is not possible in Stored functions. The rollback should be done for the specific or group of command before committing the changes in a database.
By using this command, the output will be printed in a script out panel instead of DBMS output panel. Answer: The operator is used to concate the strings. The function and procedure name should be given in the uppercase in select command to view them.
They are two types of Exceptions available.
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.
Top 65 PL/SQL Interview Questions & Answers. Details: Last Updated: 21 February Download PDF. 1) What is PL SQL? PL SQL is a procedural language.
Here is a list of these interview questions and answers:. Explain the uses of a database trigger. What is a stored procedure?
PL SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration. Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed. It is used for : 1 Audit data modifications.
What is Failover clustering overview? Answer: Failover clustering is mainly used for data availability. Typically, in a failover cluster, there are two machines. One machine provides the basic services and the second is available to run the service when the primary system fails. The primary system is monitored periodically to check if it works. This monitoring may be performed by the failover computer or an independent system also called a cluster controller. In an event of failure of the primary computer, the failover system takes control.
A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches. DBMS is a system software responsible for the creation, retrieval, updation and management of the database. It ensures that our data is consistent, organized and is easily accessible by serving as an interface between the database and its end users or application softwares. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables and relations can be defined between the common fields of these tables.
Mindmajix offers Advanced Oracle PL SQL Interview Questions that help you in cracking your interview & acquire a dream career as an Oracle PL SQL.
Answer: Cursor is a sql private work area. It opens an area of memory where the query is parsed and executed. Answer: When referencing the current row from an explicit cursor,use the where current of clause. This allows you to apply updates and delete to the row currently being addressed,without the need to explicitly reference the rowid. You must include the for update clause in the cursor query on open.
Do you want to move to a job that uses SQL? Are you unsure about what to expect during the job interview or how to benchmark your knowledge? This article will give you a good idea of where you stand. The modern division of work pushes people to perform very specific tasks, expecting them not to look at what's on the other side.
A database is a collection of information in an organized form for faster and better access, storage, and manipulation. It can also be defined as a collection of tables, schema, views, and other database objects. Data warehouse refers to a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining as well as online processing.
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.
Your email address will not be published. Required fields are marked *