Oracle Workshop Tutorial

Oracle Workshop – Tutorial

Database Objects

Tables

The create table is used to hold data. This command creates a new table. It is made of columns that have an associated data type.

create table account

(login_name varchar2(8) not null,

password varchar2(10),

name varchar2(30),

ssn varchar2(9),

account_classification number,

e_mail varchar2(20),

lab_id number,

faculty_e_mail varchar2(20),

user_level_id number,

account_status number);

Views

A view is used to create a customized selection of one or more tables. It consists of a query.

create view public_account as

select

login_name, name, account_classification, e_mail,

lab_id, faculty_e_mail, user_level, account_status

from account;

Data Types

Data types available are:

When creating a table, using the qualifier "not null" with a column forces the database to require that the specified column, for any row of data being added, contains data. Null data is not the same as numeric value "0".

Number Data Type

These are fields defined as number. These fields contain only numeric data. All common arithmetic operations can be performed using numeric data, using infix notation. There also exists a set of functions available for use on numeric data.

The operators and functions can be used on numeric column data in queries.

select lab_id + 0 from account;

select sqrt(user_level_id) from account;

The operators and functions can be used on literal data, but requires a table to be named for correct syntax. The system provides a table named "dual" for this purpose.

select ceil(98.6) from dual;

Character Data Type

These are fields defined as char, varchar, and varchar2. These fields contain values made from alphanumeric and special characters. There exists a set of functions for use on character data.

The concatenation operator '||' allows two character fields to be joined together.

Date Data Type

These are fields defined as date. This type contains both the date and time. There is a set of functions that can be used on date data.

SQL

Statements

SQL statements fall in two categories, DDL(Data Definition Language) and DML (Data Manipulation Language). DDLstatements allow objects and privileges to be modified. DML statements are used for querying and updating the database.

The most frequently used SQL DDLcommands are alter, drop, create, and grant.

The most frequently used SQL DMLcommands are insert, describe, update, delete, and select.

Describe, Insert, Update, Delete, and Select

The describe command provides brief summary and description information on a table and its columns.

describe account;

The insert command is used to add data to a table (i.e. new rows to tables or views).

insert into account

values ('bob', NULL, NULL, NULL, NULL,

NULL, NULL, NULL, NULL, NULL);

insert into account(login_name, ssn, lab_id)

values ('bill', '123456789', 2);

The select command is used to retrieve data from the database. There are four parts to the command:

1. select [ALL DISTINCT] Column1 [Column2]
2. from Table1 [, Table2]
3. where “Conditions”
4. order by “Column-List” [ASC DESC]

To run a query using the select statement, the words select and from are required. The statements where and ordered by are optional when executing a query.

ALL and DISTINCT are keywords used to select either “ALL” (default) or the “DISTINCT” or unique records in your query results. If you wish to retrieve only distinct records you can use the DISTINCT keyword for the columns you specified after the “SELECT” statement.

ASC (ascending) and DESC (descending) are key words used to either order the query results in ascending or descending order.

NOTE: Using the asterisk (*) can be used to display all the column names in the tables selected.

View all columns, all data

select * from account;

View selected columns, all data

select login_name, e_mail from account;

View all columns where lab_id equals is 1

select * from account

where lab_id = 1;

View all columns where lab_id equals is 1 and account_status is not 0

select * from account

where lab_id = 1 and account_status != 0;

View all columns where faculty_e_mail is ''

select * from account

where faculty_e_mail = '';

View all columns where user_level_id in the range of 1 through 4

select * from account

where user_level_id between 1 and 4;

View all columns where login_name in the list 'larry', 'curly', 'moe'

select * from account

where login_name in ('larry', 'curly', 'moe');

View all columns where e_mail ends with '@cs.utep.edu'

select * from account

where e_mail like '%@cs.utep.edu';

Update, Delete, Alter

The update command is used to update date stored in a table. The command has three parts:

1. The word update followed by the table to be changed
2. The word set followed by a list of columns to be changed
3. The word where followed by any selection criteria (optional)
4. update account
5. set ssn = '111223333', lab_id = 10
6. where login_name = 'larry';

The delete command is used to remove one or more rows of data from a table. The command has two parts:

1. The words delete from followed by the table name
2. The word where followed by any selection criteria (optional)
3. delete from account where login_name = 'bill';

The alter command is used to add a column to and existing table, or to modify the data type of an existing column.

alter table account add (dob varchar2(10), favorite_color date);

alter table account modify (dob date, favorite_color varchar2(10));