This Oracle Tutorial is visited quite frequently by visitors looking for answers about Oracle and the Oracle Tools.
So I thought, it might be a good idea, to give you a forum, where you can ask questions and hopefully get answers beyond this tutorial.
The forum just opened, please visit and participate, so we all benefit from your questions and solutions.
I will try to moderate as much as possible until we have found some volunteers, that are interested in taking over this task.
Structured Query Language (SQL) is the language used to manipulate
relational databases. SQL is tied very closely with the relational model.
In the relational model, data is stored in structures called
relations or tables. Each table has one or more attributes or
columns that describe the table. In relational databases, the
table is the fundamental building block of a database application.
Tables are used to store data on Employees, Equipment, Materials,
Warehouses, Purchase Orders, Customer Orders, etc. Columns in the
Employee table, for example, might be Last Name, First Name,
Salary, Hire Date, Social Security Number, etc.
SQL statements are issued for the purpose of:
Data definition - Defining tables and structures in the database (DB).
Data manipulation - Inserting new data, Updating existing data,
Deleting existing data, and
Querying the Database ( Retrieving existing data from the database).
Another way to say this is the SQL language is actually
made up of 1) the Data Definition Language (DDL) used to
create, alter and drop schema objects such as tables
and indexes, and 2) The Data Manipulation Language (DML)
used to manipulate the data within those schema objects.
The SQL language has been standardized by the ANSI X3H2 Database
Standards Committee. Two of the latest standards are SQL-89 and
SQL-92. Over the years, each vendor of relational databases has
introduced new commands to extend their particular implementation
of SQL. Oracle's implementation of the SQL language conforms to
the basic SQL-92 standard and adds some additional commands and
capabilities.
Structured Query Language (SQL) is the language used to manipulate
relational databases. SQL is tied very closely with the relational model.
In the relational model, data is stored in structures called
relations or tables. Each table has one or more attributes or
columns that describe the table. In relational databases, the
table is the fundamental building block of a database application.
Tables are used to store data on Employees, Equipment, Materials,
Warehouses, Purchase Orders, Customer Orders, etc. Columns in the
Employee table, for example, might be Last Name, First Name,
Salary, Hire Date, Social Security Number, etc.
SQL statements are issued for the purpose of:
Data definition - Defining tables and structures in the database (DB).
Data manipulation - Inserting new data, Updating existing data,
Deleting existing data, and
Querying the Database ( Retrieving existing data from the database).
Another way to say this is the SQL language is actually
made up of 1) the Data Definition Language (DDL) used to
create, alter and drop schema objects such as tables
and indexes, and 2) The Data Manipulation Language (DML)
used to manipulate the data within those schema objects.
The SQL language has been standardized by the ANSI X3H2 Database
Standards Committee. Two of the latest standards are SQL-89 and
SQL-92. Over the years, each vendor of relational databases has
introduced new commands to extend their particular implementation
of SQL. Oracle's implementation of the SQL language conforms to
the basic SQL-92 standard and adds some additional commands and
capabilities.
The following is an alphabetical list of SQL statements that can be
issued against an Oracle database. These commands are available to
any user of the Oracle database. Emphasized items are most
commonly used.
ALTER - Change an existing table, view or index definition
AUDIT - Track the changes made to a table
COMMENT - Add a comment to a table or column in a table
COMMIT - Make all recent changes permanent
CREATE - Create new database objects such as tables or views
DELETE - Delete rows from a database table
DROP - Drop a database object such as a table, view or index
GRANT - Allow another user to access database objects such as tables or views
INSERT - Insert new data into a database table
NO AUDIT - Turn off the auditing function
REVOKE - Disallow a user access to database objects such as tables and views
ROLLBACK - Undo any recent changes to the database
SELECT - Retrieve data from a database table
UPDATE - Change the values of some data items in a database table
Some examples of SQL statements follow. For all examples in this
tutorial, key words used by SQL and Oracle are given in all
uppercase while user-specific information, such as table and column
names, is given in lower case.
To create a new table to hold employee data, we
use the CREATE TABLE statement:
CREATE TABLE employee
(fname VARCHAR2(8),
minit VARCHAR2(2),
lname VARCHAR2(8),
ssn VARCHAR2(9) NOT NULL,
bdate DATE,
address VARCHAR2(27),
sex VARCHAR2(1),
salary NUMBER(7) NOT NULL,
superssn VARCHAR2(9),
dno NUMBER(1) NOT NULL) ;
To insert new data into the employee table, we
use the INSERT statement:
INSERT INTO employee
VALUES ('BUD', 'T', 'WILLIAMS', '132451122', '24-JAN-54',
'987 Western Way, Plano, TX', 'M', 42000, NULL, 5);
To retrieve a list of all employees with salary greater than 30000
from the employees table, the following SQL statement
might be issued (Note that all SQL statements
end with a semicolon):
SELECT fname, lname, salary
FROM employee
WHERE salary > 30000;
To give each employee in department 5 a 4 percent raise,
the following SQL statement might be issued:
UPDATE employee
SET salary = salary * 1.04
WHERE dno = 5;
To delete an employee record from the database, the following SQL
statement might be issued:
DELETE FROM employee
WHERE empid = 101 ;
The above statements are just an example of some of the
many SQL statements and variations that are used with relational
database management systems. The full syntax of these commands
and additional examples are given below.
In this section, the basic SQL Data Definition Language
statements are introduced and their syntax is given with
examples.
An Oracle database can contain one or more
schemas. A schema is a collection of
database objects that can include: tables, views,
indexes and sequences. By default, each user has
their own the schema which has the same name as
the Oracle username. For example, a single Oracle
database can have separate schemas for TUCANO, JONES,
SMITH and GREEN.
Any object in the database must be created in only
one schema. The object name is prefixed by the schema name
as in: schema.object_name
By default, all objects are created in the user's
own schema.
For example, when JONES creates a database object such as a
table, it is created in her own schema. If
JONES creates an EMPLOYEE table, the full
name of the table becomes: JONES.EMPLOYEE.
Thus database objects with the same name can be
created in more than one schema. This feature allows
each user to have their own EMPLOYEE table, for example.
Database objects can be shared among several users
by specifying the schema name. In order to work with
a database object from another schema, a user must
be granted authorization. See the section below on
GRANT and REVOKE for more
details.
Please note that many of these database objects
and options are not available under Personal Oracle Lite.
For example, foreign key constraints are not supported.
Please see the on-line documentation for Personal Oracle Lite
for more details.
SQL*Plus accepts SQL statements that allow a user to create, alter and
drop table, view and sequence definitions. These statements are
all standard ANSI SQL statements with the exception of CREATE SEQUENCE.
ALTER TABLE - Change an existing table definition. The table
indicated in the ALTER statement must already exist. This statement can
be used to add a new column or remove an existing column in a table,
modify the data type for an existing column, or add or remove a constraint.
ALTER TABLE has the following syntax for adding a new column
to an existing table:
Another ALTER TABLE option can change a data type of
column. The syntax is:
ALTER TABLE <TABLE NAME>
MODIFY ( <COLUMN NAME> <NEW DATA type> <[NOT]NULL> ) ;
Finally, ALTER TABLE can also be used to add a constraint
to a table such as for a PRIMARY KEY, FOREIGN KEY or
CHECK CONSTRAINT. The syntax to add a PRIMARY KEY is:
ALTER TABLE <TABLE NAME>
ADD CONSTRAINT <CONSTRAINT-NAME>
PRIMARY KEY (<COLUMN-NAME>);
In Oracle, you must use an ALTER TABLE statement to
define a composite PRIMARY KEY (a key made up of
two or more columns).
NOTE: In Oracle 8, there is no single command to drop a
column of a table. In order to drop a column from a table,
you must create a temporary table containing all of the
columns and records that will be retained. Then drop
the original table and rename the temporary table to
the original name. This is demonstrated below in
the section on Creating, Altering and Dropping Tables.
If you use Oracle 8i or later, the syntax to drop a column is:
ALTER TABLE <TABLE NAME>
DROP COLUMN <COLUMN-NAME>;
CREATE TABLE - Create a new table in the database. The
table name must not already exist. CREATE TABLE has the following
syntax:
An alternate syntax can be used to create a table with a
subset of rows or columns from an existing table.
CREATE TABLE <TABLE_NAME> AS
<SQL SELECT statement>;
DROP TABLE - Drop a table from the database. The table
name must already exist in the database. The syntax for the DROP
TABLE statement is:
DROP TABLE
<TABLE NAME>;
CREATE INDEX - Create a new Index that facilitates
rapid lookup of data. An index is typically created on
the primary and/or secondary keys of the table. The basic syntax
for the CREATE INDEX statement is:
CREATE INDEX <INDEX NAME>
ON
<TABLE NAME>
( <COLUMN NAME>, <COLUMN NAME> );
DROP INDEX - Drop an index from the database.
The syntax for the DROP INDEX statement is:
DROP INDEX <INDEX NAME>;
CREATE SEQUENCE - Create a new Oracle Sequence of
values. The new sequence name must not exist. CREATE SEQUENCE
has the following syntax:
CREATE SEQUENCE <SEQUENCE NAME>
INCREMENT BY <INCREMENT NUMBER>
START WITH <START NUMBER>
MAXVALUE <MAXIMUM VALUE>
CYCLE;
DROP SEQUENCE - Drop an Oracle Sequence. The sequence
name must exist. DROP SEQUENCE has the following syntax:
DROP SEQUENCE <SEQUENCE NAME>;
CREATE VIEW - Create a new view based on existing
tables in the database. The table names must already exist. The new
view name must not exist. CREATE VIEW has the following syntax:
CREATE VIEW <VIEW NAME> AS
<SQL SELECT statement>;
where sql select statement is in the form:
SELECT <COLUMN NAMES>
FROM
<TABLE NAME>
WHERE <WHERE CLAUSE>
Additional information on the SELECT statement and SQL queries can
be found in the next section.
Note that an ORDER BY clause may not be added to the
sql select statement when defining a view.
In general, views are read-only. That is, one
may query a view but it is normally the case that
views can not be operated on with INSERT, UPDATE
or DELETE. This is especially true in cases
where views joing two or more tables together
or when a view contains an aggregate function.
DROP VIEW - Drop a view from the database. The view
name must already exist in the database. The syntax for the DROP
VIEW command is:
DROP VIEW <VIEW NAME>;
In the following section, each of the SQL DDL commands will
be discussed in more detail.
Creating, Altering and Dropping Tables
A table is made up of one or more columns (also called attributes
in relational theory). Each column is given a name and a data type
that reflects the kind of data it will store. Oracle supports four
basic data types called CHAR, NUMBER, DATE and RAW. There are also
a few additional variations on the RAW and CHAR data types. The
basic datatypes, uses and syntax, are as follows:
VARCHAR2 - Character data type. Can contain letters, numbers and
punctuation. The syntax for this data type is: VARCHAR2(size) where
size is the maximum number of alphanumeric characters the column
can hold. For example VARCHAR2(25) can hold up to 25 alphanumeric
characters. In Oracle8, the maximum size of a VARCHAR2 column is 4,000 bytes.
The VARCHAR data type is a synonym for VARCHAR2. It is recommended to
use VARCHAR2 instead of VARCHAR.
NUMBER - Numeric data type. Can contain integer or floating point
numbers only. The syntax for this data type is: NUMBER(precision, scale)
where precision is the total size of the number including decimal
point and scale is the number of places to the right of the decimal.
For example, NUMBER(6,2) can hold a number between -999.99
and 999.99.
DATE - Date and Time data type. Can contain a date and time portion
in the format: DD-MON-YY HH:MI:SS. No additional information is needed
when specifying the DATE data type. If no time component is supplied when
the date is inserted, the time of 00:00:00 is used as a default. The output
format of the date and time can be modified to conform to local standards.
RAW - Free form binary data. Can contain binary data up to 255 characters.
Data type LONG RAW can contain up to 2 gigabytes of binary data. RAW and LONG
RAW data cannot be indexed and can not be displayed or queried in SQL*Plus.
Only one RAW column is allowed per table.
LOB - Large Object data types. These include BLOB (Binary Large
OBject) and CLOB (Character Large OBject). More than one LOB column can appear
in a table. These data types are the prefferred method for storing large objects
such as text documents (CLOB), images, or video (BLOB).
A column may be specified as NULL or
NOT NULL meaning the column may or may not be left blank,
respectively. This check is made just before a new row is inserted
into the table. By default, a column is created as NULL if no
option is given.
In addition to specifying NOT NULL constraints, tables
can also be created with constraints that enforce
referential integrity (relationships among data
between tables). Constraints can be added to
one or more columns, or to the entire table.
Each table may have one PRIMARY KEY that consists
of a single column containing no NULL values and
no repeated values. A PRIMARY KEY with multiple
columns can be identified using the ALTER TABLE
command.
Up to 255 columns may be specified per table.
Column names and table names must start with a letter
and may not contain spaces or other punctuation except for
the underscore character. Column names and table names
are case insensitive. This means that you can specify the
names of columns and tables in any way you like. For
example, the following three SELECT statements are
all identical:
SELECT lname, fname, address FROM employee;
SELECT LNAME, FNAME, ADDRESS FROM EMPLOYEE;
SELECT Lname, Fname, Address FROM Employee;
In the following example, a new table called ``employee'' is created
with ten columns of a variety of types. The columns indicated by
NOT NULL will be mandatory while the other columns, by default,
will be optional.
The numbers 2 through 11 before each line indicate the
line number supplied by the SQL*Plus program as this
statement was typed in. We will omit these numbers
in the rest of the examples to facilitate copying
and pasting this material directly into a live SQL*Plus
session.
A new table can also be created with a subset of the columns in an
existing table. In the following example, a new table called
emp_department_1 is created with only the
fname, minit, lname and bdate
columns from the employee table. This new table is
also populated with data from the employee table
where the employees are from department number 1.
SQL> CREATE TABLE emp_department_1
AS SELECT fname, minit, lname, bdate
FROM employee
WHERE dno = 1 ;
Table created.
SQL> DESCRIBE emp_department_1
Name Null? Type
------------------------------- -------- ----
FNAME VARCHAR2(8)
MINIT VARCHAR2(2)
LNAME VARCHAR2(8)
BDATE DATE
SQL>
One can also create a new table with all of the columns
from the original table, but with only a subset of
the rows form the original table:
SQL> CREATE TABLE high_pay_emp
AS SELECT *
FROM employee
WHERE salary > 50000;
Table created.
DESCRIBE is an SQL*Plus command that displays the columns of a
table and their data types. The syntax for the DESCRIBE command
is:
DESCRIBE <TABLE NAME>;
The copying of data can be suppressed by giving a WHERE clause that
always evaluates to FALSE for each record in the source table. The
following example makes a duplicate of the employee
table but does not copy any data into it.
SQL> CREATE TABLE copy_of_employee
AS SELECT *
FROM employee
WHERE 3=5;
Table created.
SQL> DESCRIBE copy_of_employee
Name Null? Type
------------------------------- -------- ----
FNAME VARCHAR2(8)
MINIT VARCHAR2(2)
LNAME VARCHAR2(8)
SSN NOT NULL VARCHAR2(9)
BDATE DATE
ADDRESS VARCHAR2(27)
SEX VARCHAR2(1)
SALARY NOT NULL NUMBER(7)
SUPERSSN VARCHAR2(9)
DNO NOT NULL NUMBER(1)
Constraints can be added to the table at the time it is
created, or at a later time using the ALTER TABLE
statement. Constraints can include:
Here is an example of creating a primary key
constraint on the empid column:
CREATE TABLE employee
(fname VARCHAR2(8),
minit VARCHAR2(2),
lname VARCHAR2(8),
ssn VARCHAR2(9) NOT NULL,
bdate DATE,
address VARCHAR2(27),
sex VARCHAR2(1),
salary NUMBER(7) NOT NULL,
superssn VARCHAR2(9),
dno NUMBER(1) NOT NULL,
CONSTRAINT pk_emp PRIMARY KEY (ssn) );
Referential integrity constraints can also be added.
In the following example, the dno
column in the employee table references the
dnumber column in the
department table. If a department is deleted,
all employees that reference the department are also
deleted. This is given by the ON DELETE CASCADE
option:
CREATE TABLE department
(dnumber NUMBER(1),
dname VARCHAR2(15),
mgrssn VARCHAR2(9),
mgrstartdate DATE
CONSTRAINT pk_department PRIMARY KEY (dnumber) );
CREATE TABLE employee
(fname VARCHAR2(8),
minit VARCHAR2(2),
lname VARCHAR2(8),
ssn VARCHAR2(9) NOT NULL,
bdate DATE,
address VARCHAR2(27),
sex VARCHAR2(1),
salary NUMBER(7) NOT NULL,
superssn VARCHAR2(9),
dno NUMBER(1) NOT NULL,
CONSTRAINT pk_emp PRIMARY KEY (ssn),
CONSTRAINT fk_dno FOREIGN KEY (dno)
REFERENCES department (dnumber) ON DELETE CASCADE);
In order to specify a foreign key constraint, the
column in the child (or detail) table (e.g., the dnumber
column in the department
table in the above example) must be either the
primary key or a unique key for the table. Thus,
the child (or detail) table must be created first
before the parent (or master) table is created using
the above constraints.
Additional CREATE TABLE constraint statements allow
the specification of what should happen when a row is
deleted or updated in a parent table. In the above example,
deleting a department causes all employees in that department
to also be deleted. Other options include
ON DELETE SET DEFAULT and
ON DELETE SET NULL. In addition, the behavior
of child tables when a parent table is updated can
also be specified using an ON UPDATE
clause.
CHECK constraints can be added to check the values
for a given column. This can be used to allow
only a specific set of valid values for a column.
In the following example, CHECK constraints are
added to limit the valid values for the sex
column and to check if the salary is greater than 10,000
(be sure to DROP TABLE employee before you try the next
one).
CREATE TABLE employee
(fname VARCHAR2(8),
minit VARCHAR2(2),
lname VARCHAR2(8),
ssn VARCHAR2(9) NOT NULL,
bdate DATE,
address VARCHAR2(27),
sex VARCHAR2(1)
CONSTRAINT ck_sex CHECK (sex IN ('M', 'F')),
salary NUMBER(7) NOT NULL
CONSTRAINT ck_salary CHECK (salary > 10000),
superssn VARCHAR2(9),
dno NUMBER(1) NOT NULL,
CONSTRAINT pk_emp PRIMARY KEY (ssn),
CONSTRAINT fk_dno FOREIGN KEY (dno)
REFERENCES department (dnumber) ON DELETE CASCADE);
The CHECK constraints are activated when inserting a
new row or when updating existing data. In the following
example, the value given for sex is 'm':
SQL> insert into employee values
2 ('Joe', 'M', 'Smith', '123456789', '01-JUN-45',
3 '123 Smith St.', 'm', 45000, '123456789', 1) ;
insert into employee values
*
ERROR at line 1:
ORA-02290: check constraint (MY_INSTANCE.CK_SEX) violated
In the previous examples, constraints were given names with
the following prefixes:
Primary key constraints: pk_
Foreign key constraints: fk_
Check constraints: ck_
Naming constraints in this fashion is simply a
convenience. Any name may be given to a constraint.
The ALTER TABLE command can be used to add a new column to an
existing table or to change the data type of an existing column.
The following examples add a new column manager
to an existing table named emp_department_1
and then modify the data type of the fname column.
SQL> DESCRIBE emp_department_1
Name Null? Type
------------------------------- -------- ----
FNAME VARCHAR2(8)
MINIT VARCHAR2(2)
LNAME VARCHAR2(8)
BDATE DATE
SQL> ALTER TABLE emp_department_1
ADD (manager VARCHAR2(8)) ;
Table altered.
SQL> ALTER TABLE emp_department_1
MODIFY (fname VARCHAR2(15));
Table altered.
SQL> DESCRIBE emp_department_1
Name Null? Type
------------------------------- -------- ----
FNAME VARCHAR2(15)
MINIT VARCHAR2(2)
LNAME VARCHAR2(8)
BDATE DATE
MANAGER VARCHAR2(8)
The ALTER TABLE command can also be used to change the
datatype of column provided there is no data in the
table. To get around this if there is data in
the table, create a temporary table using all of
the data from the existing table, delete the existing
records from the original table, alter the datatype,
and then insert the records from the temporary table
back into the original table. For example, assume
the emp_department_1 table has some records in
it and we want to change the datatype for the MANAGER
column:
CREATE TABLE temp AS SELECT * FROM emp_department_1;
DELETE FROM emp_department_1;
ALTER TABLE emp_department_1
MODIFY (manager VARCHAR2(15));
INSERT INTO emp_department_1
SELECT * FROM temp;
DROP TABLE temp;
This trick can also be used to drop a column from
a table. Assume the Employee table has the following columns:
fname, minit, lname, ssn, bdate, address,
sex, salary, superssn and dno,
and we want to drop the salary column from the table:
CREATE TABLE temp AS
SELECT fname, minit, lname, ssn, bdate,
address, sex, superssn, dno FROM employee;
DROP TABLE employee;
CREATE TABLE employee AS
SELECT * FROM temp;
The DROP TABLE command can be used to drop a table definition and
all of its data from the database. In the following example, the
table emp_department_1 created previously, is
dropped from the database.
SQL> DROP TABLE emp_department_1 ;
Table dropped.
Exercise 1: Creating and Altering Tables
As an exercise, create a table called STUDENTS with
the following columns and data types:
Column Name Data Type
StudentID NUMBER(5,0) NOT NULL
Name VARCHAR2(25)
Major VARCHAR2(15)
GPA NUMBER(6,3)
Create another table called COURSES with the
following columns and data types:
Column Name Data Type
StudentID NUMBER(5,0) NOT NULL
CourseNumber VARCHAR2(15) NOT NULL
CourseName VARCHAR2(25)
Semester VARCHAR2(10)
Year NUMBER(4,0)
Grade VARCHAR2(2)
Use the DESCRIBE command to display the data types
of the columns after each table is created.
Next, use the ALTER TABLE statement to add the
following column to the STUDENTS table:
Column Name Data Type
TutorID NUMBER(5,0)
Use the ALTER TABLE statement to define the
StudentID as the PRIMARY KEY for the STUDENTS
table.
Use the ALTER TABLE statement to define the
StudentID and CourseNumber as the PRIMARY KEY
for the COURSES table. To do this, list both
of the column names separated by a comma.
Use the ALTER TABLE statement to define
StudentID in the COURSES table as a FOREIGN KEY
that references the StudentID in the STUDENTS table.
Finally, add some data to the STUDENTS and COURSES tables
(simply copy and paste these statements into SQL*Plus
to add the data):
INSERT INTO students VALUES (101, 'Bill', 'CIS', 3.45, 102);
INSERT INTO students VALUES (102, 'Mary', 'CIS', 3.10, NULL);
INSERT INTO students VALUES (103, 'Sue', 'Marketing', 2.95, 102);
INSERT INTO students VALUES (104, 'Tom', 'Finance', 3.5, 106);
INSERT INTO students VALUES (105, 'Alex', 'CIS', 2.75, 106);
INSERT INTO students VALUES (106, 'Sam', 'Marketing', 3.25, 103);
INSERT INTO students VALUES (107, 'Jane', 'Finance', 2.90, 102);
INSERT INTO courses VALUES (101, 'CIS3400', 'DBMS I', 'FALL', 1997, 'B+');
INSERT INTO courses VALUES (101, 'CIS3100', 'OOP I', 'SPRING', 1999, 'A-');
INSERT INTO courses VALUES (101, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
INSERT INTO courses VALUES (102, 'CIS3400', 'DBMS I', 'SPRING', 1997, 'A-');
INSERT INTO courses VALUES (102, 'CIS3500', 'Network I', 'SUMMER', 1997, 'B');
INSERT INTO courses VALUES (102, 'CIS4500', 'Network II', 'FALL', 1997, 'B+');
INSERT INTO courses VALUES (103, 'MKT3100', 'Advertizing', 'SPRING', 1998, 'A');
INSERT INTO courses VALUES (103, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
INSERT INTO courses VALUES (103, 'MKT4100', 'Marketing II', 'SUMMER', 1998, 'A-');
Creating and Dropping Indexes
An index is a data structure that afford rapid lookup
of data in a table. An index is normally created on
those columns of a table used to look up data. For
example, in the employee table, the key ssn
can be used to look up the rest of an employee's
information. Creating a index on the ssn
field would be accomplished by the following statement:
SQL> CREATE INDEX employee_ssn_idx
ON employee (ssn);
Index created.
It is also possible to create indexes on other
columns of a table. For example, if the employee
table is frequently accessed by superssn, an index
can be created on that column as well:
SQL> CREATE INDEX employee_superssn_idx
ON employee (superssn);
Index created.
Indexes can be dropped using the DROP INDEX statement:
For example, to drop just the employee_superssn_idx
index, one could submit:
DROP INDEX employee_superssn_idx ;
Index Dropped.
Note that dropping a table (using the DROP TABLE statement)
automatically drops all indexes on that table.
Exercise 2: Creating and Altering Tables
For this exercise, create an index on the STUDENTS
table for the Name column. Be sure to give
this index an appropriate name.
Create an index on the COURSES table for the
semester and year columns (together).
Creating and Dropping Views
In the SQL language, a view is a representation of one or more
tables A view can be used to hide the complexity of relationships
between tables or to provide security for sensitive data in tables.
In the following example, a limited view of the
employee table is
created. When a view is defined, a SQL statement is associated
with the view name. Whenever the view is accessed, the SQL
statement will be executed.
In the following example, the view
emp_dno_1 is created as a limited
number of columns (fname, lname, dno)
and limited set of data ( WHERE dno=1 ) from
the employee table.
CREATE OR REPLACE FORCE VIEW emp_dno_1
AS SELECT fname, lname, dno
FROM employee
WHERE dno = 1;
View created.
Once the view is created, it can be queried with
a SELECT statement as if it were a table.
SELECT * FROM emp_dno_1;
FNAME LNAME DNO
-------- -------- ---------
JAMES BORG 1
Views can be dropped in a similar fashion to tables. The DROP VIEW
command provides this facility. In the following example, the view
just created is dropped.
DROP VIEW emp_dno_1;
View dropped.
Views can also be created to join several tables
together. The following is an example of creating a
view that joins two tables:
SQL> CREATE VIEW dept_managers AS
2 SELECT dnumber, dname, mgrssn, lname, fname
3 FROM employee, department
4 WHERE employee.ssn = department.mgrssn ;
View created.
SQL> SELECT * FROM dept_managers;
DNUMBER DNAME MGRSSN LNAME FNAME
------- --------------- ---------- -------- --------
5 RESEARCH 333445555 WONG FRANKLIN
4 ADMINISTRATION 987654321 WALLACE JENNIFER
1 HEADQUARTERS 888665555 BORG JAMES
This view can then be used as part of other queries
or as the basis for developing applications.
As a final example, a view can be created that
contains an aggregate function. In the following
example, a view is created that returns the
average salary of all employees per department.
SQL> CREATE VIEW dept_average_salary AS
2 SELECT dnumber, dname, AVG(salary) AS average_salary
3 FROM department, employee
4 WHERE employee.dno = department.dnumber
5 GROUP BY dnumber, dname;
View created.
SQL> SELECT * FROM dept_average_salary;
DNUMBER DNAME AVERAGE_SALARY
---------- --------------- --------------
1 HEADQUARTERS 55000
4 ADMINISTRATION 31000
5 RESEARCH 33250
Note the use of the column alias AS average_salary
and the mandatory GROUP BY clause.
Note that in general, views are read-only as in the
above cases.
To see which views are defined in a schema, submit
a query to the USER_VIEWS view:
SQL> SELECT view_name FROM user_views;
VIEW_NAME
--------------------------
DEPT_AVERAGE_SALARY
DEPT_MANAGERS
EMP_DNO_1
Exercise 3: Creating Views
For this exercise, create a view called
V_CIS_MAJORS basd upon the following
SQL SELECT statement:
SELECT * FROM students WHERE major = 'CIS';
Query the view and show the output.
Create another view called V_COURSES_TAKEN based
upon the following SQL SELECT statement:
SELECT name, major, coursenumber, coursename,
semester, year, grade
FROM students, courses
WHERE students.studentid = courses.studentid;
Before querying this view, format the output
column by submitting the following SQL*Plus
COLUMN FORMAT commands:
COLUMN name FORMAT A8
COLUMN coursename FORMAT A15
COLUMN major FORMAT A10
COLUMN year FORMAT 9999
As discussed in Section 5.2,
the format command changes the way data is displayed
in SQL*Plus. It does not change how the data is stored in the tables.
Query the V_COURSES_TAKEN view and show the output.
Creating, Altering and Dropping Sequences
The Oracle database provides a database object known as a Sequence.
Sequences are used to automatically generate a series of unique
numbers such as those used for Employee Id or Part Number columns.
Sequences are not part of the ANSI SQL-92 standard. In the
following example, an Oracle Sequence for Employee Id is
created. The numbers to be generated will be between 1001 and
9999. As a rule of thumb, sequences can be named with the
suffix seq to differentiate them from other
database objects.
In this example, the sequence will begin its
numbering at 1 and count up (in increments of 1
which is the default) until it reaches 9999. Once
the MAXVALUE is reached, accessing the sequence will
return an error.
Sequences are accessed using a SELECT statement with
a special table called DUAL. The
DUAL table is a placeholder that exists
in all schemas by default. In the following example,
the next value in the employee_id_seq
sequence is retrieved:
SELECT department_number_seq.nextval FROM dual;
NEXTVAL
---------
6
Sequences can also be used in INSERT statements to
automatically provide the next value for a key.
For example, to insert a new employee row with the
next employee id in the sequence, the following
statement would be issued:
INSERT INTO department VALUES
(department_number_seq.nextval, 'Finance',
'123456789', '01'-JAN-90');
1 Row Created.
As with most database objects, Oracle Sequences can be dropped
using a DROP SEQUENCE command. Dropping a sequence and then
re-creating it has the effect of resetting the sequence to its
START WITH number. In the following example, the Employee Id
sequence created previously is dropped.
DROP SEQUENCE department_number_seq;
Sequence dropped.
Sequences can also be altered to change the INCREMENT BY,
MAXVALUE or START WITH values. The ALTER SEQUENCE
statement achieves these changes.
Exercise 4: Working with Sequences
For this exercise, start by creating an Oracle Sequence
called student_id_seq. Have the sequence start
numbering at 120 and go up to 999.
Then, write an SQL INSERT statement to insert a
new record for the following person:
Name: Joe
Major: CIS
GPA: 3.85
TutorID: 103
Use the student_id_seq.nextval as the StudentID.
Finally, use a SELECT statement to query the
V_CIS_MAJOR and see if the record was inserted properly.
4.2.2 Grant and Revoke Statements
The GRANT and REVOKE statements allow a user to control
access to objects (Tables, Views, Sequences, Procedures, etc.)
in their schema.
The Grant command grants authorization for a subject
(another user or group) to perform some action
(SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX) on
an object (Table, View, stored procedure, sequence
or synonym).
The actions are defined as follows:
SELECT - allows a subject to select rows from the object.
INSERT - allows a subject to insert rows into the object.
UPDATE - allows a subject to update rows in the object.
DELETE - allows a subject to delete rows from the object.
ALTER - allows a subject to alter the object. For example,
add a column or change a constraint.
INDEX - allows a subject to create an index on the object.
EXECUTE - allows a subject to execute a stored procedure or trigger.
In addition to objects such as tables, the SELECT and
UPDATE actions may also be granted on individual
columns in a table or view.
The general syntax for the GRANT statement is:
GRANT <ACTION1>, <ACTION2>, ...
ON tablename
TO subject;
For example, assume user ALICE wishes to allow
another user BOB to view the rows in the employee table.
ALICE would execute the following GRANT statement:
GRANT SELECT
ON employee
TO BOB;
At this point, user BOB may now issue SQL SELECT
statements on the table ALICE.employee. For example,
user BOB may execute:
SELECT * FROM ALICE.employee;
FNAME MI LNAME SSN
-------- -- -------- ---------
JOHN B SMITH 123456789
FRANKLIN T WONG 333445555
ALICIA J ZELAYA 999887777
JENNIFER S WALLACE 987654321
RAMESH K NARAYAN 666884444
JOYCE A ENGLISH 453453453
AHMAD V JABBAR 987987987
JAMES E BORG 888665555
etc.
The REVOKE statement reverses the authorization
by removing privileges from a subject (user). The
syntax for REVOKE is:
REVOKE <ACTION>
ON
<OBJECT>
FROM <SUBJECT>
For example, to revoke Bob's privileges to
read the employee table, Alice might execute:
REVOKE SELECT
ON employee
FROM BOB;
The current authorizations in effect can be viewed
by selecting from the USER_TAB_PRIVS view. In the
following example, the columns are first formatted
(more examples of this are given in a later section),
and then the privileges for the user (table owner)
ALICE are displayed.
COLUMN grantee FORMAT A10
COLUMN grantor FORMAT A10
COLUMN owner FORMAT A10
COLUMN table_name FORMAT A10
COLUMN privilege FORMAT A10
SELECT * FROM USER_TAB_PRIVS;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA
---------- ---------- ---------- ---------- ---------- ---
BOB ALICE EMPLOYEE ALICE SELECT NO
A quick way to generate a list of GRANT statements for
every table in your schema is to run a query on the
catalog that forms the GRANT statements:
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON '
|| table_name || ' TO username;'
FROM cat
WHERE table_type = 'TABLE';
In the above example, username is the
name of the user you would like to grant access
to.
The result of this query is something like the following:
GRANT SELECT, INSERT, UPDATE, DELETE ON DEPARTMENT TO username;
GRANT SELECT, INSERT, UPDATE, DELETE ON DEPENDENT TO username;
GRANT SELECT, INSERT, UPDATE, DELETE ON DEPT_LOCATIONS TO username;
GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE TO username;
GRANT SELECT, INSERT, UPDATE, DELETE ON PROJECT TO username;
GRANT SELECT, INSERT, UPDATE, DELETE ON WORKS_ON TO username;
This output can then be copied and pasted back in to
put the grant statements into effect.
Exercise 5: GRANT and REVOKE
GRANT SELECT privileges to another member of your group. Have them query your STUDENTS table.
Then REVOKE the SELECT privilege from the
STUDENTS table. Have your group member try to
query the table after you have revoked access
and see what happens.
4.2.3 Synonyms
In many cases, a schema is created under a
single username but other users must have access
to the tables, sequences and stored
procedures. One possibility is to code
all queries and applications to specifically
access these database objects by providing a
schema name. For example;
SELECT * FROM alice.employee;
This query selects all of the columns and
rows from the employee table in user alice's schema.
One problem with this method is that if the
tables move to another user's schema, all
of the references will need to change.
An alternative is to use Synonyms to
provide a pointer to the schema and database objects.
A Synonym is like a pointer in that is has a name
that is recognized in the local schema that,
when addressed, will resolve to the schema.object
name in another user's schema.
Synonyms are created with the CREATE SYNONYM command:
CREATE SYNONYM <SYNONYM_NAME> FOR <SCHEMA>.<OBJECT>;
One can create synonyms for tables, views, sequences and stored procedures.
For example, if Bob wishes to have access to
Alice's employee table, first, Alice would
need to GRANT access to her table using the GRANT
command, and then Bob would create a synonym
using:
CREATE SYNONYM employee FOR alice.employee;
Now Bob can execute the following query:
SELECT * FROM employee;
The above query will return all columns
and rows from the employee table in Alice's
schema.
If the tables are moved to another schema such as
Abe's schema, then only the synonyms need to
be dropped and recreated. All applications
will run the same.
To generate a list of CREATE SYNONYM statements,
use the following type of query:
SELECT 'CREATE SYNONYM ' || table_name ||
' FOR schema.' || table_name || ' ;'
FROM cat
WHERE table_type = 'TABLE';
Where schema is the username containing
the tables. This results in the following output
that can be pasted back into SQL*Plus by each
user to create the set of synonyms:
CREATE SYNONYM DEPARTMENT FOR schema.DEPARTMENT ;
CREATE SYNONYM DEPENDENT FOR schema.DEPENDENT ;
CREATE SYNONYM DEPT_LOCATIONS FOR schema.DEPT_LOCATIONS ;
CREATE SYNONYM EMPLOYEE FOR schema.EMPLOYEE ;
CREATE SYNONYM PROJECT FOR schema.PROJECT ;
CREATE SYNONYM STUDENTS FOR schema.STUDENTS ;
CREATE SYNONYM WORKS_ON FOR schema.WORKS_ON ;
In this section, the SQL commands for creating, altering and
deleting tables, views and sequences, and granting and
revoking access to database objects have been introduced. A
typical database may have a dozen or more related tables with
several columns each. To facilitate the creation and deletion
of a large number of tables, the CREATE statements can be
placed into a file and executed using the SQL*Plus START
command.
4.3 SQL Data Manipulation Language
In this section, we discuss SQL statements that can
be used to manipulate data in tables and views.
SQL*Plus allows the user to enter SQL statements to select,
insert, update and delete rows in database tables. These
are all standard SQL statements.
COMMIT - Make all recent changes to the database permanent.
Changes that have occurred since the last commit are made
permanent. A commit can be done explicitly using the following
syntax:
COMMIT;
A commit is also done implicitly when the next SQL statement is
executed or the user exits SQL*Plus.
DELETE - Delete one or more rows from a table. The syntax for this SQL statement is:
DELETE FROM
<TABLE NAME>
WHERE <WHERE CLAUSE>
If the WHERE clause is omitted, all rows in the table will be
deleted.
INSERT - Insert a row of data into a table. The syntax for
this SQL statement is:
If a value for each column in the table is supplied, then the
columns do not need to be listed in the first set of parenthesis.
Values can be of 3 types: Character, Number or Date. Each one
requires a slightly different format when inserting:
Character - Must be enclosed within single quotes
For example: 'Bill Smith'
Number - No quotes are required
For example: 123, 44000.12
Date - Enclosed in single quotes in the format 'DD-MON-YY'
For example: '26-JUN-96'
All values, regardless of data type, must be separated by commas.
Another option for the INSERT statement is to pull some
data from another table. The syntax is:
INSERT INTO
<TABLE NAME>
SELECT <COLUMNS>
FROM<TABLE>
WHERE <WHERE-CLAUSE>
For example, assume table1 and table2 have the same
number of columns and the corresponding columns
have the same data types. To insert all data currently in
table1 into table2:
INSERT INTO table2
SELECT * FROM table1;
ROLLBACK - Undo all recent changes to the database. A
rollback can only undo changes made since the last commit. The
syntax for the ROLLBACK command is:
ROLLBACK;
SELECT - Retrieve existing rows from a table. If the
table is empty, a message indicating that no rows were
found will be displayed. A simplified syntax for the SELECT
statement is:
SELECT <COLUMN1, COLUMN2, . . .>
FROM <TABLE1, TABLE2, . . .>
WHERE <WHERE CLAUSE>
GROUP BY <COLUMN1, COLUMN2, . . .
HAVING <HAVING CLAUSE>
ORDER BY <COLUMN1, COLUMN2, . . .>
The WHERE clause, GROUP BY, HAVING and ORDER BY statements
are optional. If a WHERE
clause if omitted, all rows in the table will be retrieved. If the
ORDER BY statement is omitted, there is no specific order in which
the rows will be displayed. GROUP BY and HAVING are used in
conjunction with aggregate functions (functions that operate
on more than one record). If all columns in the table are to be
retrieved, an asterisk (*) may be substituted for the entire
list of columns after the SELECT key word.
More than one table can be specified in the FROM clause.
The WHERE clause typically contains logic expressions
(such as WHERE salary > 40000) that are
evaluated for each row in the table.
A more complete syntax for the SELECT statement is:
SELECT <COLUMN1, COLUMN2, . . .>
FROM <SCHEMA.TABLE1, SCHEMA.TABLE2, . . .> | <VIEW>
WHERE <WHERE CLAUSE>
CONNECT BY <CONNECT BY expression>
GROUP BY <GROUP BY expression>
HAVING <HAVING CLAUSE>
ORDER BY <COLUMN1, COLUMN2, ....> ASC | DESC
UPDATE - Change the values of existing rows in a table in the
database. One or more rows must exist in the table in order to
successfully update data. The syntax for this SQL statement is:
UPDATE
<TABLE NAME>
SET <COLUMN NAME> = <EXPRESSION>
WHERE <WHERE CLAUSE>
The expression can be either a single value or an arithmetic
expression including another column in the table. More than one
column can be updated at a time by adding additional columnname = expression pairs separated by commas.
If the WHERE clause is omitted, the update is applied to all
rows in the table.
For example, to give all employees in the marketing
department a 3% raise:
UPDATE employee
SET salary = salary * 1.03
WHERE dno =
(SELECT dno
FROM department
WHERE dname = 'MARKETING');
In the following example, a new row is inserted into the
employee table. Since a value is supplied
for each column, the columns do not need to be explicitly
listed.
DESCRIBE employee;
Name Null? Type
------------------------------- -------- ----
FNAME VARCHAR2(8)
MINIT VARCHAR2(2)
LNAME VARCHAR2(8)
SSN NOT NULL NUMBER
BDATE DATE
ADDRESS VARCHAR2(27)
SEX VARCHAR2(1)
SALARY NOT NULL NUMBER(7)
SUPERSSN NUMBER(9)
DNO NOT NULL NUMBER(1)
INSERT INTO employee
VALUES ('JOHN', 'B', 'SMITH', 123456789, '09-JAN-55',
'731 FONDREN, HOUSTON, TX', 'M', 30000, 333445555, 5) ;
1 row created.
To check the contents of the employee
table, a SELECT statement is done on the table.
SELECT * FROM EMPLOYEE;
FNAME MI LNAME SSN BDATE ADDRESS S SALARY SUPERSSN DNO
-------- -- -------- --------- --------- ------------------------- - ------ --------- ---
JOHN B SMITH 123456789 09-JAN-55 731 FONDREN, HOUSTON, TX M 30000 333445555 5
FRANKLIN T WONG 333445555 08-DEC-45 638 VOSS,HOUSTON TX M 40000 888665555 5
ALICIA J ZELAYA 999887777 19-JUL-58 3321 CASTLE, SPRING, TX F 25000 987654321 4
JENNIFER S WALLACE 987654321 20-JUN-31 291 BERRY, BELLAIRE, TX F 43000 888665555 4
RAMESH K NARAYAN 666884444 15-SEP-52 975 FIRE OAK, HUMBLE, TX M 38000 333445555 5
JOYCE A ENGLISH 453453453 31-JUL-62 5631 RICE, HOUSTON, TX F 25000 333445555 5
AHMAD V JABBAR 987987987 29-MAR-59 980 DALLAS, HOUSTON, TX M 25000 987654321 4
JAMES E BORG 888665555 10-NOV-27 450 STONE, HOUSTON, TX M 55000 1
8 rows selected.
In the next example, a row in the employee
table is updated.
UPDATE employee
SET salary = salary * 1.04
WHERE dno = 4;
3 rows updated.
SELECT * FROM employee;
FNAME MI LNAME SSN BDATE ADDRESS S SALARY SUPERSSN DNO
-------- -- -------- --------- --------- ------------------------- - ------ --------- ---
JOHN B SMITH 123456789 09-JAN-55 731 FONDREN, HOUSTON, TX M 30000 333445555 5
FRANKLIN T WONG 333445555 08-DEC-45 638 VOSS,HOUSTON TX M 40000 888665555 5
ALICIA J ZELAYA 999887777 19-JUL-58 3321 CASTLE, SPRING, TX F 26000 987654321 4
JENNIFER S WALLACE 987654321 20-JUN-31 291 BERRY, BELLAIRE, TX F 44720 888665555 4
RAMESH K NARAYAN 666884444 15-SEP-52 975 FIRE OAK, HUMBLE, TX M 38000 333445555 5
JOYCE A ENGLISH 453453453 31-JUL-62 5631 RICE, HOUSTON, TX F 25000 333445555 5
AHMAD V JABBAR 987987987 29-MAR-59 980 DALLAS, HOUSTON, TX M 26000 987654321 4
JAMES E BORG 888665555 10-NOV-27 450 STONE, HOUSTON, TX M 55000 1
8 rows selected.
In the final example, a row is deleted from the
employee table.
SQL> DELETE FROM employee
WHERE dno = 5;
4 rows deleted.
SQL> COMMIT;
Commit complete.
In the final example, if the ROLLBACK command was given instead of
the COMMIT command, the rows would have been undeleted.
4.3.2 Displaying Table Metadata (Data about the data)
Once database objects have been created, it is often useful
to query the data dictionary to see the various
characteristics of the objects. In this section, we
describe several ways to query the data dictionary
to retrieve this information.
Note that many of these statements and commands
will not work properly under Personal Oracle Lite.
The Oracle Data Dictionary maintains a collection of
USER_ views that are accessible from each user's
schema. The following table summarizes these views:
USER View
Contents
Typical Query
USER_TABLES
Table names and storage details about tables a user owns
SELECT table_name FROM USER_TABLES;
CAT or TAB
Brief list of tables and views for a user
SELECT * FROM CAT; or
SELECT * FROM TAB;
COL
Column names and NOT NULL constraints.
SELECT colno, cname, coltype, width, scale, precision, nulls
FROM col
WHERE tname = 'EMPLOYEE'
ORDER BY col.colno;
USER_INDEXES
Indexes defined on tables the user owns
COLUMN table_owner FORMAT A12
SELECT index_name, table_owner, table_name FROM USER_INDEXES ;
USER_VIEWS
View names and view definitions (queries)
a user owns
SELECT view_name, text FROM USER_VIEWS;
USER_SEQUENCES
Sequence definitions and current
values for sequences a user owns
SELECT * FROM USER_SEQUENCES ;
USER_TRIGGERS
Trigger names and definitions
for triggers a user owns
SELECT trigger_name, trigger_body FROM USER_TRIGGERS;
USER_ERRORS
Contains information about the last error that
occurred in a user's schema due to a trigger or
procedure compilation error.
SELECT * FROM USER_ERRORS;
USER_CONSTRAINTS
Constraints on tables a user owns.
Includes column constraints such as NOT NULL,
CHECK and foreign key constraints.
SELECT constraint_name, table_name, search_condition
FROM USER_CONSTRAINTS WHERE table_name = 'EMPLOYEE';
USER_OBJECTS
All database objects a user owns. Includes
tables, views, sequences, indexes, procedures, triggers, etc.
COLUMN object_name FORMAT A35
SELECT object_name, object_type FROM USER_OBJECTS ;
USER_SOURCE
Source code for stored procedures owned by the user.
To see which procedures exist: SELECT DISTINCT NAME from USER_SOURCE;
To see the actual code: SELECT TEXT FROM USER_SOURCE WHERE NAME = 'procedure_name' ORDER BY LINE;
Note: You may have to reduce the ARRAYSIZE variable to avoid
overflowing the bufer. e.g., SET ARRAYSIZE 2
USER_TS_QUOTAS
Quotas on tablespaces accessible to a user.
SELECT * FROM USER_TS_QUOTAS ;
A comprehensive list of user catalog views can be found in
the Oracle Server Reference guide.
Many of the view contain columns of type LONG.
In order to display their content, set the
SQL*Plus variable LONG to a large
number such as 4096 as follows:
SQL> SET LONG 4096
You may have to reduce the ARRAYSIZE variable to avoid
overflowing the bufer. e.g.,
SET ARRAYSIZE 2
To find out the names of tables you have created, use
the system view called CAT in a
SELECT statement: SELECT * FROM cat; .
The following is an example:
SELECT * FROM cat;
TABLE_NAME TABLE_TYPE
------------------------------ ----------
EMPLOYEE TABLE
DEPARTMENT TABLE
PROJECT TABLE
DEPENDENTS TABLE
The TAB view was supported in
older versions of Oracle and may not be available
in future releases of Oracle. In that case, try
using the CAT view instead of
TAB.
The column definitions for a table can be
displayed using the DESCRIBE command
in SQL*Plus:
DESCRIBE employee;
Name Null? Type
------------------------------- -------- ----
FNAME VARCHAR2(8)
MINIT VARCHAR2(2)
LNAME VARCHAR2(8)
SSN NOT NULL NUMBER
BDATE DATE
ADDRESS VARCHAR2(27)
SEX VARCHAR2(1)
SALARY NOT NULL NUMBER(7)
SUPERSSN NUMBER(9)
DNO NOT NULL NUMBER(1)
More detailed metadata can be retrieved from
the tables COL and user_constraints.
To get information on columns of a table,
use the following (substitute 'EMPLOYEE'
with the name of the table in question):
SQL> COLUMN coltype FORMAT A10
SQL> COLUMN cname FORMAT A15
SQL> SELECT colno, cname, coltype, width, scale, precision, nulls
FROM col
WHERE tname = 'EMPLOYEE'
ORDER BY col.colno;
COLNO CNAME COLTYPE WIDTH SCALE PRECISION NULLS
----- --------------- ---------- ----- ----- --------- ---------
1 FNAME VARCHAR2 8 NULL
2 MINIT VARCHAR2 2 NULL
3 LNAME VARCHAR2 8 NULL
4 SSN NUMBER 22 NOT NULL
5 BDATE DATE 7 NULL
6 ADDRESS VARCHAR2 27 NULL
7 SEX VARCHAR2 1 NULL
8 SALARY NUMBER 22 0 7 NOT NULL
9 SUPERSSN NUMBER 22 0 9 NULL
10 DNO NUMBER 22 0 1 NOT NULL
10 rows selected.
To see any constraints that are presently
in effect on a table, use the following
(substitute 'EMPLOYEE' with the name of the
table in question):
SQL> COLUMN search_condition FORMAT A21
SQL> SELECT constraint_name, constraint_type,
search_condition, delete_rule
FROM user_constraints
WHERE table_name = 'EMPLOYEE';
CONSTRAINT_N CONSTRAINT_T SEARCH_CONDITION DELETE_RULE
------------ ------------ --------------------- -----------
FK_DNO R CASCADE
SYS_C00886 C EMPID IS NOT NULL
SYS_C00887 C SSN IS NOT NULL
SYS_C00888 C SALARY IS NOT NULL
SYS_C00889 C DNO IS NOT NULL
CK_SEX C sex IN ('M', 'F')
CK_SALARY C salary > 10000
PK_EMP P
A list of Indexes defined on tables in the user's schema can be
displayed by querying the USER_INDEXES table:
Finally, a list of Views the user owns can be displayed
by querying the USER_VIEWS table:
SQL> SET LONG 4096
SQL> SELECT view_name, text FROM USER_VIEWS;
VIEW_NAME
-------------------
TEXT
--------------------------------------------------------------
VACCOUNTS
SELECT c.fname, c.lname, ac.account_number, at.account_typeid,
at.interest_rate, at.minimum_balance,
ac.date_opened, ac.current_balance
FROM customers c, accounts ac, account_types at
WHERE c.customerid = ac.customerid
AND ac.account_typeid = at.account_typeid
V_COURSES_TAKEN
SELECT name, major, coursenumber, coursename,
semester, year, grade
FROM students, courses
WHERE students.studentid = courses.studentid
4.3.3 Oracle Pseudo-Columns
The Oracle implementation of SQL adds several
pseudo columns to each table. These columns
do not exist in a physical table, yet they can be
used in any SQL statement for a variety of purposes.
The following table lists the major pseudo columns:
CURRVAL - Returns the current value of an Oracle sequence.
NEXTVAL - Returns the current value of an Oracle sequence
and then increments the sequence.
LEVEL - The current level in a hierarchy
for a query using STARTWITH and CONNECT BY.
ROWID - An identifier (data file, block and row) for
the physical storage of a row in a table.
ROWNUM - The integer indicating the order in which
a row is returned from a query.
Exercise 6: Dispaying Metadata
For this exercise, query the USER_ tables and display the following metadata:
List the tables in the presently in the schema
List the Indexes
List the Views
For the STUDENT and COURSES tables,
display the columns in each table, their data types
and whether or not they allow NULL values
For the STUDENT and COURSES tables,
display constraints on each table
4.3.4 Oracle SQL Functions
The Oracle implementation of SQL provides a number of
functions that can be used in SELECT statements.
Functions are typically grouped into the following:
Single row functions - Operate on column values
for each row returned by a query.
Group functions - Operate on a collection (group) of rows.
The following is an overview and brief description of
single row functions.x is some number,
s is a string of characters and c is a single character.
Math functions include: ABS (x) - Absolute Value of x CEIL (x) - Smallest integer greater than or equal to x.
COS (x) - Cosine of x FLOOR (x) - Largest integer less than or equal to x.
LOG (x) - Log of x LN (x) - Natural Log of x ROUND (x, n) - Round x to n decimal places to the right of the decimal point. SIN (x) - Sine of x TAN (x) - Tangent of x TRUNC (x, n) - Truncate x to
n decimal places to the right of the decimal point.
Character functions include: CHR (x) - Character for ASCII value x. INITCAP (s) - String s with the first letter
of each word capitalized. LOWER (s) - Converts string s to
all lower case letters. LPAD (s, x) - Pads string s
with x spaces to the left. LTRIM (s) - Removes leading spaces from s. REPLACE (s1, s2, s3) - Replace
occurrences of s1 with s2 in string s. RPAD (s, x) - Pads string s
with x spaces to the right. RTRIM (s) - Removes trailing spaces from s. SUBSTR (s, x1, x2) - Return a
portion of string s starting at position x1
and ending with position x2. If x2 is
omitted, it's value defaults to the end of s. UPPER (s) - Converts string s to all upper case letters.
Character functions that return numbers include: ASCII (c) - Returns the ASCII value of c INSTR (s1, s2, x) - Returns the
position of s2 in s1 where the search
starts at position x. LENGTH (s) - Length of s
Conversion functions include: TO_CHAR (date, format) - Converts
a date column to a string of characters. format
is a set of Date formatting codes where: YYYY is a 4 digit year. NM is a month number. MONTH is the full name of the month. MON is the abbreviated month. DDD is the day of the year. DD is the day of the month. D is the day of the week. DAY is the name of the day. HH is the hour of the day (12 hour clock) HH24 is the hour of the day (24 hour clock) MI is the minutes. SS is the seconds.
TO_CHAR (number, format) - Converts
a numeric column to a string of characters. format
is a set of number formatting codes where: 9 indicates a digit position. Blank if position value is 0. 0 indicates a digit position. Shows a 0 if the position value is 0. $ displays a leading currency indicator. TO_DATE (s, format) - Converts
a character column (string s to a date. format
is a set of Date formatting codes as above. TO_NUMBER (s, format) - Converts
a character column (string s to a Number. format
is a set of Number formatting codes as above.
Date functions include: SYSDATE - Returns the current date (and time if
the TO_CHAR function is used) from the
system clock.
Some additional function are: DECODE (s, search1, result1, search2, result2) -
Compares s with search1, search2, etc.
and returns the corresponding result when there is a match.
NVL (s, expression) - If s is
NULL, return expression. If s is not null,
then return s.
USER - Returns the username of the current user.
The following is an overview and brief description of
multiple row (group) functions.col is the
name of a table column (or expression) of type NUMBER.
AVG (col) - Returns the average of a group of rows for col
MAX (col) - Returns the maximum of a group of rows for col
MIN (col) - Returns the minimum of a group of rows for col
STDEV (col) - Returns the standard deviation of a group of rows for col
SUM (col) - Returns the sum (total) of a group of rows for col
VARIANCE (col) - Returns the variance of a group of rows for col
In addition the COUNT group function counts instances of
values. These values can be any type (CHAR, DATE or NUMBER):
COUNT (columns) - Returns the number of instances
of a group of rows for (columns)
To use an aggregate function, a GROUP BY clause must
be added to the SELECT statement.
Examples of functions are given in the following section.
Exercise 7: Functions
For this exercise, use the various functions to display the following:
Display the average, minimum, and maximum
grade point average for all of the students
For each student, write a sentence like
the following: Congratulations Bill, your grade point average is 3.45
You'll need to use the TO_CHAR function to convert the
GPA column (which is a NUMBER data type) to a set of characters.
For each student, count the number of courses he or she has
taken.
Modify the above query to only count CIS courses.
Hint: You'll need to use the SUBSTR function on
the COURSENUMBER column to extract the first three
letters. Then compare this to 'CIS'.
4.3.5 Examples of SQL DML Statements
In this section, several examples of SQL DML statements
are given. Variations on WHERE clause, FROM clause and
using SQL functions are all demonstrated.
Basic Select Statements
Example Table STUDENTS:
CREATE TABLE students (studentid NUMBER(5,0), name VARCHAR2(25),
major VARCHAR2(15), gpa NUMBER(6,3), tutorid NUMBER(5,0));
INSERT INTO students VALUES (101, 'Bill', 'CIS', 3.45, 102);
INSERT INTO students VALUES (102, 'Mary', 'CIS', 3.10, NULL);
INSERT INTO students VALUES (103, 'Sue', 'Marketing', 2.95, 102);
INSERT INTO students VALUES (104, 'Tom', 'Finance', 3.5, 106);
INSERT INTO students VALUES (105, 'Alex', 'CIS', 2.75, 106);
INSERT INTO students VALUES (106, 'Sam', 'Marketing', 3.25, 103);
INSERT INTO students VALUES (107, 'Jane', 'Finance', 2.90, 102);
Example table COURSES:
CREATE TABLE courses(studentid NUMBER(5,0) NOT NULL,
coursenumber VARCHAR2(15) NOT NULL,
coursename VARCHAR2(25), semester VARCHAR2(10),
year NUMBER(4,0), grade VARCHAR2(2));
INSERT INTO courses VALUES (101, 'CIS3400', 'DBMS I', 'FALL', 1997, 'B+');
INSERT INTO courses VALUES (101, 'CIS3100', 'OOP I', 'SPRING', 1999, 'A-');
INSERT INTO courses VALUES (101, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
INSERT INTO courses VALUES (102, 'CIS3400', 'DBMS I', 'SPRING', 1997, 'A-');
INSERT INTO courses VALUES (102, 'CIS3500', 'Network I', 'SUMMER', 1997, 'B');
INSERT INTO courses VALUES (102, 'CIS4500', 'Network II', 'FALL', 1997, 'B+');
INSERT INTO courses VALUES (103, 'MKT3100', 'Advertizing', 'SPRING', 1998, 'A');
INSERT INTO courses VALUES (103, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
INSERT INTO courses VALUES (103, 'MKT4100', 'Marketing II', 'SUMMER', 1998, 'A-');
StudentID
Name
Major
GPA
TutorId
101
Bill
CIS
3.45
102
102
Mary
CIS
3.1
103
Sue
Marketing
2.95
102
104
Tom
Finance
3.5
106
105
Alex
CIS
2.75
106
106
Sam
Marketing
3.25
103
107
Jane
Finance
2.9
102
Average GPA of all students:
SELECT AVG(gpa)
FROM students;
AVG(GPA)
----------
3.12857143
Average GPA of Finance and CIS students:
SELECT AVG(gpa)
FROM students
WHERE major = 'CIS' OR major = 'Finance';
AVG(GPA)
----------
3.14
Give the name of the student with the highest
GPA:
This is an example of a subquery
SELECT name, gpa
FROM students
WHERE gpa =
( SELECT MAX(gpa) FROM students );
NAME GPA
-------- ----------
Tom 3.5
Another option is to enclose some text in quotes and
concatenate that text with the output of the SQL statement:
SELECT 'The student with the highest GPA is ' || name
FROM students
WHERE gpa =
( SELECT MAX(gpa) FROM students );
NAME
------------------------------------------
The student with the highest grade is Tom
Show the students with the GPA grades in each major:
SELECT name, major, gpa
FROM students s1
WHERE gpa =
(
SELECT max(gpa)
FROM students s2
WHERE s1.major = s2.major
);
NAME MAJOR GPA
-------- ---------- ----------
Bill CIS 3.45
Tom Finance 3.5
Sam Marketing 3.25
Note the two aliases given to the
students table: s1 and s2.
These allow us to refer to different views
of the same table.
You may wish to sort the output based on the GPA.
In this case, the output is ordered by GPA in
decending order (highest GPA will come
first, etc.):
SELECT name, major, gpa
FROM students s1
WHERE gpa =
(
SELECT max(gpa)
FROM students s2
WHERE s1.major = s2.major
)
ORDER BY gpa DESC;
NAME MAJOR GPA
-------- ---------- ----------
Tom Finance 3.5
Bill CIS 3.45
Sam Marketing 3.25
Selecting from 2 or More Tables
In the FROM portion, list all tables
separated by commas. Called a Join.
The WHERE part becomes the Join
Condition
Example table EMPLOYEE:
FNAME MI LNAME SSN BDATE ADDRESS S SALARY SUPERSSN DNO
-------- -- ------- --------- --------- ------------------------- - ------ --------- ---
JOHN B SMITH 123456789 09-JAN-55 731 FONDREN, HOUSTON, TX M 30000 333445555 5
FRANKLIN T WONG 333445555 08-DEC-45 638 VOSS,HOUSTON TX M 40000 888665555 5
ALICIA J ZELAYA 999887777 19-JUL-58 3321 CASTLE, SPRING, TX F 25000 987654321 4
JENNIFER S WALLACE 987654321 20-JUN-31 291 BERRY, BELLAIRE, TX F 43000 888665555 4
RAMESH K NARAYAN 666884444 15-SEP-52 975 FIRE OAK, HUMBLE, TX M 38000 333445555 5
JOYCE A ENGLISH 453453453 31-JUL-62 5631 RICE, HOUSTON, TX F 25000 333445555 5
AHMAD V JABBAR 987987987 29-MAR-59 980 DALLAS, HOUSTON, TX M 25000 987654321 4
JAMES E BORG 888665555 10-NOV-27 450 STONE, HOUSTON, TX M 55000 1
Example table DEPARTMENT:
DNAME DNUMBER MGRSSN MGRSTARTD
--------------- --------- --------- ---------
RESEARCH 5 333445555 22-MAY-78
ADMINISTRATION 4 987654321 01-JAN-85
HEADQUARTERS 1 888665555 19-JUN-71
Example Table DEPT_LOCATIONS:
DNUMBER DLOCATION
------- ---------------
1 HOUSTON
4 STAFFORD
5 BELLAIRE
5 SUGARLAND
5 HOUSTON
Example table DEPENDENT:
ESSN DEPENDENT_NAME SEX BDATE RELATIONSHIP
--------- --------------- --- --------- ------------
333445555 ALICE F 05-APR-76 DAUGHTER
333445555 THEODORE M 25-OCT-73 SON
333445555 JOY F 03-MAY-48 SPOUSE
123456789 MICHAEL M 01-JAN-78 SON
123456789 ALICE F 31-DEC-78 DAUGHTER
123456789 ELIZABETH F 05-MAY-57 SPOUSE
987654321 ABNER M 26-FEB-32 SPOUSE
List all of the employees working in Houston:
SELECT employee.fname, employee.lname
FROM employee, dept_locations
WHERE employee.dno = dept_locations.dnumber
AND dept_locations.dlocation = 'HOUSTON' ;
FNAME LNAME
-------- --------
JOHN SMITH
FRANKLIN WONG
RAMESH NARAYAN
JOYCE ENGLISH
JAMES BORG
List each employee name and the
location they work in. List them in order of location and name:
SELECT dept_locations.dlocation, department.dname,
employee.fname, employee.lname
FROM employee, department, dept_locations
WHERE employee.dno = department.dnumber
AND department.dnumber = dept_locations.dnumber
AND employee.dno = dept_locations.dnumber
ORDER BY dept_locations.dlocation, employee.lname;
Results:
DLOCATION DNAME FNAME LNAME
--------------- --------------- -------- --------
BELLAIRE RESEARCH JOYCE ENGLISH
BELLAIRE RESEARCH RAMESH NARAYAN
BELLAIRE RESEARCH JOHN SMITH
BELLAIRE RESEARCH FRANKLIN WONG
HOUSTON HEADQUARTERS JAMES BORG
HOUSTON RESEARCH JOYCE ENGLISH
HOUSTON RESEARCH RAMESH NARAYAN
HOUSTON RESEARCH JOHN SMITH
HOUSTON RESEARCH FRANKLIN WONG
STAFFORD ADMINISTRATION AHMAD JABBAR
STAFFORD ADMINISTRATION JENNIFER WALLACE
STAFFORD ADMINISTRATION ALICIA ZELAYA
SUGARLAND RESEARCH JOYCE ENGLISH
SUGARLAND RESEARCH RAMESH NARAYAN
SUGARLAND RESEARCH JOHN SMITH
SUGARLAND RESEARCH FRANKLIN WONG
16 rows selected.
What is the highest paid salary in Houston ?
SELECT MAX(employee.salary)
FROM employee, dept_locations
WHERE employee.dno = dept_locations.dnumber
AND dept_locations.dlocation = 'HOUSTON';
MAX(EMPLOYEE.SALARY)
--------------------
55000
To obtain the Cartesian Product of two tables, use a SELECT statement with no WHERE clause:
List the Department name and the total salaries for each department:
SELECT department.dname, SUM( employee.salary )
FROM employee, department
WHERE employee.dno = department.dnumber
GROUP BY department.dname
Results:
DNAME SUM(EMPLOYEE.SALARY)
--------------- --------------------
ADMINISTRATION 93000
HEADQUARTERS 55000
RESEARCH 133000
We can also use a Column Alias to
change the title of the columns
SELECT department.dname, SUM( employee.salary ) AS TotalSalaries
FROM employee, department
WHERE employee.dno = department.dnumber
GROUP BY department.dname
Results:
DNAME TOTALSALARIES
--------------- -------------
ADMINISTRATION 93000
HEADQUARTERS 55000
RESEARCH 133000
Here is a combination of a function and a column alias:
SELECT fname, lname, salary AS CurrentSalary,
(salary * 1.03) AS ProposedRaise
FROM employee;
FNAME LNAME CURRENTSALARY PROPOSEDRAISE
-------- -------- ------------- -------------
JOHN SMITH 30000 30900
FRANKLIN WONG 40000 41200
ALICIA ZELAYA 25000 25750
JENNIFER WALLACE 43000 44290
RAMESH NARAYAN 38000 39140
JOYCE ENGLISH 25000 25750
AHMAD JABBAR 25000 25750
JAMES BORG 55000 56650
8 rows selected.
Recall some of the E-R diagrams and
relations we dealt with had a recursive relationship.
For example: A student can tutor one
or more other students. A student has only one tutor.
STUDENTS (studentid, name, major, grade, student_tutorid)
Provide a listing of each student and the name of their tutor:
SELECT s1.name AS Student, tutors.name AS Tutor
FROM students s1, students tutors
WHERE s1.tutorid = tutors.studentid;
STUDENT TUTOR
------------------------- -----------
Bill Mary
Sue Mary
Jane Mary
Sam Sue
Tom Sam
Alex Sam
The above is called a "recursive" query
because it access the same table two times.
We give the table two aliases called
s1 and tutors so that we can compare
different aspects of the same table.
However, as is, the table is missing
something: We don't see who is tutoring Mary.
Use a left outer join to see the
rest of the information. In MS Access, we use
the LEFT JOIN command. In Oracle, we place a
(+) after the join condition to indicate
an outer join:
In MS Access:
SELECT s1.name AS Student, tutors.name AS Tutor
FROM students s1 LEFT JOIN students tutors
ON s1.tutorid = tutors.studentid;
In Oracle:
SELECT s1.name AS Student, tutors.name AS Tutor
FROM students s1, students tutors
WHERE s1.tutorid = tutors.studentid (+);
STUDENT TUTOR
------------------------- -------------
Bill Mary
Sue Mary
Jane Mary
Sam Sue
Tom Sam
Alex Sam
Mary
Here is one more twist: Suppose we were
interested in those students who do not tutor
anyone? Use a right outer join
(RIGHT JOIN in MS Access).
How many students does each tutor work with ?
SELECT s1.name AS TutorName,
COUNT(tutors.tutorid) AS NumberTutored
FROM students s1, students tutors
WHERE s1.studentid = tutors.tutorid
GROUP BY s1.name;
TUTORNAME NUMBERTUTORED
------------------------- -------------
Mary 3
Sam 2
Sue 1
Another form of recursive query is the tree query.
A tree query decomposes the table such that each
row is a node the tree and nodes are related in
levels. Consider the Students table defined above.
Bill tutors Alex, Mary and Sue.
Mary tutors Liz and Ed
Sue tutors Petra
Using the SQL SELECT statements CONNECT BY
and START WITH clauses, we can form a set
of relationships between the rows of the
table that form a tree structure.
START WITH - indicates which row the
tree should start with.
CONNECT BY - indicates how successive related rows
are to be identified and included in the result.
LEVEL - a pseudo-column that indicates which level of
the tree the current row is assigned to.
The following example prints a tree structure
modeled after the tutoring relationships
in the Students table. We will start with Mary's student id
(102) since no one tutors her.
SELECT LPAD(' ',2*(LEVEL-1)) || students.name
As TutorTree
FROM students
START WITH studentid = '102'
CONNECT BY PRIOR studentid = tutorid;
TUTORTREE
--------------------------------------------------------------------------------
Mary
Bill
Sue
Sam
Tom
Alex
Jane
7 rows selected.
From the tree we can see that Mary tutors Bill, Sue and
Jane. In turn, Sue tutors Sam. Finally, Sam tutors both
Tom and Alex.
There are a number of expressions
one can use in a WHERE clause.
Subqueries using = (equals):
SELECT name, grade
FROM students
WHERE grade =
( SELECT MAX(grade) FROM students );
This assumes the subquery returns only one tuple as a result.
Typically used when aggregate functions are in the subquery.
Subqueries using the IN operator are
used whenever the value of a column should be
found in a set of values. The set of
values can be explicitly listed (as in the
first example) or they can be created on the fly
using a subquery.
SELECT employee.fname, department.dname
FROM employee, department
WHERE employee.dno = department.dnumber
AND department.dname IN ('HEADQUARTERS', 'RESEARCH');
FNAME DNAME
-------- ---------------
JAMES HEADQUARTERS
JOHN RESEARCH
JOYCE RESEARCH
RAMESH RESEARCH
FRANKLIN RESEARCH
SELECT employee.fname
FROM employee
WHERE employee.dno IN
(SELECT dept_locations.dnumber
FROM dept_locations
WHERE dept_locations.dlocation = 'STAFFORD');
FNAME
-------
ALICIA
JENNIFER
AHMAD
In the above case, the subquery returns a set of
tuples. The IN clause returns true when a tuple
matches a member of the set.
Subqueries using EXISTS. EXISTS will return
TRUE if there is at least one row resulting
from the subquery.
SELECT fname, lname, salary
FROM employee
WHERE EXISTS
(SELECT fname
FROM EMPLOYEE e2
WHERE e2.salary > employee.salary)
AND EXISTS
(SELECT fname
FROM EMPLOYEE e3
WHERE e3.salary < EMPLOYEE.SALARY);
FNAME LNAME SALARY
-------- -------- ---------
JOHN SMITH 30000
FRANKLIN WONG 40000
JENNIFER WALLACE 43000
RAMESH NARAYAN 38000
The above query shows all employees names and
salaries where there is at least one person who
makes more money (the first exists) and
at least one person who makes less money
(second exists).
Subqueries with NOT EXISTS. NOT EXISTS will
return TRUE if there are no rows returned
by the subquery.
SELECT fname, lname, salary
FROM employee
WHERE NOT EXISTS
(SELECT fname
FROM EMPLOYEE e2
WHERE e2.salary > employee.salary);
FNAME LNAME SALARY
-------- -------- ---------
JAMES BORG 55000
The above query shows all employees for whom
there does not exist an employee who is
paid less. In other words, the highest
paid employee.
The HAVING clause is similar to the WHERE
clause. The difference is that WHERE is used
to filter individual rows while HAVING
is used to filter groups according to
the GROUP BY clause.
Show the departments with average salary
greater than 33000.
SELECT department.dname, AVG(salary)
FROM employee, department
WHERE employee.dno = department.dnumber
GROUP BY department.dname
HAVING AVG(salary) > 33000 ;
DNAME AVG(SALARY)
--------------- -----------
HEADQUARTERS 55000
RESEARCH 33250
Show departments with 3 or more employees:
SELECT department.dname, COUNT(employee.dno)
FROM department, employee
WHERE department.dnumber = employee.dno
GROUP BY department.dname
HAVING COUNT(employee.dno) >= 3;
DNAME COUNT(EMPLOYEE.DNO)
--------------- -------------------
ADMINISTRATION 3
RESEARCH 4
Text, dates and numbers can be combined
using the various conversion functions.
In the following example, the TO_CHAR
function is used to convert the date
BDATE into a character string.
SELECT 'The oldest employee was born on ' ||
TO_CHAR( MIN(bdate), 'DD/MM/YY')
AS Sentence
FROM employee;
SENTENCE
----------------------------------------
The oldest employee was born on 10/11/27
Date math results in a numerical answer that
must be converted to characters to concatenate with
other character strings as in this next example:
SELECT 'The oldest employee was born on ' ||
TO_CHAR( MIN(bdate), 'DD/MM/YY') || ' and is now' ||
TO_CHAR( (SYSDATE - MIN(bdate)) / 365, '99') ||
' years old.'
AS Sentence
FROM employee;
SENTENCE
-----------------------------------------------------------------
The oldest employee was born on 10/11/27 and is now 70 years old.
The DECODE function can be used to provide a variety of
lookup values. In the following example, the string
concatenation operator || is used to put together
a sentence about each employee. The DECODE command
takes the COUNT of dependents as the first argument.
Then, depending on the COUNT for a given employee, it
returns an appropriate ending to the sentence.
SELECT fname || ' '|| lname || ' has ' ||
DECODE(COUNT(essn),
0, 'no dependents.',
1, 'one dependent.',
2, 'two dependents.',
3, 'three dependents.')
AS Sentence
FROM employee, dependent
WHERE employee.ssn = dependent.essn (+)
GROUP BY employee.fname, lname;
SENTENCE
---------------------------------------
AHMAD JABBAR has no dependents.
ALICIA ZELAYA has no dependents.
FRANKLIN WONG has three dependents.
JAMES BORG has no dependents.
JENNIFER WALLACE has one dependent.
JOHN SMITH has three dependents.
JOYCE ENGLISH has no dependents.
RAMESH NARAYAN has no dependents.
8 rows selected.
With no WHERE clause, DELETE
will remove all tuples from a table.
Remove all employees:
DELETE employee;
Remove only employees making more than $50,000
DELETE employee
WHERE salary > 50000;
Remove all employees working in
Houston:
DELETE employee
WHERE employee.dno IN
(SELECT dept_locations.dnumber
FROM dept_locations
WHERE dlocation = 'HOUSTON');
DELETE will not be successful if
a constraint would be violated.
For example, consider the
DNO attribute in the Employee
table as a Foreign Key.
Removing a department would then be
contingent upon no employees working in
that department.
This is what we call enforcing
Referential Integrity
Change Values using UPDATE
The UPDATE command is used to
change attribute values in the database.
UPDATE uses the SET clause to
overwrite the value.
Change the last name of an Employee:
UPDATE employee
SET lname = 'SMITH'
WHERE lname = 'JONES';
Give an Employee a raise:
UPDATE employee
SET salary = salary * 1.05
WHERE fname = 'JOYCE' AND lname = 'ENGLISH';
Give all employees over the age of 50
a raise:
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.02
WHERE TO_NUMBER( ( SYSDATE - bdate) / 365) >= 50;
Exercise 8: Update and Delete
For this exercise, write the SQL UPDATE and DELETE
statements to:
Add .05 to all of the Marketing major's GPA's.
Change Sam's tutor from Sue to Jane
For any student who is currently majoring in
CIS and who has a GPA of less than 30, change their
major to Marketing.
