Oracle Interview Questions Set - 01
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
What are different Oracle database objects?
- Tables: This is a set of elements organized in a vertical and horizontal manner.
- Tablespaces: It is a logical storage unit in Oracle.
- Views: Views are a virtual table derived from one or more tables.
- Indexes: This is a performance tuning method to process the records.
- Synonyms: It is a name for tables.
What are different types of SQL commands in Oracle?
- DDL- DATA DEFINATION LANGUAGE – CREATE, ALTER , DROP, RENAME , TRUNCATE
- DML- DATA MANIPULATION LANGUAGE – INSERT, UPDATE , DELETE
- TCL- TRANSACTIONAL CONTROL LANGUAGE – COMMIT , ROLLBACK
- DQL- DATA QUERY LANGUAGE – SELECT
- DCL- DATA CONTROL LANGUAGE – GRANT, REVOKE
What is the difference between DDL, DML and TCL commands?
DDL is Data Definition Language statements. Some examples:
‣ CREATE – To create objects in the database
‣ ALTER – alters the structure of the database
‣ DROP – delete objects from the database
‣ TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
DML is Data Manipulation Language statements. Some examples:
‣ INSERT – insert data into a table
‣ UPDATE – updates existing data within a table
‣ DELETE – deletes all records from a table, the space for the records remains
DQL is Data Query Language statements.
SELECT – retrieve data from database
TCL is Transaction Control Language statements. Some examples:
COMMIT – save work done
SAVEPOINT – identify a point in a transaction to which you can later roll back
ROLLBACK – restore database to original since the last COMMIT
What is Data Definition Language?
Data definition language (DDL) statements define, alter the structure of, and drop schema objects. DDL statements enable you to:
‣ Create, alter, and drop schema objects and other database structures, including the database itself and database users (CREATE, ALTER, DROP)
‣ Change the names of schema objects (RENAME)
‣ Delete all the data in schema objects without removing the objects’ structure (TRUNCATE)
What is Data Manipulation Language?
DML Statement are one category of SQL statement such as Select, Insert, Delete and Update query and update the actual data.
Data manipulation means
‣ Retrieval of information stored in the database
‣ Insertion of new information into the database
‣ Deletion of information from the database
‣ Modification of information from the database
A DML is a language that enables users to access or manipulate data as organised by the appropriate data mode.
What is Rollback?
Part or all of a transaction can be undone with the ROLLBACK statement. An automatic rollback occurs whenever a transaction is interrupted such as by an execution error, a power failure, update or delete statement but any that have occurred since the last commit.
When a ROLLBACK statement is issued to the database the transaction is ended and
‣ All work done by the transaction is undone as if it had not been issued.
‣ Any lock, acquired by the transaction are released.
What is COMMIT?
In a transaction a set of changes are made permanent with the commit statement.
When a COMMIT statement is issued to the database the transaction is ended and
‣ All work done by the transaction is made permanent
‣ Other sessions can see the changes made by this transaction.
‣ Any locks by the transaction are released.
Syntax:
COMMIT;
What is the difference between ‘DELETE’ and ‘TRUNCATE’ command?
DELETE is used to delete the rows from a table. However, it retains the rows to be deleted in redo log buffers and if ROLLBACK is issued, the rows restored back into the table. Only when COMMIT is executed after DELETE, the rows are permanently deleted.
TRUNCATE requires no COMMIT command to delete the rows. As soon as TRUNCATE is fired, rows are permanently deleted. Thats why it’s also called as DDL statement. TRUNCATE causes the movement of High-Water Mark.
TRUNCATE is faster than DELETE since TRUNCATE do not requires to hold the rows in redo log buffers.
Differentiate between VARCHAR and VARCHAR2 data types.
| Varchar | Varchar2 |
|---|---|
| Can store characters up to 2000 bytes | can store characters up to 4000 bytes |
| It will hold the space for characters defined during declaration, even if all of them are not used | It will release the unused space |
Difference between Decode and Case function in Oracle SQL
Both these statements Decode and Case will work similar to the if-then-else statement and also they are the alternatives for each of them.
These functions are used in Oracle for data value transformation.
Example: Decode function
Select OrderNum ,
DECODE (
Status,'O','Ordered',
'P','Packed',
'S','Shipped',
'A','Arrived'
)
FROM Orders;
Example: Case function
Select OrderNum ,
Case
( When Status='O' then 'Ordered'
When Status ='P' then Packed
When Status='S' then 'Shipped'
else 'Arrived'
)end
FROM Orders;
Both these commands will display Order Numbers with their respective Statuses like this,
Status O= Ordered
Status P= Packed
Status S= Shipped
Status A= Arrived
How do we use the DISTINCT statement? What is its use?
The SQL DISTINCT keyword is combined with the SELECT query to remove all duplicate records and return only unique records. There may be times when a table has several duplicate records.
The DISTINCT clause in SQL is used to eliminate duplicates from a SELECT statement’s result set.
What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?
BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.
Example of BETWEEN:
SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;
Example of IN:
SELECT * FROM Students where ROLL_NO IN (8,15,25);
List some case manipulation functions in SQL?
There are three case manipulation functions in SQL, namely:
LOWER: This function returns the string in lowercase. It takes a string as an argument and returns it by converting it into lower case.
Syntax:
LOWER(‘Hello World’)
UPPER: This function returns the string in uppercase. It takes a string as an argument and returns it by converting it into uppercase.
Syntax:
UPPER(‘Hello World’)
INITCAP: This function returns the string with the first letter in uppercase and rest of the letters in lowercase.
Syntax:
INITCAP(‘Hello World’)
How can you select unique records from a table?
You can select unique records from a table by using the DISTINCT keyword.
Using this command, it will print unique Student ID from the table Student.
Select DISTINCT studentID from Students;
How can you fetch first 5 characters of the string?
There are a lot of ways to fetch characters from a string.
For example:
Select SUBSTRING(StudentName,1,5) as studentname from Students;
What are Different Types of Constraints in Oracle?
NOT NULL Constraint:
1. Column cannot contain null value.
2. Will accept duplicates.
UNIQUE Constraint:
1. Column should have unique value.
2. Should not contain any duplicates.
3. Will accept nulls.
PRIMARY KEY:
1. Will not allow nulls.
2. Will not allow duplicates.
3. To give a relationship between two or more tables.
4. Table can have only one primary key.
5. Two or more columns can contain a single primary key.
6. The combination of columns in a pk should be unique.
7. This kind of primary key is called composite key or candidate key or natural key.
FOREIGN KEY (Referential integrity):
1. You can give relationship between two or more table using pk and foreign key.
2. Foreign key always refers to pk of another table or same table.
3. Without primary key, you cannot create foreign key.
4. Primary key and foreign key column datatype should be same.
5. The values which are present in primary key, only those values should be there in the foreign key.
6. The values in the foreign key can be repetitive, can contain nulls also.
7. If you have a primary key and foreign key relationship between the tables, if you want to drop any table, you have to drop the child table first, then you should drop parent table.
CHECK:
The CHECK constraint is used to limit the value range that can be placed in a column.
1. If you define a CHECK constraint on a column it will allow only certain values for this column.
2. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
Can a table contain more than one primary key?
No. Table can have only one primary key, but two or more columns can contain a single primary key also called as a Composite key.
What is a Primary key, Unique key, & Foreign key in SQL?
Primary key in SQL is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table. Null values not allowed.
Unique Key is Uniquely identifies a single row in the table. Unique key will allow Nulls, but it will not allow duplicates
Foreign key maintains referential integrity by enforcing a link between the data in two tables. The foreign key in the child table references the primary key in the parent table.
What is referential integrity?
Referential integrity refers to the relationship between tables. Because each table in a database must have a primary key, this primary key can appear in other tables because of its relationship to data within those tables. When a primary key from one table appears in another table, it is called a foreign key.
If I have Primary key and foreign key relationship between two tables, which table should we Drop first?
First, we need to drop Child Table (Which is having Foreign key), after that we need to drop parent table (which is having Primary key).
How can you insert NULL values in a column while inserting the data?
NULL values in SQL can be inserted in the following ways:
- Implicitly by omitting column from column list.
- Explicitly by specifying NULL keyword in the VALUES clause.
Difference Between Primary Key and Unique Key?
| Parameters | Primary Key | Unique Key |
|---|---|---|
| Basic | The primary key is accepted as a unique or solo identifier for every records in the table | When the primary key is not present in the table, the unique key is also used as a unique identifier of each records |
| NULL | In the case of a primary key, we cannot save NULL values. | In the case of unique key, we can save a null value. However, only one null value is supported |
| Purpose | It supports entire integrity | It supports unique data |
| Index | The primary key tends to generate a clustered index by default | The unique it ends to generate a non-clustered index |
| Number of Key | Each table holds just one primary key | A table can hold more than one unique |
| Value Modification | With the primary key, we cannot modify, modify or delete the values | With the unique key, we can modify the column values |
What is a surrogate key? How it is different from a natural key? What are the advantages of a surrogate key?
A surrogate key (also known as artificial or identity key) is a system generated key with no business value.
A surrogate key is a substitution for the natural primary key.
It is just a unique identifier or number for each row that can be used for the primary key to the table.
Data warehouses typically use a surrogate key for the dimension tables primary keys
Surrogate keys can be generated from Sequnce Generator Transformation in Informatica.
A natural key is a value that has a business meaning and people use them.
Ex: SSN, Customer Id
Advantages of Surrogate Keys:
▹ Natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult. And in many cases natural primary key is a combination of more than 1 columns. In such cases, the usage of surrogate key simplifies the design.
▹ Surrogate keys are numeric and hence indexing on them is faster.
▹ Proper tracking the slowly changing dimension. Ex, if an employee E1 belongs to business unit BU1 on 1st June 2009 but migrates to Business Unit BU2 on 1st November 2009. If natural key Employee_Id E1 is used, every other attribute will now be moved to BU2. In this case, if surrogate key is used, a new record can be created for E1 to show new data that belongs to BU2.
What is inline view?
An inline view is term given to sub query in FROM clause of query which can be used as table. Inline view effectively is a named sub query.
Example:
Select Tab1.col1, Tab1.col2, Inview.col1, Inview.Col2
From Tab1, (Select statement) Inview
Where Tab1.col1= Inview.col1
SELECT DNAME, ENAME, SAL FROM EMP,
(SELECT DNAME, DEPTNO FROM DEPT) D
WHERE A.DEPTNO = B.DEPTNO;
In the above query (SELECT DNAME, DEPTNO FROM DEPT) D is the inline view.
Inline views are determined at runtime, and in contrast to normal view they are not stored in the data dictionary,
Advantage of using inline views:
1. Better query performance
2. Better visibility of code
Practical use of Inline views:
1. Joining Grouped data with Non grouped data
2. Getting data to use in another query
What is a correlated subquery?
A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query.
Syntax:
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);
Example:
select
book_key,
store_key,
quantity
from
sales s
where
quantity =
(select max(quantity)
from sales
where book_key = s.book_key);
Example:
select
book_key,
store_key,
quantity
from
sales s
where
quantity =
(select max(quantity)
from sales
where book_key = s.book_key);
What is difference between Co-related sub query and nested sub query?
Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
Example,
Correlated Subquery:
select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno);
Nested Subquery:
select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno);
What are Different Types of joins available in Oracle?
▹ Equi Join or Inner join or Normal Join or Join
▹ Non-Equi Join
▹ Outer Join (Left Outer / Right Outer / Full Outer)
▹ Cross Join / Cartesian Product
▹ Self Join
What are joins in Oracle and What are the rules to use Joins in Oracle?
▹ We use joins to add the columns from more than one table.
▹ To get the results from two or more tables we will use joins.
▹ To join two or more tables, at least one common column with common data should be available in those tables.
▹ To join two or more tables, pk / fk relationship are not mandatory.
▹ Datatype should be same for the matching columns.
▹ To join n tables, at least n-1 joining conditions are required.
▹ You can join the tables from different schema also.
What is an Equijoin?
- An equijoin is a join with a join condition containing an equality operator.
- An equijoin combines rows that have equivalent values for the specified columns.
Example:
SELECT A.CUSTOMER_ID,A.NAME,A.ADDRESS,A.CREDIT_LIMIT,
B.ORDER_ID,B.CUSTOMER_ID,B.STATUS,B.SALESMAN_ID,B.ORDER_DATE
FROM CUSTOMERS A, ORDERS B
WHERE A.CUSTOMER_ID=B.CUSTOMER_ID;
What is Left outer Join in oracle?
Left outer join will give ALL THE RECORDS FROM Left TABLE, MATCHING RECORD FROM Right TABLE.
Example:
SELECT A.CUSTOMER_ID,A.NAME,A.ADDRESS,A.CREDIT_LIMIT,
B.ORDER_ID,B.CUSTOMER_ID,B.STATUS,B.SALESMAN_ID,B.ORDER_DATE
FROM CUSTOMERS A LEFT OUTER JOIN ORDERS B
ON A.CUSTOMER_ID=B.CUSTOMER_ID;
What is Right outer Join in oracle?
Right outer join will give ALL THE RECORDS FROM Right TABLE, MATCHING RECORD FROM Left TABLE.
Example:
SELECT A.CUSTOMER_ID,A.NAME,A.ADDRESS,A.CREDIT_LIMIT,
B.ORDER_ID,B.CUSTOMER_ID,B.STATUS,B.SALESMAN_ID,B.ORDER_DATE
FROM CUSTOMERS A Right OUTER JOIN ORDERS B
ON A.CUSTOMER_ID=B.CUSTOMER_ID;
What is FULL outer Join in oracle?
Combination of Left and Right outer join is called Full outer join.
FULL outer join will give ALL THE RECORDS FROM Right table and Left table.
Example:
SELECT A.CUSTOMER_ID,A.NAME,A.ADDRESS,A.CREDIT_LIMIT,
B.ORDER_ID,B.CUSTOMER_ID,B.STATUS,B.SALESMAN_ID,B.ORDER_DATE
FROM CUSTOMERS A Full OUTER JOIN ORDERS B
ON A.CUSTOMER_ID=B.CUSTOMER_ID;
What is Non-Equi join in oracle SQL?
When we join two or more tables, without using equality operator ( < > <= >= != between) is called Non-Equi join.
Example: Write a query to get the salaries from employees table which are falling in between min and max salary of jobs table.
SELECT A.EMPLOYEE_ID, A.FIRST_NAME , A.SALARY,
B.MIN_SALARY, B.MAX_SALARY
FROM HR.EMPLOYEES A , HR.JOBS B
WHERE A.SALARY BETWEEN B.MIN_SALARY AND B.MAX_SALARY;
What is Self Join?
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.
Example:
SELECT A.FIRST_NAME ,B.FIRST_NAME
FROM employees A, employees B
WHERE A.manager_id = B.employee_id;
What is Cross Join or Cartesian Product?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyse each product at each price.
Example:
SELECT A.*, B.*
FROM EMPLOYEES A CROSS JOIN DEPARTMENTS B;
What is view in oracle and what are advantages of views in oracle?
▹ View is actually a virtual table that is not physically stored in the database and does not store any sort of data.
▹ It is created generally by using join query with two or more tables which enables the view to hide the data complexity.
▹ It will also provide security, since it is not storing any data in the view physically so it actually restricts the access of various columns of a table to the user of the database.
▹ Views are created by a query joining one or more tables. The view gives good performance to run complex queries or join queries.
▹ View will not occupies memory space.
3 Examples:
CREATE VIEW empview AS
SELECT * FROM employees WHERE employees.manager_id = 100;
CREATE VIEW employeeview1 AS
SELECT employee_id, first_name || ' ' || last_name full_name, phone_NUMBER FROM employees;
CREATE OR REPLACE VIEW dept_manger AS
SELECT employee_id,first_name || ' ' || last_name as name, department_name FROM EMPLOYEES
INNER JOIN DEPARTMENTS on EMPLOYEES. EMPLOYEE_ID = DEPARTMENTS. MANAGER_ID;
What are the types of views?
There are two types of views:
▹ Simple views
▹ Complex views
What are Disadvantages of Views in an oracle?
▹ DML operations cannot perform in view.
▹ The view becomes inactive if the table is dropped on which it is created.
What is Materialized view?
▹ Materialized views are schema objects that can be used to summarize, Precompute, replicate, and distribute data.
Ex: To construct a data warehouse.
▹ A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
▹ The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.
▹ You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
▹ Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.
▹ A materialized view log is a schema object that records changes to a master table’s data so that a materialized view defined on the master table can be refreshed incrementally.
Example:
CREATE MATERIALIZED VIEW mv_test
BUILD IMMEDIATE
REFRESH complete
ON demand AS
SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME
FROM EMP,DEPT
where EMP.DEPTNO= DEPT.DEPTNO;
Difference between View and Materialized View in database
▹ Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.
▹ When we create view using any table, ROWID of view is same as original table but in case of Materialized view ROWID is different.
▹ In case of View, we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
▹ Performance of View is less than Materialized view.
▹ In case of view it’s only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table.
▹ In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.
What is Index?
▹ An index is a database structure that provides quick access of data in a column or columns of a table.
▹ Indexes are used in Oracle to provide quick access to rows in a table.
▹ Indexes provide faster access to data for operations that return a small portion of a table’s rows.
▹ Indexes are used to search the rows in the oracle table quickly.
By Default, Oracle creates B-Tree Indexes.
What are Advantages of Indexes in Oracle?
▹ Indexes Avoid Full Table Scan and also used to avoid a table access altogether.
▹ Indexes Speed up Select. Their use in queries usually results in much better performance.
▹ They can be used for sorting. A post-fetch-sort operation can be eliminated.
What are Disadvantages of Indexes in Oracle?
- Indexes slow down INSERT, UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field.
- INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.
- Indexes take additional disk space. Space increases with the number of fields used and the length of the fields.
What are types of Indexes?
There are following types of SQL Indexes:
1. Normal index
2. Unique Index
3. Bit Map Index
4. Composite Index
5. B-Tree Index (Oracle considered Normal indexes as B-Tree Indexes)
6.Function Based Index
7.Clustered Index
8.Non-Clustered Index.
Thank you for sharing your knowledge sir, very helpful to face the interview.
Hey Sir, I have read all oracle commands and understood very well and its clear cut explanation, its Extremely useful for us sir. thank you for stuff.
Hey Sir, I have read all oracle commands and understood very well and its clear cut explanation, its Extremely useful for us sir. thank you for stuff.
Hello Raj sir..thank you for your Excellent Training and Motivating every student to get a job, not only training and providing them Interview Questions and Answers and clarifying there doubts.
Thank you very much sir…
Leave a comment