Introducing the Structured Query Language (SQL)


Structured Query Language (SQL) is the standard language designed to access relational databases. SQL is pronounced either as the word sequel or as the letters S-Q-L. (I prefer sequel as it s quicker to say.)

SQL is based on the groundbreaking work of Dr. E.F. Codd, with the first implementation of SQL being developed by IBM in the mid-1970s. IBM was conducting a research project known as System R, and SQL was born from that project. Later in 1979, a company then known as Relational Software Inc. (known today as Oracle Corporation) released the first commercial version of SQL. SQL is now fully standardized and recognized by the American National Standards Institute (ANSI). You can use SQL to access an Oracle, SQL Server, DB2, or MySQL database.

SQL uses a simple syntax that is easy to learn and use. You ll see some simple examples of its use in this chapter. There are five types of SQL statements, outlined in the following list:

  • Query statements    Allow you to retrieve rows stored in database tables. You write a query using the SQL SELECT statement.

  • Data Manipulation Language (DML) statements    Allow you to modify the contents of tables. There are three DML statements:

    • INSERT    Allows you to add rows to a table.

    • UPDATE    Allows you to change a row.

    • DELETE    Allows you to remove rows.

  • Data Definition Language (DDL) statements    Allow you to define the data structures, such as tables, that make up a database. There are five basic types of DDL statements:

    • CREATE    Allows you to create a database structure. For example, CREATE TABLE is used to create a table; another example is CREATE USER , which is used to create a database user.

    • ALTER    Allows you to modify a database structure. For example, ALTER TABLE is used to modify a table.

    • DROP    Allows you to remove a database structure. For example, DROP TABLE is used to remove a table.

    • RENAME    Allows you to change the name of a table.

    • TRUNCATE    Allows you to delete the entire contents of a table.

  • Transaction Control (TC) statements    Allow you to permanently record the changes made to the rows stored in a table or undo those changes. There are three TC statements:

    • COMMIT    Allows you to permanently record changes made to rows.

    • ROLLBACK    Allows you to undo changes made to rows.

    • SAVEPOINT    Allows you to set a savepoint to which you can roll back changes made to rows.

  • Data Control Language (DCL) statements    Allow you to change the permissions on database structures. There are two DCL statements:

    • GRANT    Allows you to give another user access to your database structures, such as tables.

    • REVOKE    Allows you to prevent another user from accessing to your database structures, such as tables.

There are many ways to run SQL statements and get results back from the database, some of which include programs written using Oracle Forms and Reports. SQL statements may also be embedded within programs written in other languages, such as Oracle s Pro*C, which allows you to add SQL statements to a C program. You can also add SQL statements to a Java program though JDBC; for more details see my book Oracle9i JDBC Programming (Oracle Press, 2002).

Oracle also has a tool called SQL*Plus that allows you to enter SQL statements using the keyboard or to supply a file that contains SQL statements and run those statements. SQL*Plus enables you to conduct a conversation with the database because you can enter SQL statements and view the results returned by the database. You ll be introduced to SQL*Plus in the next section.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net