Inline Query: An inline query is a query that is defined in the FROM clause. Inline views can contain multiple columns.
Sub Query: A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. Subqueries (in the Oracle meaning) should return only one.
1. In which language has Oracle been developed?
Oracle has been developed using C Language.
2. What is the Difference between varchar and varchar2 data types?
Varchar can store up to 2000 bytes, and varchar2 can store up to 32672 bytes. Varchar will occupy space for NULL values, and Varchar2 will not occupy any space. Both differ with respect to space.
3. What is a RAW datatype?
RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes.
4. What is the use of the NVL and NVL2 functions?
The NVL function replaces NULL values with another or given value.
The NVL2 function will take 3 arguments NVL2('A', 'B',' C' ). It will check the first value. If it is not null, then it will return the second value. If it is null, then it will return the Third value.
NULLIF function takes two argument values. If both are exactly the same, then it will return a NULL value. If both values are not the same, then it will return the first value. If the first value is null, then it will return an error. If the second argument is null, then it will return the First value.
5. What is COALESCE function?
The coalesce function takes any number of argument values and will return the first, not the null value. COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL.
Coalesce(value1, value2,value3,…)
6. What is DML?
Data Manipulation Language (DML) is used to access and manipulate data in the existing objects. DML statements are insert, select, update and delete and it won’t implicitly commit the current transaction.
7. What is the difference between TRANSLATE and REPLACE?
Translate is used for character by character substitution and Replace is used substitute a single character with a word.
8. What is the usage of Merge Statement?
Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations. Actually merge is the combination of two DML statement Insert & Update.
Error Log table create Package:
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name=>'EMPLOYEE_SALARY_PROCESS',err_log_table_name=>'EMPLOYEE_SALARY_PROCESS_ERROR');
END;
INSERT INTO EMPLOYEE_SALARY_PROCESS SELECT * FROM EMPLOYEE_SALARY_PROCESS_TEMP9. What is a sub query and what are the different types of subqueries?
Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.
There are two different types of subqueries:
- Correlated sub query
A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.
- Non-Correlated subquery
This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.
10. What is cross join?
Cross join is defined as the Cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table.
11. What are temporal data types in Oracle?
Oracle provides following temporal data types:
- Date Data Type – Different formats of Dates
- TimeStamp Data Type – Different formats of Time Stamp
- Interval Data Type – Interval between dates and time
12. How do we create privileges in Oracle?
A privilege is nothing but right to execute an SQL query or to access another user object. Privilege can be given as system privilege or user privilege.
[sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]
13. What is VArray?
Description the varray (variable size array) is one of the three types of collections in PL/SQL (assosiated array, nested table, varray). VArray is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.
Collection: A collection is an ordered group of logically realated elements. In a collection the internal component always have the same datatype, and are called elements.
Records: In a records the internal components always have different data types, and are called fields.
14. How do we get field details of a table?
Describe <Table_Name> is used to get the field details of a specified table.
15. What is the difference between rename and alias?
Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename is nothing but replacement of name and Alias is an alternate name of the table or column.
16. What is a View?
View is a logical table based on other base tables or views. Views will not have data of his own,It has typically fetch data of his underlying base table or view. Views contains no data itself (except materialized view). There are two types of view .
1. Simple view : Simple view created based on single table without having aggregrate column,Distinct keyword. DML operation is allowed in simple view.
2. Complex view: Complex view created based on any number of tables. It can contain aggregrate column,distinct keyword or pseudocolumn. No DML operation allowed.
By using WITH READ ONLY constraints we are not able to operation any DML on the view.
Syntex: create or replace view v_emp as select * from emp with read only;
By using WITH CHECK OPTION CONSTRAINTS we can restrict a view to process the dml operation. Suppose I have create a view based on emp table where department is CSE and I want only CSE department's data will be changed. For this we can create view with check option.
Syntex: create or replace view v_emp as select * from emp where department='CSE' with check option;
View recompile : If view is going under Invalid status then need to recompile view.There are three way to compile view .one is -Alter view view_name compile; another one is to recreate view. Beside this if we call the view then it will try to compile view.
Different between View and Materialized view :
View is just a named query. It soesn't store anything. when there is a query on view, it runs the of the view defination. Actually data comes form table. no need to refresh data.
Materialized view stores data physically and get updated periodically. While querying MV, it gives data from MV. Need to refresh seperately.
17. What are SET operators?
SET operators are used with two or more queries and those operators are Union, Union All, Intersect and Minus.
Union : UNION operator is used to combine the result sets of 2 or more Oracle SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION operator must have the same number of fields in the result sets with similar data types.Union will remove duplicate and sort the data.
Union ALL: If you want to retain the duplicate rows, you explicitly use UNION ALL.Union will not remove duplicate and will not sort the data.
Intersect :The intersect of T1 and T2 result returns 2 and 3. Because these are distinct values that are output by both queries. The following picture illustrates the intersection of T1 and T2:
The illustration showed that the INTERSECT returns the intersection of two circles (or sets).
Minus : The Oracle MINUS operator compares two queries and returns distinct rows from the first query that are not output by the second query. In other words, the MINUS operator subtracts one result set from another.
The following picture illustrates the result of the MINUS of T1 and T2:
18. How can we delete duplicate rows in a table?
Duplicate rows in the table can be deleted by using ROWID.
19. What is an integrity constraint?
An integrity constraint is a declaration defined a business rule for a table column. Integrity constraints are used to ensure accuracy and consistency of data in a database. There are types – Domain Integrity, Referential Integrity and Domain Integrity.
20. What are the various constraints used in Oracle?
Following are constraints used:
- NULL – It is to indicate that particular column can contain NULL values
- NOT NULL – It is to indicate that particular column cannot contain NULL values
- CHECK – Validate that values in the given column to meet the specific criteria
- DEFAULT – It is to indicate the value is assigned to default value
21. What is difference between SUBSTR and INSTR?
SUBSTR returns specific portion of a string and INSTR provides character position in which a pattern is found in a string.
SUBSTR returns string whereas INSTR returns numeric.
22. What is the parameter mode that can be passed to a procedure?
IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.
23. What are the different Oracle Database objects?
There are different data objects in Oracle –
- Tables – set of elements organized in vertical and horizontal
- Views – Virtual table derived from one or more tables
- Indexes – Performance tuning method for processing the records
- Synonyms – Alias name for tables
- Sequences – Multiple users generate unique numbers
- Tablespaces – Logical storage unit in Oracle
24. What are the differences between LOV and List Item?
LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.
25. What is the maximum number of triggers that can be applied to a single table?
A trigger is a PL/SQL block structure which is automatically fired when an event occurs in the database. 12 is the maximum number of triggers that can be applied to a single table.
There ar 5 types of trigger.
1.DML Triggger
2.DDL Trigger
3. SYSTEM Trigger
4.Insted of trigger
5. Compound Trigger
There are 2 types of DML trigger in oracle.
1.Row level Trigger
1.Insert (Before,After)
2.Update (Before After)
3.Delete (Before, After)
2.Statement Level trigger:
1.Insert (Before,After)
2.Update (Before After)
3.Delete (Before, After)
Trigger Execution Order:
1.Before Statement level trigger
2.Before Row level trigger
3.After Row level trigger
4.After Statement Level trigger
26. What is the data type of DUAL table?
The DUAL table is a one-column table present in oracle database. The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.
27. What is difference between Cartesian Join and Cross Join?
There are no differences between the join. Cartesian and Cross joins are same. Cross join gives cartesian product of two tables – Rows from first table is multiplied with another table which is called cartesian product.
Cross join without where clause gives Cartesian product.
28. What types of joins are used in writing subqueries?
A Join is used to compare and combine, this means literally join and return specific rows of data from two or more tables in a database.
There are three types of joins in SQL that are used to write the subqueries.
- Self Join: This is a join in which a table is joined with itself, especially when the table has a foreign key which references its own primary key.
- Outer Join: An outer join helps to find and returns matching data and some dissimilar data from tables.
- Equi-join: An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns.
Both these statements Decode and Case will work similar to the if-then-else statement and also they are the alternatives for each of them. These functions are used in Oracle for data value transformation.
30. What is SQL and also describe types of SQL statements?
SQL stands for Structured Query Language. SQL is used to communicate with the server in order to access, manipulate and control data. There are 5 different types of SQL statements available. They are:
- Select: Data Retrieval
- Insert, Update, Delete, Merge: Data Manipulation Language (DML)
- Create, Alter, Drop, Rename, Truncate: Data Definition Language (DDL)
- Commit, Rollback, Savepoint: Transaction Control Statements
- Grant, Revoke: Data Control Language (DCL)
There are two data types available in PL/SQL. They are namely:
- Scalar data types
Example: Char, Varchar, Boolean, etc. - Composite datatypes
Example: Record, Collection (VARRAY,Nested Table & Associated Table). - REF Cursor : REF cursor is oracle Data Type. REF cursor are used for dynamic SQL. We will be able to dynamically assign a different and completely different set of SQL at runtime.
32. What are the uses of a database trigger
Triggers are the programs which are automatically executed when some events occur:
- Implement complex security authorizations.
- Drive column values.
- Maintain duplicate tables.
- Implement complex business rules.
- Bring transparency in log events.
Triggers are very different from Constraints in the following ways:
| Triggers | Constraints |
| Only affect those rows added after the trigger is enabled | Affect all rows of the table including that already exist when the constraint is enabled |
| Triggers are used to implement complex business rules which cannot be implemented using integrity constraints | Constraints maintain the integrity of the database |
34. Exception handling in PL/SQL
An error is an abnormal termination during a normal execution of the program. When an error occurs in PL/SQL, the corresponding exception is raised. This also means, to handle undesired situations where PL/SQL scripts gets terminated unexpectedly, error-handling code is included in the program. In PL/SQL, all exception handling code is placed in the Exception section.
There are 3 types of Exceptions:
- Predefined Exceptions: Common errors with predefined names.
- Undefined Exceptions: Less common errors with no predefined names.
- User-defined Exceptions: Do not cause runtime error but violate business rules.
COUNT (*): This returns a number of rows in a table including the duplicates rows and the rows containing null values in the columns.
COUNT (EXP): This returns the number of non-null values in the column identified by an expression.
COUNT (DISTINCT EXP): It returns the number of unique, non-null values in the column identified by an expression.
36. List out the difference between Commit, Rollback, and Savepoint?
The major differences between these are listed below:
- Commit: This ends the current transaction by ensuring that all pending data changes are made permanent.
- Rollback: This ends the current transaction by discarding or deleting all pending data changes.
- Savepoint: It divides a transaction into smaller parts. You can rollback the transaction until you find a particular named savepoint.
Answer: Both VARCHAR & VARCHAR2 are Oracle data types that are used to store character strings of variable length. Their differences are:
- VARCHAR can store characters up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.
- VARCHAR will hold the space for characters defined during declaration even if all of them are not used whereas VARCHAR2 will release the unused space.
Answer: Both the commands are used to remove data from the database.
The difference between the two include:
- TRUNCATE is a DDL operation while DELETE is a DML operation.
- TRUNCATE removes all the rows but leaves the table structure intact. It can not be rolled back as it issues COMMIT before and after the command execution while the DELETE command can be rolled back.
- The TRUNCATE command will free the object storage space while the DELETE command does not.
- TRUNCATE is faster compared to DELETE.
40. How does the ON-DELETE-CASCADE statement work?
Answer: Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.
We can add ON DELETE CASCADE option on an existing table using the below set of commands.
41. What is a trigger and what are its types?
Answer: A trigger is a stored program which is written in such a way that it gets executed automatically when some event occurs. This event can be any DML or a DDL operation.
PL/SQL supports two types of triggers:
- Row Level
- Statement Level
|
|
|
|
Row level triggers executes once |
|
|
Specifically used for data |
|
|
“FOR EACH ROW” clause is |
“FOR EACH ROW” clause is omitted in |
|
Example: If 1500 rows are to be |
|
Answer: Global variable is the one, which is defined at the beginning of the program and survives until the end. It can be accessed by any methods or procedures within the program, while the access to the local variable is limited to the procedure or method where it is declared.
42. What are the packages in PL SQL?
Answer: A package is a group of related database objects like stored procs, functions, types, triggers, cursors, etc. that are stored in the Oracle database. It is a kind of library of related objects which can be accessed by multiple applications if permitted.
PL/SQL Package structure consists of 2 parts: package specification & package body.
PL/SQL Package Benefits: Summary
This article lists key benefits provided by PL/SQL packages for PL/SQL subprograms over standalone procedures and functions. These are:
- Organized code management (Logically group the related subprograms,object and variable)
- Easy (top-down) application design
- Easy changes to implementation
- Security and maintainability (Security of code through implementation of private subprograms)
- Session-wide persistence of variables
- Better berformance ( Enter packages is loaded into memory when the package is first referenced, There is only one copy of the package in the memory for all users.)
- Overloading (Overloading of subprograms are only possible within package)
.png)
