COSC3380 EXAM 2

Data must be in the database for an update query to make an update.

True

What does CRUD stand for?

create, read, update, delete

If the Name column has data type VARCHAR(20), then the expression Name BETWEEN 'Anele' AND 'Jose' is valid.

True

What are the two types of "wildcard characters"?

% and _

If a SELECT statement has no WHERE clause, the aggregate function processes all rows

True

The base tables are specified in the view query's FROM clause.

True

A materialized view is not stored by the database

False

What are the advantages of views?

Protect sensitive data
Save complex queries
Save optimized queries

Using materialized views always improves database performance.

False

Materialized views improve performance of SELECT statements that refer to the view

The performance of a user query on a view is identical to the performance of the corresponding merged query on base tables

True

A view query can reference another view table.

True

If a base table primary key does not appear in a view, an insert to the view generates a NULL primary key value. Since primary keys may not be NULL, the insert is not allowed.

True

Join view deletes are an effective method of deleting unnecessary data

False

The effect of the join view delete is undefined and therefore not allowed.

Reversing the order of the select and project operations alters the result of the expression.

False

Reversing the order of the select and project operations does not alter the result of the expression.

Query optimizers typically choose an optimal expression based on total number of rows processed.

False

The join operation is a low-level database action.

False

____________ languages combine data and procedures into classes.

Object-oriented

____________ languages do not explicitly specify how results are processed.

Declarative

____________ languages contain control flow statements but no classes.

Procedural

Programming languages are either __________ or declarative.

imperative

____________ is the leading declarative language.

SQL

A(n) ____________ determines how to process statements in a declarative language.

optimizer/compiler

C contains control flow statements and SQL does not. This is an example of the ______ gap.

paradigm

SQL uses many keywords, like SELECT, FROM, and WHERE. C relies heavily on punctuation, like {} and #. This is an example of the ______ gap.

syntax

Usually generates a network round trip for each SQL query.

Embedded SQL

Has become less popular with the rise of object-oriented programming.

Embedded SQL

Affords the greatest database security.

Procedural SQL

Procedural SQL is compiled in two steps, with a precompiler followed by a compiler.

False

This is embedded SQL

A stored procedure is compiled every time the procedure is called.

False

____________________ creates variables for a stored procedure.

DECLARE

RETURNS specifies a data type for the return value and appears between the CREATE FUNCTION clause and the body.

True

Stored functions may be executed from the MySQL command line.

True

The <parameter-declaration> has the same syntax in stored procedures and stored functions.

False

Can only contain IN, OUT, or INOUT

Embedded SQL statements appear between EXEC SQL and a semicolon.

True

Embedded SQL is commonly supported in which language?

C

A precompiler translates embedded SQL to ________________________________

Host language

A connection consists of a database address only.

False

A connection specifies authorization credentials for a database as well as the database address.

All embedded SQL statements require an active connection.

True

The ________ statement moves a cursor to the next row of a result table.

FETCH

The ________ statement executes a query.

OPEN

The ________ statement associates a cursor name with a query.

DECLARE

The ________ statement releases resources associated with a cursor.

CLOSE

The _____ statement assigns query results to shared variables.

FETCH

The ________ statement positions a cursor before the first row of a result table.

OPEN

A(n) _______ statement must appear before an EXECUTE statement without the IMMEDIATE keyword.

PREPARE

Shared variables appear in an EXECUTE statement with the ______ clause.

USING

Statements that are written explicitly in program code are ______ SQL.

static

The precompiler cannot compile ______ SQL statements.

dynamic

In the declaration section, shared variables are assigned a data type in:

the host language

Which API supports only one language ?

DB-API

Which API is often used with the C# language ?

ADO.NET

Database APIs support:

Databases and other data sources

A driver connects directly to the database.

True

The driver manager connects the application to the drivers.

True

Each database requires a different driver manager.

False

The driver manager communicates with all drivers using the same commands.

True

Every driver implements the full capabilities of the API.

False

A secondary API can be layered on top of a third API, for a total of three API layers.

True

A connection must always be created within the try clause of a try-except-else statement.

False

A connection must be created prior to executing any database operation.

True

Which MySQLCursor method should always be called prior to closing the cursor's connection?

close()

MySQL supports the semantic type MONEY.

False

Photographs are stored with the BYTEA type in PostgreSQL.

True

XML is a common ___________________ type.

document

Which database has both XML and JSON types?

PostgreSQL

Which database does not support object types?

MySQL

Both user-defined types AND system-defined types can be either simple or complex types

True

The only difference between the multiset and list types is that elements are ordered in a list value and not in a multiset value.

True

In the ______________ set type, the base type must be a group of character strings.

MySQL

Collection type implementations always conform to the SQL standard.

False

Not all collection types are standardized in SQL.

Functions that manipulate XML and JSON values are similar in most relational databases.

False

JSON documents can be stored as a VARCHAR type in MySQL.

True

When a document is inserted to a column with XML or JSON type, databases generate an error if the syntax is incorrect.

True

In MySQL, tables can be joined on columns with type JSON.

True

All spatial data is either two- or three-dimensional.

False

Spatial data types in MySQL, SQL Server, and PostGIS are:

Similar

When no SRID is specified, MySQL assumes SRID zero, which is a Cartesian reference system.

True

What is the distance equation for the Cartesian reference system?

sqrt(x^2 + y^2)

The _______________ function computes distance between two points.

ST_DISTANCE()

The bottom level of an R-tree index is sparse.

False

The bottom level is dense, not sparse.

If all blocks of an R-tree index are full, an insert to the spatial column always causes a block split.

True

When a value is deleted from a column with an R-tree index, MBRs in the top index level may become smaller.

True

A database method is:

Either a function or a procedure

How many methods can an object type have?

Zero, one, or many

NOT FINAL is required for supertypes that have subtypes.

True

What values are required to create a PDO object?

Hostname, database name, username, and password

UPDATE, SELECT, and ORDER BY are __________________ in an SQL statement

keywords

A view table provides which benefit when created in a database with multiple tables?

A consolidated view of specific data without changing the underlying database structure

When a database stores view data, it uses a _____________ that depends on data in a corresponding __________________

materialized view, base table

Which of the following is NOT one of the ways users can be provided with dynamic access to an Oracle database?

PL/SQL Web Toolkit

Which of the following is used to access the database server at time of executing the program and get the data from the server accordingly?

Embedded SQL

Which of the following header must be included in Java program to establish database connectivity using JDBC?

import java.sql*;

DriverManager.getConnection(_________,___________,____________)

What parameters are included?

URL or machine name where server runs, Password, User ID

What is missing from the join statement?

SELECT EmployeeName, Salary FROM Employee, Department WHERE Salary > 50000

A column from Employee is not compared to a column from Department

The EXEC SQL keyword is used in procedural SQL

False

The ______________ statement moves a cursor to the next row of a result table

FETCH

Which API often uses the C# language?

ADO.NET

1415 can be stored as an INT type in Oracle database

False

A stored procedure is compiled every time the procedure is called

False

A(n) ____________ will retrieve rows from a query that has multiple rows in the result set

cursor