Transcript An Introduction to SQL

Chapter 2
An Introduction to SQL
A Guide to SQL, Sixth Edition
1
Objectives
Understand the concepts and
terminology associated with relational
databases
Create and run SQL commands
Create tables using SQL
2
Objectives
Identify and use data types to define
columns in SQL tables
Understand and use nulls
Add rows to tables
Describe a table’s layout using SQL
3
Introduction
Mid-1970s:

SQL developed under the name SEQUEL at IBM’s
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
4
Relational Databases
A relational database is essentially a
collection of tables
Tables are called relations
5
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
6
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
7
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-tomany relationship
8
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
9
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
10
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
11
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
12
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 table’s primary key by
underlining the column (or collection of
columns) that contains the primary key
13
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
14
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
15
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
16
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
17
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 SQL*Plus or
SQL*Plus Worksheet
18
Running SQL Commands
Oracle SQL*Plus is a program in which you
type SQL commands at a SQL> prompt
19
Running SQL Commands
Oracle SQL*Plus Worksheet is a GUI
(graphical user interface) program
20
Running SQL Commands
In Microsoft Access, queries can be created
in SQL view
21
Editing SQL Commands
In Oracle SQL*Plus Worksheet or Access
SQL view, SQL commands can be edited by
using the correction techniques used in word
processors
In Oracle SQL*Plus, 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
22
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
23
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
24
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
25
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
26
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
27
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
28
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
29
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
30
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
31
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
32
Describing a Table
Each DBMS provides a method to examine a
table’s structure
In either Oracle SQL*Plus or Oracle
SQL*Plus 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
33
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
34
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
35
SQL Project Two Completed
Good Luck
H. Zamanzadeh
36