Difference between TRUNCATE, DELETE and DROP commands

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.

Drop :- Drop command remove a table from the database.All the table's rows,indexes,privileges will also be removed.No DML
triggers will be fired. The operation can not be rollback.


Drop and Truncate  are DDL commands,whereas DELETE  is a DML command.Therefore delete operations can be roll



TRUNCATE:

This is the DML command. This command delete the data from table. But there is one difference from delete command.
Truncate command drop the storage  structure & data  held by this table.
Drop storage can be use by this table again or some other table.
This is the faster command because it directly drop the storage

Read More

Using Clause In Sql

The Using Clause specifies which columns to test for equality when two tables are joined.it can be used insted of an on clause in the join operation that have an explicit join clause.


Syntax:-
Using(simple-column name[,simple column name]*)
The Columns listed in the using clause mumst be present in both of two being joined.The USING clause will be  transformed to an ON clause for equality between the

named columns in two tables.



For Example :- SQL> SELECT l.city, d.department_name
  2  FROM   locations l JOIN departments d USING (location_id)
  3  WHERE  location_id = 1400;

CITY                           DEPARTMENT_NAME
------------------------------ ------------------------------
Southlake                      IT

Read More

Distinct Function

The SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however,
 sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name

SELECT DISTINCT City FROM emp_info;


SQL WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion

SELECT * FROM Emp_info
WHERE City='Mumbai'
Use Quotes Around Text Fields
SQL uses single quotes around text values (most database systems will also accept double quotes).

However, numeric values should not be enclosed in quotes.

Read More