What are the difference between DDL, DML and DCL commands? DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: o CREATE - to create objects in the database o ALTER - alters the structure of the database o DROP - delete objects from the database o TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed o COMMENT - add comments to the data dictionary o RENAME - rename an object DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: o SELECT - retrieve data from the a database o INSERT - insert data into a table o UPDATE - updates existing data within a table o DELETE - deletes all records from a table, the space for the records remain o MERGE - UPSERT operation (insert or update) o CALL - call a PL/SQL or Java subprogram o EXPLAIN PLAN - explain access path to data o LOCK TABLE - control concurrency DCL
Data Control Language (DCL) statements. Some examples: o GRANT - gives user's access privileges to database o REVOKE - withdraw access privileges given with the GRANT command TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. o COMMIT - save work done o SAVEPOINT - identify a point in a transaction to which you can later roll back o ROLLBACK - restore database to original since the last COMMIT o SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use What is SQL and where does it come from? Structured Query Language (SQL) is a language that provides an interface to relational database systems. The proper pronunciation of SQL is "ess cue ell," and not "sequel" as is commonly heard. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures. The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and are (partially?) implemented in Oracle8 and 9i. Example SQL statements: CREATE TABLE table1 (column1 NUMBER, column2 VARCHAR2(30)); INSERT INTO table1 VALUES (1, 'XYZ'); SELECT * FROM table1 WHERE column2 = 'XYZ'; Difference between TRUNCATE, DELETE and DROP commands DELETE The DELETE command is used to remove 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.
TRUNCATE TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
DROP The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. 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. From Oracle 10g a table can be "undropped". Example:
PS: DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
1>TRUNCATE is a DDL command whereas DELETE is a DML command. 2>TRUNCATE is much faster than DELETE. Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data. 3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently. 4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired. 5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause How does one escape special characters when writing SQL queries? Escape quotes Use two quotes for every one displayed. Examples:
Escape wildcard characters The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:

Escape ampersand (&) characters in SQL*Plus When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:
Other methods: Define an escape character:
Don't scan for substitution variables:
Can one select a random collection of rows from a table? The following methods can be used to select a random collection of rows from a table: The SAMPLE Clause From Oracle 8i, the easiest way to randomly select rows from a table is to use the SAMPLE clause with a SELECT statement. Examples:
In the above example, Oracle is instructed to randomly return 10% of the rows in the table.
This example will sample 5% of all formatted database blocks instead of rows. This clause only works for single table queries on local tables. If you include the SAMPLE clause within a multi-table or remote query, you will get a parse error or "ORA-30561: SAMPLE option not allowed in statement with multiple table references". One way around this is to create an inline view on the driving table of the query with the SAMPLE clause. Example:
If you examine the execution plan of a "Sample Table Scan", you should see a step like this: TABLE ACCESS (SAMPLE) OF 'EMP' (TABLE) ORDER BY dbms_random.value() This method orders the data by a by a random column number. Example:
The ORA_HASH () function The following example retrieves a subset of the data in the emp table by specifying 3 buckets (0 to 2) and then returning the data from bucket 1: SELECT * FROM emp WHERE ORA_HASH(empno, 2) = 1 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: Method 1:
Method 2:
This method is usually faster. However, remember to recreate all indexes, constraints, triggers, etc on the table when done. Method 3:


Note: One can eliminate N^2 unnecessary operations by creating an index on the joined fields in the inner loop (no need to loop through the entire table on each pass by a record). This will speed-up the deletion process. Note 2: If you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition. How does one get the time difference between two date columns? Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value. Let's investigate some solutions. Test data:
Solution 1
Solution 2 If you don't want to go through the floor and ceiling math, try this method
How does one add a day/hour/minute/second to a date value? The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:
The following format is frequently used with Oracle Replication:
Here are a couple of examples:

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

How does one code a matrix/crosstab/pivot report in SQL? Newbies frequently ask how one can display "rows as columns" or "columns as rows". Look at these example crosstab queries (also sometimes called matrix or pivot queries):
Here is the same query with some fancy headers and totals:
Here's another variation on the theme:
Can one retrieve only rows X to Y from a table? Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).
Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:
"This one was faster for me and allowed for sorting before filtering by rownum. The inner query (table A) can be a series of tables joined together with any operation before the filtering by rownum is applied."
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation. Can one retrieve only the Nth row from a table? Solution 1
Solution 2
Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation. Solution 3
Alternatively...
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation. How can one dump/ examine the exact content of a database column? Table data can be extracted from the database as octal, decimal or hex valies:
For this example, type=96 is indicating a CHAR column.
The last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded. How does one add a column to the middle of a table? Oracle only allows columns to be added to the end of an existing table. Example:
Nevertheless, some databases also allow columns to be added to an existing table after a particular column (i.e. in the middle of the table). For example, in MySQL the following syntax is valid: ALTER TABLE tablename ADD columnname AFTER columnname; Oracle does not support this syntax. However, it doesn't mean that it cannot be done. Workarounds: 1. Create a new table and copy the data across.
2. Use the DBMS_REDEFINITION package to change the structure on-line while users are workining. How does one code a hierarchical tree-structured query? The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss. The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:
Use the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes. One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval. NOTE: Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings. How does one count/sum data values in a column? Count/sum FIX values: Use this simple query to count the number of data values in a column:
A more sophisticated example...
Count/sum RANGES of data values in a column: A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:
For equal size ranges it might be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...). Eg.
How does one drop/ rename a columns in a table? Drop a column From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command. Workarounds for older releases:
Rename a column From Oracle9i one can RENAME a column from a table. Look at this example: ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn; Workarounds for older releases: Use a view with correct column names:
Recreate the table with correct column names:
Add a column with a new name and drop an old column:
How does one implement IF-THEN-ELSE logic in a SELECT statement? Oracle SQL supports several methods of coding conditional IF-THEN-ELSE logic in SQL statements. Here are some: CASE Expressions From Oracle 8i one can use CASE statements in SQL. Look at this example:
DECODE() Function The Oracle decode function acts like a procedural statement inside an
SQL statement to return different values or columns based on the values of
other columns in the select statement. Examples:
Note: The decode function is not ANSI SQL and is rarely implemented
in other RDBMS offerings. It is one of the good things about Oracle,
but use it sparingly if portability is required. GREATEST() and LEAST() Functions

NVL() and NVL2() Functions NVL and NVL2 can be used to test for NULL values. NVL(a,b) == if 'a' is null then return 'b'.
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
COALESCE() Function COALESCE() returns the first expression that is not null. Example:
NULLIF() Function NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
How does one prevent Oracle from using an Index? test 8
In certain cases, one may want to disable the use of a specific, or all indexes for a given query. Here are some examples: Adding an expression to the indexed column
Specifying the FULL hint to force full table scan
Specifying NO_INDEX hint
Using a function over the indexed column
How does one select EVERY Nth row from a table? One can easily select all even, odd, or Nth rows from a table using SQL queries like this: Method 1: Using a subquery
Method 2: Use dynamic views (available from Oracle7.2):
Method 3: Using GROUP BY and HAVING
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation. How does one select the TOP N rows from a table? From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the TOP N rows. Examples: Get the top 10 employees based on their Salary
Get the employees making the top 10 Salaries
For Oracle 8i and above, one can get the Top N rows using an inner-query with an ORDER BY clause:
Use this workaround for older (8.0 and prior) releases:
How to generate a text graphs (histograms) using SQL Sample output:
In the above example, the value returned by COUNT(*) is used to control the number of "*" characters to return for each department. We simply pass COUNT(*) as an argument to the string function LPAD (or RPAD) to return the desired number of *'s. Map/ concatenate several rows to a column This FAQ will demonstrate how row values can be concatenated into a single column value (similar to MySQL's GROUP_CONCAT function). Start by creating this function:
This function returns a string result with the concatenated non-NULL values from a SQL statement. It returns NULL if there are no non-NULL values. Here is an example of how to map several rows to a single concatenated column:
This example is more interresting, it concatenates a column across several rows based on an aggregation:
What is the difference between VARCHAR, VARCHAR2 and CHAR data types? Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used: CHAR CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.
Note: ASCII character 32 is a blank 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 VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.



1. VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring datatype. 2. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters. 3. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.
VARCHAR is used to store variable length character strings up to 4000 characters. But, remember CHAR is faster than VARCHAR - some times up to 50% faster.
[i].
CHAR(5) is fixed length, right padded with spaces.
VARCHAR(5) is fixed length, right padded with null
VARCHAR2(5) is variable length. Thus the difference between VARCHAR and VARCHAR2 is that VARCHAR is ANSI standard but takes up space whereas VARCHAR2 is Oracle-only but makes more efficient use of space. But as someone has already pointed out, Oracle have resolved this (in 9.2, maybe earlier) by casting VARCHAR to VARCHAR2. [ii]. Varchar and Varchar2 both are of variable character. Varchar can have MAximum 2000 character while Varchar can contain maximum 4000 character. [iii]
Varchar is of ANSI SQL standart while Varchar2 is of Oracle standard. What is the difference between SQL and PL/SQL? Both SQL and PL/SQL are languages used to access data within Oracle databases. SQL is a limited language that allows you to directly interact with the database. You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. However, SQL doesn't include all the things that normal programming languages have, such as loops and IF...THEN...ELSE statements. PL/SQL is a normal programming language that includes all the features of most other programming languages. But, it has one thing that other programming languages don't have: the ability to easily integrate with SQL. Some of the differences: o SQL is executed one statement at a time. PL/SQL is executed as a block of code. o SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural). o SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages. o You can embed SQL in a PL/SQL program, but you cannot embed PL/SQL within a SQL statement. What is SQL and where does it come from? Structured Query Language (SQL) is a language that provides an interface to relational database systems. The proper pronunciation of SQL is "ess cue ell," and not "sequel" as is commonly heard. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures. The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and are (partially?) implemented in Oracle8 and 9i. Example SQL statements: CREATE TABLE table1 (column1 NUMBER, column2 VARCHAR2(30)); INSERT INTO table1 VALUES (1, 'XYZ'); SELECT * FROM table1 WHERE column2 = 'XYZ'; What is PL/SQL and what is it used for? SQL is a declarative language that allows database programmers to write a SQL declaration and hand it to the database for execution. As such, SQL cannot be used to execute procedural code with conditional, iterative and sequential statements. To overcome this limitation, PL/SQL was created. PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. Some of the statements provided by PL/SQL: Conditional Control Statements: o IF ... THEN ... ELSIF ... ELSE ... END IF; o CASE ... WHEN ... THEN ... ELSE ... END CASE; o Iterative Statements: o LOOP ... END LOOP; o WHILE ... LOOP ... END LOOP; o FOR ... IN [REVERSE] ... LOOP ... END LOOP; o Sequential Control Statements: o GOTO ...; o NULL; The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database. Example PL/SQL block: