Frequently Asked Structured Query Report Interview Questions
Q – 1 Tell me What is search Record?
How to create SQC?
How to connect other application in component interface?
Ans- Search record is a record that has at least one search key. Sqc is nothing but sqr program , but the saving convention is sqc. By using Keys, methods, and properties we can connect other application in ci.
Q – 2 Explain the difference between Load Look up and Array?
Ans- Load Lookup is essentailly an array which acts in the form of a table. Load Lookup is used to reduce the complexity of joins – it populates the values of a certain field depending on the ke field specified from a certain table. Then the users can query from the preloaded lookup table instead of joining tables.
While arrays are used to store and retrieve data using the get and put commands
Apart from above mentioned advantages,Load Lookup increases the performance as it loads an array into memory with key and return value.
Q – 3 What is Performance tuning of SQR?
Ans- Use Dynamic SQL, Load Lookup.
Q – 4 Can you explain Outer join, inner join in sql?
Ans- A JOIN is a means for combining fields from two tables by using values common to each.
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record?even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).
(In this case left and right refer to the two sides of the JOIN keyword.)
No implicit join-notation for outer joins exists in standard SQL.
Left outer join:
The result of a left outer join (or simply left join) for table A and B always contains all records of the “left” table (A), even if the join-condition does not find any matching record in the “right” table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result?but with NULL in each column from B.
This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table.
For example, this allows us to find an employee’s department, but still shows the employee(s) even when they have not been assigned to a department (contrary to the inner-join example above, where unassigned employees are excluded from the result).
Example of a left outer join:
SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
Right outer joins:
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the “right” table (B) will appear in the joined table at least once. If no matching row from the “left” table (A) exists, NULL will appear in columns from A for those records that have no match in B.
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
For example, this allows us to find each employee and his or her department, but still show departments that have no employees.
Example right outer join:
SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
Inner Join:
An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.
When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) – then return all records which satisfy the join predicate.
Actual SQL implementations normally use other approaches like a Hash join or a Sort-merge join where possible, since computing the Cartesian product is very inefficient.
SQL specifies two different syntactical ways to express joins: “explicit join notation” and “implicit join notation”.
The “explicit join notation” uses the JOIN keyword to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
Q – 5 How to generate tow output files from one SQR?
Ans- Using create report you can create multiple reports in one single SQR report to create multiple files from one sqr,we have to first initialise them with different names and while writing call the appropriate files and load them.by this way the file gets generated in the specified path
Q – 6 How to decide between appeng/Ci and SQR while conversion?
Ans- If data needs to be validated well before updating records and to make parent child records synchronous with respect to data, use AE/CI.
When there is huge data to be manipulated, better use SQRs (run on server if possible).
Q – 7 Suppose We wish to print the EMPLID in the BARCODE Format. What are the commands we have to be used in SQR?
Ans- PRINT-BAR-CODE
Q – 8 Explain the difference in using inner join and outer joins?
Ans-
Inner join :
An inner join (sometimes called a “simple join”) is a join of two or more tables that returns only those rows that satisfy the join condition.
Outer Joins :
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition
Q – 9 What is Normalization in oracle?
Ans- Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency.
Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.
Q – 10 Explain difference between BI, CI, IB?
Ans-
BI – Business Inerlink –
This is peoplesoft object that behave like DLL. If you have to modify something on other application, like some of directory that controls single sign on Business Interliks are used.
CI: Component Interface –
This is used to validate any entries in peoplesoft system. say some third party system sent one inbound file and based on the data in inbound file you want to insert row in peoplesoft, you also want the current validation at component (say xyz) to take place. Then create a component interface with xyz component. read the inbound and process it through newly build CI.
IB – Inegraion Broker –
This is integration point of peoplesoft with all other peoplesoft and thirdparty application that uses Messaging.
Q – 11 Can you explain What are the compulsary SQCs that should be used in SQR Program?
Ans- Setenv.sqc,setup02.sqc,prcsapi.sqc,prcsdef.sqc,curdttim.sqc,datetime.sqc are some of the SQC’s that should be present.
Q – 12 Any Experience in UNix?
Ans- Tell if you have any experience in Unix
Q – 13 How to send emails using SQR?
Ans- using call system command
Q – 14 Explain Exp in Object oriented prog?
Ans- Object-oriented programming (OOP) is a programming paradigm that uses “objects” ? data structures consisting of datafields and methods together with their interactions ? to design applications and computer programs. Programming techniques may include features such as data abstraction, encapsulation, modularity, polymorphism, and inheritance.
It was not commonly used in mainstream software application development until the early 1990s.Many modern programming languages now support OOP.Ex.,C++,Java etc.,
Q – 15 Can you explain your experience with CI?
Ans-
1. Mainly for upload the data from the flatfile to peoplesoft application tables.
2. It will high-level of validation before updating data in the database.
3. If we want to upload data into parent-child tables, CI is best.
4. While expose CI from one component please check whether this component have level0 design or not. If it is, then do it otherwise no use to create CI.
Q – 16 Tell me Did you worked with functions, procedures in SQR?
Ans- Yes. It is similar to use function, procedure and packages in SQR. The only difference is – Use begin-Sql Begin procedure(param1,param2) End End-Sql