TestAS · Economics
Spreadsheets, data handling and business processes, for example what a spreadsheet formula returns.
testas.de lists this topic for the Economics module. In the test it comes as testlets: a short text, table or formula, then 4 to 8 questions you answer from it.
Topic list: testas.de
Full exam-length testlets from the Kollego course, generated the same way as our mocks. Answer from the material, then open each answer to check.
Tables, keys and queries in a relational database
Most business information systems store their data in relational databases. Data are organised in tables. Each row, or record, describes one object, such as a customer or an order, and each column, or attribute, describes one property, such as a name or an amount. Every table has a primary key, an attribute whose value identifies each row uniquely, such as a customer number. A primary key must never be empty and never repeat.
Tables are linked by foreign keys. A foreign key is an attribute in one table that refers to the primary key of another table. In an order table, the customer number of each order is a foreign key referring to the customer table. This way, the customer's name and address are stored only once, and every order simply points to them. Avoiding such repetition, a process called normalisation, prevents inconsistencies: if a customer moves, the address must be changed in one place only.
Data are retrieved with the query language SQL. A basic query has the form SELECT columns FROM table WHERE condition. It returns all rows of the table that meet the condition, showing only the listed columns. Functions such as COUNT, SUM and AVG summarise many rows into one value. A JOIN combines rows of two tables whose key values match, for example each order with the matching customer. Rows without a match are left out of a normal join.
Databases also control who may read or change which data and ensure that related changes, such as booking money out of one account and into another, happen completely or not at all.
Relational databases were proposed by the computer scientist Edgar F. Codd in 1970 and still dominate business applications, from banking to online shops. Alternatives, often called NoSQL databases, store data as documents or key-value pairs and are used where very large volumes of loosely structured data must be handled quickly. Whatever the system, careful design of keys and relationships is what keeps the data consistent over years of use.
The two tables below belong to a small online shop. The column customer_id in the Orders table refers to the Customers table.
| customer_id | name | city |
|---|---|---|
| 1 | Weber | Berlin |
| 2 | Yilmaz | Hamburg |
| 3 | Novak | Berlin |
| 4 | Rossi | Munich |
| order_id | customer_id | amount (EUR) |
|---|---|---|
| 101 | 1 | 250 |
| 102 | 3 | 80 |
| 103 | 1 | 120 |
| 104 | 2 | 300 |
| 105 | 3 | 50 |
1. How many rows does SELECT name FROM Customers WHERE city = 'Berlin' return?
D: 2
The WHERE condition keeps only customers with city 'Berlin'. In the Customers table those are Weber (1) and Novak (3), so 2 rows are returned.
2. What does SELECT SUM(amount) FROM Orders WHERE customer_id = 1 return?
D: 370
The query adds the amounts of orders with customer_id 1. Those are orders 101 (250) and 103 (120): 250 + 120 = 370.
3. Which column in the Orders table is a foreign key?
D: customer_id
The text says a foreign key refers to the primary key of another table, and that customer_id in Orders refers to Customers. So customer_id is the foreign key.
4. What does SELECT COUNT(*) FROM Orders WHERE amount > 100 return?
C: 3
The condition amount > 100 is met by orders 101 (250), 103 (120) and 104 (300); 80 and 50 are too small. COUNT(*) counts them: 3.
5. Which customer would not appear if Orders is joined with Customers in a normal join?
D: Rossi
The text says rows without a match are left out of a normal join. Rossi (customer_id 4) appears in no order, so Rossi would not appear.
6. What is the total amount ordered by customers from Berlin?
A: EUR 500
Berlin customers are Weber (1) and Novak (3). Weber ordered 250 + 120 = 370 and Novak 80 + 50 = 130, so the total is 370 + 130 = EUR 500.
Reading spreadsheet formulas
Spreadsheets are among the most widely used tools in business. A worksheet consists of cells arranged in columns, labelled with letters, and rows, labelled with numbers, so every cell has an address such as B3. A cell can contain a value or a formula, which begins with an equals sign and calculates a result from other cells.
The way references behave when a formula is copied is crucial. A relative reference, such as B2, is adjusted: copying =B2*C2 from row 2 to row 3 turns it into =B3*C3. An absolute reference, marked with dollar signs, such as $E$1, stays the same wherever the formula is copied. Absolute references are used for values that apply to every row, such as a tax rate or an exchange rate. Mixed references, such as $E1 or E$1, fix only the column or only the row.
Functions summarise or test data. SUM(D2:D5) adds all values in the range from D2 to D5. AVERAGE calculates the arithmetic mean, MIN and MAX return the smallest and largest value, and COUNTIF counts the cells in a range that meet a condition. The function IF(condition, value if true, value if false) returns one of two results depending on a test. In the German version of the software, function names are translated, for example SUMME and WENN, and arguments are separated by semicolons instead of commas.
Errors in spreadsheets are common and have caused costly mistakes in companies and in research. Typical sources are wrong ranges that leave out a row, relative references that should have been absolute, and numbers typed over formulas. Good practice includes keeping inputs separate from calculations, labelling everything clearly and checking totals with an independent calculation.
Large companies often replace critical spreadsheets with dedicated software once they grow, because spreadsheets are hard to check and to control when many people edit them.
The worksheet below calculates the revenue of a small stationery shop. Cell D2 contains the formula =B2*C2, which was copied down to D5. Cell E1 contains the VAT rate.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price (EUR) | Quantity | Revenue (EUR) | 0.19 |
| 2 | Pens | 2 | 150 | 300 | |
| 3 | Folders | 5 | 40 | 200 | |
| 4 | Paper | 4 | 100 | 400 | |
| 5 | Stapler | 12 | 10 | 120 |
1. What formula does cell D4 contain?
B: =B4*C4
D2 holds =B2*C2 and was copied down. Relative references adjust with the row, so two rows lower D4 holds =B4*C4.
2. What is the result of =SUM(D2:D5)?
A: 1,020
SUM(D2:D5) adds the revenue column. 300 + 200 + 400 + 120 = 1,020.
3. What is the result of =AVERAGE(B2:B5)?
B: 5.75
AVERAGE gives the arithmetic mean of B2:B5. (2 + 5 + 4 + 12) / 4 = 23 / 4 = 5.75.
4. Cell F2 contains =D2*$E$1 and is copied to F3. What does F3 contain?
D: =D3*$E$1
D2 is relative and becomes D3 one row down. $E$1 is absolute (dollar signs) and stays the same, so F3 holds =D3*$E$1.
5. What does =IF(D5>250, "A", "B") return?
A: B
IF returns 'A' if the test is true, else 'B'. D5 = 120, and 120 is not above 250, so the result is B.
6. What does =COUNTIF(C2:C5, ">50") return?
B: 2
COUNTIF counts the cells in C2:C5 above 50. The quantities are 150, 40, 100 and 10; only 150 and 100 exceed 50, so the result is 2.
One diagnostic, a projected score, and your first unit. No card, no time limit on the free tier.
Start free, no card needed