ch3

Place the clauses of the SELECT statement in their operational order. FROM WHERE SELECT

1 2 3

Which of the following is information the DDL features of SQL can specify about a relation? A set of indices to be maintained for each relation The integrity constraints The type of an individual attribute within the relation. The JDBC connector. The schema about each relation.

A set of indices to be maintained for each relation The integrity constraints The type of an individual attribute within the relation. The schema about each relation.

Views are considered updatable if which of the following conditions are satisfied: The query has group by or having clauses. Any attribute not listed in the select clause can be set to null. The select clause contains only attribute names of the relation and does not have an expressions, aggregates or distinct specification. The from clause only had one database relation

Any attribute not listed in the select clause can be set to null. The select clause contains only attribute names of the relation and does not have an expressions, aggregates or distinct specification. The from clause only had one database relation

A transaction is a single operation that performs multiple logical functions within a database. True False

False

An assertion is a predicate expressing a condition that you want to ensure never occurs within the database. True False

False

An attribute takes a null value when the value of the attribute is computed to be zero. True False

False

Indices are created by using the "GRANT index {index-name} on {relation-name} using {field-name}" command. True False

False

SQL specifies strings by enclosing them in double quotes.

False

The "UPDATE TABLE" command is used when we wish to change the structure of a table. True False

False

The NOT NULL specification allows the insertion of a null value for the associated attribute. True False

False

The ORDER BY clause lists items in descending order by default.

False

The SQL phrase "ON DELETE CASCADE" is associated with referential integrity as it applies to the WHERE clause. TRUE FALSE

False

We can disallow null values by specifying NOT NULL as a part of the field definition. This MUST be specified when declaring the primary key. True False

False

We create a table in SQL using the insert table command. True False

False

When using the GROUP BY clause, you cannot have a column in the select clause that is not in the grouping-list. True False

False

In the circumstances where we would like to apply an aggregate function to a group of sets of tuples, we specify this wish using which of the following clauses? HAVING GROUP BY ORDER BY WHERE

GROUP BY

Which of the following operations require the relation instances they are operating on to be union-compatible? Intersection Set-difference Cross-product Natural join Union

Intersection Union Set-difference

In SQL, we can using pattern matching to match strings. The pattern '__% Cubs' does which of the following (note, there are 2 underline characters before the %): Matches any string of at least 2 characters followed by a space and Cubs. Matches any string preceeded by Ernie Banks. Matches any string ending in % and followed by a space and Cubs Matches any string of exactly 3 characters followed by a space and Cubs Matches any string of exactly 3 characters preceeded by a space and Cubs.

Matches any string of at least 2 characters followed by a space and Cubs.

Match the clause of the SELECT statement to its definition. SELECT FROM WHERE

S-list the attributes described in the result of the query F- lists the relations to be scanned in the evaluation of the expression. W-consists of the predicate involving attributes of the relations

Which of the following is true about SQL? SQL is both a Data Relational Language (DRL) and a Data Transactional Language (DTL). SQL is a Turing complete language. SQL is both a Data Manipulation Language (DML) and a Data Definition Language (DDL). SQL stands for Standardized Query Language. SQL was first created in the Multics systems in the 1970s.

SQL is both a Data Manipulation Language (DML) and a Data Definition Language (DDL).

Do the queries below return the same result? SELECT name, employee_deptFROM employee, emp_deptWHERE employee.id = emp_dept.id; SELECT name, employee_deptFROM employee NATURAL JOIN emp_dept; Same Different

Same

What is the concept of referential integrity? That a foreign key entry in one table must point to a valid record in the table associated with the foreign key. That a candidate key must be unique. That a candidate key must point to a valid record in a table. That a primary key must be unique.

That a foreign key entry in one table must point to a valid record in the table associated with the foreign key.

We create views for which of the following purposes? To overlook Poudre Canyon. To oversee all the activities of a database. To provide a simplified view of the database for users. To provide a restricted set of tables to a set of users.

To provide a simplified view of the database for users. To provide a restricted set of tables to a set of users.

An identifer that is used to rename a relation is referred to as a correlation name in the SQL standard. True False

True

An index on an attribute of a relation is a data structure that allows the database system to find those tuples in the relation that have a specified value for that attribute efficiently. True False

True

Atomicity refers to the requirements that either all of a transaction must occur or none of it. True False

True

For two relation instances to be union compatible, the must have the same number of fields and the corresponding fields must have the same domain. True False

True

Outer join query operations can be implemented by a simple extension of join algorithms. True False

True

SQL allows the use of null values to indicate absence of information about the value of an attribute. True False

True

The HAVING clause, if it is present, is applied to each group of the GROUP BY clause. True False

True

The natural join operation operates on two relations and produces a single relation as the result. True False

True

View names may appear in a query any place where a relation name may appear. True False

True

We define a view in SQL by using the create view command. True False

True

We remove a relation from an SQL database using the drop table command. True False

True

Match the join type to the expected effect in the resulting relation. Right outer join Left outer join Full outer join

Tuples from the right-hand-side relation that do not match any tuple in the left-hand-side relation are padded with nulls. Tuples from the left-hand-side relation that do not match any tuple in the right-hand-side relation are padded with nulls. Both the left and the right side relations are extended and filled with nulls for the non-existent values.

What are the three basic clauses of a SELECT statement? WHERE clause KEY clause FROM clause SELECT clause SANTA clause

WHERE FROM SELECT

Match the domain type to its description. char(n) varchar(n) int smallint numeric real, double-precision

a fixed-length character string. A variable length character string integer A small integer A fixed-point number with user specified precision. Floating-point and double-precision floating point numbers

If a view relation is stored, but the DBMS makes sure that if the actual relations in the view definition change the view is kept up to date, the view is called: a materialized view a security view a stored procedure a function

a materialized view

Which of the following are data types the SQL standard supports? date time calendar localtime timestamp

date timestamp time

SELECT __________ forces the elimination of duplicates.

distinct

A nested query is a query that has another query embedded within it; the embedded query is called a recursive query.

false

Which clauses of the SELECT statement can the AS clause appear? from clause where clause select clause

from clause select clause

The _________________ command is used to insert data into a relation.

insert

Which of the following are set operations within SQL? intersect except distinct order by union

intersect except union

Which of the following are aggregate functions? min count avg left inner join left outer join

min count avg

To remove an authorization, we use the ________________command.

revoke

____________________ are a scheme that allows authorizations to be assigned to a set of responsibilities, and people to be assigned to those responsibilities.

roles

Which of the following are common subqueries - a select-from-where expression nested within another query? test for empty relations test for correct ordering of relations set comparison test for the absence of duplicate tuples set membership

test for empty relations set comparison test for the absence of duplicate tuples set membership

One key difference between a natural join and an inner join is: the number of columns returned how missing columns are handled how the group by clause is handled how the ordered by clause is handled.

the number of columns returned

Match the result of handling an unknown value in the "and" boolean operation. true and unknown false and unknown

unknown false

What are the 3 mechanisms to modify a database? update deletion recovery initialization insertion

update deletion insertion

What are the two possible SQL statements that must end a transaction? Conclude work Rollback work Revoke work Commit work

x-conclude work x-revoke work