Programming Languages Chapter 3 Guide Sql Ninth Edition Page Creating Tables Glance Instructors Manual Table

Unlock access to all the studying documents.

View Full Document

A Guide to SQL, Ninth Edition Page 3–1

Chapter 3

Creating Tables

At a Glance

Instructor’s Manual Table of Contents

• Overview

• Objectives

• Teaching Tips

• Quick Quizzes

• Class Discussion Topics

• Additional Projects

• Additional Resources

• Key Terms

A Guide to SQL, Ninth Edition Page 3–2

Lecture Notes

Overview

In this chapter, students begin the study of SQL. Students learn how to create a database by

describing and defining the tables and columns that make up the database. Students also learn

how to assign data types and how to use null values. Finally, they learn how to describe a

table’s layout using SQL.

Chapter Objectives

In this chapter, students learn about:

• Creating and running SQL commands

• Creating tables

• Identifying and using data types to define columns in tables

• Understanding and using nulls

• Adding rows to tables

• Viewing table data

• Correcting errors in a database

• Saving SQL commands to a file

• Describing a table’s layout using SQL

Teaching Tips

Introduction

2. Developed in the mid-1970s at IBM.

4. Most DBMSs use a version of SQL as their data manipulation language.

A Guide to SQL, Ninth Edition Page 3–3

Teaching

Tip

Although SQL is a standard language, different DBMSs have their own SQL

dialect, which may include extensions to the standard.

SQL is both an ANSI (American National Standards Institute) and ISO

(International Organization for Standardization) standard. See the Additional

Resources section for more information on SQL standards.

SQL is the standard database language for Internet-based computing.

Additionally, SQL is at the foundation of many database applications in human

resources management, supply chain management, customer relationship

management, and enterprise resource planning.

Creating and Running SQL Commands

1. Text uses Oracle Database 11g Express Edition to illustrate SQL commands.

3. SQL commands work the same in other versions of Oracle.

4. Can download from Oracle Web site.

Using the Oracle Database Express Edition

1. Run the app after installing.

3. Use Figure 3-1 to explain the Oracle Database 11g Express Edition home page.

5. Use Figure 3-2 to explain entering DBA username and password.

7. Create a new workspace. Requires a username and password.

9. Use Figure 3-5 to point out the icons on the Oracle Application Express home page.

11. Use Figures 3-7 and 3-8 to explain the SQL Scripts page.

12. Define breadcrumb. Breadcrumbs are the sequence of page references at the top of the

SQL editor pane.

Entering Commands

1. Use Figures 3-9 and 3-10 to illustrate entering commands in Oracle.

versions of Oracle that might be more suitable for your school. For more

A Guide to SQL, Ninth Edition Page 3–4

information on Oracle Database 11g Express, access the following website:

http://www.oracle.com/technetwork/database/database-technologies/express-

edition/overview/index.html

If your school has a different version of Oracle, the SQL commands in this book

will be identical.

In this text, each author downloaded their own version of Oracle 11g Express

and logged in as system users. You may not be able to do this in your

environment. Check with your IT staff to find out what restrictions are in place at

your institution.

If your school requires you to set up accounts for students, do it before starting

this chapter. You want students to have their own copies of the databases. Make

sure your students have user IDs and passwords for accessing Oracle. You can

do database administration with Oracle 11g Express, and the Help explains how

to create users.

You may want to prepare a handout that lists special procedures for accessing

and using Oracle. Be sure to include instructions for resetting passwords and

accessing Oracle from external locations. You also need to determine how

students will submit assignments.

Quick Quiz 1

1. _____ is one of the most popular and widely used languages for retrieving and

manipulating database data.

2. The version of Oracle used in this text is _____.

Creating a Table

1. Use the SQL command CREATE TABLE to describe the layout of a table. Following

2. Define data type. The data type indicates the type of data that the column can contain

5. Mention that commands are free-format in SQL; that is, no rule says that a particular

6. Use the Note on page 66 to point out that SQL is not case sensitive. The only exception

is when character values are inserted into a table.

8. Discuss the Access User Note on page 67. Use Figure 3-13 to explain using Access

SQL view to create a table.

9. Discuss the SQL Server User Note on page 68. Use Figure 3-14 to illustrate using SQL

Server to create a table.

Correcting Errors in SQL Commands

2. Point out that you correct errors using the same techniques you might use in a word

processor.

Dropping a Table

1. To delete a table, use the DROP TABLE command. The word TABLE is followed by

the name of the table you want to delete and a semicolon.

3. Review the embedded Question and Answer on page 69.

Teaching

Tip

Microsoft Access is part of Microsoft Office Professional. For more information

on Microsoft Office, see: http://products.office.com/en-us/home

SQL Server also has an Express edition that can be freely downloaded. For more

information on Microsoft SQL Server, see:

http://www.microsoft.com/en-us/server-cloud/products/sql-server/.

Quick Quiz 2

1. The SQL command used to describe the layout of a table is _____.

2. The _____ indicates the type of data that the column can contain as well as the

maximum number of characters or digits that the column can store.

3. Table and column names must start with a(n) _____.

Using Data Types

1. For each column in a table, you must specify the data type; that is, the type of data that

Using Nulls

1. When the values for one or more columns for a row being added to a table are unknown

2. When creating a table, you can specify when to allow null in individual columns.

4. Use the NOT NULL clause in a CREATE TABLE command to specify columns that

Adding Rows to a Table

1. After tables have been created in a database, use the INSERT command to load data.

The INSERT Command

1. Use the INSERT command to add rows to a table. To use this command, type INSERT

3. Use Example 2 and Figure 3-17 to illustrate the INSERT command.

5. Use Example 3 and Figures 3-18 and 3-19 to illustrate editing an INSERT command to

add additional rows to the REP table.

Inserting a Row that Contains Nulls

1. To enter a null value into a table, use a special format of the INSERT command. In this

2. Use Example 4 and Figure 3-20 to illustrate inserting a row containing null values.

A Guide to SQL, Ninth Edition Page 3–7

Quick Quiz 3

1. The SQL command used to add rows to a table is _____.

2. The _____ clause in a CREATE TABLE command specifies columns that cannot

contain null values.

3. _____ is used to represent a situation in which an actual value is unknown, unavailable,

or not applicable.

Viewing Table Data

1. To view the data in a table, use the SELECT command. The most basic form of the

2. Use Example 5 and Figure 3-21 to illustrate using the SELECT command.

4. Use Figures 3-24 and 3-25 to explain viewing table data in SQL Server

Correcting Errors in a Table

2. Use Figures 3-26 and 3-27 to illustrate using an UPDATE command.

4. Use Figures 3-28 and 3-29 to illustrate the DELETE command.

5. Review the Question and Answer on page 78 to be sure students understand how to

correct errors in the data.

Saving SQL Commands

2. Point out that script files have a .sql extension. You can create script files in Oracle or

in any word processing program.

4. Mention the Note on page 78.

5. Review the embedded Q&A on page 78.

6. Review the steps to create a script.

8. Mention the steps to delete, download, and upload scripts.

10. Mention the techniques for creating and using scripts in SQL Server.

Creating the Remaining Database Tables

2. Use Figure 3-30 to discuss the CREATE TABLE command for the CUSTOMER table.

4. Use Figures 3-32 through 3-37 to discuss the creation of the remaining tables in the

TAL Distributors database.

Teaching

Tip

The student data files include the Oracle, Access, and SQL Server script files

necessary to create the TAL Distributors database tables, the Colonial Adventure

Tours database tables, and the Solmaris Condominium Group database tables.

There are also scripts to drop tables in case students need to re-create tables.

Describing a Table

1. In Oracle, use the DESCRIBE command to list all of the columns in a table and their

corresponding data types.

3. Point out that, in Access, you use the Documenter tool to describe tables.

4. Mention that, in SQL Server, you use a stored procedure, exec sp_columns, to describe

columns in tables.

Teaching

Tip

Before assigning the exercises at the end of this chapter, decide what procedures

students will follow to submit assignments in this chapter and the remaining

chapters. You can print the page containing the SQL commands and results

directly from the Internet browser. You can copy and paste the commands from

the Editor and the Results pane to a Word document. Students can either print

the Word document or turn it in electronically. You may be able to send the

results to a PDF file. You can export the results to an Excel workbook. If you are

using Windows 7 or higher, you can use the Snipping Tool to copy and paste to a

Word document. Find the procedures that work best in your environment and

communicate these procedures to your students.

A Guide to SQL, Ninth Edition Page 3–9

Class Discussion Topics

1. You can use either the CHAR data type or the VARCHAR data type to store character

data. What are the differences between the two? Are there any disadvantages to using

VARCHAR?

2. Ask students why the data type for POSTAL_CODE in the REP and CUSTOMER

tables is CHAR and not SMALLINT or INTEGER? Is the length of the field large

enough?

Additional Projects

1. Place students in teams and assign each team the task of developing the CREATE

2. Have students research the Web to discover how other DBMSs handle dates. Examples

3. Have students create a script in Notepad or WordPad and upload the script to Oracle.

Additional Resources

1. Concepts of Database Management, Eighth Edition by Philip Pratt and Mary Last

3. International Organization for Standardization (ISO): www.iso.org/iso/home.htm

5. PostgreSQL: www.postgresql.org/

7. Oracle: www.oracle.com

Key Terms

➢ breadcrumb: Reference that appears at the top of the SQL editor pane to indicate the

pages in Oracle Database Express

➢ CREATE TABLE: The SQL command used to describe the layout of the table

➢ data type: Type of data that a column can contain as well as the maximum number of

characters or digits that the column can store

A Guide to SQL, Ninth Edition Page 3–10

➢ DELETE: The SQL command used to delete a row in a table

➢ DESCRIBE: The SQL command used to list all of the columns in a table and their

corresponding data types

➢ null: A data value used to represent situations in which an actual value is unknown,

unavailable or not applicable

➢ null data value: A data value used to represent situations in which an actual value is

unknown, unavailable, or not applicable

➢ script: A file that contains one or more SQL commands

➢ script file: A file that contains one or more SQL commands

➢ UPDATE: The SQL command used to change a value in a table

➢ workspace: a work area that allows multiple users to work with the same installation of

Oracle while keeping their objects and data private