Create the following additional select QBE queries for the...

Create the following additional select QBE queries for the sample tables of the DreamHome case study, using (where possible) the QBE facility of your DBMS. (a) Retrieve the branch number and address for all branch offices. (b) Retrieve the staff number, position, and salary for all members of staff working at branch office B003. (c) Retrieve the details of all flats in Glasgow. (d) Retrieve the details of all female members of staff who are older than 25 years old. (e) Retrieve the full name and telephone of all clients who have viewed flats in Glasgow. (f) Retrieve the total number of properties, according to property type. (g) Retrieve the total number of staff working at each branch office, ordered by branch number.

Create the following additional select QBE queries for the sample tables of the DreamHome case study, using (where possible) the QBE facility of your DBMS.
(a) Retrieve the branch number and address for all branch offices.
(b) Retrieve the staff number, position, and salary for all members of staff working at branch office B003.
(c) Retrieve the details of all flats in Glasgow.
(d) Retrieve the details of all female members of staff who are older than 25 years old.
(e) Retrieve the full name and telephone of all clients who have viewed flats in Glasgow.
(f) Retrieve the total number of properties, according to property type.
(g) Retrieve the total number of staff working at each branch office, ordered by branch number.

Key Concepts

Grouping

Grouping is the process of arranging rows into sets based on one or more columns. When combined with aggregation functions, grouping enables the computation of summarized statistics for each group. This is vital for queries that require a breakdown of data into categories, such as summarizing counts of properties by type or staff numbers by branch.

Ordering

Ordering allows query results to be sorted in a specified sequence based on one or more columns. This is important when the presentation of data needs to follow a logical or specific order, such as listing branches by branch number or arranging staff details in a predetermined sequence. Sorting improves the readability and analysis of the query output.

Selection

Selection, or filtering, is the process of retrieving only the rows that meet certain criteria from a table. In the context of QBE and database queries, conditions such as 'working at a specific branch' or 'being older than a certain age' are used to narrow down the dataset to the records that are of interest.

Query By Example (QBE)

QBE is a database query language that provides an intuitive, graphical means of generating queries by example rather than writing textual command languages like SQL. It allows users to define desired output and filtering conditions directly within a table-like format, making it easier to visualize and construct queries, especially for those less familiar with traditional programming syntax.

Projection

Projection refers to the operation of selecting specific columns from a table. In query languages like QBE, it involves specifying which attributes or fields should appear in the output of the query. This concept is essential for limiting the data returned to only the relevant fields needed to answer a particular question.

Aggregation Functions

Aggregation functions are used to compute summaries of data across multiple rows, such as sums, averages, or counts. They are crucial when grouping data to provide insights like the total number of properties or staff members, allowing users to generate overall statistical measures from detailed records.

Transcript

00:02 We're going to go through a series of queries, questions on queries, and write them out.

00:10 The first problem is retrieve the names of all employees who work more than 10 hours per week on the product x project.

00:21 So we're going to select first name, last name from employee e.

00:26 We're going to join that with the works on table.

00:31 Give that a shortcut of w.

00:34 We're going to join on social security number.

00:38 We're going to join on project, on project number, and we're going to check where department number is 5 and the product name is product x and ours is greater than 10.

00:59 And that's our query for problem a.

01:03 Problem b lists the names of all employees who have a dependent with the first name as themselves.

01:10 So we'll select distinct first name, last name from employees.

01:16 We're going to join that with our dependent table on social security number where employee first name equals dependent name.

01:29 Problem c.

01:32 Find the names of all employees who are directly supervised by franklin wong.

01:36 That's an easy one.

01:39 Select name from employee.

01:42 Join employee.

01:45 Name is franklin wong...