SQL Final

Which uses the least 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

*

You can use the __________________ keyword in a WHERE clause to test whether a condition is true for one or more of the values returned by the subquery

ANY

12. 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

3. Which of the following is the way to sort a VARCHAR column that contains numbers in numeric sequence

In the ORDER BY clause : all of the above

10. The default storage engine for MySQL 5.5 and later. This engine supports foreign keys and transactions

InnoDB

2. 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

3. Which of the following is true about creating indexes

MySQL automatically creates indexes for primary keys, MySQL automatically creates indexes for foreign keys, You can create an index that's based on more than one column.

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

Null

7. The latin1 character set uses how many bytes per character

One

3. Which column or columns in each table should be defined as the primary key

Orders: OrderID; OrderLineItems: OrderID, OrderSequence; Products: ProductID\

4. Which column or columns in each table are foreign keys

Orders: none; OrderLineItems: OrderID and ProductID; Products: none

If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN (subquery)

a column of one or more rows

If introduced as follows, the subquery can return which of the values listed below

a single value

If introduced as follows, the subquery can return which of the values listed below?WHERE 2 < (subquery)

a single value

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

a single, scalar value

If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)

a subquery can't be introduced in this way

If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)

a table

When you code a subquery in a FROM clause, you must assign a/an ___________________ to it

alias

Expressions coded in the HAVING clause

can use either aggregate search conditions or non-aggregate search conditions

Expressions coded in the WHERE clause

can use non-aggregate search conditions but can't use aggregate search conditions

8. In the collation, utf8_spanish_ci, the ci stands for

case-insensitive

All of the aggregate functions ignore null values, except for the _____________________ function

count(*)

The DATE data type can store

dates only

9. To be in the third normal form

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

7. If you TRUNCATE the number 17.99, you get a value of

17

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

32492.05

3. To modify the structure of an existing table, you use the _______ ________________ statement

ALTER TABLE

5. To generate unique numbers in sequence, you use the ____ ____________ attribute

BLANK

Which of the following statements is true when you use MySQL Workbench to run INSERT, UPDATE, and DELETE statements

By default, the changes are automatically committed to the database.

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

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

CONCAT_WS

Which SQL statement creates a complete copy of the users table call users_copy

CREATE TABLE users_copy AS SELECT * FROM users;

2. Which of the following diagrams best represents the relationship between a table of customers and a table of orders placed by customers

CUSTOMERS: customerId, customerName o-E ORDERS: orderId, customerId, orderDate

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

Ceiling

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

DATE_ADD

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

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

By default, MySQL automatically commits changes to the database immediately after each INSERT, UPDATE, and ______________ statement that you issue

DELETE

Write the code for a DELETE statement that deletes every row in the Invoices_Copy table

DELETE FROM Invoices_Copy

By default, all duplicate values are included in the aggregate calculation, unless you specify the __________________ keyword

DISTINCT

14. 4. A/An ___ model is a representation of the entities, or objects, of the database including the tables, views, and stored programs

EER

11. 1. If two tables have a one-to-many relationship, you need to add a _________ ______________ column to the table on the "many" side

Foreign Key

9. Which feature does the MyISAM storage engine not support

Foreign keys

A subquery can be coded in a WHERE, FROM, SELECT or ______________________ clause

HAVING

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

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

SELECT

You can use the AS keyword with the CREATE TABLE statement to create a copy of a table from a ____________________ statement

SELECT

The six clauses of the SELECT statement must be coded in the following order

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

6. Which is true when you define a column as the primary key

The column is forced to be NOT NULL, the column is forced to contain a unique value for each row, and an index is automatically created based on the column.

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

The column is frequently updated

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

The smallest practical components

4. To delete all data from a table without deleting the definition for the table, you use the ______________ statement

Truncate table

2. If you want to create an index that doesn't allow duplicate values, you use the _______________ _____________ keyword in the CREATE INDEX statement

Unique

When you code an UPDATE statement for one or more rows, the SET clause names the columns to be updated and the values to be assigned to those columns, and the ______________________ clause specifies the conditions a row must meet to be updated

WHERE

The ENUM data type stores values that are mutually exclusive

Which choice is not appropriate for the ENUM type. mushrooms, sausage, peppers

1. If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, the default setting for null values is

__________null________________.

If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery)

a column of one or more rows

4. 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

4. When you use a script to create all of the tables for a database, you must start with the tables that don't have ___________

foreign keys

1. You can't code which one of the following as part of a CREATE TABLE statement

function based indexes

10. You should create a/ an __ when a column is used frequently in search conditions or joins

index

When you code a subquery in a FROM clause, it returns a result set that can be referred to as an ____________________ view

inline

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

large object

14. Use the _____ function to find a character in a string

locate

13. The _______ ______________function returns the string with any leading spaces removed

ltrim

15. 5. To normalize a data structure, you apply the __in sequence

normal forms

When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values

null

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

precision

12. 2. Each table in a database should have a _____that uniquely identifies each row

primary key

Numbers that include a decimal point are knows as ____________________

real numbers

8. 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

5. To enforce referential integrity for a delete operation, a MySQL database can

return an error instead of deleting any rows, set the foreign key values in the related rows to null, delete the related rows in the foreign key table

To insert rows selected from one or more tables into another table with an INSERT statement, you code a/an ___________________________ in place of the VALUES clause

subquery select

A SELECT statement that includes aggregate functions is often called a/an ____________________ query

summary

5. When you define a column with the DEFAULT attribute

the default value is used if another value isn't specified when a row is added to the database.

Which uses the least amount of storage

they all use the same amount of storage

13. 3. Typically, most database designers consider a database structure normalized if it's in the _________ normal form

third

5. 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

A subquery can return a list of values, a table of values or a single ____________________

value

When coding a query, you can add one or more summary rows to a result set that uses grouping and aggregates by coding the ___________________

with rollup

2. When you create a script for creating a database

you need to create the referred to tables before you create the tables that refer to them

The default date format for MySQL is _________________

yyyy-mm-dd