SQL Overview and Commands

SQL Overview

• Structured Query Language (SQL) is often pronounced "Sequel."
• It is the standard for Relational Database Management Systems (RDBMS).
• RDBMS is a database management system that manages data as a collection of tables where relationships are represented by common values in related tables.

History of SQL

• 1970: E.F. Codd developed the relational database concept.
• 1974-79: System R with Sequel (later SQL) was created at IBM Research Lab.
• 1979: Oracle marketed the first relational DB with SQL.
• 1981: SQL/DS was the first available RDBMS system on DOS/VSE. Others followed, including INGRES (1981), IDM (1982), DG/SGL (1984), and Sybase (1986).
• 1986: ANSI SQL standard released. Major ANSI standard updates occurred in 1989, 1992, 1999, 2003, 2006, 2008, 2011, and 2016.
• SQL is supported by most major database vendors today, but is no longer certified by NIST.

Original Purpose of SQL Standard

• Specify syntax/semantics for data definition and manipulation.
• Define data structures and basic operations.
• Enable portability of database definition and application modules.
• Specify minimal (level 1) and complete (level 2) standards.
• Allow for later growth/enhancement to standard (referential integrity, transaction management, user-defined functions, extended join operations, national character sets).

Benefits of a Standardized Relational Language

• Reduced training costs
• Productivity
• Application portability
• Application longevity
• Reduced dependence on a single vendor
• Cross-system communication

SQL Environment

• Catalog: A set of schemas that constitute the description of a database.
• Schema: The structure that contains descriptions of objects created by a user, such as base tables, views, and constraints.
• Data Definition Language (DDL): Commands that define a database, including creating, altering, and dropping tables, and establishing constraints.
• Data Manipulation Language (DML): Commands that maintain and query a database.
• Data Control Language (DCL): Commands that control a database, including administering privileges and committing data.

SQL Data Types

• Strings: CHARACTER (n), VARYING CHARACTER (n)
• Binary: Binary Large Object (BLOB)
• Number: Numeric (precision, scale), Decimal (p, s), Integer
• Temporal: Timestamp, Timestamp with local time zone
• Boolean: True or False values

SQL Database Definition

• Data Definition Language (DDL)
• Major CREATE statements:
  • CREATE SCHEMA: Defines a portion of the database owned by a particular user.
  • CREATE TABLE: Defines a new table and its columns.
  • CREATE VIEW: Defines a logical table from one or more tables or views.
• Other CREATE statements: CHARACTER SET, COLLATION, TRANSLATION, ASSERTION, DOMAIN

Steps in Table Creation

1. Identify data types for attributes.
2. Identify columns that can and cannot be null.
3. Identify columns that must be unique (candidate keys).
4. Identify primary key–foreign key mates.
5. Determine default values.
6. Identify constraints on columns (domain specifications).
7. Create the table and associated indexes.

Defining Attributes and Their Data Types

Example:

CREATE TABLE Product_T
(
    ProductID               NUMBER(11,0)    NOT NULL,
    ProductDescription      VARCHAR2(50),
    ProductFinish           VARCHAR2(20)
        CHECK (ProductFinish IN ('Cherry', 'Natural Ash', 'White Ash',
                                  'Red Oak', 'Natural Oak', 'Walnut')),
    ProductStandard Price   DECIMAL(6,2),
    ProductLinelD           INTEGER,
    CONSTRAINT Product_PK PRIMARY KEY (ProductID)
);

Non-Nullable Specifications

• Some primary keys are composite, composed of multiple attributes.

Controlling the Values in Attributes

Example:

CREATE TABLE Order_T
(
    OrderID      NUMBER(11,0) NOT NULL,
    OrderDate    DATE DEFAULT SYSDATE,
    CustomerID   NUMBER(11,0),
    CONSTRAINT Order_PK PRIMARY KEY (OrderID),
    CONSTRAINT Order_FK FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID)
);

CREATE TABLE Product_T
(
    ProductID               NUMBER(11,0)    NOT NULL,
    ProductDescription      VARCHAR2(50),
    ProductFinish           VARCHAR2(20)
        CHECK (ProductFinish IN ('Cherry', 'Natural Ash', 'White Ash',
                                  'Red Oak', 'Natural Oak', 'Walnut')),
    ProductStandardPrice   DECIMAL(6,2),
    ProductLinelD           INTEGER,
    CONSTRAINT Product_PK PRIMARY KEY (ProductID)
);

Identifying Foreign Keys and Establishing Relationships

Example:

CREATE TABLE Customer_T
(
    CustomerID        NUMBER(11,0)    NOT NULL,
    CustomerName      VARCHAR2(25)    NOT NULL,
    CustomerAddress   VARCHAR2(30),
    CustomerCity      VARCHAR2(20),
    CustomerState     CHAR(2),
    CustomerPostalCode VARCHAR2(9),
    CONSTRAINT Customer_PK PRIMARY KEY (CustomerID)
);

CREATE TABLE Order_T
(
    OrderID      NUMBER(11,0) NOT NULL,
    OrderDate    DATE DEFAULT SYSDATE,
    CustomerID   NUMBER(11,0),
    CONSTRAINT Order_PK PRIMARY KEY (OrderID),
    CONSTRAINT Order_FK FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID)
);

Data Integrity Controls

• Referential integrity is a constraint that ensures that foreign key values of a table must match primary key values of a related table in relationships.
• Restricting:
  • Deletes of primary records
  • Updates of primary records
  • Inserts of dependent records

Changing Tables

• ALTER TABLE statement allows you to change column specifications.
• Table Actions:
  • ADD,
  • DROP COLUMN,
  • MODIFY.
• Example (adding a new column with a default value):
  • ALTER TABLE Product_T ADD ProductWeigh DECIMAL(8,2) Default 0;

Removing Tables

• DROP TABLE statement allows you to remove tables from your schema:
  • DROP TABLE Product_T;

INSERT Statement

• Adds one or more rows to a table
• Inserting into a table:
  • INSERT INTO Product_T VALUES (1,‘screw’, ‘steel’, 1.2,1)
• Inserting a record that has some null attributes requires identifying the fields that actually get data:
  • INSERT INTO Product_T (ProductID, ProductDescription, ProductPrice) VALUES (1,‘screw’, 1.2);
• Inserting from another table:
  • INSERT INTO newtable SELECT * FROM Product_T

Creating Tables with Identity Columns

• Inserting into a table does not require explicit customer ID entry or field list.

DELETE Statement

• Removes rows from a table
• Delete certain rows
  • DELETE FROM Product_T WHERE ProductID = 1;
• Delete all rows
  • DELETE FROM Product_T;

UPDATE Statement

• Modifies data in existing rows
  • UPDATE Product_T SET ProductPrice = 1.5 WHERE ProductID = 1;

MERGE Statement

• Makes it easier to update a table. It allows the combination of Insert and Update in one statement and is useful for updating master tables with new data.

Schema Definition

• Control processing/storage efficiency:
  • Choice of indexes
  • File organizations for base tables
  • File organizations for indexes
  • Data clustering
  • Statistics maintenance
• Creating indexes
  • Speed up random/sequential access to base table data
  • Example
    • CREATE INDEX CustNameInd ON Customer_T(CUSTOMERNAME);
    • This makes an index for the CUSTOMERNAME field of the Customer_T table.

SELECT Statement

• Used for queries on single or multiple tables
• Clauses of the SELECT statement:
  • SELECT: List the columns (and expressions) to be returned from the query
  • FROM: Indicate the table(s) or view(s) from which data will be obtained
  • WHERE: Indicate the conditions under which a row will be included in the result
  • GROUP BY: Indicate categorization of results
  • HAVING: Indicate the conditions under which a category (group) will be included
  • ORDER BY: Sorts the result according to specified criteria

SELECT Example

• Find products with a standard price less than $275
• Comparison operators include
  • = Equal to
  • > Greater than
  • >= Greater than or equal to
  • < Less than
  • <= Less than or equal to
  • <> Not equal to
  • != Not equal to

SELECT Example Using Alias

• Alias is an alternative column or table name

SELECT Example Using a Function

• Using the COUNT aggregate function to find totals
• Note: With aggregate functions, you can’t have single- valued columns included in the SELECT clause unless they are included in the GROUP BY clause.

SELECT Example – Boolean Operators

• AND, OR, and NOT Operators for customizing conditions in the WHERE clause

SELECT Example–Boolean Operators

• With parentheses… these override the normal precedence of Boolean operators.
• With parentheses, you can override normal precedence rules. In this case parentheses make the OR take place before the AND.

Sorting Results with ORDER BY Clause

• Sort the results first by STATE, and within a state by the CUSTOMER NAME
• Note: The IN operator in this example allows you to include rows whose CustomerState value is either FL, TX, CA, or HI. It is more efficient than separate OR conditions.

Categorizing Results Using GROUP BY Clause

• For use with aggregate functions
  • Scalar aggregate: single value returned from SQL query with aggregate function
  • Vector aggregate: multiple values returned from SQL query with aggregate function (via GROUP BY)
• You can use single-value fields with aggregate functions if they are included in the GROUP BY clause

Qualifying Results by Categories Using the HAVING Clause

• For use with GROUP BY
• Like a WHERE clause, but it operates on groups (categories), not on individual rows. Here, only those groups with total numbers greater than 1 will be included in the final result.

A Query with Both WHERE and HAVING

SELECT ProductFinish, AVG(ProductStandardPrice)
FROM Product_T
WHERE ProductFinish IN ('Cherry', 'Natural Ash', 'Natural Maple',
                          'White Ash')
GROUP BY ProductFinish
HAVING AVG(ProductStandardPrice) < 750
ORDER BY ProductFinish;