Retrieving Data using SQL Structured Query Language

1
Retrieving Data using SQL (Structured Query
Language)
ITK 378, ITK 378.05 Database
ProcessingLecture Notes - Spring 2006
2
SQL

• SQL Structured Query Language
• The (ANSI) standard language for all relational
  databases
• Non-Procedural Language
• Tables are manipulated implicitly by specifying
  conditions that need to be satisfied by the data
  selected, rather than giving directions on how to
  find data

3
SQL Command Categories

• DML Data Manipulation Language
• Work with actual data
• Retrieve, change values, add, remove rows
• DDL Data Definition Language
• Define table structures, other objects
• Determines what are allowable data values
• DCL Data Control Language
• Grant or revoke privileges on tables to different
  users
• E.g., who can update a specific table, who can
  even retrieve rows from it

4
Sample Database for SQL Lectures

• ORDER (ORDERNO, ORDERDATE, ORDREP,
• ACCTNO, ORDAMT, DELVDATE, PAIDFLG)
• Note ORDREP may not have a value
• ORDERLINE (ORDERNO, PRODNO, ORDERQTY)
• ACCOUNT (ACCTNO, ACCTNAME, PHONE,
• BALANCE)
• PRODUCT (PRODNO, DESCRIP, PRICE, QTYHAND,
• VENDCODE)
• SALESREP (REPID, LNAME, FNAME, REP_PHONE,
• COMMRATE)

5
Foreign Keys in this Database

• ORDER table
• ORDREP references SALESREP
• ACCTNO references ACCOUNT
• ORDERLINE table
• ORDERNO references ORDER
• PRODNO references PRODUCT

6
Our Sample Database
7
Sample Data

• ORDER table data

ORDERNO ACCTNO ORDREP ORDAMT ORDERDATE
DELVDATE PAIDFLG ------- ------ ------
---------- ---------- ---------- ------- 6345
7777 FIJ 5396.00 11/15/2000 12/18/2000 Y
6346 7777 FIJ 2190.00 12/01/2000
12/15/2000 N 6347 8888 THM
2499.00 12/15/2000 01/03/2001 Y 6348
5555 FIJ 7600.00 12/28/2000 01/06/2001 N
6349 7777 THM 1600.00 01/05/2001 -
N 6350 7777 -
4230.00 01/10/2001 - N
8
ORDERLINE Table
ORDERNO PRODNO ORDERQTY ------- ------
-------- 6345 6028 5 6345
2853 4 6346 3408 3
6347 1794 1 6347 6028 1
6347 2853 1 6348 1794
3 6348 6028 3 6348 4916
1 6349 9317 4 6350
3408 1 6350 9317 5
6350 2946 1
9
ACCOUNT Table
ACCTNO ACCTNAME PHONE
BALANCE ------ -------------------- --------
---------- 2222 HANSEN INSURANCE
427-7000 750.00 8888 TRIMBLE MOTORS
429-6363 3460.00 7777 SANDRA DUNN,CPA
653-3200 75.00 5555 HOADLEY
TRUCKING 648-1470 0.00 3333
ROBERT FORREST 429-6789 435.00
10
PRODUCT Table
PRODNO VENDCODE PRICE QTYHAND DESCRIP
------ -------- ---------- -------
--------------- 2853 A3 99.00
50 SOUND KIT 9317 C2
400.00 30 WORKSTATION 2946 A2
1500.00 10 STILL VIDEO CAM 3408
A3 730.00 20 PERS LASER PRTR
4916 C2 400.00 25 OPTICAL
DISK DR 6028 A3 1000.00 50
17 INCH MONITOR 1794 C1 1400.00
60 486 DX/33 3276 C1
3500.00 25 LASER PRINTER
11
SALSREP Table
REP
REPID LNAME FNAME PHONE
COMMRATE ----- ------------ ------------
-------- -------- RAR RAMAKRISHMAN
RADESHYAM 631-1427 0.040 FIJ FISHER
JANE 631-1496 0.060 THM
THOMAS MICHAEL 631-1455 0.050
12
Retrievals in a Relational Database

• Result of a Query LOOKS LIKE a Table
• Rows and Columns
• Can be multiple rows, multiple columns
• Can be single row or single column
• Can even be no rows (none meet the conditions)
• Query is Non-Procedural
• Specifies WHAT we want (characteristics of the
  answer result rows)
• But not HOW to obtain it

13
Retrievals in a Relational Database

• Result based on data values that occur in rows
• NOT on physical placement of data within rows
• NOT on physical placement or ordering of rows
• Selection criteria (Where clause) can be based on
  ANY column
• NOT just special "Key" fields
• Just use the column name

14
Outline of a simple SELECT statement

• SELECT column-list
• FROM table-list
• WHERE criterion
• ORDER BY column-listASC/DESC
• The column-list included in the SELECT clause
  must include column names in a comma-delimited
  structure.

15

• Every table which is the originating source of
  the columns listed in either the SELECT clause or
  the WHERE clause must appear in the FROM clauses
  listing of tables.
• Every table which is the originating source of
  the columns listed in the WHERE clause must
  appear in the FROM clauses listing of tables.

16
Example Retrieval in SQL

• Show a few columns from the ORDER table
• SELECT ORDERNO, ACCTNO, ORDAMT
• FROM ORDER

ORDERNO ACCTNO ORDAMT ------- ------
---------- 6345 7777 5396.00
6346 7777 2190.00 6347 8888
2499.00 6348 5555 7600.00 6349
7777 1600.00 6350 7777
4230.00
17
Order of Columns

• Left-to-right order in result output comes from
  order the columns are listed in the SELECT
  statement
• (independent of order in table definition)
• SELECT ACCTNO, ORDREP, ORDERNO
• FROM ORDER

ACCTNO ORDREP ORDERNO ------ ------
------- 7777 FIJ 6345 7777 FIJ
6346 8888 THM 6347 5555 FIJ
6348 7777 THM 6349 7777 -
6350
18
Adding a WHERE clause

• The WHERE clause gives criterion/conditions to
  determine which rows to include in the result
• Test each row with the condition

Which columns
SELECT PRODNO, DESCRIP, PRICE FROM
PRODUCT WHERE PRICE gt 1200
Which rows
19
WHERE Clause

• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE gt 1200
• Result

PRODNO DESCRIP PRICE ------
--------------- ---------- 2946 STILL VIDEO
CAM 1500.00 1794 486 DX/33
1400.00 3276 LASER PRINTER 3500.00
20
Another Numeric Test

• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE lt 500.00

PRODNO DESCRIP PRICE ------
--------------- ---------- 2853 SOUND KIT
99.00 9317 WORKSTATION
400.00 4916 OPTICAL DISK DR 400.00
21
Equality Test

• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE 400.00

PRODNO DESCRIP PRICE ------
--------------- ---------- 9317 WORKSTATION
400.00 4916 OPTICAL DISK DR
400.00
22
Ranges using BETWEEN

• A range of values is easily and efficiently
  specified using BETWEEN
• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE BETWEEN 400 AND 1000

23
Result of BETWEEN
PRODNO DESCRIP PRICE ------
--------------- ---------- 9317 WORKSTATION
400.00 3408 PERS LASER PRTR
730.00 4916 OPTICAL DISK DR 400.00
6028 17 INCH MONITOR 1000.00

• Note The end values, 400 and 1000, are included

24
Excluding the end values of a range

• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE gt 400
• AND PRICE lt 1000
• Useful if you dont want the end values

PRODNO DESCRIP PRICE ------
--------------- ---------- 3408 PERS LASER
PRTR 730.00
25
The WHERE clause Character tests

• SELECT PRODNO, DESCRIP, PRICE
• FROM PRODUCT
• WHERE VENDCODE 'C2

PRODNO DESCRIP PRICE ------
--------------- ---------- 9317 WORKSTATION
400.00 4916 OPTICAL DISK DR
400.00
26
Another Character Test

• SELECT LNAME, REP_PHONE
• FROM SALESREP
• WHERE LNAME gt 'RAMAKRISHMAN'

REP LNAME PHONE
------------ -------- RAMAKRISHMAN 631-1427
THOMAS 631-1455
Note Character comparisons use standard
collating sequence (i.e., alphabetical
ordering)
27
BETWEEN and Character Tests(Be Careful)

• SELECT LNAME, REP_PHONE
• FROM SALESREP
• WHERE LNAME BETWEEN 'R' AND 'W

REP LNAME PHONE
------------ -------- RAMAKRISHMAN 631-1427
THOMAS 631-1455
Would WASHINGTON be included?
28
Compound Conditions AND

• We can combine multiple conditions to determine
  the resulting displayed rows.
• SELECT DESCRIP, PRICE
• FROM PRODUCT
• WHERE PRICE gt 500
• AND VENDCODE 'A3'
• (Note that the test column VENDCODE
• need not be in the SELECT clause)

29
Result of AND Combination

• The output

DESCRIP PRICE ---------------
---------- PERS LASER PRTR 730.00 17
INCH MONITOR 1000.00
30
Compound Conditions OR

• The OR is often used for multiple conditions on
  the same column
• SELECT DESCRIP, PRICE, VENDCODE
• FROM PRODUCT
• WHERE VENDCODE 'A3'
• OR VENDCODE 'C2

31
Result of OR Combination

• The output
• Note Use parentheses () when mixing AND and OR
  conditions

DESCRIP PRICE VENDCODE
--------------- ---------- -------- SOUND KIT
99.00 A3 WORKSTATION
400.00 C2 PERS LASER PRTR 730.00
A3 OPTICAL DISK DR 400.00 C2
17 INCH MONITOR 1000.00 A3
32
Negative comparisons

• List products from vendors other than A3 (ie,
  not equal A3)
• SELECT DESCRIP, PRICE, VENDCODE
• FROM PRODUCT
• WHERE VENDCODE 'A3

DESCRIP PRICE VENDCODE
--------------- ---------- --------
WORKSTATION 400.00 C2 STILL
VIDEO CAM 1500.00 A2 OPTICAL DISK DR
400.00 C2 486 DX/33
1400.00 C1 LASER PRINTER 3500.00
C1
33
More on Negative comparisons

• Valid alternatives . WHERE VENDCODE ltgt 'A3
• .... WHERE NOT VENDCODE 'A3
• The following are invalid forms
• .... WHERE VENDCODE NOT 'A3
• .... WHERE VENDCODE 'A3'

34
The IN Comparison

• The IN comparison helps when we are seeking any
  one of several values.
• Find products from either vendor A3 or C2
• SELECT DESCRIP, PRICE, VENDCODE
• FROM PRODUCT
• WHERE VENDCODE IN ('A3', 'C2')

DESCRIP PRICE VENDCODE
--------------- ---------- -------- SOUND KIT
99.00 A3 WORKSTATION
400.00 C2 PERS LASER PRTR 730.00
A3 OPTICAL DISK DR 400.00 C2
17 INCH MONITOR 1000.00 A3
35
The NOT IN Comparison

• Find products from any vendor other than A3 and
  C2
• SELECT DESCRIP, PRICE, VENDCODE
• FROM PRODUCT
• WHERE VENDCODE NOT IN ('A3','C2')

DESCRIP PRICE VENDCODE
--------------- ---------- -------- STILL
VIDEO CAM 1500.00 A2 486 DX/33
1400.00 C1 LASER PRINTER
3500.00 C1
36
IN and Not IN

• Can include 3, 4, or more values in lists
• Quotes needed if character data or dates, not if
  numeric data type
• IN is an alternative to a one or more OR
  conditions
• NOT IN is usually easier to understand than
  alternative, which is AND of several NOT
  conditions

37
The LIKE Comparison

• Find sales reps whose last name starts with "F"
• SELECT LNAME
• FROM SALESREP
• WHERE LNAME LIKE 'F'

LNAME ------------ FISHER
38
More on LIKE Comparison

• Find products whose description contains the word
  "LASER"
• SELECT PRODNO, DESCRIP
• FROM PRODUCT
• WHERE DESCRIP LIKE 'LASER

PRODNO DESCRIP ------
--------------- 3408 PERS LASER PRTR
3276 LASER PRINTER
39
The LIKE comparison

• is used to represent any character string
  (null or a string of characters of any length)
• _ (underscore) represents a single character
• e.g.,
• LIKE '_A' finds matches when
• 2nd character is an A

40
ORDER BY Clause

• Controls the sequence in which the result rows
  are presented

• SELECT ORDERNO, ORDAMT, ACCTNO
• FROM ORDER
• ORDER BY ORDAMT

ORDERNO ORDAMT ACCTNO -------
---------- ------ 6349 1600.00 7777
6346 2190.00 7777 6347
2499.00 8888 6350 4230.00 7777
6345 5396.00 7777 6348 7600.00
5555
41
ORDER BY

• Can also specify column to ORDER BY by its
  relative position within the SELECT clause
• ....ORDER BY 2 (2nd column on SELECT)
• We can cause the rows to be sequenced from
  highest value to lowest (default ASCENDING)
• .... ORDER BY 2 DESC
• .ORDER BY ORDAMT DESC

42
ORDER BY

• ORDER BY clause comes last in the SQL statement,
  after all WHERE clauses and any other
  clauses
• SELECT ORDERNO, ORDAMT, ACCTNO
• FROM ORDER
• WHERE ORDAMT gt 2400
• ORDER BY ORDAMT DESC

43
ORDER BY

• The output

ORDERNO ORDAMT ACCTNO -------
---------- ------ 6348 7600.00 5555
6345 5396.00 7777 6350 4230.00
7777 6347 2499.00 8888

• First, rows and columns are selected
• Then, order is applied to results

44
Ordering on Multiple Columns

• Can specify a second ordering column, to be
  applied to ties on the first ordering column
• SELECT ORDERNO, ORDAMT, ACCTNO
• FROM ORDER
• ORDER BY ACCTNO, ORDAMT DESC

• ORDAMT only used to sequence with orders with the
  same ACCTNO

45
Ordering on Multiple Columns

• The output

ORDERNO ORDAMT ACCTNO -------
---------- ------ 6348 7600.00 5555
6345 5396.00 7777 6350 4230.00
7777 6346 2190.00 7777 6349
1600.00 7777 6347 2499.00 8888
46
Ordering on Multiple Columns

• Note that DESC only applies to the immediately
  preceding column
• Multiple order by columns can also be specified
  using column relative position number
• .... ORDER BY 3, 2 DESC

47
Ordering on Multiple Columns

• The output

ORDERNO ORDAMT ACCTNO -------
---------- ------ 6348 7600.00 5555
6345 5396.00 7777 6350 4230.00
7777 6346 2190.00 7777 6349
1600.00 7777 6347 2499.00 8888
48
Repeated Answer Rows

• If we select all sales rep IDs from the ORDER
  table, we get repetitions
• SELECT ORDREP
• FROM ORDER

ORDREP ------ FIJ FIJ THM
FIJ THM -
49
DISTINCT

• If we use DISTINCT, result rows that are exact
  matches are not repeated
• SELECT DISTINCT ORDREP
• FROM ORDER

ORDREP ------ FIJ THM -

ORDREP ------ FIJ FIJ THM
FIJ THM -
instead of
50
DISTINCT

• DISTINCT suppresses only exact duplications of
  the entire result row, not just duplications of
  one column in result
• SELECT DISTINCT ORDREP, ACCTNO
• If two different original rows have same order
  rep AND same acctno, then result only lists this
  pair of values once
• SELECT ORDREP, ACCTNO
• If two original rows have same order rep and
  same acctno, then both instances are listed
  (duplicate answers but from 2 different original
  rows)

51
DISTINCT

• SELECT DISTINCT ORDREP, ACCTNO
• FROM ORDER

ORDREP ACCTNO ------ ------ FIJ
5555 FIJ 7777 THM 7777 THM
8888 - 7777
Of six original Order rows, only two have the
same ORDREP and ACCTNO values
52
More Features used in SQL Retrievals
53
Arithmetic Expressions

• Can show the result of calculations
• SELECT PRODNO, PRICE 1.05
• FROM PRODUCT

PRODNO ------ ------------- 2853
103.9500 9317 420.0000 2946
1575.0000 3408 766.5000 4916
420.0000 6028 1050.0000 1794
1470.0000 3276 3675.0000
54
Arithmetic Expressions

• SELECT PRODNO, PRICE 1.04,
• PRICE 1.06, PRICE 1.08
• FROM PRODUCT

PRODNO ------ ------------- -------------
------------- 2853 102.9600
104.9400 106.9200 9317 416.0000
424.0000 432.0000 2946
1560.0000 1590.0000 1620.0000 3408
759.2000 773.8000 788.4000
4916 416.0000 424.0000 432.0000
6028 1040.0000 1060.0000
1080.0000 1794 1456.0000 1484.0000
1512.0000 3276 3640.0000
3710.0000 3780.0000
55
Labeling Calculated Results

• Can introduce a column name for a calculated
  result
• SELECT PRODNO, PRICE QTYHAND VALUE
• FROM PRODUCT
• Notice its the missing comma after the
  expression that signals you are giving a column
  label for the expression rather than the name of
  another column to be displayed

56
Virtual Column Names

• The output

PRODNO VALUE ------ ------------
2853 4950.00 9317 12000.00 2946
15000.00 3408 14600.00 4916
10000.00 6028 50000.00 1794
84000.00 3276 87500.00
57
Where can Column Labels be used

• The virtual column name (like VALUE)
• CAN be used on the ORDER BY clause
• CAN NOT be used in the WHERE clause
• SELECT PRODNO, PRICE QTYHAND VALUE
• FROM PRODUCT
• WHERE PRICE QTYHAND gt 20000
• ORDER BY VALUE

58
String Functions

• Suppose we want to combine the last and first
  names of salesreps in a single expression, with a
  comma separating them

SALESREP
REP
REPID LNAME FNAME PHONE
COMMRATE ----- ------------ ------------
-------- -------- RAR RAMAKRISHMAN
RADESHYAM 631-1427 0.040 FIJ FISHER
JANE 631-1496 0.060 THM
THOMAS MICHAEL 631-1455 0.050
59
String Concatenation

• We can concatenate character strings into a
  single result column
• SELECT LNAME ',' FNAME
• FROM SALESREP

-------------------------
RAMAKRISHMAN,RADESHYAM FISHER ,JANE
THOMAS ,MICHAEL
60
Sample Concatenation

• If the first field is variable length, there are
  no extra trailing blanks before the next part of
  the result string
• SELECT FNAME LNAME
• FROM SALESREP

------------------------- RADESHYAM
RAMAKRISHMAN JANE FISHER
MICHAEL THOMAS
Note the concatenation symbol is 2 characters,
each a single vertical line
61
Scalar Functions

• SUBSTR is a scalar string function that can be
  used in queries
• SELECT PRODNO,
• SUBSTR(DESCRIP,1,8)
• FROM PRODUCT

PRODNO ------ -------- 2853 SOUND KI
9317 WORKSTAT 2946 STILL VI 3408 PERS
LAS 4916 OPTICAL 6028 17 INCH 1794
486 DX/3 3276 LASER PR
syntax SUBSTR (column, beginning position,
length)
62
Working with Dates in SQL Retrievals
63
Working with Dates

• Date constants need to be expressed with a
  4-digit year. You can use either a hyphen or a /
  to separate the parts.
• SELECT ORDERNO, ORDERDATE
• FROM ORDER
• WHERE ORDERDATE gt 2000-12-25'

ORDERNO ORDERDATE ------- ----------
6348 12/28/2000 6349 01/05/2001 6350
01/10/2001
yyyy-mm-dd format
Display is in system default (depends on DB2
install)
64
Working with Dates

• Date constants require 4-digit year spelled out
• SELECT ORDERNO, ORDERDATE
• FROM ORDER
• WHERE ORDERDATE lt '12/15/2000'

ORDERNO ORDERDATE ------- ----------
6345 11/15/2000 6346 12/01/2000 6347
12/15/2000
65
Date Functions

• MONTH, DAY and YEAR functions can be used to
  extract the sub-parts of a date field
• SELECT ORDERNO,
• MONTH (ORDERDATE) MONTH,
• DAY (ORDERDATE) DAY,
• YEAR (ORDERDATE) YEAR
• FROM ORDER

66
Date Functions

• The output

ORDERNO MONTH DAY YEAR
------- ----------- ----------- -----------
6345 11 15 2000
6346 12 1 2000
6347 12 15 2000
6348 12 28 2000
6349 1 5 2001
6350 1 10 2001

67
Date Functions

• Date functions are often used with conditions on
  queries. For example,
• SELECT ORDERNO, ORDERDATE
• FROM ORDER
• WHERE YEAR (ORDERDATE) 2000

ORDERNO ORDERDATE -------
--------------- 6345 11/15/2000
6346 12/01/2000 6347 12/15/2000
6348 12/28/2000
68
Converting Date Formats

• Can convert a date into several character formats
• Result can then be treated as a character field
  for further manipulations, if desired
• SELECT ORDERNO, DELVDATE,
• CHAR (DELVDATE,ISO) ISO,
• CHAR (DELVDATE,USA) USA
• FROM ORDER

69
Converting Date Formats

• The output

ORDERNO DELVDATE ISO USA -------
---------- ---------- ---------- 6345
12/18/2000 2000-12-18 12/18/2000 6346
12/15/2000 2000-12-15 12/15/2000 6347
01/03/2001 2001-01-03 01/03/2001 6348
01/06/2001 2001-01-06 01/06/2001 6349 -
- - 6350 -
- -
70
Date Arithmetic

• Subtracting two date fields directly yieldsa
  date duration value which has the format
• yyyymmdd
• where yyyy is the year
• mm is the month
• dd is the day of the month
• For example, 103 as such a difference means 1
  months and 3 days, not 103 days

71
Date Arithmetic

• SELECT ORDERNO, ORDERDATE, DELVDATE,
• DELVDATE - ORDERDATE
• FROM ORDER
• WHERE DELVDATE IS NOT NULL

ORDERNO ORDERDATE DELVDATE -------
---------- ---------- ----------- 6345
11/15/2000 12/18/2000 103 6346
12/01/2000 12/15/2000 14 6347
12/15/2000 01/03/2001 19 6348
12/28/2000 01/06/2001 9
72
Finding Elapsed Days

• DAYS function
• converts dates into a number representing the
  number of days since a fixed base date
• SELECT ORDERNO, ORDERDATE, DELVDATE,
• DAYS(DELVDATE) - DAYS(ORDERDATE)
• FROM ORDER
• WHERE DELVDATE IS NOT NULL
• Subtracting two such conversions yields the
  actual difference in number of days between the
  two dates

73
Finding Elapsed Days

• The output

ORDERNO ORDERDATE DELVDATE -------
---------- ---------- ----------- 6345
11/15/2000 12/18/2000 33 6346
12/01/2000 12/15/2000 14 6347
12/15/2000 01/03/2001 19 6348
12/28/2000 01/06/2001 9
74
Finding Elapsed Days

• For all orders which have not been delivered, how
  many days has it been since they were ordered?
• (CURRENT DATE is a system variable that has
  todays date and can be used in queries)
• SELECT ORDERNO, ORDERDATE,
• DAYS(CURRENT DATE) DAYS(ORDERDATE)
• FROM ORDER

75
Labeled Durations

• Can calculate adjusted dates
• Using the DAY, MONTH, and YEAR labeled durations
• SELECT ORDERNO, ORDERDATE,
• ORDERDATE 15 DAYS,
• ORDERDATE 2 MONTHS - 10 DAYS,
• ORDERDATE 1 YEAR
• FROM ORDER

76
Labeled Durations

• The output

ORDERNO ORDERDATE ------- ----------
---------- ---------- ---------- 6345
11/15/2000 11/30/2000 01/05/2001 11/15/2001
6346 12/01/2000 12/16/2000 01/22/2001
12/01/2001 6347 12/15/2000 12/30/2000
02/05/2001 12/15/2001 6348 12/28/2000
01/12/2001 02/18/2001 12/28/2001