Transcript Basic SQL

Basic SQL
SMSU
Computer Services
Short Course
1
Contact Information
• Greg Snider – MIS Database Analyst
• Ext. 6-4397
• Email – SGS345B
Don’t hesitate to email or call if you have
any questions after you start using QM
and SQL.
2
Confidentiality
• “The confidentiality of student information is
protected by the Family Education Rights
and Privacy Act (FERPA). Do not release
confidential information obtained through
QM reports to anyone except SMSU faculty
or staff who have a need for the information
and be sure to properly dispose of reports
when you no longer have need for them.”
Contents
• Concepts
– What is SQL?
– Terminology
• SQL Language
– Its parts
• SELECT Statement
• Query Manager
4
What is SQL?
•
•
•
•
•
S(trucured) Q(uery) L(anguage)
A standard language for accessing data
Designed to be portable
Used by most database vendors
It’s how you access data stored in a …
5
Terminology
• Table
– A set of rows with columns containing data
– Think of a table as a large spreadsheet
SOC_SEC
NAME
STU_ CLASS
GPA
HRS
495867475
Joe Blow
JR
3.1
110
547574395
Sue Smith
SR
2.75
120
647567364
Jane Doe
FR
0.0
0
775847587
Joe Cool
SO
2.25
75
6
Terminology
Hot and Cold Running Tables
• Cold or Query Table is refreshed nightly or
as predetermined.
• Hot or Live Table can be updated as you
write and run your queries.
• At this point, all the tables you use in your
queries are cold tables.
Terminology
• View
– Another way of accessing the data in a table
– May or may not contain all the columns in a
table
– Can be a join of two or more tables
– Transparent to the user
8
SQL Language
•
•
•
•
•
•
Language elements
Statements
Functions
Joins
Unions
Subselects
9
Language Elements
•
•
•
•
Data types
Constants
Expressions
Predicates
10
Data Types
• Character strings
• Datetime
– Date
– Time
• Numeric
– Integer
– Decimal
– Numeric
11
Character Strings
• Fixed length
– 1 – 254 positions
12
Date
• Date
– Format MM/DD/YYYY
– 10 positions
• When using a date, it must be enclosed in
single quotes, ’09/16/2004’
13
Numeric
• Integer
– Small
• 32768 - +32767
– Large
• -2147483648 - +2147483647
– Decimal
•
•
•
•
15 digits max
(Precision, Scale)
Precision – how many digits total
Scale – how many digits to the right of the decimal point
– Numeric
• 31 digits max
14
Constants
• Integer constants
– 456, -789
• Decimal constants
– 978.34, 9584.2746
• String (character) constants
– ‘ABCE’, ‘Computers for Learning’
15
Expressions
• Operators
– || or CONCAT, /, *, +, – || only for strings
– Standard rules apply for arithmetic operations
16
Date Arithmetic
• Admit_date + 2 months + 2 days is valid
• Grad_date – Admit_date is valid
17
Predicates
•
•
•
•
=, <>, <, >, <=, >=
Between: expression (NOT) BETWEEN 123 and 999
Null:
expression IS (NOT) NULL
Like:
expression (NOT) LIKE pattern
– Pattern % represents 0 or more characters
_ represents only 1 character
• Exists
discussed when we talk about subselects
• In
expression IN (value1, value2, value3, …)
• AND and OR may be used
18
Statements
• Select
19
Select
•
•
•
•
•
•
Select clause
From clause
Where clause
Group by clause
Having clause
Order by clause
20
Select Clause
• SELECT columns, expressions
• Tells what you want to see
21
From Clause
• FROM datacoll.view-name
• Datacoll is the owner of all our views
22
Where Clause
• WHERE search-condition
23
Group By Clause
• GROUP BY column1, column2, …
24
Having Clause
• HAVING search-condition
• Each column used in the search must:
– Unambiguously identify a grouping column or
– Be specified with a column function
25
Examples
• Example 1: Show all rows and columns of
the table datacoll.classes
• Example 2: Show the job code, maximum
salary and minimum salary for each group
of rows in EMP with the same job code,
but only for groups with more than 1 row
and with a maximum salary greater than
50000
26
Examples
• Example 1:
– SELECT * FROM DATACOLL.CLASSES
• Example 2:
– SELECT JOB, MAX(SALARY), MIN(SALARY)
FROM EMP GROUP BY JOB HAVING
COUNT(*) > 1 AND MAX(SALARY) > 500000
27
How would you use SQL in your
job?
28
Column Functions
•
•
•
•
•
•
AVG
COUNT
MAX
MIN
SUM
On all column functions, you can use DISTINCT
to remove duplicates
• On COUNT, DISTINCT also removes null values
29
Scalar Functions 1
• CHAR(expression) or CHAR(expression,USA)
– The first form returns the character representation of
a number
– The second returns the character represention of a
date or time
• DATE(expression)
– If the expression is a number <= 3652059, the result
is the date that is n-1 days after 01/01/0001
– If the expression is a character string with length 7 in
yyyyddd format, the result is the date represented by
the string
– If the expression is any other character string, it must
be in valid date format (’01/01/2005’)
30
Scalar Functions 2
• DAY(expression)
– if the expression is a date, the result is the
day part of the value
• DAYS(expression)
– The expression must be a date or a valid
string of a date
– The result is 1 more that the number of days
from 01/01/0001 to the expression
31
Scalar Functions 3
• DECIMAL(expression, integer, integer)
– 2nd and 3rd arguments are for precision and
scale
– If 3rd is omitted, default is 0
– If 2nd is omitted, precision is 15
32
Scalar Functions 4
•
•
•
•
•
HOUR(expression)
INTEGER(expression)
MINUTE(expression)
MONTH(expression)
SECOND(expression
33
Scalar Functions 5
• SUBSTR(string,start,length)
– Lentgh may be omitted. If it is, the default is
the length of the string – start + 1
• TIME(expression)
34
Joins 1
• Joins are the combined data from 2 or more
tables
• Specify more than 1 table in the FROM clause,
seperated by a comma
• Specify a search condition for the join in the
WHERE clause; otherwise, you get all possible
combinations of rows for the tables in the FROM
clause
• In this case, the number of rows return is the
product of the number of rows in each table
35
Joins 2 -- Intersections or
Differences
• Intersections
•
Difference
36
Joins 3 - Intersection
• Identify the juniors who have a foreign
language major and the classes they are
taking this fall
Classes Table
Classes
Student
Table
37
Joins 4 -Intersection Example
• Select Name, Course_Code, Course_No,
Section_No, Credit_Hours
• From Tstudent S, Tclasses C
• Where First_major_curr like ‘FL%’
– and Sem = ‘4’
– and Year = ‘93’
– and S.Soc_Sec = C.Soc_Sec
• Correlation names
– Defined in the FROM clause
– Used to designate table names
38
Joins 4
• Example:
– Select name, stu_class, crs_cd, crs_num
from datacoll.students s, datacoll.classes c
where s.soc_sec = c.soc_sec
39
Unions 1
Merging results from 2 or more queries
 Identify Sr and JR in FL

Student Table
Classes Table
40
Union 2 - Example
• Select Name, Adviser_Curr, Course_code,
Comb_Grade_Pts, Course_no,
Section_no, ’1’
• From Tstudent S, Tclasses C
• Where S.Soc_Sec = C.Soc_Sec and Sem
= ‘4’ and Year = ‘93’ and First_major_curr
like ‘FL%’ and class_curr = ‘SR’
• Union
41
Union 3 - Example cont
• Select Name, Adviser_curr, Course_code,
Comb_Grade_Pts, Course_no,
Section_no, ‘2’
• From Tstudent S, Tclasses C
• Where S. Soc_sec = C.Soc_sec and Sem
= ‘4’ and Year = ‘93’ and Class_curr = ‘JR’
and First_major_curr like ‘FL%’
• Order by 7,1
42
Union 4 - Example 2
• List the names of all students who are
either advised by advisor E333 or are
juniors.
Advised by
E333
Final Report
Juniors
43
Union and Union All - 5
• Union All--In the previous example, if
students were both Juniors and advised by
E333, they would be on the final report two
times.
• Union -- Sorts and removes duplicates
• Union All -- does not eliminate duplicate
rows from the report
44
Unions 6 - Rules
• Select -- any number of columns can be
selected
• Each Select must produce similar results
– same number of columns
– by position, same general type, ie...
– Char--char--dec
Dec--char-char --NO
– Char--char--dec
Char--char--dec -Yes
45
Unions 6 - Rules continued
• You may use any combination of Union
and Union All
• Efficient to use union all on all but the last
UNION statement (Sort only once)
• ORDER BY statement must follow all
SELECTs and reference only column
positions, not names
46
Combining Union and Union All
Query 1
Union All
Query 2
Union All
Query 3
Union
Query 4
I
n
t
e
r
n
a
l
FinalReport
Report
Final
S
o
r
t
a
r
e
a
47
Unions 1
• SELECT stmt UNION (ALL) SELECT stmt
UNION (ALL) …
• UNION without the ALL option causes duplicate
rows to be eliminated
• UNION ALL causes all rows from all SELECT
stmts to be returned
• Same number of columns must be returned by
all SELECT stmts
• The corresponding columns in all SELECT stmts
must have the same compatable data types
48
Subselects
select empno, actno, emstdate, emendate
from empprojact
where empno in (select empno from emp where workdept = ‘E11’
select workdept, max(salary)
from emp
group by workdept
having max(salary) > (select avg(salary) from emp
select workdept, max(salary)
grom emp q
group by workdept
having max(salary) < (select avg(salary) from emp
where not workdept = q.workdept)
49
Lunch Break
• Class resumes at 1:00
50
Getting to Query Manager
From the SMSU
Main Menu,
select option 20
51
Getting to Query Manager
From the SMSU
Query Menu,
select option 5
52
Writing, Running, Modifying
Queries
Choose option 1 to work
with queries.
Queries can be written in
either prompted or native
SQL. For both there are
many prompts available.
From the next screen
queries can be created,
modified, executed or
deleted.
53
Working with Queries
When you enter the
work with query
manager screen, the
library will be on the top
line and query creation
mode on the second.
Library can be changed
by overtyping the
value. Rarely, if ever,
will you use this
function.
Query creation mode
can be changed by
pressing F19 (shift-F7)
54
Example 1
• Find all current students in a specific
department having a GPA over 3.0 whose
age is over 30
55
Creating a New Query
Type 1 under Opt
column
Type name under
“Query” column.
Press Enter
56
Developing a Prompted Query
Select the
functions desired
by placing a 1 in
the Opt column.
All queries will
need to specify
the desired
tables and
columns.
57
Select Table
Collection =
Library
University
tables are
stored in
DATACOLL
Type “+” on line for second
table
Press F4 to
list tables or
type in
desired table
name.
58
Select Table
Type 1 beside desired
table and press enter.
59
Select Next Criteria
All desired options
can be picked
individually or at
one time.
60
Define Expression
For example: Age =
Year(current date –
Birth_date)
Pressing F11
displays the
column’s data type
and length.
Pressing F11 again
displays the
description of the
column.
61
Select and Sequence Columns
Select columns
Press enter
Change order
and/or pick
additional ones
or delete ones
Pressing F11
displays the
column’s data
type and length.
Pressing F11
again displays
the description
of the column.
62
Verify Selection and Order
After pressing enter,
QM puts the selected
columns in order at
the top of the list with
the sequence
numbers as multiples
of 10. This allows you
to easily add columns
between others by
selecting a number
between the other
numbers.
63
Select Rows
Each line can be
prompted by
pressing F4 to get
a list of available
values, columns.
F4 gives ability
to specify
multiple values
64
Select a Row Comparison for
Change
Place the cursor on any
line of the previous
comparison tests to
change that test and
press enter.
65
Change a Row Comparison
The selected test is
copied to the top and is
available for change.
66
Columns to Sort On
This screen
allows you to
specify the
order of the
output. The
selected
columns and
expressions
can be
ordered and
ascending/
descending
specified.
67
Select Summary Functions
This screen allows
you to specify
summary functions
for any columns in
the selected tables.
Any selections made
here will be added to
the columns
previously selected.
68
Keep Duplicate Row
One row is a
duplicate of another
row if ALL column
values in that row
are equal to the
corresponding
column values in
another row.
Normally you want
to know about the
duplicates.
69
Select Form
Here you can type
in the name of the
form that the
query will use.
The form can be
the same name
as the query, but
like the query
name, can only be
10 characters
long.
70
Build Form
These are the
options you have
in building your
form.
Enter a 1 in the
Opt column for
Edit column
formatting.
71
Edit Column Format
Press F11 to change
the Headings. This
allows you to
change all of the
headings at once.
Use *NONE to
suppress a heading
for a column. Press
F11 again to return
to this screen.
Putting the cursor in
the Usage and Edit
columns and
pressing F4 lists
valid values.
72
Page Heading
73
Page Footing
74
Final Text
This is text that
is placed at the
very end of the
report.
75
Break Text
If a Break usage
was defined for a
column, this is
where you would
enter any text you
wanted to appear
either before or
after the break
column value
changed.
76
Report Format Options
77
Function Keys
• F13 to Edit query
• F5 Run Report
• F18 Display SQL
78
Save Query and Form
You can change the
names you
assigned to the
query and form
before they are
saved.
Since the names
can only be 10
positions, a
description is vital
to help you know
what the query and
form are for when a
list of queries or
forms is displayed.
79
Adds Query to list
Option 9 to Run.
Option 2 to
Modify/Change
Option 3 to Copy
to a new name.
Option 7 to
Rename
Option 10 to
Convert to SQL
80
Converted Query
If you plan to use
variables in your
query, you must
convert it to SQL
and then add the
variables.
Prompted query
under QM does
not allow
variables.
81
Create Native Query
• Shift + F7 to
change
Query
Creation
Mode from
Prompted to
SQL
82
Blank Screen
This screen is just
like the query screen
in QMF. You just key
in the select
statement as you did
before.
If you’re not sure of
the table or column
names, key in
SELECT and then
press F4.
If you know the table
name, but not the
columns, place the
cursor on the QM
line, key in DRAW
DATACOLL.table and
press enter.
83
More Prompting Possible
The cursor is
automatically
placed on the
FROM tables line.
Press F4 to bring
up a list of tables.
84
Choose Library/Collection
DATACOLL is
where the
production data
is stored.
85
Select Table/File
You will only see
the tables that you
have authority to
use.
More that 1 table
can be selected if
you want to do a
join.
86
Or Type
If you know what
table you want,
simply enter it on
the FROM tables
line.
87
Choose Columns From List
This screen
functions just
like the similar
screen under
Prompted
query.
Pressing F11
will show you
the description
of the columns.
Press F11
twice to return
to this screen.
88
Specify SELECT Statement
Once you have the
columns you want,
press F3 to return
to the edit query
screen.
If you want, you
can enter the rest
of the select
statement from
here as well.
Prompting is
available for each
clause.
89
Result of Prompting in SQL
Mode
The result of
prompting in SQL
mode provides
no spacing. The
various clauses
are built as one
continuous string.
You can go back
and forth from
this screen to the
prompting
screen. Any
spacing you add
is removed.
90
Running Query
Place a 9 by the
query you want
to run and press
enter.
91
Run Query Options
This screen allows
you to specify the
form to use when
you run your query.
Use *SYSDFT if no
form is required.
Place the cursor on
the Form line and
press F4 to bring
up a list of forms in
your library
92
List of Forms
Place a 1 by the
form you want to
use and press
enter.
93
Press Enter to Run
Press enter to
submit the query for
execution.
You’ll have to get
out of QM to the
SMSU Query Menu
and use option 10
to check if the
report has executed
and option 11 to
see the output of
the report.
94
Submission Message
Notice that there
is a submission
message placed
at the bottom of
the screen.
95
Example 2
• Find a count of all current students in all
departments in a specific college having a
GPA over 3.0 whose age is over 30
96
Copying a Query
We’re going to use
the previous example
to build upon.
Copy the query from
example 1 by placing
a 3 in the Opt column
and pressing enter.
97
Copying a Query
Tab to the “To
Query” field, and
type in the new
name.
Press enter and the
new query is copied
from the existing
query.
98
Changing an Existing Query
Place a 2 next to
the query you want
to change and
press enter.
Notice the
confirmation
message of the
copy at the bottom
of the screen.
99
Changing an Existing Query
Notice the > next
to some of the
selections. This
indicates that
those items have
been specified.
We need to add a
column and
change the row
selection criteria
for this example,
so tab to “Select
and sequence
columns” , key 1
and press enter.
100
Adding a column
We want to add a
column at the end of
the list, so page
down to find
COLL_CODE, put a
number greater than
50, press enter and
the column will be
added at the end.
101
Changing Row Selection
Criteria
Bring the clause to
change up to the
top. Change the
column to
COLL_CODE. Tab
to the value and
change it to the
correct value and
press enter.
102
Changing the Form
Place a 1 to specify
report formatting and
press enter.
103
Changing the Form
We want to
change the form
that we previously
set up, so enter
that name and
press enter.
104
Changing the Form
Enter a 1 next to
column formatting
and press enter so
we can add the
column we added to
the query.
105
Making the Form and Query
Match
Press F24 (shift +
F12) to display
additional function
keys. Press F19
(shift+F7) and the
columns in the
form will be loaded
from the columns
in the active query.
106
Adding Usage Codes
This is one way of
producing the results
wanted in the
example. We’ll
produce a count by
dept. and a total
count by college.
COLL_CODE is
omitted from the
detail so it will only
appear in the
heading.
107
Adding Break Text
This screen tells us
what break level and
column(s) we’re
working with.
We have the option
of entering break
heading or footing
text.
Let’s choose footing.
108
Adding Break Text – 2
We’ll center the line
and let QM insert the
department code in
the text.
Here is a good place
to use F18 (Display
SQL) to show you
what the columns
numbers are.
109
Saving the Form
Other parts of the
form can be
changed as well.
When F3 is pressed,
the Exit screen is
presented for you to
confirm that you
want to exit and
save. You can also
rename the query or
form at this point.
We don’t want to
wipe out the
previous form, so
key in a new form
name and press
enter.
110
Example 2 (second method)
• The example can also be accomplished by
doing a group by department, having a
count column in the query, and using sum
in the form for the final count.
111
Example 2 (second method)
Here are the columns selected
and the row selection. Notice
that the AGE expression has
been moved to the row
selection.
112
Example 2 (second method)
The 1 in the Cnt
column is where the
COUNT(*) comes
from.
The SUM usage for
the Count column
gives us the final total.
113
Example 3
• Find the name and permanent city of all
current students with a specified
department who have a GPA over 3.0 and
whose age is over 30.
114
Example 3 – Specify the first
table
We’re going to use
the STDT table and
the ADDRESSES
table, so put a + in the
more tables field.
115
Example 3 – Specify the second
table
Enter
ADDRESSES for
the table and
DATACOLL for the
collection.
116
Example 3 – Specify the join
conditions
From knowing the
tables, we know
that SOC_SEC is a
column that
appears in both
tables.
By moving the
cursor to the
bottom half of the
screen, the list of
columns is
scrollable.
117
Example 3 – Select and Sequence
Columns
These are the
columns we want
in the report.
118
Example 3 – Select Rows
These are the
row selection
criteria.
119
Example 3 – SQL Version
For those of you
that prefer using
SQL, here is the
example.
Notice how the
AGE expression is
coded. This “AS
pseudo-column”
construct can be
used with any
expression in the
select list in SQL.
This is a new
feature on the
iSeries we can
use.
120
Other Notes
• You can display the SQL while editing the
form by using F18 (shift+F6).
• You can go back and forth between native
SQL Query and Form by using F13 (Shift
+ F1).
• This lets you add columns to the query
and then add them to the form.
121