Basic SQL

The reports for sqlReports are based on SQL, so if you're not familiar with SQL, the following should help get you started.  One should also read the sections on using custom fields and intermediate SQL to take full advantage of what one can do with sqlReports.

The following examples contain fields and tables that are PowerSchool fields and tables and one could copy the examples into a SQL client and modify them slightly and make them work on his or her's own system.  Also, parts of the commands are in all uppercase to make them stand out, but you do not have to use that format for your own SQL.

Below is part of a basic SQL statement:

SELECT lastfirst, grade_level, schoolid
FROM students

Different parts of the statement are referred to as clauses.  The select clause is used to specify which fields to include in the report, with the field names separated by commas.  The from clause specifies which table to pull the information from, such as the students table.  The above statement is similar to going into DDE, selecting all students in the database, and then exporting lastfirst, grade_level, and schoolid for each student.

Every SQL statement must have a select and a from clause, however, other clauses can be added to the statement.  For example, to specify search criteria in a SQL statement, a where clause is used.  Let's say instead of all the students in the output, suppose you just wanted the active students.  There's a field in the student's table called enroll_status and active students have a zero in that field and so you would add a where clause like so:

SELECT lastfirst, grade_level, schoolid
FROM students
WHERE enroll_status = 0

If multiple criteria is needed, the parts are separated by and's or or's.  For example, if you wanted the active students in 12th grade, the clause would look like:

WHERE enroll_status = 0 and grade_level = 12

In the where clause you can follow the equal sign by a number, but if it's followed by a series of numbers, you replace the equals sign with the word in (think of it as you're looking for something IN a list of things) and place the numbers in parenthesis.  For example, to find those in grades 8 and 12, you would use the following:

WHERE enroll_status = 0 and grade_level in (8,12)

You can also search based on text, but you must place the text in single quotes.  For example, to find all the student's with the last name of Smith, change the where in the SQL statement above to read:

WHERE enroll_status = 0 and last_name = 'Smith'

To search on a series of text, you replace the equals sign with the word in and place the text inside parenthesis with single quotes around each text item.  For example, to find students with the last name of either Smith or Jones, use the following in the where clause:

WHERE enroll_status = 0 and last_name in ('Jones','Smith')

You can also search on a wildcard with text. In SQL, the wildcard is the %.  However, instead of using an equal sign, you must use the word like.  For example, the following where clause will find all students who have a first name starting with Jo:

WHERE enroll_status = 0 and first_name like 'Jo%'

It's common to see upper(fieldname) or lower(fieldname) in a where clause to account for case differences.  If you're using lower or upper in a where clause, be sure the criteria is in that case as well. For example, the following would be used to find students whose last name begins with the letter A:

WHERE upper(last_name) like 'A%'

In the example above, an uppercase A should be used because if a lowercase a is used you would not receive any results because it would be looking for where A=a.

Another common clause is the order by clause, which is used to sort the output.  It specifies how to sort based on the field(s) listed after order by. Below is an example of sorting the output by lastfirst:

SELECT lastfirst, grade_level, schoolid
FROM students
WHERE enroll_status = 0
ORDER BY lastfirst

The default sort order is ascending, but you can sort in descending order by adding desc after the field name. It's common to use lower(lastfirst) instead of just lastfirst to get the order correct because of different cases.  The lower puts everything in lower case when ordering to keep proper order.

You can also order by more than one field. For example, if you wanted to sort by grade level first, and then name, the statement would read:

SELECT lastfirst, grade_level, schoolid
FROM students
WHERE enroll_status = 0
ORDER BY grade_level, lower(lastfirst)

 

Information From More Than One Table

Sometimes you will want to pull data from more than one table and that's when you 'join' tables together.  For example, if you wanted a student's school abbreviation, you'd have to get that from the schools table because the students table does not mention the school abbreviation anywhere.  Instead, each student has a number in a field called schoolid that relates to the school_number field in the schools table.  When such a relationship between two tables exist, they can be joined together like in the following:

SELECT lastfirst, grade_level, schoolid, abbreviation
FROM students
INNER JOIN schools ON students.schoolid = schools.school_number
WHERE enroll_status = 0
ORDER BY lower(lastfirst)

The inner join makes a connection between two tables on the fields you specify and only those records where the connection is valid will be returned.  In other words, only records in the students table that have a schoolid that exists in the school_number field of the schools table will be returned.  If a student has 1234 in the schoolid field, but that number doesn't exist in the school_number field for any record in the schools table, then that student record will not be listed in the output.

The above though isn't very good syntax because in some cases you may be pulling information from tables with the same field names.  For example, both the students and teachers tables have a field called lastfirst, along with several other identically named fields.  It's better and more efficient to put the table name in front of each field throughout the statement, but to save typing, you can create aliases for each table and use the alias rather than the original name.  To create an alias, you must put your name for the alias right after the reference to the table. In the example above, we'll call students s, and call schools sch.  The revised statement would then read as follows (be sure to note the proper use when using lower or upper with a fieldname):

SELECT s.lastfirst, s.grade_level, s.schoolid, sch.abbreviation
FROM students s
INNER JOIN schools sch ON s.schoolid = sch.school_number
WHERE s.enroll_status = 0
ORDER BY lower(s.lastfirst)

Aliases can be whatever you want them to be, but you must be consistent on their use in the statement.  Often times you'll get errors because you're missing an alias in front of a field or it's the wrong alias.  If you're trying to modify someone else's custom page that uses SQL, be sure to look for possible aliases and their tables.  You can also add aliases after field names. Using the above example, here's how it may look with field aliases as well:

SELECT s.lastfirst Name, s.grade_level Grade, s.schoolid SchoolID, sch.abbreviation SchoolAbbr
FROM students s
INNER JOIN schools sch ON s.schoolid = sch.school_number
WHERE s.enroll_status = 0
ORDER BY lower(s.lastfirst)

Field aliases aren't needed with sqlReports because of the columng heading part.  However, if you're using a SQL client, the field alias will become the column header.  That's especially handy when you're exporting the data and would rather have Name instead of s.lastfirst as the column heading for your export.  Do not use reserved SQL commands as field alias names though or otherwise you'll receive an error.

Here's another example of joining tables. The first statement below pulls the attendance data for a specific student and would give you dates and numbers for the school and attendance code:

SELECT att_date, schoolid, attendance_codeid
FROM attendance
WHERE yearid = 27 and att_mode_code = 'ATT_ModeMeeting' and studentid = 3
ORDER BY att_date

To make it more meaningful, we can join the schools and attendance_code tables to the attendance table and replace schoolid with abbreviation, and attendance_codeid with description:

SELECT att.att_date, sch.abbreviation, ac.description
FROM attendance att
INNER JOIN schools sch on att.schoolid = sch.school_number
INNER JOIN attendance_code ac ON att.attendance_codeid = ac.id
WHERE att.yearid = 2 and att.att_mode_code = 'ATT_ModeMeeting' and att.studentid = 3
ORDER BY att.att_date

Let's add one more item to the output - the course name. Adding the course name gives a good example of the power of SQL.  The course name is held in the courses table, however, there is no connection between the attendance and courses tables.  But there is a connection between attendance and the cc table, and there's a connection between cc and courses, so we can join attendance and cc, join cc to courses, and then pull information from the courses table:

SELECT att.att_date, sch.abbreviation, ac.description, c.course_name
FROM attendance att
INNER JOIN schools sch ON att.schoolid = sch.school_number
INNER JOIN attendance_code ac ON att.attendance_codeid = ac.id
INNER JOIN cc cc ON att.ccid = cc.id
INNER JOIN courses c on upper(cc.course_number) = upper(c.course_number)
WHERE att.yearid = 27 and att.att_mode_code = 'ATT_ModeMeeting' and att.studentid = 3
ORDER BY att.att_date

You can user upper and lower in a join to ensure accuracy.  In the example above, upper is used to make sure records are returned even if they're not the same case in the cc and courses table.  It's possible the course could be ART101 in the courses table, but Art101 in the cc table.  Using upper ensures that the sql statement sees them as the same (ART101 = ART101) and doesn't leave records out because of the difference in case.


Outer Joins

Inner joins are the most common type of joins and sometimes the word inner is left out.  A second type of join is the outer join.  Outer joins are used when you want to list records that may not have something in common with the other table.  For example, if you wanted a list of all the active students at a school and a count of the number of discipline log entries they have in the log table, you'll want to use an outer join.  The reason why is an outer join can list all of the students, including those that do not have any discipline log entries tied to them, whereas an inner join would only list those students with discipline records in the log table as well. 

The following join would only return students with discipline log entries due to the inner join:

SELECT s.id, s.lastfirst, count(*)
FROM students s
INNER JOIN log l on l.studentid = s.id
WHERE s.schoolid = 100 and s.enroll_status = 0 and l.logtypeid = -100000
GROUP BY s.id, s.lastfirst
ORDER BY s.lastfirst

The following would return all students and discipline counts due to the outer join:

SELECT s.id, s.lastfirst, count(*)
FROM students s
LEFT OUTER JOIN log l on l.studentid = s.id and l.logtypeid = -100000
WHERE s.schoolid = 100 and s.enroll_status = 0
GROUP BY s.id, s.lastfirst
ORDER BY s.lastfirst

The left outer join means to return all students (left = the first table reference in the sql statement itself, such as students on the FROM line).   You'll notice the 'l.logtypeid = -100000' is moved up to the join instead of being in the where.  If it was left in the where, it would narrow down the overall results and give you the same results as an inner join.  By moving it to the left outer join, you're applying the criteria to the join instead.

The difference between inner and outer joins is often explained using Venn diagrams.  This web site (CodeProject.com) has a good explanation of the differences using Venn diagrams to help illustrate the joins.

 

  Back to the List of Articles