Database Management/Query by Example

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

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

Multimedia

QBE

LibreOffice Base

Microsoft Access

Activities

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

LibreOffice Base

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

Microsoft Access

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.
Complete the following tutorials using Microsoft Access:
To view the database E-R diagram, select Database Tools and Relationships.
To view SQL for a query, open the query and then select View and SQL View.

Northwind

Download a copy of the Database Examples/Northwind database for your selected database application.
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.
Select all fields and all records in the Categories table.
Select the CustomerName for all customers.
Select the CustomerName for all customers in the country 'Italy'.
Select the ProductName for all products in Category 1 with a price less than $5.00.
Select the SupplierName for all suppliers from English-speaking countries (Australia, Canada, UK, USA)
Select the first and last names of all employees having a birthday in September.
Select the CustomerName and OrderID for all orders placed on 9 September 1996.
Select the ProductName for all seafood products with a supplier from 'Boston'.
Select the CustomerName and OrderID for all customers who ordered 'Aniseed Syrup', sorted in alphabetical order.
Select the ProductName, Quantity, Price, and ExtendedPrice (Quantity * Price) for orders 10344 and 10345.
Select the first and last names and current age of all employees having a birthday in September.
Select the OrderID, count of products ordered, and total order cost for orders 10344 and 10345.
Select the total number of orders and total cost of orders shipped by 'Speedy Express' in February 1997.

Pubs

Download a copy of the Database Examples/Pubs database for your selected database application.
Review the E-R diagram in your database application to verify that it matches the Database Examples/Pubs 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.
Select all fields and all records in the Authors table.
Select the emp_id and lname for all employees.
Select the au_firstname and au_lastname for all authors in the state 'UT'.
Select the orderid for all orders in storeid 7131 with a quantity greater than 20.
Select the publishername for all publishers in the European-speaking countries (Germany, France).
Select the first and last names of all employees having a hire date in June.
Select the orderid, ord_detail, and title for all orders placed on September 13, 2024.
Select the first and last employee name for all job descriptions with a publisher name from 'NY'.
Select the storename and orderid for all stores that ordered 'The Busy Executive's Database Guide', sorted in alphabetical order.
Select the title, quantity, price, and extendedprice (quantity * price) for sales orders P2121 and P723
Select the first and last names and current number of years for all employees having a hire date in November.
Select the order number, sum of quantity sales ordered, and total extended cost for orders P2121 and P723.
Select the total number of orders and total cost of orders shipped by 'Bookbeat' between January - May 2023.

Lesson Summary

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

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]

References

← Introduction

Database Management

SQL →

[1] Wikipedia: Query by Example

[2] Wikipedia: Query by Example

[3] Wikipedia: Query by Example

[4] Wikipedia: Query by Example

[5] Wikipedia: Query by Example

[6] Wikipedia: Query by Example

[7] Wikipedia: LibreOffice Base

[8] Wikipedia: LibreOffice Base

[9] Wikipedia: LibreOffice Base

[10] Wikipedia: Microsoft Access

[11] Wikipedia: Microsoft Access

[12] Wikibooks: Microsoft Office/Create a basic two table database with reports

[13] Wikipedia: Join (SQL)

[14] Wikibooks: Microsoft Office/Create a basic two table database with reports

[15] Wikibooks: Microsoft Office/Create a basic two table database with reports

[16] Wikibooks: Microsoft Office/Create a basic two table database with reports

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

[16]

Objectives and Skills

Readings

Multimedia

QBE

LibreOffice Base

Microsoft Access

Activities

LibreOffice Base

Microsoft Access

Northwind

Pubs

Lesson Summary

Key Terms

See Also

References