Database Midterm Practice

1.

1 point

Select the FALSE statement.

a table must have at least one row

a table must have at least one column

in many commercial DBMS products the maximum number of columns in a table is limited

in most commercial DBMS products the maximum number of rows in a table is unlimited or very large

rows in a table are considered to be unsorted

2.

1 point

In a relational database a synonym for column is

table

record

tuple

row

attribute

3.

1 point

The order of columns in a relational table (is)

based on the value of the foreign key

changes as data is inserted and deleted

optimized by the database engine

based on the value of the primary key

left to right as defined when the table is created

4.

1 point

In a ‘one to many’ relationship between 2 tables, foreign keys are stored in ____ tables.

special key

child

either parent or child depending on the nature of the relationship

parent

5.

1 point

You need to insert a new product into a Product table but the classification (a grouping such as “stationery”) into which this product fits is yet to be determined. What would likely be entered for the classification code assuming it is a 3 position character field?

000 (three zeros)

three spaces

N/A

high values

nothing

6.

1 point

A primary key created from two or more columns is referred to as a(n) _____ primary key.

composite

qualitative

indexed

non unique

value added

7.

1 point

The term “surrogate” is usually associated with

foreign keys

primary keys

file systems

indexes

cross table relationships

8.

1 point

A "thing" such as a Fixed Asset would likely be considered a(n) ___ in a relational model.

entity

field

value

column

attribute

9.

1 point

What is a key attribute?

Any property that is a Primary Key or a Foreign Key

The most common property of an entity.

The central entity in an ERD.

Any property that describes an entity.

A property that uniquely describes an entity.

10.

1 point

SQL provides:

data selection and retrieval

data definition

data integrity

access control

all of the above

11.

1 point

An application that uses SQL is portable from one DBMS to another

rarely, since there are so many standards and levels of compliance

never, since SQL is unique to IBM

usually, with minor modifications required

usually, but will likely require significant changes

always, since SQL is standardized

12.

1 point

SQL is a(n)

language designed for relational databases

OO language like Java

3rd generation language like COBOL

programming language unique to Microsoft

database engine

13.

1 point

What is the maximum character limit of a table name in Oracle 11?

20

50

200

30

14.

1 point

In Oracle, when a column is declared as a CHAR(x) data type, the maximum value of x is

32,767

2,147,483,648

2000

unlimited

4000

15.

1 point

What advantage is there to naming constraints?

The name can be used to assign the constraint to a second table without having to define the constraint in the second table

The constraint can be selectively overridden when new rows are added to a table

It will eliminate the need to make any modifications when porting SQL code from one product to another (e.g., Oracle to DB2)

It makes understanding certain error messages easier.

The same constraint name can be used more than onc

16.

1 point

Select the FALSE statement.

Each table can have only one primary key.

When defining a parent/child relationship, the ON DELETE clause is specified in the child.

A surrogate key is a number that is used by the data owners to identify particular rows (e.g a Student number).

A table may have multiple candidate keys from which the primary key is chosen.

Tables may have zero, one or more foreign keys.

17.

1 point

One of the following types of constraints was added to tables without using a 'CONSTRAINT' clause. Which one is it?

PRIMARY KEY

FOREIGN KEY

CHECK

UNIQUE

NOT NULL

18.

1 point

What command will list all columns, and their datatypes, of a table?

LIST *

SHOW

SELECT *

DESCRIBE

19.

1 point

Which of the following statements will correctly select all records from the City column of the Offices table, while changing the column header to "City Name Is"?

SELECT 'City Name Is', City FROM Offices;

SELECT "City Name Is", City FROM Offices;

SELECT City as 'City Name Is' FROM Offices;

SELECT City as "City Name Is" FROM Offices;

20.

1 point

Given the following select statement:

SELECT Name
FROM SalesReps
WHERE Name LIKE '_a_';

which of the following would be a likely result set?

Jan Dan Man

Ann Dan Man

Fran Stan Dan

Jan Daniel Man

21.

1 point

Which of the following is an easier way to write this WHERE clause?

WHERE Quota >= 2000

AND Quota <= 3000

WHERE Quota BETWEEN 2000 AND 3000

WHERE Quota IS BETWEEN 2000 AND 3000

WHERE Quota BETWEEN 1999 AND 3001

WHERE Quota >= 2000 AND <= 3000

22.

1 point

What is the name of the symbolic constant that can be used in queries to represent the current date?

TODAY

CURRENT_DATE

TODAY()

CURRENTDATE

23.

1 point

What is the name of the function that can convert the value of any column to upper case?

UPPER(columnname)

LOWER(columnname)

TO_UPPER(columnname)

column data cannot be converted to upper case

24.

1 point

Under what condition must a column name be qualified with a table name?

All the time.

When selecting from one of your own tables.

When selecting from another users tables.

When selecting from one of your tables where another user has the same table name.

Never.

25.

1 point

Which of the following is not a type of join discussed in class?

Full-Inner Join

Equi-Join

Cartesian Join

Non-Equi Join Save

26.

1 point

Does this FROM clause

FROM Offices LEFT OUTER JOIN SalesReps

Mean the same thing as this FROM clause?

FROM SalesReps RIGHT OUTER JOIN Offices

True

False

27.

1 point

If there is a FULL-OUTER JOIN between two tables, what will be returned?

Return all rows where there is a match in both tables, plus any rows in the left table on the FROM clause that do not have a match on the right table.

Return all the rows from both tables whether they have a match in the other or not.

Return all rows where there is a match in both tables, plus any rows in the right table on the FROM clause that do not have a match on the left table.

Return all rows where there is a match in both tables.

28.

1 point

If there is a RIGHT-OUTER JOIN between two tables, what will be returned?

Return all rows where there is a match in both tables.

Return all the rows from both tables whether they have a match in the other or not.

Return all rows where there is a match in both tables, plus any rows in the right table on the FROM clause that do not have a match on the left table.

Return all rows where there is a match in both tables, plus any rows in the left table on the FROM clause that do not have a match on the right table.

29.

1 point

Which column function will count all records in a result set, regardless of the presence of null values?

COUNT()

COUNT(%)

COUNT(ColumnName)

COUNT(*)

30.

1 point

When using the AVG() column function, NULL values are included in the calculation.

True

False

31.

1 point

Select the true statement about column functions:

A column function takes an entire row of data and summarizes it into a single data item.

A column function takes an entire column of data and summarizes it into a single data item.

When using column functions, the WHERE clause cannot be used.

A column function takes the values of two columns and adds them together to create a new value.

32.

1 point

A SELECT statement that uses column functions can also include a WHERE clause.

True

False

33.

1 point

Select the true statements about HAVING clauses from the list below. (There are more than one.)

If a column function is used on the HAVING clause, it will be calculated after the GROUP BY, creating additional processing.

HAVING clause normally consists of one or more of the column functions applied to each of the groups.

HAVING is used to eliminate rows from the result set.

HAVING is used to eliminate groups from the result set.

34.

1 point

What is the correct order of execution of all of the following SQL clauses?

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

SELECT, WHERE, GROUP BY, HAVING, FROM, ORDER BY.

HAVING, SELECT, FROM, WHERE, GROUP BY, ORDER BY.

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

35.

1 point

A subquery can return many columns of data as its query result.

True

False

36.

1 point

The ORDER BY clause cannot be used in a subquery.

True

False

37.

1 point

Select each of the following statements that refers to Correlated Sub-Queries.

A query where the sub-query portion is executed many times.

The sub-query executes once for every row returned by the outer query.

The DBMS executes the inner query first, then the outer query.

The sub-query contains an outer reference (a column from the sub-query that is also in a table from the outer query).

A query where the sub-query portion is executed only once.

38.

1 point

Select each of the following statements that refer to Non-Correlated Sub-Queries.

The sub-query contains an outer reference (a column from the sub-query that is also in a table from the outer query).

A query where the sub-query portion is executed many times.

The sub-query executes once for every row returned by the outer query.

A query where the sub-query portion is executed only once.

The DBMS executes the inner query first, then the outer query.

39.

1 point

What does SQL lack that makes it different from languages like JAVA and VB .Net (select all that apply)?

Looping mechanisms

Conditional logic

Variable declarations

40.

1 point

Select the true statement:

Table rows are stored in sorted order

A table may have no rows

The number of columns in a table is unlimited

A table requires zero or more columns

41.

1 point

In a one to one relationship the foreign key is stored

In the child

In the parent

It doesn't really matter

42.

1 point

An entity is

A 'Thing'

A column

An attribute of a table

A vaule

43.

1 point

A CHAR(3) field that is set to null contains

Nothing

Three zeros

Spaces

44.

1 point

If you run this select:
SELECT *
FROM Customers
What does the ‘*’ give you?

The columns in the order they were created

All the columns in alphabetical order

The primary key columns

45.

1 point

If you run this select:
SELECT *
FROM Henry.Customers
What is ‘Henry.’?

Your user ID

The database name

The owners user id