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  to enhance your Access database skills.
 * 5) Complete the SQL tutorial  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. []


 * 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 (-).