SQL

Document Sample
SQL Powered By Docstoc
					                     SA0932a: SQL
           Structured Query Language


Lots of SQL books in library:
e.g. Head First SQL
Connolly/Begg (4th ed.) 5.1, 5.2, 5.3.1 to 5.3.4, 5.3.7


Lots of SQL websites: e.g.
http://www.free-ed.net/free-ed/InfoTech/informit/ITLC15.asp:
Parts 3 & 4
    So what is the most sought after
    skill in the IT industry today?


    A - being able to sort out margins efficiently in
      Microsoft WORD
    B - an ability to throw your PC at your workmate?
    C - the ability to send e-mails to the wrong people
    D - SQL



2
    What is SQL?
       A database language
       3 parts
          DDL (Data Definition Language)
            set up tables, create keys, change table design  labs 1&2
           DCL (Data Control Language)
            control access permissions to the database etc.  later
           DML (Data Manipulation Language)
            query, manipulate data in table(s)              now and next
       non-procedural
           i.e. specify what to do, not how to do it
       Widespread use in development (embedded in multiple
        platforms)
3
                            http://uadisq01.uad.ac.uk:5560/isqlplus

    SELECT Query Structure

       Basic Form

        SELECT <attribute(s)>
        FROM   <table(s)>
        WHERE <condition>;                 Semi-colon at end

                                        * denotes all columns
        SELECT *
        FROM Transport
query 1 WHERE Make='BMW' OR Make='VOLVO';

               How many rows in the answer?
4
      Column Alias (AS)

         Renames attributes for output result

          SELECT salary AS Pay
query 2   FROM Personnel                            In Oracle,
          WHERE Surname = 'FRENCH';                 you can
                                                    omit AS

                                           RESULT
          You’ll see from your database
                                            Pay
          that French’s salary is 20184     20184


 5
         Using two or more tables
         Can use many tables
             E.g.
              SELECT p.div, b.div, surname
              FROM personnel p, branch b
query 3       WHERE p.div=b.div and City='BRISTOL';
                                                         Table aliases
             List all tables used in FROM clause
             Specify matching columns in WHERE clause!!!!!!
             Make it clear which table each column belongs to
                  Use table.column notation where ambiguous
             Can use table aliases to shorten

 6
      WHERE Clause

         Any Boolean expression involving attribute conditions
         Use
             column names, symbols =, <, etc., calculations, numbers, text
         Combine conditions with AND, OR
         Text strings must be enclosed in single quotes
            case sensitive (in Oracle)!
            E.g. this will return nothing from your database


          SELECT * FROM PERSONNEL
query 4   WHERE Sex='f';
 7
      LIKE operator

         Used for string comparisons and pattern matching in
          WHERE clause
         Uses wildcards:
            _ (underscore): any single character (? in Access)
            % (percent): string of any length (* in Access)


      SELECT * FROM PERSONNEL
      WHERE surname LIKE '_A%E'
query 5      picks 'BATE' and 'MACRAE' but not 'HAMILTON' or
              'RAINES'
 8
     ORDER BY

          ORDER BY <column> [ASC|DESC]
         Sorts the result according to the column
         Can use several levels, e.g.
        SELECT *
        FROM PERSONNEL
query 6 ORDER BY JobTitle, Salary Desc;
          Sorts results by jobtitle,
           and where jobtitle is the same, sorts by salary,
           highest first
 9
           What's the result?                           query 7

    SELECT Surname,City,Salary AS Income
    FROM Personnel,Branch B
    WHERE Personnel.Div = b.div
       AND (City LIKE '%S%' OR Surname LIKE '_R%')
    ORDER BY CITY, SALARY DESC;

ASURNAME        CITY      INCOME
                                   B   SURNAME    CITY      INCOME
    KHAN        BRISTOL   42000        KHAN       BRISTOL   42000
    RAINES      BRISTOL   25872        RAINES     BRISTOL   25872
    HAMILTON    BRISTOL   18534        HAMILTON   BRISTOL   18534
    TRINGHAM    BRISTOL    9384        TRINGHAM   BRISTOL    9384
    KUMAR       LONDON    30816        FRENCH     LONDON    20184
    FRENCH      LONDON    20184        BRAY       LONDON    18000
    BRAY        LONDON    18000        BROCK      LONDON    12288
    MACRAE      LONDON    16200
    BROCK       LONDON    12288
                                   D SURNAME      CITY      INCOME
                                       TRINGHAM   BRISTOL    9384
C   SURNAME      CITY    INCOME
                                       FRENCH     LONDON    20184
    TRINGHAM     BRISTOL 9384
                                       BRAY       LONDON    18000
                                       BROCK      LONDON    12288
     Explicit Join


        can specify JOINS explicitly in the From clause
        different types of JOIN operations:
         INNER, LEFT, RIGHT, FULL

     SELECT <columns>
     FROM <table1>
          [INNER|LEFT|RIGHT|FULL] JOIN
          <table2> ON <Join Condition>;

11
query 8

     SELECT city, jobtitle
      Worked b LEFT JOIN
     FROM branchExample personnel p ON b.div=p.div
     WHERE city <>'BRISTOL';

          CITY         JOBTITLE
          LONDON       SECRETARY
          LONDON       CLERK
          LONDON       CHAIRMAN
          LONDON       DIRECTOR
          LONDON       MANAGER
          LONDON       SECRETARY
          LONDON       ACCOUNTANT
          LONDON       CONSULTANT   These are included in the
          LONDON       CONSULTANT   LEFT JOIN even though
          LONDON       MANAGER      there is no match. They
          LONDON       CONSULTANT
          MANCHESTER
                                    would NOT be included if
          BIRMINGHAM                it were an INNER JOIN
12
      More SELECT features

     What if we wanted to strip out                          query 9
     duplicates from the answer?
     Use DISTINCT word           Select distinct city, jobTitle
                                 From branch b left join
                                        personnel p on b.div=p.div
                                 Where city <>'BRISTOL';
     Can we perform maths in the
     SELECT? YES!!!

          SELECT salary/12 AS monthPay
          SELECT salary + bonus AS totalPay
13
     Aggregates

        extends SQL
        COUNT
           COUNT(*)  how many tuples?
           COUNT(DISTINCT <field>)  how many
            unique values in field?
        SUM, MAX, MIN, AVG
        Examples
            SELECT COUNT(*)
            SELECT SUM(Salary)
            SELECT
             MIN(Salary),MAX(Salary),AVG(Salary)
14
     GROUP BY

        Applies aggregate to subsets
         of tuples (subtotals)
                                        DIV SUM(SALARY)
     SELECT Div, SUM(Salary)            30         98400
     FROM Personnel
                                        20         95790
     GROUP BY Div
                                        10        179340


                                          SUM(SALARY)
     SELECT SUM(Salary)
     FROM Personnel                              373530

     SELECT Div, SUM(Salary)
                                              Error!
     FROM Personnel
15
      Group conditions: HAVING

         HAVING
             For conditions at the group level
             Can only be used with GROUP BY
         WHERE is for conditions on individual rows

          SELECT div, max(salary)- min(salary)
          FROM PERSONNEL
query10   GROUP by div
          HAVING max(salary)- min(salary)>30000;

16
    For each division where total salary is more
    than £25,000, show no. of employees and
    total salary. Which SQL will achieve this?
    SELECT Div, COUNT(Surname), SUM(SALARY)
A   FROM Personnel GROUP BY Div
    HAVING SUM(Salary)>25000;

    SELECT Div, COUNT(Surname), SUM(SALARY)
B   FROM Personnel
    WHERE Salary > 25000
    GROUP BY Div;

    SELECT Div,COUNT(Surname),SUM(SALARY) Total
C   FROM Personnel GROUP BY Div
    HAVING Total>25000;


D   Both A and C are correct
                                                   Finds employees who
      Use of Aliases                               share the same
                                                   manager and the same
                                                   job title
         Renaming
              columns in the result output
              table abbreviations for use within SQL
         Joining a table with itself
              to find multiples instances of an attribute, e.g.

          SELECT p1.surname , p2.surname
          FROM personnel p1, personnel p2
          WHERE p1.manager = p2.manager
               and p1.surname <> p2.surname
               and p1.jobtitle = p2.jobtitle;
Query11
18
     Syntax of SELECT

     The full syntax of an SQL Select statement is

      SELECT [DISTINCT] <attribute list>
      FROM <table list>
      [WHERE <condition>]
      [GROUP BY <attribute list>]
      [HAVING <group condition>]
      [ORDER BY <attribute list>];

      […] denotes optional parts. Explicit JOIN not included
19
         Keyword Definitions

        WHERE
            A condition on individual tuples determines whether it is
             included in the result
            implicit joins (e.g. table1.key = table2.key)
        GROUP BY
            Collects together tuples which have the same value for the
             specified fields
        HAVING
            A condition on each group determines whether that group is
             included in result
        ORDER BY
20          The result table is sorted with this clause
SQL Tutor – if you have bought the book!




                                                  Interactive practice
                                                   environment for
                                                   SQL
                                                  Available in the
                                                   DatabasePlace
                                                   online
                                                  You should have
                                                   details and a
                                                   password in your
                                                   copy of
     Lots of SQL tutors online – try Google!       Connolly/Begg
21

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:143
posted:2/5/2010
language:English
pages:21