QueryByExample Ping Zhou 2008 3 Introduction to QBE

Query-By-Example Ping Zhou 2008. 3

Introduction to QBE Query-By-Example A high-level database management language Alternation to SQL for querying relational database Can be translated to SQL Developed at IBM (M. Zloof) ◦ Many Databases have QBE-like extension Ping Zhou / CS 2310 Course Seminar 2

Why QBE? Convenient and unified way to query, update, define and control database Requires little knowledge to the user, minimizes the concepts user needs to learn Simple yet wide coverage of transactions Graphical and tabular interface Ping Zhou / CS 2310 Course Seminar 3

Basic Concepts Instead of writing text SQL commands… ◦ Programming is done within 2 -D “skeleton tables” ◦ Constant vs Variable Ping Zhou / CS 2310 Course Seminar 4

Basic Queries (1) Print the names and ages of all sailors… Variables that appear only once can be omitted Shorthand to print all fields Ping Zhou / CS 2310 Course Seminar 5

Basic Queries (2) Selections – Place constant in some field Print all sailors with rating=10 Ping Zhou / CS 2310 Course Seminar 6

Ordering and Grouping Order the answer using AO (ascending order) or DO (descending order) Duplicate tuples can be eliminated by using UNO. Ping Zhou / CS 2310 Course Seminar 7

Queries Over Multiple Relations Select tuples from two relations by placing same variable at join column Find sailors who reserved a boat for 8/24/96, and who are older than 25. Ping Zhou / CS 2310 Course Seminar 8

Select Using Condition Box Condition box ◦ Express a condition involving two or more columns. E. g. : _R/_A > 0. 2 ◦ Express a condition involving aggregate operation on a group ◦ Express a condition involving AND and OR operators Ping Zhou / CS 2310 Course Seminar 9

Unnamed Columns Create unnamed column for display Display fields from more than one tables Ping Zhou / CS 2310 Course Seminar 10

Updating Tables using QBE (1) Insertion, Deletion and Updating are done in the same way in QBE Insertion Ping Zhou / CS 2310 Course Seminar 11

Updating Tables using QBE (2) Deleting Updating Ping Zhou / CS 2310 Course Seminar 12

Changes Brought by QBE User Interface ◦ Text command -> Graphical, Tabular ◦ User draw the table he wants directly Easier to learn ◦ Higher scores than SQL users in paper and pencil testing Ping Zhou / CS 2310 Course Seminar 13

QBE / OBE: Office Procedure by Example ◦ Incorporate QBE into office automation ◦ Research project developed by IBM Ping Zhou / CS 2310 Course Seminar 14

Spatial QBE Adding spatial extension to QBE Retrieve all ATMs along Route 64 Retrieve all banks within 100 meters from Washington Hotel. Ping Zhou / CS 2310 Course Seminar 15

Multimedia Queries with QBE Similarity function Find all oil paintings from a Dutch painter which are similar to the given picture. Ping Zhou / CS 2310 Course Seminar 16

References Query-By-Example: a data base language, M. Zloof, 1977 Query-By-Example (QBE), http: //pages. cs. wisc. edu/~dbbook/open. Access/third Edition/qbe. pdf A Human Factors Experimental Comparison of SQL and QBE, Minnie Yi-Miin Yen and Richard W. Scamell, 1993 Spatial QBE Interface for Web GIS, Shapiee Abd Rahman, Subhash Bhalla, 2005 WS-QBE: A QBE-like Query Language for Complex Multimedia Queries, Ingo Schmitt, Nadine Schulz, Thomas Herstel, 2005 Ping Zhou / CS 2310 Course Seminar 17

THANK YOU! Ping Zhou / CS 2310 Course Seminar 18