Deck 3: Structured Query Language

Question 1

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

Answer 1

False

Question 2

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

Answer 2

True

Question 3

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

Answer 3

True

Question 4

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

Question 5

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

Question 6

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

Question 7

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

Question 8

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

Question 9

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

A) Char
B) Date/Time
C) Integer
D) Numeric
E) Decimal

Question 10

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

A) ORDER BY clause
B) SORT BY clause
C) order they are listed in following SELECT
D) order they are listed in following WHERE
E) order they are listed in following FROM

Question 11

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

Question 12

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

Question 13

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

Question 14

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

Question 15

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

Question 16

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

Question 17

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

A) SELECT
B) FROM
C) WHERE
D) ORDER BY
E) GROUP BY

Question 18

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

A) UNIQUE
B) SORT
C) ORDER BY
D) DISTINCT
E) REDUCE

Question 19

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

Question 20

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?

A) WHERE Hours = MIN(10) and MAX(50)
B) WHERE Hours IN [10, 50]
C) WHERE Hours = 10 and Hours = 50
D) WHERE Hours BETWEEN 10 AND 50
E) WHERE Hours RANGE 10 TO 50

Question 21

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

A) MODE
B) SUM
C) COUNT
D) MAX
E) AVG

Question 22

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?

A) WHERE CUSTOMER-AcctBalance > 50
B) WHERE (CUSTOMER-AcctBalance > 50)
C) WHERE AcctBalance > 50
D) WHERE (AcctBalance > 50)
E) HAVING AcctBalance > 50

Question 23

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

A) SELECT CUSTOMER-Name AND CUSTOMER-PhoneNum
B) SELECT (CUSTOMER-Name AND CUSTOMER-PhoneNum)
C) SELECT Name, PhoneNum
D) SELECT (Name, PhoneNum)
E) SELECT *

Question 24

To open a new Microsoft Access Query window ________.

A) click the New button on the Home command tab
B) click the New Query button on the Home command tab
C) click the Create Query button on the Create command tab
D) click the New Query in Design view button on the Create command tab
E) click the Query Design button on the Command tab

Question 25

Distinguish between the HAVING clause and the WHERE clause.

Question 26

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

A) WHERE
B) HAVING
C) LIKE
D) SORT
E) DISTINCT

Question 27

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

A) % (percent sign)
B) ! (exclamation mark)
C) _ (underscore)
D) ? (question mark)
E) ; (semi-colon)

Question 28

Microsoft Access SQL commands are run in ________.

A) the SQL window
B) the QBE window
C) the Query View of an SQL window
D) the Design View of a Query window
E) the SQL View of a Query window

Question 29

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

Question 30

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

A) SELECT
B) SEARCH
C) FIND
D) SUBSTRING
E) LIKE

Question 31

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

Question 32

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.

Question 33

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?

A) INSERT DATA STUDENT SET StudentID=123, Name='Jones', Advisor='Smith';
B) INSERT INTO STUDENT VALUES (123, 'Jones', 'Smith');
C) INSERT INTO STUDENT (New Student Data) VALUES (123, 'Jones', 'Smith');
D) INPUT DATA STUDENT SET StudentID=123, Name=' Jones', Advisor='Smith';
E) INPUT INTO STUDENT (123, 'Jones', 'Smith');

Question 34

Given the table
CUSTOMER(CustID,Name,PhoneNumber,AccountBalance)
write the standard SQL query to retrieve the Name and Phone Number of customers with a balance greater than 50.

Question 35

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?

A) SORT BY Department
B) REVERSE Department
C) ORDER BY Department DESC
D) SORT BY Department DESC
E) SORT BY Department REVERSE

Question 36

What are SQL Built-in Functions?

Question 37

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

Question 38

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

A) selecting the tables from the Use Table dialog box
B) selecting the tables from the Show Table dialog box
C) selecting the tables from the Tables section of the Navigation Pane
D) selecting the tables from the Queries section of the Navigation Pane
E) selecting the tables from the Relationships window

Question 39

Given the table
CUSTOMER(CustID,Name,PhoneNumber,AccountBalance)
write the standard SQL query to retrieve the Name and Phone Number of customers whose name begins with 'S'.

Question 40

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

A) % (percent sign)
B) ! (exclamation mark)
C) _ (underscore)
D) ? (question mark)
E) ; (semi-colon)

Question 41

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.

Question 42

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

Question 43

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

Question 44

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?

A) UPDATE STUDENT SET Advisor = 'Smith';
B) MODIFY STUDENT SET Advisor = 'Smith';
C) MODIFY Advisor SET STUDENT = 'Smith';
D) UPDATE Advisor = 'Smith';
E) SET STUDENT Advisor = 'Smith';

Question 45

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

A) MODIFY TABLE
B) CHANGE TABLE
C) CREATE TABLE
D) SET TABLE
E) BUILD TABLE

Question 46

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

Question 47

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

Question 48

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

A) Create a single attribute primary key.
B) Define a foreign key.
C) Establish a referential integrity constraint.
D) Define an attribute to be NOT NULL.
E) Defining a name for the constraint.

Question 49

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

A) integer(2,2)
B) integer(5,2)
C) numeric(2,2)
D) numeric(4,2)
E) numeric(5,2)

Question 50

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

Question 51

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

Question 52

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

A) Text
B) Char
C) Varchar
D) Integer
E) Numeric

Question 53

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

Question 54

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

Question 55

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

A) text(10)
B) char(10)
C) varchar(10)
D) fixed(10)
E) length(10)

Question 56

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

Question 57

Which of the following is not true about primary keys?

A) Primary keys cannot be null.
B) Primary keys must be unique.
C) Primary keys must be a single attribute.
D) Primary keys are used to represent relationships.
E) Primary keys can be defined using an SQL CONSTRAINT phrase.

Question 58

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.

Question 59

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

Question 60

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

Question 61

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

Question 62

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

Question 63

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

Question 64

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

Question 65

Explain two methods for defining a primary key using SQL.

Question 66

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

Question 67

SQL stands for ________.

Question 68

Explain how to run an SQL query in Microsoft Access.

Question 69

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

Question 70

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

Question 71

Explain why it is important to learn SQL.

Question 72

Discuss SQL data types.

Question 73

SQL was developed by ________ in the late 1970s.

Question 74

Distinguish between Char and VarChar data types.

Question 75

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

Question 76

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

Question 77

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

Question 78

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

A) setting the Data Types in Access table Design View
B) setting the field properties in Access table Design View
C) setting the relationship properties in the Edit Relationship dialog box
D) setting the referential integrity properties in the Edit Relationship dialog box
E) running an ALTER TABLE query in Access SQL

Question 79

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

A) Integer
B) Char(25)
C) VarChar(35)
D) Numeric(5,3)
E) DateTime

Question 80

Explain the essential format of the CREATE TABLE statement.