An Introduction to Structured Query Language _SQL_

Document Sample
An Introduction to Structured Query Language _SQL_ Powered By Docstoc
					    Chapter 3
    Structured Query Language (SQL)
3
    Database Systems: Design, Implementation, and Management
    4th Edition


    Peter Rob & Carlos Coronel
                Introduction to SQL
     Structured Query Language (SQL) is the
      relational database model’s standard
      language.

3    SQL enable users to
        
        
            Create database and table structure (Data definition)
            Perform various types of data manipulation
           Query the database to extract useful information

       In this section of the class, we want to focus on
        Querying the database.
    Some Common SQL Data Types

     Data Type   Format
     Numeric     NUMBER(L,D)

3                INTEGER
                 SMALLINT
                 DECIMAL(L,D)

     Character   CHAR(L)
                 VARCHAR(L)

     Date        DATE
            Basic Data Management

     Listing the Table Contents with No


3
      Condition (s)
          List all data in product table (* in SQL means all attributes
           (fields) in a table)
       SELECT * FROM PRODUCT;
          Choosing columns (Instead of using *, you
           may include all or part field names)

       SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND,
         P_MIN, P-PRICE, P_DISCOUNT, V_CODE
         FROM PRODUCT;
    Figure 3.4



3   The Contents of
    the PRODUCT
    Table
                             Queries
     Partial Listing of Table Contents with Condition (s)
      Generic Syntax
        SELECT <column(s)>
           FROM <table name>



3          WHERE <conditions>;
        An example:
        SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
           FROM PRODUCT
           WHERE V_CODE = 21344;




    Figure 3.5
     Specifying condition (s) with character or
                  numeric fields
       In the previous slide, we used the following:
       SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
         FROM PRODUCT
         WHERE V_CODE = 21344;


3    The above SQL is correct if v_code (vendor_code) is
      defined as number.

     If V-Code is defined as character the correct SQL
      statement is:
       SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
         FROM PRODUCT
         WHERE V_CODE = ‘21344’;
          Notice that the use of single quotation to quote v_code.
                 Queries
      Mathematical Operators


3
    Table 3.4
                     Queries
       SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
         FROM PRODUCT
         WHERE V_CODE <> 21344;



3

    Figure 3.7
                    Queries
      SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
        FROM PRODUCT
        WHERE P_PRICE <= 10;



3
    Figure 3.8
                       More about where
     The WHERE clause can contain several conditions
      linked by AND or OR.
          Select *
                  from customer


3           
                       where state =‘MO’
                             and city=‘Cape Girardeau’
                This query lists all customers who lives in Cape Girardeau,
                MO. Notice that since state and city is character field, we use
                quotation.

          Select * from friend
                       where height > 5
                       and weight <150
               This query lists all friends whose height is taller than 5 feet and
                whose weight is less than 150 pounds. Notice that since height
                and weight are numerical fields, no quotation is necessary.
                    Queries
     Using Mathematical Operators on
      Character Attributes


3     SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
        FROM PRODUCT
        WHERE P_CODE < ‘1558-QWI’;




    Figure 3.9
                     Queries
     Logical Operators: AND, OR, and NOT
       SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE


3        FROM PRODUCT
         WHERE V_CODE = 21344
         OR V_CODE = 24288;




    Figure 3.11
                     Queries
       SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
         FROM PRODUCT
         WHERE P_PRICE < 50
         AND P_INDATE > ‘07/15/1999’;


3

    Figure 3.12
                         Queries
     Special Operators
         BETWEEN - used to define range limits.


3     


      
          IS NULL - used to check whether an attribute
          value is null
          LIKE - used to check for similar character strings.
         IN - used to check whether an attribute value
          matches a value contained within a (sub)set of
          listed values.
         EXISTS - used to check whether an attribute has a
          value. In effect, EXISTS is the opposite of IS
          NULL.
                      Queries
     Special Operators
      BETWEEN is used to define range limits.

3      SELECT *
         FROM PRODUCT
         WHERE P_PRICE BETWEEN 50.00 AND 100.00;

       SELECT *
         FROM PRODUCT
         WHERE P_PRICE > 50.00
         AND P_PRICE < 100.00;
                             Queries
     Special Operators
       LIKE is used to check for similar character strings.
       SELECT * FROM VENDOR



3         WHERE V_CONTACT LIKE ‘John%’;
          The above SQL lists all vendors whose name contains Smith
          such as Johns, Johnson, or Johnny
       SELECT * FROM VENDOR
          WHERE V_CONTACT LIKE ‘%John%’;
          This SQL lists all vendors whose name contains Smith in
          the middle such as McJohnson, OhJohnney, etc. Of course,
          I made up these fictional names for instructional
          purposes.
                          Queries
     Special Operators
       IN is used to check whether an attribute value matches a
          value contained within a (sub)set of listed values.



3      SELECT * FROM PRODUCT
         WHERE V_CODE IN (21344, 24288);

       To do the same, we could have written as:

       SELECT * FROM PRODUCT
         WHERE V_CODE = 21344 or
               V_CODE = 24288;

       EXISTS is used to check whether an attribute has value.
       DELETE FROM PRODUCT
         WHERE P_CODE EXISTS;

       SELECT * FROM PRODUCT
         WHERE V_CODE EXISTS;
      More Complex Queries and SQL
               Functions
     Ordering a Listing
       ORDER BY <attributes>


3         SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
            FROM PRODUCT
            ORDER BY P_PRICE;

          The above SQL lists output order by P_price in an
            ascending order

       ORDER BY <attributes> desc
          SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
            FROM PRODUCT
            ORDER BY P_PRICE desc;

          This SQL lists output order by P_price in an descending
            order
    MAX and MIN Function Output Examples




3
Some Basic SQL Numeric Functio
     FUNCTION   OUTPUT
     COUNT      The number of rows containing the specified attribute.



3    MIN

     MAX

     AVG
                The minimum attribute value encountered.

                The maximum attribute value encountered.

                The arithmetic mean (average) for the specified attribute.

     SUM        The sum of all values for a selected attribute.




     Table 3.6
    COUNT Function Output Examples




3

    Figure 3.24
    MAX and MIN Function Output Examples




3

    Figure 3.25
    AVG Function Output Examples




3

    Figure 3.26
       SQL to query information from
              multiple tables
    The next slide gives you an example
      of querying from multiple tables.

3   The basic command syntax (select

    
      from where) are to be used.
       More Complex Queries and
            SQL Functions
     Joining Database Tables


3       SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE,
          VENDOR.V_NAME, VENDOR.V_CONTACT,
          VENDOR.V_AREACODE, VENDOR.V_PHONE
          FROM PRODUCT, VENDOR
          WHERE PRODUCT.V_CODE = VENDOR.V_CODE;




    Table 3.7 Creating Links Through Foreign Keys
        Querying from multiple tables
     SELECT Statements
      The SELECT statement from the previous
      slide shows that the field name consists


3     of two parts connected by dot (.)such as
      PRODUCT.P_DESCRIPT. The first part of the
      field name is table name. The second part
      is field (attribute) name. This is
      interpreted as P_DESCRIPT field in PRODUCT
      table.
     FROM Statements
      The FROM statement list all tables we are
      using.
     WHERE Statements
      Where statement specifies which field is link
      field. In the following example, these two
      tables are linked by V_Code field.


3     WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
     Query 13 asks you to list several fields
      from two tables without any additional
      conditions. Therefore, you have to only
      specify the link field as shown above.
     Query 14 asks you list several fields from
      two tables with additional condition,
      which is credit limit is 10000. In this
      case, you have to add one more condition
      in addition to the link field conditions.
    

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:10
posted:7/12/2011
language:English
pages:28