Chapter 3 Data Base

Which symbol is used in standard SQL as a wildcard to represent a series of one or more unspecified characters?

% (percent sign)

3) Which of the following is not one of the five SQL built-in functions?

A) MODE

Which of the following is not a standard data type used in SQL?

A) Text

Which of the following cannot be done using the CONSTRAINT phrase?

ALL of the above can be done using the CONSTRAINT phrase.

) Existing tables in a database can be structurally modified by using the SQL ________ command

ALTER TABLE

Microsoft Access SQL allows table structures to be modified by using the standard SQL ________ command.

ALTER TABLE

4) Which of the following data types used in SQL would define a fixed-length text field of 10 characters?

B) char(10)

A composite primary key can be defined using the CONSTRAINT phrase in which SQL command?

C) CREATE TABLE

9) Which of the following is the correct SQL clause to sort the results of a SELECT query in reverse-alphabetic order using the Department field?

C) ORDER BY Department DESC

Which of the following is not true about primary keys?

C) Primary keys must be a single attribute.

Given the table CUSTOMER(CustID, Name, PhoneNum, AcctBalance) what is the standard SQL query phrase to retrieve the Name and Phone Number of customers?

C) SELECT Name, PhoneNum

2) Which SQL keyword is used to specify a condition that rows must meet to be included in the results of an SQL SELECT query?

C) WHERE

) Given the table CUSTOMER(CustID, Name, PhoneNum, AcctBalance) what is the standard SQL query phrase to retrieve data for customers with an account balance greater than 50?

C) WHERE AcctBalance > 50

Which of the following data types used in SQL would define a numeric field of the pattern 99.99

C) numeric(2,2)

The order of the columns returned by an SQL SELECT statement is determined by the

C) order they are listed in following SELECT.

The values of existing data can be modified using the SQL ________ command, which can be used to change several column values at once.

CHANGE

6) Which SQL keyword is used to eliminate duplicate rows in the results of an SQL SELECT query?

D) DISTINCT

Which of the following standard SQL data types is not supported in Microsoft Access SQL?

D) Numeric(5,3)

) The values of existing data can be removed from a table using the SQL ________ command, which can even be used to remove all the data in the table at one time.

DELETE

A complete table, including both the table structure and any data in the table, can be removed from the database by using the SQL ________ command.

DROP

5) Which of the following SQL commands would be used to remove both the data and the table structure of a table named STUDENT?

E) DELETE FROM STUDENT;

Which of the following SQL commands would be used to remove only the data from a table named STUDENT while leaving the table structure intact?

E) DELETE FROM STUDENT;

8) Which SQL keyword can be used in conjunction with wildcards to select partial values?

E) LIKE

Given the tables STUDENT(StudentID, StudentName, AdvisorID) ADVISOR(AdvisorID, AdvisorName, Office, Phone) which of the following SQL statements would be used to implant a join between the two tables?

E) WHERE STUDENT JOIN ADVISOR ON STUDENT.AdvisorID = ADVISOR.AdvisorID

To open a new Microsoft Access Query window ________.

E) click the Query Design button on the Command tab.

Microsoft Access SQL commands are run in ________.

E) the SQL View of a Query window.

) Data from a maximum of three tables can be combined through the use of subqueries in SQL.

F

) For a column to be defined as the primary key using table constraints, the column must have been given the property NULL.

F

) In SQL, the order of the rows that result from a SELECT statement can be set using the SORT BY phrase.

F

) Microsoft Access can run QBE queries, but not SQL queries.

F

In SQL, the WHERE clause is used to specify which columns will be included in the result.

F

In SQL, to refer to a range of values in a WHERE clause, use the WITHIN keyword.

F

Microsoft Access SQL supports and will correctly implement the numeric data type with (m,n) notation.

F

The ON DELETE CASCADE referential integrity constraint does not apply when rows are deleted using the SQL DELETE command.

F

The TOP built-in function in SQL is used to find the maximum value in a numeric column.

F

The result for SELECT statements in SQL is a relation unless the result is a single number.

F

Values of existing data can be changed using SQL through the CHANGE command.

F

When using SQL to create a table, a column is defined by declaring, in this order: data type, column name, and optional constraints.

F

When using SQL to create a table, specifying the NULL property for a column indicates that only null values may be stored in that column.

F

Which SQL keyword is used to apply conditions to restrict groups that appear in the results of a SELECT query that uses GROUP BY?

Having

SQL was developed by ________ in the late 1970s

IBM

Given the table STUDENT(StudentID, Name, Advisor), which of the following SQL statements would be used to add new student data to the STUDENT table?

INSERT INTO STUDENT VALUES (123, 'Jones', 'Smith');

The SQL command used to add new data to a table is ________.

Insert

Built-in SQL functions cannot be applied to data combined using the GROUP BY keyword.

N/A

The SQL keyword(s) ________ mean(s) that a value must be supplied before a new row can be created.

NOT NULL

An SQL data type of ________ means that values consist of seven decimal numbers with two numbers assumed to the right of the decimal point.

Numeric(7,2)

Using SQL commands, multiple tables can be queried so that all the values from one table appear in the results, regardless of whether they have one or more corresponding values in the other table, by using the SQL ________ command.

OUTER JOIN

Which type of join, although not included in standard SQL, was created to allow unmatched rows to appear in the result of a join operation?

Outer Join

) In Microsoft Access, the default method of creating and running a query is ________.

Query By Example (QBE)

Microsoft Access SQL commands are run in the ________ of a Query window.

SQL View

One way to specify all of the columns of a table is to use the special character * after the SQL keyword ________.

Select

In Microsoft Access, tables are added to a Query window by ________.

Selecting the tables from the Show Table dialog box.

In Microsoft Access, tables are added to a QBE Query window by selecting the tables from the ________ dialog box.

Show Table

SQL stands for ________.

Structured Query Language

) In SQL, the IS NULL keyword can be used to select on records containing NULL values in a particular column.

T

) The basic idea of a join is to combine the contents of two or more relations into a new relation.

T

) To force the DBMS to remove duplicate rows from the results of an SQL SELECT query, the keyword DISTINCT must be used.

T

A subquery is appropriate only if the final result contains only data from a single table.

T

Data is added to a table using the SQL INSERT command.

T

If you need to create a primary key that is a composite key using SQL, the key may be defined when the table is created using the CREATE TABLE statement.

T

In Microsoft Access, default values must be set as a field property while the table is in Design View.

T

In SQL, multiple conditions in the WHERE clause can be combined by using the SQL AND keyword.

T

In SQL, the LIKE keyword can be combined with the NOT keyword to form the NOT LIKE condition for selecting values.

T

In SQL, the LIKE keyword can be used to select on partial values.

T

In SQL, the NOT keyword can be combined with the IN keyword to form the NOT IN condition for selecting values

T

Microsoft Access SQL commands are run within the SQL View of a Query window.

T

Referential integrity constraints can be created using the ON DELETE phrase when the table is created using the CREATE TABLE statement.

T

Referential integrity constraints using the ON DELETE NO ACTION phrase may be explicitly stated when the table is created using the CREATE TABLE statement.

T

SQL is a data sublanguage, not a complete programming language.

T

Standard SQL does not allow built-in functions to be used in a WHERE clause.

T

The SQL GROUP BY keyword can be used to group rows by common values.

T

The SUM built-in function in SQL is used to total the values in a numeric column.

T

To open a new Microsoft Access Query window, click the Query Design button on the Create command tab.

T

Using standard SQL, unmatched rows will not appear in the result of a join.

T

When using SQL to create a table, specifying a data type of Char(10) indicates a fixed length field of 10 characters.

T

The ________ statement removes all data from a table while leaving the table structure, and also resets any surrogate primary key values back to the starting point.

TRUNCATE TABLE

Given the table STUDENT(StudentID, Name, Advisor), which of the following SQL statements would be used to change the value of the Advisor field to 'Smith' for all rows in the STUDENT table?

UPDATE STUDENT SET Advisor = 'Smith';

An SQL data type of ________ would indicate a variable-length character string with maximum length 75.

VarChar(75)

Conditions in an SQL INSERT command and after the SQL ________ keyword require single quotes around values for Char and VarChar columns, but not around values for Integer and Numeric columns.

WHERE

Which of the following is the correct SQL clause to restrict the results of a SELECT query to only records that have a value in the range of 10 to 50 in the Hours column?

WHERE Hours BETWEEN 10 and 50

SQL is not a complete programming language, but is rather a(n) ________.

data sublanguage

Using SQL commands, multiple tables can be queried using either subqueries or ________.

joins

Table and relationship properties not supported by Microsoft Access SQL can usually be set ________ in the appropriate window or dialog box.

manually

) In Microsoft Access, standard SQL table creation syntax items not supported by Access SQL can generally be implemented by ________.

setting the field properties in Access table Design view.

Which symbol is used in standard SQL as a wildcard to represent a single, unspecified character?

underscore)