List of Commands

6 List of Commands

Here are some of the commands required for your homework. Most of these statements can be used in embedded SQL. It is up to you which you will enter in SQL*Plus and which you will embed. In general, if you are using them in a Pro*C++ file, prepend the command with "EXEC SQL". If you are using them in SQL*Plus, remember to use the semicolon as the statement terminator. The purpose of the following list is to supplement the tutorials which have been made available to you.

There are times where just the function name is listed and no description of its syntax is provided. The reason is that the statement is rarely used and is unlikely to be needed in the course of your work. Full Oracle documentation is online (see the tutorial page "How to Get More Help/Links"). However, most students should get by quite nicely just by using these CPSC 304 tutorial pages.

6.1 ALTER TABLE

The ALTER TABLE command modifies a table that has already been created. It can:

add a column

add an integrity constraint

redefine a column's datatype, size, or default value

modify storage characteristics or other parameters

enable, disable, or drop an integrity constraint or trigger

explicitly allocate an extent

Most of the time, you probably are better off dropping the table and re-creating it, especially if you're testing. The amount of data you have is small and it's "fake" data in the sense that you have it in a script file somewhere, so you can probably re-run it to simply re-populate the table with data.

6.2 ALTER VIEW

This command re-compiles a view. To use it, simply issue the following statement:

ALTER VIEW customer_view COMPILE;

6.3 COMMENT

The COMMENT command adds a comment to a table, view, snapshot, or column into Oracle's data dictionary or catalog. For example,

COMMENT ON COLUMN shipping.notes
IS 'Special packing or shipping instructions';

COMMENT ON COLUMN shipping.notes IS '';

6.4 COMMIT

In Oracle, statements that you have entered are separated into transactions. As you insert, update, or delete rows, you can imagine yourself as changing a temporary copy of the table. The original copy of the table is not updated until you perform a COMMIT. The reason for this is to allow you to "undo" your changes and give you a chance to enter several commands before you decide to COMMIT.

Data Definition Language statements will do an implicit COMMIT; however, adding, updating, or deleting rows will not. Thus, if you add a bunch of rows to a table, and you kill your Oracle session, then upon re-entering Oracle, you will find that those rows are no longer there. If you wanted the changes to be permanent, you need to perform a COMMIT.

To COMMIT, just enter the word, "COMMIT". If you do not want your changes to take effect, then use "ROLLBACK". An embedded SQL version of this command also exists.

6.5 CONSTRAINT

A CONSTRAINT clause is used to define an integrity constraint, which is a rule that restricts the values for one or more columns in a table. This clause can only appear in the CREATE TABLE or ALTER TABLE statements. For a description of the CONSTRAINT clause, please check the examples in the CREATE TABLE command.

6.6 CREATE INDEX

This command creates an index on one or more columns of a table. An index is a database object that contains an entry for each value that appears in the indexed column(s) of the table. This allows for faster access to the rows. For example:

CREATE INDEX i_emp_ename
ON emp(ename);

6.7 CREATE SYNONYM

This command creates another name for an existing object such as a table. It is especially useful for projects that involve more than one person, because, often, the user was not the same person who created the table. Without a synonym, a user would have to specify the full name of the table, including its owner. For example:

CREATE SYNONYM employee
FOR ops$rwan.employee;

Once you create the synonym, you will save some time since you do not have to type "ops$rwan.employee" all the time. Instead, just type "employee" and Oracle will know what table you mean. (By the way, the table owner (creator) must GRANT permission to allow others to access the table.)

6.8 CREATE TABLE

This statement is used to create a table, and is best described with an example.

CREATE TABLE PLAYERS (
PLAYERNO       SMALLINT  NOT NULL,
NAME           CHAR(15)  NULL,
SEX            CHAR(1)   NOT NULL  CHECK (SEX IN ('M', 'F')),
YEAR_OF_BIRTH  SMALLINT            CHECK (YEAR_OF_BIRTH > 1920)
);

This will create a table. You should be able to guess what most of the parameters mean. For the "SEX" column, the CHECK parameter is called a row constraint.

By the way, white space does not matter, provided you end the statement with a semicolon and delimit each column with a comma. Add spaces to make it look "nice", if you want, so that the names of the columns and the descriptions of the columns are separated and neatly lined up.

CREATE TABLE PENALTIES (
PAYMENTNO INTEGER NOT NULL,
PLAYERNO SMALLINT NOT NULL,
PEN_DATE DATE DEFAULT '08-OCT-96',
AMOUNT DECIMAL (7,2) DEFAULT 50.00);

The DEFAULT clause provides an "assumed" value in the absence of an explicit assignment. In this example, AMOUNT defaults to 50.00 if the user doesn't provide an explicit value for AMOUNT during an INSERT operation, for example.

CREATE TABLE TEAMS (
TEAMNO SMALLINT NOT NULL PRIMARY KEY,
PLAYERNO SMALLINT NOT NULL UNIQUE,
DIVISION CHAR(6) NOT NULL);

You should already be familiar with the term PRIMARY KEY. Here, the UNIQUE operand ensures that each value in the column is unique, even though the column is not the primary key.

CREATE TABLE TEAMS (
TEAMNO SMALLINT NOT NULL,
PLAYERNO SMALLINT NOT NULL,
DIVISION CHAR(6) NOT NULL,
PRIMARY KEY (TEAMNO, PLAYERNO),
UNIQUE (DIVISION)
FOREIGN KEY (PLAYERNO) REFERENCES PLAYERLIST (NO)
};

Compare this example with the previous one. Note the locations of PRIMARY KEY and UNIQUE. Here, they appear at the end separated by commas, whereas before, they appear to be a part of the definition of a row. The first case is a column integrity rule and the second case is a table integrity rule. If you plan to make a single column a primary key, then either will do. But, as the two examples show, if you want to make the primary key from two or more columns, only a table integrity rule will work.

Also note the foreign key syntax, above. The type, precision, etc. of both the attribute in this table and the attribute in the table that it is referencing must be the same. The attribute name, though, does not have to be the same. In the table TEAMS, the PLAYERNO is a foreign key. The table it references is called PLAYERLIST, and in that table, what it is referencing to is a column called NO. Note: NO of PLAYERLIST has to be the primary key of that table.

Also, if PLAYERLIST's primary key was composed of more than one column, then when TEAMS uses the foreign key constraint, it has to refer to all of those columns since the concatenation of all of them forms a primary key to what TEAMS is referencing. You can't just reference a column of that primary key.

6.9 CREATE VIEW

This command creates a view, which is simply a logical table that is based on one or more tables or views. For example:

CREATE OR REPLACE VIEW dept20
AS SELECT ename, sal*12 annual_salary
FROM emp
WHERE deptno = 20;

Since an existing view has to be deleted before being re-created, "OR REPLACE" allows you to create it without dropping it first. The rest of the syntax is the minimum requirement. You need a SELECT query since the view is taken from one or more base tables.

6.10 DELETE

The DELETE command removes a set of rows from a table or from a view's base table. For example:

DELETE FROM temp_assign;

DELETE FROM emp
WHERE job = 'SALESMAN'
AND comm < 100;

In the first example, since there is no WHERE clause, everything is deleted. In the second example, the WHERE clause has to be satisfied (as well as the AND clause) in order for the deletion to succeed. An embedded SQL version of DELETE also exists.

6.11 DROP INDEX

The DROP INDEX command drops a index from the database. For example:

DROP INDEX monolith;

6.12 DROP SYNONYM

The DROP SYNONYM command drops a synonym from the database. For example:

DROP SYNONYM market;

6.13 DROP TABLE

Likewise, the DROP TABLE command drops a table from the database. This command can end with the words, "CASCADE CONSTRAINTS" if you wish to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If this option is omitted and the referential integrity constraints exist, then an error message is returned.

DROP TABLE test_data;

6.14 DROP VIEW

This command is used to drop a view.

DROP VIEW view_data;

6.15 GRANT

In industry, it is commonly the situation that large-scale development projects involve many specialized players. In such a case, one person (a Database Administrator (DBA)) is likely the only person who has the ability to create tables and indexes. As another example, in previous years, CPSC 304 has had a large project component in which only one of the two team members had the ability to create tables and store many rows of data. This was done for two reasons: permissions (as just noted) and disk quota. Recall that Oracle uses Oracle USERID's. Oracle has its own disk quota, and this is not the same as your Unix disk quota. For the UNIX version of Oracle, a user is defined by OPS$<username>. So, for example, a certain user might be referred to as:

OPS$rwan

This may seem unimportant to you, but in case you ever have to refer to a table other than one which you created, then the Oracle ID will be used to qualify the table (and give you the right one). For example, if the table is called STUDENTS, an alternative name that would prevent confusion would be:

OPS$rwan.STUDENTS

Access to tables can only be given to its owner and higher authorities (or by using the WITH GRANT OPTION). Here are the table privileges that you can give to someone:

SELECT - allows use of SELECT statement. The grantee also has the ability to use it in a view.

INSERT - allows insertion of rows using INSERT

DELETE - allows deletion of rows using DELETE

UPDATE - allows modifications to values of an existing row, using UPDATE

INDEX - allows for the creation and dropping of indexes

ALTER - allows the grantee to add new columns to an existing table or change data types

REFERENCES - allows for the user to define foreign keys

ALL or ALL PRIVILEGES - all of the above

Syntax:

GRANT <table privileges>
ON <table name>
TO <grantees>
[ WITH GRANT OPTION ]

For table privileges, use any of the above, but UPDATE can take an extra set of parameters, namely the columns to which changes are allowed. Without it, it implies all columns.

The last optional "WITH GRANT OPTION" just allows who you're granting privileges to, to have the ability to in turn grant privileges to somebody else.

Here are some examples:

GRANT SELECT
ON PLAYERS
TO OPS$rwan;

GRANT INSERT, UPDATE
ON TEAMS
TO OPS$rwan;

Remember to use full Oracle user ID's (e.g., OPS$name) and not just account ID's.

6.16 INSERT

The INSERT command adds a row to a table or to a view's base table. Here are a few examples of insertion:

INSERT INTO dept
VALUES (50, 'PRODUCTION', 'SAN FRANCISCO');

INSERT INTO emp (empno, ename, job, sal, comm, deptno)
VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);

INSERT INTO bonus
SELECT ename, job, sal, comm
    FROM emp
    WHERE comm > 0.25 * sal
      OR job IN ('PRESIDENT', 'MANAGER');

In the first example, the column names of dept are not listed. Therefore, the 3 values to be inserted are considered to correspond to the first 3 columns that were defined by the CREATE TABLE command. The second example explicitly says what the names of the 6 columns are, and this is a much safer approach. In the third example, the rows that are SELECTed from the emp table are placed in the bonus table. This shows how a query can be used to determine the values to be inserted.

An embedded SQL version of INSERT also exists.

6.17 RENAME

The RENAME command renames a table, view or synonym. Here is a self-explanatory example:

RENAME dept TO emp_dept;

6.18 REVOKE

Though unnecessary for your purposes, the possibility exists to REVOKE the access you GRANTed to someone previously. The options are very similar to those of the GRANT command. With the list of permissions described in the GRANT section above, and using the following example, you can probably guess what the syntax for REVOKE is.

6.19 ROLLBACK

The ROLLBACK statement is the opposite of the COMMIT statement. Please read the section on COMMIT for a description of what a transaction is.

If you have performed several data manipulation operations like insertion and deletion and you do not want the changes to be made permanent, then you can issue the ROLLBACK command (with no arguments) so that the changes are not updated in the permanent copy of the table(s). In other words, you can abort a transaction. An embedded SQL version of ROLLBACK also exists.

6.20 SELECT

Central to a database system is the power to actually retrieve data from the database. Therefore, the SELECT statement is extremely important. Some possible extensions are nested SELECTs which you may use, but if you understand the basic SELECT syntax, extending it is pretty easy.

This is the syntax of a SELECT statement:

<select statement> :: = 
  <select clause>
  <from clause>
  [ <where clause> ]
  [ <connect by clause> ]
  [ <group by clause> [ <having clause> ] ]
  [ <order by clause> ] ;

Examples:

SELECT PLAYERNO, LEAGUENO
FROM PLAYERS
WHERE TOWN = 'Stratford'
ORDER BY LEAGUENO;

SELECT * FROM PLAYERS;

In the first example, two columns are selected from the table called PLAYERS. The search criteria is that the TOWN value has to be the character string 'Stratford'. This data is ordered in ascending order by LEAGUENO. The second example shows the use of "*" which simply specifies all columns. The absence of a WHERE clause means that the search gets all the rows of the table. The absence of an ORDER BY clause means the rows are returned without explicitly being sorted. Depending on the data entry order, and any possible modifications, you should assume an arbitrary ordering.

Here are a few more examples:

SELECT deptno, MIN(sal), MAX(sal)
FROM emp
GROUP BY deptno;

SELECT deptno, MIN(sal), MAX(sal)
FROM emp
WHERE job = 'CLERK'
GROUP BY deptno
HAVING MIN(sal) < 1000;

In the first example, the minimum and maximum of a column is calculated but rather than calculating one overall minimum and one overall maximum (thus giving a total of two answers), the data is grouped by department number first, and then the minimum and maximum of each department number is found. The HAVING clause in the second example extends the first by only including those departments having a minimum salary of less than 1000.

SELECT dept.deptno, dname, SUM(sal)
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dept.deptno, dname
ORDER BY dept.deptno;

SELECT dept.deptno, dname, SUM(sal)
FROM emp, dept
WHERE emp.deptno(+) = dept.deptno
GROUP BY dept.deptno, dname
ORDER BY dept.deptno;

These two examples are examples of joins. Joins are simply the combination or concatenation of two or more tables through a common column. In the first example, we are joining the tables emp and dept through the deptno's which exist in both tables. Two dept columns exist (i.e., emp.deptno and dept.deptno), so if we are SELECTing one, we have to specify which one we want (in SELECT clause). The GROUP BY and ORDER BY clauses just group rows by department number first and then department name and finally sort them in ascending sequence.

The second example is slightly more interesting. The only difference is the plus sign (+). In this example, if a department has no employees, the department is still displayed but anything associated with the emp table is left NULL. Without the (+), a resulting row is displayed only if an emp.deptno and a dept.deptno exist and are equal. Simply put, the (+) just says the existence of emp.deptno is not required.

Nested subqueries come from the basic SELECT syntax. Not much can be said about them, but here are some examples of them:

SELECT ename, deptno
FROM emp
WHERE deptno =
    (SELECT deptno
      FROM EMP
      WHERE ename = 'TAYLOR');

UPDATE emp
SET sal = sal * 1.1
WHERE empno NOT IN (SELECT empno FROM bonus);

CREATE TABLE newdept (deptno, dname, loc)
AS SELECT deptno, dname, loc FROM dept;

6.21 UPDATE

The UPDATE command is used to update an existing value in a table or in a view's base table. Its basic syntax is simple, but the ability to extend it using a nested SELECT is possible. A simple example of this extension was shown in the section entitled "SELECT". But, the basic form of an UPDATE command can be shown with this example:

UPDATE emp
SET job = 'MANAGER', sal = sal + 1000, deptno = 20
WHERE ename = 'JONES';