Explain Check constraint.

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: Dropped tables can be recovered using DROP TABLE flashback It works the way recycle bin works Example: FLASHBACK TABLE EMPLOYEE TO BEFORE DROP; The most recently dropped table with that original name is retrieved from the recycle bin, with its original name

Correct Answer: New column can be added using the ALTER TABLE command as shown below: Example: ALTER TABLE employee ADD DateOfBirth date

Correct Answer: When any file is attempted to edit without any arguments (example ed or edit), the last SQL command is saved in AFIEDTBUF, It acts like a default buffer file for edit commands

Correct Answer: 1) START- Used to run a SQL script 2) ACCEPT- Accepts input from user 3) GET- Gets the sql file from user to place in buffer 4) LIST- Displays the last command executed 5) RUN- Used to list and run the command in buffer 6) SHOW- Shows the environment settings Example: SHOW PAGESIZE

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: 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: 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: 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: The number of rows returned by a select query can be restricted by the LIMIT clause Example: SELECT * from employee LIMIT 20