Data Manipulation Quiz

A programmer is displaying records from a tblProducts table one at a time on a form. After displaying the first product, they want to show the next one. Which command is used to move the dataset's cursor to the very next record in the table?

• tblProducts.First;

• tblProducts.Next;

• tblProducts.Last;

• tblProducts.Bookmark;

Answer explanation

The command tblProducts.Next; is used to move the dataset's cursor to the next record in the tblProducts table. The other options do not serve this purpose.

You have a table tblSales with fields Quantity and Price. You need to display the total cost for each sale (Quantity * Price) in a TDBGrid, but you don't want to add a permanent 'TotalCost' field to the actual database table. How can you achieve this?

• Manually calculate the total for each row and add it to the TDBGrid cells.

• Create a calculated field in the dataset component that computes the total on-the-fly.

• Use the tblSales.Insert; command to add a new column to the table temporarily.

• Write a separate SQL query just to calculate the totals and display it in another grid.

Answer explanation

Creating a calculated field in the dataset allows for dynamic computation of TotalCost (Quantity * Price) without altering the database structure, making it the most efficient solution for displaying totals in the TDBGrid.

In an application for a veterinarian, you need to calculate the total amount billed for a specific animal's treatments. The tblInvoices contains VisitDate, AnimalID, and Amount. What is the most efficient way to sum the Amount for only the records where AnimalID is 'A101'?

• Loop through the entire tblInvoices table with .First and .Next, and use an if statement inside the loop to check if AnimalID is 'A101' before adding the Amount to a running total.

• Use the .Locate method to find the first record for 'A101' and then only read the Amount from that single record.

• Filter the dataset using tblInvoices.Filter := 'AnimalID = ''A101'''; tblInvoices.Filtered := True; and then loop through the much smaller, filtered result set to sum the Amount.

• Ask the user to manually select all the correct records in the TDBGrid and then click a 'Calculate Total' button.

Answer explanation

Filtering the dataset for 'A101' reduces the number of records to process, making it more efficient to sum the Amount. This method avoids unnecessary checks on unrelated records, unlike looping through the entire table.

An administrator at a school needs to find all students in tblStudents who are registered in 'Grade 11'. Which line of code correctly sets the filter for the dataset?

• tblStudents.Filter := 'Grade = 11';

• tblStudents.Filter := 'Grade = ''11''';

• tblStudents.Filter := 'Grade: 11';

• tblStudents.Filtered := 'Grade = 11';

Answer explanation

The correct filter syntax requires single quotes to denote string values in SQL-like queries. Thus, 'Grade = ''11'';' correctly filters for students in Grade 11, while the other options are either incorrect or improperly formatted.

A real estate application needs to display a list of available properties in a TDBGrid. The tblProperties table has a Status field. Which two properties of the dataset component must be used together to show only the properties where the Status is 'Available'?

• .Filter and .Filtered

• .Filter and .Active

• .Locate and .Filtered

• .IndexName and .MasterSource

Answer explanation

.Filter allows you to specify a condition (e.g., Status = 'Available'), while .Filtered must be set to true to apply that filter. Together, they effectively limit the dataset to show only the desired properties.

A sales manager wants to see all sales from the last quarter that were greater than R5000. The tblSales table has a SaleDate (a TDateTime field) and a SaleAmount (a numeric field). Which filter expression would achieve this?

• tblSales.Filter := 'SaleDate >= #01/05/2025# AND SaleAmount > 5000';

• tblSales.Filter := 'SaleDate > 01-05-2025 AND SaleAmount > 5000';

• tblSales.Filter := 'SaleDate is after #01/05/2025# AND SaleAmount >= 5000';

• tblSales.Filter := 'SaleAmount > 5000 OR SaleDate >= #01/05/2025#';

Answer explanation

The correct filter expression is 'SaleDate >= #01/05/2025# AND SaleAmount > 5000' as it accurately captures sales from the last quarter (starting from 01/05/2025) that exceed R5000.

A data clerk is cleaning a tblMembers database. They need to find members who have not provided an email address. The Email field for these members is empty. What is the correct way to check if the Email field for the current record is empty?

• if tblMembers['Email'] = '' then...

• if tblMembers['Email'].IsEmpty then...

• if tblMembers.FieldByName('Email').IsNull then...

• if tblMembers['Email'] = NULL then...

Answer explanation

The correct choice is 'if tblMembers.FieldByName('Email').IsNull then...'. This checks if the Email field is null, which indicates that no email address has been provided, unlike the other options that are incorrect.

You need to count how many students in tblStudents are enrolled in the 'IT' subject. The table is large, and you have already filtered it to show only IT students. What is the most direct way to get this count?

• Use a while loop to iterate from the first to the last record, incrementing a counter.

• Use the dataset's built-in RecordCount property.

• Use a for loop from 1 to 1000 and check each record.

• Execute a separate SQL query: SELECT COUNT(*) FROM tblStudents WHERE Subject = 'IT'.

Answer explanation

Using the dataset's built-in RecordCount property is the most direct way to get the count of filtered IT students, as it provides an immediate count without the need for loops or additional SQL queries.

A program needs to check if any books in tblBooks are currently out on loan (i.e., the ReturnDate field is NULL). Which of the following code snippets correctly sets a Boolean flag bIsLoanedOut to True if at least one book is on loan and then stops searching?

• bIsLoanedOut := False; while not tblBooks.EOF do begin if tblBooks['ReturnDate'].IsNull then bIsLoanedOut := True; tblBooks.Next; end;

• bIsLoanedOut := False; tblBooks.First; while not tblBooks.EOF do begin if tblBooks['ReturnDate'].IsNull then begin bIsLoanedOut := True; Break; end; tblBooks.Next; end;

• bIsLoanedOut := tblBooks['ReturnDate'].IsNull;

• if tblBooks.Locate('ReturnDate', Null, []) then bIsLoanedOut := True;

Answer explanation

The correct choice initializes bIsLoanedOut to False, iterates through tblBooks, and sets bIsLoanedOut to True if a book is on loan, then uses Break to stop further searching, making it efficient.

You have two tables: tblAuthors (with AuthorID as a primary key) and tblBooks (with BookID as a primary key). To link a book to its author, what must be included in tblBooks?

• A field that stores the author's name.

• A foreign key field that stores the AuthorID from tblAuthors.

• A copy of the entire tblAuthors table inside tblBooks.

• A field named BookAuthor with a text data type.

Answer explanation

To link a book to its author, tblBooks must include a foreign key field that stores the AuthorID from tblAuthors. This establishes a relationship between the two tables, allowing for proper data association.