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
