Query construction in the SQL assessment

What it covers

Writing correct SELECT statements from a plain-English request: filtering, expressions, NULL handling and knowing the order in which a query's clauses are evaluated.

Topics in the practice set for this area

• filtering
• execution order
• null handling
• query writing
• expressions

How it is tested

In the timed assessment, questions in this area use these formats: code you write, matching, multiple choice, put the steps in order, short answer. Each attempt draws its own set of questions, and this area gets its own sub-score on the credential when the attempt includes enough questions from it. The assessment questions themselves are never published; the practice set below is written separately for study.

What to practice

• Write the logical evaluation order of FROM, WHERE, GROUP BY, HAVING, SELECT and ORDER BY from memory, then check it.
• Rewrite filters that compare against NULL and predict what each returns before running it.
• Take a small schema and turn five one-sentence business questions into queries without looking anything up.

Try a practice question

From the free practice set (7 questions in this area). Answers and explanations are in the practice set.

• Which SQL keyword removes duplicate rows from a result set?
• A query uses GROUP BY, SELECT DISTINCT and LIMIT 10. Put these steps in the order the database logically applies them.
• `products` has some rows where `price` is NULL. Which rows does `SELECT * FROM products WHERE price > 100 OR price <= 100` return?

More SQL study guides

• How to Pass the Provyn SQL Assessment
• Joins & set logic
• Aggregation & windows
• Performance & indexing
• Transactions & correctness