Chapter Five

Question 1

What are the benefits of a standardized relational language?

Answer 1

• Reduced training costs
• Productivity
• Application portability
• Application longevity
• Reduced dependence on a single vendor
• Cross-system communication

None

Question 2

What is the processing order of Boolean operators by default?

Answer 2

NOT, then AND, then OR

None

Question 3

What does the SQL SELECT statement do?

Answer 3

Used for queries on single or multiple tables

None

Question 4

What are the main clauses of the SELECT statement?

Answer 4

• SELECT: List the columns to be returned
• FROM: Indicate the table(s) or view(s)
• WHERE: Indicate conditions for rows
• GROUP BY: Indicate categorization of results
• HAVING: Conditions for categories
• ORDER BY: Sort results according to criteria

None

Question 5

What is an alias in SQL?

Answer 5

An alternative column or table name

None

Question 6

Fill in the blank: Comparison operators include =, >, >=, <, <=, <> and _______.

Answer 6

!=

None

Question 7

What is the purpose of the GROUP BY clause?

Answer 7

To categorize results for use with aggregate functions

None

Question 8

What is the purpose of the HAVING clause?

Answer 8

Qualifying results by categories, operates on groups, not individual rows

None

Question 9

What does the MERGE statement do in SQL?

Answer 9

Easier to update a table, combines Insert and Update in one statement

None

Question 10

What is the function of the DELETE statement?

Answer 10

Removes rows from a table

None

Question 11

What are the major CREATE statements in Data Definition Language (DDL)?

Answer 11

• CREATE SCHEMA
• CREATE TABLE
• CREATE VIEW

None

Question 12

What types of data can be defined in SQL?

Answer 12

• Strings: CHARACTER, VARYING CHARACTER
• Binary: BLOB
• Number: Numeric, Decimal, Integer
• Temporal: Timestamp
• Boolean: True or False

None

Question 13

What is the role of Data Definition Language (DDL)?

Answer 13

Commands that define a database, including creating, altering, and dropping tables

None

Question 14

What does the UPDATE statement do?

Answer 14

Modifies data in existing rows

None

Question 15

What does the ALTER TABLE statement allow you to do?

Answer 15

Change column specifications in a table

None

Question 16

What is the purpose of the DROP TABLE statement?

Answer 16

Removes tables from your schema

None

Question 17

What does the INSERT statement do?

Answer 17

Adds one or more rows to a table

None

Question 18

What is referential integrity in SQL?

Answer 18

Constraint ensuring foreign key values must match primary key values in related tables

None

Question 19

What are the steps in table creation?

Answer 19

• Identify data types for attributes
• Identify columns that can/cannot be null
• Identify unique columns
• Identify primary key–foreign key relationships
• Determine default values
• Identify constraints on columns
• Create the table and associated indexes

None

Question 20

What does the SQL:1999 and SQL:2016 standards refer to?

Answer 20

Updates to the SQL standard over the years

None

Question 21

Is SQL a standard certified by NIST?

Answer 21

No, it is no longer certified by NIST

None

Question 22

Expressions are mathematical manipulations of data in a table that may be included as part of the SELECT statement.

Answer 22

True

Question 23

What will result from the following SQL Select statement?

SELECT MIN(Product_Description)
FROM Product_V;

Answer 23

SELECT @ will be displayed.

*The first product description alphabetically in Product_V will be shown. *

An error message will be generated.

The minimum value of Product_Description will be displayed.

Question 24

When creating a table, it is not important to consider foreign key–primary key mates.

Answer 24

False

Question 25

One of the original purposes of the SQL standard was to provide a vehicle for portability of database definition and application modules between conforming DBMSs.

Answer 25

True

Question 26

A view may not be updated directly if it contains:

Answer 26

the WHERE clause. * the HAVING clause. * the FROM clause. the SELECT clause

Question 27

What will be returned when the following SQL query is executed? SELECT driver_no, COUNT(*) as num_deliveries FROM deliveries GROUP BY driver_no HAVING COUNT(*) > 2;

Answer 27

A listing of the number of deliveries greater than 2 * A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries* A listing of all drivers A listing of all drivers who made more than 2 deliveries

Question 28

What result set is returned from the following query? SELECT Customer_Name, telephone FROM customers WHERE city in ('Boston','New York','Denver');

Answer 28

The Customer_Name and telephone of all customers The Customer_Name of all customers living in Boston, New York or Denver * The Customer_Name and telephone of all customers living in either Boston, New York or Denver * The Customer_Name and telephone of all customers living in Boston and New York and Denver

Question 29

Given a table named store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? INSERT INTO store values ('234 Park Street')

Answer 29

*You must specify the fields to insert if you are only inserting some of the fields. * It would work just fine. There is no table keyword. INSERT INTO should be INSERT to.

Question 30

Indexes are created in most RDBMSs to:

Answer 30

increase the cost of implementation. decrease the amount of disk space utilized. provide a quicker way to store data. * provide rapid random and sequential access to base-table data. *

Question 31

The following INSERT command would work fine: INSERT INTO budget values 121,222,111;

Answer 31

False

Question 32

SQL originated from a project called System-S.

Answer 32

False

Question 33

INSERT INTO is an example of ________ code.

Answer 33

*DML * TIO DDL DCL

Question 34

The ________ is the structure that contains descriptions of objects such as tables and views created by users.

Answer 34

catalog master view SQL * schema *

Question 35

What result set will the following query return? SELECT Item_No, description FROM item WHERE weight > 100 and weight < 200;

Answer 35

*The Item_No and description for all items weighing between 101 and 199 * The Item_No and description for all items weighing less than 100 The Item_No for all items weighing between 101 and 199 The Item_No for all items weighing more than 200

Question 36

A database is maintained and queried using the data mapping language (DML).

Answer 36

False

Question 37

The following query totals sales in state= 'MA' for each salesperson. SELECT salesperson_id, sum(sales) FROM salesperson GROUP BY salesperson_id HAVING state = 'MA';

Answer 37

False

Question 38

The CREATE SCHEMA DDL command is used to create a table.

Answer 38

False

Question 39

In an SQL statement, which of the following parts states the conditions for row selection?

Answer 39

*WHERE * FROM GROUP BY SELECT

Question 40

Which of the following is true of the order in which SQL statements are evaluated?

Answer 40

*The SELECT clause is processed before the ORDER BY clause. * The SELECT clause is always processed last. The GROUP BY clause is processed before the WHERE clause. The SELECT clause is always processed first.