1_Reports_lp_l1_v1

Long-term plan unit:

 10.3A Information Systems

School

Date:

Teacher name:

Grade: 10

Number present:

Grade:

The topic of the lesson:

Reports

Learning objectives(s) that this lesson is contributing to

  10.3.2.5 create reports using extracted data (SQL)

Assessment criteria

●        Create a report based on two tables using the Report Wizard tool.

Success criteria

All learners will be able to know:

create a report for data output

Language objectives

Students can:

create reports using extracted data

Vocabulary and terminology specific to the subject:

Forms, reports, Form Wizard, Report Wizard, Constructor.

Useful expressions for conversations and writing:

The report is intended for ...

Value links

Group work , co-operation, time management, academic integrity

Cross curricular links

English

Previous learning

 -

Course of the lesson

Planned stages of the lesson

Planned activities at the lesson

Resources

Beginning

0-2

Warm up

Definition of the topic.

Setting lesson objectives and success criteria

Middle

2-15

A report is a special form of data presentation intended for printing.

The report allows:

Present data in a form that is easy to read and analyze;

Group records with the calculation of the total and average values;

Include in the report and print graphic objects.

Before creating a report, you must create a database.

Creating a database in design mode;

Creating a standard form;

Filling the form with data.

To retrieve the data contained in the SQL database tables, use the SELECT statement,

having in general a complex and multivariate syntax. In this training

The manual covers only simple and frequently used examples.

constructions operator SELECT.

Simplified SELECT statement looks like this:

SELECT [ALL | DISTINCT] in_expression ...

 FROM name_name [syn_table], ...

 [WHERE complex_condition]

 [GROUP BY full_name_column | column_name, ...]

 [ORDER BY full_column_name | column_name [ASC | DESC], ...]

 [HAVING complex_condition];

The result of the operator's work is output to standard output (display screen)

newly built table for which

• the number and meaning (semantics) of columns is determined by the list of elements

c_expression;

• the contents of the rows are determined by the contents of the source tables from the FROM list and

the sampling criterion given by the complex_condition.

When describing the syntax of the SELECT statement, the following notation is used:

• syn_table is an optional table name synonym used to abbreviate

the length of the expression and condition record in the SELECT statement.

• full_name_column - the full name of the column in the form

[table_name | syn_table] column_name

A table-specific prefix in the column name is only required for

distinguish between columns that have the same name in different tables from the FROM list.

• column_number is the column number of the resulting table.

Presentation

15-32

Practical task

Database "Recycling Firm"

The company is engaged in receiving recyclables and has several reception points. Each item receives money for their delivery to deliverers in exchange for raw materials. In fact, the diagram shows two databases. Each task for this scheme uses only one pair of tables (either with the prefix _o, or without it).

In the Income_o and Outcome_o tables, the primary key is the pair of attributes {point, date} - the number of the receiving point and the date. This key should simulate the situation when information about the receipt of money at the receiving point and its issuance to deliverers are recorded in the database no more than once a day.

The task -

Calculate the cash balance at the beginning of the day on April 15, 2001 at each receiving point for a database with reports no more than once a day. Conclusion: point balance.

Solution -

SELECT i.point, CASE inc

                     WHEN NULL

                     THEN 0

                     ELSE inc

                END -

                CASE out

                     when NULL

                     THEN 0

                     ELSE out

                END

FROM (SELECT point, SUM(inc) inc

      FROM Income_o

      WHERE '20010415' > date

      GROUP BY point

      ) AS I FULL JOIN

     (SELECT point, SUM(out) out

      FROM Outcome_o

      WHERE '20010415' > date

      GROUP BY point

      ) AS III ON III.point = I.point

End

 32-40

Reflection

The learners speak in a circle with one sentence, choosing the beginning of a phrase from the reflective screen on the board:

Today I found out ... it was interesting ...

it was hard ... I was doing the job ...

I realized that ... now I can ...

I felt that ... I acquired ...

I learned ... I did it ...

I could ... I will try ...

I was surprised ... he gave me a lesson for life ...

Cards

Differentiation – how do you plan to give more support? How do you plan to challenge the more able learners?

Assessment – how are you planning to check students’ learning?

Health and safety regulations

Additional support.

Work in pairs / groups - support for classmates.

Help the teacher, if required.

More capable learners can demonstrate aspects of their decisions that seem interesting / more complex to the other learners.

Answers questions and a session of answers.

Use questions when completing a task.

Use survey questions to understand the results / success of learners at each stage of the lesson.

Remind of some safety rules when working with computer equipment, for example, that you must be careful when installing the screen, keyboard and mouse; beware of wires, as they pose a threat to movement.