SQL Statements

The following is an alphabetical list of SQL statements that can be issued against an Oracle database. These commands are available to any user of the Oracle database. Emphasized items are most commonly used.

• ALTER - Change an existing table, view or index definition
• AUDIT - Track the changes made to a table
• COMMENT - Add a comment to a table or column in a table
• COMMIT - Make all recent changes permanent
• CREATE - Create new database objects such as tables or views
• DELETE - Delete rows from a database table
• DROP - Drop a database object such as a table, view or index
• GRANT - Allow another user to access database objects such as tables or views
• INSERT - Insert new data into a database table
• No AUDIT - Turn off the auditing function
• REVOKE - Disallow a user access to database objects such as tables and views
• ROLLBACK - Undo any recent changes to the database
• SELECT - Retrieve data from a database table
• UPDATE - Change the values of some data items in a database table

Some examples of SQL statements follow. For all examples in this tutorial, key words used by SQL and Oracle are given in all uppercase while user-specific information, such as table and column names, is given in lower case.

To create a new table to hold employee data, we use the CREATE TABLE statement:

     
  CREATE TABLE employee
  (fname           VARCHAR2(8),
   minit           VARCHAR2(2),
   lname           VARCHAR2(8),
   ssn             VARCHAR2(9) NOT NULL,
   bdate           DATE,
   address         VARCHAR2(27),
   sex             VARCHAR2(1),
   salary          NUMBER(7) NOT NULL,
   superssn        VARCHAR2(9),
   dno             NUMBER(1) NOT NULL) ;

To insert new data into the employee table, we use the INSERT statement:

INSERT INTO employee
VALUES ('BUD', 'T', 'WILLIAMS', '132451122',
      '24-JAN-54', '987 Western Way, Plano, TX',
      'M', 42000, NULL, 5);

To retrieve a list of all employees with salary greater than 30000 from the employees table, the following SQL statement might be issued (Note that all SQL statements end with a semicolon):

SELECT fname, lname, salary FROM employee WHERE salary > 30000;

To give each employee in department 5 a 4 percent raise, the following SQL statement might be issued:

         
         UPDATE employee
         SET    salary = salary * 1.04
         WHERE  dno = 5;

To delete an employee record from the database, the following SQL statement might be issued:

         
         DELETE FROM employee
         WHERE  empid = 101 ;

The above statements are just an example of some of the many SQL statements and variations that are used with relational database management systems.