How to display all tables in different databases

In this article, we will see how to connect to the different databases(MySQL, Oracle, PostgreSQL, DB2) and how to display the all table names. 

MySQL Connect to the database: mysql [-u username] [-h hostname] database-name

To list all databases, in the MySQL prompt type:
show databases

Then choose the right database:
use <database-name>

List all tables in the database:
show tables

Describe a table:

desc <table-name>

Oracle

Connect to the database: 
connect username/password@database-name;

To list all tables owned by the current user, type:
select tablespace_name, table_name from user_tables;

To list all tables in a database:
select tablespace_name, table_name from dba_tables;

To list all tables accessible to the current user, type:
select tablespace_name, table_name from all_tables;

To describe a table:
desc <table_name>;

PostgreSQL

Connect to the database:
psql [-U username] [-h hostname] database-name

To list all databases, type either one of the following:
list

To list tables in a current database, type:
\dt

To describe a table, type:
\d <table-name>

DB2

Connect to the database:

db2 connect to <database-name>;

List all tables:

db2 list tables for all;

To list all tables in selected schema, use:

db2 list tables for schema <schema-name>;

To describe a table, type:

db2 describe table <table-schema.table-name>;

Happy coding...

Read More

Case Insensitive Sorting Example in Oracle11g

Step1: Create Employee table 

create table employee(id int, name varchar(20));

Step2: Insert values to Employee table

insert into employee values(1,'Ranga');
insert into employee values(2,'rAnga Reddy');
insert into employee values(3,'Raja');
insert into employee values(4,'raJa Reddy');
insert into employee values(5,'raja');
insert into employee values(6,'Reddy');
insert into employee values(7,'ranga');

Step3: Select the Employee values with out sorting

select * from employee;

------------------------------------------- 

ID NAME
1 Ranga
2 rAnga Reddy
3 Raja
4 raJa Reddy
5 raja
6 Reddy
7 ranga

Step4: Select the Employee values with sorting 

select * from employee order by name;

------------------------------------------- 

ID NAME
3 Raja
1 Ranga
6 Reddy
2 rAnga Reddy
4 raJa Reddy
7 ranga
5 raja

Step5: Select the Employee values with case insensitive sorting 

select * from employee order by upper(name);

------------------------------------------- 

ID NAME
5 raja
3 Raja
4 raJa Reddy
7 ranga
1 Ranga
2 rAnga Reddy
6 Reddy

Click here to Run and Test the above example in online.

Read More

Write a SQL program to accept the users birthdate and calculates the Age.

create table Persons(id int, name varchar2(40), dob date);

insert into Persons values(1,'ranga','01-JUN-88');
insert into Persons values(2,'eswar','01-JUL-88');
insert into Persons values(3,'vinod','01-APR-87');

1. SELECT p.*, trunc( (sysdate - dob) / 365.25) as age FROM Persons p;
2. SELECT p.*, floor(months_between(sysdate,dob)/12) age FROM Persons p;
3. SELECT  p.*, trunc((sysdate - p.dob)/365.25) as age FROM Persons p;

Output:

Displaying the Year Month Day
-----------------------------------------------------

SELECT trunc(months_between(sysdate,dob)/12) as YEAR,
       trunc(mod(months_between(sysdate,dob),12)) as MONTH,
       trunc(sysdate - add_months(dob,trunc(months_between(sysdate,dob)/12) *12 + trunc(MOD(months_between(sysdate,dob),12)))) DAY
FROM (SELECT p.dob dob FROM Persons p);

Read More

Finding the Nth heighest Salary in SQL

Program: 

Write a SQL query to get the Nth highest salary from Employee table. Here N can be any number.

Solution:

SELECT e.* FROM Employee e;

Step1: If you want to find the height salary, initially you need to sort the employee data in descending order or if you want to find the lowest salary you need to sort the employee data in ascending.


Here i am finding the height salary so i will sort the employee data in descending order.

SELECT e.* FROM Employee e ORDER By e.salary DESC;

Step2: After Sorting the data, now give the rownum to the table. 

SELECT ROWNUM rn, Emp.* FROM (SELECT e.* FROM Employee e ORDER By e.salary DESC) Emp;

Step3: Finally, which highest salary do you want to find give that value in where condition.

Here i am finding the 3rd heighest employee information.

SELECT * FROM(SELECT ROWNUM rn, Emp.* FROM (SELECT e.* FROM Employee e ORDER By e.salary DESC) Emp) WHERE rn=3;

Note: rownum works some of the databases only example oracle



MySQL:

mysql>CREATE TABLE Employee(id INT NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(50) NOT NULL,
   lastname VARCHAR(50),
   salary INT,
   designation varchar(5),
   PRIMARY KEY (id));

Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('ranga','reddy',50000,'Software Engineer');
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('vinod','reddy',170000,'Software Developer');
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('raja','reddy',7000,'Trainee Test Engineer');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('vasu','reddy',79000,'Test Engineer');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('manu','reddy',90,'Engineer');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from Employee;
+----+-----------+----------+--------+-------------+
| id | firstname | lastname | salary | designation |
+----+-----------+----------+--------+-------------+
|  1 | ranga     | reddy    |  50000 | Softw       |
|  2 | vinod     | reddy    | 170000 | Softw       |
|  3 | raja      | reddy    |   7000 | Train       |
|  4 | vasu      | reddy    |  79000 | Test        |
|  5 | manu      | reddy    |     90 | Engin       |
+----+-----------+----------+--------+-------------+
5 rows in set (0.00 sec)

Sort the Employee data in Ascending because i am finding the Nth lowest salary.

mysql> select * from Employee e order by e.salary;
+----+-----------+----------+--------+-------------+
| id | firstname | lastname | salary | designation |
+----+-----------+----------+--------+-------------+
|  5 | manu      | reddy    |     90 | Engin       |
|  3 | raja      | reddy    |   7000 | Train       |
|  1 | ranga     | reddy    |  50000 | Softw       |
|  4 | vasu      | reddy    |  79000 | Test        |
|  2 | vinod     | reddy    | 170000 | Softw       |
+----+-----------+----------+--------+-------------+
5 rows in set (0.00 sec)

In mysql there is keyword called limit is used to find out the limit number of records.

mysql> select * from Employee e order by e.salary limit 2;
+----+-----------+----------+--------+-------------+
| id | firstname | lastname | salary | designation |
+----+-----------+----------+--------+-------------+
|  5 | manu      | reddy    |     90 | Engin       |
|  3 | raja      | reddy    |   7000 | Train       |
+----+-----------+----------+--------+-------------+
2 rows in set (0.00 sec)

In the above example i passed limit value as 2 so it displayed only 2 records. Here Limit is keyword is taking only one argument.

There is one more limit it will take two arguments one is offset and second is maximum number of rows return.

Syntax looks like Limit N-1, No.of records

For example i want to find the 1st lowest employee information then i need to pass like 1-1, 1 ==> 0,1

mysql> select * from Employee e order by e.salary limit 0,1;
+----+-----------+----------+--------+-------------+
| id | firstname | lastname | salary | designation |
+----+-----------+----------+--------+-------------+
|  5 | manu      | reddy    |     90 | Engin       |
+----+-----------+----------+--------+-------------+
1 row in set (0.00 sec)


In the next example we will see finding the top n records.

Read More

Hibernate4.X Application example by using Gradle

Prerequisite requirement

- Installed and configured Maven, MySQL, Eclipse IDE.
- See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.dpuf

Prerequisite requirement

- Installed and configured Maven, MySQL, Eclipse IDE.
- See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.dpufPrerequisite requirement

- Installed and configured Maven, MySQL, Eclipse IDE.
- See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.dpu

Prerequisite requirement

- Installed and configured Maven, MySQL, Eclipse IDE.
- See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.

Step1: Create a Java project with Maven

Run the following command in terminal or command prompt to generate a standard java project.

Create a quick project file structure with Maven command ‘mvn archetype:generate‘ - See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.dpuf

[ranga@ranga gradle]$ mvn archetype:generate -DgroupId=com.ranga -DartifactId=HibernateExample1 -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false

Project structure:

HibernateExample1/
├── pom.xml
└── src
    ├── main
    │   └── java
    │       └── com
    │           └── ranga
    │               └── App.java
    └── test
        └── java
            └── com
                └── ranga
                    └── AppTest.java

9 directories, 3 files

Step2: Create build file to execute App.java application.

create a new file called build.gradle under HibernateExample1 and the following content.

build.gradle
 
apply plugin: 'java'

task runApp(dependsOn: 'classes', type: JavaExec) {
    main = 'com.ranga.App'
    classpath = sourceSets.main.runtimeClasspath
}

defaultTasks 'runApp'

Project structure:

HibernateExample1/
├── build.gradle
├── pom.xml
└── src
    ├── main
    │   └── java
    │       └── com
    │           └── ranga
    │               └── App.java
    └── test
        └── java
            └── com
                └── ranga
                    └── AppTest.java
9 directories, 4 files

Step3: Run the Java project by using gradle.

[ranga@ranga HibernateExample1]$ gradle -q runApp
Hello World!

Step4: Create a resources folder

Create a new folder called resources under src/main. Here we are adding the Configuration files and Mapping files.

Project structure:

HibernateExample1/
├── build.gradle
├── pom.xml
└── src
    ├── build.gradle
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── ranga
    │   │           └── App.java
    │   └── resources
    ├── pom.xml
    └── test
        └── java
            └── com
                └── ranga
                    └── AppTest.java

10 directories, 6 files

Step5: Hibernate configuration file

Create a Hibernate configuration file and put under the resources root folder, “src/main/resources/hibernate.cfg.xml“. Add the following content.

hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
        <property name="connection.username">ranga</property>
        <property name="connection.password">ranga</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create</property>

       <!-- Mapping files  -->  
       <mapping resource="com/ranga/mapping/Employee.hbm.xml" />

    </session-factory>
</hibernate-configuration>

Step6: Hibernate Mapping file

Create a Employee.hbm.xml file and put it in “src/main/resources/com/ranga/mapping“ folder. 

Employee.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.ranga.mapping">
    <class name="Employee" table="Employees">
        <id name="id" type="java.lang.Long">
            <generator class="native" />
        </id>
        <property name="name" type="java.lang.String" />
        <property name="age" type="java.lang.Integer">
    </class>
</hibernate-mapping> 

Project structure:

HibernateExample1/
├── build.gradle
├── pom.xml
└── src
    ├── build.gradle
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── ranga
    │   │           └── App.java
    │   └── resources
    │       ├── com
    │       │   └── ranga
    │       │       └── mapping
    │       │           └── Employee.hbm.xml
    │       └── hibernate.cfg.xml
    ├── pom.xml
    └── test
        └── java
            └── com
                └── ranga
                    └── AppTest.java

13 directories, 8 files
 
Step7: POJO class or Model class

Create a Employee.java file and put it in “src/main/java/com/ranga/mapping”

Employee.java

package com.ranga.mapping;

import java.io.Serializable;

public class Employee implements Serializable {
        private long id;
        private String name;
        private int age;

        public long getId() {
                return id;
        }
        public void setId(long id) {
                this.id = id;
        }
        public String getName() {
                return name;
        }
        public void setName(String name) {
                this.name = name;
        }
        public int getAge() {
                return age;
        }
        public void setAge(int age) {
                this.age = age;
        }

        @Override
        public String toString() {
           return "Employee [id = "+id+", name = "+name+", age = "+age+"]";
        }
}

Step8: Create Hibernate Utility class

Create a HibernateUtil.java class to take care of Hibernate start up and retrieve the session easily. Create a util folder and put this file in it, “src/main/java/com/ranga/util”.

HibernateUtil.java

package com.ranga.util;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class HibernateUtil {
        private static final SessionFactory sessionFactory;
        private static final ServiceRegistry serviceRegistry;
        static {
                try {
                        // Create the SessionFactory from hibernate.cfg.xml
                        Configuration configuration = new Configuration();
                        configuration.configure();
                        serviceRegistry = new ServiceRegistryBuilder().applySettings(
                                        configuration.getProperties()).buildServiceRegistry();
                        sessionFactory = configuration.buildSessionFactory(serviceRegistry);
                } catch (Throwable ex) {
                        // Make sure you log the exception, as it might be swallowed
                        System.err.println("Initial SessionFactory creation failed." + ex);
                        throw new ExceptionInInitializerError(ex);
                }
        }

        public static SessionFactory getSessionFactory() {
                return sessionFactory;
        }

        public static void closeSessionFactory() {
        if (sessionFactory != null)
            sessionFactory.close();
    }
}


Step 9: Client Application

App.java

package com.ranga;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import com.ranga.mapping.Employee;
import com.ranga.util.HibernateUtil;

public class App
{
    public static void main( String[] args )
    {
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
        Session session = sessionFactory.openSession();
        session.beginTransaction();

        Employee employee = new Employee();
        employee.setName("Ranga Reddy");
        employee.setAge(25);

        long employeeId  = (Long)session.save(employee);
        employee = (Employee) session.get(Employee.class, employeeId);

        System.out.println(employee);

        session.close();
    }
}

Project structure:

HibernateExample1/
├── build.gradle
├── pom.xml
└── src
    ├── build.gradle
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── ranga
    │   │           ├── App.java
    │   │           ├── mapping
    │   │           │   └── Employee.java
    │   │           └── util
    │   │               └── HibernateUtil.java
    │   └── resources
    │       ├── com
    │       │   └── ranga
    │       │       └── mapping
    │       │           └── Employee.hbm.xml
    │       └── hibernate.cfg.xml
    ├── pom.xml
    └── test
        └── java
            └── com
                └── ranga
                    └── AppTest.java

15 directories, 10 files

Now the Hibernate Application is ready.

Step10: Run the hibernate application

[ranga@ranga HibernateExample1]$ gradle runApp
 
Output:

Employee [id = 1, name = Ranga Reddy, age = 25]

Download

Read More

Extend the System Tablespace on Oracle

There are two ways to extend the system table space.

1. Set the current datafile to AUTOEXTEND
2. Add a new datafile

1. Set the Current datafile to AUTOEXTEND

SELECT 'ALTER database datafile ''' || file_name || ''' ' || ' AUTOEXTEND ON maxsize 2097152000;'
FROM dba_data_files
WHERE tablespace_name = 'SYSTEM';

ALTER database datafile '/u01/app/oracle/oradata/XE/system.dbf'  AUTOEXTEND ON maxsize 2097152000;

database datafile '/U01/APP/ORACLE/ORADATA/XE/SYSTEM.DBF' altered.

2. Add a New datafile

alter tablespace SYSTEM add datafile '/u01/app/oracle/oradata/XE/system2.dbf' size 1024m;

Read More

How Connect to Oracle when you forgot your Password

Reset Oracle Password

 
Step1: Open SQL Command Line  and enter the following command:

SQL> conn sys/manager as sysdba;
Connected.

Step2: To reset the password of the SYSTEM password (or any other user password), run the following query:
 
SQL> alter user sys identified by manager;
User altered.

Or

Your password file should be under <orahome>\database\PWD<SID>.ora.

Delete it and run the Oracle password utility from the command prompt:

c\:Oracle\ora92\database>ORAPWD file=PWD<SID>.ora password={password} entries={however many}.

The <password> is your new sys password. After you log in as sys you can change it and create new passwords for system.

Read More

How to Install Oracle 11g XE on Linux Fedora 17/18

Installing Oracle Database 11g XE on Fedora 17/18

The following are the steps to install a Oracle 11g in fedora.

Step1:  Downloading the Software

Download the oracle-xe-11.2.0-1.0.x86_64.rpm.zip file from follwing url

http://www.oracle.com/technetwork/products/express-edition/downloads/index.html

Step2: Extracting the content

Extract the zip into any location. Here i am extracting the above zip into following location.

[ranga@ranga Disk1]$ pwd

/home/ranga/install/Disk1

[ranga@ranga Disk1]$ ls

oracle-xe-11.2.0-1.0.x86_64.rpm  response  upgrade

[ranga@ranga Disk1]$ 

Step3: Install libaio

[root@ranga Disk1]#  yum install libaio

Step4: Installing the oracle by using rpm command.

[root@ranga Disk1]# rpm -i oracle-xe-11.2.0-1.0.x86_64.rpm

Preparing... ########################################### [100%] 1:oracle-xe ########################################### [100%] Executing post-install steps... You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

Your installation done but u need configure

Step5: Configuring the database

[root@ranga Disk1]# /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration ------------------------------------------------- This will configure on-boot properties of Oracle Database 11g Express Edition. The following questions will determine whether the database should be starting upon system boot, the ports it will use, and the passwords that will be used for database accounts. Press <enter> to accept the defaults. Ctrl-C will abort. Specify the HTTP port that will be used for Oracle Application Express [8080]: 9090 Specify a port that will be used for the database listener [1521]: Specify a password to be used for database accounts. Note that the same password will be used for SYS and SYSTEM. Oracle recommends the use of different passwords for each database account. This can be done after initial configuration: Confirm the password: Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y Starting Oracle Net Listener...Done Configuring database...Done Starting Oracle Database 11g Express Edition instance...Done Installation completed successfully.

In the above configuration, i am given port number is 9090 by default 8080 and database listener is same and you need to enter SYS or SYSTEM password. If you want while booting it self starting the database your giving 'y' in boot option. Finaly your database configured successfully. This installation created under /u01 directory. 

Starting the Database manually : 

To start the database manually, run this command as root user:

# /etc/init.d/oracle-xe start
or

# /usr/lib/oracle/xe/app/oracle/product/11.2.0/server/bin/lsnrctl start

Stopping the Database manually:

To stop the database manually, run the following command as root user:

# /etc/init.d/oracle-xe stop

Un Install the Oracle 11g: 

Step1: First you need to check which oracle version is installed.

[ranga@ranga ~]$ rpm -qa | grep oracle

oracle-xe-11.2.0-1.0.x86_64

Step2: Uninstall the oracle by using rpm -e with which version it is installed.

[ranga@ranga ~]$ rpm -e 

oracle-xe-11.2.0-1.0.x86_64

Read More

SQL Command

SQL Commands: 
SQL commands are broadly classified into 5 categories: They are
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)
5. DQL (Data Query Language)

1. Data Definition Language (DDL) - DDL commands are used to Creating, Modifying and Deleting the structure of database objects.
DDL Commands: CREATE, ALTER, DROP, TRUNCATE and RENAME

2. Data Manipulation Language (DML) - DML commands are used to storing, modifying and deleting the data in the database.
DML Commands: INSERT, UPDATE and DELETE

3. Data Control Language (DCL) - DCL commands are used for providing the security to database objects.
DCL Commands: GRANT and REVOKE.

4. Transaction Control Language (TCL) - TCL commands are used to allow the user to control the transactions in a database.
TCL Commands: COMMIT, ROLLBACK and SAVEPOINT

5. Data Query Language (DQL) - DQL command are used to get/retrieve the data from the database.
DQL Commands: SELECT

NOTE: SQL Commands are terminated by a semicolon(;)
Create Command - The CREATE TABLE command is used to create table(s) or relation(s) to store data.
Syntax:

CREATE TABLE table_name(
   column_name1 datatype,
   column_name2 datatype,
   column_name3 datatype,
   .....
   column_nameN datatype,
   PRIMARY KEY( one or more columns )
);

Example:

CREATE TABLE employee( 
  id number(5), 
  name varchar(20),   
  age number(2), 
  salary number(10),
  PRIMARY KEY(id)
);

Describe Command - The DESCRIBE or DESC command is used to view the description of a table.
Syntax:

Desc table_name;

Example:

Desc employee;

Insert command - Insert command is used to insert the values in a table.
Syntax:

INSERT INTO TABLE_NAME [ (column1,column2,column3,... columnN)] VALUES (value1, value2, value3,...valueN);

Example:

INSERT INTO employee(id, name, age, salary) VALUES(1, 'Ranga', 27, 3000);
INSERT INTO employee VALUES(2, 'Raja', 47, 8000);

Select Command - The SELECT statement is used to query or retrieve data from a table in the database.
A query may retrieve information from specified columns or from all of the columns in the table.
Syntax:
There are three ways we can retrieve data from a table:

• Retrieve one column
• Retrieve multiple columns
• Retrieve all columns

SELECT column_name FROM table_name;
SELECT column_name1, column_name2, column_name3 FROM table_name;
SELECT * FROM table_name;

Example:

SELECT name FROM employee;

SELECT id, name, age FROM employee;

SELECT * FROM employee;

Update Command - The UPDATE command is used to update the values of a table.
Syntax:

UPDATE table_name SET column_name1=value1, column_name2=value2, ......column_namen=valuen WHERE condition;

Example:

UPDATE employee SET name='Ranga Reddy' WHERE id=1;

Alter Command : The ALTER command is used to alter the structure of a table. Alter command has three attributes namely add, modify and drop.
Add:      Adding a column in a table.
Modify: Modify the size of a column.
Drop:     Dropping a column of a table.
Add Column:
Syntax:

ALTER TABLE table_name ADD(column_name datatype);

Example:

ALTER TABLE employee ADD(company varchar(30));

Modify Column:
Syntax:

ALTER TABLE table_name MODIFY(column_name datatype);

Example:

ALTER TABLE employee MODIFY(name varchar(25));

Drop Column:
Syntax:

ALTER TABLE table_name DROP column column_name;

Example:

ALTER TABLE employee DROP column company;

Rename Command - The RENAME command is used to change the name of the table.
Syntax:

RENAME old_table_name TO new_table_name;

Example:

RENAME employee TO employees;

Delete Command - DELETE command is used to delete a row(s) from a table.
Syntax:

DELETE FROM table_name [WHERE condition];

Example:

DELETE FROM employee WHERE id=1;

Truncate command - The TRUNCATE command is used to delete all rows from a table and free the space containing the table.
Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE employee;

Drop Command - The DROP command is used to drop the structure of a table permanently. If you drop a table, all the rows in the is deleted.
Syntax: 

DROP TABLE table_name;

Example:

DROP TABLE employee;

Commit Command - The COMMIT is used to save the transaction. It will saves all transactions to the database since the last COMMIT or ROLLBACK command.
Syntax & Example:

COMMIT

Rollback Command - ROLLBACK command is used to restoring the database to its original position since last COMMIT.
Syntax & Example:

ROLLBACK 

SavePoint Command- The SAVEPOINT command used to identifies the transaction in a database.
Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example:

SAVEPOINT S1;

The ROLLBACK command is used to undo a group of transactions.
Syntax:

ROLLBACK TO SAVEPOINT_NAME;

Example:

ROLLBACK TO S1;

Grant Command: The GRANT command is used to grants(gives) user permissions to access the database objects.

Syntax:

GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name}[WITH GRANT OPTION]; 

privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.
object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE.
Example:

CREATE USER rangareddy IDENTIFIED by ranga;
GRANT ALL privileges TO rangareddy;

Revoke Command: REVOKE command is used to rovokes(removes) the permission given to user.
Syntax:

REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name} 

Example:

REVOKE ALL ON employee FROM rangareddy;

Read More