SQL Chapter 2
An Introduction to SQL

Objectives

Understand the concepts and terminology associated with relational databases

Create and run SQL commands

Create tables using SQL

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

Introduction

Mid-1970s:

l SQL developed under the name SEQUEL at IBM’s San Jose research facilities

1980:

l Language renamed SQL to avoid confusion with an unrelated hardware product called SEQUEL

Most relational DBMSs use a version of SQL

Relational Databases

A relational database is essentially a collection of tables

Tables are called relations

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

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

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

Definitions

A relation is a two-dimensional table in which:

l The entries in the table are single-valued

l Each column has a distinct name

l All values in a column are values of the same attribute

l The order of columns is immaterial

l Each row is distinct

l The order of rows is immaterial

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

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

l Exception: when inserting character values into a table, use the correct case

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

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

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

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

Typical Column Naming Conventions

The name cannot be longer than 18 characters

l 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

SQL Commands

SQL commands are free format

l 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

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

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

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

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

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

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:

l Type INSERT INTO followed by the name of the table into which data is being added

l Type the VALUES command followed by the specific values to be inserted in parentheses

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

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

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

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

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

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

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

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

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

SQL Project Two Completed

Good Luck

H. Zamanzadeh