Technical Interview Questions

1. Can you create a table with fields not referring to data elements?

YES. eg:- ITAB LIKE SPFLI.here we are referening to a data object(SPFLI) not data element. What are the different types of data dictionary objects? tables, structures, views, domains, data elements, lock objects, Match code objects.

2. What should be the approach for writing a BDC program?

STEP 1: CONVERTING THE LEGACY SYSTEM DATA TO A FLAT FILE to internal table CALLED “CONVERSION”.
STEP 2: TRANSFERING THE FLAT FILE INTO SAP SYSTEM CALLED “SAP DATA TRANSFER”.
STEP 3: DEPENDING UPON THE BDC TYPE
i)call transaction(Write the program explicity)
ii) create sessions (sessions are created and processed.if success data will transfer).

3. What are the problems in processing batch input sessions and How is batch input process different from processing online?

PROBLEMS:
i) If the user forgets to opt for keep session then the session will be automatically removed from the session queue(log remains). However if session is processed we may delete it manually.
ii)if session processing fails data will not be transferred to SAP database table.

4. What does an extract statement do in the ABAP program?

Once you have declared the possible record types as field groups and defined their structure, you can fill the extract dataset using the following statements: EXTRACT. When the first EXTRACT statement occurs in a program, the system creates the extract dataset and adds the first extract record to it. In each subsequent EXTRACT statement, the new extract record is added to the dataset EXTRACT HEADER. When you extract the data, the record is filled with the current values of the corresponding fields. As soon as the system has processed the first EXTRACT statement for a field group , the structure of the corresponding extract record in the extract dataset is fixed. You can no longer insert new fields into the field groups and HEADER. If you try to modify one of the field groups afterwards and use it in another EXTRACT statement, a runtime error occurs. By processing EXTRACT statements several times using different field groups, you fill the extract dataset with records of different length and structure. Since you can modify field groups dynamically up to their first usage in an EXTRACT statement, extract datasets provide the advantage that you need not determine the structure at the beginning of the program.

5. Can a transparent table exist in data dictionary but not in the data base physically?

NO. TRANSPARENT TABLE DO EXIST WITH THE SAME STRUCTURE BOTH IN THE DICTIONARY AS WELL AS IN THE DATABASE,EXACTLY WITH THE SAME DATA AND FIELDS.

6. What is the step by step process to create a table in data dictionary?

step 1: creating domains(data type, field length, range).
step 2: creating data elements(properties and type for a table field).
step 3: creating tables(SE11).

7. What is the typical structure of an ABAP/4 program?

HEADER ,BODY,FOOTER.

8. A situation: An ABAP program creates a batch input session. We need to submit the program and the batch session in back ground. How to do it?

go to SM36 and create background job by giving job name, job class and job steps (JOB SCHEDULING)

9. What are the domains and data elements?

DOMAINS : FORMAL DEFINITION OF THE DATA TYPES.THEY SET ATTRIBUTES SUCH AS DATA TYPE,LENGTH,RANGE. DATA ELEMENT : A FIELD IN R/3 SYSTEM IS A DATA ELEMENT.

10. What is the alternative to batch input session?

Call transaction.

11. What is a batch input session?

BATCH INPUT SESSION is an intermediate step between internal table and database table. Data along with the action is stored in session ie data for screen fields, to which screen it is passed, program name behind it, and how next screen is processed.

12. Describe the nature of the relationship between SAP and D&B?

D&B and SAP have been Global Partners for over a year. D&B are mySAP.com Software Alliance Partners. The joint offering, D&B for SAP R/3, delivered as part of mySAP.com, seamlessly integrates the D&B for SAP R/3 solution and D&B business information for enhanced decision support. D&B also offers a Data Rationalization Service for SAP customers to consolidate, cleanse and eliminate redundant customer and vendor records that originate from a wide variety of sources and multiple formats.

13. Is the relationship between D&B and SAP exclusive to either party?

No, the partnership agreement is not exclusive. It allows D&B to work with other application software providers and SAP to team with other content providers.

How do you start and stop MySQL on Windows? - net start MySQL, net stop MySQL
How do you start MySQL on Linux? - /etc/init.d/mysql start
Explain the difference between mysql and mysqli interfaces in PHP? - mysqli is the object-oriented version of mysql library functions.
What’s the default port for MySQL Server? - 3306
What does tee command [...]

What is DDL, DML and DCL? - If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. [...]

Explain MySQL architecture. - The front layer takes care of network connections and security authentications, the middle layer does the SQL query parsing, and then the query is handled off to the storage engine. A storage engine could be either a default one supplied with MySQL (MyISAM) or a commercial one supplied by a third-party [...]

1. How would you determine the time zone under which a database was operating?
2. Explain the use of setting GLOBAL_NAMES equal to TRUE.
3. What command would you use to encrypt a PL/SQL application?
4. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
5. Explain the use of table functions.
6. Name three advisory statistics you can collect.
7. Where in the Oracle directory tree structure are audit traces placed?
8. Explain materialized views and how they are used.
9. When a user process fails, what background process cleans up after it?
10. What background process refreshes materialized views?
11. How would you determine what sessions are connected and what resources they are waiting for?
12. Describe what redo logs are.
13. How would you force a log switch?
14. Give two methods you could use to determine what DDL changes have been made.
15. What does coalescing a tablespace do?
16. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
17. Name a tablespace automatically created when you create a database.
18. When creating a user, what permissions must you grant to allow them to connect to the database?
19. How do you add a data file to a tablespace?
20. How do you resize a data file?
21. What view would you use to look at the size of a data file?
22. What view would you use to determine free space in a tablespace?
23. How would you determine who has added a row to a table?
24. How can you rebuild an index?
25. Explain what partitioning is and what its benefit is.
26. You have just compiled a PL/SQL package but got errors, how would you view the errors?
27. How can you gather statistics on a table?
28. How can you enable a trace for a session?
29. What is the difference between the SQL*Loader and IMPORT utilities?
30. Name two files used for network connection to a database.

What is an UTL_FILE.What are different procedures and functions associated with it?

UTL_FILE is a package that adds the ability to read and write to operating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.

Can you use a commit statement within a database trigger.

No

What is the maximum buffer size that can be specified using the DBMS_OUTPUT. ENABLE function?

1,000,000

What is use of a cursor variable? How it is defined.

A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C). Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.

What should be the return type for a cursor variable.Can we use a scalar data type as return type?

The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE

How you open and close a cursor variable.Why it is required?

OPEN cursor variable FOR SELECT…Statement CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used.In order to free the resources used for the query CLOSE statement is used.

How you were passing cursor variables in PL/SQL 2.2?

In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2,the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

Can cursor variables be stored in PL/SQL tables.If yes how.If not why.

No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.

Difference between procedure and function.

Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

What are different modes of parameters used in functions and procedures.

IN
OUT
INOUT

What is difference between a formal and an actual parameter

The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

Can the default values be assigned to actual parameters.

Yes

Can a function take OUT parameters.If not why.

No.A function has to return a value,an OUT parameter cannot return a value.

What is syntax for dropping a procedure and a function .Are these operations possible.

Drop Procedure procedure_name Drop Function function_name

What are ORACLE PRECOMPILERS.

Using ORACLE PRECOMPILERS ,SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,… This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to creater an executable.

What is OCI. What are its uses.

Oracle Call Interface is a method of accesing database from a 3GL program. Uses–No precompiler is required,PL/SQL blocks are executed like other DML statements. The OCI library provides
-functions to parse SQL statements
-bind input variables
-bind output variables
-execute statements
-fetch the results

Difference between database triggers and form triggers.

a) Data base trigger(DBT) fires when a DML operation is performed on a data base table.Form trigger(FT) Fires when user presses a key or navigates between fields on the screen
b) Can be row level or statement level No distinction between row level and statement level.
c) Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.
d) Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger.
e) Can cause other database triggers to fire.Can cause other database triggers to fire,but not other form triggers.

There is a string ‘120000 12 0 .125′ ,how you will find the position of the decimal place

INSTR(’120000 12 0 .125′,1,’.') output 13

There is a ‘%’ sign in one field of a column. What will be the query to find it.

‘\’ Should be used before ‘%’.

When you use WHERE clause and when you use HAVING clause

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

Which is more faster - IN or EXISTS

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

What is a OUTER JOIN

Outer Join–Its a join condition used where you can query all the rows of one of the tables in the join condition even though they dont satisfy the join condition.

How you will avoid your query from using indexes

SELECT * FROM emp Where emp_no+’ ‘=12345; i.e you have to concatenate the column name with space within codes in the where condition. SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234; i.e using HINTS

What is a pseudo column. Give some examples

It is a column that is not an actual column in the table. eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL. Suppose customer table is there having different columns like customer no, payments.What will be the query to select top three max payments. SELECT customer_no, payments from customer C1 WHERE 3<=(SELECT COUNT(*) from customer C2 WHERE C1.payment <= C2.payment)

What is the purpose of a cluster.

Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.

What is a cursor.

Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

Difference between an implicit & an explicit cursor.

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.

Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR…IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

What are cursor attributes

%ROWCOUNT
%NOTFOUND
%FOUND
%ISOPEN

What is a cursor for loop.

Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor’s record.

Difference between NO DATA FOUND and %NOTFOUND

NO DATA FOUND is an exception raised only for the SELECT….INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.

What a SELECT FOR UPDATE cursor represent.

SELECT……FROM……FOR……UPDATE[OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.