Database Storage & Design Chapter 2 2 Left Join Right Join Include Both And Are Correct Answer Aacsb Information

81) Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of

the following would find all employees whose name begins with the letter “S” using Microsoft

Access?

A) SELECT *

FROM EMPLOYEE

WHERE Name IN [‘S’];

B) SELECT EmpNo

FROM EMPLOYEE

WHERE Name LIKE ‘S’;

C) SELECT *

FROM EMPLOYEE

WHERE Name LIKE ‘S*’;

D) SELECT *

FROM EMPLOYEE

WHERE Name LIKE ‘S%’;

82) In an SQL query, which built-in function is used to total numeric columns?

A) AVG

B) COUNT

C) MAX

D) SUM

83) In an SQL query, which built-in function is used to compute the average value of numeric

columns?

A) AVG

B) MEAN

C) AVERAGE

D) SUM

18

84) In an SQL query, which built-in function is used to obtain the largest value of numeric

columns?

A) AVG

B) COUNT

C) MAX

D) MIN

85) In an SQL query, which built-in function is used to obtain the smallest value of numeric

columns?

A) AVG

B) COUNT

C) MAX

D) MIN

86) In an SQL query, the built-in functions SUM and AVG work with columns containing data

of which of the following data types?

A) Integer

B) Numeric

C) Char

D) Both A and B are correct

87) In an SQL query, which built-in function is used to compute the number of rows in a table?

A) AVG

B) COUNT

C) MAX

D) SUM

88) Assuming the “Quantity” column of an ORDER table contains integer data, what does

COUNT(Quantity) compute?

A) The number of rows in the ORDER table

B) The number of non-zero values in the Quantity column

C) The number of distinct values in the Quantity column

D) The number of non-null values in the Quantity column

89) In an SQL query, which SQL keyword is used with built-in functions to group together rows

that have the same value in a specified column or columns?

A) GROUP BY

B) ORDER BY

C) SELECT

D) SORT BY

90) In an SQL query, which SQL keyword is used with GROUP BY to select groups meeting

specified criteria?

A) AND

B) WHERE

C) HAVING

D) IN

91) Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of

the following is not a valid ANSI SQL command?

A) SELECT *

FROM EMPLOYEE

WHERE Name LIKE ‘Ja%’;

B) SELECT COUNT(*)

FROM EMPLOYEE

WHERE Salary < 30000;

C) SELECT HireDate, COUNT(*)

FROM EMPLOYEE

GROUP BY HireDate;

D) SELECT HireDate, COUNT(*)

FROM EMPLOYEE

WHERE Salary < 30000;

22

92) Based on the tables below, which of the following SQL commands would return the average

customer balance grouped by SalesRepNo?

GENERAL SALES DATABASE:

SALESREP

SalesRepNo

RepName

HireDate

654

Jones

01/02/2005

734

Smith

02/03/2007

345

Chen

01/25/2018

434

Johnson

11/23/2004

CUSTOMER

CustNo

CustName

Balance

SalesRepNo

9870

Winston

500

345

8590

Gonzales

350

434

7840

Harris

800

654

4870

Miles

100

345

A) SELECT AVG (Balance)

FROM CUSTOMER

WHERE SalesRepNo;

B) SELECT AVG (Balance)

FROM CUSTOMER

GROUP BY SalesRepNo;

C) SELECT AVG (Balance)

FROM CUSTOMER, SALESREP

WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo;

D) SELECT AVG (Balance)

FROM CUSTOMER

ORDER BY SalesRepNo;

93) Based on the tables below, which of the following commands in ANSI SQL would return

only the name of the sales representative and the name of the customer for each customer that

has a balance greater than 400?

GENERAL SALES DATABASE:

SALESREP

SalesRepNo

RepName

HireDate

23

654

Jones

01/02/2005

734

Smith

02/03/2007

345

Chen

01/25/2018

434

Johnson

11/23/2004

CUSTOMER

CustNo

CustName

Balance

SalesRepNo

9870

Winston

500

345

8590

Gonzales

350

434

7840

Harris

800

654

4870

Miles

100

345

A) SELECT *

FROM SALESREP, CUSTOMER

WHERE Balance > 400;

B) SELECT DISTINCT RepName, CustName

FROM SALESREP, CUSTOMER

WHERE Balance > 400;

C) SELECT *

FROM SALESREP, CUSTOMER

WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo

AND Balance > 400;

D) SELECT RepName, CustName

FROM SALESREP, CUSTOMER

WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo

AND Balance > 400;

94) In an SQL query, which SQL keyword is used to implement a subquery?

A) GROUP BY

B) HAVING

C) ORDER BY

D) SELECT

95) When one SQL query is embedded in another SQL query, this is referred to as a ________.

A) subquery

B) join

C) WHERE Query

D) subset query

96) When one SQL query is embedded in another SQL query to simulate a join, the second SQL

query is embedded in the ________ of the first query.

A) SELECT

B) FROM

C) WHERE

D) HAVING

97) When one SQL query is embedded in another SQL query, the top-level SQL query can still

contain an SQL ________ clause.

A) FROM or SORT BY

B) UNIQUE IN

C) GROUP BY

D) WHERE IN

98) In an SQL query, which SQL keyword is used to specify the names of tables to be joined?

A) FROM

B) HAVING

C) JOIN

D) WHERE

99) In an SQL query, which SQL keyword is used to specify the column names to be used in a

join?

A) FROM

B) JOIN

C) SELECT

D) WHERE

100) Regarding the interchangeability of subqueries and joins, ________.

A) a join can always be used as an alternative to a subquery, and a subquery can always be used

as an alternative to a join

B) a join can sometimes be used as an alternative to a subquery, and a subquery can sometimes

be used as an alternative to a join

C) a join can always be used as an alternative to a subquery, and a subquery can sometimes be

used as an alternative to a join

D) a join can sometimes be used as an alternative to a subquery, and a subquery can always be

used as an alternative to a join

101) In an SQL query of two tables, which SQL keyword indicates that we want data from all

the rows of one table to be included in the result, even if the row does not correspond to any data

in the other table?

A) LEFT JOIN

B) RIGHT JOIN

C) INCLUDE

D) Both A and B are correct

102) Suppose tables EMPLOYEE and CUSTOMER both store address information, and you

want to send a letter to all employees and customers of your company to make a major

announcement. Which SQL keyword would you most likely use here?

A) INTERSECT

B) UNION

C) UNION ALL

D) JOIN

103) Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you

want to find all employees who are also customers, which SQL keyword would you most likely

use?

A) INTERSECT

B) UNION

C) EXCEPT

D) UNION ALL

104) Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you

want to find all customers who are not also employees, which SQL keyword would you most

likely use?

A) INTERSECT

B) UNION

C) EXCEPT

D) UNION ALL

105) What is SQL?

106) Explain why it is important to learn SQL.

107) Briefly describe subqueries and joins. Explain when each is not an acceptable alternative

for the other.

108) The following database will be used in this question:

GENERAL SALES DATABASE:

SALESREP

SalesRepNo

RepName

HireDate

654

Jones

01/02/2005

734

Smith

02/03/2007

345

Chen

01/25/2018

434

Johnson

11/23/2004

CUSTOMER

CustNo

CustName

Balance

SalesRepNo

9870

Winston

500

345

8590

Gonzales

350

434

7840

Harris

800

654

4870

Miles

100

345

Explain the use of the of SQL keyword SELECT. Include an example based on the CUSTOMER

table in the General Sales database.

109) The following database will be used in this question:

GENERAL SALES DATABASE:

SALESREP

SalesRepNo

RepName

HireDate

654

Jones

01/02/2005

734

Smith

02/03/2007

345

Chen

01/25/2018

434

Johnson

11/23/2004

CUSTOMER

CustNo

CustName

Balance

SalesRepNo

9870

Winston

500

345

8590

Gonzales

350

434

7840

Harris

800

654

4870

Miles

100

345

Explain the use of the SQL keyword LIKE. Include an example based on the CUSTOMER table

from the General Sales database; use at least one wildcard.

110) The following database will be used in this question:

GENERAL SALES DATABASE:

SALESREP

SalesRepNo

RepName

HireDate

654

Jones

01/02/2005

734

Smith

02/03/2007

345

Chen

01/25/2018

434

Johnson

11/23/2004

CUSTOMER

CustNo

CustName

Balance

SalesRepNo

9870

Winston

500

345

8590

Gonzales

350

434

7840

Harris

800

654

4870

Miles

100

345

Explain the use of the GROUP BY keyword. Include an example based on the CUSTOMER

table from the General Sales database.

111) Given the table CUSTOMER(CustID, Name, PhoneNumber, AccountBalance), write

the standard SQL query to retrieve the Name and PhoneNumber of customers with a balance

greater than 50.

112) Given the table CUSTOMER(CustID, Name, PhoneNumber, AccountBalance), write

the standard SQL query to retrieve the Name and PhoneNumber of customers whose name

begins with ‘S’.

113) What are SQL Built-in Functions?

114) Distinguish between the HAVING and WHERE clause.