Fundamentals

Question 1

Chronicles

Answer 1

Post-relational database

Question 2

Clarity

Answer 2

1. Is the relational database

2. The process of moving production data to this relational database (ETL process)

Question 3

Operational Reporting

Answer 3

Time sensitive / relatively small / Accessible in Hyperspace / Actionable (used as part of a workflow)

Question 4

Operation Rptg Tools

Answer 4

Application reports: DAR, ASAP Track Board,
Reporting Workbench: flexible, more report like

KB-SQL: mainly used in the Clarity extract process
Chronicles Ad-Hoc: available in Text only
Cache Script: programming language on which Chronicles is built

Question 5

Analytical Reporting

Answer 5

Comprehensive, Summarized, Standardized. A year, a month, many departments… trending

Question 6

What is the main difference between grouping in SQL and grouping in Crystal Reports

Answer 6

SQL’s Group By collapses the results into groups, no longer displaying the details.

Question 7

Aggregate functions perform a specific operation over all rows in a group.

Answer 7

COUNT(*) - Counts all rows
COUNT(value) - counts all non-null values
AVG(value) - averages all non-null values
MAX(value) - returns highest value
MIN(value) - returns lowest value
SUM(value) - returns the sum (total) of all non-null values

Question 8

In Crystal why would you use ‘Show SQL query’?

Answer 8

To verify two things:

1. All the record selection criteria is in the WHERE clause.
2. SQL syntax makes sense.

Question 9

Why do NULLs exist in Epic?

Answer 9

1. fields are not required
2. data in one table may be more up to date than another
3. poorly or incorrectly formatted data may be deleted, resulting in a null.

Question 10

IsNull( ) returns True if the field contains a null value. Why is it handy?

Answer 10

Use it to give nulls a default value, *No Attending Provider If… Then… Else

Question 11

What are two ways of handling null values?

Answer 11

Assign a default value.

Filter them from your report.

Question 12

If you are using a function or operator and want more information about it, what are two ways to open Crystal Reports online help?

Answer 12

F1 and the ? button.

Question 13

True or False: a clarity table with only one column listed as the primary key will always hold no-add, single-response data.

Answer 13

False. ID (no-add,single) or CSN (overtime, single)

Question 14

Your clarity ETL administrator has created a custom Clarity column in your environment to extract a previously unextracted item. Which tool(s) can you use to look up information about that clarity column?

Answer 14

Clarity Compass

Question 15

What is the difference between a foreign key and a primary key?

Answer 15

A foreign key serves as a link between two tables. A primary key is a unique identifier.

Question 16

If you include multiple database objects in your report and do not link them Crystal will attempt to combine every row in the first table with every row in the second. The result is this…?

Answer 16

Cartesian product

Question 17

What are the Data Types in SQL…

Answer 17

VARCHAR, NUMERIC, DATETIME, FLOAT (cdr is the only float date), INTEGER (category)

Question 18

SQL queries are written with their clauses in what order?

Answer 18

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

Question 19

SQL query clauses are processed in what order?

Answer 19

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

Question 20

On the SQL Editor what are two ways to run your query?

Answer 20

F5 or by clicking the Execute! button.

Question 21

UNION or UNION ALL are used for what purpose?

Answer 21

Combine two separate groups of results. Buy default they return only distinct values. Use UNION ALL to allow duplicate values.

Question 22

True/False: After inserting a Command object you can add additional tables in Crystal.

Answer 22

False. Crystal will often perform a Cartesian product of the results and filter it locally.

Question 23

True/False: A Command Object always makes your report run faster!

Answer 23

False. It does give you more control over what actions are performed on the server.

Question 24

True/False: Formatting doesn’t matter when pasting a SQL query into a command object.

Answer 24

True

Question 25

What feature can be added to a SQL query using a command object from Crystal Reports?

Answer 25

Parameters

Question 26

SQL subqueries can be contained in what clauses?

Answer 26

SELECT, FROM and WHERE

Question 27

True/False: A correlated query can slow down your report.

Answer 27

True. Correlated subqueries are executed once for each row in the parent.

Question 28

Using a subquery in the FROM clause, is often referred to as an…

Answer 28

Inline View. Most useful when returning multiple rows and columns

Question 29

Each named query in the WITH clause is called a…

Answer 29

Common Table Expression (CTE)

Question 30

If you have multiple subqueries in the FROM clause of your PARENT query, what do you need to do to combine them?

Answer 30

Join them together, as you would tables in the FROM clause.

Question 31

What are the three conditions for using UNION and UNION ALL?

Answer 31

Have to have the same # of columns.
Have to be similar data types.
Columns have to be in the same order.

Question 32

True/False: Grouping in SQL is equivalent to grouping in Crystal?

Answer 32

False. SQL summarizes the group, eliminating the details.

Question 33

What keyword can you use to change the direction of a sort in SQL?

Answer 33

DESC (ascending is default)

Question 34

Epic Standard SQL Functions (EFN_)

Answer 34

A library of User Defined Functions (UDFs) developed and released by Epic. Make their SQL code database independent.

Question 35

True/False: All functions require the use of parenthesis, regardless of whether or not they take any arguments.

Answer 35

False. CURRENT_TIMESTAMP !!

Question 36

SP_HELPINDEX

Answer 36

Will pull the list of indexed columns for a given table.

Question 37

True/False: You can put aggregate functions in any clause.

Answer 37

False. The WHERE clause does not handle aggregate functions, you must add a HAVING clause