What is correlated query? Explain its uses.

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: Primary key constraint ensures that the column(s) always has a unique value to identify the record Example: Below, the primary key is created for column id with name prim_id create table employee ( id number NOT NULL, Name varchar(200) Constraint prim_id primary key(id) );

Correct Answer: PL/SQL is Procedural Language SQL that is an extension of SQL that results in a more structural language composed of blocks It is mainly used in writing applications that needs to be structured and has error handling

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;