Db2 Basics, Part 1

The statement is true because when using the LIKE predicate with the "_" wildcard, it represents any single alphanumeric character. This means that the underscore can match any letter, number, or symbol in the specified position. For example, if we have a pattern "A_", it can match "AB", "AC", "A1", "A!", etc. So, the underscore acts as a placeholder for any single character in the search pattern.

The general form is: CREATE ObjectType [ObjectName] [clauses]

The equivalent term for CHARACTER LARGE OBJECT is CLOB. CLOB is a data type in database management systems that is used to store large amounts of character data. It is commonly used to store text or textual data that exceeds the limitations of a regular character data type. CLOB stands for Character Large Object, and it provides a way to efficiently store and retrieve large amounts of text or character data.

A DB2 database is a collection of related tables. Tables in a database are used to store and organize data in a structured manner. Each table represents a specific entity or concept, and the relationships between tables help to define the overall structure and organization of the database. By storing data in tables, it becomes easier to retrieve, manipulate, and analyze information efficiently. Therefore, tables play a crucial role in a DB2 database as they hold the actual data and provide the foundation for data management and retrieval operations.

If the keyword JOIN appears with no qualifiers, it means that the default type of join is INNER join. Therefore, the statement "If the keyword JOIN appears with no qualifiers, the keyword INNER is assumed" is true.

The form is: DROP [ObjectType] [ObjectName]

The equivalent term for CHARACTER VARYING is VARCHAR. Both terms are used in database systems to define a variable-length character string data type. They are interchangeable and can be used to store alphanumeric data of varying lengths.

An inner join, when using the JOIN keyword, combines rows from two tables based on a matching condition. It returns only the rows where there is a match between the tables. This means that every row in one table that has a corresponding row in another table is combined to produce a new record. Therefore, the statement is true.

A join allows retrieval of data from two or more tables by combining rows from different tables based on a related column between them. It is used to establish a relationship between tables and fetch data that meets certain criteria. The join operation combines rows with matching values in the specified columns, providing a way to connect and retrieve data from multiple tables in a single query.

The LEFT OUTER JOIN returns the same rows as an INNER JOIN, together with rows from the left table that would have been eliminated by the inner join. The RIGHT OUTER JOIN returns the same rows as an INNER JOIN, together with rows from the right table that would have been eliminated by the inner join. The FULL OUTER JOIN returns the same rows as an INNER JOIN, together with rows from both tables that would have been eliminated by the inner join.

The correct answer is Two or 2 because for every SQL statement executed, there are typically two messages that must go through the network. The first message is sent from the client to the server, containing the SQL statement. The second message is sent from the server to the client, containing the result or response to the SQL statement. Therefore, two messages are involved in the network communication for each SQL statement execution.

Client/server development focuses on separating the application logic and the code that interacts with a database. The application logic is placed on one or more client workstations, which are the clients, while the code that interacts directly with the database is placed on a central server. This architecture allows for a distributed system where the clients can access the application logic and interact with the server to retrieve or update data in the database.

When tables A, B, and C are joined to form a Cartesian product, every row in table A will be combined with every row in table B, and then each of these combinations will be combined with every row in table C. Since table A has 3 rows, table B has 4 rows, and table C has 5 rows, the total number of combinations will be 3 * 4 * 5 = 60 rows. Therefore, the result dataset will have 60 rows.

The wildcard "%" represents any sequence of alphanumeric characters (including zero characters).

Joining two or more tables that have a column in common is a type of inner join. In an inner join, only the rows that have matching values in both tables are included in the result set. This means that any rows that do not have a match in the other table are excluded. Inner joins are commonly used to combine data from multiple tables based on a shared column, allowing for more comprehensive analysis and retrieval of relevant information.

A DB2 database manages the storage, modification, and retrieval of data. DB2 is a relational database management system (RDBMS) developed by IBM. It provides a platform for organizing and storing data in a structured manner, allowing users to efficiently store, access, and manipulate data. With its robust features and capabilities, a DB2 database ensures data integrity, security, and reliability, making it an essential component for managing data in various applications and systems.

The correct answer is DATE, TIME, TIMESTAMP. These are the three data types used to store date and time information in a database. The DATE data type is used to store only the date, the TIME data type is used to store only the time, and the TIMESTAMP data type is used to store both the date and time together. These data types are essential for accurately recording and manipulating temporal data in a database system.

Example:
SELECT lastname, deptname
FROM employee e, department d
WHERE e.workdept = d.deptno

A server is a computer system that can host multiple instances of DB2, with each instance behaving like a separate installation of DB2. Each instance can then control multiple databases. This allows for efficient management and organization of data within the server environment.

Example:
SELECT lastname, deptname
FROM employee e INNER JOIN department d
ON e.workdept = d.deptno

After an inner join, the result dataset may be missing rows found in either or both of the join tables.

In a basic client/server environment, the SQL statement is sent through a network from the client workstation to a database server. The database server processes the statement and the results are sent back through the network to the client.

The correct answer is "cursor." In the context of the question, the explanation is that while the searched update uses one of the normal forms of the UPDATE statement, the positioned update uses a cursor. A cursor is a database object that allows for the retrieval and manipulation of data row by row, making it suitable for performing updates on specific records within a result set.

Similar to the update operation, the DELETE operation in a database can also involve searching or positioning. When performing a DELETE operation, the database needs to locate the specific data or records that need to be deleted. This may involve searching for the data based on certain criteria, such as a specific value or condition. Additionally, the DELETE operation may also require positioning the cursor or pointer to the correct location in the database to delete the desired data. Therefore, the DELETE operation can involve both searching and positioning, similar to the update operation.

Set operators and set operations are both mechanisms used to combine two or more queries into a single query. These terms are often used interchangeably, but they refer to the same concept. Set operators or set operations allow for the manipulation and combination of sets of data, such as union, intersection, and difference operations. By using these operators, multiple queries can be merged together to retrieve the desired information from a database or dataset.

SQL is a standardized language used to work with database objects, such as tables, views, indexes, and procedures, and the data they contain. It allows users to retrieve, manipulate, and manage data stored in a database. By using SQL, users can create, modify, and delete database objects, as well as perform various operations on the data, such as inserting, updating, and deleting records. SQL provides a way to interact with databases and perform tasks related to data management efficiently and effectively.

See example in Question 39.

SQL is non-procedural; thus, it is NOT an actual programming language.

Common table expressions (CTEs) are used to construct local temporary tables that reside in memory. This means that the table exists only for the duration of the SQL statement that defines it. Additionally, the table can only be referenced by the SQL statement that created it. CTEs can also be used in place of a view, providing a convenient way to define a temporary result set that can be referenced multiple times within a query. Therefore, all of the given statements about common table expressions are true.

The given code snippet is an example of a "searched" CASE expression. In this type of CASE expression, each condition is evaluated separately and the corresponding result is returned based on the condition that evaluates to true. In the given code, the CASE expression checks the value of the "job" column and returns a different result for different job values. If the job is "MANAGER" or "SUPRVSR", the salary is multiplied by 1.10. If the job is "DBA" or "SYS PROG", the salary is multiplied by a different factor.

A DB2 database can be explicitly cataloged using three methods: the Control Center, the Configuration Assistant, and the CATALOG DATABASE command. These methods allow users to manage and configure the database, including creating, modifying, and deleting database objects. The Control Center is a graphical user interface tool that provides a comprehensive view of the database and its components. The Configuration Assistant is a wizard-based tool that assists users in setting up and configuring the database. The CATALOG DATABASE command is a command-line interface command that allows users to catalog a database and make it accessible to applications.

The correct answer is "single" because when using set operators, such as UNION or INTERSECT, the results of each query are combined into a single result dataset. This means that all the individual query results are merged together to create a unified output.

BINARY is not a numeric data type because it is used to store binary data, such as images or files, rather than numerical values. Numeric data types, on the other hand, are used to store numbers and perform mathematical operations on them. Examples of numeric data types include SMALLINT, NUMERIC, DOUBLE, and REAL.

LARGEINT is not a numeric data type. The other options listed, DECIMAL, FLOAT, DOUBLE PRECISION, and BIGINT, are all numeric data types commonly used in programming and database systems. However, LARGEINT is not a standard numeric data type, and it is not commonly supported in most programming languages or database systems.

The given SQL statement selects all rows from two tables, TableName1 and TableName2, and concatenates each row from the first table with each row from the second table. This operation is known as a Cartesian product. In a Cartesian product, every possible combination of rows from both tables is returned, resulting in a large number of rows in the output.

The use of correlation names saves on typing, and may be easier to read. Example: d.deptno replaces department.deptno

The correct answer is DBCLOB, GRAPHIC, LONG VARGRAPHIC, VARGRAPHIC. These are the four double-byte character data types in alphabetical order. DBCLOB is used to store large character data, GRAPHIC is used for storing fixed-length graphical data, LONG VARGRAPHIC is used for storing variable-length graphical data, and VARGRAPHIC is used for storing variable-length character data.

When a DB2 database is created, it is automatically cataloged. Cataloging a database means that the necessary metadata information about the database, such as its name, location, and access permissions, is stored in the system catalog. This allows users and applications to easily locate and access the database when needed. By cataloging the database, it becomes part of the overall DB2 system and can be managed and utilized effectively.

When performing a positioned update or delete, a cursor must be created to establish a reference to the target row in the database. The cursor is then opened to make it available for use. Finally, the cursor must be positioned on the target row, indicating the specific location within the result set where the update or delete operation will be performed. This sequence of steps ensures that the cursor is properly initialized and ready to interact with the target row in the database.

A common table expression uses the "WITH" keyword to define the temporary table and the "AS" keyword to specify the query that will populate the table. The "WITH" clause is followed by the table name and column names in brackets, while the "AS" clause is followed by the select statement that retrieves the data for the table.

The given CASE expression is an example of a simple CASE expression. In a simple CASE expression, a single expression is compared to multiple possible values, and each value has a corresponding result. In this case, the expression being compared is the substring of the workdept column, and each possible value ('A', 'B', etc.) has a corresponding result ('ADMINISTRATION', 'BUSINESS', etc.).

The correct answer is INNER JOIN and FULL OUTER JOIN. These are not set operators but rather join operations used in SQL to combine rows from two or more tables based on a related column between them. Set operators, on the other hand, are used to combine rows from two or more SELECT statements and include UNION, EXCEPT ALL, and INTERSECT.

The data types CHARACTER, CLOB, LONG VARCHAR, and CHARACTER VARYING are all character data types commonly used in databases. However, DOUBLE CHARACTER is not a valid character data type. DOUBLE CHARACTER does not exist as a recognized data type in most database systems.

The Create Database wizard and the CREATE DATABASE command are both valid methods for creating a DB2 database. The Create Database wizard is a graphical interface that guides the user through the process of creating a database, allowing them to specify various options and settings. On the other hand, the CREATE DATABASE command is a SQL statement that can be executed directly in the DB2 command line or through a script. Both methods achieve the same result of creating a new database in DB2.

In a positioned update, the UPDATE statement must include a WHERE CURRENT OF clause. This clause allows the update to be performed on the current row of the cursor specified by its name. It is used to ensure that the update is applied to the correct row in the database. Without this clause, the update would not have a specific target row and could potentially affect multiple rows or none at all.

The correct answer is "BINARY LARGE OBJECT, XML". This is because the question asks for the binary and XML data types in alphabetical order, and the options "BINARY LARGE OBJECT" and "XML" are listed in alphabetical order. The option "BLOB, XML" is not in alphabetical order, so it is not the correct answer.