SQL Introduction Read this: http:ecommons'library'cornell'eduhandle1813165 Watch this: http:ecommons

1
SQL IntroductionRead this http//ecommons.librar
y.cornell.edu/handle/1813/165Watch this
http//ecommons.library.cornell.edu/handle/1813/24
89

• What is SQL
• SQL data types
• SQL aggregate functions
• SQL Expressions

2
What is SQL

• SQL (pronounced "ess-que-el") stands for
  Structured Query Language.
• SQL is used to communicate with a database.
• the standard language for relational database
  management systems
• It is a specialized language for updating,
  deleting, and requesting information from
  databases
• A relational database system contains one or more
  objects called tables. The data or information
  for the database are stored in these tables.
• Tables are uniquely identified by their names and
  are comprised of columns and rows. Columns
  contain the column name, data type, and any other
  attributes for the column. Rows contain the
  records or data for the columns

3
SQL Data Types

• Numeric
• short integer
• long integer
• Single
• Double
• Date/Time
• geometry types
• Geom, geomSDE, geomSHP, GeomWKB
• Latitude, longitude
• Text

4
Expressions

• Generic Expressions
• Generic expressions are expressions that can
  return values of any type
• Numeric Expressions
• Numeric expressions are expressions that return
  numeric values
• SELECT 95 Population (July 1) - 90 Population
  (April 1) AS PopChange,
• Name, State
• FROM Counties
• SELECT 95 Population (July 1) / 90 Population
  (April 1) AS PercChange,
• Name, State
• FROM Counties
• String Expressions
• String expressions are expressions that return
  string values
• SELECT FROM Lower 48 States WHERE
  Left(Name,2) "Mi
• SELECT FROM Lower 48 States WHERE Name
  "maryland"
• SELECT FROM Lower 48 States WHERE
  Lower(Name) "maryland"
• Date Expressions
• Date expressions are expressions that return date
  values
• SELECT day(UTC DATE-TIME) from Eqs

5
Crime by Month
SELECT monthname(month(Reported)),
Activity FROM2005 WHERE CInt(month(Reported)
) in (9,10)
SELECT FROM lower 48 States WHERE
soundslike(Name, "Mayne")
6
SQL Statements - SELECT

• SELECT selects data from existing tables
• Syntax
• SELECT ltsomethinggt FROM lttablegt
• WHERE ltsome conditiongt AND,OR, IN, lt, gt,
• SELECT statements don't change data. The minimum
  syntax for a SELECT statement is 
• SELECT columns FROM table
•  
• We can use an asterisk () to select all columns
  in a table. The following example selects all
  columns in the Employees table
•  SELECT FROM States
• We may restrict the table to return only the
  columns we want with a table filter if a column
  name is included in more than one table in the
  FROM clause, precede it with the table name and a
  dot (.).
• SELECT FROM COUNTIES, COUNTY2KWHERE
  CINT(STATE FIPS) CINT(Counties.ST)AND
  CINT(COUNTY) CINT(County2k.County FIPS)

7
GROUP BY
SELECT sum(90 Population (April 1)),
TIME_ZONE FROM counties GROUP BY
TIME_ZONE ORDER BY PopDens DESC
SELECT sum(90 Population (April 1))/sum(Area
(I))10000 PopDens, TIME_ZONE FROM
counties GROUP BY TIME_ZONE ORDER BY
PopDens DESC
8
SQL Statements - ALTER

• Modifies the design of an existing table
• Using the ALTER TABLE statement, we can alter an
  existing table in several ways. We can
•  Use ADD COLUMN to add a new column to the table.
  Specify the name, data type, an optional size,
  and an optional default value of the column.
• Use ALTER COLUMN to alter type, size or default
  value of an existing column.
• Use DROP COLUMN to delete a column. Specify only
  the name of the column.
• Use RENAME COLUMN to rename an existing column.
• Examples
• ALTER TABLE Counties ADD COLUMN PerCapInc
  CURRENCY
• ALTER TABLE Counties DROP COLUMN PerCapInc
• ALTER TABLE Counties ADD COLUMN TotalInc Real
  

9
SQL Statements - UPDATE

• Changes values in columns in a specified table or
  query.
• Syntax
• UPDATE table SET column expression DEFAULT
  , column expression DEFAULT ... WHERE
  ...
• The UPDATE statement is especially useful when we
  want to change many records or when the records
  that we want to change are in multiple tables
• UPDATE Counties SET TotalInc 89 Income
  per capita 89 Population (July 1)WHERE
  STATE "MD"
• UPDATE Counties SET TotalInc 89 Income
  per capita 89 Population (July 1)WHERE
  STATE ltgt "MD"

10
SQL Statements CREATE Table

• Creates a table
• Use the CREATE TABLE statement to define a new
  table and its columns
• CREATE TABLE NewTable (First NameTEXT,
  Last Name TEXT, Birth DateDATETIME)

11
SQL Statements CREATE Drawing

• Creates a new drawing
• CREATE DRAWING MD (STATE Text)

12
SQL Statements SELECT INTO

• Selects data from existing tables or queries into
  a new table
• SELECT INTO MDFROM COUNTIES WHERE STATE
  "MD"

13
SQL Statements - DELETE

• Deletes records from a table
• DELETE FROM Counties WHERE STATE "MD"

14
SQL Statements INSERT INTO

• Adds a record or multiple records to a table
• We can use the INSERT INTO statement to insert
  either a single record with default values, or to
  insert one or more records with arbitrary values.
  Records are added to the end of the table
• INSERT INTO MD (STATE, Geom (i))
• (SELECT (STATE),Geom (I) FROM COUNTIES WHERE
  STATE NY")
• INSERT INTO Counties (STATE, Geom (i))
• (SELECT (STATE),Geom (I) FROM MD WHERE STATE
  "MD")
• INSERT INTO COUNTIES (Geom (I))
• VALUES (CGeom(CGeomWKB("point (-76 42)")))
• INSERT INTO MD SELECT FROM COUNTIES

15
SQL Statements - TRANSFORM

• Selects data from one or more tables or queries
  grouping it with a specified pivot expression
• Use the TRANSFORM statement with a query that
  includes the GROUP BY clause to establish two
  independent sets of groups and to compute an
  aggregate function in the records defined by the
  intersection of these sets of groups
• The TRANSFORM statement splits each group
  returned by the GROUP BY clause into subgroups
  using a pivot expression, computes an aggregate
  function in each subgroup, and composes the
  computed values into a record for this group.
• TRANSFORM INT(SUM(Area (I)))
• SELECT lc
• FROM LU68 2
• WHERE luse_gen ltgt "" AND lc ltgt ""
• GROUP BY lc
• PIVOT luse_gen

TRANSFORM INT(SUM(Area (I))) SELECT state
FROM Counties GROUP BY State PIVOT
time_zone
16
SQL Aggregate Functions

• An aggregate function is a function that
  calculates the total value of a group of values
• avg, count, first, last, min, max, stdev, stdevp,
  sum, var, varp
• SELECT avg(90 Black Population), stdev(90
  Black Population)
• FROM counties
• Interesting calculations
• Mean, Standard Deviation, Coefficient of
  Variation

SELECT stdev(90 Hispanic Population/90
Population (April 1))/avg(90 Hispanic
Population/90 Population (April 1)) AS CV FROM
counties
SELECT SUM(Longitude (I) 90 Population
(April 1))/sum(90 Population (April 1)) AS X,
SUM(Latitude (I) 90 Population (April
1))/sum(90 Population (April 1)) AS Y FROM
counties
SELECT AVG(Longitude (I)) AS X,
AVG(Latitude (I)) AS Y FROM Lower 48 States
17
Clauses

• Crime and addresses (crime.map)
• PARAMETERS Enter Today's Date text, Enter
  Days integer
• SELECT 2006 Incidents.
• FROM 2006 Incidents
• WHERE
• Reported BETWEEN
• DateAdd("d",Enter Days-1, CDate(Enter
  Today's Date))
• AND CDate(Enter Today's Date)
• Transform

18
ALL, ANY, TOP, SOME, SKIP Quantifiers

• Determine if the value of an expression is equal
  to any or all values in a specified list.
• SELECT FROM Counties
• WHERE 90 Population (April 1) gt ALL
• (SELECT 89 Population (July 1)
• FROM
• Counties
• WHERE 90 Crimes per 100K gt 500)

• SELECT FROM counties
• WHERE 90 Population (April 1) gt ANY
• (SELECT Population FROM County2K, Birth
  Characteristics
• WHERE Births to teenage mothers , 1990 gt 10
• AND CInt(CInt(Birth Characteristics.FIPS)/1
  000) CInt(State FIPS/100))

SELECT SKIP 5 FROM Counties ORDER BY 90
Crimes per 100K DESC
19
ORDER BY

• Sorts records using specified criteria in
  ascending or descending order
• SELECT FROM Employees ORDER BY Last Name
  ASC

20

• BETWEEN AND Operator
• Determines whether the value of an expression
  falls within a specified range of values.
• SELECT FROM Birth Characteristics
• WHERE Births to teenage mothers , 1995
• BETWEEN 5 AND 10

21

• IN Operator
• Determines whether the value of an expression is
  equal to any of several values in a specified
  list.
• SELECT FROM Lower 48 States
• WHERE Name IN
• (SELECT Name FROM Birth Characteristics
• WHERE Births to teenage mothers , 1995
• BETWEEN 5 AND 10)

22

• LIKE and Operator
• Checks if a string value matches a given pattern.
• 'SELECT FROM Lower 48 States
• 'WHERE Name LIKE "New"
• 'SELECT FROM Lower 48 States
• 'WHERE Name LIKE "C"
• SELECT FROM Lower 48 States
• WHERE Name LIKE "ss"

23
Group by

• select sum(89 Income per capita) inc, state
• from counties
• group by state
• order by inc

24

• PARAMETERS
• Queries using PARAMETERS can ask for user
  specified parameters when the query is run.
  Parameter variables are declared in the first
  line of the query using a declaration in the
  form
• PARAMETERS name type

25
wicomico.map

• CASE Operator
• Returns a value depending on a set of
  conditions.
• SELECT ID,
• CASE LU
• WHEN "R" THEN "Residential"
• WHEN "C" THEN "Commercial"
• ELSE "NOTHING"
• END AS LU1
• FROM D

SELECT ID,ACCTID, CASE LU WHEN "R" THEN
NFMTTLVL .01 WHEN "C" THEN NFMTTLVL
.03 ELSE 0 END AS TaxPayment FROM D
SELECT ID,ACCTID, CASE WHEN NFMTTLVL BETWEEN
0 AND 100000 THEN NFMTTLVL .01 WHEN
NFMTTLVL BETWEEN 100000 AND 200000 THEN
NFMTTLVL .03 WHEN NFMTTLVL BETWEEN 200000
AND 300000 THEN NFMTTLVL .035 WHEN
NFMTTLVL gt 300000 THEN NFMTTLVL .04 ELSE
0 END AS TaxPayment FROM D
26

• INNER JOIN Operator
• Combines records from two tables.
• SELECT Lower 48 States.Name, Births to
  teenage mothers , 1990
• FROM Lower 48 States
• INNER JOIN Birth Characteristics
• ON Lower 48 States.Name Birth
  Characteristics.Name
• ADD GEOMETRY

27
Gerrymandering
SELECT name,Party_AFF, Length (I)/(Sqr(Area
(i) 4 3.1415926)) AS Gerry FROM
Cong99 WHERE Gerry gt 3 AND Edge is false

• How to compute a Gerry Factor
• Based on the area of a feature, assume that area
  is a circle
• Compute the circumference of an object, assuming
  it was a circle a circle is the most compact
  shape possible
• Circumference Sqr(Area (i) 4 3.1415926)
• Determine the true perimeter of the object.
• Determine the ratio of the true perimeter to the
  hypothesized circumference if the object was a
  circle, and therefore compact
• The higher the Gerry Factor, the more likely the
  area is an odd shape however, pay particular
  attention to boundary areas.

SELECT Party_AFF, count() FROM Cong99 WHERE
Length (I)/(Sqr(Area (i) 4 3.1415926)) gt
3 AND Edge is false GROUP BY PARTY_AFF
28
The Spatial Stuff

• area, areaearth, assigncoordsys, buffer,
  centroid, contains, converttoarea, distance,
  distanceearth, intersects, linepoint, newpoint,
  newline, touches, unionareas

29
Adjacent

• SELECT FROM D
• WHERE Adjacent(D.id,(SELECT id FROM D
• WHERE Selection (I) TRUE))
• (now get the sum of the land values)
• PARAMETERS ENTER PARCEL ID text
• SELECT FROM D
• WHERE Adjacent(D.id,(SELECT id FROM D
• WHERE ACCTID ENTER PARCEL ID))
• Use 0603029603 for ACCTID

30
Adjacent II

• PARAMETERS Enter Birth to Teenage Mothers
  real
• SELECT FROM Lower 48 States, Birth
  Characteristics
• WHERE Births to teenage mothers , 1990 gt
  Enter Birth to Teenage Mothers
• AND Lower 48 States.Name Birth
  Characteristics.Name
• .
• .
• .
• SELECT FROM D, (SELECT id FROM D
• WHERE Selection (I) TRUE) AS J
• WHERE Adjacent(D.id,J.ID)
• (now do it where the NFMTTLVL is greater than
  300000 well call this query FIND RICH
  NEIGHBORS)

31
Area and AreaEarth

• PARAMETERS ENTER PROPERTY VALUE real
• SELECT sum(Area (I))
• FROM D
• WHERE NFMTTLVL ENTER PROPERTY VALUE
• .
• .
• PARAMETERS ENTER PROPERTY VALUE real
• SELECT sum(AreaEarth(Geom (I),"mi"))
• FROM D
• WHERE NFMTTLVL gt ENTER PROPERTY VALUE

32
Buffer

• PARAMETERS Enter ACCTID text
• SELECT AreaEarth((Geom (I)),"ft") AS Raw,
• AreaEarth((buffer(Geom (I),300,"ft")),"ft")
  As Buffered
• FROM D
• WHERE ACCTID Enter ACCTID
• ACCTID 0603021904
• PARAMETERS Enter ACCTID text
• SELECT D., buffer(Geom (I),300,"ft") As
  Buffered
• FROM D
• WHERE ACCTID Enter ACCTID
• Now do it for NFMTTLVL gt 300000

33
Contains

• SELECT D.
• FROM D,Growth
• WHERE CONTAINS(Growth.id,D.id)
• .
• .
• PARAMETERS ENTER ACCTID text
• SELECT D.
• FROM D,D BUFD
• WHERE CONTAINS(buffer(BUFD.id,300,"ft"),D.id)
• AND BUFD.ACCTID ENTER ACCTID
• 0603021904
• Now, find parcels that are inside the Critical
  Area (CA)

34
Distance

• SELECT distance(d.id,ca.id) as dist, d.ID
• from d, ca
• where distance(d.id,ca.id) lt 300
• and d.Selection (I) true
• .
• .
• SELECT distance(d.id,ca.id) as dist, d.ID
• from d, ca
• where NFMTTLVL gt 300000
• and d.Selection (I) true
• .
• .
• Now, find the sum of the NFMTTLVL for the
  properties within 300 feet of the critical area
• select from (SELECT distance(d.id,ca.id, "mi")
  as dist, d.ID
• from d, ca
• where NFMTTLVL gt 300000)
• where dist between 1 and 300

35
DistanceEarth

• PARAMETERS Enter City 1 text, Enter City 2
  text
• SELECT DISTANCEEARTH(A.ID,B.ID,"mi")
• FROM Cities and Towns A, Cities and Towns B
• WHERE B.Name ", " B.STATE Enter City
  1
• AND A.Name ", " A.STATE Enter City 2

36
INTERSECTS

• SELECT d.geom (i) as buffered, d.ID
• FROM d, ca
• WHERE intersects(d.id,ca.id)
• Now, get the sum of the geometries

37
CLIPINTERSECT

• SELECT clipIntersect(d.id,ca.id) as buffered
• FROM d, ca
• Overlay.map

38
NewPoint and NewLine

• INSERT INTO D (Geom (i)) VALUES(NewPointLatLon
  (-75.833,38.877))

39
UnionAll

• SELECT UnionAll(Geom (I)),sum(90 Population
  (April 1)) pop,STATE
• FROMCounties98_demog
• GROUP BYSTATE
• UnionAll is an aggregate function, just like SUM.
  So, create a landuse layer, grouping the
  geometries by LU

40
Other geometry stuff

• SELECT TriangulationLines(AllCoords(Geom (I)))
  FROM Cities and Towns
• WHERE IsPoint(ID)
• AND Selection (I) TRUE
• .
• .
• INSERT INTO Cities and Towns (Geom (i)) (
• SELECT TriangulationLines(AllCoords(Geom (I)))
  FROM Cities and Towns
• WHERE IsPoint(ID)
• AND Selection (I) TRUE)
• .
• .
• INSERT INTO Cities and Towns (Geom (i)) (
• SELECT Triangulation(AllCoords(Geom (I))) FROM
  Cities and Towns
• WHERE IsPoint(ID)
• AND Selection (I) TRUE)

41
More other stuff

• CREATE DRAWING Interstate (SIGN1 Text)
• INSERT INTO Interstate (Geom (I), SIGN1)
• SELECT AllBranches(Geom(ID)), SIGN1 FROM
  Interstates D GROUP BY SIGN1
• PARAMETERS Enter State text
• SELECT Cities and Towns. FROM Cities and
  Towns, Interstates, Lower 48 States
• WHERE Contains(Lower 48 States.id,Cities and
  Towns.id)
• AND Cities and Towns.POP_90 gt 20000
• AND Lower 48 States.Name Enter State
• AND DISTANCEEARTH(Interstates.ID,Cities and
  Towns.ID,"mi") lt 20

42
Create Rectangles

• INSERT INTO D (Geom (I), MyPit)
• (select
• ConvertToArea(NewLine(
• NewPoint(X (I)-40,Y (I)-40),
• NewPoint(X (I)-40,Y (I)40),
• NewPoint(X (I)40,Y (I)40),
• NewPoint(X (I)40,Y (I)-40),
• NewPoint(X (I)-40,Y (I)-40))),mypit
• from D)

43
SURFACE STUFF
44
SELECT Heights

• OPTIONS COORDSYS("u27elu" AS COMPONENT)
• SELECT FROM U27elu WHERE Height (I) gt 300
• OPTIONS COORDSYS("u27elu" AS COMPONENT)
• SELECT FROM U27elu WHERE Height (I) BETWEEN
  300 AND 400

45
CHANGE THE SELECTION, DETERMINE TOTAL AREA

• UPDATE u27elu SET Selection (I) True
• WHERE Height (I) BETWEEN 300 AND 400
• SELECT Count()100 FROM u27elu
• WHERE Height (I) BETWEEN 300 AND 400

46
Grouping Heights

• SELECT HGTTYPE, Count()100
• FROM (SELECT
• CASE
• WHEN Height (I) BETWEEN 300 and 350 THEN
  "Low"
• WHEN Height (i) BETWEEN 351 AND 400 THEN
  "Medium"
• WHEN Height (i) BETWEEN 401 AND 451 THEN
  "High"
• WHEN Height (i) BETWEEN 451 AND 500 THEN
  "Very High"
• WHEN Height (i) BETWEEN 551 AND 600 THEN
  "Really High"
• WHEN Height (i) gt 600 THEN "Way High"
• END AS HGTTYPE
• FROM S)
• GROUP BY HGTTYPE

47
COMPUTE SLOPE AND ASPECT

• UPDATE U27elu 3
• SET Height (I) SlopeHeight(U27elu,X
  (I),Y (I),3)
• UPDATE U27elu 3
• SET Height (I) AspectHeight(U27elu,X
  (I),Y (I),3)

48
RECLASSIFY A SURFACE

• OPTIONS COORDSYS("u27elu" AS COMPONENT)
• UPDATE U27elu 3
• SET Height (I)
• IIf(Height (I) lt 300,0,
• IIf(Height (I) BETWEEN 300 AND 400,1,
• IIf(Height (I) BETWEEN 400 AND 500,2,3)))
• CREATE A COST DISTANCE SURFACE
• UPDATE S SET Height (I) (SELECT
  Min(Distance(
• AssignCoordSys(NewPoint(S.Center X (I),
  S.Center Y (I)), CoordSys("S" AS COMPONENT)),
• D.Geom (I)
• )) FROM D)

49
Spatial Selection on Surfaces

• OPTIONS COORDSYS("S" AS COMPONENT)
• SELECT S. FROM D,S
• WHERE touches(NewPointLatlon(S.Longitude (I),
  S.Latitude (I)),
• D.Geom (I))

50
TRANSFER HEIGHTS, COMPUTE VOLUMES, KRIGING

• UPDATE Field Points
• SET hgt HeightMax(U27elu,Field Points.Geom
  (I))
• UPDATE Land
• SET Color Volume(u27elu,Land.Geom (I))
• UPDATE land
• SET color KRIGING(Field points,Hgt,10,geom
  (i))

51
Geocoding
52
Selecting close to address

• SELECT
• FROM Cities and Towns
• WHERE ClosetoAddress(ID,"1440 East SAndy Acres
  Drive, Salisbury, MD",30,"mi") true
• PARAMETERS Enter Address text
• SELECT FROM Cities and Towns
• WHERE ClosetoAddress(ID,Enter Address,30,"mi")
  true
• PARAMETERS Enter Address text, Enter miles
  int
• SELECT FROM Cities and Towns
• WHERE ClosetoAddress(ID,Enter Address,Enter
  miles,"mi") true
• PARAMETERS Enter Address text, Enter miles
  int
• UPDATE Cities and Towns
• SET Selection (I) true
• WHERE ClosetoAddress(ID,Enter Address,Enter
  miles,"mi") true

53
Distance to Address

• PARAMETERS Enter Address text, Enter miles
  int
• SELECT Name,State,DistanceToAddress(id,Enter
  Address,"mi")
• FROM Cities and Towns
• WHERE State "MD"
• PARAMETERS Enter Address text, Enter miles
  int
• SELECT sum(POP_90),State
• FROM Cities and Towns
• WHERE DistanceToAddress(id,Enter Address,"mi")
  lt 300
• AND POP_98 gt 0
• GROUP BY State
• PARAMETERS Enter Address text, Enter miles
  int
• SELECT TOP 10 Name,State,DistanceToAddress(id,Ent
  er Address,"mi") dist
• FROM Cities and Towns
• WHERE State "MD"
• ORDER by dist ASC

54
Locate Address

• PARAMETERS Enter Address text, Enter miles
  int
• VALUES (LocateAddress(Enter Address))

55
SU Fun Day!

• PARAMETERS Enter Address text
• Values(LocateAddress(Enter Address))
• SELECT Wicomico_sheets_all Drawing.id,sheet_id
  from Wicomico_sheets_all Drawing,Query Data
• WHERE TOUCHES(Query Data.id,Wicomico_sheets_all
  Drawing.id)

56
SU Fun Day 1 Query

• PARAMETERS Enter Address text
• SELECT Wicomico_sheets_all Drawing.id,sheet_id
• FROM Wicomico_sheets_all Drawing
• WHERE TOUCHES((Values(LocateAddress(Enter
  Address))),Wicomico_sheets_all Drawing.id)
• MURPHYS LAW OF MAPPING
• PARAMETERS Enter Address text
• SELECT Wicomico_sheets_all Drawing.id,sheet_id
• FROM Wicomico_sheets_all Drawing
• WHERE DISTANCE((Values(LocateAddress(Enter
  Address))),Wicomico_sheets_all Drawing.id) lt
  100

57
Return Lat/Lon on Addresses

• PARAMETERS Enter Address text
• Values(LocateAddressLat(Enter Address),LocateAdd
  ressLon(Enter Address))

58
NEAR (lab319)

• SELECT Field Points.NAME intheme,
• land.NAME neartheme,
• distance(field points.ID,land.id) dist
• FROM tri92 drawing land,schools drawing
  Field Points
• ORDER BY dist
• SELECT intheme,first(neartheme),min(dist) dist
• FROM
• (SELECT D.NAME intheme,
• land.NAME neartheme,
• distance(D.ID,land.id) dist
• FROM schools drawing land,tri92 drawing D
• ORDER BY dist)
• GROUP BY intheme