Chapter 7 - Structured Query Language (SQL)

Note I

Primary Chapter Objectives

To learn

The basic commands and functions of SQL

How SQL is used for data administration (to create tables, indexes, and views)

How SQL is used for data manipulation (to add, modify, delete, and retrieve data: INSERT, UPDATE, and DELETE, SELECT)

How to use SQL to query a database to extract useful information

About more advanced SQL features such as updatable views, stored procedures, and triggers

  1. Introduction

SQL is considered to be a 4GL. A Fourth Generation Language (4GL) is a nonprocedural language. That is, its user merely commands what is to be done without having to worry about how it's to be done. Contrast this approach to that taken by such 3GL languages as COBOL, BASIC, or Pascal. Given this characteristic, the 4GL languages are much easier to use than their 3GL predecessors.

Two sub types of SQL

-Data definition(DDL) creates the database and its table structures (code that works with metadata)

-Data manipulation(DML) selects, deletes and updates data within the database tables (code that works with actual data)

-.

Three basic data functions are provided by SQL:

Data definition through the use of CREATE - DDL

Data manipulationusing INSERT, UPDATE, and DELETE - DML

Data querying through the use of SELECT - DML ... which is the basis for all SQL queries.

  1. Data definition commands

DDL allows specification of not only a set of relations(tables) but also information about each relation, including

-CREATE SCHEMA - The schema for each database

-CREATE TABLE – creates each table within a database

-Integrity constraints.

-The set of indices to be maintained for each relation.

-Security and authorization information for each relation

-The physical storage structure of each relation on disk.

Schemas – a group of related database objects such as tables and indexes owned by a specific user (usually the creator)

Syntax:

CREATE SCHEMA CUSTOMER_IS

Tables are then stored as schema_name.table_name

With Oracle: if a schema name is not supplied, the default schema name is the username of the creator.

Example: CSC30501.EMP_TBL

Creating table structures

Syntax:

CREATE TABLE <table name> (

attribute_1_name and characteristics,

attribute_2_name and characteristics,

attribute_3_name and characteristics,

primary key designation,

foreign key designation and foreign key requirements) ;

Data Types supported:

Character:

char(n) - fixed length character string with user specified length n (n <=255)

Oracle = 1 – 2,000 characters

varchar(n) - variable length character strings, with user-specified maximum length n.

Oracle = VARCHAR2 – up to 4,000 characters

Numeric:

NUMBER(l,d) - fixed point number, with user-specified precision of digits,

with d digits to the right of decimal point. If no digits specified, Oracle will assume NO decimal points in number

DECIMAL(l,d)

INTEGER – Up to 11 digits.

SMALLINT – integer values up to six digits.

Date:

DATE – stores dates in the Julian format. The year value is four digits long (0001- 9999),

month two digits (01-12). Day two digits 01 – 31

Oracle date: DD-MON-YY example: 09-Oct-03

Example 1: VENDOR, PRODUCT

CREATE TABLE VENDOR
(V_CODE INTEGER NOT NULLUNIQUE,
V_NAME VARCHAR2(35) NOT NULL,
V_CONTACT VARCHAR2(15) NOT NULL,
V_AREACODE CHAR(3) NOT NULL,
V_PHONE CHAR(7) NOT NULL,
V_STATE CHAR(2) NOT NULL,
V_ORDER CHAR(5) NOT NULL,
PRIMARY KEY (V_CODE));

CREATE TABLE PRODUCT(
P_CODEVARCHAR(10)NOT NULLUNIQUE,
P_DESCRIPTVARCHAR(35)NOT NULL,
P_INDATEDATENOT NULL,
P_ONHANDSMALLINTNOT NULL,
P_MINSMALLINTNOT NULL,
P_PRICEDECIMAL(8,2)NOT NULL,
P_DISCOUNTDECIMAL(4,1)NOT NULL,
V_CODEINTEGER,
PRIMARY KEY (P_CODE),
FOREIGN KEY (V_CODE) REFERENCES VENDOR
ON DELETE RESTRICT
ON UPDATE CASCADE);

The list of attributes is enclosed in parentheses. A comma is used after each attribute and its characteristics have been defined.

If the primary key is a composite key, all of its components are contained within the parentheses and are separated with commas. The order of the primary components is important since the indexing starts with the first-mentioned attribute, then proceeds with the next attribute, and so on.

Example 2:

create table BRANCH(

BRANCH_NAME char(15) not null UNIQUE,

BRANCH_CITYchar(30),

ASSETSINTEGER,

Primary key (BRANCH_NAME),

Check (assets >=0 ));

  • Delete a table from the database: DROP <table name>;

DROP TABLE BRANCH;

  • SQL Integrity Constraints
  • Adherence to entity integrity and referential integrity rules is crucial
  • Entity integrity is enforced automatically if the primary key specified in the CREATE TABLE command sequence exists
  • Referential integrity can be enforced with the specification of FOREIGN KEY
  • Other specifications to ensure conditions met:
  • ON DELETE RESTRICT
  • Example: cannot delete a vendor record if a product references that vendor
  • ON UPDATE CASCADE
  • Example: if a change is made to the vendor code of a specific record, a change will automatically occur to that vendor code on the product table

(In Oracle, it’s not necessary to include the ON DELETE RESTRICT and ON UPDATE CASCADE constraints in the table definition because the DBMS automatically enforces those constraints when a foreign key is defined

Also, in Oracle. it is not necessary to include NOT NULL or UNIQUE on the primary key)

 Using Domains

Domain is set of permissible values for a column

Definition requires:

Name

Data type

Default value

Domain constraint or condition

CREATE DOMAIN <domain_name> AS DATA_TYPE
[DEFAULT <default_value>]

[CHECK (<condition>)]

Example:

CREATE DOMAIN MARITAL_STATUS AS VARCHAR(8)

CHECK (VALUE IN (‘Single’, ‘Married’, ‘Divorced’, ‘Widowed’));

We can use the domain name instead of the attribute’s data type in the CREATE TABLE command.

CREATE TABLE EMPLOYEE (

EMP_NUM INTEGER ,

EMP_LNAME VARCHAR(15)NOT NULL,

EMP_FNAME VARCHAR(15)NOT NULL,

EMP_STATUSMARITAL_STATUSNOT NULL);

To delete a domain definition, use

DROP DOMAIN <domain_name> [RESTRICT | CASCADE]

Example: DROP DOMAIN MARITAL_STATUS CASCADE;

DROP TABLE vendor;

  • Save the tables: COMMIT <table name>;

Eg. COMMIT PRODUCT;

  • Any changes made to the table contents are not physically saved on disk until you use the COMMIT command, close the table, or log out SQL.
  • Do not X out of the software, any changes you made will NOT be committed to the database.
  • It’s used to save additions, changes, and deletions made in the table contents.

The ultimate purpose of the COMMIT and ROLLBACK commands is to ensure database update integrity in transaction management