Top Oracle Interview Questions

What is the difference between a database and a data warehouse?

What is the purpose of indexing in a database?

What is the difference between a left join and a right join in SQL?

What is the purpose of normalization in database design?

What is the difference between a primary key and a foreign key in a database?

What is the purpose of a database trigger?

What is the difference between a stored procedure and a function in a database?

What is the purpose of data encryption in a database?

What is the difference between a cluster and a grid in a database?

What is the purpose of data backup and recovery in a database?

What is the difference between a relational database and a NoSQL database?

What is the purpose of data warehousing in a database?

What is the difference between a data mart and a data warehouse?

What is the purpose of ETL (Extract, Transform, Load) in a database?

What is the difference between a star schema and a snowflake schema in a database?

Write a Java program to connect to an Oracle database and retrieve all the rows from a table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class OracleConnector {
  public static void main(String[] args) {
    
    String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
    String username = "username";
    String password = "password";
    String query = "SELECT * FROM table_name";
    try {
      Connection conn = DriverManager.getConnection(url, username, password);
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        
        System.out.println(rs.getString(1));
      }
    }
    catch (SQLException e) {
      
      System.out.println(e.getMessage());
    }
  }
}

Write a Python program to insert a new row into an Oracle database table.

import cx_Oracle;
conn = cx_Oracle.connect('username/password@localhost:1521/ORCL');
cursor = conn.cursor();
query = "INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')";
cursor.execute(query);
conn.commit();
cursor.close();
conn.close()

Write a Java program to update a row in an Oracle database table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class OracleUpdater {
  public static void main(String[] args) {
    
    String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
    String username = "username";
    String password = "password";
    String query = "UPDATE table_name SET column1 = 'new_value' WHERE column2 = 'old_value'";
    try {
      Connection conn = DriverManager.getConnection(url, username, password);
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(query);
      conn.close();
    }
    catch (SQLException e) {
      
      System.out.println(e.getMessage());
    }
  }
}

Write a Python program to delete a row from an Oracle database table.

import cx_Oracle;
conn = cx_Oracle.connect('username/password@localhost:1521/ORCL');
cursor = conn.cursor();
query = "DELETE FROM table_name WHERE column1 = 'value1'";
cursor.execute(query);
conn.commit();
cursor.close();
conn.close()

Write a Java program to create a new table in an Oracle database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class OracleCreator {
  public static void main(String[] args) {
    
    String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
    String username = "username";
    String password = "password";
    String query = "CREATE TABLE table_name (column1 VARCHAR2(20), column2 NUMBER)";
    try {
      Connection conn = DriverManager.getConnection(url, username, password);
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(query);
      conn.close();
    }
    catch (SQLException e) {
      
      System.out.println(e.getMessage());
    }
  }
}

Write a Python program to drop a table from an Oracle database.

import cx_Oracle;
conn = cx_Oracle.connect('username/password@localhost:1521/ORCL');
cursor = conn.cursor();
query = "DROP TABLE table_name";
cursor.execute(query);
conn.commit();
cursor.close();
conn.close()