Explain unique Constraint.

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: Oracle NOT NULL is used on a column to ensure that the value for that column can never be NULL Example: Below, the constraint is that the id should never be NULL If it is, oracle throws an error create table employee ( id number NOT NULL, Name varchar(200) );

Correct Answer: Oracle check constraint is used to ensure that before inserting the data in the database, it is validated and checked for the condition Example: Below, the constraint is that the id has to be between 0 and 1000 create table employee ( id number check (id between 0 and 1000), Name varchar(200) );

Correct Answer: Oracle constraints are used to maintain consistent of data and ensure the data is properly maintained A constraint is more or less a restriction we try to apply on a table Types of constraints: - Check constraints - NOT NULL constraint - PRIMARY KEY constraint - REFERENCES constraint - UNIQUE constraint

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: 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: 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: 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