1. What are the advantages of view?
    • Join 2 or more tables and show it as one object
    • View data without storing the data into the object
    • Restrict the view/visibility of the able
    • Restrict access to the table
    • Views do not store the data, only view defination occupies space so view does not occupy any physical space in the DB
    • Can perform DML on views but the underlying tables are affected
  2. What are disadvantages of view?
    • When the underlying table(s) is dropped the view becomes inactive
    • As views just represent data in the underlying tables we cannot change the data of a viewing without changing the data of the tables.
  3. Truncate and Delete
    • Truncate is faster than Delete. (Why)
    • Truncate applies to whole table but Delete can be executed for a filtered set of records
    • Data cannot be recoved in Truncate, but we can recover data in Delete. (Why)
    • Truncate is DDL and Delete is DML
  4. Truncate and Drop
    • Truncate will not delete the structure of the table.
    • Drop will delete everything including the structure.
  5. Can you Delete a column in table with data in Oracle?
    Yes but not always. (Why)
  6. What is the datatype of null in oracle?
    NULL is a marker that represents missing, unknown, or inapplicable data. Null is untyped in SQL, meaning that it is not designated as a NUMBER, CHAR, or any other specific data type. Do not use NULL to represent a value of zero, because they are not equivalent.

    So it does not have a datatype as it is untyped?
  7. Maximum level of sub-queries in the WHERE clause of an SQL statement?
    Oracle allows up to 255 levels of subqueries in the WHERE clause.
  8. How to kill a session in oracle?
    To kill session using SQL command. Just check serial no and sid (system identifier) from v$session view as follows:

    select sid,serial# from v$session where machine='GPTWORKGROUP';

    alter system kill session '9,171' immediate;

    immediate is used for forced kill
  9. Difference between Having Clause and Where Clause?
    • Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:
    • The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
    • The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
    • The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
  10. Difference between IN and EXISTS
    • IN:Returns true if a specified value matches any value in a subquery or a list
    • Exists:Returns true if a subquery contains any rows.
Card Set
interview questions for oracle sql