What are different types Do Select?

1)      Select/Fetch

2)      Reselect

3)      Restart able

Select/Fetch: -

 Opens the cursor only at the first time and retrieve rows one at loop.

 Commits inside the step (commits in the Called Section) are ignored if AE is Restart enabled.

Reselect: -

It opens the cursor and closes the cursor on each iteration of the loop.

It will reselect the same row of data.   

Logic in Actions of the step should be such that it will be changing the status of the rows in the table the do select is selecting.

Commits are not ignored and will be committed in a reselect loop when the restart is enabled.

Restart able: - similar to select/Fetch but it WILL COMMIT inside the loop thus allowing the checkpoint to the PS_AERUNCONTROL table.

What are the options for using SQL in people code?

         a) sqlexec

         b) Record class methods (selectbykey,delete,insert,update)

         c) Using sql class, its properties and methods.

What is the difference in using SQL directly and calling SQL from Peoplecode in Application Engine?

If you use SQL directly then it will go to database directly to fetch rows of data. But if u write SQL'S in peoplecode, den the trip will be from application server to database because peoplecode resides in app server.

If we use SQL and Call section at same time then Database deadlock will occur. Why does this Dead lock Occur?

When a SQL is run, there is a lock on the table by the database until that step is completed. If during that time there runs some other process which will use the same table locked by our section using call section, this process will fail.

What is the diffrence between SQLExec and CreateSQL?

Sqlexec is used to select, insert, update or delete a single record using a sql statement. Sqlexec executes the sql command from within peoplecode bypassing the component processor and interacts directly with database server.


But in the case of Create SQL we can able insert the data in bulk. If you want to select, insert, update or delete a series of record then use getsql or createsql and then execute sql class method.

Briefly explain the difference between first, second and third normal forms?

First NF---Attribute should be atomic an no redundant row. (functional dependency)

Second Normal Form : All Non-Key attribute should be fully functionally dependent on primary  key.(removes partial dependency) non key attribute should be fully dependent on the key attribute.


Third normal Form : There is no transitivity dependency between attribute

Difference between sql statements and metasql statements?

SQL statements:

The SQl statements are used to fetch/retrieve, update, insert, delete data from the database. In PeopleSoft we use the sql stmts using sqlexec or createsql. Differnet sql statements are:
Select, Insert, Update, Delete.

Meta Sqls:
Different RDBMS have different date and time formats. Though the component processor takes care of the automatic conversion but at the time of using the sqlexec the automatic conversion does not take place. So in order to understand this, the meta sql concept has come. Which we can use in diff sql stmts. for example %datetimein, %date, %time, %currentdatetime, %datetimeout etc.

%dateIn: This is used in the where clause of "Select and update"
%dateout: This is used while selecting the data.

SQL Statements are Platform Dependent where as Meta-SQL statements are Platform Independent

What is left join, right join? (Learn all joins)

Suppose if you have table-A (Left Table) & Table-B(Right Table).

Left Join: Merging the values of Table-A & Table-B of common fields between them & also other values of Left Table (Table-A).

Right Join: Merging the values of Table-A & Table-B of common fields between them & also other values of Right Table (Table-B). 

What is the difference in using SQL directly and calling SQL from PEOPLECODE in Application Engine?

If you use SQL directly then it will go to database directly to fetch rows of data. But if you write SQL'S in PEOPLECODE, den the trip will be from application server to database because PEOPLECODE resides in application server. 

One should avoid writing SQL'S in App engine PEOPLECODE as it would degrade the performance. 

What is the difference between delete,drop and truncate?

Drop deletes the table and its structure from the data base. It cannot be rolled back

Truncate will also delete the records but it will not delete the table space which is created by the data base. It cannot be rolled back

Delete command used for deleting the records from the table, and it removes the table space which is allocated by the data base. It can be rolled back

What is view? Types of views?use of views?how to create view(syntax)?

View is the vertual or logical or the duplicate copy of the original table with schema parts only.

A view is a virtual table whose contents are defined by a query

View is used for the purposes as stated below:

a) Security
b) Faster Response
c) Complex Query solve
Create or replace view([olumn1],[column2]...)
as
Select column1,column2...
from table_name
[where condition]

[with read only],[with check option]

Can we update views.If not,why?

We can update view, if the view is created with one table.


But we cannot update view if the view is created with more than one table with multiple columns because view is single virtual table created multiple columns from different tables. so we cannot update this virtual table.

Different types of joins?

1. Self Join-when u need to join a table to itself

2 Equi Join:-The relation between the employee table and dept table is an equi join that both tables must be equal

3.Non equi Join:-The relationship is obtained using operator other than = operator.

4.Outer Join:-The missing rows can be obtained is outer join operation is used.It returns rows from one table that have no direct match in other table.

5.Cross Join:-produces cross product of two tables.

6.Natural Join;-It is based on all columns in two tables that have same name or same column.

7.Inner Join-To find out only matched columns.

8.Full outer join:-inner join+left outer join+right outer join