1 / 34

Getting started with SQL Queries and Programming

Getting started with SQL Queries and Programming . Jeremy Brinkman Assistant VP for Information Technology University of Northwestern Ohio. University of Northwestern Ohio. Private, Non-profit Institution Founded in 1920 4600+ students from all 50 states and 24 foreign countries

cora
Télécharger la présentation

Getting started with SQL Queries and Programming

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Getting started with SQL Queries and Programming Jeremy Brinkman Assistant VP for Information Technology University of Northwestern Ohio

  2. University of Northwestern Ohio • Private, Non-profit Institution • Founded in 1920 • 4600+ students from all 50 states and 24 foreign countries • Five Colleges: College of Applied Technologies, College of Business, College of Occupational Professions, College of Health Professions, Graduate College www.unoh.edu

  3. Presentation Overview • The SQL developer’s toolbox • Locating data in Colleague • SQL query fundamentals

  4. The SQL Developer’s Toolbox

  5. The SQL Developer’s Toolbox • SQL Server Management Studio

  6. The SQL Developer’s Toolbox • Colleague Screens • RDEL • RDEP • RFEI • RDSF

  7. The SQL Developer’s Toolbox • Colleague Screens Continued • EOFM

  8. The SQL Developer’s Toolbox • Simple but effective: browse the data SELECT TOP 10 * FROM TABLE

  9. The SQL Developer’s Toolbox • Other useful tools • SQL Server Profiler • Colleague Studio • Red-gate SQL Prompt

  10. Locating Data in Colleague

  11. Locating Data on Colleague Screens • Field help • Form/process help • F1

  12. Locating Data on Colleague Screens

  13. Locating Data on Colleague Screens Lookup the field on RDEL to get the file name.

  14. Locating Data on Colleague Screens Match the file name from RDEL to the SQL table

  15. Locating Data on Colleague Screens Interpreting RDEL • Replace the “.” with “_” to derive the SQL table name from the UniData file name. • Data Fields (D) – data can be found inside the table listed in the File Name field. • List Fields (L) – Data can be found in the _LS table version of the table in the File Name field. PERSON_LS is one example. • Associated Fields (A) – Data can be found in a SQL table named for the associationlisted on RDEL. • X Pointer and Q Pointer Fields – Related data can be found in the Secondary Pointer/File. For X Pointer fields, the pointer exists in the SQL table itself. For Q Pointer fields, the pointer exists in the _LS table.

  16. Locating Data on Colleague Screens Q Pointer example:

  17. Locating Data on Colleague Screens Q Pointer example in SQL Server:

  18. Locating Data on Colleague Screens Associated field example:

  19. Locating Data on Colleague Screens Associated field example in SQL Server:

  20. Locating Data on Colleague Screens Q Pointer + Association example:

  21. Locating Data on Colleague Screens Q Pointer + Association in SQL:

  22. Locating Data on Colleague Screens A few more tips: • The POS field is used to store the position of data in multivalued fields or associations. • “VAR” fields found on Colleague forms do not represent actual fields in the SQL database. • File Suites are often difficult to interpret. In general, when you see “ACYR” in a file, you can replace it with the file suite year.

  23. SQL Query Fundamentals

  24. Elements of a basic SQL Query SELECT < list of fields to return > FROM < tables > WHERE < data filters > ORDER BY < fields to sort >

  25. SQL Query Example #1 SELECT PERSON.FIRST_NAME, PERSON.LAST_NAME FROM PERSON WHERE PERSON.LAST_NAME =‘Flintstone’ ORDER BY PERSON.FIRST_NAME

  26. Coding Tip Coding Tip: Always prefix your field names with a table name or alias.

  27. SQL Query Example #2 SELECT PERSON.FIRST_NAME, PERSON.LAST_NAME, dbo.AGE(PERSON.ID) FROM PERSON WHERE PERSON.LAST_NAME = 'Flintstone' ORDER BY PERSON.FIRST_NAME Using a Computed Column

  28. Let’s Join Another Table SELECT < list of fields to return > FROM < table 1 > INNER JOIN < table 2 > ON <table 1 field>=<table 2 field> WHERE < data filters > ORDER BY < fields to sort >

  29. SQL Query Example #3 SELECT PERSON.FIRST_NAME, PERSON.LAST_NAME, PERSON_LS.POS, PERSON_LS.PER_ETHNICS FROM PERSON INNER JOIN PERSON_LS ONPERSON_LS.ID =PERSON.ID WHERE PERSON.LAST_NAME=‘Flintstone’ ORDER BY PERSON.FIRST_NAME

  30. Coding Tip Coding Tip: Keep code clean by using proper indentation.

  31. Most Common Join Types • INNER JOIN – Include only records where the join field(s) matches in the left and right table. • LEFT OUTER JOIN – Include all records from the left table and only those in the right table that match the join field(s)

  32. SQL Query Example #4List all addresses for a person SELECT PERSON.FIRST_NAME, PERSON.LAST_NAME, ADDRESS_LS.ADDRESS_LINES, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.ZIP FROM PERSON INNER JOIN PSEASON ON PERSON.ID = PSEASON.ID INNER JOIN ADDRESS ON PSEASON.PERSON_ADDRESSES = ADDRESS.ADDRESS_ID INNER JOIN ADDRESS_LS ON ADDRESS.ADDRESS_ID = ADDRESS_LS.ADDRESS_ID AND ADDRESS_LS.POS = 1 WHERE PERSON.LAST_NAME = 'Flintstone'

  33. Questions?

  34. Contact Information • Jeremy Brinkman • Assistant VP for Information Technology • University of Northwestern Ohio • jbrinkman@unoh.edu

More Related