ORACLE
1. If a package is created with two functions having same name but with different arguments as follows
create package overload_eg is
function test(c_val char) return char;
function test(c_val varchar2) return varchar2;
Now which function will be executed when call to function a is made as follows
SELECT overload_eg.test(‘x’) FROM dual;
ANS : It will return an error ORA-06553: PLS-307: too many declarations of 'A' match this call.
2. What is the new feature in import & export utility in Oracle 8I
You can Import or Export partition
3. What is the difference between truncate & delete command
TRUNCATE
It is a DDL command
It is faster than delete command. Because it doesn’t fires tables delete trigger.
It doesn’t generates rollback inforation.
DELETE
It is a DML command.
It is slower than truncate command.
It generates rollback inforation.
Truncate command is used to remove all rows from table or cluster and reset the storage parameters to the values when table or cluster were created.
4. What are the possible impacts on database objects when a table is dropped.
When table is dropped
· It invalidates table dependent objects like views, synonym etc.
· When table is recreated it requires you to regrant object privileges on the table.
· You need to recreate indexes, integrity constraints & triggers.
All the above problems doesn’t occur if we truncate the table.
Difference Between Synonym & View
1. View can be based on multiple tables. Synonym can be based on single table.
2. View can be for tables, view, or synonym. Synonym can be for any schema object.
1. Delete the duplicate rows from the table.
DELETE FROM emp a
WHERE Where rowid > (SELECT min (rowid)
FROM emp b
WHERE a.empno = b.empno)
2. To find the nth maximum salary.
SELECT sal FROM emp a Where &no = (SELECT count(distinct sal)
FROM emp b
WHERE a.sal <= b.sal) 3. To find the maximum 3 salaries : SELECT sal FROM emp a Where &no > (SELECT count(distinct sal)
FROM emp b
WHERE a.sal <> (SELECT count(distinct sal)
FROM emp b
WHERE a.sal < b.sal)
5. Give a query to retrieve last 5 rows from emp table.
SELECT * FROM emp
WHERE rownum < 6
ORDER BY rownum DESC
6. What will be the answer of SELECT * FROM emp WHERE rownum = 20;
No rows selected. Because query is successful, when WHERE clause is evaluated to TRUE. In above case when query retrieves first record the ROWNUM is 1. The WHERE condition is evaluated to FALSE. Hence no rows are returned.
7. Give a query to retrieve rows from 3 to 6.
8. Emp table contains 6 rows.
Dept table contains 10 rows.
What will be the output of SELECT * FROM emp, dept
9. Emp table contains 0 rows.
Dept table contains 10 rows.
What will be the output of SELECT * FROM emp, dept
10. What will be the output of ROUND(10, -2)
11. What will be the output of SQRT(-4)
12. What is the result of sum (500 + '50')?
550. Oracle doses implicit data conversion.
13. What is the result of NVL(SAL, 1000) 1000
An error FROM Keyword not found where expected is displayed.
What are joins? Give its example.
A) Equijoins
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.
B) Self Joins
A self join is a join of a table to itself. This table appears twice in the FROM clause
C) Outer Joins
The outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition.
What do you mean by TRANSACTION.
How to view the list of PROCEDURES. (Like select * from tab for list of TABLES).
Will INDEX on a TABLE WILL occupy space in the DATABASE.
What happens when someone updates the row while processing and what will you do for that?
Mutating Error encountered.
What are the consequences of dropping a table?
It invalidates the table’s dependent objects such as views.
The integrity constraints are lost.
All the indexes, triggers are lost.
What is Normalization? How many normal forms are there?
What is the format of ROWID
Every row in a nonclustered table of an Oracle database is assigned a unique ROWID that corresponds to the physical address of a row's row piece. In the case of clustered tables, rows in different tables that are in the same data block can have the same ROWID. Each table in an Oracle database internally has a pseudocolumn named ROWID. The Oracle Server uses an extended ROWID format, which supports features such as table partitions, index partitions, and clusters. The extended ROWID includes the following information:
· Data object (segment) identifier
· Datafile identifier
· Block identifier
· Row identifier
Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.
· Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.
· Logical rowids store the addresses of rows in index-organized tables.
A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables
What is PSUDEOCOLUMNS? Name them?
· ROWID
· ROWNUM
· CURR_VAL : returns the current value of a sequence.
· NEXT_VAL : increments the sequence and returns the next
· value.
· LEVEL : For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any non–root node. A parent node is any node that has children. A leaf node is any node without children.
Which statement is suitable
Select sum(c1) +sum(c2) from emp
Select sum ((c1) + (c2)) from emp
Select sum(c1) +sum(c2) from emp
What is Rollback segments?
What is Rollback forwarding?
Where NEXTVAL &CURRVAL cannot be used?
You can use CURRVAL and NEXTVAL in these places
· The SELECT list of a SELECT statement that is not contained in a Subquery, snapshot or view
· The SELECT list of a subquery in an INSERT statement
· The VALUES clause of an INSERT statement
· The SET clause of an UPDATE statement
You cannot use CURRVAL and NEXTVAL in these places
· A subquery in a DELETE, SELECT, or UPDATE statement
· In View or Snapshot.
· SELECT statement with the DISTINCT operator, GROUP BY or ORDER BY clause
· Query conaining UNION, INTERSECT, or MINUS set operator
· The WHERE clause of a SELECT statement
· DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
· The condition of a CHECK constraint
What are the set operators?
Union, Union All, Intersect, Minus.
What is FOR UPDATE & FOR UPDATE OF clause?
The FOR UPDATE clause locks the rows selected by the query. Once you have selected a row for update, other users cannot lock or update it until you end your transaction. The FOR UPDATE clause cannot be used with the following other
constructs:
· DISTINCT operator
· GROUP BY clause
· Set operators
· Group functions
In case of joins where columns from 2 or more tables are involved, FOR UPDATE clause will lock rows from all tables appearing in the join condition. In this case if you want to lock rows of specific table only then make use of FOR UPDATE OF clause and mention table to be locked.
The following statement locks rows in the EMP, & DEPT tables.
SELECT empno, sal, comm FROM emp, dept
WHERE job = ’CLERK’ AND emp.deptno = dept.deptno
AND loc = ’NEW YORK’ FOR UPDATE;
The following statement locks rows only from EMP table.
SELECT empno, sal, comm FROM emp, dept
WHERE job = ’CLERK’ AND emp.deptno = dept.deptno
AND loc = ’NEW YORK’ FOR UPDATE OF emp;
Stored Procedures, Functions and packages.
A stored procedure or function is a PL/SQL program unit that
Has a name
· Can take parameters, and return values
· Are stored in the data dictionary n compiled format.
· Can be invoked by many users
Stored procedures offer you advantages in the following areas:
· Development
· Integrity
· Security
· Performance
· Memory allocation
A package is a group of PL/SQL types, objects, and stored procedures and functions. The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.
What is PL / SQL table
Can we Import Constraints?
Is there any way to write trigger on schema?
What is the difference between Trigger & Procedure?
1. You need to call procedure explicitly. Where as Trigger fires automatically when any DML statement is issued on table.
2. You can pass parameters a procedure. You can not pass parameters to a Trigger.
3. You can write COMMIT in procedure but not in TRIGGER.
What is the difference between Function & Procedure?
1. Function returns value with a RETURN statement. We can not use RETURN statement in procedure. In Procedure we can return value using
2. You can use function in SQL statement. But you can not use procedure in select statement.
What is Co-related sub-query?
A correlated subquery is a subquery that is evaluated once for each row processed by the parent statement. the parent table is referred in the where clause of the sub-query .
What are the steps involved in starting & stopping the oracle database? Explain briefly.
Starting an Instance.
Mounting the Database
Opening a Database
What is the function of SQL*Net ?
If the user and server processes are on different computers of a network or if the user processes connect to shared server processes through dispatcher processes, the user process and server process communicate using SQL*Net. Dispatchers are optional background processes, present only when a multi–threaded server configuration is used. SQL*Net is Oracle’s interface to standard communications protocols that allows for the proper transmission of data between computers.
REPORTS
1. What is the use of placeholder column.
2. Can you assign value to a placeholder column from PL/SQL trigger of a formula column item?
3. Can you assign value to a placeholder column from Format trigger of a text item on layout?
4. Can you change value of a parameter at runtime whose List of values property contains static values or values from a select statement. - Yes
5. What are the different types of parameters in report. – User & System
6. What is data parameter.
7. How many groups you can have in matrix report. - 4
8. What is the use of Distribution property in layout section.
9. What is the use of additional layout.
10. Can you call a report from a report. - Yes
11. What is the use of SRW package.
12. What is External Query Source File property of a query.
The External Query Source File property is the name of a query stored in the database or a file whose SELECT statement should be used as the SELECT statement for the current query. Enter a valid external query source file name not to exceed 1K. The external query source file you reference cannot exceed 32K in length. If it does, it will be truncated and a warning raised. If the query is stored in a file, you can prefix a path to the query name. If a path is not prefixed to the filename, Report Builder uses its file path search order to find the file. If the query is stored in the database, you can prefix DB: to it to indicate that it is stored in the database. For example:
DB:myquery
If you prefix DB: to the query name, the Report Builder will go directly to the database to find the query. If you are not connected to the database, it will not be able to find the query and will issue a message to that effect.
If you update the external query source file, you must ensure that the update does not invalidate the report definition. For example, if the report has a column for DEPTNO and you delete DEPTNO from the SELECT list of the external query source file, the report cannot run.
If you change the query in source file the query in report gets changed automatically. But you need to recompile the report again.
If you delete the source file and try to open the report dependent on that file you get the following error message
REP-1062 Unable to find external query.
When you specify an external query source file name, the SELECT statement of the external query appears in the SQL Query Statement field as read-only. To modify it, you must open the external query source file.
13. What is template.
Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report.
When you create a report through the Report Wizard, you have the option of applying a template (.tdf file) to the report. When you choose a template, objects in the margin area of a template are imported into the same locations in the current report section, overwriting any existing objects.
No comments:
Post a Comment