InterSystems IRIS SQL Specialist

InterSystems IRIS, InterSystems IRIS for Health, SQL, Relational Tables, Indexing, Query Plans

The InterSystems IRIS SQL Specialist exam is designed to validate core skills for developers who design and manage InterSystems IRIS SQL applications and operations.

Purchase Certification Exam

Exam Details

Target Exam Role

An IT professional who uses InterSystems IRIS SQL to:
- design InterSystems IRIS SQL applications,
- manage InterSystems IRIS SQL operations,
- and load and efficiently query datasets in InterSystems IRIS SQL.
At least 1 to 2 years of experience designing and managing InterSystems IRIS SQL applications, or expertise in other SQL platforms and has 1 year experience with managing InterSystems IRIS SQL applications.

Recommended Preparation

Basic experience with standard ANSI SQL.
Complete all of the online courses within the Using SQL in InterSystems IRIS learning path, as well as experience searching InterSystems Documentation.
Review the set of practice questions found here.

Exam Parameters

Number of questions: 47
Time allotted to take exam: 2 hours
Passing score: 74%
Language offered: English

Exam format: multiple choice and multiple response. Access to InterSystems IRIS Documentation will be available during the exam.

Note: This exam has a 2-hour time limit. While InterSystems documentation will be available during the exam, candidates will not have time to search the documentation for every question. Thus, completing the recommended preparation before taking the exam, and searching the documentation only when absolutely necessary during the exam, are both strongly encouraged.

Exam Topics and Content

The exam contains question items that cover the areas for the stated role as shown in the KSA (Knowledge, Skills, Abilities) chart immediately below.

TOPIC/KNOWLEDGE	SECTION/SKILLS	ABILITIES
T1 Designs InterSystems IRIS SQL applications	T1.1 Designs a SQL schema	Identifies role of bitmap extent index Determines use cases for indexes Distinguishes use cases for different index types Distinguishes primary, unique, and ID keys Identifies properties for foreign key relationships Identifies behavior of referential actions for update and delete Identifies tradeoffs for adding ever more indexes Identifies use cases for composite indexes
	T1.2 Designs advanced schemas	Distinguishes the differences between date/time data types
	T1.3 Loads data	Uses LOAD DATA command Uses SQL diagnostics tables (%SQL_Diag) Identifies options for exporting data (physical vs logical)
	T1.4 Writes business logic	Defines stored procedures Defines triggers Identifies language options for writing business logic
	T1.5 Develops object/relational applications	Recalls default object/relational mapping Recalls SQL best practices when defining classes
	T1.6 Deploys SQL applications	Lists mechanisms for deploying SQL applications Determines what needs to be part of a deployment
T2 Uses InterSystems IRIS SQL	T2.1 Manages InterSystems IRIS query processing	List considerations made by the optimizer Differentiates syntax and runtime errors Uses Statement Index to find statement metadata
	T2.2 Interprets query plans	Identifies ways to view query plans for a statement Identifies Full Table Scans in a query plan Identifies the use of indexes in a query plan Distinguishes between loops and lookups in a query plan Distinguishes modules called once vs repeatedly in a query plan Recalls role of table statistics in query planning Uses hints to troubleshoot query planning Identifies opportunities for indexes, based on a query plan Recalls the meaning of relative cost
	T2.3 Uses InterSystems IRIS SQL in applications	Defines InterSystems IRIS connections Identifies prepare/execute steps Recalls how proper use of parameters can guard against SQL injection Leverages explicit transaction controls effectively
	T2.4 Uses InterSystems IRIS-specific SQL capabilities	Uses SelectMode appropriately Uses arrow syntax for implicit joining
	T2.5 Uses transactions effectively	Determines appropriate transaction boundaries Uses CommitMode appropriately Recalls impact of large or long-running transactions
T3 Manages InterSystems IRIS SQL operations	T3.1 Manages SQL operations	Uses SQL Process view to monitor SQL activity (2022.2+) Gathers table statistics Uses the runtime stats in the Statement Index to find statements with optimization opportunities
	T3.2 Manages SQL Security	Assigns SQL privileges to users and roles Differentiates SQL privilege checking and application security Identifies impact of enabling SQL auditing
	T3.3 Uses PTools for advanced performance analysis	Distinguishes between performance metrics "time spent", "global references" and "commands executed"