PrintNumber ErrorLocation Error Correction DateAdded
1 p i Make sure register mark is correct. done 5/13/2009
1 p ii Update print-line and delete Safari Enabled info at bottom of page. done Second printing July 2009 5/13/2009
1 p xiii Hour 12, “Understanding Dates and Time” 416 Hour 12, “Understanding Dates and Times” 416 5/13/2009
1 p 2 In the listings, all code that you type in (input) appears in boldface monospace. Output appears in standard monospace. Any code that is serving as a placeholder appears in
italic monospace.
In the listings, all code that you type in (input) appears in boldface monospace. Output appears in standard monospace. Any code that is serving as a placeholder appears in
italic monospace.
5/13/2009
1 p 2 SELECT [ ALL | * | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE [ , TABLE2 ];
SELECT [ ALL | * | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE [ , TABLE2 ];
5/13/2009
1 p 3 Notes are provided to expand on the material covered in each hour of the book.
Cautions are provided to warn the reader about “disasters” that could occur and
certain precautions that should be taken.
Tips are also given to supplement the material covered during appropriate hours
of study.

5/13/2009
1 p 8 The newest standard is now called SQL-2003 and a draft of the newest version is being voted upon as of December 2007. The newest standard is now called SQL-2003, and a draft of the newest version is being voted upon as of December 2007. 5/13/2009
1 p 9 SQL-2003 has eight interrelated documents and other documents may be added in the near future as the standard is expanded to encompass newly emerging technology needs. The eight interrelated parts are as follows:
• Part 1—SQL/Framework—Specifies the general requirements for conformance and defines the fundamental concepts of SQL.
• Part 2—SQL/Foundation—Defines the syntax and operations of SQL.
. Part 3—SQL/Call-Level Interface—Defines the interface for application programming to SQL.
• Part 4—SQL/Persistent Stored Modules—Defines the control structures that then define SQL routines. Part 4 also defines the modules that contain SQL routines.
• Part 9—SQL/Host Language Bindings—Defines extensions to SQL to support the management of external data through the use of data-wrappers and datalink types.
• Part 10—Object Language Bindings—Defines extensions to the SQL language to support the embedding of SQL statements into programs written in Java.
• Part 11—Information and Definition Schemas—Defines specifications for the Information Schema and Definition Schema, which provide structural and security information related to SQL data.
• Part 13—Routines and Types Using the Java Programming Language—Defines the capability to call Java static routines and classes as SQL-invoked routines.
• Part 14—XML-Related Specifications—Defines ways in which SQL can be used with XML.
SQL-2003 has nine interrelated documents, and other documents may be added in the near future as the standard is expanded to encompass newly emerging technology needs. The nine interrelated parts are as follows:
• Part 1—SQL/Framework—Specifies the general requirements for conformance and defines the fundamental concepts of SQL.
• Part 2—SQL/Foundation—Defines the syntax and operations of SQL.
. Part 3—SQL/Call-Level Interface—Defines the interface for application programming to SQL.
• Part 4—SQL/Persistent Stored Modules—Defines the control structures that then define SQL routines. Part 4 also defines the modules that contain SQL routines.
• Part 9—SQL/Host Language Bindings—Defines extensions to SQL to support the management of external data through the use of data-wrappers and datalink types.
• Part 10—Object Language Bindings—Defines extensions to the SQL language to support the embedding of SQL statements into programs written in Java.
• Part 11—Information and Definition Schemas—Defines specifications for the Information Schema and Definition Schema, which provide structural and security information related to SQL data.
• Part 13—Routines and Types Using the Java Programming Language—Defines the capability to call Java static routines and classes as SQL-invoked routines.
• Part 14—XML-Related Specifications—Defines ways in which SQL can be used with XML.
5/13/2009
1 p 13 A customer simply invokes an Internet browser, goes to the organization’s website, logs in (if required by the organization), and uses an application built into the organization’s web page to access data. Most organizations require users to register with them, and will issue a login and password to the customer. A customer simply invokes an Internet browser, goes to the organization’s website, logs in (if required by the organization), and uses an application built into the organization’s web page to access data. Most organizations require users to register with them and issue a login and password to the customer. 5/13/2009
1 p 13 Some of the most predominant database vendors include Oracle, Microsoft, Informix, Sybase, and IBM. These vendors distribute various versions of the relational database for a significant cost. Many other vendors supply an open-source version of an SQL database (relational database). Some of these vendors include MySQL, PostgresSQL, and SAP. Some of the most predominant database vendors include Oracle, Microsoft, Informix, Sybase, and IBM. These vendors distribute various versions of the relational database for a significant cost. Many other vendors supply an open-source version of a SQL database (relational database). Some of these vendors include MySQL, PostgresSQL, and SAP. 5/13/2009
1 p 14 When you attempt to connect to a database, you are automatically prompted for a password that is associated with your current username. The username is used to authenticate yourself to the database, and the password is the key that allows entrance. When you attempt to connect to a database, you are automatically prompted for a password that is associated with your current username. The username is used to authenticate you to the database, and the password is the key that allows entrance. 5/13/2009
1 p 15 Some of the most fundamental DDL commands discussed during following hours include the following: Some of the most fundamental DDL commands discussed during the following hours include: 5/13/2009
1 p 16 Though comprised of only one command, Data Query Language (DQL) is the most concentrated focus of SQL for modern relational database users. The base command is as follows: Though composed of only one command, Data Query Language (DQL) is the most concentrated focus of SQL for modern relational database users. The base command is as follows: 5/13/2009
1 p 16 Data control commands in SQL allow you to control access to data within the database. These Data Control Language (DCL) commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows: Data control commands in SQL allow you to control access to data within the database. These Data Control Language (DCL) commands are normally used to create objects related to user access and control the distribution of privileges among users. Some data control commands are as follows: 5/13/2009
1 p 21 A record, also called a row of data, is each individual, horizontal entry that exists in a table. Looking at the last table, PRODUCTS_TBL, consider the following first record in that table:
11235 WITCH’S COSTUME 29.99
A record, also called a row of data, is each individual, horizontal entry that exists in a table. Looking at the last table, PRODUCTS_TBL, consider the following first record in that table:
11235 WITCHES COSTUME 29.99
5/13/2009
1 p 24 The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers. The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers. 5/13/2009
1 p 28 This usually involves the use of a SQL function, such as CAST or CONVERT. For example:
SELECT CAST(‘12/27/1974’ AS DATETIME) AS MYDATE
This usually involves the use of a SQL function, such as CAST or CONVERT. For example:
SELECT CAST(‘1974-12-24’ AS DATETIME) AS MYDATE
5/13/2009
1 p 29 Constant characters, those strings that always have the same length, are stored using a fixed-length data type. The following is the standard for an SQL fixed-length character: Constant characters, those strings that always have the same length, are stored using a fixed-length data type. The following is the standard for a SQL fixed-length character: 5/13/2009
1 p 29 Spaces are normally used to fill extra spots when using a fixed-length data type; if a field’s length was set to 10 and data entered filled only five places, the remaining five spaces are recorded as spaces. The padding of spaces ensures that each value in a field is a fixed length. Spaces are normally used to fill extra spots when using a fixed-length data type; if a field’s length were set to 10 and data entered filled only five places, the remaining five spaces would be recorded as spaces. The padding of spaces ensures that each value in a field is a fixed length. 5/13/2009
1 p 29 Common data types for variable-length character values are the VARCHAR,
VARBINARY, and VARCHAR2 data types. VARCHAR is the ANSI standard, which Microsoft SQL Server and MySQL use; both VARCHAR and VARCHAR2 are used by Oracle. The data stored in a character-defined column can be alphanumeric, which means that the data value may contain numeric characters. VARBINARY is similar to VARCHAR and VARCHAR2 except that it contains a variable length of bytes.
Common data types for variable-length character values are the VARCHAR,
VARBINARY, and VARCHAR2 data types. VARCHAR is the ANSI standard, which Microsoft SQL Server and MySQL use; both VARCHAR and VARCHAR2 are used by Oracle. The data stored in a character-defined column can be alphanumeric, which means that the data value may contain numeric characters. VARBINARY is similar to VARCHAR and VARCHAR2 except that it contains a variable length of bytes.
5/13/2009
1 p 30 Some variable-length data types need to hold longer lengths of data than what is traditionally reserved for a VARCHAR field. The BLOB and TEXT data types are two examples of such data types in modern database implementations. These data types are specifically made to hold large sets of data. The BLOB is a binary large object and so its data is treated as a large binary string (a byte string). A BLOB is especially useful in an implementation that needs to store binary media files in the database, such as images or MP3s. Some variable-length data types need to hold longer lengths of data than what is traditionally reserved for a VARCHAR field. The BLOB and TEXT data types are two examples of such data types in modern database implementations. These data types are specifically made to hold large sets of data. The BLOB is a binary large object, so its data is treated as a large binary string (a byte string). A BLOB is especially useful in an implementation that needs to store binary media files in the database, such as images or MP3s. 5/13/2009
1 p 32 The last numeric value, 12.449, is rounded off to 12.45 upon input into the column. In this case, any numbers between 12.445 and 12.449 would be rounded to 12.45. The last numeric value, 12.449, is rounded off to 12.45 upon input into the column. In this case, any numbers between 12.445 and 12.449 would be rounded to 12.45. 5/13/2009
1 p 32 Floating-point decimals are decimal values whose precision and scale are variable lengths and virtually without limit. Any precision and scale is acceptable. The REAL data type designates a column with single-precision, floating-point numbers. The DOUBLE PRECISION data type designates a column that contains double-precision, floating-point numbers. To be considered a single-precision floating point, the precision must be between 1 and 21 inclusive. To be considered a double-precision floating point, the precision must be between 22 and 53 inclusive. The following are examples of the FLOAT data type: Floating-point decimals are decimal values whose precision and scale are variable lengths and virtually without limit. Any precision and scale is acceptable. The REAL data type designates a column with single-precision, floating-point numbers. The DOUBLE PRECISION data type designates a column that contains double-precision, floating-point numbers. To be considered a single-precision floating point, the precision must be between 1 and 21 inclusive. To be considered a double-precision floating point, the precision must be between 22 and 53 inclusive. The following are examples of the FLOAT data type: 5/13/2009

p 34 BOOLEAN Values
A BOOLEAN value is a value of TRUE, FALSE, or NULL. BOOLEAN values are used to make data comparisons. For example, when criteria are specified for a query, each condition evaluates to a TRUE, FALSE, or NULL. If the BOOLEAN value of TRUE is
BOOLEAN Values
A Boolean value is a value of TRUE, FALSE, or NULL. Boolean values are used to make data comparisons. For example, when criteria are specified for a query, each condition evaluates to a TRUE, FALSE, or NULL. If the Boolean value of TRUE is
5/13/2009
1 p 35 returned by all conditions in a query, data is returned. If a BOOLEAN value of FALSE or NULL is returned, data might not be returned. returned by all conditions in a query, data is returned. If a Boolean value of FALSE or NULL is returned, data might not be returned. 5/13/2009
1 p 35 The CREATE TYPE statement is used to create a user-defined type. The CREATE TYPE statement is used to create a user-defined type. Currently, MySQL does not support user defined types, so the following code examples are in Oracle syntax. 5/13/2009
1 p 35 The domain is used like the user-defined type. The domain is used like the user-defined type. Like user defined types, the domain syntax is not currently implemented within the MySQL framework, so we have provided Oracle syntax to demonstrate its usage below. 5/13/2009
1 p 37 A. Numeric values are still alphanumeric, which are allowed in string data types. The process is called an implicit conversion because it is handled automatically by the database system. Typically, the only data stored as numeric values are values used in computations. However, it might be helpful for some to define all numeric fields with a numeric data type to help control the data entered in that field. A. Numeric values are still alphanumeric, which are allowed in string data types. The process is called an implicit conversion because it is handled automatically by the database system. Typically, the only data stored as numeric values are values used in computations. However, it might be helpful for some to define all numeric fields with a numeric data type to help control the data entered in that field. 5/13/2009
1 p 37 If you use a varying-length data type with a length of 20 and inserted Smith, only 5 bytes of space are taken. If you then imagine that you are inserting 100,000 rows of data into this system, you could possibly save 1.5 million bytes of data. If you use a varying-length data type with a length of 20 and insert Smith, only 5 bytes of space are taken. If you then imagine that you are inserting 100,000 rows of data into this system, you could possibly save 1.5 million bytes of data. 5/13/2009
1 p 37 The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. You may refer to Appendix C, “Answers to Quizzes and Exercises,” for answers. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in the previous hour of study. Please take time to complete the quiz questions and exercises before continuing. You may refer to Appendix C, “Answers to Quizzes and Exercises,” for answers. 5/13/2009
1 p 38 5. Which numbers could be inserted into a column whose data type is
DECIMAL(4,1)?
A. 16.2
B. 116.2
C. 16.21
D. 1116.2
E. 1116.21
6. What is data?
Exercises
1. Take the following column titles, assign them to a data type, decide on the proper length, and give an example of the data you would enter into that column.
A. ssn
B. state
C. city
D. phone_number
E. zip
F. last_name
5. Which numbers could be inserted into a column whose data type is
DECIMAL(4,1)?
a. 16.2
b. 116.2
c. 16.21
d. 1116.2
e. 1116.21
6. What is data?
Exercises
1. Take the following column titles, assign them to a data type, decide on the proper length, and give an example of the data you would enter into that column.
a. SSN
b. STATE
c. CITY
d. PHONE_NUMBER
e. ZIP
f. LAST_NAME
5/13/2009
1 p 39 G. first_name
H. middle_name
I. salary
J. hourly_pay_rate
K. date_hired
2. Take the same column titles and decide whether they should be NULL or NOT NULL, realizing that in some cases where a column would normally be NOT NULL, the column could be NULL or vice versa, depending on the application.
A. ssn
B. state
C. city
D. phone_number
E. zip
F. last_name
G. first_name
H. middle_name
I. salary
J. hourly_pay_rate
K. date_hired
g. FIRST_NAME
h. MIDDLE_NAME
i. SALARY
j. HOURLY_PAY_RATE
k. DATE_HIRED
2. Take the same column titles and decide whether they should be NULL or NOT NULL, realizing that in some cases where a column would normally be NOT NULL, the column could be NULL or vice versa, depending on the application.
a. SSN
b. STATE
c. CITY
d. PHONE_NUMBER
e. ZIP
f. LAST_NAME
g. FIRST_NAME
h. MIDDLE_NAME
i. SALARY
j. HOURLY_PAY_RATE
k. DATE_HIRED
5/13/2009
1 p 43 This could possibly lead to you unintentionally dropping the wrong object. So you must always pay careful attention as to which user you are currently logged into the database with. This could possibly lead to your unintentionally dropping the wrong object. So you must always pay careful attention as to which user you are currently logged into the database with. 5/13/2009
1 p 44 Figure 3.2
An Example of a Column
Figure 3.2
An example of a column.
5/13/2009
1 p 45 A row is comprised of fields that contain data from one record in a table. A table can contain as little as one row of data and up to as many as millions of rows of data or records. Figure 3.3 illustrates a row within a table.
Figure 3.3
Example of a Table Row.
A row is composed of fields that contain data from one record in a table. A table can contain as little as one row of data and up to as many as millions of rows of data or records. Figure 3.3 illustrates a row within a table.
Figure 3.3
Example of a table row.
5/13/2009
1 p 46 In this hour’s examples, we use the popular data types CHAR (constant-length character), VARCHAR (variable-length character), NUMBER (numeric values, decimal and non-decimal), and DATE (date and time values).
Create a table called EMPLOYEE_TBL in the following example:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL,
EMP_NAME VARCHAR (40) NOT NULL,
EMP_ST_ADDR VARCHAR (20) NOT NULL,
EMP_CITY VARCHAR (15) NOT NULL,
EMP_ST CHAR(2) NOT NULL,
EMP_ZIP INTEGER(5) NOT NULL,
EMP_PHONE INTEGER(10) NULL,
EMP_PAGER INTEGER(10) NULL);
In this hour’s examples, we use the popular data types CHAR (constant-length character), VARCHAR (variable-length character), NUMBER (numeric values, decimal and nondecimal), and DATE (date and time values).
Create a table called EMPLOYEE_TBL in the following example:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL,
LAST_NAME VARCHAR (40) NOT NULL,
FIRST_NAME VARCHAR (20) NOT NULL,
MIDDLE_NAME VARCHAR (20) NOT NULL,
ADDRESS VARCHAR (20) NOT NULL,
CITY VARCHAR (15) NOT NULL,
ST CHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE INTEGER(10) NULL
5/13/2009
1 p 47 Each record, or row of data, in this table would consist of the following:
EMP_ID, EMP_NAME, EMP_ST_ADDR, EMP_CITY, EMP_ST, EMP_ZIP, EMP_PHONE, EMP_PAGER
Each record, or row of data, in this table would consist of the following:
EMP_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS, CITY, ST, ZIP, PHONE
5/13/2009
1 P 47 When selecting names for objects, specifically tables and columns, the name should reflect the data that is to be stored. For example, the name for a table pertaining to employee information could be named EMPLOYEE_TBL. Names for columns should follow the same logic. When storing an employee’s phone number, an obvious name for that column would be PHONE_NUMBER. When selecting names for objects, specifically tables and columns, the name should reflect the data that is to be stored. For example, a table pertaining to employee information could be named EMPLOYEE_TBL. Names for columns should follow the same logic. When storing an employee’s phone number, an obvious name for that column would be PHONE_NUMBER. 5/13/2009
1 p 47 alter table table_name [modify] [column column_name][datatype|null not null]
[restrict|cascade]
[drop] [constraint constraint_name]
[add] [column] column definition
alter table table_name [modify] [column column_name][datatype|null not null]
[restrict|cascade]
[drop] [constraint constraint_name]
[add] [column] column definition
5/13/2009
1 p 49 In MySQL the implementation provides the SERIAL method to produce a truly unique value for the table. Following is an example: In MySQL, the implementation provides the SERIAL method to produce a truly unique value for the table. Following is an example: 5/13/2009
1 p 50 create table new_table_name as
select [ *|column1, column2 ]
from table_name
[ where ]
Notice some new keywords in the syntax, particularly the SELECT keyword. SELECT is a database query and is discussed in more detail in Chapter 7, “Introduction to Database Query.” However, it is important to know that you can create a table based on the results from a query.
create table new_table_name as
select [ *|column1, column2 ]
from table_name
[ where ]
Notice some new keywords in the syntax, particularly the SELECT keyword. SELECT is a database query and is discussed in more detail in Hour 7, “Introduction to the Database Query.” However, it is important to know that you can create a table based on the results from a query.
5/13/2009
1 p 51 drop table table_name [ restrict|cascade ]
In the following example, you drop the table that you just created:
drop table products.tmp;
drop table table_name [ restrict|cascade ]
In the following example, you drop the table that you just created:
drop table products_tmp;
5/13/2009
1 p 52 The following example identifies the EMP_ID column as the PRIMARY KEY for the EMPLOYEES table:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR (40) NOT NULL,
EMP_ST_ADDR VARCHAR (20) NOT NULL,
EMP_CITY VARCHAR (15) NOT NULL,
EMP_ST CHAR(2) NOT NULL,
EMP_ZIP INTEGER(5) NOT NULL,
EMP_PHONE INTEGER(10) NULL,
EMP_PAGER INTEGER(10) NULL);
This method of defining a primary key is accomplished during table creation. The primary key in this case is an implied constraint. You can also specify a primary key explicitly as a constraint when setting up a table, as follows:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL,
EMP_NAME VARCHAR (40) NOT NULL,
EMP_ST_ADDR VARCHAR (20) NOT NULL,
EMP_CITY VARCHAR (15) NOT NULL,
EMP_ST CHAR(2) NOT NULL,
EMP_ZIP INTEGER(5) NOT NULL,
EMP_PHONE INTEGER(10) NULL,
EMP_PAGER INTEGER(10) NULL,
PRIMARY KEY (EMP_ID));
The following example identifies the EMP_ID column as the primary key for the EMPLOYEES table:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL,
LAST_NAME VARCHAR (40) NOT NULL,
FIRST_NAME VARCHAR (20) NOT NULL,
MIDDLE_NAME VARCHAR (20) NOT NULL,
ADDRESS VARCHAR (20) NOT NULL,
CITY VARCHAR (15) NOT NULL,
ST CHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE INTEGER(10) NULL
);
This method of defining a primary key is accomplished during table creation. The primary key in this case is an implied constraint. You can also specify a primary key explicitly as a constraint when setting up a table, as follows:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL,
LAST_NAME VARCHAR (40) NOT NULL,
FIRST_NAME VARCHAR (20) NOT NULL,
MIDDLE_NAME VARCHAR (20) NOT NULL,
ADDRESS VARCHAR (20) NOT NULL,
CITY VARCHAR (15) NOT NULL,
ST CHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE INTEGER(10) NULL,
PRIMARY KEY (EMP_ID));
5/13/2009
1 p 53 A primary key that consists of more than one column can be defined by either of the following methods:
CREATE TABLE PRODUCTS
(PROD_ID VARCHAR2(10) NOT NULL,
VEND_ID VARCHAR2(10) NOT NULL,
PRODUCT VARCHAR2(30) NOT NULL,
COST NUMBER(8,2) NOT NULL,
PRIMARY KEY (PROD_ID, VEND_ID));

ALTER TABLE PRODUCTS
ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (PROD_ID, VEND_ID);
A primary key that consists of more than one column can be defined by either of the following methods:
CREATE TABLE PRODUCTS
(PROD_ID VARCHAR(10) NOT NULL,
VEND_ID VARCHAR(10) NOT NULL,
PRODUCT VARCHAR(30) NOT NULL,
COST DECIMAL(8,2) NOT NULL,
PRIMARY KEY (PROD_ID, VEND_ID));

ALTER TABLE PRODUCTS
ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (PROD_ID, VEND_ID);
»
5/14/2009
1 p 53 Study the following example:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR (40) NOT NULL,
EMP_ST_ADDR VARCHAR (20) NOT NULL,
EMP_CITY VARCHAR (15) NOT NULL,
EMP_ST CHAR(2) NOT NULL,
EMP_ZIP INTEGER(5) NOT NULL,
EMP_PHONE INTEGER(10) NULL UNIQUE,
EMP_PAGER INTEGER(10) NULL);
Study the following example:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL PRIMARY KEY,
LAST_NAME VARCHAR (40) NOT NULL,
FIRST_NAME VARCHAR (20) NOT NULL,
MIDDLE_NAME VARCHAR (20) NOT NULL,
ADDRESS VARCHAR (20) NOT NULL,
CITY VARCHAR (15) NOT NULL,
ST CHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE INTEGER(10) NULL);
5/14/2009
1 p 54 Study the creation of the foreign key in the following example:
CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID CHAR(9) NOT NULL,
POSITION VARCHAR2(15) NOT NULL,
DATE_HIRE DATE NULL,
PAY_RATE NUMBER(4,2) NOT NULL,
DATE_LAST_RAISE DATE NULL,
CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID));
Study the creation of the foreign key in the following example:
CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID CHAR(9) NOT NULL,
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATE NULL,
PAY_RATE DECIMAL(4,2) NOT NULL,
DATE_LAST_RAISE DATE NULL,
CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID));
5/14/2009
1 p 54 Figure 3.4 fixed...all caps. fixed 5/15/2009
1 p 54 For a value to be inserted for EMP_ID in the child table, a value for EMP_ID in the parent table must first exist. For a value to be inserted for EMP_ID in the child table, a value for EMP_ID in the parent table must exist first. 5/15/2009
1 p 54 alter table employee_pay_tbl
add constraint id_fk foreign key (emp_id)
references employee_tbl (emp_id);
alter table employee_pay_tbl
add constraint id_fk foreign key (emp_id)
references employee_tbl (emp_id);
5/15/2009
1 p 55 In addition, the actual use and definitions of constraints also vary, but the concept of referential integrity should be the same with all relational databases.
The actual use and definitions of constraints also vary, but the concept of referential integrity should be the same with all relational databases.
5/15/2009
1 p 55 General edits restrict values that can be entered into columns or objects, whether within the database itself or on a front-end application. General edits restrict values that can be entered into columns or objects, whether within the database or on a front-end application. 5/15/2009
1 p 55 The following example illustrates the use of a check constraint:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL,
EMP_NAME VARCHAR2(40) NOT NULL,
EMP_ST_ADDR VARCHAR2(20) NOT NULL,
EMP_CITY VARCHAR2(15) NOT NULL,
EMP_ST CHAR(2) NOT NULL,
EMP_ZIP NUMBER(5) NOT NULL,
EMP_PHONE NUMBER(10) NULL,
EMP_PAGER NUMBER(10) NULL),
PRIMARY KEY (EMP_ID),
CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP = ‘46234’);
The following example illustrates the use of a check constraint:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL,
LAST_NAME VARCHAR(40) NOT NULL,
FIRST_NAME VARCHAR(20) NOT NULL,
MIDDLE_NAME VARCHAR(20) NOT NULL,
ADDRESS VARCHAR(20) NOT NULL,
CITY VARCHAR(15) NOT NULL,
ST CHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE INTEGER(10) NULL,
CONSTRAINT CHK_EMP_ZIP CHECK ( ZIP = ‘46234’);
5/15/2009
1 p 56 CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID CHAR(9) NOT NULL,
POSITION VARCHAR2(15) NOT NULL,
DATE_HIRE DATE NULL,
PAY_RATE NUMBER(4,2) NOT NULL,
DATE_LAST_RAISE DATE NULL,
CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID),
CONSTRAINT CHK_PAY CHECK ( PAY_RATE > 12.50 ) );
CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID CHAR(9) NOT NULL,
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATE NULL,
PAY_RATE DECIMAL(4,2) NOT NULL,
DATE_LAST_RAISE DATE NULL,
CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID),
CONSTRAINT CHK_PAY CHECK ( PAY_RATE > 12.50 ) );
5/15/2009
1 p 56 Instead of permanently dropping a constraint from the database you might want to temporarily disable the constraint, and then enable it later.
Instead of permanently dropping a constraint from the database, you might want to temporarily disable the constraint, and then enable it later.
5/15/2009
1 p 56 You have learned a little about database objects in general, but have specifically learned about the table. You have learned a little about database objects in general, but you have specifically learned about the table. 5/15/2009
1 p 57 You have also learned how to modify the structure of existing tables using the ALTER TABLE command. You also learned how to modify the structure of existing tables using the ALTER TABLE command. 5/15/2009
1 p 58 1. Will the following CREATE TABLE statement work? If not, what needs to be done to correct the problem(s)?
Create table EMPLOYEE_TABLE as:
( ssn number(9) not null,
last_name varchar2(20) not null,
first_name varchar2(20) not null,
middle_name varchar2(20) not null,
st address varchar2(30) not null,
city char(20) not null,
state char(2) not null,
zip number(4) not null,
date hired date);
1. Will the following CREATE TABLE statement work? If not, what needs to be done to correct the problem(s)?
Create table EMPLOYEE_TABLE as:
( SSN NUMERIC(9) NOT NULL,
LAST_NAME VARCHAR(20) NOT NULL
FIRST_NAME VARCHAR(20) NOT NULL,
MIDDLE_NAME VARCHAR(20) NOT NULL,
ST ADDRESS VARCHAR(30) NOT NULL,
CITY CHAR(20) NOT NULL,
STATE CHAR 2) NOT NULL,
ZIP NUMERIC(4) NOT NULL,
DATE HIRED DATE);
5/15/2009
1 p 62 Figure 4.1 updated fixed 5/15/2009
1 p 63 What measures can be taken to ensure ease of use for the end-user who is maintaining the data? What measures can be taken to ensure ease of use for the end user who is maintaining the data? 5/15/2009
1 p 64 For example, to normalize a database using the second normal form, the database must first be in the first normal form.
For example, to normalize a database using the second normal form, the database must initially be in the first normal form.

5/15/2009
1 p 65 Figure 4.2 should be all caps.

On the other hand, the information that is only partly dependent on the EMP_ID (each individual employee) is used to populate EMPLOYEE_PAY_TBL (EMP_ID, POSITION, POSITION_
DESC, DATE_HIRE, PAY_RATE, and DATE_LAST_RAISE).
Fixed

On the other hand, the information that is only partly dependent on the EMP_ID (each employee) is used to populate EMPLOYEE_PAY_TBL (EMP_ID, POSITION, POSITION_
DESC, DATE_HIRE, PAY_RATE, and DATE_LAST_RAISE).
5/15/2009
1 p 66-67 Figure 4.3 and Figure 4.4 should be all caps.
Fixed
5/15/2009
1 p 68 Enforces concept of referential integrity Enforcement of referential integrity
5/15/2009
1 p 68 Enforces concept of referential integrity Enforcement of referential integrity
5/15/2009
1 p 70 Q. Why should I be so concerned with the end user’s needs when designing the database?
Q. Why should I be so concerned with the end users’ needs when designing the database?
5/15/2009
1 p 72 2. Open a command prompt and login to your MySQL instance as you did in the exercises in Hour 3. 2. Open a command prompt and log in to your MySQL instance as you did in the exercises in Hour 3. 5/15/2009
1 p 75 products_tbl

COLUMN Name Null? DATA Type
------------------------------ -------- -------------
PROD_ID NOT NULL VARCHAR2(10)
PROD_DESC NOT NULL VARCHAR2(25)
COST NOT NULL NUMBER(6,2)
PRODUCTS_TBL

COLUMN NAME NULL? DATA TYPE
------------------------------ -------- -------------
PROD_ID NOT NULL VARCHAR(10)
PROD_DESC NOT NULL VARCHAR(25)
COST NOT NULL NUMERIC(6,2)
5/21/2009
1 p 75 Although single quotation marks are not required around numeric data that is being inserted, they may be used with any data type. Said another way, single quotation marks are optional when referring to numeric data values in the database, but required for all other data values (data types). Although usually a matter of preference, most SQL users choose not to use quotation marks with numeric values as it makes their queries more readable.
Although single quotation marks are not required around numeric data that is being inserted, they may be used with any data type. Said another way, single quotation marks are optional when referring to numeric data values in the database, but they required for all other data values (data types). Although usually a matter of preference, most SQL users choose not to use quotation marks with numeric values because it makes their queries more readable. 5/21/2009
1 p 76 COLUMN NAME Null? DATA TYPE
------------------------------ --------- ------------
ORD_NUM NOT NULL VARCHAR2(10)
CUST_ID NOT NULL VARCHAR2(10)
PROD_ID NOT NULL VARCHAR2(10)
QTY NOT NULL NUMBER(4)
ORD_DATE DATE
COLUMN NAME NULL? DATA TYPE
------------------------------ --------- ------------
ORD_NUM NOT NULL VARCHAR(10)
CUST_ID NOT NULL VARCHAR(10)
PROD_ID NOT NULL VARCHAR(10)
QTY NOT NULL NUMERIC(4)
ORD_DATE DATE
5/21/2009
1 p 77 Now, insert values into the PRODUCTS_TMP table based on the preceding query. You can see that 11 rows are created in the temporary table.
Create a table with the same structure as the PRODUCTS_TBL table. Now, insert values into the PRODUCTS_TMP table based on the preceding query. You can see that 11 rows are created in the temporary table.
5/21/2009
1 p 81 The DELETE command is used to remove entire rows of data from a table. The DELETE command is not used to remove values from specific columns; a full record, including all columns, is removed. The DELETE statement must be used with
caution—as it works all too well.
To delete a single record or selected records from a table, the DELETE statement must be used with the following syntax:
The DELETE command is used to remove entire rows of data from a table. The DELETE command is not used to remove values from specific columns; a full record, including all columns, is removed. The DELETE statement must be used with
caution—because it works all too well.
To delete a single record or selected records from a table, you must use the DELETE statement with the following syntax:
5/21/2009
1 p 83 1. Use the EMPLOYEE_TBL with the following structure:
column data type (not)null
last_name varchar2(20) not null
first_name varchar2(20) not null
ssn char(9) not null
phone number(10) null

LAST_NAME FIRST_NAME SSN PHONE
SMITH JOHN 312456788 3174549923
ROBERTS LISA 232118857 3175452321
SMITH SUE 443221989 3178398712
PIERCE BILLY 310239856 3176763990
What would happen if the following statements were run?
A.
insert into employee_tbl
(‘JACKSON’, ‘STEVE’, ‘313546078’, ‘3178523443’);
B.
insert into employee_tbl values
(‘JACKSON’, ‘STEVE’, ‘313546078’, ‘3178523443’);
C.
insert into employee_tbl values
(‘MILLER’, ‘DANIEL’, ‘230980012’, NULL);
D.
insert into employee_tbl values
(‘TAYLOR’, NULL, ‘445761212’, ‘3179221331’);

1. Use the EMPLOYEE_TBL with the following structure:
column data type (not)null
last_name varchar(20) not null
first_name varchar(20) not null
ssn char(9) not null
phone numerIC(10) null

LAST_NAME FIRST_NAME SSN PHONE
SMITH JOHN 312456788 3174549923
ROBERTS LISA 232118857 3175452321
SMITH SUE 443221989 3178398712
PIERCE BILLY 310239856 3176763990
What would happen if the following statements were run?
A.
insert into employee_tbl
(‘JACKSON’, ‘STEVE’, ‘313546078’, ‘3178523443’);
B.
insert into employee_tbl values
(‘JACKSON’, ‘STEVE’, ‘313546078’, ‘3178523443’);
C.
insert into employee_tbl values
(‘MILLER’, ‘DANIEL’, ‘230980012’, NULL);
D.
insert into employee_tbl values
(‘TAYLOR’, NULL, ‘445761212’, ‘3179221331’);
5/21/2009
1 p 84 E.
delete from employee_tbl;
F.
delete from employee_tbl
where last_name = ‘SMITH’;
G.
delete from employee_tbl
where last_name = ‘SMITH’
and first_name = ‘JOHN’;
H.
update employee_tbl
set last_name = ‘CONRAD’;
I.
update employee_tbl
set last_name = ‘CONRAD’
where last_name = ‘SMITH’;
J.
update employee_tbl
set last_name = ‘CONRAD’,
first_name = ‘LARRY’;
K.
update employee_tbl
set last_name = ‘CONRAD’
first_name = ‘LARRY’
where ssn = ‘313546078’;


E.
delete from employee_tbl;
F.
delete from employee_tbl
where last_name = ‘SMITH’;
G.
delete from employee_tbl
where last_name = ‘SMITH’
and first_name = ‘JOHN’;
H.
update employee_tbl
set last_name = ‘CONRAD’;
I.
update employee_tbl
set last_name = ‘CONRAD’
where last_name = ‘SMITH’;
J.
update employee_tbl
set last_name = ‘CONRAD’,
first_name = ‘LARRY’;
K.
update employee_tbl
set last_name = ‘CONRAD’,
first_name = ‘LARRY’
where ssn = ‘313546078’;
5/21/2009
1 p 84 1. Go to Appendix E, “INSERT Statements for Data in Book Examples.” Invoke MySQL as you have done in previous exercises.

1. Go to Appendix E, “INSERT Statements for Book Examples.” Invoke MySQL as you have done in previous exercises. 5/21/2009
1 p 85 2. Use the PRODUCTS_TBL for the next exercise.
A. Add the following products to the product table:
PROD_ID PROD_DESC COST
301 FIREMAN COSTUME 24.99
302 POLICEMAN COSTUME 24.99
303 KIDDIE GRAB BAG 4.99
Write DML to accomplish the following:
B. Correct the cost of the two costumes added. The cost should be the same as the witch’s costume.
C. Now we have decided to cut our product line, starting with the new products. Remove the three products you just added.
2. Use the PRODUCTS_TBL for the next exercise.
a. Add the following products to the product table:
PROD_ID PROD_DESC COST
301 FIREMAN COSTUME 24.99
302 POLICEMAN COSTUME 24.99
303 KIDDIE GRAB BAG 4.99
Write DML to accomplish the following:
b. Correct the cost of the two costumes added. The cost should be the same as the witches costume.
c. Now we have decided to cut our product line, starting with the new products. Remove the three products you just added.
5/21/2009
1 p 87 When managing transactions, each designated transaction (group of DML statements) must be successful as one entity or none of them will be successful.
When you are managing transactions, each designated transaction (group of DML statements) must be successful because one entity or none of them will be successful.
5/21/2009
1 p 91-93 select * from products_tmp;
SELECT * FROM PRODUCTS_TMP;

all pages
5/21/2009
1 p 97 2. True or false: A SAVEPOINT command actually saves transactions after a specified amount of transactions have executed.
2. True or false: A SAVEPOINT command actually saves transactions after a specified number of transactions have executed.
5/21/2009
1 p 97 2. Take the following group of transactions and create a savepoint command after the first three transactions.
Then place a ROLLBACK statement to your savepoint at the end.
2. Take the following group of transactions and create a SAVEPOINT command after the first three transactions.
Then place a ROLLBACK statement to your SAVEPOINT at the end.
5/21/2009
1 p 97 Then place a ROLLBACK statement to your savepoint at the end. Then place a ROLLBACK statement to your SAVEPOINT at the end. 5/22/2009
1 p 102 SELECT [ * | ALL | DISTINCT COLUMN1, COLUMN2 ]
pipes should be solid. 5/22/2009
1 p 105 select [ all | * | distinct column1, column2 ]
from table1 [ , table2 ]
where [ condition1 | expression1 ]
[ and|OR condition2 | expression2 ]
pipes should be solid. 5/22/2009
1 p 107 select [ all | * | distinct column1, column2 ]
from table1 [ , table2 ]
where [ condition1 | expression1 ]
[ and|OR condition2 | expression2 ]
ORDER BY column1|integer [ ASC|DESC ]
pipes should be solid. 5/22/2009
1 p 110 Display data for a given condition and sort the output by multiple columns using integers. The order of the columns in the sort is different than their corresponding order after the SELECT keyword: Display data for a given condition and sort the output by multiple columns using integers. The order of the columns in the sort is different from their corresponding order after the SELECT keyword: 5/22/2009
1 p 113 Column aliases can be used to customize names for column headers and can also be used to reference a column with a shorter name in some SQL implementations.
Column aliases can be used to customize names for column headers and to reference a column with a shorter name in some SQL implementations.
5/22/2009
1 p 118-119 All non-equality, non-equals, etc

Global change per Std conventions.
nonequality, nonequal, etc. 5/22/2009
1 p 119 The following example shows all of the product information from the products table that do not have the product id of 2345: The following example shows all of the product information from the products table that do not have the product ID of 2345: 5/22/2009
1 p 119 1234 KEY CHAIN 5.95
2345 OAK BOOKSHELF 59.99

11 rows selected.
1234 KEY CHAIN 5.95

10 rows selected.
5/22/2009
1 p 119 The following examples show that salary is less than or greater than to 20000: The following examples show that salary is less than or greater than 20000: 5/22/2009
1 p 122 Understand that the literal word null is different than a NULL value. Examine the following example:
Understand that the literal word null is different from a NULL value. Examine the following example:
5/22/2009
1 p 123 The following example show using the IN operator to match all of the products that have a product id within a certain range of values:
The following example shows using the IN operator to match all of the products that have a product ID within a certain range of values: 5/22/2009
1 p 126 The following example shows how the ALL operator is used in conjunction with subquery:

(Is in mono font and should not be.)
fixed 5/22/2009
1 p 130 The NOT can be used with operators to form the following methods: NOT can be used with operators to form the following methods: 5/22/2009
1 p 133 To find values that do not start with 2 and have a length of three or greater:
WHERE SALARY NOT LIKE ‘2_%_%’
The following example demonstrates using the NOT LIKE operator to display a list of values:

NOT LIKE (sb mono)
To find values that do not start with 2 and have a length of 3 or greater:
WHERE SALARY NOT LIKE ‘2_%_%’
The following example demonstrates using the NOT LIKE operator to display a list of values:
5/22/2009
1 p 134 The following example demonstrates using the IS NOT NULL operator to retrieve a list of employees whose page number is not NULL.

IS NOT NULL and NULL (sb mono)

The following example demonstrates the use of the NOT EXISTS operator in conjunction with a subquery:

NOT EXISTS (sb mono)
fixed 5/22/2009
1 p 135 The following example multiplies the SALARY column by 10:

SALARY (sb mono)
fixed 5/22/2009
1 p 139 7. What, if anything, is wrong with the following SELECT statements?
A.
SELECT SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY BETWEEN 20000, 30000
B.
SELECT SALARY + DATE_HIRE
FROM EMPLOYEE_PAY_TBL
C.
SELECT SALARY, BONUS
FROM EMPLOYEE_PAY_TBL
WHERE DATE_HIRE BETWEEN 1999-09-22
AND 1999-11-23
AND POSITION = ‘SALES’
OR POSITION = ‘MARKETING’
AND EMPLOYEE_ID LIKE ‘%55%
Exercises
1. Using the following CUSTOMER_TBL:
DESCRIBE CUSTOMER_TBL:

Name Null? Type
7. What, if anything, is wrong with the following SELECT statements?
a.
SELECT SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY BETWEEN 20000, 30000;
b.
SELECT SALARY + DATE_HIRE
FROM EMPLOYEE_PAY_TBL;
c.
SELECT SALARY, BONUS
FROM EMPLOYEE_PAY_TBL
WHERE DATE_HIRE BETWEEN 1999-09-22
AND 1999-11-23
AND POSITION = ‘SALES’
OR POSITION = ‘MARKETING’
AND EMPLOYEE_ID LIKE ‘%55%;
Exercises
1. Using the following CUSTOMER_TBL:
DESCRIBE CUSTOMER_TBL

NAME NULL? TYPE
5/22/2009
1 p 140 Name Null? Type
NAME NULL? TYPE
5/22/2009
1 p 140 4. Write a SELECT statement that returns products that are either less than 1.00 or greater than 12.50. There are two ways to achieve the same results.
4. Write a SELECT statement that returns products that are either less than $1.00 or greater than $12.50. There are two ways to achieve the same results. 5/22/2009
1 p 141 An aggregate function is used to provide summarization information for an SQL statement, such as counts, totals, and averages. An aggregate function is used to provide summarization information for a SQL statement, such as counts, totals, and averages. 5/26/2009
1 p 143 COUNT [ (*) | (DISTINCT | ALL) ] (COLUMN NAME)


pipes should be solid.
fixed 5/26/2009
1 p 143 This example counts all employee IDs:
SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEE_PAY_ID
This example counts all employee IDs:
SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEE_PAY_TBL
5/26/2009
1 p 145 Observe how the DISTINCT command in the following example skews the results from above, this is why it is rarely useful. Observe how the DISTINCT command in the following example skews the preceding results. This is why it is rarely useful. 5/26/2009
1 p 147 AVG(PAY_RATE) AVG(SALARY)
------------- -----------
13.5833333 30000
Align table columns. 5/26/2009
1 p 148 COUNT(ORD_NUM) SUM(QTY) AVG_QTY
-------------- ---------- ----------
7 160 22.857143
Align table columns. 5/26/2009
1 p 150 3. If every product cost $10.00, what would be the total dollar amount for all orders? 3. If every product costs $10.00, what would be the total dollar amount for all orders? 5/26/2009
1 p 151 Grouping data is the process of combining columns with duplicate values in a logical order.

Grouping data should be italic.
fixed 5/26/2009
1 p 151-2 You would do this by using the aggregate function AVG on the SALARY column, as you learned last hour, and by using the GROUP BY clause to group the output by city.
Grouping data is accomplished through the use of the GROUP BY clause of a SELECT statement (query). Last hour, you learned how to use aggregate functions.
You would do this by using the aggregate function AVG on the SALARY column, as you learned in the previous hour, and by using the GROUP BY clause to group the output by city.
Grouping data is accomplished through the use of the GROUP BY clause of a SELECT statement (query). In the previous hour, you learned how to use aggregate functions.
5/26/2009
1 p 153 The column name can also be represented by a number, which is discussed later in Representing Column Names with Numbers. The column name can also be represented by a number, which is discussed later in “Representing Column Names with Numbers.“ 5/26/2009
1 p 157 All non-aggregate columns selected must be listed in the GROUP BY clause. All nonaggregate columns selected must be listed in the GROUP BY clause. 5/26/2009
1 p 158 Notice the order of data in the previous results, as well as the LAST_NAME of the individual for each CITY. In the following example, all employee records in the EMPLOYEE_TBL table are now counted, and the results are grouped by CITY, but ordered by the count on each city first:

Bad break in mono term.
fixed 5/26/2009
1 p 159 The CREATE VIEW statement is discussed in depth in Hour 20, “Creating and Using Views and Synonyms.”


Bad break in mono term.
fixed 5/26/2009
1 p 159 You group the output by CITY, but only want to display those... You group the output by CITY, but you only want to display those... 5/26/2009
1 p 163 8. Write a query to list the average salary by position from the EMPLOYEE_PAY_TBL table where the average salary is greater than 20000. 8. Write a query to list the average salary by position from the EMPLOYEE_PAY_TBL table where the average salary is greater than $ 20,000. 5/26/2009
1 p 166 The following sections illustrate the application of ANSI concepts in some of the leading implementations of SQL, such as Oracle, Sybase, SQLBase, Informix, and SQL Server. The following sections illustrate the application of ANSI concepts in some of the leading implementations of SQL, such as Oracle, MySQL, Sybase, SQLBase, Informix, and SQL Server. 5/26/2009
1 p 167-168 All pipes should be solid. fixed 5/26/2009
1 p 167 This SQL Server statement concatenates the values for city and state into one value, placing a space between the two original values. This SQL Server statement concatenates the values for city and state into one value, placing a space between the two original values: 5/26/2009
1 p 168 This SQL Server statement concatenates the last name with the first name and inserts a comma between the two original values. This Oracle statement concatenates the last name with the first name and inserts a comma between the two original values: 5/26/2009
1 p 168 The TRANSLATE function searches a string of characters and checks for a specific character, makes note of the position found, searches the replacement string at the same position, and then replaces that character with the new value. The syntax is The TRANSLATE function searches a string of characters and checks for a specific character, makes note of the position found, searches the replacement string at the same position, and then replaces that character with the new value. This function is available only on Oracle systems. The syntax is 5/26/2009
1 p 171 The following SQL statement is what you would use for Oracle. The following SQL statement is what you would use for Oracle: 5/26/2009
1 p 173 This SQL statement returns the positions and also the returns the position with the word ‘SALES’ trimmed from the left side of the character string:
In most implementations the LTRIM function is used to only trim blank spaces from the left side of the string. Oracle provides for the full implementation of allowing you to specify the trimming set. This SQL statement also returns the positions with the word ‘SALES’ trimmed from the left side of the character string: 5/26/2009
1 p 173-174 This SQL statement returns the first name BRANDON and trims the ON, leaving BRAND as a result:
Like LTRIM, Oracle enables the full implementation to include the definition of the trimming set. Most other database implementations merely trim only the blank spaces from the right side of the string. This SQL statement returns the first name BRANDON and trims the ON, leaving BRAND as a result: 5/27/2009
1 p 174 This SQL statement returns a list of the postions in the PAY_TBL as well as the positions with the letters ‘ER’ trimmed from the right of the character string: This SQL statement returns a list of the positions in the PAY_TBL as well as the positions with the letters ER trimmed from the right of the character string: 5/27/2009
1 p 175 This SQL statement returns the product description and also its corresponding length: This SQL statement returns the product description and its corresponding length: 5/27/2009
1 p 181 PAY_RATE TO_CHAR(PAY_RATE)
---------- -----------------

alignment off.
fixed 5/27/2009
1 p 182 It is very important to realize that the ANSI standard is a guideline for how SQL should be implemented by vendors, but does not dictate the exact syntax or necessarily place limits on vendors’ innovations. It is very important to realize that the ANSI standard is a guideline for how SQL should be implemented by vendors, but it does not dictate the exact syntax or necessarily place limits on vendors’ innovations. 5/27/2009
1 p 183 Descriptions Functions
a. Used to select a portion of a character string ||
Description Function
a. Used to select a portion of a character string ||

(pipes should be solid)
5/27/2009
1 p 184 2. Type the following code to print each employee’s concatenated name and their area code:
SELECT CONCAT(LAST_NAME, ‘, ‘, FIRST_NAME), SUBSTRING(PHONE, 1, 3)
FROM EMPLOYEE_TBL;
3. Write a SQL statement that lists employee emails. Email is not a stored column. The email for each employee should be as follows:
FIRST.LAST@PERPTECH.COM
2. Type the following code to print each employee’s concatenated name and area code:
SELECT CONCAT(LAST_NAME, ‘, ‘, FIRST_NAME), SUBSTRING(PHONE, 1, 3)
FROM EMPLOYEE_TBL;
3. Write a SQL statement that lists employee emails. Email is not a stored column. The email for each employee should be as follows:
FIRST.LAST@PERPTECH.COM

(FIRST.LAST should be italic)
5/27/2009
1 p 184 4. Write a SQL statement that lists employee emails. Email is not a stored column. The email for each employee should be as follows:
FIRSTINITIAL.LAST@PERPTECH.COM
For example, John Smith’s email would be JSMITH@PERPTECH.COM.
5. Write a SQL statement that lists each employee’s name, employee ID, and phone number in the following formats:
. This name should be displayed as SMITH, JOHN
. The employee id should be displayed as 999-99-9999
. The phone number should be displayed as (999)999-9999
4. Write a SQL statement that lists employee emails. Email is not a stored column. The email for each employee should be as follows:
FIRSTINITIAL.LAST@PERPTECH.COM
For example, John Smith’s email address would be JSMITH@PERPTECH.COM.
5. Write a SQL statement that lists each employee’s name, employee ID, and phone number in the following formats:
. This name should be displayed as SMITH, JOHN
. The employee ID should be displayed as 999-99-9999
. The phone number should be displayed as (999)999-9999

(FIRSTINITIAL.LAST should be italic and the 3 bullets in #5 should not all be in mono font.)
5/27/2009
1 p 185 In this hour, you will learn about the nature of dates and time in SQL. In this hour, you will learn about the nature of dates and times in SQL. 5/27/2009
1 p 185 The highlights of this hour include The highlights of this hour include: 5/27/2009
1 p 186 DATETIME Element Valid Ranges

(DATETIME should be mono bold.)
fixed 5/27/2009
1 p 193 The following table displays some of the common date parts used in various implementations. Table 12.1 displays some of the common date parts used in various implementations. 5/27/2009
1 p 197 Last hour, you saw how functions varied depending on each implementation. In the previous hour, you saw how functions varied depending on each implementation. 5/27/2009
1 p 198 1. From where is the system date and time normally derived? 1. From where are the system date and time normally derived? 5/27/2009
1 p 203 The highlights of this hour include
The highlights of this hour include:
5/27/2009
1 p 204 Bullet list: Non-equijoins Nonequijoins 5/27/2009
1 p 206 The table being joined is added after the JOIN syntax and then the JOIN operators are placed after the ON qualifier. The table being joined is added after the JOIN syntax, and then the JOIN operators are placed after the ON qualifier. 5/27/2009
1 p 207 aligned last code segment and added code continuation characters fixed 5/27/2009
1 p 208 As you will learn later in the section on Self Joins, giving the tables aliases is a necessity for the self join. As you will learn later in the section on self joins, giving the tables aliases is a necessity for the self join. 5/27/2009
1 p 208 Joins of Non-Equality
A non-equijoin joins two or more tables based on a specified column value not equaling a specified column value in another table. The syntax for the non-equijoin is
Joins of Nonequality
A nonequijoin joins two or more tables based on a specified column value not equaling a specified column value in another table. The syntax for the nonequijoin is
5/27/2009
1 p 208 The following example is a join of non-equality:
The following example is a join of nonequality:
5/27/2009
1 p 209 Because non-equality was tested in the join of the two tables, each row in the first table is paired with all rows from the second table, except for
its own corresponding row.
Because nonequality was tested in the join of the two tables, each row in the first table is paired with all rows from the second table, except for
its own corresponding row.
5/27/2009
1 p 210 When using non-equijoins, you might receive several rows of data that are of no use to you. Check your results carefully. When using nonequijoins, you might receive several rows of data that are of no use to you. Check your results carefully. 5/27/2009
1 p 210 {RIGHT | LEFT | FULL} [OUTER] JOIN

Pipes should be solid.
fixed 5/28/2009
1 p 213 You might have a table that has a primary key that is comprised of more than one column. You might have a table that has a primary key that is composed of more than one column. 5/28/2009
1 p 213 PRODUCT_NAME NOT NULL VARCHAR2(30) PRODUCT_NAME NOT NULL VARCHAR(30) 5/28/2009
1 p 217 SQL> SELECT V FROM TABLE2;

X
SQL> SELECT V FROM TABLE2;

V
5/28/2009
1 p 220 1. Invoke MySQL, point to your learnsql database, and type the following code and study the resultset (Cartesian product):
1. Invoke MySQL, point to your learnsql database, type the following code, and study the resultset (Cartesian product): 5/28/2009
1 p 220 3. Rewrite the SQL query from Exercise 2, using the INNER JOIN syntax.
4. Write a SQL statement to return the EMP_ID, LAST_NAME, and FIRST_NAME columns from the EMPLOYEE_TBL and SALARY and BONUS columns from the EMPLOYEE_PAY_TBL. Use both types of INNER JOIN techniques.
3. Rewrite the SQL query from exercise 2, using the INNER JOIN syntax.
4. Write a SQL statement to return the EMP_ID, LAST_NAME, and FIRST_NAME columns from the EMPLOYEE_TBL, and SALARY and BONUS columns from the EMPLOYEE_PAY_TBL. Use both types of INNER JOIN techniques.

INNER should be mono font.
5/28/2009
1 p 221 The highlights of this hour include
The highlights of this hour include: 5/28/2009
1 p 222 A subquery cannot be immediately enclosed in a set function. A subquery cannot be immediately enclosed in a SET function.

SET in mono font.
5/28/2009
1 p 223 PAY_RATE
----------
11


Align heading.
Fixed 5/28/2009
1 p 223 SUM(COST)
----------
138.08

1 row selected.


Align heading.
Fixed 5/28/2009
1 p 228 SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_ID IN (SELECT O.CUST_ID
FROM ORDERS_TBL O, PRODUCTS_TBL P
WHERE O.PROD_ID = P.PROD_ID
AND O.QTY + P.COST > 138.08);
SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_ID IN (SELECT O.CUST_ID
FROM ORDERS_TBL O, PRODUCTS_TBL P
WHERE O.PROD_ID = P.PROD_ID
AND O.QTY * P.COST > 138.08);
5/28/2009
1 p 229 In the following example, the table join between CUSTOMER_TBL and ORDERS_TBL in the subquery is dependent on the alias for CUSTOMER_TBL (C) in the main query. In the following example, the table join between CUSTOMER_TBL and ORDERS_TBL in the subquery is dependent on the alias for CUSTOMER_TBL C in the main query. 5/28/2009
1 p 232 3. Are the following syntax(s) correct? If not, what is the correct syntax?
A.
SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_ID =
(SELECT CUST_ID
FROM ORDERS_TBL
WHERE ORD_NUM = ‘16C17’);
B.
SELECT EMP_ID, SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY BETWEEN ‘20000’
AND (SELECT SALARY
FROM EMPLOYEE_ID
WHERE SALARY = ‘40000’);
C.
UPDATE PRODUCTS_TBL
SET COST = 1.15
WHERE CUST_ID =
(SELECT CUST_ID
FROM ORDERS_TBL
WHERE ORD_NUM = ‘32A132’);
3. Are the following syntax(s) correct? If not, what is the correct syntax?
a.
SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_ID =
(SELECT CUST_ID
FROM ORDERS_TBL
WHERE ORD_NUM = ‘16C17’);
b.
SELECT EMP_ID, SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY BETWEEN ‘20000’
AND (SELECT SALARY
FROM EMPLOYEE_ID
WHERE SALARY = ‘40000’);
c.
UPDATE PRODUCTS_TBL
SET COST = 1.15
WHERE CUST_ID =
(SELECT CUST_ID
FROM ORDERS_TBL
WHERE ORD_NUM = ‘32A132’);
5/28/2009
1 p 233 2. Using a subquery, write an SQL statement to update the CUSTOMER_TBL table. 2. Using a subquery, write a SQL statement to update the CUSTOMER_TBL table. 5/28/2009
1 p 235 The highlights of this hour include The highlights of this hour include: 5/28/2009
1 p 237 This hour’s examples begin with a simple SELECT statement from two tables:
SELECT PROD_DESC FROM PRODUCTS_TBL;

PROD_DESC
-----------------------
WITCHES COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF

1 rows selected.
This hour’s examples begin with a simple SELECT statement from two tables:
SELECT PROD_DESC FROM PRODUCTS_TBL;

PROD_DESC
-----------------------
WITCHES COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF

11 rows selected.
5/28/2009
1 p 242 The following example demostrates the use of the MINUS operator as a replacement for the EXCEPT operator.

Line should be regular font except for mono terms.
fixed 5/28/2009
1 p 243 [ WHERE ]
OPERATOR{UNION | EXCEPT | INTERSECT | UNION ALL}
SELECT COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE ]
[ ORDER BY ]

Pipes should be solid.
fixed 5/28/2009
1 p 244 OPERATOR {UNION | EXCEPT | INTERSECT | UNION ALL}

Pipes should be solid.
fixed 5/28/2009
1 p 248 A.
SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
UNION
SELECT EMP_ID, POSITION, DATE_HIRE
FROM EMPLOYEE_PAY_TBL;
B.
SELECT EMP_ID FROM EMPLOYEE_TBL
UNION ALL
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL
ORDER BY EMP_ID;
C.
a.
SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
UNION
SELECT EMP_ID, POSITION, DATE_HIRE
FROM EMPLOYEE_PAY_TBL;
b.
SELECT EMP_ID FROM EMPLOYEE_TBL
UNION ALL
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL
ORDER BY EMP_ID;
c.
5/28/2009
1 p 249 Write your queries out by hand on a sheet of paper because MySQL does not support some of the operators covered in this hour.
Write out your queries by hand on a sheet of paper because MySQL does not support some of the operators covered in this hour.
5/28/2009
1 p 249 1. Write a compound query to find the customers that have placed an order.
2. Write a compound query to find the customers that have not placed an order.
1. Write a compound query to find the customers who have placed an order.
2. Write a compound query to find the customers who have not placed an order.
5/28/2009
1 p 253 The highlights of this hour include The highlights of this hour include: 5/28/2009
1 p 254 Therefore, they need to be considered when estimating the size your database storage needs.
Therefore, they need to be considered when estimating the size that your database storage needs.
5/28/2009
1 p 255 Some implementations allow the specification of a storage clause (as with the CREATE TABLE statement), ordering (DESC||ASC), and the use of clusters.

Pipes should be solid.
fixed 5/28/2009
1 p 261 Indexes might need to be rebuilt sometimes to reduce fragmentation. Indexes might need to be rebuilt to reduce fragmentation. 5/28/2009
1 p 261 A. Many factors are involved, such as the size of the index being dropped, CPU usage, and the machine’s power. A. Many factors are involved, such as the size of the index being dropped, the CPU usage, and the machine’s power. 5/28/2009
1 p 262 A. Several columns, but a rather small table
B. Medium-sized table, no duplicates should be allowed
C. Several columns, very large table, several columns used as filters in the WHERE clause
D. Large table, many columns, a lot of data manipulation

a. Several columns, but a rather small table
b. Medium-sized table, no duplicates should be allowed
c. Several columns, very large table, several columns used as filters in the WHERE clause
d. Large table, many columns, a lot of data manipulation

5/28/2009
1 p 265 The highlights of this hour include The highlights of this hour include: 5/28/2009
1 p 267 Most relational database implementations have what is called an SQL optimizer, which evaluates a SQL statement and determines the best method for executing the statement based on the way a SQL statement is written and the availability of indexes in the database. Most relational database implementations have what is called a SQL optimizer, which evaluates a SQL statement and determines the best method for executing the statement based on the way a SQL statement is written and the availability of indexes in the database. 5/28/2009
1 p 267-8 Remove em dashes from basic rules bullets for consistency. fixed 5/28/2009
1 p 274 Query 3 also picks up any last name starting with ST; this is the only way to assure that you receive all the Stevens (or Stephens).
Query 3 also picks up any last name starting with ST; this is the only way to ensure that you receive all the Stevens (or Stephens).
5/28/2009
1 p 276 Another tool in Oracle measures the actual elapsed time of a SQL statement is TKPROF. Another tool in Oracle that measures the actual elapsed time of a SQL statement is TKPROF. 5/28/2009
1 p 278 3. Should the most restrictive clause(s) be placed before the join condition(s) or after the join conditions in the WHERE clause?
3. Should the most restrictive clause(s) be placed before the join condition(s) or after the join conditions(s) in the WHERE clause?
5/28/2009
1 p 279 ABC numbers should be lowercase. fixed 5/28/2009
1 p 283 You will learn the concepts behind creating users in SQL, user security, the user versus the schema, user profiles, user attributes, and tools users utilize.
You will learn the concepts behind creating users in SQL, user security, the user versus the schema, user profiles, user attributes, and tools that users utilize.
5/28/2009
1 p 284 The user’s needs are taken into consideration when the database is designed, and the final goal in implementing a database is making the database available to users, who in turn utilize the database that you and possibly many others have had a hand in developing.
The users’ needs are taken into consideration when the database is designed, and the final goal in implementing a database is making the database available to users, who in turn utilize the database that you and possibly many others have had a hand in developing.
5/28/2009
1 p 285 Documentation also allows you to have a paper trail in which to point to when the security of your system would possibly need to be audited either internally or externally.
Documentation also allows you to have a paper trail to point to when the security of your system would possibly need to be audited either internally or externally.
5/28/2009
1 p 286 User account management is one of the simplest database management tasks, theoretically, but is often complicated by politics and communication problems.
User account management is one of the simplest database management tasks, theoretically, but it is often complicated by politics and communication problems.
5/28/2009
1 p 290 CREATE SCHEMA AUTHORIZATION USER1
CREATE TABLE EMP
(ID NUMBER NOT NULL,
NAME VARCHAR2(10) NOT NULL)
CREATE TABLE CUST
(ID NUMBER NOT NULL,
NAME VARCHAR2(10) NOT NULL)
GRANT SELECT ON TBL1 TO USER2
GRANT SELECT ON TBL2 TO USER2;

Schema created.
CREATE SCHEMA AUTHORIZATION USER1
CREATE TABLE EMP
(ID NUMBER NOT NULL,
NAME VARCHAR(10) NOT NULL)
CREATE TABLE CUST
(ID NUMBER NOT NULL,
NAME VARCHAR(10) NOT NULL)
GRANT SELECT ON TBL1 TO USER2
GRANT SELECT ON TBL2 TO USER2;

Schema created.
5/28/2009
1 p 290 First, the RESTRICT option. First is the RESTRICT option. 5/28/2009
1 p 291 If the DROP SCHEMA command is not available in your implementation, you can remove a schema by removing the user that owns the schema objects. If the DROP SCHEMA command is not available in your implementation, you can remove a schema by removing the user who owns the schema objects. 5/28/2009
1 p 292 An SQL session is initiated when a user connects from the client to the server using the CONNECT statement. A SQL session is initiated when a user connects from the client to the server using the CONNECT statement. 5/28/2009
1 p 292 Most users access the database through a vendor-provided or third-party tool that prompts the user for a username and password, which in turn connects to the database and initiates a database user session.
Most users access the database through a vendor-provided or third-party tool that prompts them for a username and password, which in turn connects to the database and initiates a database user session.
5/28/2009
1 p 294 Upon user creation or addition to the database, a generic password is usually given to the user and must be changed as quickly as possible by the user to a password of his choice. Upon user creation or addition to the database, a generic password is usually given to the user that he must change as quickly as possible to a password of his choice. 5/28/2009
1 p 297 The highlights of this hour include
The highlights of this hour include:
5/28/2009
1 p 298 Although user management and database security are definitely related, each has its own purpose and work together to achieve a secure database. Although user management and database security are definitely related, each has its own purpose and works with other to achieve a secure database. 5/28/2009
1 p 304 Therefore, by granting permissions to public you may unintentionally give access to data to users whom have no business accessing it. Therefore, by granting permissions to PUBLIC, you may unintentionally give access to data to users whom have no business accessing it.

PUBLIC should be mono font.
5/28/2009
1 p 306 Let’s say that a schema named APP01 grants the SELECT table privilege to the RECORDS_CLERK role on the EMPLOYEE_PAY table.

Bad break of mono term.
fixed 5/28/2009
1 p 310 5. Get a list of all database users by typing the following:
SELECT * FROM USER;

Code line should be bold.
fixed 5/28/2009
1 p 313 The highlights of this hour include The highlights of this hour include: 5/28/2009
1 p 315 To bridge this gap, you would create a view which would contain the join and give the end users the right to select from the view. To bridge this gap, you would create a view that would contain the join and give the end users the right to select from the view. 5/28/2009
1 p 316 For example, older versions of MySQL you would use REPLACE VIEW to alter a current view. For example, in older versions of MySQL, you would use REPLACE VIEW to alter a current view. 5/28/2009
1 p 316 The WITH CHECK OPTION is discussed later this hour in the WITH CHECK OPTION section. The WITH CHECK OPTION is discussed later this hour in the “WITH CHECK OPTION” section. 5/28/2009
1 p 318 SELECT E.CITY, AVG(P PAY_RATE) AVG_PAY SELECT E.CITY, AVG(P.PAY_RATE)
AVG_PAY
6/1/2009
1 p 318 WITH CHECK OPTION is discussed later this hour in the WITH CHECK OPTION section. WITH CHECK OPTION is discussed later this hour in the “WITH CHECK OPTION” section. 6/1/2009
1 p 323 Running head: Views and the Order By Clause Dropping a View 6/2/2009
1 p 324 A PUBLIC synonym can be used by any user of the database; a PRIVATE synonym can be used only by the owner and any users that have been granted privileges. A PUBLIC synonym can be used by any user of the database; a PRIVATE synonym can be used only by the owner and any users who have been granted privileges. 6/2/2009
1 p 324 This frees you from having to spell out the full table name. This frees you from having to spell out the full table name, but is not available in MySQL. 6/2/2009
1 p 327 3. Write statements that drop the two views that you created in Exercises 1 and 2. 3. Write statements that drop the two views that you created in exercises 1 and 2. 6/2/2009
1 p 329 The highlights of this hour include The highlights of this hour include: 6/2/2009
1 p 329 Information in the system catalog defines the structure of the database and also information on the data contained therein. Information in the system catalog defines the structure of the database and information on the data contained therein. 6/2/2009
1 p 331 In MySQL the database is contained in the mysql system database. In MySQL, the database is contained in the mysql system database. 6/2/2009
1 p 332 The system catalog also stores security information, such as user identifications, encrypted passwords, and various privileges and groups of privileges database users utilize to access the data. The system catalog also stores security information, such as user identifications, encrypted passwords, and various privileges and groups of privileges that database users utilize to access the data. 6/2/2009
1 p 335 SELECT USER
FROM ALL_USER;

USER
SELECT USER
FROM USER;

USER
6/2/2009
1 p 336 The following is an example from MS Access: The following is an example from Microsoft Access: 6/2/2009
1 p 338 A. Users can use sets of tables and/or views to query in most system catalogs. One set of these tables and views includes information on what objects to which you have access. In order to find out about other user’s access you would need to check the system catalogs that contain that information. For example, in Oracle you could check the DBA_TABLES and DBA_USERS system catalogs. A. Users can use sets of tables and/or views to query in most system catalogs. One set of these tables and views includes information on what objects you have access to. In order to find out about other user’s access, you would need to check the system catalogs that contain that information. For example, in Oracle you could check the DBA_TABLES and DBA_USERS system catalogs.


DBA_TABLES and DBA_USERS should be in mono font.
6/2/2009
1 p 343 The highlights of this hour include The highlights of this hour include: 6/2/2009
1 p 344 An SQL cursor is an area in database memory where the last SQL statement is stored. A SQL cursor is an area in database memory where the last SQL statement is stored. 6/2/2009
1 p 348 An example of a very simple stored procedure to insert new rows into the
PROD-UCTS_TBL table is as follows:

Mono term should not be broken.
An example of a very simple stored procedure to insert new rows into the
PRODUCTS_TBL table is as follows:
6/2/2009
1 p 349 A trigger can be created using the CREATE TRIGGER.

A trigger can be created using the CREATE TRIGGER statement. 6/2/2009
1 p 349 A trigger can be created using the CREATE TRIGGER.

A trigger can be created using the CREATE TRIGGER statement. 6/2/2009
1 p 353 Next time you are writing SQL statements and have repeated the same statement several times, allow your imagination to take hold and let SQL do the work for you. The next time you are writing SQL statements and have repeated the same statement several times, allow your imagination to take hold and let SQL do the work for you. 6/2/2009
1 p 354 Almost all aggregate functions can be used as windowed table functions and they provide five new windowed table functions:
Almost all aggregate functions can be used as windowed table functions, and they provide five new windowed table functions:
6/2/2009
1 p 354 Normally, it would be difficult to calculate something like an individual’s ranking within their pay year. Normally, it would be difficult to calculate something like an individual’s ranking within his pay year. 6/2/2009
1 p 354 The ANSI standard presented an XML-related features section in their 2003 version. The ANSI standard presented an XML-related features section in its 2003 version. 6/2/2009
1 p 357 (Hint: It is similar to Exercise 1.) (Hint: It is similar to exercise 1.) 6/2/2009
1 p 359 The highlights of this hour include
The highlights of this hour include:
6/2/2009
1 p 360 The back-end
application includes the actual database server, data sources, and the appropriate middleware used to connect an application to the Web or a remote database on the local network.
The back-end
application includes the actual database server, the data sources, and the appropriate middleware used to connect an application to the Web or a remote database on the local network.
6/2/2009
1 p 362 ODBC is now a standard and is used by several products, such as Sybase’s PowerBuilder, FoxPro, Visual C++, Visual Basic, Borland’s Delphi, Microsoft Access, ASP.NET and many more.
ODBC is now a standard and is used by several products, such as Sybase’s PowerBuilder, FoxPro, Visual C++, Visual Basic, Borland’s Delphi, Microsoft Access, ASP.NET, and many more.
6/2/2009
1 p 366 SQL requests made through the internet must be extremely conscience of performance. In these scenarios not only must the data be retrieved from the database but it must also then be presented to the user through her browser. This normally involves transforming the data into some kind of HTML-compliant code to be displayed on the user’s browser. Additionally, the web connection might also be slower than a normal intranet connection and therefore the sending of the data back and forth might be slower as well. SQL requests made through the internet must be extremely conscience of performance. In these scenarios, not only must the data be retrieved from the database, but it must also then be presented to the user through her browser. This normally involves transforming the data into some kind of HTML-compliant code to be displayed on the user’s browser. Additionally, the web connection might be slower than a normal intranet connection; therefore, the sending of the data back and forth might be slower as well. 6/2/2009
1 p 367 Q. Is a back-end database for a web application any different than a back-end database for a client/server system?
A. The back-end database itself for a web application is not necessarily any different than that of a client/server system.
Q. Is a back-end database for a web application any different from a back-end database for a client/server system?
A. The back-end database itself for a web application is not necessarily any different from that of a client/server system.
6/2/2009
1 p 367 5. How is a remote database accessed through a Web application? 5. How is a remote database accessed through a web application? 6/2/2009
1 p 368 A. Does the page come up quickly or is it bogged down with too many graphics?
B. Is the page interesting to read?
C. Do you know anything about the company, services, or products after reading the available information?
D. If applicable, has access to the database been easy?
E. Do there appear to be any security mechanisms on the web page? Can a login be entered to access data that might be stored in a database?
a. Does the page come up quickly or is it bogged down with too many graphics?
b. Is the page interesting to read?
c. Do you know anything about the company, services, or products after reading the available information?
d. If applicable, has access to the database been easy?
e. Do there appear to be any security mechanisms on the web page? Can a login be entered to access data that might be stored in a database?
6/2/2009
1 p 369 Although most implementations conform to the standard for the most part, many vendors have provided extensions to standard SQL through various enhancements.

Although most implementations conform to the standard, many vendors have provided extensions to standard SQL through various enhancements.
6/2/2009
1 p 369 The highlights of this hour include The highlights of this hour include: 6/2/2009
1 p 371 Therefore, limiting your code to only strict ANSI compliant statements would improve portability but would most likely severely limit database performance. Therefore, limiting your code to only strict ANSI-compliant statements would improve portability but would most likely severely limit database performance. 6/2/2009
1 p 373 SQL is non-procedural, and the database decides how and where to select and manipulate data.

non-procedural bad break.
fixed 6/2/2009
1 p 375 MySQL can be downloaded from http://www.mysql.com.

URL to be consistent with others, should not be in mono.
fixed 6/2/2009
1 p 375 The statement is used to create users into a database. The statement is used to create users in a database. 6/2/2009
1 p 377 2. What are some of the reasons differences in SQL exist? 2. What are some of the reasons that differences in SQL exist? 6/2/2009
1 p 381 The following Appendix details some of the most common SQL commands that you will use. As we have stated throughtout the book, check your database documentation as some of the statements will vary depending upon your implementation.
The following appendix details some of the most common SQL commands that you will use. As we have stated throughtout the book, check your database documentation, as some of the statements will vary depending upon your implementation. 6/2/2009
1 p 392 1. Identify in what categories the following SQL commands fall:
1. Identify the categories in which the following SQL commands fall: 6/3/2009
1 p 394 5. Which numbers could be inserted into a DECIMAL(4,1)? 1. Identify the categories in which the following SQL commands fall: 6/3/2009
1 p 395 Every individual might not have a phone (however rare that might be) and not everyone has a middle name, so these columns should allow NULL values. Every individual might not have a phone (however rare that might be), and not everyone has a middle name, so these columns should allow NULL values. 6/3/2009
1 p 395 1. Will the following CREATE TABLE statement work? If not, what needs to be done to correct the problem(s)?
CREATE TABLE EMPLOYEE_TABLE AS:
( SSN NUMBER(9) NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
FIRST_NAME VARCHAR(20) NOT NULL,
MIDDLE_NAME VARCHAR2(20) NOT NULL,
ST_ADDRESS VARCHAR2(20) NOT NULL,
CITY CHAR(20) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP NUMBER(4) NOT NULL,
DATE_HIRED DATE);
1. Will the following CREATE TABLE statement work? If not, what needs to be done to correct the problem(s)?
CREATE TABLE EMPLOYEE_TABLE AS:
( SSN NUMERIC(9) NOT NULL,
LAST_NAME VARCHAR(20) NOT NULL
FIRST_NAME VARCHAR(20) NOT NULL,
MIDDLE_NAME VARCHAR(20) NOT NULL,
ST ADDRESS VARCHAR(20) NOT NULL,
CITY CHAR(20) NOT NULL,
STATE CHAR 2) NOT NULL,
ZIP NUMERIC(4) NOT NULL,
DATE HIRED DATE);
6/3/2009
1 p 396 A. The CREATE TABLE statement will not work because there are several errors in the syntax. The corrected statement follows. A listing of what was incorrect follows a corrected statement.
CREATE TABLE EMPLOYEE_TABLE
( SSN NUMBER() NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
FIRST_NAME VARCHAR2(20) NOT NULL,
MIDDLE_NAME VARCHAR2(20),
ST_ADDRESS VARCHAR2(30) NOT NULL,
CITY VARCHAR2(20) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP NUMBER(5) NOT NULL,
DATE_HIRED DATE );

A. The CREATE TABLE statement will not work because there are several errors in the syntax. The corrected statement follows. A listing of what was incorrect follows a corrected statement.
CREATE TABLE EMPLOYEE_TABLE
( SSN NUMERIC() NOT NULL,
LAST_NAME VARCHAR(20) NOT NULL,
FIRST_NAME VARCHAR(20) NOT NULL,
MIDDLE_NAME VARCHAR(20) NULL,
ST_ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(20) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP NUMERIC(5) NOT NULL,
DATE_HIRED DATE );;
6/3/2009
1 p 396 5. The CITY column works, although it would be better to use the
VARCHAR2 data type. If all city names were a constant length, CHAR would be okay.
5. The CITY column works, although it would be better to use the
VARCHAR data type. If all city names were a constant length, CHAR would be okay.
6/3/2009
1 p 396 9. The comma after 3K in the STORAGE clause should not be there.

Delete statement.
fixed 6/3/2009
1 p 396 2. Can I drop a column from a table? 2. Can you drop a column from a table? 6/3/2009
1 p 397 5. What statement would you use in order to restrict the people added into the preceding EMPLOYEE_TABLE to only reside in the state of New York (‘NY’)? 5. What statement would you use to restrict the people added into the preceding EMPLOYEE_TABLE to only reside in the state of New York (‘NY’)? 6/3/2009
1 p 397 6. What statement would you use in order to add an auto-incrementing column called ‘EMPID’ to the preceding EMPLOYEE_TABLE? 6. What statement would you use to add an auto-incrementing column called ‘EMPID’ to the preceding EMPLOYEE_TABLE? 6/3/2009
1 p 398 2. True or false: Having no duplicate or redundant data in a database and having everything in the database normalized is always the best way to go. 2. True or false: Having no duplicate or redundant data in a database, and having everything in the database normalized, is always the best way to go. 6/3/2009
1 p 398 A. Having redundant and duplicate data takes up valuable space; it is harder to code, and much more data maintenance is required. A. Having redundant and duplicate data takes up valuable space, it is harder to code, and much more data maintenance is required. 6/3/2009
1 p 400 1. Using the EMPLOYEE_TBL with the structure:
column data type (not)null
last_name varchar2(20) not null
first_name varchar2(20) not null
ssn char(9) not null
phone number(10) null

LAST_NAME FIRST_NAME SSN PHONE
SMITH JOHN 312456788 3174549923
ROBERTS LISA 232118857 3175452321
SMITH SUE 443221989 3178398712
PIERCE BILLY 310239856 3176763990
1. Using the EMPLOYEE_TBL with the structure:
column data type (not)null
last_name varchar(20) not null
first_name varchar(20) not null
ssn char(9) not null
phone numerIC(10) null

LAST_NAME FIRST_NAME SSN PHONE
SMITH JOHN 312456788 3174549923
ROBERTS LISA 232118857 3175452321
SMITH SUE 443221989 3178398712
PIERCE BILLY 310239856 3176763990
6/3/2009
1 p 400 e. DELETE FROM RMPLOYEE_TBL;

e. DELETE FROM EMPLOYEE_TBL;

6/3/2009
1 p 402 The cost should be the same as the witch’s costume.
The cost should be the same as the witches costume.
6/3/2009
1 p 402 1. True or false: If you have committed several transactions and have several more transactions that have not been committed and you issue a ROLLBACK command, all your transactions for the same session will be undone. 1. True or false: If you have committed several transactions, have several more transactions that have not been committed, and you issue a ROLLBACK command, all your transactions for the same session are undone. 6/3/2009
1 p 402 2. True or false: A savepoint actually saves transactions after a specified amount of transactions have executed.
A. False. A savepoint is only used as a point for a rollback to return to..
2. True or false: A SAVEPOINT command actually saves transactions after a specified number of transactions have executed.
A. False. A SAVEPOINT command is only used as a point for a ROLLBACK to return to..
6/3/2009
1 p 403 1. Take the following transactions and create a savepoint after the first three transactions. Then place a rollback statement to your savepoint at the end. Try to determine what the CUSTOMER_TBL will look like after you are done. 1. Take the following transactions and create a SAVEPOINT command after the first three transactions. Then place a ROLLBACK statement to your SAVEPOINT at the end. Try to determine what the CUSTOMER_TBL will look like after you are done. 6/3/2009
1 p 403 2. Take the following group of transactions and create a savepoint after the first three transactions.
Then place a COMMIT statement at the end followed by a ROLLBACK statement to your savepoint. What do you think should happen?
2. Take the following group of transactions and create a SAVEPOINT command after the first three transactions.
Then place a COMMIT statement at the end followed by a ROLLBACK statement to your SAVEPOINT. What do you think should happen?
6/3/2009
1 p 406 3. Write a SELECT statement that returns the name and cost of each product from the PRODUCTS_TBL. Which product is the most expensive?

SELECT and PRODUCTS_TBL should be in mono font.
fixed 6/3/2009
1 p 407 1. Using the following CUSTOMER_TBL:
DESCRIBE CUSTOMER_TBL
Name Null? Type


DESCRIBE CUSTOMER_TBL should be bold.

Name Null? Type should be caps.
fixed 6/3/2009
1 p 408 Write a SELECT statement that returns customer IDs and customer names (alpha order) for customers who live in Indiana, Ohio, Michigan, and Illinois, with names that begin with the letters A or B. Write a SELECT statement that returns customer IDs and customer names (alpha order) for customers who live in Indiana, Ohio, Michigan, and Illinois, and whose names begin with the letters A or B. 6/3/2009
1 p 408 2. Using the following PRODUCTS_TBL:
DESCRIBE PRODUCTS_TBL
Name Null? Type

DESCRIBE CUSTOMER_TBL should be bold.

Name Null? Type should be caps.
fixed 6/3/2009
1 p 408 Write a SELECT statement that returns the product ID, product description, and the product cost. Limit the product cost to range from $1.00 to $12.50.
Write a SELECT statement that returns the product ID, the product description, and the product cost. Limit the product cost to range from $1.00 to $12.50.
6/3/2009
1 p 408 4. Write a SELECT statement that returns products that are either less than 1.00 or greater than 12.50. 4. Write a SELECT statement that returns products that are either less than $1.00 or greater than $12.50. 6/3/2009
1 p 409 1. The AVG function returns an average of all rows from a select column, including any NULL values.
A. False. The NULL values are not considered.
2. The SUM function is used to add column totals.
A. False. The SUM function is used to return a total for a group of rows.
3. The COUNT(*) function counts all rows in a table.
A. True.
1. True or false: The AVG function returns an average of all rows from a select column, including any NULL values.
A. False. The NULL values are not considered.
2. True or false: The SUM function is used to add column totals.
A. False. The SUM function is used to return a total for a group of rows.
3. True or false: The COUNT(*) function counts all rows in a table.
A. True.
6/3/2009
1 p 410 b. SELECT COUNT(EMP_ID), SALARY
FROM EMPLOYEE_PAY_TBL
GROUP BY SALARY;
b. SELECT COUNT(EMP_ID), SALARY
FROM EMPLOYEE_PAY_TBL

6/3/2009
1 p 410 A. No, this statement will not work because LAST_NAME needs to be a
numeric value.

LAST_NAME should be in mono font.
fixed 6/3/2009
1 p 411 c. What is the total of all the salaries? c. What are the total salaries? 6/3/2009
1 p 411 3. If every product cost $10.00, what would be the total dollar amount for all orders? 3. If every product costs $10.00, what would be the total dollar amount for all orders? 6/3/2009
1 p 414 8. Write a query to list the average salary by position from the EMPLOYEE_PAY_TBL where the average salary is greater than 20000.

EMPLOYEE_PAY_TBL should be in mono font.
8. Write a query to list the average salary by position from the EMPLOYEE_PAY_TBL where the average salary is greater than $20,000. 6/3/2009
1 p 414 Descriptions Functions Description Function 6/3/2009
1 p 414 2. True or false: Using functions in a select statement to restructure the appearance of data in output will also affect the way the data is stored in the database.
2. True or false: Using functions in a SELECT statement to restructure the appearance of data in output will also affect the way the data is stored in the database.
6/3/2009
1 p 415 For example, John Smith’s email address would be JMITH@PERPTECH.COM. For example, John Smith’s email address would be JSMITH@PERPTECH.COM. 6/3/2009
1 p 415 5. Write a SQL statement that lists each employee’s name and phone number in the following formats:
5. Write a SQL statement that lists each employee’s name, employee ID, and phone number in the following formats: 6/3/2009
1 p 416 Hour 12, “Understanding Dates and Time” Hour 12, “Understanding Dates and Times” 6/3/2009
1 p 417 2. The JOIN conditions are located in what part of the SQL statement? 2. The JOIN conditions are located in which parts of the SQL statement? 6/3/2009
1 p 417 5. Use the following tables:
ORDERS_TBL
ORD_NUM VARCHAR2(10) NOT NULL primary key
CUST_ID VARCHAR2(10) NOT NULL
PROD_ID VARCHAR2(10) NOT NULL
QTY INTEGER NOT NULL
ORD_DATE DATE

PRODUCTS_TBL
PROD_ID VARCHAR2(10) NOT NULL primary key
PROD_DESC VARCHAR2(40) NOT NULL
COST DECIMAL(,2) NOT NULL
5. Use the following tables:
ORDERS_TBL
ORD_NUM VARCHAR(10) NOT NULL primary key
CUST_ID VARCHAR(10) NOT NULL
PROD_ID VARCHAR(10) NOT NULL
QTY INTEGER NOT NULL
ORD_DATE DATETIME

PRODUCTS_TBL
PROD_ID VARCHAR(10) NOT NULL primary key
PROD_DESC VARCHAR(40) NOT NULL
COST DECIMAL(6,2) NOT NULL
6/3/2009
1 p 418 Use both types of join techniques. Use both types of INNER JOIN techniques. 6/3/2009
1 p 421 EMPLOYEE_TBL
EMP_ID VARCHAR(9) NOT NULL,
LAST_NAME VARCHAR(15) NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
MIDDLE_NAME VARCHAR(15),
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE VARCHAR(10),
PAGER VARCHAR(10),


EMPLOYEE_PAY_TBL
EMP_ID VARCHAR(9) NOT NULL, primary key,
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATETIME,
PAY_RATE DECIMAL(4,2) NOT NULL,
DATE_LASTRAISE DATE,
SALARY DECIMAL(8,2),
BONUS DECIMAL(6,2),
EMPLOYEE_TBL
EMP_ID VARCHAR(9) NOT NULL,
LAST_NAME VARCHAR(15) NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
MIDDLE_NAME VARCHAR(15),
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE VARCHAR(10),
PAGER VARCHAR(10),
CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)

EMPLOYEE_PAY_TBL
EMP_ID VARCHAR(9) NOT NULL, primary key,
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATETIME,
PAY_RATE DECIMAL(4,2) NOT NULL,
DATE_LASTRAISE DATE,
SALARY DECIMAL(8,2),
BONUS DECIMAL(6,2),
CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID)
REFERENCES EMPLOYEE_TBL (EMP_ID)
6/3/2009
1 p 422 Refer to the Oracle syntax covered in this hour for the following exercises. Write your queries out by hand on a sheet of paper because MySQL does not support the operators covered in this hour. When you are finished, compare your results to ours.
Using the CUSTOMER_TBL and the ORDERS_TBL as listed:
CUSTOMER_TBL
CUST_IN VARCHAR(10) NOT NULL primary key
CUST_NAME VARCHAR(30) NOT NULL,
CUST_ADDRESS VARCHAR(20) NOT NULL,
CUST_CITY VARCHAR(15) NOT NULL,
CUST_STATE VARCHAR(2) NOT NULL,
CUST_ZIP INTEGER(5) NOT NULL,
CUST_PHONE INTEGER(10),
CUST_FAX INTEGER(10)

ORDERS_TBL
ORD_NUM VARCHAR(10) NOT NULL primary key
CUST_ID VARCHAR(10) NOT NULL,
PROD_ID VARCHAR(10) NOT NULL,
QTY INTEGER(6) NOT NULL,
ORD_DATE DATETIME
Refer to the Oracle syntax covered in this hour for the following exercises. Write out your queries by hand on a sheet of paper because MySQL does not support some of the operators covered in this hour. When you are finished, compare your results to ours.
Using the CUSTOMER_TBL and the ORDERS_TBL as listed:
CUSTOMER_TBL
CUST_IN VARCHAR(10) NOT NULL primary key,
CUST_NAME VARCHAR(30) NOT NULL,
CUST_ADDRESS VARCHAR(20) NOT NULL,
CUST_CITY VARCHAR(15) NOT NULL,
CUST_STATE VARCHAR(2) NOT NULL,
CUST_ZIP INTEGER(5) NOT NULL,
CUST_PHONE INTEGER(10),
CUST_FAX INTEGER(10)

ORDERS_TBL
ORD_NUM VARCHAR(10) NOT NULL primary key,
CUST_ID VARCHAR(10) NOT NULL,
PROD_ID VARCHAR(10) NOT NULL,
QTY INTEGER(6) NOT NULL,
ORD_DATE DATETIME
6/3/2009
1 p 424 1. Decide whether an index should be used in the following situations, and if so, what type of index should be used.
1. For the following situations, decide whether an index should be used in the following situations, and if so, what type of index should be used. 6/3/2009
1 p 424 4. Create a multi-column index on the ORDERS_TBL table. Include the following columns: CUST_ID, PROD_ID, ORD_DATE. 4. Create a multi-column index on the ORDERS_TBL table. Include the following columns: CUST_ID, PROD_ID, and ORD_DATE. 6/3/2009
1 p 425 3. Should the most restrictive clause(s) be evaluated before or after the join condition(s) in the WHERE clause?
3. Should the most restrictive clause(s) be evaluated before the join condition(s) or after the join condition(s) in the WHERE clause?
6/3/2009
1 p 425 EMPLOYEE_TBL
EMP_ID VARCHAR(9) NOT NULL Primary key
LAST_NAME VARCHAR(15) NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
MIDDLE_NAME VARCHAR(15),
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE VARCHAR(10),
PAGER VARCHAR(10),


EMPLOYEE_PAY_TBL
EMP_ID VARCHAR(9) NOT NULL primary key
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATETIME,
PAY_RATE DECIMAL(4,2) NOT NULL,
DATE_LAST_RAISE DATETIME,
SALARY DECIMAL(8,2),
BONUS DECIMAL(8,2),

EMPLOYEE_TBL
EMP_ID VARCHAR(9) NOT NULL primary key,
LAST_NAME VARCHAR(15) NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
MIDDLE_NAME VARCHAR(15),
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE VARCHAR(10),
PAGER VARCHAR(10),
CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)

EMPLOYEE_PAY_TBL
EMP_ID VARCHAR(9) NOT NULL primary key,
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATETIME,
PAY_RATE DECIMAL(4,2) NOT NULL,
DATE_LAST_RAISE DATETIME,
SALARY DECIMAL(8,2),
BONUS DECIMAL(8,2),
CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID)
REFERENCES EMPLOYEE_TBL (EMP_ID)
6/3/2009
1 p 428 2. When privileges are granted to PUBLIC, do all users of the database acquire the privileges, or just a listing of chosen users?


2. When privileges are granted to PUBLIC, do all users of the database acquire the privileges, or only specified users?
6/3/2009
1 p 428 4. What type of privilege is the SELECT privilege?
4. What type of privilege is SELECT? 6/3/2009
1 p 430 3. Write statements that drop the two views that you created in Exercises 1
and 2.
3. Write statements that drop the two views that you created in exercises 1
and 2.
6/3/2009
1 p 430 3. What Sybase system table would be used to retrieve information about views that exist in the database?
A. SYSVIEWS would be used.
4. Who owns the system catalog?
A. The owner of the system catalog is often a privileged database user account called SYS or SYSTEM. The system catalog can also be owned by the owner of the database, but is not ordinarily owned by a particular schema in the database.
3. What Sybase system table is used to retrieve information about views that exist in the database?
A. SYSVIEWS is used.
4. Who owns the system catalog?
A. The owner of the system catalog is often a privileged database user account called SYS or SYSTEM. The system catalog can also be owned by the owner of the database, but it is not ordinarily owned by a particular schema in the database.
6/3/2009
1 p 433 A. Yes. SQL can be embedded in Internet programming languages, such as Java. A. Yes. SQL can be embedded into Internet programming languages, such as Java. 6/3/2009
1 p 433 2. What are some of the reasons differences in SQL exist? 2. What are some of the reasons that differences in SQL exist? 6/3/2009
1 p 443 13. Last line of code:
INSERT INTO SCORES VALUES (‘10’,’4’,’78’,’WIN’)
INSERT INTO SCORES VALUES (‘10’,’4’,’78’,’WIN’); 6/3/2009
1 p 444 24. How many games were played on the 5th of May, 2002?
24. How many games were played on May 5, 2002?
6/3/2009
1 p 447 client  The client is typically a PC, but can be another server that is dependent on another computer for data, services, or processing. A client application enables a client machine to communicate with a server. client  The client is typically a PC, but it can be another server that is dependent on another computer for data, services, or processing. A client application enables a client machine to communicate with a server. 6/3/2009
1 p 447 data dictionary  Another name for the system catalog. See system catalog. data dictionary  Another name for the system catalog. See system catalog. 6/3/2009
1 p 448 DDL  Data Definition Language.The part of the SQL syntax that specifically deals with defining database objects such a tables, views, and functions. DDL  Data Definition Language. The part of the SQL syntax that specifically deals with defining database objects such as tables, views, and functions. 6/3/2009
1 p 448 DQL  Data Query Language. The part of the SQL syntax that specifically deals with querying data using the SELECT statement.


SELECT should be in mono font.
fixed 6/3/2009
1 p 449 referential integrity  Assures that values from one column depend on the values from another column. Referential integrity is normally used between two tables, but in some tables, can be used so that a table references itself. A self-referenced table is referred to as a recursive relationship.
referential integrity  Assures that values from one column depend on the
values from another column. Referential integrity is normally used between two tables, but in some tables, it can be used so that a table references itself. A self-
referenced table is referred to as a
recursive relationship.
6/3/2009
1 p 451 != (non-equality operator), 119
; (semicolons), 46
“ (single quotation marks), 353
!= (nonequality operator), 119
; (semicolons), 46
‘ (single quotation marks), 353
6/3/2009
1 p 454 non-equality, 119 nonequality, 119 6/3/2009
1 p 461 greater than operator ( ), 119-120 greater than operator (>), 119-120 6/3/2009
1 p 461 greater than operator ( ), 119-120 greater than operator (>), 119-120 6/3/2009
1 p 462 non-equijoins, 208-209 nonequijoins, 208-209 6/3/2009
1 p 464 non-equality operator (!=), 119
non-equijoins, 208-209
nonequality operator (!=), 119
nonequijoins, 208-209
6/3/2009
1 p 468 single quotation marks (“), 353 single quotation marks (‘), 353 6/3/2009
1 p 473 non-equijoins, 208-209 nonequijoins, 208-209 6/3/2009
1 p 474 non-equijoins, 208-209 nonequijoins, 208-209 6/3/2009
1 p 484-486 Ads moved from p484-486 back one page from 483-485 to add new Safari-Free ad on p486. fixed 6/4/2009