Geog 495: GIS Database Design

11/1/05

SQL SYNTAX SUMMARY

 

Please note notation conventions used in this handout

 

<DATA DEFINITION>

 

1. Creating table

 

CREATE TABLE tablename (

column1 datatype [constraint] [,

column2 datatype [constraint] [,

PRIMARY KEY (column1 [, column2]) [,

FOREIGN KEY (column1 [, column2]) REFERENCES tablename(columnname));

 

2. Deleting table

 

DROP TABLE tablename;

 

3. Creating indexes

 

CREATE INDEX indexname ON tablename(column1[,column2]);

 

4. Adding a row

 

INSERT INTO tablename

VALUES (value1, value2, …, valuen);

 

INSERT INTO tablename (column1[,column2])

VALUES(value1[,value2]);

 

5. Deleting a row

 

DELETE FROM tablename

[WHERE conditionlist];

 

6. Adding a column

 

ALTER TABLE tablename

ADD columnname datatype;

 

7. Deleting a column

 

ALTER TABLE tablename

DROP COLUMN columnname;

 

8. Modifying a column characteristic

 

ALTER TABLE tablename

MODIFY columnname datatype;

 

<DATA MANIPULATION>

 

1. Selecting records

 

SELECT columnlist

FROM tablelist

[WHERE conditionlist];

 

2. Updating records

 

UPATE tablename

SET columnname = value

[WHERE conditionlist];

 

3. Joining tables

 

Let’s assume the relationship between table1 and table2 is 1:M

Then, the following SQL will return all matching rows based on referential integrity rules as indicated in WHERE statement

 

SELECT columnlist

FROM table1, table2

WHERE table1.primary_key = table2.foreign_key;

 

3-1. Inner join

 

The following SQL will return the same result as above

 

 

SELECT columnlist

FROM table1 INNER JOIN table2

ON table1.primary_key = table2.foreign_key;

 

 

 

 

3-2. Outer join

 

3-2-1. Left outer join

 

The following SQL will yield all rows from the left table

 

SELECT columnlist

FROM table1 LEFT OUTER JOIN table2

ON table1.primary_key = table2.foreign_key;

 

 

 

 

3-2-2. Right outer join

 

The following SQL will yield all rows from the right table

 

SELECT columnlist

FROM table1 RIGHT OUTER JOIN table2

ON table1,primary_key = table2.foreign_key;

 

 

 

 

Miscellaneous

 

1. Special operators

 

operators

meaning

LIKE

Is like

IN

Any match with valuelist?

BETWEEN

Any match within a range?

IS NULL

Is the value is NULL?

 

2. Comparison operator

 

>, <, >=, <=, <>

 

Works for all kinds of data types

 

3. Aggregate functions

 

SUM(), COUNT(), MAX(), MIN(), AVG()

 

It returns the number

 

4. DISTINCT lists unique values

 

INV      STATE                                               Unique list of STATE

1                    NY                                                 NY

2                    NY                                                 WA

3                    NY                         à

4                    WA

5                    WA

6                    WA

 

SELECT DISTINCT state from tablename

 

5. GROUP BY in SELECT statement

 

Must be used with aggregate function

For example, sum() gives you the total sum of all records à number

What about sum of records grouped by project? à a set of records

 

EMP_NUM    CHARGE_HOUR      PROJ_NUM

1                      20                                1

1                      12                                2

2                      21                                1

3                      10                                3

3                      10                                1

4                      2                                  1

4                      14                                2

 

SELECT SUM(CHARGE_HOUR) FROM tablename

will return 79 (= 20+12+21+10+10+2+14)

 

SELECT PROJ_NUM, SUM(CHARGE_HOUR) AS sum_hour FROM tablename

GROUP BY PROJ_NUM

Will return

 

PROJ_NUM   sum_hour

1                                            53

2                                            26

3                                            10