Chapter 3 Creating Tables

55 %
45 %
Information about Chapter 3 Creating Tables
Technology

Published on March 8, 2014

Author: dyang10

Source: slideshare.net

A Guide to SQL, Eighth Edition Chapter Three Creating Tables

Objectives • Create and run SQL commands • Create tables • Identify and use data types to define columns in tables • Understand and use nulls • Add rows to tables A Guide to SQL, Eighth Edition 2

Objectives (continued) • View table data • Correct errors in a table • Save SQL commands to a file • Describe a table’s layout using SQL A Guide to SQL, Eighth Edition 3

Introduction • Structured Query Language (SQL) – Most popular and widely used language for retrieving and manipulating database data – Developed in mid 1970s under the name SEQUEL – Renamed SQL in 1980 – Used by most DBMSs A Guide to SQL, Eighth Edition 4

Creating and Running SQL Commands • Oracle Database 10g Express – Software used in text to illustrate SQL – Commands will work the same in other versions of Oracle • Differences between Oracle and Microsoft Access and SQL Server 2005 are noted in special boxes A Guide to SQL, Eighth Edition 5

Starting the Oracle Database Express Edition • Software loads in Internet Explorer – Other browsers may not fully support examples used in text • Must have a username and password • Click icons on Home page to access various tools A Guide to SQL, Eighth Edition 6

Starting the Oracle Database Express Edition (continued) A Guide to SQL, Eighth Edition 7

Entering Commands A Guide to SQL, Eighth Edition 8

Entering Commands (continued) A Guide to SQL, Eighth Edition 9

Creating a Table • Describe the layout of each table in the database • Use CREATE TABLE command • TABLE is followed by the table name • Follow this with the names and data types of the columns in the table • Data types define type and size of data A Guide to SQL, Eighth Edition 10

Creating a Table (continued) • Table and column name restrictions – Names cannot exceed 30 characters – Must start with a letter – Can contain letters, numbers, and underscores (_) – Cannot contain spaces A Guide to SQL, Eighth Edition 11

Creating a Table (continued) A Guide to SQL, Eighth Edition 12

Creating a Table (continued) • Commands are free-format; no rules stating specific words in specific positions • Indicate the end of a command by typing a semicolon • Commands are not case sensitive • In Oracle, enter the command in the SQL editor pane A Guide to SQL, Eighth Edition 13

Creating a Table (continued) A Guide to SQL, Eighth Edition 14

Creating a Table (continued) A Guide to SQL, Eighth Edition 15

Creating a Table (continued) A Guide to SQL, Eighth Edition 16

Correcting Errors in SQL Commands • Use the same techniques that you might use in a word processor • Make changes and click Run button to execute command again • Check Results pane to determine if command executed successfully A Guide to SQL, Eighth Edition 17

Dropping a Table • Can correct errors by dropping (deleting) a table and starting over • Useful when table is created before errors are discovered • Command is followed by the table to be dropped and a semicolon • Any data in table also deleted A Guide to SQL, Eighth Edition 18

Using Data Types • For each column, the type of data must be defined • Common data types – CHAR(n) – VARCHAR(n) – DATE – DECIMAL(p,q) – INT – SMALLINT A Guide to SQL, Eighth Edition 19

Using Nulls • A special value to represent a situation when the actual value is not known for a column • Can specify whether to allow nulls in the individual columns • Should not allow nulls for primary key columns A Guide to SQL, Eighth Edition 20

Using Nulls (continued) • Use NOT NULL clause in CREATE TABLE command to exclude the use of nulls in a column • Default is to allow null values • If a column is defined as NOT NULL, system will reject any attempt to store a null value there A Guide to SQL, Eighth Edition 21

Using Nulls (continued) CREATE TABLE REP (REP_NUM CHAR(2) PRIMARY KEY, LAST_NAME CHAR(15) NOT NULL, FIRST_NAME CHAR(15) NOT NULL, STREET CHAR(15), CITY CHAR(15), STATE CHAR(2), ZIP CHAR(5), COMMISSION DECIMAL(7,2), RATE DECIMAL(3,2) ); A Guide to SQL, Eighth Edition 22

Adding Rows to a Table • INSERT Command – INSERT INTO followed by table name – VALUES command followed by specific values in parentheses – Values for character columns in single quotation marks A Guide to SQL, Eighth Edition 23

The Insert Command A Guide to SQL, Eighth Edition 24

The INSERT Command (continued) • To add new rows, modify previous insert command • Use same editing techniques as those used to correct errors A Guide to SQL, Eighth Edition 25

Inserting a Row that Contains Nulls • Use a special format of INSERT command to enter a null value in a table • Identify the names of the columns that accept non-null values and then list only the non-null values after the VALUES command A Guide to SQL, Eighth Edition 26

Inserting a Row that Contains Nulls (continued) A Guide to SQL, Eighth Edition 27

Viewing Table Data • Use SELECT command – Can display all the rows and columns in a table • SELECT * FROM followed by the name of the table • Ends with a semicolon A Guide to SQL, Eighth Edition 28

Viewing Table Data (continued) A Guide to SQL, Eighth Edition 29

Viewing Table Data (continued) • In Access – Enter SELECT statement in SQL view • In SQL Server – Enter SELECT statement in Query Editor window A Guide to SQL, Eighth Edition 30

Correcting Errors in a Table • UPDATE command is used to update a value in a table • DELETE command allows you to delete a record • INSERT command allows you to add a record A Guide to SQL, Eighth Edition 31

Correcting Errors in a Table (continued) A Guide to SQL, Eighth Edition 32

Correcting Errors in a Table (continued) A Guide to SQL, Eighth Edition 33

Correcting Errors in a Table (continued) A Guide to SQL, Eighth Edition 34

Saving SQL Commands • Allows you to use commands again without retyping • Save commands in a script file or script – Text file with .sql extension • Script repository – Special location in Oracle – Can download to local drive A Guide to SQL, Eighth Edition 35

Saving SQL Commands (continued) • To create a script file in Oracle: – Use Script Editor page – Enter a name for script – Type the command or commands to save in script – Save the script A Guide to SQL, Eighth Edition 36

Saving SQL Commands (continued) • Once a script file is created: – – – – Can view, edit, or run Can delete Can download from script repository to local drive Can upload from local drive to script repository A Guide to SQL, Eighth Edition 37

Saving SQL Commands (continued) • Access – Does not use script files – Save SQL commands as query objects • SQL Server – Can create scripts – Can view, edit, run scripts – Can delete scripts A Guide to SQL, Eighth Edition 38

Creating the Remaining Database Tables • Execute appropriate CREATE TABLE and INSERT commands • Save these commands as scripts • Separate multiple commands in a script file with a semicolon • Figures 3-25 through 3-32 give additional table information for Premiere Products A Guide to SQL, Eighth Edition 39

Describing a Table • DESCRIBE command (Oracle) • Documenter tool (Access) • Exec sp_columns command (SQL Server) A Guide to SQL, Eighth Edition 40

Describing a Table (continued) A Guide to SQL, Eighth Edition 41

Summary • Use the CREATE TABLE command to create tables • Use the DROP TABLE command to delete a table • CHAR, VARCHAR, DATE, DECIMAL, INT, and SMALLINT data types – Access does not support DECIMAL – SQL Server uses DATETIME instead of DATE A Guide to SQL, Eighth Edition 42

Summary (continued) • Null value used when actual value for a column is unknown, unavailable, or not applicable • Use NOT Null clause to identify columns that cannot have a null value • Use INSERT command to add rows • Use SELECT command to view data in a table A Guide to SQL, Eighth Edition 43

Summary (continued) • Use UPDATE command to change the value in a column • Use DELETE command to delete a row • Save SQL commands in a script file • Use DESCRIBE command to display a table’s structure A Guide to SQL, Eighth Edition 44

Add a comment

Related presentations

Related pages

Chapter 3: Creating Access Tables - Access 2013 Bible [Book]

Chapter 3: Creating Access Tables. IN THIS CHAPTER. Creating a new table. Modifying the design of a table. Working with field properties. Specifying the ...
Read more

Search › table chapter 3 creating | Quizlet

Search results for: table chapter 3 creating 500 Study Sets 500 Sets 500 Classes 500 Users; Most relevant Most recent
Read more

Chapter 3: Creating the Oracle Database - cs.kent.edu

Chapter 3 * Creating the Oracle Database * Overview of Data Modeling and Database Design * Stages of System Development * Needs Assessment * Database Design *
Read more

3 Creating MPI and IHE Databases and Tables

3 Creating MPI and IHE Databases and Tables. This chapter provides procedures that lead you through creating Master Person Index and IHE ...
Read more

Chapter 3 Creating a Business Letter with a Letterhead and ...

Chapter 3 Creating a Business Letter with a Letterhead and Table Microsoft Word 2013. ... Section Goals Understand Tables Know how to create, ...
Read more

Chapter 3 - Table Create and Data Types - Teradata Load ...

Chapter 3 – Table Create and Data Types “Be not afraid of going slowly, Be afraid of standing still.” - Chinese Proverb. Creating a Table with a ...
Read more

Creating Tables of Contents, Indexes and Bibliographies

Introduction This chapter describes how to create and maintain a table of contents, an index, and a bibliography for a text document using OpenOffice.org
Read more

Chapter 3 - Weebly

MODULE 5. Microsoft Word 2010. Chapter 1: Creating Documents. Chapter 2: Formatting Documents. Chapter 3: Working with Tables and Objects. Chapter 4 ...
Read more

Chapter 4 Creating a Document with a Title Page, Lists ...

Presentation on theme: "Chapter 4 Creating a Document with a Title Page, Lists, Tables, and a Watermark Microsoft Word 2013."— Presentation transcript: 1 ...
Read more

Creating Tables - Oracle

Creating Tables. To create a new table in your schema, you must have the CREATE TABLE system privilege. To create a table in another user's schema, you ...
Read more