The Review Questions 7.3 to 7.40, from the Kroenke book starting page 357, have been duplicated below, to assist in the completion of this Lab Assignment.

RQ Questions 7.1, 7.2 and 7.3 are answered below to assist you. These Answers are identified with BOLD/purple text. Attach this document to the assignment link to be graded.

What does DDL stand for? List the SQL DDL statements.

DDL stands for Data Definition Language. DDL statements include:

CREATE TABLE

ALTER TABLE

DROP TABLE

TRUNCATE TABLE

What does DML stand for? List the SQL DML statements.

DML stands for Data Manipulation Language. DML statements include:

INSERT

UPDATE

DELETE

MERGE

Explain the meaning of the following expression: IDENTITY (4000, 5).

The IDENTITY keyword is used to modify a column name, and is used to specify surrogate keys. The first number parameter after IDENTITY specifies the starting value for the surrogate key, and the second number specifies the increment value for each additional record. Thus a column named RelationID and modified by IDENTITY (4000, 5) will be a surrogate key named RelationID with an initial value of 4000 (for the first record in the relation), and with following values incremented by 5: 4000, 4005, 4010, etc.

When the assignment references Figures in the book, ex. Figure 7-35, you will need to look at the book for the example. Figure 7-35 is pictured on page 357.

For this set of Review Questions, we will create and use a database with a set of tables that will allow us to compare variations in SQL CREATE TABLE and SQL INSERT statements. The purpose of these questions is to illustrate different situations that call for specific uses of various SQL CREATE TABLE and SQL INSERT options.

The database will be named CH07_RQ_TABLES and will contain the following six tables:

CUSTOMER_01 (EmailAddress, LastName, FirstName)

CUSTOMER_02 (CustomerID, EmailAddress, LastName, FirstName)

CUSTOMER_03 (CustomerID, EmailAddress, LastName, FirstName)

CUSTOMER_04 (CustomerID, EmailAddress, LastName, FirstName)

SALE_01 (SaleID, DateOfSale, EmailAddress, SaleAmount)

SALE_02 (SaleID, DateOfSale, CustomerID, SaleAmount)

EmailAddress is a text column containing an email address, and is therefore not a surrogate key. CustomerID is a surrogate key that starts at 1 and increments by 1. SaleID is a surrogate key that starts at 20150001 and increases by 1.

The CH07_RQ_TABLES database has the following referential integrity constraints:

EmailAddress in SALE_01 must exist in EmailAddress in CUSTOMER_01

CustomerID in SALE_02 must exist in CustomerID in CUSTOMER_04

The relationship from SALE_01 to CUSTOMER_01 is N:1, O-M.

The relationship from SALE_02 to CUSTOMER_04 is N:1, O-M.

The column characteristics for these tables are shown in Figures 7-35 (CUSTOMER_01), 7-36 (CUSTOMER_02, CUSTOMER_03, and CUSTOMER_04), 7-37 (SALE_01), and 7-38 (SALE_02). The data for these tables are shown in Figures 7-39 (CUSTOMER_01), 7-40 (CUSTOMER_02), 7-41 (CUSTOMER_04), 7-42 (SALE_01), and 7-43 (SALE_02).

Figure 7-35 to 7-43 are pictured, starting on page 357.

If you are using Microsoft SQL Server, Oracle Database, or MySQL, create a folder in your Documents folder to save and store the *.sql scripts containing the SQL statements that you are asked to create in the following Review Questions about the CH07_RQ_TABLES database.

For SQL Server Management Studio, create a folder named CH07-RQ-TABLES-Database in the Projects folder in your SQL Server Management Studio folder.

For Oracle SQL Developer, create a folder named CH07-RQ-TABLES-Database in your SQL Developer folder.

For SQL Workbench, create a folder named CH07-RQ-TABLES-Database in the Schemas folder in your MySQL Workbench folder.

If you are using Microsoft Access 2013, create a folder named CH07-Databases in your DBPe14-Access-2013-Databases folder.

Create a database named CH07_RQ_TABLES.

This step is completed in assignment, S09_Assignment_ToCreateRQ_TABLES_DBinSSMS.

If you are using Microsoft SQL Server, Oracle Database, or MySQL, create and save an SQL script named CH07-RQ-TABLES-Tables-Data-and-Views.sql to hold the answers to Review Questions 7.77.40. Use SQL script commenting (/* and */ symbols) to enter your answers to Review Questions that require written answers as comments.

Each Review Question SQL script will need to be stored in a file named, CH_07_RQ_TABLES_Data_and_Views.sql, stored in the Projects folder in the SQL Server Management Studio folder.

You should have created the Projects folder when you created the Cape_Codd Database in Chapter 2, or in S09_Assignment_ToCreateRQ_TABLES_DBinSSMS.

Directions on how to document multiple SQL scripts in the same file, is provided in S09_HowToSaveMultipleSQL_scriptsIntoOneFile.docx, located under the Resources Link.

Write and run an SQL CREATE TABLE statement to create the CUSTOMER_01 table.

Write and run an SQL CREATE TABLE statement to create the CUSTOMER_02 table.

Are there any significant differences between the CUSTOMER_01 and CUSTOMER_02 tables? If so, what are they?

Write and run an SQL CREATE TABLE statement to create the CUSTOMER_03 table.

Are there any significant differences between the CUSTOMER_02 and CUSTOMER_03 tables? If so, what are they?

Write and run an SQL CREATE TABLE statement to create the CUSTOMER_04 table.

Are there any significant differences between the CUSTOMER_03 and CUSTOMER_04 tables? If so, what are they?

Write and run an SQL CREATE TABLE statement to create the SALE_01 table. Note that the foreign key is EmailAddress, which references CUSTOMER_01. EmailAddress. In this database, CUSTOMER_01 and SALE_01 records are never deleted, so that there will be no ON DELETE referential integrity action. However, you will need to decide how to implement the ON UPDATE referential integrity action.

In Review Question 7.14, how did you implement the ON UPDATE referential integrity action? Why?

Are there any significant differences between the CUSTOMER_01 and SALE_01 tables? If so, what are they?

Could we have created the SALE_01 table before creating the CUSTOMER_01 table? If not, why not?

Write and run an SQL CREATE TABLE statement to create the SALE_02 table. Note that the foreign key is CustomerID, which references CUSTOMER_04.CustomerID. In this database, CUSTOMER_04 and SALE_02 records are never deleted, so that there will be no ON DELETE referential integrity action. However, you will need to decide how to implement the ON UPDATE referential integrity action.

In Review Question 7.18, how did you implement the ON UPDATE referential integrity action? Why?

Are there any significant differences between the SALE_01 and SALE_02 tables? If so, what are they?

Could we have created the SALE_02 table before creating the CUSTOMER_04 table? If not, why not?

Write and run a set of SQL INSERT statements to populate the CUSTOMER_01 table.

Write and run a set of SQL INSERT statements to populate the CUSTOMER_02 table. Do not use a bulk INSERT command.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_01 and CUSTOMER_02 tables? If so, what are they?

Write and run an SQL INSERT statement to populate the CUSTOMER_03 table. Use a bulk INSERT command and the data in the CUSTOMER_01 table.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_02 and CUSTOMER_03 tables? If so, what are they?

Write and run a set of SQL INSERT statements to populate rows 1 through 3 in the CUSTOMER_04 table. Note that this question involves non-sequential surrogate key values and is based on techniques for Microsoft SQL Server 2014 in Chapter 10A, for Oracle Database in Chapter 10B, or for MySQL 5.6 in Chapter 10C, depending upon which DBMS product you are using.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_02 table and rows 1-3 of the CUSTOMER_04 table? If so, what are they?

Write and run a set of SQL INSERT statements to populate rows 4 through 6 in the CUSTOMER_04 table. Note that this question involves sequential surrogate key values and is based on techniques for Microsoft SQL Server 2014 in Chapter 10A, for Oracle Database in Chapter 10B, or for MySQL 5.6 in Chapter 10C, depending upon which DBMS product you are using.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_02 table and rows 4-6 of the CUSTOMER_04 table? If so, what are they?

Write and run a set of SQL INSERT statements to populate the SALE_01 table.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_01 table and the SALE_01 table? If so, what are they?

Could we have populated the SALE_01 table before populating the CUSTOMER_01 table? If not, why not?

Write and run a set of SQL INSERT statements to populate the SALE_02 table.

Are there any significant differences between the sets of SQL INSERT statements used to populate the SALE_01 table and the SALE_02 table? If so, what are they?

Could we have populated the SALE_02 table before populating the CUSTOMER_04 table? If not, why not?

Write and run an SQL INSERT statement to insert the following record into the SALE_02 table:

What was the result of running this statement? Why did this result occur?

Write an SQL statement to create a view named Customer01DataView based on the CUSTOMER_01 table. In the view, include the values of EmailAddress, LastName as CustomerLastName, and FirstName as CustomerFirstName. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

Write an SQL statement to create a view named Customer04DataView based on the CUSTOMER_04 table. In the view, include the values of Customer ID, LastName as CustomerLastName, FirstName as CustomerFirstName, and EmailAddress in that order. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

Write an SQL statement to create a view named CustomerSalesView based on the the CUSTOMER_04 and SALE_02 tables. In this view, include the values of Customer ID, LastName as CustomerLastName, FirstName as CustomerFirstName, EmailAddress, SaleID, DateOfSale, and SaleAmount in that order. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

The Review Questions 7.3 to 7.40, from the Kroenke book starting page 357, have been duplicated below, to assist in the completion of this Lab Assignment.

RQ Questions 7.1, 7.2 and 7.3 are answered below to assist you. These Answers are identified with BOLD/purple text. Attach this document to the assignment link to be graded.

What does DDL stand for? List the SQL DDL statements.

DDL stands for Data Definition Language. DDL statements include:

CREATE TABLE

ALTER TABLE

DROP TABLE

TRUNCATE TABLE

What does DML stand for? List the SQL DML statements.

DML stands for Data Manipulation Language. DML statements include:

INSERT

UPDATE

DELETE

MERGE

Explain the meaning of the following expression: IDENTITY (4000, 5).

The IDENTITY keyword is used to modify a column name, and is used to specify surrogate keys. The first number parameter after IDENTITY specifies the starting value for the surrogate key, and the second number specifies the increment value for each additional record. Thus a column named RelationID and modified by IDENTITY (4000, 5) will be a surrogate key named RelationID with an initial value of 4000 (for the first record in the relation), and with following values incremented by 5: 4000, 4005, 4010, etc.

When the assignment references Figures in the book, ex. Figure 7-35, you will need to look at the book for the example. Figure 7-35 is pictured on page 357.

For this set of Review Questions, we will create and use a database with a set of tables that will allow us to compare variations in SQL CREATE TABLE and SQL INSERT statements. The purpose of these questions is to illustrate different situations that call for specific uses of various SQL CREATE TABLE and SQL INSERT options.

The database will be named CH07_RQ_TABLES and will contain the following six tables:

CUSTOMER_01 (EmailAddress, LastName, FirstName)

CUSTOMER_02 (CustomerID, EmailAddress, LastName, FirstName)

CUSTOMER_03 (CustomerID, EmailAddress, LastName, FirstName)

CUSTOMER_04 (CustomerID, EmailAddress, LastName, FirstName)

SALE_01 (SaleID, DateOfSale, EmailAddress, SaleAmount)

SALE_02 (SaleID, DateOfSale, CustomerID, SaleAmount)

EmailAddress is a text column containing an email address, and is therefore not a surrogate key. CustomerID is a surrogate key that starts at 1 and increments by 1. SaleID is a surrogate key that starts at 20150001 and increases by 1.

The CH07_RQ_TABLES database has the following referential integrity constraints:

EmailAddress in SALE_01 must exist in EmailAddress in CUSTOMER_01

CustomerID in SALE_02 must exist in CustomerID in CUSTOMER_04

The relationship from SALE_01 to CUSTOMER_01 is N:1, O-M.

The relationship from SALE_02 to CUSTOMER_04 is N:1, O-M.

The column characteristics for these tables are shown in Figures 7-35 (CUSTOMER_01), 7-36 (CUSTOMER_02, CUSTOMER_03, and CUSTOMER_04), 7-37 (SALE_01), and 7-38 (SALE_02). The data for these tables are shown in Figures 7-39 (CUSTOMER_01), 7-40 (CUSTOMER_02), 7-41 (CUSTOMER_04), 7-42 (SALE_01), and 7-43 (SALE_02).

Figure 7-35 to 7-43 are pictured, starting on page 357.

If you are using Microsoft SQL Server, Oracle Database, or MySQL, create a folder in your Documents folder to save and store the *.sql scripts containing the SQL statements that you are asked to create in the following Review Questions about the CH07_RQ_TABLES database.

For SQL Server Management Studio, create a folder named CH07-RQ-TABLES-Database in the Projects folder in your SQL Server Management Studio folder.

For Oracle SQL Developer, create a folder named CH07-RQ-TABLES-Database in your SQL Developer folder.

For SQL Workbench, create a folder named CH07-RQ-TABLES-Database in the Schemas folder in your MySQL Workbench folder.

If you are using Microsoft Access 2013, create a folder named CH07-Databases in your DBPe14-Access-2013-Databases folder.

Create a database named CH07_RQ_TABLES.

This step is completed in assignment, S09_Assignment_ToCreateRQ_TABLES_DBinSSMS.

If you are using Microsoft SQL Server, Oracle Database, or MySQL, create and save an SQL script named CH07-RQ-TABLES-Tables-Data-and-Views.sql to hold the answers to Review Questions 7.77.40. Use SQL script commenting (/* and */ symbols) to enter your answers to Review Questions that require written answers as comments.

Each Review Question SQL script will need to be stored in a file named, CH_07_RQ_TABLES_Data_and_Views.sql, stored in the Projects folder in the SQL Server Management Studio folder.

You should have created the Projects folder when you created the Cape_Codd Database in Chapter 2, or in S09_Assignment_ToCreateRQ_TABLES_DBinSSMS.

Directions on how to document multiple SQL scripts in the same file, is provided in S09_HowToSaveMultipleSQL_scriptsIntoOneFile.docx, located under the Resources Link.

Write and run an SQL CREATE TABLE statement to create the CUSTOMER_01 table.

Write and run an SQL CREATE TABLE statement to create the CUSTOMER_02 table.

Are there any significant differences between the CUSTOMER_01 and CUSTOMER_02 tables? If so, what are they?

Write and run an SQL CREATE TABLE statement to create the CUSTOMER_03 table.

Are there any significant differences between the CUSTOMER_02 and CUSTOMER_03 tables? If so, what are they?

Write and run an SQL CREATE TABLE statement to create the CUSTOMER_04 table.

Are there any significant differences between the CUSTOMER_03 and CUSTOMER_04 tables? If so, what are they?

Write and run an SQL CREATE TABLE statement to create the SALE_01 table. Note that the foreign key is EmailAddress, which references CUSTOMER_01. EmailAddress. In this database, CUSTOMER_01 and SALE_01 records are never deleted, so that there will be no ON DELETE referential integrity action. However, you will need to decide how to implement the ON UPDATE referential integrity action.

In Review Question 7.14, how did you implement the ON UPDATE referential integrity action? Why?

Are there any significant differences between the CUSTOMER_01 and SALE_01 tables? If so, what are they?

Could we have created the SALE_01 table before creating the CUSTOMER_01 table? If not, why not?

Write and run an SQL CREATE TABLE statement to create the SALE_02 table. Note that the foreign key is CustomerID, which references CUSTOMER_04.CustomerID. In this database, CUSTOMER_04 and SALE_02 records are never deleted, so that there will be no ON DELETE referential integrity action. However, you will need to decide how to implement the ON UPDATE referential integrity action.

In Review Question 7.18, how did you implement the ON UPDATE referential integrity action? Why?

Are there any significant differences between the SALE_01 and SALE_02 tables? If so, what are they?

Could we have created the SALE_02 table before creating the CUSTOMER_04 table? If not, why not?

Write and run a set of SQL INSERT statements to populate the CUSTOMER_01 table.

Write and run a set of SQL INSERT statements to populate the CUSTOMER_02 table. Do not use a bulk INSERT command.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_01 and CUSTOMER_02 tables? If so, what are they?

Write and run an SQL INSERT statement to populate the CUSTOMER_03 table. Use a bulk INSERT command and the data in the CUSTOMER_01 table.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_02 and CUSTOMER_03 tables? If so, what are they?

Write and run a set of SQL INSERT statements to populate rows 1 through 3 in the CUSTOMER_04 table. Note that this question involves non-sequential surrogate key values and is based on techniques for Microsoft SQL Server 2014 in Chapter 10A, for Oracle Database in Chapter 10B, or for MySQL 5.6 in Chapter 10C, depending upon which DBMS product you are using.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_02 table and rows 1-3 of the CUSTOMER_04 table? If so, what are they?

Write and run a set of SQL INSERT statements to populate rows 4 through 6 in the CUSTOMER_04 table. Note that this question involves sequential surrogate key values and is based on techniques for Microsoft SQL Server 2014 in Chapter 10A, for Oracle Database in Chapter 10B, or for MySQL 5.6 in Chapter 10C, depending upon which DBMS product you are using.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_02 table and rows 4-6 of the CUSTOMER_04 table? If so, what are they?

Write and run a set of SQL INSERT statements to populate the SALE_01 table.

Are there any significant differences between the sets of SQL INSERT statements used to populate the CUSTOMER_01 table and the SALE_01 table? If so, what are they?

Could we have populated the SALE_01 table before populating the CUSTOMER_01 table? If not, why not?

Write and run a set of SQL INSERT statements to populate the SALE_02 table.

Are there any significant differences between the sets of SQL INSERT statements used to populate the SALE_01 table and the SALE_02 table? If so, what are they?

Could we have populated the SALE_02 table before populating the CUSTOMER_04 table? If not, why not?

Write and run an SQL INSERT statement to insert the following record into the SALE_02 table:

What was the result of running this statement? Why did this result occur?

Write an SQL statement to create a view named Customer01DataView based on the CUSTOMER_01 table. In the view, include the values of EmailAddress, LastName as CustomerLastName, and FirstName as CustomerFirstName. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

Write an SQL statement to create a view named Customer04DataView based on the CUSTOMER_04 table. In the view, include the values of Customer ID, LastName as CustomerLastName, FirstName as CustomerFirstName, and EmailAddress in that order. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

Write an SQL statement to create a view named CustomerSalesView based on the the CUSTOMER_04 and SALE_02 tables. In this view, include the values of Customer ID, LastName as CustomerLastName, FirstName as CustomerFirstName, EmailAddress, SaleID, DateOfSale, and SaleAmount in that order. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.