Question 3

Explanation

True Statements:

• You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command. (This refers to unloading data from the database to external files using the external table mechanism, which is a valid use case).

• Their data can be retrieved by using only SQL or PL/SQL. (External tables are designed to be queried using standard SQL, and PL/SQL can execute SQL).

Examine each statement regarding external tables:

• You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.

  • While you can use CREATE TABLE AS SELECT from an external table to populate a regular database table, you generally don't "populate" an external table from existing data in the database in the same way you would a regular table. External tables are typically used to query data that resides outside the database (e.g., flat files). However, some database systems (like SQL Server with PolyBase) do allow CREATE EXTERNAL TABLE AS SELECT to export data from the database to an external location. In Oracle, you can use the external table framework to unload data from a database table using CREATE TABLE ... ORGANIZATION EXTERNAL AS SELECT. So this statement has some truth to it, specifically in the context of unloading data to an external location using the external table framework.

• SELECT DML statements can be used on them.

  • SELECT is a Data Query Language (DQL) statement, not a Data Manipulation Language (DML) statement. DML statements include INSERT, UPDATE, and DELETE. External tables are read-only, meaning you cannot use INSERT, UPDATE, or DELETE on them. However, you absolutely can use SELECT to retrieve data from them. So, the phrasing "SELECT DML statements" makes this statement inaccurate as SELECT is not a DML statement. If it said "SELECT statements can be used on them," it would be true.

• Their data can be retrieved by using only SQL or PL/SQL.

  • External tables provide a way to access external data using standard SQL queries. PL/SQL can also be used to execute SQL queries against external tables. This statement is generally true.

• Their metadata and actual data are both stored outside the database.

  • This is incorrect. The metadata (the table definition, column names, data types, and location of the external files) of an external table is stored within the database's data dictionary. The actual data resides outside the database in flat files (or other external sources).

• Indexes can be created on them.

  • This is incorrect. External tables are read-only and you cannot create indexes on them. Queries against external tables typically perform full table scans on the external data.

Based on the analysis, the true statements are:

1. You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command. (Specifically for unloading data to an external location using the external table framework in some database systems like Oracle with ORACLE_DATAPUMP access driver, or SQL Server's CETAS).

2. Their data can be retrieved by using only SQL or PL/SQL. (This is the primary way to interact with external tables).

In Oracle, CREATE TABLE ... ORGANIZATION EXTERNAL AS SELECT is used to unload data from an existing database table into external files, where the external table then refers to these newly created files. This is a form of "populating" the external files from existing data in the database.