CSC301 final study

Which uses the least amount of storage? [a. 'example' stored in a column of type VARCHAR2(20) b. 'exam' stored in a column of type VARCHAR2(20) c. 'ex' stored in a column of type VARCHAR2(20) Correct d. they all use the same amount of storage]

'ex' stored in a column of type VARCHAR2(20)

To return all of the columns from the base table, you can code the ___ operator in the SELECT clause.

*

If you TRUNCATE the number 17.99, you get a value of:

17

To retrieve rows in which an expression matches a string pattern called a mask, you can use the ___ keyword followed by the mask.

LIKE

Which of the following types of statements does not modify the data in a table?

SELECT

You can combine inner and outer joins within a single ___ statement

SELECT

A union combines the rows from two or more

SELECT statements

When you code a SELECT statement, you must code the four main clauses in the following order

SELECT, FROM, WHERE, ORDER BY

When you code a union that combines two result sets, which of the following is not true?

The result sets must be derived from different tables.

Which of the following WHERE clauses will return vendor names from A to C?

WHERE vendor_name < 'D'

A database driver is software that lets the

data access model communicate with the database

To override the order of precedence in an arithmetic expression, you can use

parentheses

Which is not true about the USING keyword?

you code a USING clause in addition to the ON clause

The default date format for MySQL is:

yyyy-mm-dd

If expiration_date contains a value that's equivalent to November 2, 2011 and the SYSDATE function returns a value that's equivalent to December 17, 2011, what will the exp_days column contain when this code is executed? expiration_date - SYSDATE() AS exp_days

-45

All of the following values can be stored in a column that's defined as DECIMAL(6,2), except

32492.05

In most cases, the join condition of an inner join uses the ___ operator to compare two keys.

=

The ___ function is an ANSI-standard function that you can use to perform an explicit conversion.

CAST

Write the code for converting the DATE data type in a column named invoice_date to the DATETIME data type:

CAST(invoice_date AS DATETIME)

The ___ function returns the smallest integer that is greater than or equal to the number.

CEILING

The ___ data type is used for fixed-length strings, which use the same amount of storage for each value regardless of the actual length of the string.

CHAR

To concatenate character strings, you use the ___ function in a string expression.

CONCAT

The ___ function concatenates a string with the specified separator string added in between.

CONCAT_WS

Which of the following types of SQL statements is not a DML statement?

CREATE TABLE

You use the ___ to add a specified number of date parts to a date.

DATE_ADD

Which code returns the date in the format Friday, April 20th, 2015?

DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')

SQL statements that define the tables in a database are referred to as _

DDL

To prevent duplicate rows from being returned by a SELECT statement, you can code the ___ keyword in the SELECT clause.

DISTINCT

Which of the following statements best describes what this SELECT statement does? SELECT invoice_number, invoice_date, CASE WHEN (SYSDATE() - invoice_date) >= 30 AND (SYSDATE() - invoice_date) < 60 THEN invoice_total ELSE 0 END AS "30-60", CASE WHEN (SYSDATE() - invoice_date) >= 60 AND (SYSDATE() - invoice_date) < 90 THEN invoice_total ELSE 0 END AS "60-90", CASE WHEN (SYSDATE() - invoice_date) > 90 THEN invoice_total ELSE 0 END AS "Over 90" FROM invoices

Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date.

The ___ clause of the SELECT statement specifies the table that contains the data.

FROM

The ___ function lets you test an expression and return one value if the expression is true and another value if the expression is false.

IF

To search for null values, use the ____ clause.

IS NULL

Which of the following would return a maximum of 7 rows, starting with the 4th row?

LIMIT 4, 7

The ___ data types can store large, unstructured data such as text, images, sound, and video.

LOB (or large object)

Use the ___ function to find a character in a string.

LOCATE

The ___ function returns the string with any leading spaces removed.

LTRIM

The order of precedence for the logical operators in a WHERE clause is

Not, And, Or

To sort the rows that are retrieved by a SELECT statement in descending sequence by invoice_total, you code this ORDER BY clause:

ORDER BY invoice_total Answer DESC

Write the code for a SELECT statement that uses the ROUND function to return the payment_total column with 1 decimal digit.

ROUND(payment_total, 1)

A _ statement is used to retrieve selected columns and rows from a base table.

SELECT

To format TIME values, you use the ___ function.

TIME_FORMAT

Which of the following is not a good guideline for deciding when to create an index for a column?

The column is frequently updated.

Which of the following recommendations won't improve the readability of your SQL statements?

Use comments to describe what each statement does.

If you want to filter the rows that are returned by a SELECT statement, you must include a/an ___ clause.

WHERE

When you use the implicit syntax for coding joins, the join conditions are coded in the ___ clause

WHERE

The result of a SELECT statement is

a result set

To be in the first normal form, each cell in a table must contain

a single, scalar value

You can simulate a full outer join by using

a union

A table ___ can be used when you want to assign a temporary name to a table.

alias

SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id The "v" in this example is known as a/an ___

alias

Unless you assign a/an ___, the column name in the result set is the same as the column name in the base table.

alias

The processing that's done by the DBMS is typically referred to as

back-end processing

The intersection of a row and a column is commonly called a/an

cell

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column number

Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union

combines the result sets of two or more SELECT statements

To enforce referential integrity for a delete operation, a MySQL database can a. return an error instead of deleting any rows b. set the foreign key values in the related rows to null c. delete the related rows in the foreign key table d. a, b, or c

d. a, b, or c

Which of the following is the way to sort a VARCHAR column that contains numbers in numeric sequence? In the ORDER BY clause a. use the CAST function to convert the column values to numbers b. add zeros to the column values using + 0 c. use the LPAD function to pad the numbers with leading zeros d. all of the above

d. all of the above

The interface between an application program and the DBMS is usually provided by the

data access API

The DATE data type can store

dates only

Which of the following does not violate the referential integrity of a database?

deleting a row in a foreign key table without deleting the related row in the related primary key table

To be in the third normal form,

each non-key column must depend only on the primary key

A _ diagram can be used to show how the tables in a database are defined and related..

entity relationship (ER)

If first_name contains Edward and last_name contains Williams, what will the solution column contain when this code is executed? LOWER(LEFT(first_name,1) + LEFT(last_name,7)) AS solution

ewilliam

SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id This join is coded using the ___ syntax

explicit

In most cases, the join condition of an inner join compares the primary key of one table to the ___ key of another table.

foreign

To relate one table to another, a/an _ in one table is used to point to the primary key in another table.

foreign key

When MySQL automatically converts one data type to another, it's known as a/an ___ conversion.

implicit

SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id This type of join is called a/an ___

inner join

Numbers that don't include a decimal point are known as

integers

Which of the following expressions does not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?

invoice_total - credit_total - payment_total / 10

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an invoice_total value of $1000 or less?

invoice_total IN (0, 1000)

Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax

is an older syntax that works with legacy code

The type of operation that retrieves data from two or more tables is called a _

join

Denormalization typically results in all but one of the following. Which one is it? a. larger tables b. redundant data c. more complicated SQL coding d. reduced performance

more complicated SQL coding

The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type?

mushrooms, sausage, peppers

The three main hardware components of a client/server system are the clients, the server, and the _

network

If an error occurs, each of the numeric functions returns a/an ___ value

null

The COALESCE function provides one way to substitute other values for ___ values.

null

The most common type of relationship between two tables is a/an _ relationship

one-to-many

When coded in a WHERE clause, which search condition will return invoices when payment_date isn't null and invoice_total is greater than or equal to $500?

payment_date IS NOT NULL AND invoice_total >= 500

The ___ of a real number indicates the total number of digits that can be stored in the data type.

precision

A _ uniquely identifies each row in a table.

primary key

To retrieve or update the data in a database, the client sends a/an _

query

Numbers that include a decimal point are knows as

real numbers

To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This

reduces redundancy and makes maintenance easier

In a cross join, all of the rows in the first table are joined with all of the

rows from the second table

A relational database consists of one or more _

tables

If you define a column with a default value, that value is used whenever a row

that doesn't include a value for that column is added to the table

When you use the USING keyword for a join,

the join must be based on a column or columns that have the same name in both tables

SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id The total number of rows returned by this query must equal:

the number of rows in the Invoices table

When you identify the data elements in a new database, you typically subdivide data elements into

the smallest practical components

Which uses the least amount of storage? [a. 'example' stored in a column of type CHAR(20) b. 'exam' stored in a column of type CHAR(20) c. 'ex' stored in a column of type CHAR(20) d. they all use the same amount of storage ]

they all use the same amount of storage

The DATETIME data type includes not only the date, but also a

time

Which of the following isn't a valid column alias?

total sales

When a column in a table is defined, it is given a data _ that determines what kind of data it can store.

type

A full outer join returns

unmatched rows from both the left and right tables

If you assign an alias to one table in a join, you have to

use that alias to refer to the table throughout your query

Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the DATETIME data type?

use the DATE_ADD function to remove the time values from the dates in each column

When coded in a WHERE clause, which of the following would not return rows for vendors in the cities of San Diego and Santa Ana?

vendor_city REGEXP 'NA$'

In a join, column names need to be qualified only

when the same column names exist in both tables