Database Applications/Advanced Queries

This lesson assists users in expanding your knowledge by working with advanced queries using multiple tables.

Objectives and Skills

Objectives and skills for this lesson include:

Readings

1. McFadyen: Relational Databases and Microsoft Access - Chapter 6 (Microsoft Access Queries - Advanced)
2. Tutorials Point: Action Queries
3. Microsoft Access: Like Criterion
4. w3schools.com: Introduction to SQL
5. Tutorials Point: SQL Overview
6. Tutorials Point: MS Access SQL View
7. Microsoft Office Support: Queries
8. Microsoft Access: Like Criterion
9. Wikibooks: Data Definition Language (DDL)
10. Wikibooks: Structured Query Language/Data Manipulation Language
11. w3schools: SSQL update tutorial and you also can test other statements at https://www.w3schools.com

Multimedia

1. GCF Global: Access 2016 Designing a Simple Query
2. Creating a Query in SQL View
3. SQL with Microsoft Access 2016 - SELECT query
4. Youtube: Microsoft Access 2016 Queries: Unique Values and Unique Records
5. Youtube: Microsoft Access Crosstab Query
6. Youtube: Microsoft Access Queries
7. Youtube: SQL with Access 2016 - Create Table
8. Youtube: How To Make A Delete Query In Access 2016
9. Youtube: How To Make Action Queries In Access
10. YouTube: Back Up A Database in Access 2016
11. Youtube: How to use Append Query in Access

Activities

1. Complete the tutorial GCF Global: Access 2016 Designing a Simple Query.
2. Review [McFadyen: Relational Databases and Microsoft Access - Chapter 4 (Microsoft Access Queries)]. Complete all of the exercises in Chapter 4.
3. Complete the SQL tutorial SQL Exercises/The computer store to view structured query language statements.
4. Complete the Microsoft Office/Access Basic Exam [2] to enhance your Access database skills.
5. Complete the SQL tutorial [3] to understand the basics of SQL.

Lesson Summary

• A database is an organized collection of data.
• You can match for keywords or phrases in a query using the Like operator.
• You can match for specific character in a query using the In operator.
• Before making any changes to a query you must first make a backup copy of the database prior to making any changes or modifying data.
• Action queries are queries that can add, change, or delete data in a database.
• Cross Tab queries are queries where results are displayed with both row and column headings similar to a spreadsheet.
• Microsoft Access has four different action query types including: Append, Delete, Make-table, and Update.
• Structured Query Language (SQL) is the standard language for relational database management systems.
• SQL is a computer language used to store, manipulate, and retrieve data from a relational database management system.

Key Terms

action query

An action query can ask for additional operations with the data, such as insertion, updating, or deletion.

aggregate function

An aggregate function or aggregation function is a function where the values of multiple rows are grouped together to form a single summary value.

AND operator

The value of an AND expression is true only if both input values are true.

AVG

AVG is the sum of the numbers divided by how many numbers are being averaged.

backup

A backup, or the process of backing up, refers to the copying and archiving of computer data so it may be used to restore the original after a data loss event. It is also important to note that the backup can be restored in case of a problem by preforming a dry run. So that any problem will not be discovered only in the event of actual problem. This should be part of a disaster recovery plan.

calculated field

A calculated field is the addition of a new numerical field based upon field calculations.

conditional statements

Conditional statements or conditional expressions are features of a programming language which perform different computations or actions.

COUNT

COUNT is the process of determining the number of elements of a finite set of objects.

crosstab

Crosstab (or contingency table) is a type of table in a matrix format that displays the (multivariate) frequency distribution of the variables. They are heavily used in survey research, business intelligence, engineering and scientific research.

delete

The The delete statement is used to remove specific rows in a table with conditions.

DDL

Data Definition Language is a subset of SQL. Its statements define database schema. They are used to create tables, indexes, and other structures.

DML

Data Manipulation Language, a subset of SQL, includes Select, Insert, Update, and Delete queries.

inner join

An inner join requires each row in the two joined tables to have matching column values. Inner join creates a new result table by combining column values of two tables. An inner join is one in which Access only includes data from a table if there is corresponding data in the related table, and vice versa. Inner joins are useful because they let you combine data from two sources based on shared values – so you only see data when there’s a complete picture. [[4]]

join

A join combines columns from one or more tables in a relational database.

LIKE operator

The LIKE operator is a logical operator that determines if a character string matches a specified pattern.

MAX

MAX is the process of determining the largest value of the function.

MIN

MIN is the process of determining the smallest value of the function.

OR operator

The value of an OR expression is when at least one of the input values is true.

outer join

An outer join occurs when the joined table retains each row—even if no other matching row exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both).

parameter query

A parameter query lets the user answer the question each time the query is executed to get to the records that they want.

SELECT (SQL)

The SELECT command retrieves data from one or more tables or views.

self-join

A case where a table is joined to itself twice; also known as a "recursive join"

spatial data

A spatial database is a database that is optimized for storing and querying data that represents objects defined in a geometric space.

Structured Query Language (SQL)

Structured Query Language (SQL) is a widely-used programming language for working with relational databases.

SUM

SUM is the addition of a sequence of numbers; the result is their sum or total.

update

The update statement is used to modify already existent records in a table.

wildcard character

A wildcard character is a kind of placeholder represented by a single character, such as an asterisk (*), question mark (?), bracket ([]).

ANSI-89 wildcard characters also include hashtag (#), exclamation point (!), and dash (-).

Review Questions

1. There are many types of queries, this _______ query will remove one or more records from a table.

   There are many types of queries, this delete query will remove one or more records from a table.
2. It is very important that before one modifies their database (append, update, delete, create a new table), one must ______ their database.

   It is very important that before one modifies their database (append, update, delete, create a new table), one must backup their database.
3. _____ is an action query that adds the records in a query's result set to the end of an existing table.

   Append query is an action query that adds the records in a query's result set to the end of an existing table.
4. _____ logical operator displays all data besides the criteria in question.

   Not logical operator displays all data besides the criteria in question.
5. True or False: Nulls are passed over when the counting of field values is performed.

   True : Nulls are passed over when the counting of field values is performed.
6. To do a totals query you must first create a query in Query design and hit the total button in the ____.

   To do a totals query you must first create a query in Query design and hit the total button in the Design tab.

See Also

• Wikibooks: Database Design/Fundamental Concepts

References

← Queries

Database Applications

Forms →