Wikiversity

Database Applications/Advanced Queries

< Database Applications

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

Objectives and Skills

edit

Objectives and skills for this lesson include:

Readings

edit
  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

edit
  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

edit
  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

edit
  • A database is an organized collection of data.[1]
  • 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

edit
action query
An action query can ask for additional operations with the data, such as insertion, updating, or deletion.[2]
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.[3]
AND operator
The value of an AND expression is true only if both input values are true.[4]
AVG
AVG is the sum of the numbers divided by how many numbers are being averaged.[3]
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.[5] It is also important to note that the backup can be restored in case of a problem by preforming a dry run.[6] So that any problem will not be discovered only in the event of actual problem. This should be part of a disaster recovery plan.[7]
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.[8]
COUNT
COUNT is the process of determining the number of elements of a finite set of objects.[3]
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.[9]
delete
The The delete statement is used to remove specific rows in a table with conditions.[10]
DDL
Data Definition Language is a subset of SQL.[11] Its statements define database schema.[12] They are used to create tables, indexes, and other structures. [11]
DML
Data Manipulation Language, a subset of SQL, includes Select, Insert, Update, and Delete queries.[13]
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.[14] 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.[15]
LIKE operator
The LIKE operator is a logical operator that determines if a character string matches a specified pattern. [16]
MAX
MAX is the process of determining the largest value of the function.[3]
MIN
MIN is the process of determining the smallest value of the function.[3]
OR operator
The value of an OR expression is when at least one of the input values is true.[17]
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).[18]
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.[19]
SELECT (SQL)
The SELECT command retrieves data from one or more tables or views.[20]
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.[21]
Structured Query Language (SQL)
Structured Query Language (SQL) is a widely-used programming language for working with relational databases.[22]
SUM
SUM is the addition of a sequence of numbers; the result is their sum or total.[3]
update
The update statement is used to modify already existent records in a table.[23]
wildcard character
A wildcard character is a kind of placeholder represented by a single character, such as an asterisk (*), question mark (?), bracket ([]).[24]
ANSI-89 wildcard characters also include hashtag (#), exclamation point (!), and dash (-).[11]

Review Questions

edit
Enable JavaScript to hide answers.
Click on a question to see the answer.
  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.[25]
  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.[26]
  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

edit

References

edit
  1. ↑ Wikipedia: Database
  2. ↑ Wikibooks: The Computer Revolution/Databases/basic structure
  3. ↑ 3.0 3.1 3.2 3.3 3.4 3.5 Wikipedia: Aggregate function
  4. ↑ Wikibooks: Digital Circuits/Logic Operations
  5. ↑ Wikipedia: Backup
  6. ↑ "Dry run (testing)". Wikipedia. 2018-04-23. https://en.wikipedia.org/w/index.php?title=Dry_run_(testing)&oldid=837897252. 
  7. ↑ "Disaster recovery". Wikipedia. 2019-02-13. https://en.wikipedia.org/w/index.php?title=Disaster_recovery&oldid=883165870. 
  8. ↑ Wikipedia: Conditional (computer programming)
  9. ↑ Wikipedia: Contingency table
  10. ↑ Wikipedia: Delete (SQL)
  11. ↑ 11.0 11.1 11.2 [1] McFadyen: Relational Databases and Microsoft Access
  12. ↑ GeeksforGeeksː SQL | DDL, DQL, DML, DCL and TCL Commands
  13. ↑ Wikibooks: Structured Query Language/Data Manipulation Language
  14. ↑ Wikipedia: Join (SQL) Inner Join
  15. ↑ Wikipedia: Join (SQL)
  16. ↑ SQLServerTutorial: SQL Server Basics
  17. ↑ W3Schools.com SQL, AND, OR, NOT
  18. ↑ Wikipedia: Join (SQL) Outer Join
  19. ↑ Wikibooks: Microsoft Office/Create queries for a database
  20. ↑ Wikibooks: Structured Query Language/SELECT: Fundamentals
  21. ↑ wikipedia: Spatial database
  22. ↑ Wikibooks: Structured Query Language
  23. ↑ Wikipedia: Update (SQL)
  24. ↑ Wikipedia: Wildcard charter
  25. ↑ Wikipedia: Delete (SQL)
  26. ↑ Quizlet: Access Lesson 12
Retrieved from "https://en.wikiversity.org/w/index.php?title=Database_Applications/Advanced_Queries&oldid=2309372"