Published on March 8, 2014
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
Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...
In this presentation we will describe our experience developing with a highly dyna...
Presentation to the LITA Forum 7th November 2014 Albuquerque, NM
Un recorrido por los cambios que nos generará el wearabletech en el futuro
Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...
Chapter 3: Creating Access Tables. IN THIS CHAPTER. Creating a new table. Modifying the design of a table. Working with field properties. Specifying the ...
Search results for: table chapter 3 creating 500 Study Sets 500 Sets 500 Classes 500 Users; Most relevant Most recent
Chapter 3 * Creating the Oracle Database * Overview of Data Modeling and Database Design * Stages of System Development * Needs Assessment * Database Design *
3 Creating MPI and IHE Databases and Tables. This chapter provides procedures that lead you through creating Master Person Index and IHE ...
Chapter 3 Creating a Business Letter with a Letterhead and Table Microsoft Word 2013. ... Section Goals Understand Tables Know how to create, ...
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 ...
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
MODULE 5. Microsoft Word 2010. Chapter 1: Creating Documents. Chapter 2: Formatting Documents. Chapter 3: Working with Tables and Objects. Chapter 4 ...
Presentation on theme: "Chapter 4 Creating a Document with a Title Page, Lists, Tables, and a Watermark Microsoft Word 2013."— Presentation transcript: 1 ...
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 ...