SQL, DB2, and SSIS Quiz

Option A correctly uses SELECT * to retrieve all columns and ORDER BY to sort by ProductID. Option C uses ORDERED BY which is invalid SQL syntax. Option B filters by WHERE condition, and Option D only selects the ProductID column.

The HAVING clause filters groups after the GROUP BY aggregation. This query first groups sales records by customer, counts orders per customer, then returns only those customers whose order count exceeds 5. Option A misses the HAVING filter. Option C incorrectly describes the aggregation scope. Option D is false because the query will return records for customers meeting the condition.

To understand what a foreign key is, the user needs to have a basic understanding of database design.

The correct answer is:

C. A foreign key is a key field (column) in a database table, which relates the table to another table where the key is a primary key. The primary-foreign key relations are used to cross-reference database tables.

Explanation of options:

A. This option is incorrect because a foreign key cannot have NULL values. A foreign key must reference a valid primary key in another table.

B. This option is incorrect because a foreign key is not related to SQL locking mechanisms. SQL locking mechanisms are used to manage concurrent access to data.

D. This option is incorrect because a foreign key cannot be a primary key for that table. A primary key is a unique identifier for a table, while a foreign key is used to reference a primary key in another table.

INSERT INTO is the correct SQL syntax for adding records. Option C specifies the target table (Projects), lists columns to receive values (ProjectName, ProjectDescription), then provides the corresponding data in the VALUES clause. Options A and B omit required INTO keyword or column specification. Option D uses non-existent SAVE command.

The proper SQL BETWEEN syntax uses BETWEEN value1 AND value2 with a single column name and an asterisk to select all columns. Hence SELECT * FROM Sales WHERE Date BETWEEN '10/12/2005' AND '01/01/2006' is correct; the other forms misuse parentheses or omit the asterisk.

A self-join occurs when a table is joined to itself, typically to compare rows within the same table or establish hierarchical relationships. The table appears in both the FROM and JOIN clauses, often with different aliases. Option B's 'Selective Join' is not standard SQL terminology. Option C (Outer Join) preserves unmatched rows but isn't specific to self-joins.

To solve this question, the user needs to know what a reserved SQL keyword is. A reserved SQL keyword is a word that has a special meaning in SQL and cannot be used as a name for a table, column, or other database object.

Now, let's go through each option and explain why it is right or wrong:

A. GROWN: This option is incorrect because "GROWN" is not a reserved SQL keyword. It is not a commonly used SQL keyword and can be used as a name for a table, column, or other database object.

B. FROM: This option is correct. "FROM" is a reserved SQL keyword that is used to specify the table or tables from which to retrieve data in a SELECT statement. It is a commonly used SQL keyword and cannot be used as a name for a table, column, or other database object.

C. UPDATING: This option is incorrect because "UPDATING" is not a reserved SQL keyword. While "UPDATE" is a reserved SQL keyword that is used to modify data in a table, "UPDATING" is not a commonly used SQL keyword and can be used as a name for a table, column, or other database object.

D. DELETING: This option is incorrect because "DELETING" is not a reserved SQL keyword. While "DELETE" is a reserved SQL keyword that is used to remove rows from a table, "DELETING" is not a commonly used SQL keyword and can be used as a name for a table, column, or other database object.

The Answer is: B

SELECT COUNT(*) is the standard SQL command to count the total number of rows in a table. This works in DB2 as well as other SQL databases. SELECT DISTINCT returns unique rows, SELECT MAX returns the maximum value of a column, and ROWNUM is an Oracle pseudocolumn, not standard DB2 syntax.

SELECT DISTINCT is the correct SQL clause to eliminate duplicate values from query results. It returns only unique rows. UNION combines result sets, UNION ALL keeps duplicates, and DISTINCT ALL(*) is not valid SQL syntax.

DB2 is indeed a relational database management system (RDBMS) from IBM. It follows the relational model with tables, rows, columns, keys, and uses SQL as its query language. This is a fundamental characteristic of DB2.

In DB2, a single table can span multiple tablespaces. This is especially true for partitioned tables where different partitions can be stored in different tablespaces. Even non-partitioned tables can have their data and indexes in different tablespaces. The statement is true.

SSIS connection managers provide connectivity to various data sources. Analysis Services Connection Manager is indeed available for connecting to SSAS cubes and databases. This is a standard connection manager type in SSIS.

OLE DB Connection Manager is commonly used to connect to Oracle databases in SSIS. The statement claims OLE DB is NOT used, which is false. OLE DB providers for Oracle are standard in SSIS.

The Execute Process Task belongs to the Control Flow pane in SSIS, allowing external programs to be run as part of the workflow. Therefore the statement is true and the stored answer is correct.

SSIS packages can send emails directly using the Send Mail Task. The statement claims it is not possible, which is false. Send Mail Task is a standard control flow task in SSIS.

Script Task is used in Control Flow, while Script Component is used in Data Flow. They are different components. The statement claims Script component is used in both, which is false.

SSIS supports parent-child package relationships through the Execute Package Task. This allows modular package design where child packages can be called from parent packages.

OLE DB Command in Data Flow can execute stored procedures per row. It passes row values as parameters to the stored procedure. This is a valid approach for parameterized database calls in data flow.

A for‑each loop iterates over each element of a collection, repeating the enclosed task for every object. This description is accurate, making the True answer correct.