Docstoc

Tutor

Document Sample
Tutor Powered By Docstoc
					Agenda

• TMA01
• M876 Block 3 – Using SQL
Structured Query Language
- SQL

• A non-procedural language to
  – Create database and relation structures.
  – Perform basic data management tasks, such
    as insertion, modification and deletion of data
    from the relations.
  – Perform both simple and complex queries to
    transform the raw data into information.
• ISO standard  Portable.
• Easy to learn.
DDL and DML

• Two major types of SQL command
  – Data Definition Language (DDL)
     • Commands to define the database including
       creating, altering and dropping tables, and
       establishing constraints and creating views.
     • E.g. CREATE TABLE, ALTER TABLE, CREATE VIEW,
       CREATE INDEX and so on.
  – Data Manipulation Language(DML)
     • Commands to insert, update, delete and retrieve data
       from the database.
     • E.g. INSERT, UPDATE, DELETE, SELECT and so on.
Terminology

Relational Model   Relational Database
terminology        terminology
Relation           Table

Attribute          Column

Domain             Data type

Tuple              Row
Table Name             Column Name



STAFF

StaffID      Name           Salary

1111         Bill Gates     10000    Row


2468         Bill Clinton   15000

1357         John Major     8000
    General form of a SELECT
    statement

SELECT   [DISTINCT | ALL] {*|column_expression[AS new_name]][,…]}
  FROM   table_name [alias][,…]
[WHERE   condition]
[GROUP   BY column list][HAVING condiion]
[ORDER   BY coluum list]

Remarks:
SELECT         Specifies which columns are to appear in the output
FROM           Specifies the table or tables to be used.
WHERE          Filters the rows subject to some conditions.
GROUP BY       Form groups of rows with the same column value.
HAVING         Filters the groups subject to some condition.
ORDER BY       Specifies the order of the output.
    Retrieve all columns, all
    rows

                      List full details of all staff.


SELECT sno, fname, lname, address, tel_no, position, sex, dob, salary, nin, bno
 FROM staff;

                                    
SELECT *
 FROM staff;
   Retrieve specific columns,
   all rows

Produce a list of salaries for all staff, showing only the staff number,
the first and last names, and the salary details.

                  SELECT sno, fname, lname, salary
                   FROM staff;


              sno       fname       lname      salary
              SL21      John        White       30000.00
              SG37      Ann         Beech       12000.00
              SG14      David       Ford        18000.00
              SA9       Mary        Howe         9000.00
              SG5       Susan       Brand       24000.00
              SL41      Julie       Lee          9000.00
 Use of DISTINCT

List the property numbers of all properties that have been viewed.



     SELECT pno                        SELECT DISTINCT(pno)
       FROM viewing;                     FROM viewing;


          pno                                 pno
          PA14                                PA14
          PG4                                 PG4
          PG4                                 PG36
          PA14
          PG36
Calculated fields

Produce a list of monthly salaries for all staff, showing the staff
number, the first and last names and the salary details.


      SELECT sno, fname, lname, salary/12 AS monthly_salary
        FROM staff;


         sno       fname      lname       monthly_salary
         SL21      John       White              2500.00
         SG37      Ann        Beech              1000.00
         SG14      David      Ford               1500.00
         SA9       Mary       Howe                750.00
         SG5       Susan      Brand              2000.00
         SL41      Julie      Lee                 750.00
Comparison search condition


   List all staff with a salary greater than 10,000.


       SELECT sno, fname, lname, position, salary
         FROM staff
       WHERE salary > 10000;


  sno       fname       lname      position    salary
  SL21      John        White      Manager      30000.00
  SG37      Ann         Beech      Snr Asst     12000.00
  SG14      David       Ford       Deputy       18000.00
  SG5       Susan       Brand      Manager      24000.00
Compound comparison
search condition

List addresses of all branch offices in London or Glasgow.



       SELECT bno, street, area, city, pcode
        FROM branch
       WHERE city = ‘London’ OR city = ‘Glasgow’;


   bno    street         area      city       pcode
   B5     22 Deer Rd     Sidcup    London     SW1 4EH
   B3     163 Main St    Partick   Glassgow   G11 9QX
   B2     56 Clover Dr             London     NW10 6EU
Range search condition
(BETWEEN/NOT BETWEEN)


 List all staff with a salary between 20,000 and 30,000.


       SELECT sno, fname, lname, position, salary
         FROM staff
       WHERE salary BETWEEN 20000 AND 30000;


   sno       fname      lname      position   salary
   SL21      John       White      Manager     30000.00
   SG5       Susan      Brand      Manager     24000.00
Set membership search
condition (IN/NOT IN)


   List all Managers and Deputy Managers.


    SELECT sno, fname, lname, position
      FROM staff
    WHERE position IN (‘Manager’,’Deputy’);


   sno       fname      lname      position
   SL21      John       White      Manager
   SG14      David      Ford       Deputy
   SG5       Susan      Brand      Manager
    Pattern match search
    condition (LIKE/NOT LIKE)

Find all staff with the string ‘Glasgow’ in their address.
(Note: % precent character represents any sequence of zero or more characters
       _ underscore character represents any single character



                  SELECT sno, fname, lname, address, salary
                   FROM staff
                  WHERE address LIKE ‘%Glasgow%’;


 sno      fname    lname    address                               salary
 SG37     Ann      Beech    81 George St, Glasgow PA1 2JR          12000.00
 SG14     David    Ford     63 Ashbby St, Partick, Glasgow G11     18000.00
 SG5      Susan    Brand    5 Gt Western Rd, Glasgow G12           24000.00
 NULL search condition
 (IS NULL/IS NOT NULL)

List the details of all viewings on property PG4 where a comment
has not been supplied.


          SELECT rno, date
           FROM viewing
          WHERE pno = ‘PG4’ AND comment IS NULL;


                    pno      date
                    CR56     26-May-98
 Single column ordering

Produce a list of salaries for all staff, arranged in descending order
of salary.


                 SELECT sno, fname, lname, salary
                  FROM staff
                 ORDER BY salary DESC;


             sno       fname       lname      salary
             SL21      John        White       30000.00
             SG5       Susan       Brand       24000.00
             SG14      David       Ford        18000.00
             SG37      Ann         Beech       12000.00
             SA9       Mary        Howe         9000.00
             SL41      Julie       Lee          9000.00
Multiple column ordering

Produce an abbreviation list of properties arranged in order of
property type and then in descending order of rent.


              SELECT pno, type, rooms, rent
               FROM property_for_rent
              ORDER BY type, rent DESC;


             pno     type     rooms rent
             PG16    Flat     4       450
             PL94    Flat     4       400
             PG36    Flat     3       375
             PG4     Flat     3       350
             PA14    House    6       650
             PG21    House    5       600
Use of COUNT(*)

How many properties cost more than 350 per month to rent?


               SELECT COUNT(*) AS count
                FROM property_for_rent
               WHERE rent > 350;


                         count
                           5
Use of COUNT(DISTINCT)

How many different properties were viewed in May 1998?


   SELECT COUNT(DISTINCT pno) AS count
    FROM viewing
   WHERE date BETWEEN ‘1-May-98’ AND ‘31-May-98’;


                        count
                          2
Use of COUNT and SUM

Find the total number of Managers and the sum of their salaries.


      SELECT COUNT(sno) AS count, SUM(salary) AS sum
       FROM staff
      WHERE position = ‘Manager’;



              count                          sum
                2                          54000.00
Use of MIN, MAX, AVG

   Find the minimum, maximum, and average staff salary.


SELECT MIN(salary) AS min, MAX(salary) as max, AVG(salary) AS avg
 FROM staff;



          min                  max                   avg
        9000.00              30000.00              17000.00
Use of GROUP BY

Find the number of staff working in each branch and the
sum of their salaries.


  SELECT   bno, COUNT(sno) AS count, SUM(salary) AS sum
    FROM   staff
  GROUP    BY bno
  ORDER    BY bno;


        bno                 count                sum
        B3                    3                54000.00
        B5                    2                39000.00
        B7                    1                9000.00
Mutiple grouping columns

Find the number of properties handled by each staff members.

         SELECT s.bno, s.sno, COUNT(*) AS count
           FROM staff s, property_for_rent p
          WHERE s.sno = p.sno
         GROUP BY s.bno, s.sno
         ORDER BY s.bno, s.sno;

       bno                 sno                    count
       B3                 SG14                      2
       B3                 SG37                      2
       B5                 SL41                      1
       B7                 SA9                       1
   Use of HAVING

Find each branch office with more than one member of staff, find the
number of staff working in each branch and the sum of their salaries

       SELECT   bno, COUNT(sno) AS count, SUM(salary) AS sum
         FROM   staff
       GROUP    BY bno
       HAVING   COUNT(sno) > 1
       ORDER    BY bno;


             bno                 count                sum
             B3                    3                54000.00
             B5                    2                39000.00
Using a subquery with
equality

  List the staff who work in the branch at ‘163 Main St’.

         SELECT sno, fname, lname, position
           FROM staff
         WHERE bno =
                (SELECT bno
                   FROM branch
                 WHERE street = ‘163 Main St’);

       sno       fname       lname      position
       SG37      Ann         Beech      Snr Asst
       SG14      David       Ford       Deputy
       SG5       Susan       Brand      Manager
  Using a subquery with an
  aggregate function

List all staff whose salary is greater than the average salary, and list
by how much their salaries is greater than the average.

     SELECT sno, fname, lname, position,
            salary - (SELECT AVG(salary) FROM staff) AS sal_diff
       FROM staff
     WHERE salary > (SELECT AVG(salary)
                        FROM staff);


          sno       fname      lname       position   sal_diff
          SL21      John       White       Manager     13000.00
          SG14      David      Ford        Deputy       1000.00
          SG5       Susan      Brand       Manager      7000.00
Nested subqueries
Use of IN


List the properties that are handled by staff who work in the
branch at ‘163 Main St’.

   SELECT pno, street, area, city, pcode, type, rooms, rent
     FROM property_for_rent
   WHERE sno IN
          (SELECT sno
             FROM staff
           WHERE bno =
                    (SELECT bno
                        FROM branch
                     WHERE street = ‘163 Main St’));
  Simple Join

List the names of all renters who have viewed a property along with
any comment supplied.


            SELECT r.rno, fname, lname, pno, comment
              FROM renter r, viewing v
            WHERE r.rno = v.rno;


        rno      fname      lname     pno    comment
        CR56     Aline      Stewart   PG36
        CR56     Aline      Stewart   PA14   too small
        CR56     Aline      Stewart   PG4
        CR62     Mary       Tregear   PA14   no dining room
        CR76     John       Kay       PG4    too remote
Sorting a join

For each branch office, list the names of staff who manage
properties, and the properties they manage.

         SELECT s.bno, s.sno, fname, lname, pno
           FROM staff s, property_for_rent p
         WHERE s.sno = p.sno;

        bno   sno    fname      lname      pno
        B3    SG14   David      Ford       PG4
        B3    SG14   David      Ford       PG16
        B5    SL41   Julie      Lee        PL94
        B3    SG37   Ann        Beech      PG21
        B3    SG37   Ann        Beech      PG36
        B7    SA9    Mary       Howe       PA14
Sorting a join (Cont.)

    SELECT s.bno, s.sno, fname, lname, pno
      FROM staff s, property_for_rent p
    WHERE s.sno = p.sno
     ORDER BY s.bno, s.sno, pno;


    bno   sno    fname     lname      pno
    B3    SG14   David     Ford       PG16
    B3    SG14   David     Ford       PG4
    B3    SG37   Ann       Beech      PG21
    B3    SG37   Ann       Beech      PG36
    B5    SL41   Julie     Lee        PL94
    B7    SA9    Mary      Howe       PA14
Three-table join

 For each branch, list the staff who manage properties,
 including the city in which the branch is located and the
 properties they manage.

        SELECT b.bno, b.city, s.sno, fname, lname, pno
          FROM branch b, staff s, property_for_rent p
        WHERE b.bno = s.bno AND s.sno = p.sno
         ORDER BY b.bno, s.sno, pno;

      bno   city        sno     fname      lname         pno
      B3    Glasgow     SG14    David      Ford          PG16
      B3    Glasgow     SG14    David      Ford          PG4
      B3    Glasgow     SG37    Ann        Beech         PG21
      B3    Glasgow     SG37    Ann        Beech         PG36
      B5    London      SL41    Julie      Lee           PL94
      B7    Aberdeen    SA9     Mary       Howe          PA14
Left outer join

   List the branch offices and properties that are in the
   same city along with any unmatched branches.


SELECT b.*, p.*
FROM branch1 b LEFT JOIN property_for_rent1 p ON b.bcity = p.pcity;



                bno   bcity        pno     pcity
                B3    Glasgow      PG4     Glasgow
                B4    Bristol      NULL    NULL
                B2    London       PL94    London
Right outer join

   List the branch offices and properties that are in the
   same city and any unmatched properties.


SELECT b.*, p.*
FROM branch1 b RIGHT JOIN property_for_rent1 p ON b.bcity = p.pcity;



                bno bcity          pno     pcity
                NULL NULL          PA14    Aberdeen
                B3 Glasgow         PG4     Glasgow
                B2 London          PL94    London
Full outer join

   List the branch offices and properties that are in the
   same city and any unmatched branches or properties.


SELECT b.*, p.*
FROM branch1 b FULL JOIN property_for_rent1 p ON b.bcity = p.pcity;




               bno    bcity      pno     pcity
               NULL   NULL       PA14    Aberdeen
               B3     Glasgow    PG4     Glasgow
               B4     Bristol    NULL    NULL
               B2     London     PL94    London
Query using EXISTS

     Find all staff who work in a London branch.


  SELECT sno, fname, lname, position
    FROM staff s
  WHERE EXISTS
         (SELECT ‘Y'
            FROM branch b
          WHERE s.bno = b.bno AND city = ‘London’);


      sno      fname      lname     position
      SL21     John       White     Manager
      SL41     Julie      Lee       Assistant
Use of UNION

Construct a list of all areas where there is either a branch
office or a rental property.


                 (SELECT area                   area
                    FROM branch                 Sidcup
                  WHERE area IS NOT NULL)       Dyce
                 UNION                          Partick
                 (SELECT area                   Leigh
                    FROM property_for_rent      Dee
                  WHERE area IS NOT NULL);      Kilburn
                                                Hyndland
Use of INTERSECT

Construct a list of all cities where there is both a branch
office and a rental property.


                 (SELECT city
                    FROM branch)
                 INTERSECT
                 (SELECT city
                    FROM property_for_rent);



                         city
                         Aberdeen
                         Glasgow
                         London
Use of EXCEPT

Construct a list of all cities where there is a branch office
but no rental properties.


                 (SELECT city
                    FROM branch)
                 EXCEPT
                 (SELECT city
                    FROM property_for_rent);



                         city
                         Bristol
Add data to the database
(INSERT)




 INSERT INTO table_name [(column_list)]
 VALUES (data_value_list)
INSERT … VALUES

  Insert a new record into the Staff table supplying data for
  all columns.



INSERT INTO staff
VALUES (‘SG16’, ‘Alan’, ‘Brown’, ’67 Endrick Rd, Glosgow G32 8QX’,
         ‘0141-211-3001’, ‘Assistant’, ’M’, DATE ‘1975-05025’, 8300,
         ‘WN848391H’, ‘B3’);
INSERT uisng defaults
 Insert a new record into the Staff table supplying data for
 all mandatory columns: Sno, FName, LName, Position,
 Salary, and Bno.



  INSERT INTO staff (sno, fname, lname, position, salary, bno)
  VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, 8100, ‘B3’);
   Modify data in the database
   (UPDATE)



UPDATE table_name
SET column_name1 = data_value1 [, column_name2 = data_value2 …]
[WHERE search_condition]
UPDATE all rows

Give all staff a 3% pay increase.




                UPDATE staff
                   SET salary = salary * 1.03;
UPDATE specific rows

Give all Managers a 5% pay increase.




              UPDATE staff
                 SET salary = salary * 1.05
               WHERE position = ‘Manager’;
UPDATE multiple columns

Promote David Ford (Sno = ‘SG14’) to Manager and
change his salary to £18,000.



              UPDATE staff
                 SET position = ‘Manager’,
                     salary = 18000
               WHERE sno = ‘SG14’;
   Deleting data from the
   database (DELETE)



DELETE FROM table_name
[WHERE search_condition]
DELETE specific rows

Delete all viewings that relate to property PG4.




                DELETE FROM viewing
                WHERE pno = ‘PG4’;
DELETE all rows

Delete all records from the Viewing table.




                DELETE FROM viewing;

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:10
posted:1/28/2012
language:English
pages:50