Delete multiple duplicate rows ? OR How to Delete Duplicate Records in Oracle ?

Link One

Link Two

What is the difference between UNION and UNION ALL?

This deals with SQL. UNION only selects distinct values, UNION ALL selects all values.

What is a Left outer join?

This deals with SQL. Left outer join preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

What is SQL?

SQL is a Structured Query Language that allows you to 
communicate with a database and handle the data it 
contains in all kinds of ways. It consists of Data 
Definition Language commands, such as Create, Alter, 
Truncate and Drop, and Data Manipulation Language 
commands, such as Select, Insert, Update and Delete. 
The most common databases that use SQL as their query 
language are Oracle, SQL Server, DB2, and MySQL.

How is the primary key different from a unique key?

Both the primary and unique keys uniquely identify a record in a database table. One difference is that 
you can have more than one unique key per table, but only one primary key. Also, the primary key does not 
allow any null value, while the unique key allows one.

Write a SQL query to get the second highest salary from the table above. Also write a query to find the nth highest salary in SQL, where n can be any number.

Solution to finding the 2nd highest salary in SQL

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

Finding nth highest salary example

SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

Find the nth highest salary in Oracle using rownum

select * from (
  select Emp.*, 
row_number() over (order by Salary DESC) rownumb 
from Employee Emp)
where rownumb = n;  /*n is nth highest salary*/

Reference:-

One Link

SQL Links ?

One Link

Two Link

Three Link

Fourth Link

How is the primary key different from a unique key?

 Both the primary and unique keys uniquely identify a record in a database table. One difference is that you can have more than one unique key per table, but only one primary key. Also, the primary key does not allow any null value, while the unique key allows one.

What are the basic steps to create a JDBC application?

Following are the basic steps to create a JDBC application:

1. Import packages containing the JDBC classes needed for database programming.
2. Register the JDBC driver, so that you can open a communications channel with the database.
3. Open a connection using the DriverManager.getConnection () method.
4. Execute a query using an object of type Statement.
5. Extract data from result set using the appropriate ResultSet.getXXX () method.
6. Clean up the environment by closing all database resources relying on the JVM's garbage collection.

What is a ResultSet?

These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data. The java.sql.ResultSet interface represents the result set of a database query.

What is a statement?

Statement encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.

What is a connection?

Connection interface consists of methods for contacting a database. The connection object represents communication context.

What are types of ResultSet?

There are three constants which when defined in result set can move cursor in resultset backward, forward and also in a particular row.

1. ResultSet.TYPE_FORWARD_ONLY: The cursor can only move forward in the result set.
2. ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can scroll forwards and backwards, and the result set is not sensitive to changes made by others to the database that occur after the result set was created.
3. ResultSet.TYPE_SCROLL_SENSITIVE: The cursor can scroll forwards and backwards, and the result set is sensitive to changes made by others to the database that occur after the result set was created.