NSAA IT Conference

basesprocketData Management

Oct 31, 2013 (3 years and 7 months ago)

90 views

SQL Scripting

http://www.apa.virginia.gov

NSAA IT Conference

SQL Scripting

_____________________________________

October 2, 2013

Goran Gustavsson

Audit Director

Virginia Auditor of Public Accounts


Page
1

SQL Scripting


Page
2

http://www.apa.virginia.gov

What is SQL


S
tructured
Q
uery
L
anguage


A “Domain
-
specific language” to query
databases


Other Examples:


HTML (to build web pages)


Mathematica (symbolic mathematics)


Tiny programming language



SQL Scripting

What is SQL


Relational Databases


Oracle Database (70%)


Microsoft SQL Server (68%)


MySQL (50%)


IBM DB2 (39%)


IBM Informix (18%)



http://www.apa.virginia.gov

Source: Gartner Group “Database Installations and Deployment Plans


2008”


Page
3

SQL Scripting

SQL Syntax


Clauses


Expressions


Predicates


Queries


Statements

http://www.apa.virginia.gov


Page
4

SQL Scripting

SQL Syntax

SELECT
[what you want to see]

FROM
[what table(s) has/have the data]

WHERE
[limit and connect (make
relational) the data retrieved]
;

http://www.apa.virginia.gov


Page
5

SQL Scripting

SQL Syntax

I want to see all flight numbers departing
Dulles International Airport.

http://www.apa.virginia.gov


Page
6

SQL Scripting

SQL Syntax

I want to see all flight numbers departing
Dulles International Airport.

http://www.apa.virginia.gov

SELECT
flight_number

FROM flight

WHERE
origin_iata

= ‘IAD’ ;


Page
7

IATA = International Air Transport Association

IAD = Washington Dulles International Airport

SQL Scripting

SQL Syntax

I want to see all flight numbers departing
Dulles International Airport.

http://www.apa.virginia.gov

SELECT
flight_number

FROM flight

WHERE
origin_iata

= ‘IAD’ ;

SELECT clause

FROM clause

WHERE clause

Expression

Predicate

Statement


Page
8

SQL Scripting

SQL Syntax
-

Operators

http://www.apa.virginia.gov

Operator

Description

Example

=

Equal

to

Flight_number

= ‘1180’

<> or

!=

Not equal to

Origin <> ‘RIC’
=
>
=
G牥ate爠than
=
aep_date
=
> ’30
-
JUN
-
13’

<

Less than

Landing_fee

<

1500

>=

Greater than or equal

Dep_date

>= ‘30
-
JUN
-
13’

<=

Less than or equal

Landing_fee

<= 1500

BETWEEN

Between an inclusive range

Price

BETWEEN 400.00 AND 500.00

LIKE

Match a character pattern

Last_Name

LIKE ‘Gus%’

IN

Equal to one or

more values

Origin

IN (‘RIC’, ‘MCO’)

IS or IS NOT

Compare to null (missing data)

Origin IS NOT NULL


Page
9

SQL Scripting

Relational Databases


Tables


Views


Primary / Foreign Keys


Relationships

http://www.apa.virginia.gov


Page
10

SQL Scripting

Relational Databases
-

Tables

http://www.apa.virginia.gov

FLIGHT_NUMBER

ORIGIN_IATA

DESTINATION_IATA

SK0926

IAD

CPH

SK0904

EWR

ARN

SK0946

ORD

ARN

Westbound

Odd numbers

Eastbound

Even numbers

Flight table


Page
11

SQL Scripting

Relational Databases
-

Views

http://www.apa.virginia.gov

FLIGHT_NUMBER

ORIGIN_IATA

SK0904

EWR

SK0946

ORD

Dest_ARN View

SELECT
flight_number
,
origin_iata

FROM flight

WHERE
destination_iata

= ‘ARN’

Creating View

SELECT *

FROM
dest_ARN_vw
;

Querying View


Page
12

SQL Scripting

Relational Databases


Primary/Foreign Keys

http://www.apa.virginia.gov

FLIGHT_NUMBER

ORIGIN_IATA

DESTINATION_IATA

MIN_CABIN_CREW

SK0926

IAD

CPH

10

SK0904

EWR

ARN

10

SK0946

ORD

ARN

12

Primary

Foreign

Foreign

AIRPORT_IATA

AIRPORT_NAME

IAD

Washington Dulles International Airport

ORD

O’Hare
=
fnte牮ational=Ai牰o牴
=
AoN
=
却o捫colm
=
A牬anda
=
Ai牰o牴
=
Primary


Page
13

SQL Scripting

Relational Databases


Primary/Foreign Keys

http://www.apa.virginia.gov

FLIGHT

FLIGHT_NUMBER

Primary

ORIGIN_IATA

Foreign

DESTINATION_IATA

Foreign

MIN_CABIN_CREW

AIRPORT

AIRPORT_IATA

Primary

AIRPORT_NAME


Page
14

SQL Scripting

Relational Databases


Relationships

http://www.apa.virginia.gov

FLIGHT

FLIGHT_NUMBER

Primary

ORIGIN_IATA

Foreign

DESTINATION_IATA

Foreign

MIN_CABIN_CREW

AIRPORT

AIRPORT_IATA

Primary

AIRPORT_NAME

1:1


One to One

1:M


One to Many

M:M


Many to Many

ERD

Entity
-
Relationship Diagram


Page
15

SQL Scripting


Page
16

http://www.apa.virginia.gov

AIRPORT

AIRPORT_IATA

P

AIRPORT_NAME

RESERVATION

FLIGHT_NUMBER

P / FK

ORIGIN_IATA

P / FK

DESTINATION_IATA

P / FK

DEPARTURE_DATE

P / FK

LAST_NAME

P

FIRST_NAME

P

SEAT

PRICE

CABIN_CLASS_ID

FK

FLIGHT

FLIGHT_NUMBER

P

ORIGIN_IATA

P / FK

DESTINATION_IATA

P / FK

DEPARTURE_DATE

P

MAIN_CABIN_CREW

ARRIVAL_DATE

CABIN_CLASS

CABIN_CLASS_ID

P

CABIN_CLASS_NAME

AIRLINE

AIRLINE_IATA

P

AIRLINE_NAME

SQL Scripting

Example #1

Show me all flights that require less than
or equal to 5 cabin crew members.

http://www.apa.virginia.gov

SELECT
flight_number
,
origin_iata
,
destination_iata
,
min_cabin_crew

FROM flight

WHERE
min_cabin_crew

<= 5;

FLIGHT_NUMBER

ORIGIN_IATA

DESTINATION_IATA

MIN_CABIN_CREW

SK0535

ARN

DUB

3

SK0573

ARN

CDG

5

SK1407

ARN

CPH

2


Page
17

SQL Scripting

Example #1

http://www.apa.virginia.gov

FLIGHT_NUMBER

ORIGIN_IATA

DESTINATION_IATA

MIN_CABIN_CREW

SK3655

Stockholm

Arlanda

Airport

Dublin

International

Airport

3

SK0573

Stockholm
Arlanda

Airport

Charles de

Gaulle
Airport

5

SK1415

Stockholm

Arlanda

Airport

Copenhagen

Airport

2


Page
18

SQL Scripting

Example #1


Let’s JOIN tables

http://www.apa.virginia.gov

SELECT

f.flight_number
,



a1.airport_name AS “ORIGIN”,



a2.airport_name AS “DESTINATION”,



f.min_cabin_crew

FROM

flight f, airport a1, airport a2

WHERE

min_cabin_crew

<= 5 AND



f.origin_iata

= a1.airport_iata AND



f.destination_iata

= a2.airport_iata;




Page
19

SQL Scripting

Example #2

How many flights require 5 cabin crew
members?

http://www.apa.virginia.gov

SELECT
flight_number
,
origin_iata
,
destination_iata
,
min_cabin_crew

FROM flight

WHERE
min_cabin_crew

= 5;

FLIGHT_NUMBER

ORIGIN_IATA

DESTINATION_IATA

MIN_CABIN_CREW

DY5213

ARN

LPA

5

DY5216

LPA

ARN

5

SK0571

ARN

CDH

5


Page
20

SQL Scripting

Example #2
-

COUNT

How many flights require 5 cabin crew
members?

http://www.apa.virginia.gov

SELECT count(
flight_number
) AS “COUNT 5 CREW”

FROM flight

WHERE
min_cabin_crew

= 5;

COUNT

5 CREW

35


Page
21

SQL Scripting

Example #2


COUNT & GROUP BY

How many flights require 5 cabin crew
members by originating airport?

http://www.apa.virginia.gov

SELECT
origin_iata
, count(
flight_number
) AS “Flights”

FROM flight

WHERE
min_cabin_crew

= 5

GROUP BY
origin_iata
;

ORIGIN

Flights

ARN

32

CDG

2

LPA

1


Page
22

SQL Scripting

Functions


Numeric functions


Character to character functions


Character to number functions


Datetime functions


Conversion functions


Aggregate functions


http://www.apa.virginia.gov


Page
23

SQL Scripting

Functions
-

Numeric


ABS(n)


ROUND(n, integer)


TRUNC(n1, n2)


http://www.apa.virginia.gov

Source: Oracle® Database SQL Reference 10g Release 2 (10.2)


Page
24

SELECT ROUND(price, 0) AS “ROUNDED PRICE”

FROM reservation;

SQL Scripting

Functions


Char to Char


CONCAT(char1, char2)


INITCAP(char)


LOWER(char)


LTRIM(char, set)


RTRIM(char, set)


SUBSTR(char,position,substring_length)


TRIM(trim_character FROM trim_source)


UPPER(char)


http://www.apa.virginia.gov


Page
25

Source: Oracle® Database SQL Reference 10g Release 2 (10.2)

SQL Scripting

Functions


Char to Char


SUBSTR(
char,position,substring_length
)




UPPER(char)


http://www.apa.virginia.gov


Page
26

Source: Oracle® Database SQL Reference 10g Release 2 (10.2)

SELECT SUBSTR(
flight_number
, 1, 2) AS “AIRLINE”

FROM flight;

SELECT UPPER(
airport_name
)

FROM airport;

SQL Scripting

Functions


Char to Number


LENGTH(char)


http://www.apa.virginia.gov


Page
27

Source: Oracle® Database SQL Reference 10g Release 2 (10.2)

SELECT
airport_name
, length(
airport_name
)

FROM airport;

SQL Scripting

Functions


Datetime


ADD_MONTHS(
date,integer
)


CURRENT_DATE
{session
timezone
}


MONTHS_BETWEEN(date1, date2)


SYSDATE
{O/S date}


TO_CHAR(
datetime
, format)


http://www.apa.virginia.gov


Page
28

Source: Oracle® Database SQL Reference 10g Release 2 (10.2)

SELECT
to_char
(
departure_date
, ‘MONTH’) AS “DEP MONTH”

FROM flight;

SQL Scripting

Functions


Conversion


TO_CHAR(n, format)


TO_DATE(char, format)


TO_NUMBER(expr, format)



http://www.apa.virginia.gov


Page
29

Source: Oracle® Database SQL Reference 10g Release 2 (10.2)

SELECT
to_char
(price, ‘$9,999.99’) AS “CURRENCY”

FROM reservation;

SQL Scripting

Functions


Aggregate


AVG(expr)


COUNT(expr)


MAX(expr)


MIN(expr)


SUM(expr)




http://www.apa.virginia.gov


Page
30

Source: Oracle® Database SQL Reference 10g Release 2 (10.2)

SELECT SUM(price) AS “REVENUE”

FROM reservation;

SQL Scripting

On
-
Line Reference


Oracle® Database SQL Reference 10g
Release 2


http://docs.oracle.com/cd/B19306_01/server.102/b14200/index.htm

http://www.apa.virginia.gov


Page
31

SQL Scripting

SQL Summary

http://www.apa.virginia.gov

SQL CLAUSE

SELECT

FROM

WHERE

GROUP

BY

ORDER BY

OPERATORS

=

<>

or !=

>

<

>=

<=

BETWEEN

LIKE

IN

IS or IS NOT

FUNCTIONS

COUNT()

SUM()

AVG()

MIN()

MAX()

NVL()

UPPER()

LOWER()

SUBSTR()

TRIM()


Page
32

SQL Scripting

Let’s build a query


Page
33

http://www.apa.virginia.gov

SELECT
airl.airline_name
, (SELECT COUNT(*)


FROM flight
f_inner


WHERE SUBSTR(f_inner.flight_number,1,2) =
airl.airline_iata

AND


f_inner.origin_iata

= ‘IAD’ AND


upper(
to_char
(
f_inner.departure_date
, ‘YYYY’)) = ‘2013’) AS “IAD 2013”,


(SELECT COUNT(*)


FROM flight
f_inner


WHERE SUBSTR(f_inner.flight_number,1,2) =
airl.airline_iata

AND


f_inner.origin_iata

= ‘IAD’ AND


upper(
to_char
(
f_inner.departure_date
, ‘YYYY’)) = ‘2014’) AS “IAD 2014”

FROM airline
airl

ORDER BY
airl.airline_name
;

SQL Scripting

Let’s build a query


Page
34

http://www.apa.virginia.gov

SELECT
r.last_name

as “LAST NAME”,


r.first_name

as “FIRST NAME”,


to_char
(
r.departure_date
, ‘Month DD, YYYY HH24:MM’) AS “DEP DATE and TIME”,


cc.cabin_class_name

AS “CABIN”,


f.origin_iata

AS “ORIGIN IATA”,


airp1.airport_name AS “FROM”,


f.destination_iata

AS “DESTINATION IATA”,


airp2.airport_name AS “TO”,


to_char
(
r.price
, ‘$9,999.99’) AS “PRICE”

FROM reservation r, flight f, airport airp1, airport airp2,
cabin_class

CC

WHERE
r.cabin_class_id

=
cc.cabin_class_id

AND
r.flight_number

=
f.flight_number

AND


r.origin_iata

=
f.origin_iata

AND
r.destination_iata

=
f.destination_iata

AND


r.departure_date

=
f.departure_date

AND
r.origin_iata

= airp1.airport_iata AND


r.destination_iata

= airp2.airport_iata AND
r.last_name

= ‘
Gustavsson


ORDER BY “DEP DATE and TIME”;

SQL Scripting


One more thing…


Page
35

http://www.apa.virginia.gov

SQL Scripting

Types of Databases


On
-
Line Transaction Database (OLTP)


Normalized


Real
-
Time data


Production Banner, PeopleSoft, etc.


Data Warehouse


De
-
normalized


Historical data


Copies of data in Banner, PeopleSoft etc.


Page
36

http://www.apa.virginia.gov

SQL Scripting

http://www.apa.virginia.gov




SQL>SELECT * FROM users WHERE clue > 0;

0 rows returned

SQL>_






Page
37

SQL Scripting

Questions?



Goran Gustavsson

Director


Information Systems Security and Office Technology

Virginia Auditor of Public Accounts

(804) 225
-
3350 x306

goran.gustavsson@apa.virginia.gov


Page
38

http://www.apa.virginia.gov