Docstoc

sql

Document Sample
sql Powered By Docstoc
					Wishing a very warm
GOOD MORNING to
 everybody ………
    What is a Database Management
                System?

• A database management system (DBMS) is a
  collection of interrelated data and a set of
  programs to access those data.
• The collection of data, usually referred to as
  database, contains information relevant to an
  enterprise.
      Database System Vs File System
• Keeping organizational information in file-
  processing system has a number of major
  disadvantages:
  o   Data Redundancy and inconsistency
  o   Difficulty in accessing data
  o   Data isolation
  o   Integrity problems
  o   Atomicity problems
  o   Concurrent-access anomalies
  o   Security Problems
                         SQL
• The history of SQL begins in an IBM laboratory in San
  Jose, California, where SQL was developed in the late
  1970s.
• SQL stands for “Structured Query language”.
• It is the standard language for relational database
  management systems.
• It is a nonprocedural language(means what rather than
  how).
• SQL lets you access and manipulate databases.
• It was originally developed for IBM's DB2 product.
                     Continues…….
• Some common RDBMS that used SQL are ORACLE,
  CYBASE, and Microsoft SQL SERVER.
• Consists of several parts:
  o   Data Definition Language (DDL)
        CREATE, ALTER, DROP, RENAME etc.

  o   Data Manipulation Language (DML)
        INSERT, UPDATE, DELETE etc.

  o   Data Control Language(DCL)
        GRANT, REVOKE etc.
           SELECT Statement
• Used to retrieve data from database.
• Syntax:
SELECT [ALL|DISTINCT] columns / expr [AS new name]
FROM table
[WHERE conditions]
[GROUP BY fields]
[HAVING conditions]
[ORDER BY column/integer [ASC|DESC]];
              INSERT Statement
• The INSERT statement enables you to enter data
  into the database.
• It can be broken down into two statements:
  o   INSERT...VALUES
  o   INSERT...SELECT
        INSERT...VALUES Statement
• Enters data into a table one record at a time.
• Syntax:
     INSERT INTO table_name (col1, col2...)
     VALUES (value1, value2...)
• Rules:
  o The values used must be the same data type as the
    fields they are being added to.
  o The data's size must be within the column's size.
  o The data's location in the VALUES list must
    correspond to the location in the column list of the
    column it is being added to.
                  Examples
• INSERT INTO COLLECTION (ITEM, WORTH,
  REMARKS) VALUES('SUPERMANS CAPE', 250.00,
  'TUGGED ON IT');
• INSERT INTO COLLECTION
  VALUES('STRING',1000.00,'SOME DAY IT WILL BE
  VALUABLE');
• INSERT INTO COLLECTION VALUES ('SPORES
  MILDEW FUNGUS', 50.00, ' ');
       INSERT...SELECT Statement
• It enables the programmer to copy information from a
  table or group of tables into another table.
• Syntax:
       INSERT INTO table_name(col1, col2...)
       SELECT col1, col2…FROM tablename
       WHERE search_condition
• Rules:
  o The SELECT statement cannot select rows from the table that is
    being inserted into.
  o The number of columns in the INSERT INTO statement must equal
    the number of columns returned from the SELECT statement.
                Continues…
• The data types of the columns in the INSERT
  INTO statement must be the same as the data
  types of the columns returned from the SELECT
  statement.
• Example:
 INSERT INTO INVENTORY (ITEM, COST, REMARKS)
 SELECT ITEM, WORTH, REMARKS
 FROM COLLECTION;
                UPDATE Statement
• The purpose of the UPDATE statement is to change the
  values of existing records.
• Syntax:
       UPDATE table_name
       SET columnname1 = value1
       [, columname2 = value2]...
       WHERE search_condition
• Example:
       Update collection
       Set worth = 900, item = ball
       Where item = 'STRING';
             DELETE Statement
• The purpose of the DELETE statement is to
  delete existing records from a single table.
• Syntax:
    DELETE FROM table_name
    WHERE condition;
• Example:
    DELETE FROM COLLECTION
    WHERE WORTH < 275;
                   Generic data types
• Char(x):
       A column of characters, where x is the maximum number of characters
        allowed in the column.
• Integer:
       A column of whole numbers, positive or negative.
• Decimal(x, y):
       A column of decimal numbers, where x is the maximum length in digits
        of the decimal numbers, and y is the maximum number of digits
        allowed after the decimal point.
• Date:
       A date column in a DBMS-specific format.
• Logical:
       A column that can hold only two values: TRUE or FALSE.
                     Operators in SQL
• Arithmetic operators
      Used for arithmetic operations.
      -, +, *, /,%.
• Concatenation operator(||)
      It combines columns or character string to other columns.
• Comparison operators
        <,<= ,>, >=, (<>, !=, ^=).
        LIKE
        IN
        IS NULL
        BETWEEN…AND..
                        Continues…
• Logical Operators
      AND, OR, NOT
• Set Operators
        Used to combine results of two or more queries.
        UNION
        UNION ALL
        INTERSECT
        MINUS
                 SQL Functions
•   Number Functions
•   Character Functions
•   Group Functions
•   Date Functions
•   Conversion Functions
•   Miscellaneous Functions
                Number Functions
• Number functions accept numeric input and
  return numeric values.
• Examples:
  o   ABS (n)
  o   ROUND (n [, m])
  o   MOD(M,N)
  o   SQRT(n) etc.
               Character Functions
• Character functions accept character input and
  return either character or numeric values.
• Examples:
  o   CONCAT (col1 /expr1, col2 / expr2)
  o   INITCAP (col / expr)
  o   LENGTH (col / expr)
  o   SUBSTR(col / expr, M[,N])etc.
                    Group Functions
• Operate on group of records.
• Group function does not work in the WHERE clause.
• Examples:
  o   AVG ([DISTINCT | ALL] n)
  o   SUM ([DISTINCT | ALL] n)
  o   MAX ([DISTINCT | ALL] expr)
  o   MIN ([DISTINCT | ALL] expr)
  o   COUNT (* | [DISTINCT | ALL] expr) etc.
                Date Functions
• Date functions operate on values of the DATE
  data type.
• All date functions return a value of DATE data
  type, except the MONTHS_BETWEEN function,
  which returns a number.
• Examples:
  o   SYSDATE
  o   MONTHS_BETWEEN (date1,date2)
  o   ADD_MONTHS(date, n) etc.
              Conversion Functions
• Conversion functions convert a value from one
  data type to another.
• Examples:
  o   TO_CHAR (number / date, ‘format model’)
  o   TO_NUMBER (‘char’, ‘format model’)
             Miscellaneous Functions
• Examples:
  o   NVL(column, expr)
        It converts a null value to actual value.

  o   NVL2(column, expr1, expr2)
        The function examines the column if the value is not null
         then the function returns the 1st expr, otherwise the 2nd
         expr is returned.

  o   USER
        It returns the character name of the current user of the
         database.
                     Constraints
• A constraint is basically a rule associated with a column
  that the data entered into that column must follow.
• Types of SQL constraints:
   o NOT NULL constraint
   o Unique constraint
   o Primary key constraint
   o Foreign key constraint
   o Check constraint
                    Joining Tables
• Sometimes we have to select data from two or
  more tables to make our result complete, we have
  to perform a join.
• Types of joins
  o   Inner Joins
  o   Outer Joins
                    Inner joins
• Returns result only if it find match on both side
  according to the condition in the ON clause.
• Types:
      Equijoins and Non-equijoins
      Multiple Joins
      Cross Joins
      Self-Joins
                    Outer joins
• An outer join doesn’t require a match on both
  sides.
• You can specify which table always returns
  results regardless of the conditions in the ON
  clause.
• Types:
      Left Outer Join
      Right Outer Join
      Full Outer Join

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:85
posted:8/19/2012
language:English
pages:28