Team Fly 

Page 34

SQL is the fundamental access tool of the Oracle database; in fact, it is the fundamental access tool of all relational databases. SQL is used to build database objects and it is also used to query and manipulate both these objects and the data they may contain. You cannot insert a row of data into an Oracle database unless you have first issued some basic SQL statements to create the underlying tables. While Oracle provides SQL*Plus, a SQL tool that enables you to interact with the database, there are also many GUI tools that can be used, which then issue SQL statements on your behalf behind the scenes.

CRITICAL SKILL 2.1
Learn the SQL Statement Components

Before learning many of the SQL commands that you will use frequently, first let's take a look at the two different categories into which SQL statements are classified. They are DDL, or data definition language, and DML, or data manipulation language. The majority of this chapter will deal with the latter.

DDL

DDL is the set of SQL statements that define or delete database objects such as tables or views. For the purposes of this chapter, we will concentrate on dealing with tables. Examples of DDL are any SQL statements that begin with create, alter, drop, and grant. Table 2-1 is a sample list of some DDL statements. It does not completely represent the many varied statements that all have a unique purpose and value.

SQL Command

Purpose

create table

Creates a table

create index

Creates an index

alter table

Adds a column, redefines an existing column, changes storage allocation

drop table

Drops a table

grant

Grants privileges or roles to a user or another role

truncate

Removes all rows from a table

revoke

Removes privileges from a user or a role

analyze

Gathers performance statistics on database objects for use by the cost-based optimizer

TABLE 2-1. Common Formats of Date Type Data

Team Fly 
0053-CRITICAL SKILL 2.1 Learn the SQL Statement Components