How to recover a dropped table?

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

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