Table Names and Properties - Gonzaga University



Business DataBase Systems Name: __________________

Professor Chen

ENHANCED GUIDE TO ORACLE

Chapter 2 Creating and Modifying Database Tables

Names and Properties

I. Naming Conventions (Table and Field names)

1. from 1 to 30 characters

2. only alphanumeric characters, and special characters ($, _ ,#)

3. must begin with a letter and can not contain blank spaces or hyphens

e.g., Are the following names valid? Why?

customer order

customer-order

#order

II. Data Types

SQL*PLUS commands are NOT case sensitive (but Query within the single quotation are case sensitive).

There are three types of datatype in Oracle: builit-in, library, or user-defined. Built-in are standard, user-defined are specialized types built by users, and library types are types built by a third party and supplied to the user. The followings are most commonly used built-in data types:

1. VARCHAR2

a) variable length character up to ROLLBACK;

5. ROLLBACK can be used with SAVEPOINT that mark the beginning of a transaction. Any other commands issued between the savepoint and the rollback will not be saved in the database.

SQL> SAVEPOINT

…

…

SQL> ROLLBACK TO

6. Rules on entering the data into tables with referential integrity (see figure on the next page):

Updating Existing Records in a Table

UPDATE

SET =

WHERE ;

e.g.,

UPDATE student

SET s_class = ‘SR’

WHERE sid = 102;

DELETE FROM

WHERE ;

e.g.,

DELETE FROM student;

WHERE sid = 102;

How many records will be deleted from the next command?

DELETE FROM ;

B. Using the SELECT Command to Retrieve Database Data (p. 99)

SELECT COMMANDS

-- Retrieving Data from a Single Table

SELECT

FROM

WHERE ;

SELECT

FROM

WHERE ;

SELECT

FROM

WHERE

AND …

OR …;

SELECT

FROM

WHERE NOT ;

SELECT *

FROM

SELECT DISTINCT

FROM ;

-- Sorting the Query Output

-- Ascending is the default

SELECT

FROM

WHERE

ORDER BY ;

SELECT

FROM

WHERE

ORDER BY ;

SELECT SYSDATE

FROM DUAL;

Note that DUAL is a “utility” table provided by Oracle. It has exactly one column, called DUMMY, and one row. DUAL is used to do things such as:

1. Arithmetic

2. Obtain system information such as the date

3. Return literal values.

You can do this with other tables, but the results would be returned once for each row in the table.

Using Calculations in a Query

1. Number Functions

ABS(n) e.g., SELECT ABS(-1) FROM DUAL; Result: 1

POWER(n, power) e.g., SELECT POWER(3,2) FROM DUAL; Result: 9

ROUND(n, precision) e.g., SELECT ROUND(123.476, 1) FROM DUAL; Result: 123.5

TRUNC(n, precision) e.g., SELECT TRUNC(123.476, 1) FROM DUAL; Result: 123.4

e.g.,

SELECT sid, slname, TRUNC((SYSDATE – sdob)/365.25) AS age

FROM student, DUAL

2. Using Date Arithmetic

Specify the shipments are in the period from 8/11/2001 through 8/17/2001.

SELECT shipid, date_expected, quantity_expected

FROM shipping

WHERE date_expected > TO_DATE(‘08/10/2001’, ‘ MM/DD/YYYY’)

AND date_expected =5

GROUP BY bldg_code, room;

4. Creating a Column Alias

SELECT AS

e.g.,

SELECT csecid, naxenrl, currenrl, (maxenrl – currenrl) AS open

FROM course_section

C. Using Queries to (INNER) Join Multiple Tables

SELECT

FROM , , …

WHERE ;

Note that the column-name in the WHERE condition should be the same (either pk or fk). If a column-name is on multiple tables, it should be followed by a tablename (e.g., faculty.loc_id)

SELECT

FROM tablename1, tablename2, tablename3, tablename4, …

WHERE tablename1.column-name = talbename2.column-name

AND tablename3.column-name = talbename4.column-name

…

AND

… ;

Using Set Operators to Combine Query Results

UNION: returns all rows from both queries, but ONLY displays duplicate rows once

UNION ALL : returns all (duplicate) rows from both queries, and displays ALL duplicate rows

INTERSECT: returns all rows that are returned by both queries

MINUS: returns all rows returned by the first query minus the matching rows returned by the second query

They all require that both queries have the same number of display columns in the SELECT statement, and that each column in the first qurey has the same data type as the corresponding column in the second query.

Database Views

A database view is a logical (virtual) table based on a query. It does not store data, but presents it in a format different from the one in which it is stored in the underlying tables.

CREATE VIEW AS

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download