What is a sub query? What are its various types?

Correct Answer: A synonym can be called as an alias for a table, view, sequence or program unit It is basically of two types: - Private - Only the owner can access it - Public - Can be accessed by any database user

Correct Answer: - These are the Logical Storage Units into which a database is divided - It is used to group together the related logical structures

Correct Answer: It is virtual table which is defined as a stored procedure based on one or more tables

Correct Answer: - Rename : It is a permanent name provided to a table or column - Alias : It is a temporary name provided to a table or column which gets over after the execution of SQL statement

Correct Answer: - TABLES - VIEWS - INDEXES - SYNONYMS - SEQUENCES - TABLESPACES

Correct Answer: Oracle database comprises of three kinds of files: - Datafiles - Redo log files - Control files

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