Docstoc

Introduction to SQL

Document Sample
Introduction to SQL Powered By Docstoc
					Introduction to SQL
      Revision
                 Rules
   All table names and field names
    must be one word eg customer_ref
   Be consistent in naming policy –
    usually better to keep everything
    lowercase
   Type SQL keywords in capitals eg
    CREATE TABLE customer …..
   Put a semicolon at the end of the
    SQL statement
            Creating a Table
There is a standard format for inserting
  SQL
to create a new table

CREATE TABLE customer
(cust_no INTEGER CONSTRAINT cust_no PRIMARY
KEY, surname CHAR (20), forename CHAR (12), tel_no
CHAR (12));


In Access you should save each query
   after running
it.
     Inserting Data into Table
Use the INSERT INTO sql statement

INSERT INTO customer
(cust_no, surname, forename, tel_no)
VALUES (25, „Smith‟, „John‟, „01475 678849‟);


Note: it is better to include the
  fieldnames in
the statement although not absolutely
necessary.
Text values have inverted commas round
  them.
Text values can have spaces between
           Deleting a record
It is easy to delete one record using
  the
wildcard (*) and a criteria to identify
  the
record to be deleted.
DELETE * FROM customer
WHERE cust_no=25;


Note: this only deletes one record
  because the
Primary Key value has been chosen
          The Select Query
Sets of values can be retrieved from the
  database
using a Select statement – this can be
  further refined
by the means of the WHERE element that
  states a
criteria

SELECT * FROM customer
WHERE surname=“smith”;


This statement returns all records from
  the table
                 The Operators

Looking at the previous statement
….
WHERE surname = “’Smith’

The operator in this instance is the = sign
Other operators that can be used with number
values including dates and currency are:
> (greater than)
< (less than)
           The Operators (cont’d)

> (greater than)
< (less than)
>= (greater than or equal to)
<= (less than or equal than

We can also find a range eg
between 10 and 20

This would find all values of 10 and above up to
and including 20.
          More complex criteria

Often we need to find information where there are
2 sets of criteria eg Find the names of all customers
who live in Greenock and owe more than £20

SELECT surname, forename, owed FROM customer
WHERE town=‘Greenock’
AND owed>=20;


Note: we have only selected the fields we want to see
The amount owed does not have a pound sign in the criteria
                  FUNCTIONS

We can use calculations in SQL statements to find
Maximum value
Minimum value            These only work
                         with numerical values
Average value
Sum

Count This is the total number of entries for a given field
           Function Examples
SELECT MAX (owed) AS max_owed
FROM customer;

SELECT AVG (owed) AS average_owed
FROM customer;

SELECT COUNT (*) AS no_of_records
FROM customer;    This counts the total no of records

SELECT COUNT (surname) AS no_of_names
FROM customer;   This counts the total no of records that
                 have a value in the surname field
        More complex functions
If we wanted to return all the records from the
customer table that had the maximum amount owed
we would need to use a nested SQL statement
First break down the requirement into individual
parts
1. All records from customer
2. Where owed is maximum
More complex functions cont’d

SELECT * FROM customer
WHERE owed =
(SELECT MAX (owed FROM customer));
Changing the structure of a
          table

Use the ALTER command to create another
field inside a table

ALTER TABLE customer
ADD column vat CURRENCY;



This adds a new field to the customer table
called vat with a fieldtype currency.
Inserting data into new field

We have created a new field called vat – now
we want to populate it with the vat amount
relating to the amount owed for each record
UPDATE customer
SET vat = owed * 0.175;



This updates the table by calculating the VAT
amount on each amount owed (multiply by
0.175)
   Merge data from one table to
            another

 First, make sure each table has the same number of
 field with the same structure
 Both tables should be inside the database

INSERT INTO customer (cust_no, surname, forename, tel_no, owed)
SELECT cust_no, surname, forename, telephone, owed
FROM branch_customer;


 Note: the fieldnames don’t have to be identical
           Creating relationships
Imagine we have two tables, customer and salesman.
We are told that one salesman has many customers but one
customer only deals with one salesman. This is called a one
to many relationship and can be shown as follows:
 customer                salesman
                 Deals
 cust_no (PK)    with
                         sales_id (PK)
 csurname                smsurname
 cfirstname              smforename
 address                 department
 sales_id (FK)
                  Relationships
        customer                salesman
                        Deals
        cust_no (PK)    with
                                sales_id (PK)
        csurname                smsurname
        cfirstname              smforename
        address                 department
        sales_id (FK)

This shows that the salesman table is related to the customer
table through the Primary key in the salesman table (sales_id)
and the copy of that primary key in the customer table, known
as the foreign key.
                Relationships

The relationships that are created are important to
allow you to create queries that return information
from 2 or more related tables.
In Access you can set up the relationship in the
design of your database and then create select
queries that will draw information from more than
one table.
In SQL you have to create the relationship inside
each select query you wish to run
An SQL query based on 2 tables

If you wished a list of customers associated to a
particular salesman (say Ken Mills), you would run a
query such as:

SELECT customer.csurname, customer.cfirstname,
salesman.smsurname, salesman.snforename
FROM customer, salesman
WHERE salesman.sales_id=customer.salesid
AND salesman.smsurname=‘Mills’;

				
DOCUMENT INFO