sql tips n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Tips PowerPoint Presentation
Download Presentation
SQL Tips

Loading in 2 Seconds...

play fullscreen
1 / 20

SQL Tips - PowerPoint PPT Presentation


  • 172 Views
  • Uploaded on

SQL Tips. IMS User Group Meeting Fall 2002. 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({* | [DISTINCT|ALL] expr}).

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

SQL Tips


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
    Presentation Transcript
    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({* | [DISTINCT|ALL] expr})

    3. Aggregation: Count SELECT catalog_nbr,COUNT(*)AS Num_of_Stu FROMPS_DWSA_STDNT_ENRL WHERE acad_group ='TCHE' AND acad_career ='UGRD' AND stdnt_enrl_status ='E' AND subject ='FSOS' GROUPBY 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([DISTINCT|ALL] n)

    5. Aggregation: SUM SELECT acad_career, subject,SUM(unt_taken) units_taken FROMPS_DWSA_STDNT_ENRL WHERE subject ='SOIL' GROUPBY 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 FROMPS_DWSA_STDNT_ENRL WHERE acad_group ='TCHE' AND acad_career ='UGRD' AND stdnt_enrl_status ='E' AND subject ='FSOS' AND component_main ='DIS' GROUPBY catalog_nbr, class_section HAVINGCOUNT(*)>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 didn’t 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 SELECTDISTINCT a.NAME, CASE WHEN a.emailid_2 ISNULLTHEN a.emailid_1 ELSE a.emailid_2 END email_add FROMPS_DWSA_DEMO_ADDR a,PS_DWSA_PROG_DTL b WHERE a.emplid = b.emplid AND b.acad_prog ='32UGR' Case/Decode/NVL

    10. Case/Decode/NVL DECODE SELECTDISTINCT a.NAME, DECODE(a.emailid_2,NULL, a.emailid_1, a.emailid_2) email_add FROMPS_DWSA_DEMO_ADDR a,PS_DWSA_PROG_DTL b WHERE a.emplid = b.emplid AND b.acad_prog ='32UGR'

    11. Case/Decode/NVL NVL SELECTDISTINCT a.NAME, NVL(a.emailid_2, a.emailid_1) email_add FROMPS_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 <=40THEN'GET A LIFE' WHEN a.vac_hrs_taken_ytd BETWEEN41AND100THEN'NEED A BREAK?' WHEN a.vac_hrs_taken_ytd >=101THEN'WELL RESTED' END ) mental_wellbeing FROMPS_DWPY_VAC_SICK a WHERE a.deptid ='831A' AND a.fisc_yr ='2003' AND a.pay_period ='06' AND a.empl_status ='A' ORDERBY2

    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 SELECTNVL(catalog_nbr,'GRAND_TOTAL') catalog_nbr, class_section, SUM(unt_taken) total_units,COUNT(*) num_of_stu FROMPS_DWSA_STDNT_ENRL WHERE acad_group ='TCHE' AND acad_career ='UGRD' AND stdnt_enrl_status ='E' AND subject ='FSOS' GROUPBY 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 FROMPS_DWHR_DEMO_ADDR a, (SELECT x.emplid FROMPS_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. WITHOUT: WITH: ROUND SELECTROUND(AVG(eng_act_score),1) FROMPS_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 don’t have the scale and precision defined. The error message is returned as‘ERROR: 007~ASP 0101~UNEXPECTED ERROR~THE 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