SQL%20Tips

1
SQL Tips

• IMS User Group Meeting
• Fall 2002
•  

2
Aggregation COUNT

• Provides a count of all (distinct) values in a
  particular column or table. The column can be
  either alpha or numeric. Null values in the
  column are included in the count.
• Syntax
• COUNT( DISTINCTALL expr)

3
Aggregation Count

• SELECT catalog_nbr, COUNT () AS Num_of_Stu
• FROM PS_DWSA_STDNT_ENRL
• WHERE acad_group 'TCHE'
• AND acad_career 'UGRD'
• AND stdnt_enrl_status 'E'
• AND subject 'FSOS'
• GROUP BY catalog_nbr

4
Aggregation SUM

• Provides a sum of all (distinct) values in a
  particular column. The column must be numeric.
  Null values in the column are not included in the
  sum.
• Syntax
• SUM(DISTINCTALL n)

5
Aggregation SUM

• SELECT acad_career, subject, SUM (unt_taken)
  units_taken
• FROM PS_DWSA_STDNT_ENRL
• WHERE subject 'SOIL'
• GROUP BY acad_career, subject

6
Aggregation HAVING

• Use the HAVING clause to restrict which groups of
  rows defined by the GROUP BY clause are returned
  by the query.

7
Aggregation HAVING

• SELECT catalog_nbr, class_section, COUNT () AS
  num_of_stu
• FROM PS_DWSA_STDNT_ENRL
• WHERE acad_group 'TCHE'
• AND acad_career 'UGRD'
• AND stdnt_enrl_status 'E'
• AND subject 'FSOS'
• AND component_main 'DIS'
• GROUP BY catalog_nbr, class_section
• HAVING COUNT () gt 50

8
Case/Decode/NVL

• Case and Decode statements both perform
  procedural logic inside a SQL statement without
  having to resort to PL/SQL .
• All of these queries will return a list of
  student names with their secondary email
  addresses unless they didnt report a secondary
  address, then it will return their primary email
  address.
• It is best to use the CASE statement when
  comparing ranges or more complex logic.

9
Case/Decode/NVL

• CASE
• SELECT DISTINCT a.NAME,
• CASE
• WHEN a.emailid_2 IS NULL THEN
  a.emailid_1
• ELSE a.emailid_2
• END
• email_add
• FROM PS_DWSA_DEMO_ADDR a,
  PS_DWSA_PROG_DTL b
• WHERE a.emplid b.emplid
• AND b.acad_prog '32UGR'

10
Case/Decode/NVL

• DECODE
• SELECT DISTINCT a.NAME,
• DECODE (a.emailid_2, NULL,
  a.emailid_1, a.emailid_2) email_add
• FROM PS_DWSA_DEMO_ADDR a,
  PS_DWSA_PROG_DTL b
• WHERE a.emplid b.emplid
• AND b.acad_prog '32UGR'

11
Case/Decode/NVL

• NVL
• SELECT DISTINCT a.NAME,
• NVL (a.emailid_2, a.emailid_1) email_add
• FROM PS_DWSA_DEMO_ADDR a,
  PS_DWSA_PROG_DTL b
• WHERE a.emplid b.emplid
• AND b.acad_prog '32UGR'

12
Case/Decode/NVL

• SELECT a.NAME,
• (CASE
• WHEN a.vac_hrs_taken_ytd lt 40 THEN
  'GET A LIFE'
• WHEN a.vac_hrs_taken_ytd BETWEEN 41
  AND 100 THEN 'NEED A BREAK?'
• WHEN a.vac_hrs_taken_ytd gt 101 THEN
  'WELL RESTED'
• END
• )
• mental_wellbeing
• FROM PS_DWPY_VAC_SICK a
• WHERE a.deptid '831A'
• AND a.fisc_yr '2003'
• AND a.pay_period '06'
• AND a.empl_status 'A'
• ORDER BY 2

13
Case/Decode/NVL
14
Aggregation ROLLUP

• The use of a ROLLUP clause in the GROUP BY part
  of the SQL expression displays subtotals and
  grand totals depending on its use.

15
Aggregation ROLLUP

• SELECT NVL (catalog_nbr, 'GRAND_TOTAL')
  catalog_nbr, class_section,
• SUM (unt_taken)
• total_units, COUNT () num_of_stu
• FROM PS_DWSA_STDNT_ENRL
• WHERE acad_group 'TCHE'
• AND acad_career 'UGRD'
• AND stdnt_enrl_status 'E'
• AND subject 'FSOS'
• GROUP BY ROLLUP (catalog_nbr, class_section)

16
INLINE VIEWS

• You can use a SQL statement in the FROM clause of
  a SQL statement. This is called an inline view.
  Oracle treats the data set that is returned from
  the inline view as if it were a table.

17
INLINE VIEWS

• SELECT a.NAME, a.office1_phone
• FROM PS_DWHR_DEMO_ADDR a,
• (SELECT x.emplid
• FROM PS_DWHR_JOB x
• WHERE x.deptid '831A'
• AND x.status_flg 'C'
• AND x.job_terminated 'N') b
• WHERE a.emplid b.emplid

18
ROUND

• Returns a number rounded to m places right of the
  decimal point if m is omitted, to 0 places. m
  can be negative to round off digits left of the
  decimal point. m must be an integer.
• Syntax
• ROUND(n,m)

19
ROUND

• SELECT ROUND (AVG (eng_act_score), 1)
• FROM PS_DWAD_UGRS_SCRS
• WHERE eng_act_score ! 0

NOTE There has been an issue with the Web Query
tool involving the selection of NUMBER fields
that dont have the scale and precision defined.
The error message is returned as ERROR 007ASP
0101UNEXPECTED ERRORTHE FUNCTION RETURNED ..
Use of the ROUND function will alleviate the
issue.
20
A text file with these SQL Tips will be available
under the Information section at http//dw.umn.edu