Useful Queries

Table of Contents

CLASS ROSTER
LIST OF MAJORS AND ADVISERS
DEGREE LISTS
DEGREE LIST FOR COMMENCEMENT CEREMONIES
SCHEDULE OF CLASSES
ENROLLMENT STATISTICS
COURSE SUMMARY
TEACHING SCHEDULE REPORT
UNOFFICIAL TRANSCRIPT
CALCULATING A DEPARTMENTAL GPA

SELECTING STOPOUTS USING SUBQUERIES

Download these for use in GQL

You can download these queries and they will automatically be available for use when you select from the GQL menu Query->Load Query. They are identified in the list of queries with a pad-lock icon and a leading underscore ("_") character. If you have problems, consult the appropriate contact person for your operating system (Mac or PC).

Download for the PC

Download for the Mac

Function: To duplicate the class roster provided by the registrars office.

ALL TERMS (2) MODEL

Person Object

Select Last Name (sort 1)
LFM Name
Student ID-Function: Count Distinct
Confidentiality Flag

Qualify Term = x
Select Class Standing
All Majors

Qualify Include in Enrollment = Y
Qualify & Select CRN = x
Select Grading Option
Registration Status Code

Function: To count majors, to set up office advising files, to check adviser names.

ALL TERMS (1) MODEL

Person Object

Select Last Name (sort 1)
LFM Name
Student ID
Confidentiality flag

Qualify All Majors contains | x |
Student Level Code = UG
Hours Registered > 0
Select Class Standing
Major1, Major2, Major3, Major4
Primary Adviser Name

Note:
For minors, switch majors to minors and drop adviser name
For email addresses, also select email address in the Person Object

Function: To verify awarded degrees.

ALL TERMS (2) MODEL

Person Object

Select Last Name (sort 1)
LFM Name
Student ID

Qualify Degree Term = 199xxx
Degree Status = AW
Degree Level = UG
Qualify & Select
All Degree Majors contains | x |
Select Degree Code
Degree Department Honor Description
Degree Date
Confidentiality Flag

For all graduate degrees:

Same except Last Name = sort 3
Degree Date = sort 1
Degree Code = sort 2
Degree Level = GR
Degree Term qualify (equal to or greater than 199301)

No need to select All Majors or any Honors info

Function: To identify students eligible to participate in June ceremony, and to create list and labels. To be run spring term, after degree application deadline.

ALL TERMS (1) MODEL

Person Object

Select Last Name (sort 1)
LFM Name
Student ID

Qualify Address code
Select Address Line 1
Address Line 2
Address Line 3
Address Line 4

Fall:

Qualify Degree status = AW
Select and Qualify All Degree Majors contains | X |
Degree Term = 199x01

Winter:

Qualify Degree status = AW
Select and Qualify All Degree Majors contains | X |
Degree Term = 199x02

Spring:

Qualify Degree status in AP,CP,AW
Select and Qualify All Degree Majors contains | X |
Degree Term = 199x03

Qualifying Address Code: Use RD or PR address for fall and winter grads, MA for spring applican'ts.

Function: For schedule planning. To supplement the Course Edit Report.

ALL TERMS MODEL (2)

Schedule Object DRIVING TABLE

Qualify Dept Code
Term
Section Status Code = A
Select in order
Subj Code
Course Numb (sort 1)
Title
CRN
Spec Approval Code
Credit Hours Min
Credit Hours Max
UO Grading Mode Code
Major Grading Mode Code
Begin Time
End Time
Room Number
Building Code
Weekdays
Instructor Names
Fee Amounts
Enroll Max
Enroll Actual

Function: For up to date enrollment statistics. Easier to print than SSASECQ.

ALL TERMS MODEL (1)

Schedule Object DRIVING TABLE

Qualify Dept Code
Term
Section Status Code = A
Select Cross List (sort 1)
Subj Code
Course Numb (sort 2)
Title
Instructor (pipes removed)
CRN
Spec Approval Code
Enroll Max
Enroll Actual
Seats Available
Waitlist Count

Results: Show as report.
Subtotal the cross list codes--"none" for all columns except "Enroll Actual"

Function: To compare various characteristics (time, days, room, actual enrollment, fee amounts) for a course or courses over several terms.

ALL TERMS (2) MODEL

Schedule Object DRIVING TABLE

Qualify Department Code
Term = x
Select Term (sort 2)
Course Number (sort 1)
Title
Begin Time
End Time
Days
Room Number
Building Code
Instructor Name
Actual Enrollment
Fee Detail Descriptions
Fee Amounts

Combining Results

1. Create first query (Teaching Sched 1). Use the Schedule object. This query brings in information for (a) courses that are non cross-listed and for (b) the 400-level cross-listed courses.

Schedule object

 
Select
SUBJ CODE
Course Number
Title
CRN
Credit Hours Min
Credit Hours Max
UO Grading Mode Code
Begin Time
End Time
Weekdays
Building Codes
Room Number
Instructor Names
Enroll Proj
Section Status Code
Cross List Code (Sort 1)
Enroll Actual
Qualify
 
TERM = term prompt AND
Credit Subject = subject code prompt AND
((Course number < 500 AND Cross List Code IS NOT NULL) OR (Cross List Code IS NULL))
After building and testing, save query as Teaching Sched 1.

 

2. Create second query (Teaching Sched 2). Again, use the Schedule object. This query brings in information unique to the 500-level cross-listed courses.

Schedule object
 

Select:
Course Number
CRN
Enroll Proj
Cross List Code
Enroll Actual
Sort:
Cross List Code
Qualify
TERM = term prompt AND
Credit Subject = subject code prompt AND
Course number >= 500 AND
Cross List Code IS NOT NULL
After building and testing, save query as Teaching Sched 2.
 
3. Run Teaching Sched 1 and then run Teaching Sched 2 by loading and submitting.

 
4. Choose Combine Results (Join Columns) from the Results menu. Join on Cross List Code.

Join Columns:
 

Left Results Set Right Results Set

Query Results 2 Query Results 1

 
Column to Join Column to Join

Cross List Code Cross List Code

"Include unmatched rows" selected.

 

5. Sort results (choose Filter, Sort from the Results menu). Sort by Subject Number, CRN. Save the query (it will be a super query).

 

6. Choose Page Setup from the File menu and choose landscape orientation. You might want to change the report default font (choose Preferences from the Edit Menu).

 

7. Choose Show as Report (GQL Standard) from the Results menu. Edit report, saving report specs every so often.

 --reorder columns (1^st un-order all)
--resize, rename and combine columns
--rename, title

Note: You can't edit super queries. Thats why we saved the first two simple queries so that if we needed to change the super query, we can at least deconstruct it by editing the simple queries and then recombine and save as a new super query.

Select the All Terms (2) Model.

Transcript Object
 

Select Transcript Term (sort)
Subj Code
Course Numb
Title
Credit Hours
Grade
Transfer Subj Code
Transfer Course Numb
Transfer Title
Institution Desc

Select FML Name
Qualify Student ID (you may insert prompt, if desired)

1. Choose "Show as Report" from under the "Results" pull-down menu.
2. Choose "GQL Standard".
3. Choose "Reorder Columns..." from the "Reports" pull-down menu.
4. Highlight "FML Name" and click on the "First" button. Click OK button.
5. Select the "FML Name" header in the report.
6. Select "Suppress Duplicates" from the "Report" pull-down menu.
7. Select "Column Settings" from the "Report" pull-down menu.
8. Choose the "Band Column" and "Before" options.
9. To narrow the column widths, first adjust the titles of most columns, then click on the column and then drag the right column border to the left to make the column narrower.
10. You can also click on the side of the "Title" column to re-size it by dragging.
11. Double click on the "Query Results..." header, then click "Edit Text...".
12. Enter a new title, then click "OK" twice.

BY BUILDING A COMPUTED ATTRIBUTE

In addition to the attributes offered by the Student Data Warehouse in each of its objects, you can add your own custom-built attributes based on the attributes already available. You can save these "computed attributes" so you can use them for future queries. When you create an attribute it will be available to you but not to other users.

In this example, let's say you want to create a computed attribute that will calculate a GPA based on selected courses (say, all undergraduate courses with a "PS" subject code). Your plan for calculating the GPA is to use this fraction:

• the sum of all grade points for selected courses divided by

• the sum of all GPA hours for selected courses

1. Go to the All Terms (1) model.
2. Open the Transcript (1) object.
3. Now you need to decide where you want your new attribute to appear on the attribute list. Once you've decided, pick the attribute immediately above where you what the new attribute to appear and using its function box select the "calculation..." function. (After you have finished defining your new attribute, it's name will appear directly beneath the attribute you have selected.)
4. You should now see the Edit Calculation window. Notice the Expressions box. This is where you build and edit your computed attribute. Click once on the box to begin (you should get a blinking cursor in the box). You can build an expression by a combination of typing, instating attributes, inserting functions, and/or using the keypad buttons.

Sort by building the numerator (see our formula above). Select the Grade Points attribute from the scrolling Table/Attributes window by clicking on it once. Press the Insert Function button and select SUM from the list on the left. Press the Insert button. (We could type in the attribute and function instead of inserting them, but its much easier to just pick from a list and press insert).

The expression

(SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GRADE_POINTS))

should now appear in the expression box. That's the numerator. If it doesn't look right, just delete everything in the Edit Calculation window and do step 4 over again.
 

5. Now you need to put in the division operator. In the expression box move the cursor between the last two parentheses and press the "/" button.

The expression should now look like this:

(SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GRADE_POINTS)/)
 

6. Now for the denominator. Make sure the cursor is blinking right after the / you just inserted. Select the GPA Hours attribute from the scrolling Table/Attributes window by clicking on it once. Press the Insert Function button and select SUM from the list on the left. Press the Insert button.

 
7. Your new attribute has been defined. The expression should now look like this:

(SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GRADE_POINTS) / SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GPA_HOURS))

If you've made a mistake, edit the expression in the Edit Calculation window until it looks exactly like the expression above.
 

8. Type in a name for your new attribute in the Name: field (e.g., "Grouped GPA"). Press the OK button and your new attribute will appear on the attribute list.

How do you use your new attribute? Let's say you want to produce a report of all current political science majors including their names, ids, and GPAs based just on their political science courses.

Here's how you do it:

1. Go to the All Terms (1) model.
2. Select New Query from the Query menu.
3. Open the Person (1) object.
4. Select Student ID and click its group box.
5. Select LFM Name.
6. Close the Person (2) window and open the Transcripts (1) object.
7. Select the new Grouped GPA attribute.
8. Qualify Count in GPA Flag = "Y".
9. Qualify SUBJECT CODE = "PS"
10. Qualify GPA Hours > 0 (GPA Hours is in the denominator and we can't divide by zero).
11. You want only UO courses to be counted in the GPA, so qualify Inst Transfer Flag = "I".
12. Qualify Course Level = UG (just undergrad courses).
13. Close the Transcript (1) window and open the Current Students object.
14. Qualify Hours Registered > 0.
15. Qualify All Majors contains |PS|.
16. Qualify Level Code = "UG" (just undergrad students).
17. Run the query.

Note that you grouped on ID because you wanted a separate GPA for each student. You also made sure that the course counted in the GPA and that only PS courses were used in the calculation. And you made sure that GQL wouldn't have to divide by zero. You qualified on Course Level because GPAs should always be calculated by level. And finally, you used the Current Student object to only bring back GPAs for currently enrolled political science majors.

By changing the selection, you can calculate a GPA on other groups of courses making this a very handy attribute! The above query took about 30 minutes to execute.

We'll define a "stopout" as an undergraduate student who attended last term but who is not attending this term and who did not graduate. In our example, our list will be limited to Anthropology majors.

You'll use one subquery to eliminate from our list students who have graduated and you'll use another to eliminate students who are attending this term.

In the first subquery, you need to identify every student who has had a degree awarded at the UO. Then you'll use a NOT IN qualifier in our main query.

To define the first subquery:

1. Go to the All Terms (2) Model.
2. Select New Query from the Query menu.
3. Open the Degree (2) object.
4. Select Warehouse ID.
5. Qualify Inst Transfer Flag = I.
6. Qualify Degree Status = AW.
7. Qualify Level Code = UG.
8. Select Save Query from the Query menu.
9. Type a descriptive name for the query (like "UG Degrees Awarded") in the Query Name field.
10. Click save.

To define the second subquery:

1. Select New Query from the Query menu.
2. Open the All Students object.
3. Select Warehouse ID.
4. Qualify Hours Registered > 0.
5. Qualify TERM = the current term (e.g., 199602).
6. Qualify Level Code = UG.
7. Select Save Query from the Query menu.
8. Type a descriptive name for the query (like "Current UGs") in the Query Name field.
9. Click save.

To define the main query:

1. Select New Query from the Query menu.
2. Open the Person (2) object.
3. Select STUDENT ID and LFM Name.
4. Close the Person (2) window and open the All Students object.
5. Qualify TERM = last term (e.g., 199601).
6. Qualify Student Level Code = "UG".
7. Qualify Hours Registered > 0.
8. Qualify All Majors contains |ANTH|.
9. Now you need to use your first subquery to eliminate the students who have a degree. To do this first, qualify on WAREHOUSE ID and choose the NOT IN qualifier.
10. Select Insert Subquery from the Edit menu.
11. In the Saved Queries list, select the subquery "UG Degrees Awarded" that we created above. Press the Load button and the query name will appear in the qualification box.
12. Next you need to use your second subquery to eliminate currently enrolled students. Qualify WAREHOUSE ID again and choose the NOT IN qualifier.
13. Select Insert Subquery from the Edit menu.
14. In the Saved Queries list, select the subquery "Current UGs" that you created above. Press the Load button and the query name will appear in the qualification box.
15. Run the query.