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

Topic 18 – Databases: Producing Different Output Layouts

Learning Objectives

• Design a simple but fully‑specified database (tables, fields, data‑types, keys, validation, normalisation, form layout).
• Develop queries that sort, search, calculate, aggregate and import/export data.
• Control the appearance of query results – column headings, numeric/ date formatting, page‑setup and conditional formatting.
• Choose the most appropriate layout (tabular, columnar or narrative) for the intended audience.
• Evaluate the strengths and limitations of a chosen layout (AO3).

1. Why Layout Matters

• A clear layout lets users read and interpret information quickly.
• Different tasks require different presentations – detailed reports (grid), summary lists (single column), or narrative statements (sentence style).
• SQL can format the output directly, so the final screen or printed report already looks professional.

2. Database Design – What the Syllabus Expects

2.1 Choosing Data Types & Field Properties

2.2 Simple Form Layout

• Arrange fields in a logical order (e.g. ID → Name → DOB → Class).
• Give clear labels and use appropriate controls (text box, combo box, date picker, check box).
• Make the primary‑key field read‑only.

3. Query Design Process (AO2)

1. Identify the information required.
2. Select the fields to display.
3. Set criteria (WHERE, LIKE, BETWEEN, AND/OR).
4. Choose sorting (ORDER BY) and/or grouping (GROUP BY).
5. Add calculated fields or aggregates if needed.
6. Apply formatting functions and column headings.
7. Test the query, refine criteria, then run the final version.

4. Core SQL Operations – “Query Toolbox”

5. Controlling Column Labels & Data Formatting

Use AS to give a user‑friendly heading and apply functions before the data is displayed.

SELECT student_id          AS "ID",
firstname || ' ' || lastname AS "Student Name",
ROUND(average_mark,1) AS "Avg Mark",
TO_CHAR(dob,'DD/MM/YYYY') AS "Date of Birth"
FROM   students;

5.1 Numeric Formatting Options (syllabus requirement)

• ROUND(number, d) – round to d decimal places.
• CAST(number AS MONEY) or printf('£%.2f', number) – add a currency symbol.
• ROUND(number*100,0) || '%' AS "Result" – display percentages.
• FORMAT(number,'N0') (where supported) – thousand‑separator commas.

5.2 Date & Text Formatting

• TO_CHAR(date,'DD/MM/YYYY') – British date style.
• UPPER(text), LOWER(text), INITCAP(text) – change case.
• CONCAT(a,' ',b) or the || operator – build full names or sentences.

6. Page‑Setup & Presentation of Printed Results

• Orientation: Portrait for tall lists, Landscape for wide tables.
• Margins: usually 1 cm all round; adjust if the report is very wide.
• Header/footer: include report title, date, page number (e.g. “Student List – 04 Jan 2026 – Page 1”).
• Paper size: A4 is standard for IGCSE examinations.
• Choose Print Preview to verify column widths and line breaks before printing.

7. Conditional Formatting in Database Reports

While most IGCSE work is done in Access or similar tools, the same ideas apply to SQL‑generated reports.

• Highlight high‑value rows (e.g., sales > £10 000) by using a CASE expression to prepend a symbol or colour tag that the report‑viewer recognises.
• Example (Access‑style expression):

SELECT product_name,
price,
IIF(price > 1000,'*HIGH*','') AS "Flag"
FROM products;

In a printed report the asterisk can be explained in a legend.

8. Output Layouts

8.1 Tabular Output (Grid Layout)

Best when several fields are required – each row = a record, each column = a field.

SELECT product_id,
product_name,
price
FROM   products
ORDER BY price DESC;

8.2 Columnar Output (List Layout)

Ideal for a single field – displayed as a simple vertical list.

SELECT DISTINCT city
FROM   customers
ORDER BY city;

• Boston
• Chicago
• London
• New York
• Paris

8.3 Narrative (Single‑Column) Layout

Use concatenation to produce a readable sentence for each record.

SELECT CONCAT('Order ', order_id, ': ',
product_name, ' (', quantity, ')') AS "Order Summary"
FROM   orders;

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

8.4 Grouped Summary with Clear Headings

SELECT department      AS "Dept.",
COUNT(*)         AS "No. of Staff",
ROUND(AVG(salary),0) AS "Avg Salary (£)"
FROM   staff
GROUP BY department
ORDER BY department;

9. Example Queries Covering All Required Features

1. Full‑detail employee report (tabular)

   SELECT firstname || ' ' || lastname AS "Employee Name",
   department                     AS "Dept.",
   ROUND(salary,0)                AS "Salary (£)",
   TO_CHAR(dob,'DD/MM/YYYY')      AS "DOB"
   FROM   staff
   ORDER BY department, last_name;
   

2. Single‑column narrative for the same data

   SELECT CONCAT(firstname,' ',lastname,
   ' – ',department,
   ' – £',ROUND(salary,0)) AS "Employee Summary"
   FROM   staff;
   

3. Sales total per region with currency formatting (tabular)

   SELECT region                         AS "Region",
   '£' || printf('%.2f',SUM(sale_amount)) AS "Total Sales"
   FROM   sales
   GROUP BY region
   ORDER BY region;
   

10. Evaluation Prompts (AO3)

• What information does the chosen layout highlight most effectively?
• Are any important details omitted because of the layout (e.g., loss of column headings in a narrative list)?
• How would the report need to change for a different audience (e.g., senior management vs. classroom teacher)?
• What are the limitations of the formatting functions used (e.g., rounding may hide small variations)?
• Could conditional formatting improve the usefulness of the report? If so, how?

11. Summary Checklist (What You Must Know for the Exam)

• Database design: data‑type, field size, validation, DEFAULT, PRIMARY/FOREIGN keys, referential integrity, 1NF/2NF.
• Form layout: logical order, clear labels, appropriate controls.
• Query design process: fields → criteria → sort/group → calculate → format → test.
• Core SQL operations – ORDER BY, WHERE (AND/OR, LIKE), calculated fields, aggregates, GROUP BY, IMPORT/EXPORT.
• Column headings – use AS with quotes for spaces.
• Formatting functions – UPPER, LOWER, ROUND, CONCAT, TO_CHAR, currency/percentage patterns.
• Output layouts – choose grid for multi‑field data, list for single‑field data, narrative for sentence‑style output.
• Page‑setup – orientation, margins, header/footer, print preview.
• Conditional formatting – use CASE or IIF to flag extreme values.
• Evaluation – be ready to discuss the suitability of the layout for the intended audience.