Applying Software Agent in Collaborative Software Maintenance

Document Sample
Applying Software Agent in Collaborative Software Maintenance Powered By Docstoc
					Structured Query Language
           (SQL)
             Dr. Fatimah Sidi
                  Room: C2-07
                H/P: 012-2038131

Research Area::
Data Warehouse; Database; Data Quality & Integrity;
Management Information Sistem;
Knowledge Management;
       INTRODUCTION

Structured Query Language
(SQL) is the standard language
designed to access relational
databases.
SQL should be pronounced as
the letters “S-Q-L.”
               SQL statements
Five types of SQL statements
1. Query statements
  – SELECT
2. Data Manipulation Language (DML)
   statements
  – INSERT
  – UPDATE
  – DELETE
                   SQL statements
3. Data Definition Language (DDL)
   statements
   -There are five basic types of DDL
   statements:
    •   CREATE
    •   ALTER
    •   DROP
    •   RENAME
    •   TRUNCATE
                     SQL statements
4. Transaction Control (TC) statements
   -There are three TC statements:
     •   COMMIT
     •   ROLLBACK.
     •   SAVEPOINT
5. Data Control Language (DCL) statements
   -There are two DCL statements:
     •   GRANT
     •   REVOKE
    Creating the Store Schema
• The imaginary store sells items such as
  books, videos, DVDs, and CDs.
• The database for the store will hold
  information about the customers,
  employees, products, and sales.
• The SQL*Plus script to create the
  database is named store_schema.sql.
               Running the
              SQL*Plus Script
• You perform the following steps to
  create the store schema:
1. Start SQL*Plus.
2. Log into the database as a user
  (user1 – user28)
3. Run the store_schema.sql script from
  within SQL*Plus using the @ command.
            Examining the Store
                  Tables
• Customer details
• Types of products sold
• Product details
• A history of the products purchased by
  the customers
• Employees of the store
• Salary grades
               Describe tables

SQL> Describe customers
                   Query tables

SQL>Select * from customers;
                    Insert

SQL> INSERT INTO customers
 (customer_id, first_name, last_name,
 dob, phone)
 VALUES ( 6, 'Fred', 'Brown', '01-JAN-
 1970', '800-555-1215' );

1 row created.
                                   Insert

SQL> INSERT INTO customers
 VALUES ( 6, 'Fred', 'Brown', '01-JAN-
 1970', '800-555-1215' );

INSERT INTO customers (
*
ERROR at line 1:

ORA-00001: unique constraint (STORE.CUSTOMERS_PK) violated
                    Update

SQL> UPDATE customers
    SET last_name = 'Orange'
    WHERE customer_id = 2;
 1 row updated.
                  Delete

SQL> DELETE FROM customers
     WHERE customer_id = 2;
    1 row deleted.
                Undo Changes

SQL> ROLLBACK;
Rollback complete.
             Using the
           WHERE Clause
SELECT list of items
FROM list of tables
WHERE list of conditions;
           Specifying Rows to Retrieve
                    Using the
                 WHERE Clause

SQL> SELECT *
 FROM customers
 WHERE customer_id = 2;
            Performing Arithmetic

Operator Description
• + Addition
• - Subtraction
• * Multiplication
• / Division
           Performing Arithmetic

SQL> DESC products;
SQL> SELECT * FROM products;
SQL> SELECT name, price + 2
     FROM products;
SQL> SELECT name, price * 3 + 1
     FROM products;
         Using Column Aliases

SQL> SELECT price * 2 DOUBLE_PRICE
     FROM products;
SQL> SELECT price * 2 "Double Price"
    FROM products;
SQL> SELECT price * 2 AS"Double
    Price" FROM products;
           Distinct Rows

SQL> SELECT customer_id
     FROM purchases;

DISTINCT is used to suppress the duplicate
 rows
SQL> SELECT DISTINCT customer_id
     FROM purchases;
        Comparing Values

Operator Description
=        Equal
<> or != Not equal
<        Less than
>        Greater than
<=       Less than or equal to
>=       Greater than or equal to
           Comparing Values

SQL> SELECT *
     FROM customers
     WHERE customer_id <> 2;
           Comparing Values

SELECT product_id, name
FROM products
WHERE product_id > 8;
            SQL Operators
Operator             Description
LIKE                 Matches patterns in strings
IN                   Matches lists of values
BETWEEN              Matches a range of values
You can also use NOT to reverse the meaning of
  an operator:
NOT LIKE
NOT IN
NOT BETWEEN
                LIKE Operator

• To search a string for a pattern
• Underscore (_) Matches one character in
  a specified position
• Percent (%) Matches any number of
  characters beginning at the specified
  position
              LIKE Operator

SQL> SELECT *
     FROM customers
     WHERE first_name LIKE '_o%';
SQL> SELECT *
     FROM customers
    WHERE first_name NOT LIKE '_o%';
            IN Operator

SQL> SELECT *
     FROM customers
     WHERE customer_id IN (2, 3, 5);

SQL>SELECT *
    FROM customers
 WHERE customer_id NOT IN (2, 3, 5);
         BETWEEN Operator

SQL> SELECT * FROM customers
   WHERE customer_id BETWEEN 1
 AND 3;

SQL>SELECT * FROM customers
WHERE customer_id NOT BETWEEN 1
 AND 3;
             Logical Operators

• Operator      Description
• x AND y       Returns true when both
                x and y are true
• x OR y        Returns true when
                either x or y is true
• NOT x         Returns true if x is false,
                and returns false if x is
                true
      Logical Operators

SQL> SELECT * FROM customers
WHERE dob > '01-JAN-1970'
AND customer_id > 3;

SQL>SELECT * FROM customers
WHERE dob > '01-JAN-1970'
OR customer_id > 3;
        ORDER BY Clause

• to sort the rows retrieved by a query

SQL> SELECT *
   FROM customers
   ORDER BY last_name;
      ORDER BY Clause

SQL> SELECT * FROM customers
     ORDER BY first_name ASC,
     last_name DESC;
               Group By Clause
• Group by clause must be included in a
  select statement that uses a group
  function
  – AVG (column)
  – COUNT(*)
  – MAX (column)
  – MIN(column)
  – SUM (column)
               AVG

SQL> SELECT AVG(quantity)
      FROM purchases
      GROUP BY customer_id;
            COUNT

SQL> SELECT COUNT(*) FROM
 employees;
               MAX

SQL> SELECT MAX(price) FROM products;
               MIN

SQL> SELECT MIN(salary) FROM
 employees;
               SUM

SQL> SELECT SUM(quantity)
   FROM purchases
   GROUP BY product_id;
       Performing SELECT Statements
            That Use Two Tables
SQL>SELECT name, product_type_id
FROM products
WHERE product_id = 3;

SQL>SELECT name
FROM product_types
WHERE product_type_id = 2;
    Performing SELECT Statements
         That Use Two Tables

SQL> SELECT products.name,
   product_types.name
   FROM products, product_types
   WHERE products.product_type_id =
   product_types.product_type_id
   AND products.product_id = 3;
    Performing SELECT Statements
         That Use Two Tables

SQL>SELECT products.name,
   product_types.name
   FROM products, product_types
   WHERE products.product_type_id =
   product_types.product_type_id
   ORDER BY products.name;
          Table Aliases

SELECT p.name, t.name
   FROM products p, product_types t
   WHERE p.product_type_id =
       t.product_type_id
   AND p.product_id = 3;
          Table Aliases

SQL>SELECT p.name, t.name
   FROM products p, product_types t
   WHERE p.product_type_id =
   t.product_type_id
   ORDER BY p.name;
                Exercise

1. Insert a new row into the customers table
2. Update all the prices of products with an
   increase of 15%.
3. List all employees with descending order
   of last name.
4. What is the average/min/max price of
   products?
5. Delete customers who are more than 40
   years old.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:2/26/2012
language:
pages:45