Technical Interview Questions and Answers :: SQL
Delete is an DML statement.When we delete the data that data can't delete permenently without we give commit command.But Truncate is an DDL statement.When we truncate the data that can be deleted perminently without given commit command also.
They are two types of views .1)Simple view. 2)Complex View
a materialized view is a database object that contains the results of a query.
A function is considered deterministic if it always returns the same result for a specific input value.
The un-named system exceptions can be assigned with a name explicitly by using the pragma compiler Exception_init. Exception_init will associate the Oracle predefined error code to a user defined name
The term "automous transaction" refers to the ability of PL/SQL temporarily suspend the current transaction and begin another, fully independent transaction (which will not be rolled-back if the outer code aborts). The second transaction is known as an autonomous transaction. The autonomous transaction functions independently from the parent code.
DDL stands for Data Definition Language. DML stands for Data Manipulation Language. DDL commands are used to create,alter,truncate,drop,rename etc. Where DML are used to insert,update,delete,merge etc.,
Oracle db: We can see all tables in a scheme for curent user by using query 'select * from user_tables'. Dba user can see tables for all users by using query 'select * from all_tables'. Second query will return owner of table too.
A view can be created even if the defining query of the view cannot be executed. We call such a view as view with errors.
1. Cost based Optimization (Physical)
This is based on the cost of the query. The query can use different paths based on indexes, constraints, sorting methods etc.