Access Exam #2

Table

Group of fields used to describe one entity

Which SQL command normally includes the use of logical operators?

WHERE

Which SQL statement is used to filter my data?

WHERE

key to designing queries that generate outputs is understanding the proper business logic, then translating that business logic into the DBMS using the proper physical design steps

We will work both on 1. Logic - so that you can apply your learning when using any software package of an employee or client 2. Query Design Mechanics - so you can test your logic skills in this class

(T/F) If in my where statement, I say I want only numbers "between 1 and 10", my output will include the number 1 and the number 10

True "between" includes the numbers you give as parameters

Queries do more than just answer questions about data. Which type of query can make changes to records from one or more tables?

Update query

LIKE

Used to test whether or not text matches a pattern

data dictionary

information about the structure of the database, including a description of each data element

parameter query

prompts the query user for information to be used in executing the query. For example, use this type if you want a query that displays only the order ids for the customer id that the user inputs

In this class, what type of queries will we generally be creating?

select and total queries primarily, along with a few parameter queries. We will input them into access using the QBE grid

append query

to add records from one or more tables to the end of one or more other tables

make table query

to create a new table using data from one or more existing tables

delete query

to delete records from one or more tables, including using a criteria to specify which rows should be deleted

update query

to make changes to records from one or more tables, including using a criteria to specify which rows should be updated

In this course, we will use SQL for logic discussion, then translate that logic into the QBE

true

select query

use to view data from only certain fields in a table, review data from multiple tables simultaneously or pull data based on certain criteria

crosstab query

used to calculate (sum, average, count, etc.) data that is grouped by two types of information - one down the left side of the datasheet and one across the top. The cell at the junction of each row and column displays the results of the query's calculation. For example, if you wanted to review product subtotals, but you also want to aggregate by month, so that each row shows subtotals for a product, and each column shows product subtotals for a month.

Query logic steps

Select (fields/columns) Find (Tables or query views) Where

NOT

Selects all records that do not match the criteria

Is Null

finds records with no value

>

greater than

*

implies wildcard

The asterisk (*) is a wildcard operator that takes the place of many characters.

True

What type of joins gives me all records from both tables regardless of if they match?

Full Outer Join

Inner join function (equi-function)

Matches up records between two tables based on designated values. The result set that you get contains the rows from the table on the left that match the table on the right. If there are rows in either table that don't match, they aren't returned in the results set.

<

less than

<>

not equal to

OR

Either condition in the criteria must be met

totals query

(a variation of a Select query) - You can use the Totals row in a basic Select query, but a Totals query allows you to ask more complex questions by grouping and summarizing data via the Sum function (an aggregate function).

Two approaches to constructing access queries:

1. SQL - the user writes structured language commands (line by line) to input queries in the following format: - select (fields/columns) - from (tables or query views) - where (criteria for selecting rows or tables/views) 2. Query by example (QBE) - a graphical database query language for relational databases. Rather than having users write SQL line commands, QBEs provide a graphical front end consisting of visual tables and a grid where the user enters commands, example elements and conditions. Behind the scenes, the DBMS software convert the users actions into statements expressed in a database manipulation language, such as SQL.

Sample Query Types

1. select query 2. totals query 3. parameter query 4. crosstab query 5. delete query 6. update query 7. append query 8. make-table query

Physical query design in access involves up to a ___ step process. You need to consider each design and step for each query. For simple queries, some steps may require no action. Reminder - Perform physical design only AFTER you have completed logic steps.

9

Primary key

A field (or group of fields) that uniquely identifies a given entity in a table

Typical logical indicators:

AND OR NOT LIKE BETWEEN * > <>

AND

BOTH conditions in the criteria must be met

You need to begin queries offline

Begin by making sure you understand the question being asked Then, you can follow the three step logic process described on the next two slides Once you have your logic planned, you can use it to guide your QBE input

To a computer, the value of TRUE and "TRUE" are the same thing.

False

(T/F) To a computer, the value of True and "True" mean the same thing

False the value of true is a system value, like yes or no "true" is a text string

In my select statement, I put the ____ that I want to see or use in my query.

Fields

Query logic step 2

From (Tables or query views) Identify which table(s) or query view(s) are the sources of the desired fields/data items that you identified during logic step 1. *If you need multiple tables/views, determine how they will be linked. (Do you need to add a table to enable linking using a common field???)

If I want to see all matching records between two tables, I need a _______ join.

Inner

Types of join functions:

Inner join (Access calls this an equi-join) Left join Right join Full outer join *Union

Which type of join will provide a designer with the records that match from both tables?

Inner join and Equi-join

BETWEEN

Often used for dates in a criteria

Which type of query prompts the end user for input?

Parameter Query

Queries that require more than one table often require you to think about how you need to link the tables (From query logic step)

Recall that relational databases include common fields in more than one table to enable table linking, or joining of tables. When you link or combine database tables, you have options regarding the type of join. Understanding joins is important for using databases to design queries, and when accessing databases using analytical tools such as Tableau or Power BI. The type of join you need depends on the logic of your query (what data you want to include in the output). Typical join types include inner join, left join, right join, and full outer join.

A ______ describes record field names, the width of each field, and the type of data stored in each field (E.G., Numeric, Alphanumeric, Date)

Record layout

Query logic step 1

Select (fields/columns) Make a list of the fields you need to include in your query. The fields you will need tend to fall into the following categories: - fields you want to display in your output - fields you need to create calculations or expressions - fields you need to use for logic purposes (e.g. in where criteria) *in some cases, if you are creating a query that will be used as input to a second query, think about whether you need to add a field as query 1 output to enable proper linking when designing query 2

The combination of the Order and OrderLines table make which source document?

Sales Order

Full outer join

The full outer join is asking for every row from the table on the left and every row from the table on the right. If they have matching values, the computer matches them up. If they don't have matching values, the computer will still display each row, but show nulls where they don't match.

Left join

The left join is asking for all the rows from the table displayed or listed on the left and, if there's a match in the table on the right, match it up. When a value in the left table doesn't have a corresponding match in the right table, you see a null value in the input.

Right join

The right join is asking for all the rows from the table displayed or listed on the right and, if there's a match in the table on the left, match it up. When a value in the right table doesn't have a corresponding match in the left table, you see a null value in the data grid.

Union

Though union is not a type of join, union is another method for combing two or more tables by appending rows of data from one table to another. Ideally, the tables that you union have the same number of fields, and those fields have matching names and data types.

Computers differentiate between data types such as text or strings, different types of numbers, dates, video, etc.

To a computer, "1" is different than 1 (something you likely noticed when learning spreadsheets). "1" denotes text or string , 1 denotes a numeric value. "TRUE" denotes text or string, TRUE is a logical system value. Also be advised that computers are picky about spelling and and can be case (uppercase, lowercase) sensitive. In some packages, like Access, the software tries to avoid these issues by helping the user. When entering WHERE criteria, you will need to pay attention to data types to make sure you are using the proper method to enter a criteria filter.

If I want to filter the data that I see in my query output, I should use which statement to do so?

Where

Query logic step 3

Where Decide if you need to input any criteria that limits the query view output to only records that meet your condition(s). You will use criteria terms such as AND, OR, BETWEEN. The Where criteria limits the rows/records selected from the tables/query views. Add the fields and tables needed for criteria to your lists created in steps 1 and 2. How we filter our output, our criteria

SQL (Structured Query Language) is actually a standardized query language that can be used to accomplish many tasks (not just ask questions)

You can use SQL commands to do everything from creating databases, creating schemas, creating table structures, updating table structures, deleting tables, joining tables, adding or updating data, adding database users, setting database controls, and requesting information from a database

Is Not Null

all records that have a value