Understanding Data Schema Using
Pass-Through SQL Queries
Peter Yustat
Agenda
• What is a Pass Through?
• Infinite Campus Schema
• Getting started
• Basic SQL
• Creating pass-through SQL queries
• Examples
• Census
• Curriculum • Grading
What are Pass-Through Queries?
• Ad Hoc Reporting tool that uses SQL
• Accesses Infinite Campus tables and views
• Student & Course Information is school specific (you must have a school chosen in your calendar drop down). NOTE: Census/Staff info is not specific to a school.
• Returns lists of students, people, or courses with pre-defined information.
• Student type returns grade: Last Name, First Name, Student Number
• Census/Staff: Last Name, First Name
Background – Relational Databases
Relational Database: Multiple
tables with records tied together by key fields called Primary Keys
1
1 1
∞
• Some commonly used Campus key fields
• calendarID • personID • courseID • sectionID
What are the Advantages?
• Powerful and flexible • Highly Customizable • Can be shared
• Accesses the entire Infinite Campus Schema
• Best way to build cohort-based, longitudinal filters • Requires knowledge of SQL
Ad Hoc Filter Types
Selection Editor
• Set list
• Query Wizard
• Dynamic List based on selected criteria
• Pass Through Filter
Setting Expectations
• What do you expect to see in your results • Do you know where the data is?
• Can you build the SQL?
Pass-Through Fundamentals
• It all starts with the Student View
• The beginning part of the query is already written (Select DISTINCT...)
• In the Text box below the SELECT statement you can join to other tables.
• The WHERE clause is a condition that will give you all students. You may filter further by adding more such as:
Box A: continues the
Select Statement Box B: Specifies conditions 1 2 3 1 2 3
Query Editor
Pass-Through Parameters
• IS NULL (not blank – there is a Null in the filed)
• IS NOT NULL (there is data in the field)
• =, <>, >=, <= (Mathematical operations)
• BETWEEN (dates, numeric fields, alpha fields)
• LIKE (uses % as a wildcard)
SELECT – retrieves information from a Database and MUST have a
FROM. An * will return all columns of data.
FROM – names the table you are retrieving information from. WHERE – (Filter) conditionally limits your results (result set).
IN( ) – allows multiple values in a WHERE statement
ORDER BY – (Sort) sorts in ascending order the column chosen. AND & OR – filters by more than one condition.
DISTINCT – used in the SELECT statement it returns ONLY
different values.
JOINS
INNER JOIN
• commonly used
• Returns rows that match both tables
Joins are used to reference additional tables in a query, based on a condition or column that both tables have in common.
c d
c d
LEFT OUTER JOIN
• Allows a check for unmatched records
Census Tips
Contact
• Contact table refers to individual contact information • Email, cellphone, contact language
Relationships
• RelatedPair table links personID1 with personID2 • Guardian, Messenger, Portal Flags
• StartDates and EndDates
Households
• HouseholdMember table links on personID • StartDates and EndDates
Let’s Begin
• Access Interchange1
-https://training.ccsappservices.com/campusTraining/ny1.jsp
• User – Interchange • Password – ICny2016
Students without Guardians
Box A
LEFT OUTER JOIN RelatedPair rp ON rp.personID1 = student.personID AND rp.guardian = 1
Box B
Use your new Filter
• Once the Filter is complete, you can access record set • Share with other Campus Users
Individuals in Multiple Households
INNER JOIN householdMember hm on individual.personID = hm.personid
and hm.enddate is null
group by individual.personid, individual.lastname, individual.firstname having count(*) > 1
Box B Box A
Student Emails
Box A
Inner join Contact c on student.personid = c.personid
Box B
Curriculum Tips
Courses
• Trial – locate active calendar
• Course – Course information, linked to calendar • Section – linked to courseID
• Roster – Linked to sectionID
Courses without Sections
Box A
LEFT OUTER JOIN [Section] s ON s.courseID = course.courseID LEFT OUTER JOIN Trial tr on tr.trialID = s.trialID AND tr.active = 1
Box B
AND s.sectionID IS NULL AND course.active = 1
Grading Tips
GradingTaskCredit
• Courses grading setup table
Grading Scores
• V_GradingDetail view pulls all grading score and grading standard tables and links personID, courseID…
GPA
• V_TermGPA – view calculates termGPA by personID, term, calendarID
Transcript
Grading Task Scores
Box A
inner join v_gradingDetail gd on gd.personid = student.personID
Box B
and gd.task = 'Quarter Grade' and score > '95'
Courses without Grading Tasks
LEFT OUTER JOIN gradingtaskcredit gtc ON gtc.courseID = course.courseID AND gtc.calendarID = course.calendarID
AND gtc.taskCreditID IS NULL
Box B Box A
Honor Roll
Box A
LEFT OUTER JOIN [dbo].[v_termGPA] GPA on student.personid = GPA.personid
and student.calendarid = gpa.calendarid
Box B
Custom Data
Box A
inner join CustomStudent cs on student.personID = cs.personID inner join CampusAttribute ca on cs.attributeid = ca.attributeid
Box B
AND ca.element = 'MailRC' and cs.value = '1'
12th Graders with less than 20 credits
Box A
INNER JOIN v_transcriptdetail v on v.personID = student.personID
Box B
AND student.grade = '12'
GROUP BY student.personID, student.lastName, student.firstName,
student.grade, student.studentNumber
Testing Your Queries
Data Viewer
• Turn your Ad-Hoc into a Report • Export and Share
Tips
• Learn SQL (Transact-SQL) • Alias your tables
• Explore and Understand the Schema • Test your results
• Store your Pass Through Filters together • Build on your successes
Two Truths and a Lie
Peter Yustat
• #p12_a1 – I once had a beer with Paul McCartney
• #p12_a2 – I met my wife when I was 12
• #p12_a3 – I have hugged the Stanley Cup - twice
Send a direct tweet of the Lie to @2016icnycgame Sample Tweet: @2016icnycgame #p12_a1
Keep learning!
• From Custom Computer Specialists, Inc. • Online Resources
• http://www.w3schools.com/sql/
•
https://www.khanacademy.org/computing/computer-programming/sql
• https://msdn.microsoft.com/en-us/library/bb510741.aspx
To request a training class contact
