I85PREP
⌘ K
Denodo Platform 9 Certified Developer Professional study materials
Denodo Platform 9 Certified Developer Professional study resources

Denodo Platform 9 Certified Developer Professional practice questions

One question at a time. Build your Denodo Platform 9 Certified Developer Professional understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Virtual DataPort: Data Sources, Base Views, and Connection Management

In the Denodo Virtual DataPort Administration Tool, which object type is created when you register the connection details of an external relational database so that its tables can later be exposed as views?

  1. A derived view
  2. A data source Correct answer.
  3. A base view
  4. A data movement job

Explanation

A data source stores the connectivity information, such as the JDBC driver, URL, and credentials, for an external system. Base views are created on top of that data source to expose specific tables or queries as virtual relations. The data source itself holds no query definition until a base view references it.

Question 2Virtual DataPort: Data Sources, Base Views, and Connection Management

Which Denodo Virtual DataPort data source adapter type should be chosen to access a database through a standard JDBC driver?

  1. The Custom data source
  2. The ODBC data source
  3. The Generic JDBC data source Correct answer.
  4. The Web Service data source

Explanation

The Generic JDBC adapter is designed to connect to any relational database for which a compliant JDBC driver is available. You supply the driver class, the connection URL, and credentials. The other adapter types target different protocols or extension mechanisms.

Question 3Virtual DataPort: Data Sources, Base Views, and Connection Management

When you create a base view from a relational data source, which option lets you expose the result of a SQL statement rather than a single physical table?

  1. Enabling the cache module
  2. Setting the delegation policy
  3. Defining the base view from a query Correct answer.
  4. Configuring a swap source

Explanation

A base view can be created either from a table or from a SQL query executed against the underlying database. Defining it from a query lets you push joins, filters, or projections down to the source and treat the result as a virtual table. The other options affect performance or source substitution, not the shape of the view definition.

Question 4Virtual DataPort: Data Sources, Base Views, and Connection Management

A developer wants every query against a base view to be rewritten to a different data source at runtime without editing the view. Which Denodo Virtual DataPort feature provides this?

  1. Cache mode
  2. A derived view
  3. Swap sources Correct answer.
  4. A remote table

Explanation

Swap sources let you substitute one data source for another so the same views resolve against a different environment, such as production instead of development. The view definitions remain unchanged because the substitution happens at the data source level. This is commonly used for promoting configurations between environments.

Question 5Virtual DataPort: Data Sources, Base Views, and Connection Management

Which statement best describes the role of a base view in the Denodo Virtual DataPort model?

  1. It permanently copies source rows into the Denodo repository.
  2. It exposes data from a data source as a virtual relation that can be queried in VQL. Correct answer.
  3. It defines only the connection credentials for an external system.
  4. It stores the execution plan of every query issued against the server.

Explanation

A base view wraps a table or query from a data source and presents it as a relation that can be queried with VQL like any other view. It does not copy data by default; data is read from the source at query time unless caching is configured. Connection credentials belong to the data source, not the base view.

Question 6Virtual DataPort: Data Sources, Base Views, and Connection Management

When configuring a JDBC data source in Virtual DataPort, which setting determines whether the server validates the connection immediately upon saving?

  1. The delegation policy
  2. The query timeout of the base view
  3. The Test connection action on the data source configuration Correct answer.
  4. The cache preload option

Explanation

The Test connection action attempts to open a connection using the configured driver, URL, and credentials, confirming that the data source is reachable and valid. It is an explicit validation step rather than an automatic behavior of caching or delegation. Query timeout affects execution, not connection validation.

Question 7Virtual DataPort: Data Sources, Base Views, and Connection Management

A base view created from a relational source is queried with a filter on a column. What does Denodo Virtual DataPort typically do to optimize this query?

  1. It rejects the query because base views cannot be filtered.
  2. It always retrieves all rows and filters them in the Denodo server memory.
  3. It delegates the filter to the underlying database when the source supports it. Correct answer.
  4. It converts the filter into a new data source automatically.

Explanation

Virtual DataPort analyzes the query and pushes down operations such as filters and projections to the source when the adapter supports them. This reduces the volume of data transferred and leverages the source's processing power. Full retrieval and in-memory filtering is a fallback, not the default when delegation is possible.

Question 8Virtual DataPort: Data Sources, Base Views, and Connection Management

Which Virtual DataPort object should be created to expose a stored procedure from a relational database as a queryable object in Denodo?

  1. A derived view
  2. A base view from a table
  3. A data movement job
  4. A stored procedure view Correct answer.

Explanation

A stored procedure view wraps a procedure from the underlying database and exposes it so it can be invoked through VQL. Base views from tables expose tabular data rather than procedures. Derived views and data movement jobs serve different purposes.

Question 9Virtual DataPort: Data Sources, Base Views, and Connection Management

A developer needs to connect Denodo Virtual DataPort to a REST API that returns JSON. Which data source adapter is most appropriate?

  1. The ODBC data source
  2. The LDAP data source
  3. The JSON data source Correct answer.
  4. The Generic JDBC data source

Explanation

The JSON data source adapter is designed to read JSON documents from files or HTTP endpoints and expose them as relations. JDBC and ODBC adapters target relational databases, and the LDAP adapter targets directory services. Choosing the JSON adapter lets Denodo parse the JSON structure into a base view.

Question 10Virtual DataPort: Data Sources, Base Views, and Connection Management

When creating a base view from a JSON data source, what must usually be specified so Denodo can map the document structure to columns?

  1. A tuple root and the JSON path expressions for each field Correct answer.
  2. A JDBC connection URL and driver class
  3. A stored procedure name and parameter list
  4. An ODBC DSN and user credentials

Explanation

JSON documents are hierarchical, so the adapter needs to know where the repeating tuple begins and how each output column maps to a field path. This is expressed through the tuple root and path expressions in the view definition. JDBC, ODBC, and stored procedure settings apply to relational sources.

Question 11Virtual DataPort: Data Sources, Base Views, and Connection Management

Which Denodo Virtual DataPort data source type is used to read data from delimited text files stored on a file system?

  1. The Web Service data source
  2. The Delimited File data source Correct answer.
  3. The XML data source
  4. The Generic JDBC data source

Explanation

The Delimited File adapter reads structured text files such as CSV or TSV and exposes them as relations. It handles delimiters, headers, and encoding settings. The XML, Web Service, and JDBC adapters target other formats and protocols.

Question 12Virtual DataPort: Data Sources, Base Views, and Connection Management

A developer configures a data source with a username and password. Where in Virtual DataPort are these credentials stored for the data source?

  1. In the cache database tables
  2. In each base view definition individually
  3. In the VQL query text of derived views
  4. In the data source configuration, optionally protected by the credential store Correct answer.

Explanation

Credentials are part of the data source configuration and can be secured using the credential store so passwords are not stored in clear text. Base views reference the data source rather than duplicating credentials. Derived views and cache tables do not hold connection passwords.

Question 13Virtual DataPort: Data Sources, Base Views, and Connection Management

What is the effect of setting a base view to use the cache module in 'full' mode?

  1. Query results are stored and subsequent identical queries can be served from the cache. Correct answer.
  2. All filters are prevented from being delegated.
  3. The data source connection is disabled after the first query.
  4. The view definition is copied into the source database.

Explanation

The cache module stores the results of queries so that later queries can be answered without hitting the source, improving performance and reducing load. Full cache mode preloads or stores the whole result set according to configuration. It does not alter the source database or disable delegation.

Question 14Virtual DataPort: Data Sources, Base Views, and Connection Management

Which statement about a derived view in Virtual DataPort is accurate?

  1. It can only be built from a single base view with no joins.
  2. It stores its own JDBC connection to an external database.
  3. It replaces the need for a data source when accessing relational tables.
  4. It is defined by a VQL query over one or more other views. Correct answer.

Explanation

A derived view is defined by a VQL SELECT statement that references other views, which may include base views and other derived views. It can join, filter, and aggregate across multiple sources. It does not carry its own external connection; connectivity is handled by the underlying data sources.

Question 15Virtual DataPort: Data Sources, Base Views, and Connection Management

A developer wants to add a new column to a base view without recreating the entire view from the data source. Which approach is valid in Virtual DataPort?

  1. Disable the cache and re-run the query.
  2. Delete the data source and create a new one with the column.
  3. Convert the base view into a data movement job.
  4. Edit the base view definition and add the field mapped to the source column. Correct answer.

Explanation

Base view definitions can be edited to add, remove, or remap fields as long as the mapping to the underlying source is valid. This avoids recreating the view and losing dependent objects. Deleting the data source, converting to a job, or disabling the cache does not add a column.

Question 16Virtual DataPort: Data Sources, Base Views, and Connection Management

When a base view is created from a relational table, which metadata does Virtual DataPort typically import to define the view's schema?

  1. The execution plan of the source database
  2. The physical file locations of the table data
  3. The column names and data types from the source table Correct answer.
  4. The list of database users and their roles

Explanation

Virtual DataPort reads the table metadata through the JDBC driver to obtain column names, data types, and nullability, which form the schema of the base view. It does not import execution plans, user lists, or physical file locations. The schema can then be adjusted manually if needed.

Question 17Virtual DataPort: Data Sources, Base Views, and Connection Management

A developer needs to join data from two different relational databases in a single view. What is the recommended Virtual DataPort approach?

  1. Copy both tables into the cache and query the cache only.
  2. Create a single base view that references both databases directly.
  3. Use a data movement job to merge the tables into one source.
  4. Create a base view for each database and a derived view that joins them. Correct answer.

Explanation

Each database is registered as its own data source with a base view, and a derived view joins the two base views. This is the standard federation pattern in Virtual DataPort and allows the optimizer to push down operations where possible. A single base view cannot span two independent data sources.

Question 18Virtual DataPort: Data Sources, Base Views, and Connection Management

Which Virtual DataPort feature allows a developer to define a view whose data is physically stored in Denodo rather than read from an external source at query time?

  1. A materialized view Correct answer.
  2. A derived view
  3. A swap source
  4. A base view

Explanation

A materialized table, often created through the cache or data movement mechanisms, stores data physically in Denodo's repository. Base and derived views are virtual and read from their sources at query time unless cached. Swap sources redirect queries rather than store data.

Question 19Virtual DataPort: Data Sources, Base Views, and Connection Management

When configuring a data source, what does the 'Database adapter' or driver selection determine?

  1. How Virtual DataPort communicates with the external system and which capabilities it can delegate Correct answer.
  2. The number of users allowed to log in to Denodo
  3. The retention period of the query log
  4. The color scheme of the Administration Tool

Explanation

The adapter or driver selection determines the protocol and the set of capabilities Virtual DataPort can push down to the external system, such as filters, joins, and aggregations. It is central to query optimization. It does not control UI appearance, user limits, or log retention.

Question 20Virtual DataPort: Data Sources, Base Views, and Connection Management

A base view query performs poorly because the source database cannot delegate a complex function. Which Virtual DataPort mechanism can help by moving the computation into Denodo?

  1. Delegation policy settings that allow the function to be evaluated by the server Correct answer.
  2. The data source description field
  3. The credential store
  4. The swap source feature

Explanation

Delegation policies control which operations are pushed to the source and which are executed by Virtual DataPort. If the source cannot handle a function, the policy can allow the server to evaluate it instead. Credential storage, swap sources, and description fields do not affect query execution strategy.

Question 21Virtual DataPort: Data Sources, Base Views, and Connection Management

Which consideration is most important when choosing between creating a base view from a table versus from a query for a large source table?

  1. Whether the data source description is populated
  2. Whether the view name follows naming conventions
  3. Whether the Administration Tool is running in a browser
  4. Whether pushing filters and projections into the source query reduces transferred data Correct answer.

Explanation

Defining the base view from a query lets you push selective filters and projections to the source, which can dramatically reduce the data transferred for large tables. Naming conventions, descriptions, and client type do not affect data volume. The choice should be driven by performance and data movement.

Question 22Virtual DataPort: Data Sources, Base Views, and Connection Management

What happens in Virtual DataPort when a data source connection fails during query execution?

  1. The query silently returns an empty result set.
  2. Virtual DataPort automatically creates a new data source.
  3. The query returns an error indicating the connection problem, and dependent views cannot return data. Correct answer.
  4. The base view is deleted from the server.

Explanation

If the connection to the source cannot be established, the query fails with an error describing the connectivity issue, and views depending on that source cannot return data. Virtual DataPort does not silently substitute an empty result or create new data sources. The view definition remains intact for later retries.

Question 23Virtual DataPort: Data Sources, Base Views, and Connection Management

Which Virtual DataPort feature is designed to periodically refresh the data stored by a materialized view or cache?

  1. A swap source
  2. A cache refresh or scheduled job Correct answer.
  3. A credential store entry
  4. A stored procedure view

Explanation

Cache refresh schedules or jobs re-execute the underlying query to update the stored data so it reflects changes in the source. Swap sources, stored procedure views, and credential entries serve different purposes. Scheduling refresh is essential to keep materialized data current.

Question 24Virtual DataPort: Data Sources, Base Views, and Connection Management

A developer needs to expose a MongoDB collection in Virtual DataPort. Which data source adapter should be used?

  1. The Delimited File data source
  2. The LDAP data source
  3. The MongoDB data source Correct answer.
  4. The Generic JDBC data source

Explanation

Virtual DataPort provides a dedicated MongoDB adapter that understands the document model and query language of MongoDB. The Generic JDBC adapter is for relational databases, and the Delimited File and LDAP adapters target other systems. Using the MongoDB adapter enables proper delegation of filters to the database.

Question 25Virtual DataPort: Data Sources, Base Views, and Connection Management

When a base view is created from a relational data source, what does the 'Create base view' wizard allow regarding the primary key?

  1. It can import the primary key from the source or let the developer define one manually. Correct answer.
  2. It forces the view to have no primary key.
  3. It requires the primary key to be a string column.
  4. It creates a new primary key in the source database.

Explanation

The wizard can detect and import the primary key defined in the source table, or the developer can specify a primary key manually if the source does not have one or a different key is needed. This metadata helps the optimizer and affects caching behavior. It does not modify the source database schema.

Question 26Virtual DataPort: Data Sources, Base Views, and Connection Management

In Virtual DataPort, a developer wants a base view to read from a relational table but also wants Virtual DataPort to keep the imported schema synchronized when the source table gains columns. Which data source capability should the developer rely on?

  1. Creating a derived view over the base view so columns are inferred at query time
  2. Scheduled schema refresh of the base view, which re-reads source metadata and updates the view definition Correct answer.
  3. Enabling delegation of the SELECT list to the source so unknown columns pass through
  4. Setting the base view cache to full mode so the new columns are discovered automatically

Explanation

Virtual DataPort offers a refresh of a base view's schema that re-reads the source metadata and updates the view definition, and this refresh can be scheduled on the data source. Caching affects stored rows, not the view's column definitions, so it cannot discover new columns. Delegation concerns which parts of a query are pushed to the source, not metadata import.

Question 27Virtual DataPort: Data Sources, Base Views, and Connection Management

A developer must create a base view over a relational source, but the source table has no primary key and contains duplicate rows. Which Virtual DataPort concept explains why the resulting base view may return unexpected row counts when queried through joins?

  1. Virtual DataPort rejects base views without a primary key, so the view must be created from a query instead
  2. Virtual DataPort always pushes joins to the source, so duplicate handling is decided by the source optimizer only
  3. Virtual DataPort automatically adds a synthetic sequence key to every base view, so duplicates are always collapsed
  4. Virtual DataPort relies on the primary key declared in the base view to identify rows, so without one it cannot guarantee row identity Correct answer.

Explanation

The primary key of a base view tells Virtual DataPort which columns uniquely identify a row, which matters for operations such as join planning and cache management. When the source table has no key, the developer must decide how to declare one, and duplicates can produce inflated results. Virtual DataPort does not fabricate a hidden key, and it does not refuse to create such views.

Question 28Virtual DataPort: Data Sources, Base Views, and Connection Management

Which Virtual DataPort data source type should a developer choose to query an XML document that is retrieved over HTTP and parsed into fields?

  1. The JDBC data source, using an XML-capable driver registered in the driver list
  2. The LDAP data source, configured with the XML document as its search base
  3. The delimited text data source, with the delimiter set to a tag character
  4. The XML data source, which reads XML documents from a URL or file and exposes their structure Correct answer.

Explanation

Virtual DataPort provides a dedicated XML data source that can read XML from a URL, a local file, or a stream and then map elements and attributes to view fields. JDBC adapters target relational drivers, not XML documents. Delimited text and LDAP adapters have entirely different parsing and access models.

Question 29Virtual DataPort: Data Sources, Base Views, and Connection Management

A developer is configuring a connection pool for a JDBC data source in Virtual DataPort and wants to limit how long an idle pooled connection is kept before being discarded. Which data source setting addresses this requirement?

  1. The fetch size configured on the data source
  2. The delegation pattern configured for the data source
  3. The maximum idle time configured in the data source's connection pool settings Correct answer.
  4. The query timeout configured on the base view

Explanation

Connection pool settings on a JDBC data source include parameters that govern pool behavior, such as the maximum number of connections and how long idle connections may remain before being closed. Query timeout limits execution time of a statement, not the lifetime of an idle connection. Fetch size and delegation patterns affect data retrieval and query pushdown rather than pool retention.

Question 30Virtual DataPort: Data Sources, Base Views, and Connection Management

When defining a new JDBC data source in Virtual DataPort, what is the purpose of the connection URL field?

  1. It defines the SQL query that the base view will execute against the source
  2. It supplies the driver-specific address and parameters that the JDBC driver uses to locate the database Correct answer.
  3. It stores the encrypted password used to authenticate against the database
  4. It names the Virtual DataPort database where the base views will be stored

Explanation

The connection URL is passed to the JDBC driver and contains the host, port, database name, and driver-specific properties needed to reach the source. Credentials are held in separate user and password fields, and the target Virtual DataPort database is chosen elsewhere in the wizard. The SQL defining a view is specified when creating the base view, not in the data source URL.

Question 31Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

In Virtual DataPort, which object type is created when you save a query built in the Derived View editor rather than a view whose definition is stored as SQL text only?

  1. A base view that wraps the underlying tables
  2. A stored procedure published as a web service
  3. A materialized table stored in the cache database
  4. A derived view defined by a selection, projection, join, union or aggregation over other views Correct answer.

Explanation

A derived view is defined by a query over existing views, typically created through the graphical wizard that records selection, projection, join, union and aggregation steps. Base views, by contrast, wrap physical data sources and are created from the data source configuration.

Question 32Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

You need a derived view that returns only the columns customer_id and total_amount from an existing view named sales. Which relational operation does this represent in the Derived View wizard?

  1. Union
  2. Aggregation
  3. Selection
  4. Projection Correct answer.

Explanation

Projection restricts the output to a subset of columns while keeping all rows. Selection filters rows by a condition, which is a different step in the wizard.

Question 33Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

A derived view must return only orders whose status equals 'OPEN'. Which operation should you add in the Derived View definition?

  1. A projection that keeps the status column
  2. An aggregation grouped by status
  3. A selection with the condition status = 'OPEN' Correct answer.
  4. A union with a table of open orders

Explanation

Selection applies a filter condition to the rows produced by the previous node of the query tree. Keeping the status column via projection does not remove any rows.

Question 34Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

When you build a join in the Derived View wizard between two views, what does the resulting derived view return by default?

  1. Rows of the left view plus matching rows of the right view Correct answer.
  2. Only rows present in both views
  3. Only rows present in the right view
  4. All rows of both views, combined vertically

Explanation

The default join type in the wizard is a left outer join, so all rows of the left input are preserved and right-hand columns are null when no match exists. An inner join would return only matching rows.

Question 35Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

Two derived views are combined with UNION. What must be true of their output schemas for the union to be valid?

  1. Both branches must read from the same base view
  2. Both branches must be sorted by the same column
  3. The column names must be identical in both branches
  4. The number of columns must match and each pair must have compatible data types Correct answer.

Explanation

A union requires the same number of output columns and type compatibility between corresponding positions; names need not match because the first branch supplies the output names. Sorting is irrelevant to union validity.

Question 36Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

Which UNION behaviour should you expect from a union node created in the Derived View wizard?

  1. The union always fails if duplicates exist
  2. The union automatically groups rows by all columns
  3. Duplicate rows are removed by default
  4. Duplicate rows are kept unless you enable the distinct option Correct answer.

Explanation

The union node defaults to UNION ALL semantics, so duplicate rows are preserved for efficiency. You must explicitly request duplicate elimination, which introduces a distinct operation in the execution plan.

Question 37Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

In the execution plan of a query, which element indicates that two branches are combined without removing duplicates?

  1. UnionDistinct
  2. Join
  3. Aggregation
  4. UnionAll Correct answer.

Explanation

UnionAll concatenates the branch results and preserves duplicates, matching UNION ALL semantics. UnionDistinct adds a duplicate-removal step and is more expensive.

Question 38Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

A query filters a derived view on a column that originates in a remote base view. What does the optimizer attempt to do with that filter?

  1. Convert the filter into a new base view
  2. Push the condition down to the data source so fewer rows are transferred Correct answer.
  3. Discard the filter because it references a derived column
  4. Force the condition to run only in the client application

Explanation

Query pushdown moves conditions and projections to the underlying source query so the source returns fewer rows and columns. This reduces network traffic and source-side processing, which is the main optimization goal in Virtual DataPort.

Question 39Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

Which factor most directly prevents the optimizer from pushing a condition down to a JDBC data source?

  1. The condition uses a function that the source does not support Correct answer.
  2. The query includes an ORDER BY clause
  3. The condition references a column with a numeric type
  4. The derived view contains more than two columns

Explanation

Pushdown depends on the source's capabilities: if a function or expression cannot be delegated, the condition is evaluated in the Virtual DataPort server instead. Numeric types, column counts and ordering do not by themselves block delegation.

Question 40Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

You want to inspect which parts of a query were delegated to each data source. Which Virtual DataPort feature gives you this information?

  1. The schema export of the database
  2. The server log level set to DEBUG for the web container
  3. The execution plan trace of the query Correct answer.
  4. The cache status panel

Explanation

The execution plan trace shows the query tree and the source-specific queries sent to each data source. This reveals which operations were pushed down and which execute in the server.

Question 41Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

A derived view joins a large remote fact view with a small local view. Which join strategy generally reduces data transfer most?

  1. Broadcast the small view to the source and perform the join remotely Correct answer.
  2. Force a nested loop join in the server
  3. Disable pushdown for the join and use a union instead
  4. Transfer both views entirely to the server and join there

Explanation

When one side is small, shipping it to the remote source lets the join execute close to the large data, avoiding transfer of the fact rows. Joining both sides in the server moves much more data across the network.

Question 42Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

In Virtual DataPort, what is the purpose of the 'swap' optimization applied to joins?

  1. It replaces the join condition with an equality on all columns
  2. It converts the join into a union of both inputs
  3. It reorders the join inputs so the smaller or better-suited side drives execution Correct answer.
  4. It removes duplicate rows from the join output

Explanation

Join swapping changes which input is treated as the outer or driving side, which can enable pushdown or reduce intermediate data. It does not alter the join condition or the result set.

Question 43Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

Which statement best describes the difference between a selection and a join condition in a derived view?

  1. Selection matches rows between two inputs; join condition filters one input
  2. Selection filters rows from one input; join condition matches rows between two inputs Correct answer.
  3. Both always produce the same execution plan
  4. Selection changes column names; join condition changes data types

Explanation

A selection applies a predicate to the rows of a single input, while a join condition defines how rows of two inputs are matched. Both can be pushed down, but they serve different relational purposes.

Question 44Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

A derived view aggregates sales by region using SUM(amount) and GROUP BY region. Which statement about pushdown of this aggregation is correct?

  1. Aggregation pushdown requires the derived view to be materialized first
  2. Aggregation pushdown requires the GROUP BY column to be renamed
  3. Aggregation can be pushed to the source when the source supports grouping and the grouping columns are available there Correct answer.
  4. Aggregation is never pushed down and always runs in the server

Explanation

Grouping and aggregate functions can be delegated to a source that supports them, reducing the rows returned. If the source lacks support or the grouping depends on server-only expressions, the aggregation stays in the server.

Question 45Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

What happens when a derived view references another derived view in its definition?

  1. The inner definition is expanded and the combined query is optimized as a whole Correct answer.
  2. The outer view is rejected because nesting is not allowed
  3. The inner view is executed in the client and results are uploaded
  4. The inner view must be materialized before the outer view can run

Explanation

Derived views can be nested; the server expands the inner definition into the overall query tree and optimizes the combined tree. Materialization is optional and not a requirement for nesting.

Question 46Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

Which join type should you choose to return all rows from the right-hand view even when no matching row exists in the left-hand view?

  1. Left outer join
  2. Full outer join
  3. Right outer join Correct answer.
  4. Inner join

Explanation

A right outer join preserves every row of the right input and fills left-side columns with nulls when there is no match. A left outer join preserves the left input instead.

Question 47Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

A full outer join is required between two remote views on different data sources. What is the main optimization concern?

  1. Full outer joins are converted into unions automatically
  2. The join may need to execute in the server because not all sources support full outer joins Correct answer.
  3. Full outer joins cannot be expressed in Virtual DataPort
  4. Full outer joins always push down completely to any source

Explanation

Delegation depends on source capabilities; many sources support full outer joins, but when they do not, the server performs the join and more data must be transferred. The join is not silently rewritten as a union.

Question 48Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

Which cache-related action can improve response time for a derived view whose underlying data changes infrequently?

  1. Enable caching so results are stored and reused until invalidated Correct answer.
  2. Remove all selection conditions from the view
  3. Add more union branches to the definition
  4. Convert the derived view into a stored procedure

Explanation

Caching stores the results of a view so repeated executions avoid re-querying the sources until the cache is invalidated. It is appropriate when the underlying data changes rarely.

Question 49Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

In Virtual DataPort, which clause of a SELECT statement over a derived view is processed after the rows are returned from the sources?

  1. The join condition between two base views
  2. The projection of source columns
  3. The ORDER BY clause when sorting cannot be delegated Correct answer.
  4. The WHERE clause on a pushed-down column

Explanation

Sorting is delegated when the source supports it and the query shape allows; otherwise the server sorts the returned rows. WHERE conditions, join conditions and projections are more frequently pushed down.

Question 50Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization

What does the 'distinct' option on a derived view add to the execution plan?

  1. A duplicate-removal operation over the result rows Correct answer.
  2. A filter that removes null values
  3. A second join between the inputs
  4. A sort of the source tables before reading

Explanation

The distinct option eliminates duplicate rows from the derived view output, adding a duplicate-removal node to the plan. It does not filter nulls or add joins.