SQL A STANDARD FOR DATABASE PROCESSING

1
SQLA STANDARD FOR DATABASE PROCESSING

• CHAPTER 8

2
SQLStructured Query Language

• A standard for relational systems

3
RDBMS Relational Database Management System

• A database management system that manages data as
  a collection of tables in which all data
  relationships are represented by common values in
  related tables.
• Table 1 Table2 Table3

4
Purpose of SQL

• 1. Specific syntax and semantics
• 2. Define data structures and operations
• 3.Portability between systems
• 4. Minimal (Level1) and complete (level2)
  standards to permit adoption
• 5. Incomplete standard that will be enhanced
  later

5
Benefits of SQL

• 1. reduce training costs
• 2. productivity
• 3. application portability
• 4. application longevity
• 5. reduce dependence on single vendor
• 6. cross-system communication

6
SQL Data Types

• DECIMAL (m, n)
• m total number of digits including sign
• n number of digits to the right
• 192.25

7
INTEGER

• Large up to 11 digits
• positive or negative whole numbers
• quantity on hand 1,245,500
• population 250,000,000

8
SMALLINT

• Small 5 or 6 digits depending on DBMS
• positive or negative whole numbers
• less storage is required
• age 21
• temperature 89
• flighnumber 1213

9
FLOAT (m,n)

• Whole and fractional numbers represented in
  scientific notation
• m is the total number of digits including sign
• n is the number of digits to the right of the
  decimal point

10
CHAR (n)

• Alphanumeric (character) data
• where n is the maximum length for this character
  string
• n character positions are allocated toeach
  instance of a CHAR column

11
DATE

• Calendar dates
• system variables set formats
• month/day/year
• year/month/day

12
LOGICAL

• True or false values

13
VARCHAR (n)

• Character data that vary significantly in length.
• Such data are stored in a variable-length format
  to reduce wasted space.
• Address
• product description

14
LONG VARCHAR

• Variable-length character data that can be longer
  than the VARCHAR data types allows
• comments

15
DATA DEFINITION COMMANDS

• CREATE TABLE
• DROP TABLE
• ALTER TABLE
• CREATE INDEX
• DROP INDEX
• CREATE VIEW
• DROP VIEW

16
CREATE TABLE

• Defines a new table and
• its columns

17
DROP TABLE

• Destroys a table
• (definition and contents as well as any views and
  indexes associated with it).

18
ALTER TABLE

• Adds one or more new columns to a table
• in some RDBMSs this would also permit deleting
  columns or redefining the columns data type.

19
CREATE INDEX

• Defines an index on one column
• (or a concatenation of columns)
• that enables rapid access to the rows of a table
  in a sequence of randomly by key value.
• A table may have many indexes

20
DROP INDEX

• Destroys and index.

21
CREATE VIEW

• Defines a logical table from one or more tables
  or views.
• Views may not be indexed.
• There are limitations on on updating data through
  a view, but some updating of data through a view
  is permitted.

22
DROP VIEW

• Destroys a view definition
• and any other views defined from the deleted view

23
NULL VALUE

• A special column value,
• distinct from 0,
• blank
• or any other value,
• that indicates that the value for the column is
  missing or otherwise unknown.

24
ADDITIONAL TABLE DEFINITIONS

• CREATE SYNONYM ALTERNATIVE NAME
• DROP SYNONYM DESTROYS SYNONYM
• DBDEFINE DEFINITIONS FOR TABLES
• LABEL COLUMN HEADING
• COMMENT REMARK ON COLUMNS

25

• 1. Index primary or secondary key
• 2. File organization for base tables
• 3. File organizations for indexes
• 4. Clustering data
• 5. Statistics of tables

26
ROW CLUSTERIN

• Cluster rows of different tables
• into adjacent physical storage
• to minimize access
• between related tables

27
DATA INTEGRITY CONTROLS

• Ensures that only valid data are entered
• and that data are consistent
• across all tables
• differs from
• Transaction Integrity Facilities

28
REFERENTIAL INTEGRITY

• An integrity constraint that specifies that the
  value (or existence) of an attribute in one
  relation depends on the value (or existence) of
  the same attribute in another relation.

29
DATA DICTIONARY FACILITIES

• SYSDBS
• SYSTABAUTH
• SYSCOLAU
• SYSCOLS
• SYSIDSX
• SYSKEYS
• SYSSYNS

30
DATA DICTIONARY FACILITIES

• SYSTABLS describes tables and views
• SYSTIMES ensure that latest version of
  internal tables
• SYSVDEPS describes relationship between views
  and tables
• SYSVIEWS define each view

31
DATA RETRIEVAL AND MANIPULATION IN SQL

• TUPLE CALCULUS - QEL
• DOMAIN CALCULUS - rare
• TRANSFORM LANGUAGE - SQL

32
RELATIONAL CALCULUS

• Combines SELECT and PROJECT
• and binary operators such as SUBTRACT
• into one SELECT statement
• that lists the column names
• and use WHERE clause to specify
• the selection criteria

33
CALCULUS

• WHERE clause to specify
• intertable associations used for
• implicitly joining JOIN
• relations in the SELECT command.

34
RETRIVAL CLAUSES

• SELECT
• FROM
• WHERE

35
SELECT

• Lists the columns
• from base tables or views
• to be projected into the table
• that will be the result of the command

36
FROM

• Identifies the tables or views
• from which columns will be chosen
• to appear in the result table
• and includes the tables or views
• needed to join tables
• to process the query

37
WHERE

• Includes the conditions for row selection within
  a single table or view,
• and the conditions between tables or views
  joining.

38
Example

• SELECT CHARGE
• FROM BILLED
• WHERE PATIENT_NO 1234
• SAVE TO TEMP TEMP_CHG (COST) KEEP
• new table
  TEMP_CHG
• labels column
  COST
• not deleted at end of SQL session KEEP

39
BUILT-IN FUNCTIONS

• COUNT
• MIN
• MAX
• SUM
• AVG
• SELECT COUNT ( )
• FROM ATTENDS
• WHERE PATIENT_NO 1234