SQL Query Structure, Access & Excel Data Analysis for Students

What is the structure of a SQL query?

SELECT fields FROM table(s) WHERE criteria GROUP BY fields HAVING aggregate_criteria ORDER BY fields;

When should you use the WHERE clause in SQL?

To filter table-level fields before grouping.

When should you use the HAVING clause in SQL?

To filter aggregate results after GROUP BY.

What is the SQL date format in Access?

#MM/DD/YYYY#

What is the SQL text format in Access?

"Text"

What does the DISTINCT keyword do in SQL?

Removes duplicate values in a field.

What does the IN operator do in SQL?

Matches any value in a list.

What does the NOT IN operator do in SQL?

Returns all values NOT in a list.

What is the purpose of the LIKE operator in SQL?

Pattern matching.

What does the wildcard * mean in SQL?

Any number of characters.

What does the wildcard ? mean in SQL?

Exactly one character.

What does ORDER BY ASC do?

Sorts results in ascending order.

What does ORDER BY DESC do?

Sorts results in descending order.

What does COUNT(*) mean in SQL?

Counts all records.

What are aggregate functions in SQL?

SUM(), AVG(), MIN(), MAX(), COUNT()

What is the GROUP BY rule in SQL?

Any non-aggregate field must appear in GROUP BY.

What is the SQL Join pattern?

FROM table1 INNER JOIN table2 ON table1.key = table2.key.

What is a Primary Key?

Uniquely identifies each record.

What is a Foreign Key?

Primary key from another table used to form relationships.

What is a Table in a database?

Collection of fields related to a subject.

What is a Record in a database?

A row in a table representing one data item.

What is a Field in a database?

A single data element (column).

What are Database Objects?

Tables, queries, forms, reports, macros, modules.

What is Referential Integrity?

Ensures consistency between tables when keys change.

What is Cascade Update?

Automatically updates foreign keys when primary key changes.

What is Cascade Delete?

Automatically deletes related records.

What does Null mean in a database?

Missing/blank value in a field.

What is the purpose of the Unmatched Query Wizard?

Finds records with no match in a related table.

What must a Foreign Key match?

The primary key in the related table.

What does a 1-to-Many Relationship symbol look like?

1 → ∞.

What is the first rule of query design?

Do NOT add extra tables.

What is the second rule of query design?

All tables in a query must be related.

What is the order of Design View in Access?

Tables → Fields → Sort → Criteria → Show → Run.

What is an example of a Calculated Field in Access?

NewField: [Field1] + [Field2].

What is the purpose of the COUNTIF function?

Counts cells that meet a specific criterion.

What does the PMT function calculate?

Calculates the payment for a loan based on constant payments and a constant interest rate.

What is a Pivot Table used for?

To summarize data by different categories.

What is the symbol for a Primary Key?

1.

What is the symbol for a Foreign Key?

∞ (many).

What is a Many-to-Many Relationship?

Occurs through two one-to-many joins.

What is the first step in the data analysis task?

Build E1 / E2 queries in Access.

What is the last step in the data analysis task?

Write a 2-4 sentence summary of comparison.