Question 1

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

Accepted Answer

- Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables - A sub query is added in the where clause of the main query There can be two types of subqueries: a) Correlated sub query : - It can reference column in a table listed in the from list of the outer query but is not as independent as a query b) Non-Correlated sub query : - Results of this sub query are submitted to the main query or parent query - It is independent like a query

Question 2

What are the varoius components of physical database structure of Oracle database?

Accepted Answer

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

Question 3

What do you mean by Redo Log file mirroring ?

Accepted 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

Question 4

Explain the difference between trigger and stored procedure.

Accepted 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

Question 5

Explain Row level and statement level trigger.

Accepted 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

Question 6

What are aggregate functions in SQL? What are those functions?

Accepted 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

Question 7

What are Number Functions in SQL?

Accepted 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

Question 8

What are the basic SQL*Plus commands?

Accepted 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

Question 9

What is AFIEDT.BUF?

Accepted 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

Question 10

How to Add a new column to an existing table?

Accepted Answer

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

Question 11

How to recover a dropped table?

Accepted 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

Question 12

What are the types of constraints avaialable in oracle?

Accepted 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

Question 13

Explain Check constraint.

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

Question 14

Explain Not Null constraint.

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

Question 15

Explain primary key constraint.

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

Question 16

Explain foreign key constraint.

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

Question 17

Explain unique Constraint.

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

Question 18

Explain how to limit the rows that are retrieved by a query.

Accepted Answer

The number of rows returned by a select query can be restricted by the LIMIT clause Example: SELECT * from employee LIMIT 20

Question 19

What are joins? Explain its characteristic features .

Accepted 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

Question 20

What is correlated query? Explain its uses.

Accepted 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

Technology Questions