Database Management/Query by Example

From Wikiversity
Jump to navigation Jump to search

This lesson introduces Query by Example (QBE). QBE is a database query language for relational databases, using visual tables where the user enters commands, example elements and conditions.[1]

Objectives and Skills[edit | edit source]

Objectives and skills for this lesson include:

  • Understand QBE concepts
  • Create single-table QBE queries using a database application
  • Create multi-table QBE queries using a database application

Readings[edit | edit source]

  1. Wikipedia: Query by Example
  2. Wikipedia: LibreOffice Base
  3. Wikipedia: Microsoft Access
  4. Wikibooks: Microsoft Office/Create queries for a database

Multimedia[edit | edit source]

  1. YouTube: Running Queries: Query by Example (QBE)
  2. YouTube: Query By Example
  3. YouTube: Getting Started with LibreOffice Base
  4. YouTube: Access - Getting Started
  5. YouTube: How to Create a Calculation Query in Microsoft Access

Activities[edit | edit source]

  1. Select a database application to use for this lesson. LibreOffice Base and Microsoft Access are recommended.
  2. Review Database Applications and Database Software for background information and tutorials.

LibreOffice Base[edit | edit source]

  1. For Linux, MacOS, and Windows. Download and install the free and open LibreOffice suite.
  2. Complete one or more of the following tutorials using LibreOffice Base:
  3. To view the database E-R diagram, select Tools and Relationships.
  4. To view SQL for a query, open the query and then select View and Switch Design View On/Off.

Microsoft Access[edit | edit source]

  1. For Windows only. If you don't already have Microsoft Access installed, you can sign up for the free Microsoft: Azure for Students. Within the Microsoft: Azure Education Software portal, there is an option to download Access 2016.
  2. Complete the following tutorials using Microsoft Access:
  3. To view the database E-R diagram, select Database Tools and Relationships.
  4. To view SQL for a query, open the query and then select View and SQL View.

Northwind[edit | edit source]

  1. Download a copy of the Database Examples/Northwind database for your selected database application.
  2. Review the E-R diagram in your database application to verify that it matches the Database Examples/Northwind example. Note the primary keys, foreign keys, and relationships. Then create QBE queries to determine results for each of the following. View the SQL generated for each query.
  3. Select all fields and all records in the Categories table.
  4. Select the CustomerName for all customers.
  5. Select the CustomerName for all customers in the country 'Italy'.
  6. Select the ProductName for all products in Category 1 with a price less than $5.00.
  7. Select the SupplierName for all suppliers from English-speaking countries (Australia, Canada, UK, USA)
  8. Select the first and last names of all employees having a birthday in September.
  9. Select the CustomerName and OrderID for all orders placed on 9 September 1996.
  10. Select the ProductName for all seafood products with a supplier from 'Boston'.
  11. Select the CustomerName and OrderID for all customers who ordered 'Aniseed Syrup', sorted in alphabetical order.
  12. Select the ProductName, Quantity, Price, and ExtendedPrice (Quantity * Price) for orders 10344 and 10345.
  13. Select the first and last names and current age of all employees having a birthday in September.
  14. Select the OrderID, count of products ordered, and total order cost for orders 10344 and 10345.
  15. Select the total number of orders and total cost of orders shipped by 'Speedy Express' in February 1997.

Lesson Summary[edit | edit source]

  • Query by Example (QBE) is a database query language for relational databases.[2]
  • QBE is a graphical query language, using visual tables where the user enters commands, example elements and conditions.[3]
  • QBE supports retrieving data, inserts, deletes, and updates, as well as creation of temporary tables.[4]
  • Behind the scenes, QBE converts the user's actions into statements expressed in a database manipulation language, such as SQL. It is this statement that is actually executed.[5]
  • It is easier and more productive for end-users (and even programmers) to select tables and columns by selecting them rather than typing in their names.[6]
  • LibreOffice Base is a free and open-source relational database management system that is part of the LibreOffice office suite.[7]
  • LibreOffice Base is designed to allow users to easily create, access, modify, and view databases and their data.[8]
  • LibreOffice Base provides users with a graphical user interface that allows users to work with four main tools: tables, queries, forms, and reports.[9]
  • Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools.[10]
  • Microsoft Access users can create tables, queries, forms and reports, and connect them together with macros. Advanced users can use Visual Basic for Applications (VBA) to write rich solutions with advanced data manipulation and user control.[11]

Key Terms[edit | edit source]

form
Provides an attractive layout used for entering or looking up data on screen.[12]
join
Combines columns from one or more tables into a new logical table or view.[13]
query
Provides the ability to access or modify data by asking questions.[14]
report
Provides an attractive layout used for printing out data with sophisticated presentation.[15]
table
Stores information about one specific kind of item.[16]

See Also[edit | edit source]

References[edit | edit source]