1 basic sql smsu computer services short course. 2 contact information greg snider – mis database...
TRANSCRIPT
1
Basic SQL
SMSU
Computer Services
Short Course
2
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.
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.”
4
Contents
• Concepts– What is SQL?– Terminology
• SQL Language– Its parts
• SELECT Statement
• Query Manager
5
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 …
6
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
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.
8
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
9
SQL Language
• Language elements
• Statements
• Functions
• Joins
• Unions
• Subselects
10
Language Elements
• Data types
• Constants
• Expressions
• Predicates
11
Data Types
• Character strings
• Datetime– Date– Time
• Numeric– Integer– Decimal– Numeric
12
Character Strings
• Fixed length– 1 – 254 positions
13
Date
• Date– Format MM/DD/YYYY– 10 positions
• When using a date, it must be enclosed in single quotes, ’09/16/2004’
14
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
15
Constants
• Integer constants– 456, -789
• Decimal constants– 978.34, 9584.2746
• String (character) constants– ‘ABCE’, ‘Computers for Learning’
16
Expressions
• Operators– || or CONCAT, /, *, +, -– || only for strings– Standard rules apply for arithmetic operations
17
Date Arithmetic
• Admit_date + 2 months + 2 days is valid
• Grad_date – Admit_date is valid
18
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
19
Statements
• Select
20
Select
• Select clause
• From clause
• Where clause
• Group by clause
• Having clause
• Order by clause
21
Select Clause
• SELECT columns, expressions
• Tells what you want to see
22
From Clause
• FROM datacoll.view-name
• Datacoll is the owner of all our views
23
Where Clause
• WHERE search-condition
24
Group By Clause
• GROUP BY column1, column2, …
25
Having Clause
• HAVING search-condition
• Each column used in the search must:– Unambiguously identify a grouping column or– Be specified with a column function
26
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
27
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
28
How would you use SQL in your job?
29
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
30
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’)
31
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
32
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
33
Scalar Functions 4
• HOUR(expression)
• INTEGER(expression)
• MINUTE(expression)
• MONTH(expression)
• SECOND(expression
34
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)
35
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
36
Joins 2 -- Intersections or Differences
• Intersections
• Difference
37
Joins 3 - Intersection
• Identify the juniors who have a foreign language major and the classes they are taking this fall
StudentTable
Classes
Classes Table
38
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
39
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
40
Unions 1
Merging results from 2 or more queries Identify Sr and JR in FL
Student Table Classes Table
41
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
42
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
43
Union 4 - Example 2
• List the names of all students who are either advised by advisor E333 or are juniors.
Advised byE333
Juniors
Final Report
44
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
45
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
46
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
47
Combining Union and Union All
Query 2
Query 3
Query 4
Union All
Union All
Union
Query 1 Internal
area
Final ReportFinal Report
Sort
48
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
49
Subselectsselect empno, actno, emstdate, emendatefrom empprojactwhere empno in (select empno from emp where workdept = ‘E11’
select workdept, max(salary)from empgroup by workdepthaving max(salary) > (select avg(salary) from emp
select workdept, max(salary) grom emp qgroup by workdepthaving max(salary) < (select avg(salary) from emp where not workdept = q.workdept)
50
Lunch Break
• Class resumes at 1:00
51
Getting to Query Manager
From the SMSU
Main Menu,
select option 20
52
Getting to Query Manager
From the SMSU
Query Menu,
select option 5
53
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.
54
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)
55
Example 1
• Find all current students in a specific department having a GPA over 3.0 whose age is over 30
56
Creating a New Query
Type 1 under Opt column
Type name under “Query” column.
Press Enter
57
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.
58
Select Table
Collection = Library
University tables are stored in DATACOLL
Press F4 to list tables or type in desired table name.
Type “+” on line for second table
59
Select Table
Type 1 beside desired table and press enter.
60
Select Next Criteria
All desired options can be picked individually or at one time.
61
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.
62
Select and Sequence ColumnsSelect 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.
63
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.
64
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
65
Select a Row Comparison for Change
Place the cursor on any line of the previous comparison tests to change that test and press enter.
66
Change a Row Comparison
The selected test is copied to the top and is available for change.
67
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.
68
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.
69
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.
70
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.
71
Build Form
These are the options you have in building your form.
Enter a 1 in the Opt column for Edit column formatting.
72
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.
73
Page Heading
74
Page Footing
75
Final Text
This is text that is placed at the very end of the report.
76
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.
77
Report Format Options
78
Function Keys
• F13 to Edit query
• F5 Run Report
• F18 Display SQL
79
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.
80
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
81
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.
82
Create Native Query
• Shift + F7 to change Query Creation Mode from Prompted to SQL
83
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.
84
More Prompting Possible
The cursor is automatically placed on the FROM tables line. Press F4 to bring up a list of tables.
85
Choose Library/Collection
DATACOLL is where the production data is stored.
86
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.
87
Or Type
If you know what table you want, simply enter it on the FROM tables line.
88
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.
89
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.
90
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.
91
Running Query
Place a 9 by the query you want to run and press enter.
92
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
93
List of Forms
Place a 1 by the form you want to use and press enter.
94
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.
95
Submission Message
Notice that there is a submission message placed at the bottom of the screen.
96
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
97
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.
98
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.
99
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.
100
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.
101
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.
102
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.
103
Changing the Form
Place a 1 to specify report formatting and press enter.
104
Changing the Form
We want to change the form that we previously set up, so enter that name and press enter.
105
Changing the Form
Enter a 1 next to column formatting and press enter so we can add the column we added to the query.
106
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.
107
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.
108
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.
109
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.
110
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.
111
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.
112
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.
113
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.
114
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.
115
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.
116
Example 3 – Specify the second table
Enter ADDRESSES for the table and DATACOLL for the collection.
117
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.
118
Example 3 – Select and Sequence Columns
These are the columns we want in the report.
119
Example 3 – Select Rows
These are the row selection criteria.
120
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.
121
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.