Define PL/SQL. Explain its purpose

Correct Answer: In a correlated query, the outer query is related to the inner query This means one or more columns in the outer query are referenced It?s used when the outer queries value is being used by inner query For example, we need to find which employee had more perks in the current month than they did in the previous month The correlated subquery is executed for each row of perks information in the parent query to first determine what the perks were for each employee in the previous month This data, in turn, is compared to perks for each employee in the current month, and only those employees whose perks in the current month were greater that their previous month's perks are returned

Correct Answer: Joins are used to combine data of one or more tables Joins should be used when there is abundant data Joins can be LEFT, RIGHT, OUTER, INNER or even SELF JOIN The purpose is to bind data from multiple tables without any receptivity

Correct Answer: The number of rows returned by a select query can be restricted by the LIMIT clause Example: SELECT * from employee LIMIT 20

Correct Answer: A unique constraint on a column uniquely identifies the record by a combination of one or more fields Few unique constraint fields can have a NULL value as long as the combination of values is unique Example: create table employee ( id number NOT NULL, dob DATE, professor_id NOT NULL, Name varchar(200) Constraint id_unique UNIQUE(id,dob) );

Correct Answer: A foreign key is a reference to another table It is used to establish relationships between tables For example, relationship between employee and professor table One employee can have multiple professors The Primary key of employee becomes foreign key of professor Example: create table employee ( id number NOT NULL, professor_id NOT NULL, Name varchar(200) Constraint prim_id Foreign key(id) references professor(professor_id) );

Correct Answer: - Because of the block nature, multiple statements are processed at once thereby improving performance - PL/SQL handles exceptions on catching which, action can be taken - PL/SQL is highly portable as it works with all procedural languages and is highly secured because of privileges

Correct Answer: SQL is a structured query language while PL/SQL is an extension of SQL by introducing a procedural flow PL/SQL has blocks of statements PL/SQL works like other procedural languages and has concepts like control statements, sequential statements, exception handling etc

Correct Answer: Even though both PL/SQL and Java (or) NET can be used, PL/SQL stands above these two in terms of integration overhead This is because Java is an open source proprietary and Data manipulation is slightly faster in PL/SQL than in Java

Correct Answer: One can debug PL/SQL program by printing the output using DBMS_OUTPUT package Put_line can be used to display a line as shown below: BEGIN dbms_outputput_line(?Sample line'); END;

Correct Answer: The UTL_FILE database package can be used to read and write operating system files You need to have read /write access rights in that directory before the package can be used Example to write file: Fhandler is a variable of type UTL_FILEFILE_TYPE UTL_FILEPUTF(fHandler, 'Im writing to a file\n'); Example to read file: UTL_FILEGET_LINE(fHandler, buf);