Chapter 2 An Introduction to SQL

1
Chapter 2An Introduction to SQL
2
Objectives

• Understand the concepts and terminology
  associated with relational databases
• Create and run SQL commands
• Create tables using SQL

3
Objectives

• Identify and use data types to define columns in
  SQL tables
• Understand and use nulls
• Add rows to tables
• Describe a tables layout using SQL

4
Introduction

• Mid-1970s
• SQL developed under the name SEQUEL at IBMs San
  Jose research facilities
• 1980
• Language renamed SQL to avoid confusion with an
  unrelated hardware product called SEQUEL
• Most relational DBMSs use a version of SQL

5
Relational Databases

• A relational database is essentially a collection
  of tables
• Tables are called relations

6
Entities, Attributes, and Relationships

• An entity is a person, place, object, event, or
  idea for which you want to store and process data
• The entities of interest to Premiere Products are
  customers, orders, parts, and sales reps

7
Entities, Attributes, and Relationships

• An attribute is a characteristic or property of
  an entity
• For Premiere Products, the attributes of interest
  for the entity customer are customer name,
  street, city, and so on
• An attribute is also called a field or column in
  many database systems

8
Entities, Attributes, and Relationships

• A relationship is the association between
  entities
• There is an association between customers and
  sales reps at Premiere Products
• A sales rep is associated with all of his or her
  customers, and a customer is associated with its
  sales rep
• This particular relationship is called a
  one-to-many relationship

9
Definitions

• A relation is a two-dimensional table in which
• The entries in the table are single-valued
• Each column has a distinct name
• All values in a column are values of the same
  attribute
• The order of columns is immaterial
• Each row is distinct
• The order of rows is immaterial

10
Definitions

• A relational database is a collection of
  relations
• Rows in a table (relation) often are called
  records or tuples
• Columns are often called fields or attributes

11
Common Shorthand Representation

• Write the table name and then, within
  parentheses, list all the columns (fields) in the
  table
• SQL is not case-sensitive
• Type commands using uppercase or lowercase
  letters
• Exception when inserting character values into a
  table, use the correct case

12
Qualifying Names

• To associate the correct table with the column
  name
• Write both the table name and the column name,
  separated by a period
• CUSTOMER.REP_NUMBER
• REP.REP_NUMBER
• This technique is known as qualifying the names

13
Indicating the Primary Key

• The primary key of a table (or relation) is the
  column or collection of columns that uniquely
  identifies a given row
• Indicate a tables primary key by underlining the
  column (or collection of columns) that contains
  the primary key

14
Database Creation

• The layout of each table that a database will
  contain must be described
• The CREATE TABLE command is used to describe the
  layout of a table
• The word TABLE is followed by the name of the
  table to be created and then by the names and
  data types of the columns

15
Database Creation

• The data type indicates
• The type of data that the column can contain
• For example, characters, numbers, or dates
• The maximum number of characters or digits that
  the column can store

16
Typical Column Naming Conventions

• The name cannot be longer than 18 characters
• In Oracle, names can be up to 30 characters in
  length
• The name must start with a letter
• The name can contain letters, numbers, and
  underscores ( _ )
• The name cannot contain spaces

17
SQL Commands

• SQL commands are free format
• No rule says that a particular word must begin in
  a particular position on a line
• Press the Enter key at the end of each line and
  then continue typing the command on the next line
• Indicate the end of a command line by typing a
  semicolon

18
Running SQL Commands

• The precise manner in which SQL commands are run
  depends on the program in which the work is being
  done
• If you are using Oracle 9i, you can complete your
  work in SQLPlus or SQLPlus Worksheet

19
Running SQL Commands

• Oracle SQLPlus is a program in which you type
  SQL commands at a SQLgt prompt

20
Running SQL Commands

• Oracle SQLPlus Worksheet is a GUI (graphical
  user interface) program

21
Running SQL Commands

• In Microsoft Access, queries can be created in
  SQL view

22
Editing SQL Commands

• In Oracle SQLPlus Worksheet or Access SQL view,
  SQL commands can be edited by using the
  correction techniques used in word processors
• In Oracle SQLPlus, the most recent command
  entered is stored in a special area called the
  command buffer
• The command in the buffer can be edited by using
  editing commands

23
Dropping a Table

• Use the DROP TABLE command to delete a table
• The command DROP TABLE is followed by the name of
  the table you want to delete and a semicolon
• DROP TABLE SALES_REP
• Note that when a table is dropped, any data that
  you entered into the table is dropped

24
Data Types

• For each column in a table, the type of data that
  the column will store must be specified
• The exact list of data types available in
  different implementations of SQL varies

25
Nulls

• In SQL, a special value can be used to represent
  situations in which an actual value is unknown,
  unavailable, or not applicable
• This special value is called a null data value,
  or simply a null

26
Loading a Table with Data

• Once tables are created in a database, data can
  be loaded into them by using the INSERT command
• The INSERT command adds rows to a table
• To use this command
• Type INSERT INTO followed by the name of the
  table into which data is being added
• Type the VALUES command followed by the specific
  values to be inserted in parentheses

27
The INSERT Command with Nulls

• To enter a null value into a table, a special
  format of the INSERT command must be used
• Identify the names of the columns that will
  accept non-null values, and then list only these
  non-null values after the VALUES command

28
Viewing Table Data

• To view the data in a table, the SELECT command
  can be used
• The SELECT command is complex and the subject of
  the next two chapters
• A simple version can be used to display all the
  rows and columns in a table

29
Viewing Table Data

• The simpler version consists of the word SELECT,
  followed by an asterisk, followed by the word
  FROM and then the name of the table
• The command ends with a semicolon

30
Correcting Errors in the Database

• After reviewing the data in the table changes may
  have to be made to the values in a column
• The UPDATE command can be used to update a value
  in a table
• The DELETE command is used to delete a record

31
Saving SQL Commands

• SQL commands can be saved in a file so that the
  commands can be used in the future without having
  to retype them
• The exact manner in which you create and use
  saved files depends on the SQL implementation
  that you are using

32
Creating the Remaining Database Tables

• To create the remaining tables in the Premiere
  Products database, the appropriate CREATE TABLE
  and INSERT commands need to be executed

33
Describing a Table

• Each DBMS provides a method to examine a tables
  structure
• In either Oracle SQLPlus or Oracle SQLPlus
  Worksheet, the DESCRIBE command can be used to
  list all the columns in a table and their
  corresponding data types
• In Access, the Documenter tool is used to produce
  documentation about tables and other objects
  stored in a database

34
Summary

• A relational database is a collection of related
  tables
• An entity is a person, place, thing, or event
• Tables in the database are entities
• An attribute is a property of an entity
• Attributes are columns in the tables
• A relationship is the association between tables
  in the database

35
Summary

• The CREATE TABLE command is used to create a
  table
• The INSERT command is used to load a table
• The SELECT command is used to view the data in a
  table
• The UPDATE command is used to change the value in
  a column
• The DELETE command is used to delete a row in a
  table