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 1SQL/FrameworkSpecifies the general requirements for conformance and defines the fundamental concepts of SQL. Part 2SQL/FoundationDefines the syntax and operations of SQL. . Part 3SQL/Call-Level InterfaceDefines the interface for application programming to SQL. Part 4SQL/Persistent Stored ModulesDefines the control structures that then define SQL routines. Part 4 also defines the modules that contain SQL routines. Part 9SQL/Host Language BindingsDefines extensions to SQL to support the management of external data through the use of data-wrappers and datalink types. Part 10Object Language BindingsDefines extensions to the SQL language to support the embedding of SQL statements into programs written in Java. Part 11Information and Definition SchemasDefines specifications for the Information Schema and Definition Schema, which provide structural and security information related to SQL data. Part 13Routines and Types Using the Java Programming LanguageDefines the capability to call Java static routines and classes as SQL-invoked routines. Part 14XML-Related SpecificationsDefines 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 1SQL/FrameworkSpecifies the general requirements for conformance and defines the fundamental concepts of SQL. Part 2SQL/FoundationDefines the syntax and operations of SQL. . Part 3SQL/Call-Level InterfaceDefines the interface for application programming to SQL. Part 4SQL/Persistent Stored ModulesDefines the control structures that then define SQL routines. Part 4 also defines the modules that contain SQL routines. Part 9SQL/Host Language BindingsDefines extensions to SQL to support the management of external data through the use of data-wrappers and datalink types. Part 10Object Language BindingsDefines extensions to the SQL language to support the embedding of SQL statements into programs written in Java. Part 11Information and Definition SchemasDefines specifications for the Information Schema and Definition Schema, which provide structural and security information related to SQL data. Part 13Routines and Types Using the Java Programming LanguageDefines the capability to call Java static routines and classes as SQL-invoked routines. Part 14XML-Related SpecificationsDefines 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 organizations website, logs in (if required by the organization), and uses an application built into the organizations 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 organizations website, logs in (if required by the organization), and uses an application built into the organizations 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 WITCHS 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 fields 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 fields 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 hours 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 hours 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 employees 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 employees 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 users 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. |