Introduction to SQL Using Oracle

1
Introduction to SQL Using Oracle

• Florida State University
• Administrative Information Systems
• University Data Services
• Penny Bowman
• May, 2002

2
Intro. To SQL Using OracleCourse Overview

• Relational Concepts
• Introduction to SQL
• Oracle Training Database
• Select Data
• Use SQLPlus Worksheet
• Discover Your Oracle Environment
• AIS Oracle Standards

• Oracle Functions
• Number Functions
• Character Functions
• Group Functions
• Date Functions
• Conversion Functions
• Coding SQL Predicates
• Grouping Data

3
Relational Concepts

• A relational database is a collection of data
  where logically related data is grouped into
  tables (files) within the database
• Rows (records) appear horizontally in a report,
  and contain one or more columns
• Tables within a relational database hold columns
  (fields) of data that appear vertically in a
  report
• Indexes are created for relating tables and for
  faster access to the data

4
Introduction to Structured Query Language

• DCL - Data Control Language - for data security -
  uses Grant and Revoke verbs
• DDL - Data Definition Language - for physical
  data structure maintenance - uses Create, Alter,
  Drop verbs
• DML - Data Manipulation Language - for accessing
  and modifying data - uses Select, Insert, Delete,
  and Update verbs
• SQL can be bound ahead of execution time
  (static) or used prepared during execution
  (dynamic)

5
Oracle Training Database

• Throughout the training course we will be using
  10 demonstration tables that come with the Oracle
  installation
• These tables contain business related
  information
• Location, Department, Job, Employee,
  Salary_grade, Product, Customer, Sales_Order,
  Item
• Lets Review the Database Schema

6
SELECT Employee Data

• Read Employee name and hire date information from
  the Employee Table for a certain department
• SELECT
• Last_name, First_name, Middle_initial,
  hire_date, department_id
• FROM pbowman.employee
• WHERE department_id 20
• ORDER BY last_name , first_name

7
Example Select Employee Information

• LAST_NAME FIRST_NAME M HIRE_DATE
  DEPARTMENT_ID
• ------------------ ------------------- -
  --------------- -------------------------
• ADAMS DIANE G
  12-JAN-87 20
• FORD JENNIFER D
  03-DEC-85 20
• JONES TERRY M
  02-APR-85 20
• SCOTT DONALD T 09-DEC-86
  20
• SMITH JOHN Q
  17-DEC-84 20

8
SELECT Statement

• The result of a SELECT query is a result set that
  includes the rows and columns that satisfy your
  query.
• SELECT - specifies the columns or fields to be
  included in the result set
• FROM - identifies the tables or views from which
  the data will be retrieved
• WHERE - the predicate(s) that determine which
  rows will be retrieved
• ORDER BY - determines the sequence of the rows

9
SELECT Statement Syntax

• SELECT ALLDISTINCT
• columns AS newname
• FROM table or view AS shortname
• WHERE conditions
• GROUP BY fields
• HAVING conditions
• ORDER BY columninteger ASCDESC

10
Use SQLPlus Worksheet

• Use the SQLPlus Worksheet to develop and test
  your Oracle SQL statements
• When you are comfortable that the SQL is working
  correctly, copy the sql statement into your
  program
• Check with your DBA for the best performing SQL
  guidelines
• Look in the SQLPlus Users Guide and Reference
  manual in the file ? a53717.pdf
• Look in the Oracle 8 SQL Reference manual in the
  file ? a58225.pdf

11
Lab 1 - Use Oracle SQLPlus Worksheet

• Open a SQL Worksheet session
• Connect to STAGING using your assigned training
  userid
• Type the sample Employee table query (emp1)
• Press the run button
• Practice selecting information from the Employee
  table

12
Lab - Use Oracle SQLPlus Worksheet

• DESCRIBE the table
• desc pbowman.employee
• Name
  Null? Type
• -----------------------------------------
  -------- ----------------------------
• EMPLOYEE_ID NOT NULL
  NUMBER(4)
• LAST_NAME
  VARCHAR2(15)
• FIRST_NAME
  VARCHAR2(15)
• MIDDLE_INITIAL
  VARCHAR2(1)
• JOB_ID
  NUMBER(3)
• MANAGER_ID
  NUMBER(4)
• HIRE_DATE
  DATE
• SALARY
  NUMBER(7,2)
• COMMISSION
  NUMBER(7,2)
• DEPARTMENT_ID
  NUMBER(2)

13
Discover Your Oracle Environment

• While in SQLPlus Worksheet
• Enter your sql statement at the top
• Click the lightning bolt to execute it
• Notice the result set in the bottom half
• Click the SQL button to look at previous commands
  for this session
• Use Edit ? Clear All to empty the top or the
  bottom, depending on your cursor position
• Save your sql
• File ? Save Input As saves the top half
• Save the results of the query execution
• File ? Save Output As saves the bottom half
• Open previously saved files
• File ? Open

14
Data Dictionary Views for Users

• Use the DESCribe command to examine the table
  structure of each View before you SELECT from
  the View. This is a sampling of the available
  views
• Prefix choices are USER_, ALL_, DBA_
• USER_CATALOG
• USER_COL_COMMENTS
• USER_COL_PRIVS
• USER_CONSTRAINTS
• USER_CONS_COLUMNS
• USER_DEPENDENCIES

• USER_INDEXES
• USER_IND_COLUMNS
• USER_OBJECTS
• USER_RESOURCE_LIMITS
• USER_ROLE_PRIVS
• USER_TABLES
• USER_TABLESPACES
• USER_TAB_COLUMNS
• USER_TAB_COMMENTS
• USER_USERS
• USER_VIEWS

15
AIS Oracle Standards

• FSU AIS Database standards and training
  documentation can be found at this site
• http//www.oti.fsu.edu/dba/dba_standards.html

16
Quiz 1

• A table ____ is a group of _________ related to
  each other just as a file record is a similar
  group of fields.
• The SQL SELECT statement consists of these 4
  basic parts _____________, _____________,
  _____________, and _____________.
• Which part of the SELECT statement contains the
  predicates that define the conditions that must
  be met for data to be included in the result set?
• ________ provides a quick way to test and
  execute your SQL.
• How can you quickly find out the structure of a
  table in Oracle?
• How can you find out what tables you can access
  in Oracle?

17
Oracle SQL Operator Precedence

• Oracle evaluates operators with a higher
  precedence first.
• Operators on the same line on the next slide
  have the same precedence.
• Oracle evaluates operators with equal precedence
  from left to right within an expression.

18
Oracle SQL Operator Precedence

• Operator
• , -
• , /
• , -,
• , !, lt, gt, lt, gt, IS NULL, LIKE, BETWEEN, IN
• NOT
• AND
• OR

• Operation
• Identity, negation
• Multiplication, Division
• Addition, Subtraction, Concatentation
• Comparison
• Logical negation
• Conjunction
• Disjunction

19
Oracle SQL Functions

• Look in Chapter 3 of the Oracle 8 SQL Reference
  manual in the file ? a58225.pdf
• Number Functions
• Character Functions
• Group Functions
• Date Functions and formats
• Conversion Functions
• Other Functions
• Note Be careful if you plan to port your SQL
  from Oracle to DB2 - Only a few of the functions
  are coded the same between the two database
  systems. Compare the Oracle Functions and the DB2
  Version 6 functions before you use them.

20
Number Functions
Number functions accept numeric input and return
numeric values.

• ABS(n)
• ACOS(n)
• ASIN(n)
• ATAN(n)
• ATAN2(n, m)
• CEIL(n)
• COS(n)
• COSH(n)
• EXP(n)
• FLOOR(n)
• LN(n)
• LOG(m,n)

• MOD(m,n)
• POWER(m,n)
• ROUND(n,m)
• SIGN(n)
• SIN(n)
• SINH(n)
• SQRT(n)
• TAN(n)
• TANH(n)
• TRUNC(n,m)

21
Character Functions

• Character functions accept character input and
  return either character or numeric values.
• This first group of functions accept character
  input and return character values.
• The second group of functions accept character
  input and return numeric values.

22
Character Functions First Set

• CHR(n)
• CONCAT(char1, char2)
• INITCAP(char)
• LOWER(char)
• LPAD(char,n,char2)
• LTRIM(char,set)
• NLS_INITCAP (n , nls_sort)
• NLS_LOWER (n , nls_sort)

• NLS_UPPER (n , nls_sort)
• REPLACE (char, search_string ,
  replacement_string)
• RPAD (char1, n,char2)
• RTRIM (char ,set)
• SOUNDEX(char)
• SUBSTR(char, m,n)
• SUBSTRB(char, m,n)
• TRANSLATE(char,from,to)
• UPPER(char)

23
Character Functions Second Set

• ASCII(char)
• INSTR(char1, char2 ,n,m)
• INSTRB(char1, char2 ,n,m)
• LENGTH(char)
• LENGTHB(char)
• NLSSORT(char , nls_sort)

24
Date Functions

• Date functions operate on values of the DATE
  datatype.
• All date functions return a value of DATE
  datatype, except the MONTHS_BETWEEN function,
  which returns a number.

25
Date Functions

• ADD_MONTHS(d,n)
• LAST_DAY(d)
• MONTHS_BETWEEN(d,e)
• NEW_TIME(d,a,b)
• NEXT_DAY(d,char)
• ROUND (d ,fmt)
• SYSDATE
• TRUNC(d ,fmt)

26
Date Truncation and Rounding

• ROUND(d,fmt)
• TRUNC(d,fmt)
• Format model for fmt in ROUND and TRUNC
• CC or SCC
• YYYY or SYYYY
• YYY or YY or Y
• Y,YYY or YEAR or SYEAR
• Q

• MONTH or MON or MM or RM
• WW or IW
• W
• DDD or DD or J
• DAY or DY or D
• HH or HH12 or HH24
• MI

27
Conversion Functions

• Conversion functions convert a value from one
  datatype to another.
• Generally, the form of the function names follows
  the convention datatype TO datatype
• The first datatype is the input datatype the
  last datatype is the output datatype.

28
Conversion Functions

• CHARTOROWID(char)
• CONVERT( char, dest_char_set ,source_char_set )
• HEXTORAW(char)
• RAWTOHEX(raw)
• ROWIDTOCHAR(rowid)
• TO_CHAR, date conversion
• TO_CHAR(d , fmt , nlsparams )
• TO_CHAR, number conversion
• TO_CHAR(n , fmt , nlsparams )

• TO_DATE (char , fmt , nlsparams )
• TO_MULTI_BYTE(char)
• TO_NUMBER (char , fmt , nlsparams )
• TO_SINGLE_BYTE(char)
• TRANSLATE USING
• TRANSLATE(text USING CHAR_CS NCHAR_CS )

29
Date Format Models for fmt of TO_CHAR and TO_DATE

• SCC or CC
• YYYY or SYYYY
• YYY or YY or Y
• IYYY
• IYY or IY or I
• Y,YYY
• SYEAR or YEAR
• RR
• BC or AD
• B.C. or A.D.
• Q
• MM
• RM
• MONTH
• MON

• WW or W
• IW
• DDD or DD or D
• DAY
• DY
• J
• AM or PM
• A.M. or P.M.
• HH or HH12
• HH24
• MI
• SS or SSSSS
• -/ , . (punctuation)
• text.. (string)

30
Date Format Prefixes and Suffixes

• Prefix
• FM (fill mode)
• FX (format exact)
• Suffix
• TH (ordinal number 4th)
• SP (spelled out number - FOUR)
• SPTH and THSP (spelled out ordinal number
  FOURTH)
• Note When prefixes and suffixes are added to a
  date format, the case (upper, initial, or lower)
  is determined by the format element, not by the
  prefix or suffix. ddTH produces 04th not
  04TH

• Date Format Case Control
• Uppercase
• DAY, DY, MONTH, MON, YEAR, AM, PM, A.M., A.m.,
  P.M., P.m.
• Initial Caps
• Day, Dy, Month, Mon, Year, Am, Pm
• Lowercase
• day, dy, month, mon, year, am, pm

31
Number Formats for fmt of TO_CHAR

• 9
• 0
• B
• MI
• S
• PR
• D
• G

• L
• C
• ,
• .
• V
• EEEE
• RN or rn
• DATE

32
SYSDATE Function

• The SYSDATE function returns the current date and
  time from Oracle.
• If you want to only manipulate the CURRENT DATE,
  and you are not selecting other columns from a
  table, you may issue a SELECT statement using the
  Oracle one row table
• SELECT SYSDATE FROM DUAL
• Note This is the DB2 way ?
• SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1

33
Review Date Functions

• Before we move to the next topic, lets practice
  writing SQL queries that use the Oracle DATE
  functions and formats using the DUAL table.
• SELECT SYSDATE FROM DUAL (notice the default
  Oracle date format is mm/dd/yy)
• SELECT TO_CHAR(SYSDATE,'MM/DD/YY')as MyDate from
  dual
• SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD')as MyDate
  from dual
• SELECT TO_CHAR(sysdate 1) as NextDay from dual

34
Review Date Functions

• SELECT TO_CHAR(SYSDATE,'Year') as MyYear ,
  TO_CHAR(SYSDATE,'Month') as MyMonth,
  TO_CHAR(SYSDATE,'Day') as MyDay from dual
• SELECT TO_CHAR(SYSDATE,'YYYYTH-MMTH-DDTH')as
  MyDate from dual
• SELECT TO_CHAR(SYSDATE, 'hhmmss') as Time1,
  TO_CHAR(SYSDATE,'HHSPMMSPSSSP') as Time2 from
  dual
• Use SQLPlus Worksheet to practice these queries.
  Develop other date / time queries.

35
Other Single-Row Functions

• DUMP (expr , display_format , start_position ,
  length )
• EMPTY_B CLOB()
• BFILENAME (directory, filename)
• GREATEST (expr , expr )
• LEAST (expr , expr )
• NLS_CHARSET_DECL_LEN(bytecnt, csid)

36
Other Single-Row Functions

• NLS_CHARSET_ID(text)
• NLS_CHARSET_NAME(n)
• NVL (expr1, expr2)
• UID
• USER
• USERENV (option) (option can be ENTRYID,
  SESSIONID, TERMINAL, LANGUAGE, LABEL)
• VSIZE (expr)

37
Object Reference Functions

• DEREF(e)
• REFTOHEX(r)
• MAKE_REF(table, key , key )

38
Group Functions

• Default is to include all the candidate rows, but
  using DISTINCT causes the group function to use
  only the distinct values of the argument
  expression
• AVG (DISTINCT ALL n)
• COUNT( DISTINCT ALL expr )
• MAX(DISTINCT ALL expr)
• MIN ( DISTINCT ALL expr)
• STDDEV(DISTINCT ALL n)
• SUM(DISTINCT ALL n)
• VARIANCE (DISTINCT ALL n)
• Note more on grouping data later

39
Lab 2 - Functions

• Practice writing SELECT statements using Oracle
  functions
• DATE conversions
• Conversion functions
• Grouping on a single column

40
Quiz 2

• Use the _________ function to extract a portion
  of a character value.
• The ______ function combines two characters
  values into a single field.
• Use the ______ function to determine how many
  characters are in a character value.
• Which function would you use to determine the
  highest salary in the Employee table?
• Which function would you use to determine the
  total salary expense for a specific department?
• How do you obtain the current date and time from
  Oracle?
• Which function is used in Oracle to convert
  between date and character format? And between
  character and date format?

41
Coding Predicates in a SELECT

• The Predicates are found in the WHERE clause of
  the SELECT statement
• Used to identify which rows we want
• Basic predicates compare only 2 values
• The result is either True or False.
• If the value of either operand is null or the
  result of the subselect is empty, the result of
  the predicate is unknown.

42
Select High Total Orders

• List order information where the total is greater
  than 2000.
• SELECT
• Customer_Id, Order_Date, Order_Id, Total
• FROM pbowman.Sales_Order
• WHERE Total gt 2000
• ORDER BY Customer_id, Order_Date

43
Report Orders Shipped on the Same Day as the Order

• SELECT
• Customer_Id, Order_Date, Ship_Date, Total
• FROM pbowman.Sales_Order
• WHERE Order_Date Ship_date
• ORDER BY Customer_id, Order_Date
• Practice changing the order of the result set

44
Compound Predicate

• All of the predicate forms can be combined using
  the compound logical operators
• AND, OR, and NOT
• WHERE NOT predicate ANDOR
• NOT predicate
• The result is either TRUE, FALSE, or Unknown
  about a given row or group of rows (grouping
  predicates)
• If you dont use parentheses, these operators are
  processed in this order NOT , AND, OR

45
IN Predicate

• Tests if the value equals another value in a
  provided set of values
• WHERE field NOT IN
• (field1, field2, field3)
• Equivalent to ANY
• Field can be a value, set or subquery

46
BETWEEN Predicate

• Tests if the value of a field falls (inclusively)
  between two other values
• WHERE field NOT BETWEEN
• field AND field
• Logically Comparable to using gt and lt

47
NULL Predicate

• A NULL value is the relational database
  representation of no value exists
• WHERE field IS NOT NULL
• Due to the complexity of null handling, usage of
  Nulls has been discouraged in FSU/AIS database
  table columns

48
LIKE Predicate

• Powerful for character string comparisons
• If a pattern does not contain the character,
  the condition can be TRUE only if both operands
  have the same length
• WHERE field NOT LIKE value
• Wildcard characters
• - string of zero or more characters ABC
• _ - underscore - any single character A_C
• Examples
• WHERE ename LIKE MA
• WHERE ename LIKE SMITH_
• WHERE ename LIKE S_I

49
Lab - Predicates

• Practice writing SELECT statements using
• BETWEEN
• LIKE
• AND
• OR
• IN
• NULL

50
Quiz 3

• _________ are found in the WHERE clause of SELECT
  statements and describe attributes of the data to
  be included in the result set of the query.
• A _______ value is the relational database
  representation of "no value exists".
• The ________ predicate searches character fields
  for patterns in the text.
• The ____ wildcard is used to search for any
  string of zero or more characters.

51
Quiz 3

• The ____ wildcard is used to search for any
  single character.
• The _______ predicate is used to test if the
  value of a field falls (inclusively) between two
  other values.
• The _______ predicate is used to determine if a
  fields value equals any value in a list of other
  values.
• All of the predicate forms can be combined using
  the compound logical operators _____________,
  _____________, and _____________.

52
Grouping Data

• Occurs any time detail information is used only
  for the purpose of deriving summary information
• Used to cluster your result into sets, or
  groups of rows that have equal values in the
  same columns
• Use GROUP BY and HAVING

53
GROUP BY

• SELECT fields FROM tables
• WHERE conditions
• GROUP BY fields
• HAVING conditions
• ORDER BY fields
• GROUP BY is used to summarize the columns
  selected.
• DB2 automatically sorts by the grouping column(s)

54
GROUP BY

• HAVING optionally follows the GROUP BY and can be
  used to place a condition on the GROUP
• The HAVING condition can reference
• One of the columns
• A column function applied to the rows of the
  groups
• A subquery which only returns one row

55
Functions Commonly Used With Group By

• COUNT
• MAX
• MIN
• AVG
• SUM

56
Count Customers By State

• SELECT State ,
• Count() as Total
• FROM pbowman.CUSTOMER
• GROUP BY State

57
Count Customers By State

• ST TOTAL
• -- ----------
• CA 8
• MA 6
• MN 1
• NY 9
• TX 9

58
Count the Number of Customers in Texas

• SELECT State , Count() as Total
• FROM pbowman.CUSTOMER
• GROUP BY State
• Having State TX
• ST TOTAL
• -- ----------
• TX 9

59
Count States With Only 1 Customer

• SELECT State , Count() as Total
• FROM pbowman.CUSTOMER
• GROUP BY State
• Having Count() 1
• ST TOTAL
• -- ----------
• MN 1

60
Count States With Lowest Credit Limits

• SELECT State , Count()as Total
• FROM pbowman.CUSTOMER
• GROUP BY State
• having sum(credit_limit) lt 10000
• ST TOTAL
• -- ----------
• MN 1

61
Lab Grouping Data

• Practice writing SELECT statements grouping
  employee and customer information
• Utilize the HAVING clause to select only certain
  groups from the result sets

62
Quiz 4

• You should use the _____ ____ clause to
  summarize data.
• The _________ clause puts a condition on the
  GROUP, and must follow the GROUP BY clause.
• The ______ clause puts a condition on each row in
  the result set.
• .

63
Intro. To SQL Using OracleCourse Review

• Relational Concepts
• Introduction to SQL
• Oracle Training Database
• Select Data
• Use SQLPlus Worksheet
• Discover Your Oracle Environment
• AIS Oracle Standards

• Oracle Functions
• Number Functions
• Character Functions
• Group Functions
• Date Functions
• Conversion Functions
• Coding SQL Predicates
• Grouping Data