Think you know your Access SQL? Here is a quick five-question quiz covering some of the basics that every serious Access developer should have down cold: SELECT, FROM, WHERE, ORDER BY, field order, and that little asterisk that seems harmless until it comes back to haunt you.
Give yourself a few seconds to answer each question before checking the answer. No peeking. This is the honor system, and Access knows when you lie. Probably.
Question 1: Which SQL clause is used to sort the records returned by a query?
A. WHERE
B. ORDER BY
C. FROM
D. SELECT
Answer: B. ORDER BY
The ORDER BY clause controls the order in which records appear in your query results. For example, you might sort customers by LastName, invoices by InvoiceDate, or products by Price. It does not change the records in the table. It only changes how Access displays the results.
Question 2: What does the asterisk mean in this SQL statement: SELECT * FROM CustomerT;
A. Return all fields from CustomerT
B. Return only records with an ID value
C. Sort the records alphabetically
D. Return only calculated fields
Answer: A. Return all fields from CustomerT
The asterisk is essentially SQL shorthand for "give me all available fields." So SELECT * FROM CustomerT returns every column from CustomerT.
This can be convenient while you are experimenting or quickly looking at table data. However, in real-world queries, I generally prefer listing the fields I actually need. It makes the query easier to read, avoids returning unnecessary data, and prevents surprises if somebody later adds a new field to the table.
Question 3: What is the purpose of a WHERE clause in a SELECT query?
A. It changes the names of fields in the table
B. It determines the order of returned columns
C. It limits the records returned to those meeting a condition
D. It permanently deletes records that do not match
Answer: C. It limits the records returned to those meeting a condition
The WHERE clause is where you put your criteria. If you only want customers in New York, unpaid invoices, active employees, or orders placed this month, WHERE is what narrows down the records.
A SELECT query with a WHERE clause does not delete anything. It simply shows you the records that meet your condition. SQL can certainly delete records, but that requires a DELETE query, which is a whole different animal and one you should approach with appropriate respect and possibly a backup.
Question 4: In a SQL Row Source for a combo box, what determines the order of the columns returned by the query?
A. The order of fields in the source table design
B. The field used in the ORDER BY clause
C. The order in which fields appear after SELECT
D. The combo box's Tab Order property
Answer: C. The order in which fields appear after SELECT
This one catches a lot of people. ORDER BY controls the order of the records, vertically down the list. The fields after SELECT control the order of the columns, horizontally across the query.
That matters a lot with combo boxes. If your Row Source begins with CustomerID and then FullName, the first returned column is CustomerID and the second is FullName. Your Bound Column and Column Width settings depend on that sequence. Rearrange the SELECT list, and you may suddenly bind the wrong value without realizing it. Fun times.
Question 5: Assume every field comes from CustomerQ and none of the field names contain spaces. Which edit can shorten this SQL without changing its results?
SELECT [CustomerQ].[CustomerID], [CustomerQ].[FullName] FROM CustomerQ;
A. Remove CustomerID from the SELECT list
B. Remove the FROM CustomerQ clause
C. Replace SELECT with ORDER BY
D. Remove the CustomerQ prefixes and square brackets
Answer: D. Remove the CustomerQ prefixes and square brackets
If every field in the query comes from the same source, Access can usually identify the fields without repeating the table or query name in front of each one. And if the field names are simple names without spaces or special characters, brackets are optional too.
So the shorter version can simply be: SELECT CustomerID, FullName FROM CustomerQ;
There are times when table prefixes and brackets are important. Prefixes are especially useful when joining tables that have similarly named fields, and brackets are necessary when names contain spaces or reserved words. But when they are not needed, removing the clutter makes your SQL much easier to read.
If you got all five right, congratulations, you have successfully navigated the SQL energy cloud. If not, no worries. These are exactly the kinds of little SQL details that become second nature once you start building more queries, forms, combo boxes, and reports in Access.
Watch the embedded video if you want to take the quiz along with the timer. These SQL topics and plenty more are covered in Access Expert Level 3, Lesson 1.
Live long and prosper,
RR
No comments:
Post a Comment