Oracle SQL Interview Questions and Answers

How does one eliminate duplicate rows from a table?

To remove duplicate rows of data, use the following statement: it will remove duplicate rows from a table and leaving only unique records in the table:

Delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
Post comment
Cancel
Thanks for your comment.!
Write a comment(Click here) ...

What is SQL?

Structured Query Language (SQL) is a language that provides an interface to relational database systems.
The proper pronunciation of SQL is "sequel". SQL was developed by IBM in the 1970.
Today, SQL is accepted as the standard RDBMS language.

Difference between TRUNCATE, DELETE and DROP commands?

DELETE command is used to remove some or all rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

DELETE will not free up used space within a table. This means that repeated DELETE commands will severely fragment the table and queries will have to navigate this "free space" in order to retrieve rows.

What the difference between UNION and UNION ALL?

Both UNION and UNION ALL is used to combine results of two SELECT queries.
The main difference between them is that, Union will remove the duplicate rows from the result set while Union all doesn't.
The performance of UNION ALL will be better than UNION, since UNION requires the server to do additional work of removing duplicates.

select * from T1
A
1
1
1
/*union example*/
select * from T1
union
select * from T1 
A
1
‚Äč/*union all example*/
select * from T1
union all
select * from T1
A
1
1
1
1
1
1

How to Change a SQL prompt name?

SQL> set prompt oracle
oracle> 

Find out nth highest salary from emp table?

With the help of correlated sub-query, row_number(), dense_rank() and rank() , we can find nth highest salary -

SELECT * FROM Employee order by 2
NAME    SALARY
Ram    3000
Tom    3000
Vinod    4000
Venky    5000
Manoj    7000

/*correlated sub-query example */
SELECT DISTINCT (a.salary)
  FROM Employee A
WHERE &N = (SELECT COUNT(DISTINCT(b.salary))
               FROM Employee B
              WHERE a.salary <= b.salary);

N= 2 -- to identify 2nd highest salary
SALARY
5000

/*row_number example */
SELECT DISTINCT (salary)
FROM (SELECT e.*, ROW_NUMBER() OVER(ORDER BY salary DESC) rn
FROM Employee e)
WHERE rn = &N;
N= 3 -- to identify 3rd highest salary
SALARY
4000

/*correlated sub-query example */
SELECT *
  FROM (SELECT  name,
               salary,
               DENSE_RANK() OVER(ORDER BY salary DESC) nth_highest_sal
          FROM Employee )
 WHERE nth_highest_sal = &n;

N= 3 -- to identify 3rd highest salary
SALARY
4000

/*rank example */
SELECT *
  FROM (SELECT  name,
               salary,
                RANK() OVER(ORDER BY salary DESC) nth_highest_sal
          FROM Employee )
 WHERE nth_highest_sal = &n;

N= 3 -- to identify 3rd highest salary
SALARY
4000

What is the difference between CHAR, VARCHAR and VARCHAR2 data types?

CHAR, VARCHAR and VARCHAR2 are used to store the character string values -

CHAR should be used for storing fixed length character strings. String values will be space/blank padded before stored on disk. CHAR(n) will ALWAYS be n bytes long. If the string length is <n, it will be blank padded upon insert to ensure a length of n.

VARCHAR Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself. VARCHAR2(n) will be 1 to n bytes long. If the string length is <n, it will not be blank padded.

What is difference between Co-related sub query and nested sub query?

Correlated sub query 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.

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 sub query runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

Select empname, basicsal, deptno
from emp
where (deptno, basicsal) in
(select deptno, max (basicsal)
from emp
group by deptno)

What are the more common pseudo-columns?

A pseudo-column behaves like a table column but it is not actual column of the table. We can select from pseudo-columns, but we cannot insert, update, or delete their values.

For example - SYSDATE, USER, UID, CURVAL, NEXTVAL, ROWID, ROWNUM, LEVEL

What is the difference between group by and order by?
Group by controls the presentation of the rows, Order by controls the presentation of the columns.

What keyword does an SQL SELECT statement use for a string search?
The LIKE keyword used for string searches. The % sign is used as a wildcard and _ for single.
What are various constraints / Integrity Constraints used in SQL?

Data integrity allows defining certain data quality requirements that the data in the database needs to meet.

Five type of constraints :-

Primary key uniquely identifies each record in the table.
Unique Key uniquely identifies each record in a database table. but may be null.
Foreign Key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data.
Not Null enforces a column to NOT accept NULL values
Check allows stating a minimum requirement for the value in a column.

What is difference between UNIQUE and PRIMARY KEY constraints?

Difference is that Primary Key have an implicit NOT NULL constraint while Unique Keys do not.

A table can have only one Primary Key whereas there can be any number of Unique Keys

When do you use WHERE clause and when do you use HAVING clause?
WHERE clause applies to the individual rows. HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause.
What are the difference between DDL, DML and DCL commands?

DDL - Data Definition Language: statements used to define the database structure or schema. 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 
COMMENT - add comments to the data dictionary 
RENAME - rename an object 


DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples: 

SELECT - retrieve data from the a database 
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 remain 
MERGE - UPSERT operation (insert or update) 
CALL - call a PL/SQL or Java subprogram 
EXPLAIN PLAN - explain access path to the data 
LOCK TABLE - controls concurrency 
DCL - Data Control Language. Some examples: 
GRANT - gives user's access privileges to database 
REVOKE - withdraw access privileges given with the GRANT command 

TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. 

COMMIT - save work done 
SAVEPOINT - identify a point in a transaction to which you can later roll back 
ROLLBACK - undo the modification I made since the last COMMIT 
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use 
SET ROLE - set the current active roles 
DML are not auto-commit. i.e. you can roll-back the operations, but DDL are auto-commit 

What are sequences? Explain with syntax?

A field in oracle can be kept as auto incremented by using sequence. it can be used to create a number sequence. 

For example

e_seq will cache up to 20 values for performance. Starts from one.

CREATE SEQUENCE e_seq
MINVALUE 1
MAXVALUE 99999
START WITH 1
INCREMENT BY 1
CACHE 20;
What is a view?

A View in Oracle and in other database systems is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.

Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing. 

Security Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to.

Latest Updates

Fill in the blank

I ________ station before the train arrived.

If I ______ a king, I would help everyone.

Radha___________her daughter for an hour.

Errors Identification

Find out which part of the sentence below has an error and mark the option accourdingly -
Sohan was married in 1990 and have three children.

Find out which part of the sentence below has an error and mark the option accourdingly -
There is no money in the bank in Ram Singh's and Rama's joint account.

Direction

Swapnil start from his home and walked 35 km towards east, after that he turned to his right and walked 20 km. He again turned to his right and walks 35 km. Finally, he turned to his left and walked 20 km and reached his office. How far is his office from his home?

Numbers

How many 2 digit numbers are divisible by 6?

HCF and LCM

Find the smallest square number which is exactly divisible by 3, 5, 15, 18, 20 and 24?

HCF and LCM of two numbers are 82 and 574 respectively. if one of the numbers is 287, find the second number?

Numbers

How many numbers between 1200 to 1500 are multiple of 13?

Find the total number of terms in the series : -3, 1, 5, 9,............., 101 ?

Find the sum of the first 20 terms of the series : 8, -3, 11, -6, 14, -12, 17, -24,....... ?

Series

Find the missing term in the given series - 101, 120, 145, 168, (?), 224

Find the missing term in the given series - 11A13, 17E19, (?), 31O37, 41U43

Find the missing term in the given series - 2,4,8,3,9,27,4,16,(?),5,25,125

Agriculture

Which gas is fixed by Rhizobium bacteria present in the root nodules of a pea plant ?

Chemistry

The formation of sulphurous acid involves ?

The acid present in soft drinks is ?