What are the basic SQL*Plus commands?

Correct Answer: - ABS(number) Returns the absolute positive value of an expression Syntax: ABS(expression) Example: SELECT ABS(-10), ABS(00), ABS(10) Output: 10 0 10 - CEIL(number) Returns the smallest integer greater than, or equal to, the specified numeric expression Syntax: CEILING(expression) Example: SELECT CEILING($22345), CEILING($-22345), CEILING($00) Output: 22400 -22300 000 - FLOOR(number) Returns the largest integer less than, or equal to, the specified numeric expression Syntax: FLOOR(expression) Example: SELECT FLOOR($22345), CEILING($-22345), CEILING($00) Output: 22300 -22400 000 - MOD(number, divisor) Returns the remainder of the division from 2 integer values Syntax: MOD(dividend, divisor) Example: SELECT MOD(20,3) Output: 2 - POWER(number, power) Returns the exponential value for the numeric expression Syntax: POWER(number, power) Example: SELECT POWER(20, 30) Output: 80 - SIGN(number) Returns the sign ie positive or negative value for the numeric expression It returns -1 for negative expressions, a value of 0 for zero Syntax: SIGN(number) Example: SELECT SIGN(4) Output: 1 - ROUND(number, precision) Returns the numeric value rounded off to the next value specified Syntax: ROUND(number, number of places) Example: SELECT ROUND(13456, 2) - SQRT(number) Returns the square root value of the expression Syntax: SQRT(number) Example: SELECT SQRT(40) Output: 20 - TRUNC(number, precision) Returns a numeric value that truncate to the specific places Syntax: TRUNCATE(number,places) Example: SELECT TRUNCATE(13456, 2) Output: 134

Correct Answer: Aggregate functions in SQL are used to perform calculation on data These functions are inbuilt in SQL and return a single value SUM( ) SUM function returns the sum or addition of all NOT NULL values of a column For eg I have a Table employee with the fields id, name, salary and I want the sum of all salaries, I can use SUM function as shown SELECT SUM(emp_salary) from employee; Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 63,000 AVG( ) AVG function returns the average of all NOT NULL values of a column For eg I have a Table employee with the fields id, name, salary and I want the average of all salaries, I can use AVG function as shown SELECT AVG(emp_salary) from employee; Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 21,000 COUNT( ) COUNT function returns the number of rows or values of a table For eg I have a Table employee with the fields id, name, salary and I want the count of all rows, I can use COUNT function as shown SELECT COUNT(*) from employee; Max ( ) and Min ( ) MAX function returns the largest value of a column in a table For eg I have a Table employee with the fields id, name, salary and I want the maximum salary of an employee, I can use MAX function as shown SELECT MAX(emp_salary) from employee; Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 22,000 MIN function returns the smallest value of a column in a table For eg I have a Table employee with the fields id, name, salary and I want the minimun salary of an employee, I can use MIN function as shown SELECT MIN(emp_salary) from employee; Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 20,000

Correct Answer: Row Level Trigger : Row Level Trigger is fired each time row is affected by Insert, Update or Delete command If statement doesn?t affect any row, no trigger action happens Statement Level Trigger : This kind of trigger fires when a SQL statement affects the rows of the table The trigger activates and performs its activity irrespective of number of rows affected due to SQL statement

Correct Answer: - A stored procedure can accept parameters while a trigger cannot - A trigger can?t return any value while stored procedures can - A trigger is executed automatically on some event while a stored procedure needs to be explicitly called - Triggers are used for insertions, update and deletions on tables while stored procedures are often using independently in the database - A trigger cannot be written in a stored procedure However, the reverse is not possible

Correct Answer: - The process of having a copy of redo log files is called mirroring - It is done by creating group of log files together This ensures that LGWR automatically writes them to all the members of the current on-line redo log group - In case a group fails, the database automatically switches over to the next group It diminishes the performance

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: New column can be added using the ALTER TABLE command as shown below: Example: ALTER TABLE employee ADD DateOfBirth date

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