Chapter 3 Database

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

% (percentage symbol)

7) Conditions after the WHERE keyword require single quotes around the values for columns that have which data type?

Char & VarChar

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

Chart (10)

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

Create Table

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

Create a single attribute primary key, define a foreign key, establish a referential integrity constraint (All the above)

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

DateTime

19) 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?

Delete from Student

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

Distinct

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

Drop table student

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

False

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

False

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

False

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

False

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

False

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

False

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

False

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

False

54) 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.

False

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

False

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

False

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

False

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

False

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

False

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

False

25) 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

26) 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")

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

Like

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

Mode

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

Numeric (2.2)

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?

Order by department DESC

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

Order they are listed in following select

21) 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

16) Which of the following is not true about primary keys?

Primary keys must be a single attribute

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

Select Name, PhoneNum

27) In Microsoft Access, tables are added to a Query window by

Selecting the tables from the show table dialogue box

30) 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

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

Text

12) Microsoft Access SQL commands are run in

The SQL View

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

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

True

63) 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.

True

29) 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

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?

Where

11) 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

Where AcctBalance> 50

22) 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

20) Given the tablesSTUDENT(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?

Where student join advisor on Student.AdvisorID=ADVISOR.AdvisorID

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

_(Underscore)

13) To open a new Microsoft Access Query window

click the query design button on the create common tab