Oracle SQL Jumpstart with Examples- P9

Oracle SQL Jumpstart with Examples- P9

Oracle SQL Jumpstart with Examples- P9: Review
"As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...

Nội dung Text: Oracle SQL Jumpstart with Examples- P9

370 17.2 Using XML in Oracle
Figure 17.11
Duplicating Parent
Tags.
later in this chapter. For now all I have done is copy the ﬁrst two rows in
Figure 17.10 and pasted and annotated them into Figure 17.11.
Most relational database interpretation of XML is direct and dumps
rows into two dimensions, as results would appear in row form, such as in
this join.
Note: Two-dimensional data is useful for platform-independent transfer
between multiple databases. However, there are other, faster methods for
achieving this task with Oracle Database.
The beauty of XML is its potential object hierarchical nature, effectively
allowing removal of duplicated data. Figure 17.11 clearly shows that dupli-
cation is present in abundance. What can we do about this? We can use a
function called XMLAGG to aggregate data. In its simplest form,
XMLAGG is limited, because it appears to be capable of descending only
into a single level of a hierarchy. XMLCONCAT does not help either in
this respect because of conﬂict between the aggregation functions and the
GROUP BY clause. The result of the following query as shown in Figure
17.12 is much better than that of Figure 17.11, but it is still not correct, as
can be seen by appropriate annotations in Figure 17.12, because artists
remain duplicated.
SELECT XMLELEMENT("Artist", XMLATTRIBUTES(A.NAME "Name")
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

372 17.2 Using XML in Oracle
The point to make about Figure 17.12 is that all duplication cannot be
removed; thus the duplicated artist tags cannot be removed. The reason
why is as follows: Even if an XMLAGG function could contain another
embedded XMLAGG function, the GROUP BY clause cannot have more
than a single layer. There are alternative methods of solving this multilay-
ered duplication issue. Obviously, other XML generation methods can be
used. Additionally, a CAST(MULTISET(… into a nested table for each
subset may help. Other obvious answers are a FROM clause inline view
embedded subquery and using PL/SQL, which may be the best option.
Another point to make is that if programming languages have to be resorted
to at the second layer of a hierarchy, then something like PL/SQL may be
the better option than SQL/XML. In PL/SQL or another programming
language, the complex query we have been using would be a simple multi-
layered nested cursor procedure, dumping values using the
DBMS_OUTPUT procedure. Therefore, I will not pursue this topic any
further using SQL/XML. See Chapter 24 for details on PL/SQL.
The SYS_XMLGEN function in the next section shows multilayered
capabilities using CAST(MULTISET(… functionality and user-deﬁned
types. I still think PL/SQL might be easier to code.
17.2.1.2.2 The SYS_XMLGEN Function
The SYS_XMLGEN function creates an XML document for each row
read. Unfortunately, this function does not appear to work properly in my
current release of Oracle Database 10g, but this is more or less how it is sup-
posed to work. In general, it passes subset row arrays into subset type arrays
(nested tables).
CREATE OR REPLACE TYPE tSONG AS OBJECT(
TITLE VARCHAR2(64), RECORDING_DATE DATE
, PLAYING_TIME CHAR(10));
/
CREATE OR REPLACE TYPE tSONG_LIST AS TABLE OF tSONG;
/
CREATE OR REPLACE TYPE tARTIST AS OBJECT(
NAME VARCHAR2(32), CITY VARCHAR2(32)
, COUNTRY VARCHAR2(32), SONG_LIST tSONG_LIST);
/
SELECT SYS_XMLGEN(tARTIST(A.NAME, A.CITY, A.COUNTRY,
CAST(MULTISET(SELECT tSONG(S.TITLE
, S.RECORDING_DATE, S.PLAYING_TIME)
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

17.2 Using XML in Oracle 373
FROM SONG S
WHERE S.ARTIST_ID = A.ARTIST_ID)
AS tSONG_LIST))).GETCLOBVAL()
AS ARTISTXML FROM ARTIST A;
Now let’s look at how XML documents can be changed in an Oracle
database.
17.2.2 XML and the Database
In this section we examine XML and Oracle Database in three ways: (1)
creating new XML documents in the database; (2) retrieving XML docu-
ments stored in the database, both in whole and in part; and (3) changing
XML documents stored in the database.
17.2.2.1 New XML Documents
This command creates a table to store XML documents. This same table
creation command has already been shown earlier in this chapter but is
repeated here for convenience.
CREATE TABLE XML (ID NUMBER NOT NULL, XML XMLType
, CONSTRAINT XPK_XML PRIMARY KEY (ID));
There are various methods of adding XML data to a database. In short,
an XML document string can be added as a CLOB object, typecast as
XMLType datatype from a string, or added using XMLELEMENT and
similar SQL/XML functions. The XMLELEMENT function produces an
XMLType datatype. In this case, the query shown following is described by
the XML document shown in Figure 17.12. This INSERT command will
create an XMLType data object in the XML table just created.
INSERT INTO XML(ID,XML)
SELECT CD.MUSICCD_ID, XMLELEMENT("Artist"
, XMLATTRIBUTES(A.NAME "Name")
, XMLFOREST(A.CITY "City", A.COUNTRY "Country")
, XMLELEMENT("CD", XMLATTRIBUTES(CD.TITLE "Title"
, G.GENRE "Genre")
, XMLFOREST(CD.PRESSED_DATE "Released"
, CD.LIST_PRICE "Price")
, XMLAGG(XMLELEMENT("Song", XMLATTRIBUTES(S.TITLE "Title"
Chapter 17
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

374 17.2 Using XML in Oracle
, T.TRACK_SEQ_NO "Track")
, XMLFOREST(S.RECORDING_DATE "Recorded"
, TRIM(S.PLAYING_TIME) "Length")))))
FROM ARTIST A
JOIN SONG S ON(S.ARTIST_ID = A.ARTIST_ID)
JOIN CDTRACK T ON(T.SONG_ID = S.SONG_ID)
JOIN MUSICCD CD ON(CD.MUSICCD_ID = T.MUSICCD_ID)
JOIN GENRE G ON(G.GENRE_ID = CD.GENRE_ID)
GROUP BY CD.MUSICCD_ID, A.NAME, A.CITY, A.COUNTRY, CD.TITLE
, G.GENRE, CD.PRESSED_DATE, CD.LIST_PRICE;
That was easy! Now let’s ﬁnd out how to retrieve XML data.
17.2.2.2 Retrieving from XML Documents
XMLType datatype column values can be retrieved using SQL SELECT
commands, XML extraction functions, and special Oracle text operators.
When extracting CLOB values, the SET LONG command is
required in SQL*Plus in order to show enough of the string value in the
CLOB object. SET LONG 80 is the default and restricts width to 80 char-
acters, which is not much when it comes to XML. Here are four simple
examples for showing entire XML value contents. The ﬁrst two examples
will return the entire XML value in a single row on a single line. The third
and fourth examples will beautify the result, as shown in Figure 17.13. The
fourth example speciﬁcally must have SET LONG applied, other-
wise only one row will be returned.
SET LONG 2000;
SELECT X.XML.GETSTRINGVAL() AS Artist FROM XML X WHERE ID = 4;
SELECT X.XML.GETCLOBVAL() AS Artist FROM XML X WHERE ID = 4;
SELECT X.XML.EXTRACT('/*') AS Artist FROM XML X WHERE ID = 4;
SELECT XML FROM XML WHERE ID = 4;
Now let’s examine how to extract individual pieces from within an XML
document. XML document subset parts are searched for and retrieved
using pattern-matching methods and various functions. Pattern-matching
methods are similar to regular expressions (see Chapter 14). An XML docu-
ment is effectively parsed for speciﬁc strings or tags and then the parts
within the matched patterns are returned. Various standard pattern-match-
ing characters are used for XML subset searches:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

17.2 Using XML in Oracle 375
Figure 17.13
Beautifying
XMLType Datatype
Output.
/. Speciﬁes a root node either as the root of an entire XML tree or a
subtree, and used as a multiple-path speciﬁcation separation charac-
ter. Thus Artist/CD/Song/Length ﬁnds all CDs with a Length tag.
//. Finds all child elements from a speciﬁed root. Therefore, /Artist//
Length ﬁnds once again all CDs with a Length tag.
[ … ]. Used to build predicates within expressions such as /Art-
ist[City="Vienna" or City="Boston"], which ﬁnds all artists resident
in Vienna and Boston.
@. The @ sign is used in XML to access tag attributes. /Artist/
@Name will ﬁnd the name Mozart in the tag .
Before we show some examples, there are several functions we need to
cover in addition to pattern-matching characters already described.
Chapter 17
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

376 17.2 Using XML in Oracle
EXISTSNODE (XMLType object, search path, expression).
Searches for the expression in a path (search path) within an XML
document XMLType object. This function will return 1 if a node
exists.
EXTRACT (XMLType object, search path, expression). As already
seen, the EXISTSNODE function veriﬁes the presence of a string.
The EXTRACT function returns the tag and its contents.
EXTRACTVALUE (XMLType object, search path, expression).
This function ﬁnds the same strings or patterns as the EXTRACT
function except it returns scalar values, as opposed to tags. Therefore,
where the EXTRACT function returns Los Angeles,
the EXTRACTVALUE function returns the value between the City
tags, namely Los Angeles.
Now let’s demonstrate by example. The ﬁrst example ﬁnds the CD iden-
tiﬁer where that CD has at least one Length value
(SONG.PLAYING_TIME) in its structure:
SELECT ID FROM XML WHERE EXISTSNODE(XML
, 'Artist/CD/Song/Length') = 1;
This query will verify the previous query by looking at the data in the
tables. Figure 17.14 shows both of these queries put together.
Figure 17.14
Demonstrating /, //,
and
EXISTSNODE.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

17.2 Using XML in Oracle 377
Figure 17.15
Demonstrating
EXTRACT and
EXTRACTVALUE.
SELECT DISTINCT(MUSICCD_ID) FROM CDTRACK WHERE SONG_ID IN
(SELECT SONG_ID FROM SONG
WHERE PLAYING_TIME IS NOT NULL);
The next example extracts every City tag and the value within every City
tag for all entries in the XML document. The result is shown in Figure
17.15.
COLUMN TAG FORMAT A32
COLUM CITY FORMAT A20
SELECT ID, EXTRACT(XML, '/Artist/City') AS Tag
, EXTRACTVALUE(XML, '/Artist/City') AS City
FROM XML;
The next two examples use EXTRACT to retrieve, EXISTSNODE to
validate and predicate pattern matching to ﬁnd multiple elements. Results
are shown in Figures 17.16 and 17.17.
Chapter 17
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

378 17.2 Using XML in Oracle
Figure 17.16
Demonstrating
EXTRACT,
EXISTSNODE,
and a Single-Value
Pattern Match.
SELECT ID, EXTRACT(XML, '/Artist[City="Vienna"]') FROM XML
WHERE EXISTSNODE(XML, '/Artist[City="Vienna"]') = 1;
SELECT ID, EXTRACT(XML, '/Artist[City="Vienna" or
City="Boston"]')
FROM XML WHERE EXISTSNODE(XML, '/Artist[City="Vienna"
or City="Boston"]') = 1;
Figure 17.17
Demonstrating
EXTRACT,
EXISTSNODE,
and a Multiple-
Value Pattern
Match.
That covers data retrieval for XML documents in Oracle SQL.
17.2.2.3 Changing and Removing XML Document Content
An XML document is stored internally as a CLOB or large binary text
object. As a result, updating the contents of an XML document in an
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

17.2 Using XML in Oracle 379
XMLType datatype simply replaces the entire document. The easiest
method of changing XML document content is using the UPDATEXML
function.
UPDATEXML(XMLType object, search path, expression [, search
path, expression ], 'replace string'). The UPDATEXML function can
be used to change pattern-matched parts of XML documents.
There are some important things to remember about the UPDA-
TEXML function:
UPDATEXML can be used to update single tags, tag attributes, and
even entire subtrees.
Deleting XML document content is essentially the same as updating.
If a value is to be removed, simply ﬁnd it and set it to NULL using
UPDATEXML.
Remember that the UPDATEXML function can only ﬁnd and
update what already exists in the XML structure. If some values are
null valued when initially creating an XML document from relational
tables, those values will not exist in the XML document at all, not
even as tags. The only method of using UPDATEXML in this situa-
tion is to edit an entire parent tag.
Let’s change Mozart’s name and city as shown in Figures 17.15, 17.16,
and 17.17. The result is shown in Figure 17.18.
SET LONG 2000 WRAP ON LINESIZE 5000;
UPDATE XML SET XML =
UPDATEXML(XML, '/Artist/City/text()', 'Wien')
WHERE ID = 12;
UPDATE XML SET XML =
UPDATEXML(XML, '/Artist/@Name', 'Wolfgang Amadeus Mozart')
WHERE ID = 12;
SELECT X.XML.EXTRACT('/*') FROM XML X WHERE X.ID = 12;
Chapter 17
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

380 17.3 Metadata Views
Figure 17.18
Using
UPDATEXML to
Change XML
Documents.
Now let’s remove Mozart’s single CD from the XML document alto-
gether, as shown in the following script and in Figure 17.19.
SET LONG 2000 WRAP ON LINESIZE 5000;
UPDATE XML SET XML = UPDATEXML(XML, '/Artist//CD', NULL)
WHERE ID = 12;
SELECT X.XML.EXTRACT('/*') FROM XML X WHERE X.ID = 12;
To add Mozart’s CD back into the XML document, we can either re-
create from the source tables or update the entire node with the original
XML subtree.
17.3 Metadata Views
This section describes metadata views applicable to XML tables. Chapter
19 examines the basis and detail of Oracle Database metadata views.
USER_XML_TABLES and USER_XML_TAB_COLS. The struc-
ture of XML tables from the perspective of both tables and columns.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

17.4 Endnotes 381
Figure 17.19
UPDATEXML
Can Delete an
Entire Subtree.
USER_XML_VIEWS and USER_XML_VIEW_COLS. The struc-
ture of XML views and their columns structures.
USER_XML_SCHEMAS. Registered XML schemas.
This chapter has attempted to introduce the use of XML directly from
within Oracle SQL. XML is vastly more complex and detailed than pre-
sented in this chapter, both with respect to XML itself and to that of Oracle
software. This chapter is merely included to present the usefulness of XML
with respect to both Oracle Database and relational databases in general.
The next chapter will begin coverage of Data Deﬁnition Language (DDL)
commands by looking at tables.
17.4 Endnotes
1. www.oracledbaexpert.com/menu/HTML.html
2. www.oracledbaexpert.com/menu/DHTML.html
3. www.oracledbaexpert.com/menu/xml.html
4. www.incits.org
Chapter 17
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

This page intentionally left blank
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

18
Tables
In this chapter:
What is a table?
How do we create a table?
How do we change and destroy tables?
How are comments added to tables?
What is the recycle bin?
This chapter shows you how to do all sorts of stuff with tables. Creating
and changing of tables includes deﬁning and creating structure within
tables and making changes to those structures. Subsequent chapters cover
views and constraints. This chapter concentrates solely on tables.
18.1 What Is a Table?
Tables are used as structural deﬁnitions of data. The structure of a table
deﬁnes what kind of data can be stored in the table. Rows of repeating data
items are stored in tables in an Oracle schema. A schema is the Oracle user
that owns the tables. A user and a schema are the same thing as far as Oracle
Database is concerned. An Oracle relational database can contain many
Oracle schemas. A schema in Oracle is the equivalent of a single database in
other relational databases such as Sybase or Ingres.
18.1.1 Types of Tables
Oracle Database 10g supports many different types of tables. The easiest
method of explanation is to list the different available table types as follows:
383
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

384 18.1 What Is a Table?
Relational Table. The basic structure and core of a relational data-
base, holding user data.
Object Table. A table using an object type for its column deﬁnition,
or it can contain instances of strictly typed objects, such as type struc-
tures, collections, or binary objects.
Temporary Table. Temporary tables are available to all sessions, but a
separate data set is temporarily available for each session using a tem-
porary table.
Index-Organized Table. Index-Organized tables are often called
IOTs. A simple relational table, described previously, holds table data
in one physical object and index data in another physical object. For
an IOT, all columns in the table, not just the indexed columns, are
stored as a BTree index, based on the primary key. The data rows are
organized in the order of the index. This can improve performance in
some situations.
Cluster. Used to store multiple indexes of frequently joined tables
into a single, physical object. A cluster is similar to an IOT where
more data than usual is stored with indexes, increasing data access
performance. Performance especially improves when the joined tables
are most commonly accessed together, such as in a view or join query.
A cluster is much more of an index than an IOT is and therefore is
covered in detail in Chapter 21.
External Table. A read-only table storing data external to the data-
base, such as in a text ﬁle.
XMLType Table. A table with an Oracle internally managed XML
datatype structure, either as the table or in a column of a table. XML
is covered in Chapter 17.
Partitioned Table. Tables can be subdivided into partitions and sub-
partitions. Partitions are an effective performance-tuning approach
for dividing large tables on a range, list value, or hashing algorithm
basis. Partitioned tables are useful in data warehouse environments or
very large databases where parallel processing and rapid dataﬁle
movement can be utilized.
18.1.2 Methods of Creating Tables
Tables can be created in one of three ways:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

18.1 What Is a Table? 385
Scripted. The CREATE TABLE command can be used to list each
column’s attributes.
CREATE TABLE ... AS subquery. The CREATE TABLE command
can be executed as a creation from a subquery.
Tools. There are numerous tools available, which can be used to cre-
ate tables both in a graphical user interface (GUI) or as generated,
modiﬁable scripting.
18.1.2.1 Scripted Method
Examine the script shown following. This example is a part of the script
used to create the ARTIST table for the MUSIC schema (see Appendix A).
CREATE OR REPLACE TYPE
INSTRUMENTSCOLLECTION AS VARRAY(10) OF VARCHAR2(32);
/
CREATE TABLE ARTIST(
ARTIST_ID NUMBER NOT NULL
, NAME VARCHAR2(32) NOT NULL
, STREET VARCHAR2(32)
, POBOX CHAR(20)
, CITY VARCHAR2(32)
, STATE_PROVINCE VARCHAR2(32)
, COUNTRY VARCHAR2(32)
, ZIP CHAR(10)
, EMAIL VARCHAR2(32)
, INSTRUMENTS INSTRUMENTSCOLLECTION
, CONSTRAINT XPKARTIST PRIMARY KEY (ARTIST_ID)
);
CREATE UNIQUE INDEX XUK_ARTIST_NAME ON ARTIST (NAME);
Each column has a name, a datatype, a size (if needed for the datatype),
and a position in the table. There are several points to note about the ART-
IST table creation script:
The ARTIST table is by deﬁnition an object table and not a rela-
tional table. Why? A very simple reason. The ARTIST table contains
an object as one of its object types. The INSTRUMENTS column is
an object collection column of the user-deﬁned structural type
INSTRUMENTSCOLLECTION.
Chapter 18
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

386 18.1 What Is a Table?
The XPKARTIST column is a primary key constraint. Constraints
are covered in Chapter 20. This particular constraint is a primary key
placed onto the ARTIST_ID column. Being a primary key column,
the ARTIST_ID can never be the same for more than a single row in
the ARTIST table.
The ﬁnal command in the script shown previously is an index cre-
ation command. Indexes are covered in Chapter 21. The only impor-
tant point to note about this index at this point is that the NAME
column, like the primary key ARTIST_ID column, must be unique.
This index simply enforces that uniqueness of names.
18.1.2.2 CREATE TABLE ... AS Subquery
The subquery table creation method creates a copy of an existing table or
tables using a subquery. In the next example shown, we create a new table as
a join between ﬁve of the MUSIC schema tables. The output shows guest
appearances and then drops the table at the end because we do not want to
keep it. The result is shown in Figure 18.1.
CREATE TABLE EXTRAS AS
SELECT S.TITLE AS SONG, A.NAME AS ARTIST
, I.NAME AS INSTRUMENT
FROM GUESTAPPEARANCE GA, ARTIST A, SONG S
, INSTRUMENTATION IA, INSTRUMENT I
WHERE GA.GUESTARTIST_ID = A.ARTIST_ID
AND GA.GUESTARTIST_ID = S.SONG_ID
AND IA.SONG_ID = GA.SONG_ID
AND IA.GUESTARTIST_ID = GA.GUESTARTIST_ID
AND I.INSTRUMENT_ID = IA.INSTRUMENT_ID;
SELECT ARTIST||' played '||INSTRUMENT||' on '
||SONG AS "Who Played What?" FROM EXTRAS;
DROP TABLE EXTRAS;
18.1.2.3 Tools
Other methods of creating tables include use of tools such as Oracle Enter-
prise Manager, which provides a GUI for database object creation, includ-
ing table creation. Additionally, data modeling tools such as ERwin can be
utilized to generate scripts, which create entire application table sets. Figure
18.2 shows the table creation tool in Oracle Enterprise Manager.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

18.2 CREATE TABLE Syntax 387
Figure 18.1
Demonstrating
CREATE TABLE
... AS Subquery.
So far we have looked at different types of tables and various methods for
creating those different table types. Now we examine syntax for the CREATE
TABLE command, which is used for, you guessed it, creating tables.
18.2 CREATE TABLE Syntax
The syntax of the CREATE TABLE command is highly complex at ﬁrst
glance in Oracle documentation. However, the focus of this book is on
Oracle SQL and not database administration. Database administration
functionality for the CREATE TABLE command includes any physical
storage parameters such as tablespace locations and most types of physical
properties. Therefore, we get to leave a lot of the syntax out because we are
only dealing with Oracle SQL. This makes it a lot easier, but unfortunately
not easy enough. So syntax for the CREATE TABLE command has to be
divided into sections. Let’s begin with a very simple form of the syntax, per-
haps it could be called a pseudo-like syntax, for creating tables, as shown in
Figure 18.3.
What we do from this point onward is to pass through each table type in
turn, examining syntax and describing by example.
Chapter 18
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

388 18.3 Creating Different Table Types
Figure 18.2
Creating a Table
Using Oracle
Enterprise
Manager.
18.3 Creating Different Table Types
An easy way of simplifying CREATE TABLE syntax is to divide it up into
the different table types, as already brieﬂy described in this chapter. XML-
Type tables will be ignored in this section because they are extremely simple
and covered in Chapter 17.
Note: It is important to remember that different table types do not always
ﬁt precisely within the classiﬁcations assigned to them here. For example,
an IOT or a temporary table can be relational or object tables and vice
versa. The table types are simply divided neatly to facilitate ease of compre-
hension for the reader.
18.3.1 Creating Relational Tables
A relational table is termed relational because of the way in which tables are
linked together. We get to that shortly and in more detail in Chapter 20
when discussing constraints. The syntax for creating a simple relational
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

18.3 Creating Different Table Types 389
Figure 18.3
A CREATE
TABLE Pseudo-
like Syntax.
table is shown in Figure 18.4. Inline and out-of-line constraints are covered
in detail in Chapter 20.
We have already looked at the ARTIST table in this chapter. Let’s look
at the data warehouse section SALES table. The SALES table has more col-
umns than the ARTIST table and many more different datatypes for its col-
umns. Once again, all primary and foreign keys are constraints and are
covered in Chapter 20. Additionally, NOT NULL is a constraint prohibit-
ing a column from being empty within a row. Other than those points, the
only thing to note is that DEFAULT clauses have been added to allow for
column values with nothing added to them. Various numeric columns will
be set to zero if a row is added to the SALES where those defaulted columns
are not speciﬁed. In these cases, null values will be replaced with default val-
ues speciﬁed. Note that the DEFAULT clauses are not included in the
MUSIC schema table creation scripts. The DEFAULT clause is rarely used.
CREATE TABLE SALES (
SALES_ID NUMBER NOT NULL
, MUSICCD_ID NUMBER NOT NULL
, CUSTOMER_ID NUMBER NOT NULL
, RETAILER_ID NUMBER
, CONTINENT_ID NUMBER
, COUNTRY_ID NUMBER
, LIST_PRICE FLOAT DEFAULT 0
, DISCOUNT FLOAT DEFAULT 0
Chapter 18
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.