SQL DATA MANIPULATION LANGUAGE

Click here for part A of audio-text lecture and feed it to the speech agent

Click here for part B of audio-text lecture and feed it to the speech agent

Click here for an audio lecture that can be played using RealPlayer

  • DML statements

    SELECT
    UPDATE
    DELETE
    INSERT

  • Simple Queries

    SELECT S#, STATUS
    FROM S
    WHERE CITY = 'Paris' ;

  • Qualified Names
    SELECT S.S#, S.STATUS
    FROM S
    WHERE S.CITY = 'Paris' ;

    SIMPLE QUERIES


  • Simple retrieval (projection)
    SELECT P#
    FROM SP ;
  • Eliminate duplicates
    SELECT DISTINCT P#
    FROM SP ;
  • Computed values
    SELECT P.P#, 'Weight in grams =', P.WEIGHT * 234
    FROM P ;
  • Select all columns
    SELECT *
    FROM S ;
    SELECT S.*
    FROM S ;
    SELECT S.S#, S.SNAME, S.STATUS, S.CITY
    FROM S ;

    QUALIFIED RETRIEVAL


    SELECT S#
    FROM S
    WHERE CITY = 'Paris'
    AND STATUS > 20 ;
  • The Condition is a logical expression
         involving AND, OR, NOT, =, <>, >, >=, <, <=
    


  • Retrieval with ordering

    SELECT S#, STATUS
    FROM S
    WHERE CITY = 'Paris'
    ORDER BY STATUS DESC ;
  • Select all columns with condition. (Restriction)
    SELECT *
    FROM S
    WHERE CITY = 'Paris' ;

    JOIN QUERIES


  • Join operation is the most powerful feature of the relational system.
  • Use "join" to combine tables.
    SIMPLE EQUIJOIN
    SELECT S.*, P.*
    FROM S, P
    WHERE S.CITY = P.CITY ;

    S TABLE
    S# SNAME STAT CITY


    P TABLE
    P# PNAME COLOR WGT CITY


    RESULT TABLE
    S# SNAME STAT S.CITY P# PNAME COLOR WGT P.CITY

  • S.CITY=P.CITY is called the "join condition"
  • If one of the two IDENTICAL columns is eliminated, it is a "natural join".
    SELECT S#,SNAME,STAT,S.CITY
    P#,PNAME,COLOR,WGT
    FROM S, P
    WHERE S.CITY = P.CITY ;

    RESULT TABLE
    S# SNAME STAT S.CITY P# PNAME COLOR WGT

    GENERALIZED JOINS


  • Greater-than Join
    SELECT S.*, P.*
    FROM S, P
    WHERE S.CITY > P.CITY ;

  • Additional conditions
    SELECT S.*, P.*
    FROM S, P
    WHERE S.CITY = P.CITY
    AND S.STATUS <> 20 ;

  • Selected fields
    SELECT S.S#, P.P#
    FROM S, P
    WHERE S.CITY = P.CITY ;

  • Multiple tables
    SELECT DISTINCT S.CITY, P.CITY
    FROM S, SP, P
    WHERE S.S# = SP.S#
    AND SP.P# = P.P# ;

  • Joining a table with itself
    SELECT FIRST.S#, SECOND.S#
    FROM S FIRST, S SECOND
    WHERE FIRST.CITY = SECOND.CITY
    AND FIRST.S# < SECOND.S# ;

    (get all supplier pairs in same city)
  • Notice the use of "range variables"

    AGGREGATE FUNCTIONS


    COUNT - number of values in the column
    SUM - sum of the values in the column
    AVG - average of the values in the column
    MIN - minimum of the values in the column
    MAX - maximum of the values in the column


  • Get the total number of suppliers.

    SELECT COUNT(*)
    FROM S ;

  • Get the total quantity of part P2 supplied.
    SELECT SUM(QTY)
    FROM SP
    WHERE P# = 'P2' ;

    GROUP BY


  • Get the part # and total shipment quantity for each part.
    SELECT P#, SUM(QTY)
    FROM SP
    GROUP BY P# ;

    RESULT:
    P1 600
    P2 1000
    P3 400
    P4 500
    P5 500
    P6 100

  • Get part numbers for all parts supplied by more than one supplier
    SELECT P#
    FROM SP
    GROUP BY P#
    HAVING COUNT(*) > 1 ;


  • HAVING is to groups what WHERE is to rows.


  • HAVING is used to eliminate groups, just as
    WHERE is used to eliminate rows.

    SUBQUERIES



  • A subquery is an expression of the form
    SELECT-FROM-WHERE-GROUP BY-HAVING
    which is nested inside another such expression.


  • We usually use subquery to represent a set of possible values to be searched in an "IN" condition.


  • Get supplier names for suppliers who supply part P2.


    SELECT SNAME
    FROM S
    WHERE S# IN
    ( SELECT S#
    FROM SP
    WHERE P# = 'P2' ) ;


    SELECT SNAME
    FROM S
    WHERE S# IN {S1, S2, S3, S4} ;

    QUERY EXAMPLE


    Get supplier numbers for suppliers supplying at least one part supplied by at least one supplier who supplies at least one read part.


    SQL Query:


    SELECT DISTINCT S#
    FROM SPJ
    WHERE P# IN
    ( SELECT P#
    FROM SPJ
    WHERE S# IN
    ( SELECT S#
    FROM SPJ
    WHERE P# IN
    ( SELECT P#
    FROM P
    WHERE COLOR = "Red") ) )


    STEP #1


    ( SELECT P#
    FROM P
    WHERE COLOR = "Red") ) )


    P TABLE


    P# PNAME COLOR WEIGHT CITY
    P1 Nut Red 12 London
    P2 Bolt Green 17 Paris
    P3 Screw Blue 17 Rome
    P4 Screw Red 14 London
    P5 Cam Blue 12 Paris
    P6 Cog Red 19 London


    PROJECTED AND RESTRICTED P TABLE


    P#
    P1
    P4
    P6

    STEP #2



    ( SELECT S#
    FROM SPJ
    WHERE P# IN {P1, P4, P6}


    S# P# J# QTY
    S1 P1 J1 200*
    S1 P1 J4 700*
    S1 P3 J1 400
    S2 P3 J2 200
    S2 P3 J3 200
    S2 P3 J4 500
    S2 P3 J5 600
    S2 P3 J6 400
    S2 P3 J7 800
    S2 P5 J2 100
    S3 P3 J1 200
    S3 P4 J2 500*
    S4 P6 J3 500*
    S4 P6 J7 500*
    S5 P2 J2 200
    S5 P2 J4 100
    S5 P5 J7 100
    S5 P6 J2 200*
    S5 P1 J4 100*
    S5 P3 J4 200
    S5 P4 J4 800*
    S5 P5 J4 400
    S5 P6 J4 500*

    STEP #3



    ( SELECT P#
    FROM SPJ
    WHERE S# IN {S1,S3,S4,S5}


    S# P# J# QTY
    S1 P1 J1 200*
    S1 P1 J4 700*
    S1 P3 J1 400*
    S2 P3 J2 200
    S2 P3 J3 200
    S2 P3 J4 500
    S2 P3 J5 600
    S2 P3 J6 400
    S2 P3 J7 800
    S2 P5 J2 100
    S3 P3 J1 200*
    S3 P4 J2 500*
    S4 P6 J3 500*
    S4 P6 J7 500*
    S5 P2 J2 200*
    S5 P2 J4 100*
    S5 P5 J7 100*
    S5 P6 J2 200*
    S5 P1 J4 100*
    S5 P3 J4 200*
    S5 P4 J4 800*
    S5 P5 J4 400*
    S5 P6 J4 500*

    STEP #4



    SELECT DISTINCT S#
    FROM SPJ
    WHERE P# IN {P1,P2,P3,P4,P5,P6}


    S# P# J# QTY
    S1 P1 J1 200*
    S1 P1 J4 700*
    S1 P3 J1 400*
    S2 P3 J2 200*
    S2 P3 J3 200*
    S2 P3 J4 500*
    S2 P3 J5 600*
    S2 P3 J6 400*
    S2 P3 J7 800*
    S2 P5 J2 100*
    S3 P3 J1 200*
    S3 P4 J2 500*
    S4 P6 J3 500*
    S4 P6 J7 500*
    S5 P2 J2 200*
    S5 P2 J4 100*
    S5 P5 J7 100*
    S5 P6 J2 200*
    S5 P1 J4 100*
    S5 P3 J4 200*
    S5 P4 J4 800*
    S5 P5 J4 400*
    S5 P6 J4 500*

    QUERY EXAMPLE



    Get supplier numbers for suppliers supplying at least one part supplied by at least one supplier who supplies at least one red part.


    SQL Query:


    SELECT DISTINCT S#
    FROM SPJ
    WHERE P# IN
    ( SELECT P#
    FROM SPJ
    WHERE S# IN
    ( SELECT S#
    FROM SPJ
    WHERE P# IN
    ( SELECT P#
    FROM P
    WHERE COLOR = "Red") ) )


    ANSWER: {S1,S2,S3,S4,S5}

    QUERY EXAMPLE


    Query: Get part numbers for parts supplied
    by a supplier in London.

    SELECT DISTINCT P#
    FROM SPJ, S
    WHERE SPJ.S# = S.S#
    AND CITY = 'London' ;


    S# P# J# QTY CITY SNAME STATUS
    S1 P1 J1 200 London ...
    S1 P1 J4 700 London ...
    S1 P3 J1 400 London ...
    S4 P6 J3 500 London ...
    S4 P6 J7 500 London ...

    ANSWER: {P1, P3, P6}
    .

    QUERY EXAMPLE


    Query: Get all pairs of part numbers such that some supplier supplies both the indicated parts.

    SELECT SPJX.P#, SPJY.P#
    FROM SPJ SPJX, SPJ SPJY
    WHERE SPJX.S# = SPJY.S#
    AND SPJX.P# > SPJY.P# ;

    SPJ TABLE
    S# P# J# QTY
    S1 P1 J1 200 (P1,P3)
    S1 P1 J4 700
    S1 P3 J1 400
    S2 P3 J2 200 (P3,P5)
    S2 P3 J3 200
    S2 P3 J4 500
    S2 P3 J5 600
    S2 P3 J6 400
    S2 P3 J7 800
    S2 P5 J2 100
    S3 P3 J1 200 (P3,P4)
    S3 P4 J2 500
    S4 P6 J3 500 ?
    S4 P6 J7 500
    S5 P2 J2 200 ?
    S5 P2 J4 100
    S5 P5 J7 100
    S5 P6 J2 200
    S5 P1 J4 100
    S5 P3 J4 200
    S5 P4 J4 800
    S5 P5 J4 400
    S5 P6 J4 500

    QUERY EXAMPLE


    Query: Get part numbers of parts supplied to some project in an average quantity of more than 320.

    SELECT DISTINCT P#
    FROM SPJ
    GROUP BY P#, J#
    HAVING AVG(QTY) > 320 ;

    SPJ TABLE AFTER GROUPING

    S# P# J# QTY
    S1 P1 J1 200

    S5 P1 J4 100
    S1 P1 J4 700

    S5 P2 J2 200

    S5 P2 J4 100

    S1 P3 J1 400
    S3 P3 J1 200

    S# P# J# QTY

    S1 P1 J1 200
    S1 P1 J4 700
    S1 P3 J1 400
    S2 P3 J2 200
    S2 P3 J3 200
    S2 P3 J4 500
    S2 P3 J5 600
    S2 P3 J6 400
    S2 P3 J7 800
    S2 P5 J2 100
    S3 P3 J1 200
    S3 P4 J2 500
    S4 P6 J3 500
    S4 P6 J7 500
    S5 P2 J2 200
    S5 P2 J4 100
    S5 P5 J7 100
    S5 P6 J2 200
    S5 P1 J4 100
    S5 P3 J4 200
    S5 P4 J4 800
    S5 P5 J4 400
    S5 P6 J4 500

    S TABLE


    S# SNAME STATUS CITY
    S1 Smith 20 London
    S2 Jones 10 Paris
    S3 Blake 30 Paris
    S4 Clark 20 London
    S5 Adams 30 Athens

    P TABLE



    P# PNAME COLOR WEIGHT CITY
    P1 Nut Red 12 London
    P2 Bolt Green 17 Paris
    P3 Screw Blue 17 Rome
    P4 Screw Red 14 London
    P5 Cam Blue 12 Paris
    P6 Cog Red 19 London

    J TABLE


    J# JNAME CITY
    J1 Sorter Paris
    J2 Punch Rome
    J3 Reader Athens
    J4 Console Athens
    J5 CollatorLondon
    J6 TerminalOslo
    J7 Tape London

    ADVANCED FEATURES



  • Retrieval using LIKE for string matching


    Get all parts whose names begin with 'C'.


    SELECT P.*
    FROM P
    WHERE P.PNAME LIKE 'C%' ;


  • '_' any single character


  • '%' any sequence of characters



    RETRIEVAL INVOLVING NULL



    Get supplier numbers for suppliers with status greater than 25.


    SELECT S#
    FROM S
    WHERE STATUS > 25;


  • If a record has STATUS equal to NULL, it will NOT qualify, because NULL does NOT match ANYTHING.

    QUERY USING EXISTS



  • EXISTS represents the existential quantifier in logic

    Get supplier names for suppliers who supply part P2.


    SELECT SNAME
    FROM S
    WHERE EXISTS
    ( SELECT *
    FROM SP
    WHERE S# = S.S#
    AND P# = 'P2' ) ;

    UPDATE OPERATIONS



    UPDATE table
    SET field = scalar-expression
    [, field = scalar-expression ] ...
    [ WHERE condition ] ;


    Change the color of part P2 to yellow, and increase weight by 5, and set city to "unknown".


    UPDATE P
    SET COLOR = 'Yellow',
    WEIGHT = WEIGHT + 5,
    CITY = NULL
    WHERE P# = 'P2' ;

    DELETE AND INSERT



    Delete supplier S5.


    DELETE
    FROM S
    WHERE S# = 'S5' ;


    Insert a new record to table P.


    INSERT
    INTO P ( P#, CITY, WEIGHT )
    VALUES ( 'P7', 'Athens', 24 );

    SQL DATA DEFINITION LANGUAGE



  • DDL statements


    CREATE TABLE
    CREATE VIEW
    CREATE INDEX


    ALTER TABLE


    DROP TABLE
    DROP VIEW
    DROP INDEX



    CREATE TABLE



    CREATE TABLE base-table
    ( column-definition [, column-definition ] ...
    [, primary-key-definition ]
    [, foreign-key-definition
    [, foreign-key-definition ]...]);


    Where column-definition is
    column data-type [ NOT NULL ]


    CREATE TABLE S
    ( S# CHAR(5) NOT NULL,
    SNAME CHAR(20) NOT NULL,
    STATUS SMALLINT NOT NULL,
    CITY CHAR(15) NOT NULL,
    PRIMARY KEY ( S# ) );


  • Data Types


    INTEGER, SMALLINT, DECIMAL(p,q), FLOAT
    CHAR(n),VARCHAR(n),GRAPHIC(n),VARGRAPHIC(n)
    DATE, TIME, TIMESTAMP

    ALTER TABLE



    ALTER TABLE base-table ADD column data-type ;


    ALTER TABLE S ADD DISCOUNT SMALLINT ;


  • Effects of adding columns


    When a record is read from disk, nulls are added to the added fields


    When a record is written to the disk, the expanded version is written


  • Effects of dropping columns


    Not supported by DB2


    DROP TABLE


    DROP TABLE base-table ;

    INDEXES



  • User can create indexes. But DB2 decides whether to use indexes or not.


    CREATE [ UNIQUE ] INDEX index
    ON base-table
    ( column [ order ] [, column [ order ]]...)
    [ CLUSTER ] ;


  • order can be ASC (ascending) or DESC (descending) or unspecified (ascending by default)


  • CLUSTER is cluster index. Each table can have at most one.


    CREATE INDEX X ON T (P, Q DESC, R) CLUSTER;


  • UNIQUE means no two records in the indexed table have identical indexed field (or fields).


    CREATE UNIQUE INDEX XS ON S ( S# ) ;


  • DBS requires unique index on every primary key.


    CREATE INDEX XSC ON S ( CITY ) ;


  • XSC is not unique index.


    DROP INDEX XSC ;