Oracle SQL Interview Questions and Answers

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.

How does one eliminate duplicates rows from a table?

Choose one of the following queries to identify or remove duplicate rows from a table 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);
What the difference between UNION and UNIONALL?
Union will remove the duplicate rows from the result set while Union all doesn't.

How to Change a SQL prompt name?
SQL> set sqlprompt "oracle >" oracle >
Find out nth highest salary from emp table?
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
What is the difference between VARCHAR, VARCHAR2 and CHAR data types?

CHAR should be used for storing fixed length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space. 

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.

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?
its associated with table but its a not a part of the table. for example SYSDATE, USER, UID, CURVAL, NEXTVAL, ROWID, ROWNUM
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.

Latest Updates

General Knowledge of India

In 1897, the Birsa Movement supporters raised which colour flag as a symbol of Birsa Raj?

Ratio and Proportion

The cost of 10 books is Rs. 80/-. Find the value of 15 books?

The cost of 48 bags of paddy is Rs. 16,800/-. What is the cost of 36 bags of paddy?

Monthly expenditure of a family of 4 members is Rs 2800/-. What is the expenditure if there are 3 members in the family?

Annual incomes of the families of Sita and Latha are in the ratio 4:3 and their annual expenditure are in the ratio 3:2. What is their annual income if they save Rs. 3000/- in a year?

A certain sum of money is divided between A and B in the ratio of 5/4 : 5/3 and A got Rs. 360/-. Find the total sum of money and B’s share?

In an office, the ratio of the salaries of an officer and a clerk is 15:7. If the salary of a clerk is Rs. 720/- less than the salary of the officer, find the salary of each?

In an election to a municipal council, the votes secured by two candidates are in the ratio 13:19. The elected candidates gets 312 votes more than the second candidate. How many votes are secured by the second candidate?

A certain sum of money is divided between Murthy and Prasad in the ratio 22/7 : 8/3. If Murthy gets Rs. 120/- more than Prasad, find the share of each?

In a cricket test series, the runs made by Raju and Tendulkar are in the ratio 5:9 and Tendulkar and Azar are in the ratio 6:7. What are the runs made by them if Azar makes 187 runs more than Raju?

If A:B = 2:3 and B:C = 9:5 find A:B:C?

If A:B = 3/5:5/7 and B:C = 3/4:2/5, find A:B:C?

If R:S = 0.01:0.11 and S:T = 2.2:1, Find R:S:T?

In a school, Under a Saving Scheme, Rekha saves 3/2 times the savings of Bindu, Geeta saves 4 times the savings of Rekha. Find the ratio of the savings of all the three in least terms?

Simplification

A man left 1/3 rd of his property to his wife and 3/5 th of the remainder to his daughter. He gave the rest to his son who received Rs. 6400/-. How much was his original property worth?

Ratio and Proportion

In a school 4/9 of the pupils are boys. There are 125 girls. How many boys are there?

If A:B = 3:4 and B:C = 5:6, find A:C?

IF X:Y = 3:4 AND Y:Z = 4:5, FIND X:Y:Z?