Be able to produce different output layouts including controlling the display of data, labels, tabular or columnar format

Topic 18 – Databases

Objective

Be able to produce different output layouts, including controlling the display of data, labels, and presenting results in tabular or columnar format.

1. Why Layout Matters

• Clear presentation helps users understand the information quickly.
• Different tasks require different layouts – e.g., reports vs. summary screens.
• Database queries can be written to format the output directly.

2. Controlling Column Labels

In SQL the AS clause gives a column a user‑friendly label.

SELECT student_id AS "ID",
firstname || ' ' || lastname AS "Student Name",
grade AS "Result"
FROM   students;

The result will show column headings ID, Student Name, and Result instead of the original field names.

3. Formatting Data Within Columns

Functions can change the appearance of data before it is displayed.

4. Tabular Output (Grid Layout)

Standard query results are displayed in a table where each row represents a record and each column a field.

SELECT productid, productname, price
FROM   products
ORDER BY price DESC;

Typical screen output:

5. Columnar Output (List Layout)

When only a single field is required, a columnar (list) format can be used.

SELECT DISTINCT city
FROM   customers
ORDER BY city;

Result displayed as a single column list:

• Boston
• Chicago
• London
• New York
• Paris

6. Combining Labels and Data in One Column

Use concatenation to create a descriptive line for each record.

SELECT CONCAT('Order ', orderid, ': ', productname, ' (', quantity, ')') AS "Order Summary"
FROM   orders;

Sample output:

1. Order 1023: Keyboard (2)
2. Order 1024: Mouse (5)
3. Order 1025: Monitor (1)

7. Using GROUP BY with Labels

Aggregated data often needs a clear label for each group.

SELECT department AS "Dept.",
COUNT(*)    AS "No. of Staff",
A \cdot G(salary) AS "Avg Salary"
FROM   staff
GROUP BY department;

Result example:

8. Practical Exercise

1. Create a query that lists each employee’s full name, their department, and their salary rounded to the nearest pound. Use appropriate column labels.
2. Modify the query to display the result in a single column that reads: “John Smith – Finance – £32,500”.
3. Write a query that shows the total sales per region in a tabular layout with headings “Region”, “Total Sales”.

9. Summary Checklist

• Use AS to give columns meaningful headings.
• Apply formatting functions (UPPER, ROUND, CONCAT) to control how data appears.
• Choose a tabular layout for multi‑field results; choose a columnar or list layout for single‑field results.
• Combine labels and data with concatenation when a narrative style is required.
• Remember to order or group data to make the output logical and easy to read.