Objective Assessment Prep

Which action deletes all rows from a table?

Omitting the WHERE clause from a DELETE statement

Refer to the given MySQL statement. INSERT INTO Invoice VALUES (10217, '2018-08-01', 55527), (10218, '2018-08-01', 44436); Describe what the parentheses denote.

The column values for two individual rows

SUBSTRING(s, pos, len)

SQL string function that returns the substring from s that starts at position pos and has length len

UPPER(s)

SQL string function that returns the uppercase s

How does a row subquery differ from a table subquery?

A row subquery returns a single row of one or more values.

Which command creates a database only if it does not already exist?

CREATE DATABASE IF NOT EXISTS db_name ;

ORDER BY

Each group in a GROUP BY clause a may be ordered with the ___________ ___________ clause.

attribute

Each tuple position is called a/n...

condition

an expression that evaluates to TRUE, FALSE, or NULL.

cross-join

combines two tables without comparing columns.

Refer to the given SQL statement. CREATE TABLE member ( member_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (member_id), last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, suffix VARCHAR(5) NULL, expiration DATE NULL, email VARCHAR(100) NULL, street VARCHAR(50) NULL, city VARCHAR(50) NULL, state VARCHAR(2) NULL, zip VARCHAR(10) NULL, phone VARCHAR(20) NULL, interests VARCHAR(255) NULL ); Which two columns are created as something other than variable-length strings in this statement? Choose 2 answers

member_id, expiration

aggregate function

operates on numeric values from multiple rows, including only rows selected by the WHERE clause.

LIKE

operator that, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

IN

operator used in a WHERE clause to determine if a value matches one of several values.

Refer to the given SQL statement. INSERT INTO student VALUES ('John','S',NULL), ('Mary','S',NULL); What do the parentheses denote?

The column values for two individual rows

What does the clause PRIMARY KEY followed by a field name in parentheses mean in a CREATE TABLE statement?

The column, in parentheses, is the primary key for the table.

data types

The columns in a JOIN must have comparable ____________ ____________.

an asterisk (*)

The columns names in a SELECT statement can be replaced with ____________ to select all columns.

Which condition must be in effect to use the INSERT INTO ... VALUES syntax for an INSERT statement?

The references list must contain a value for each attribute/column in the table.

A database administrator tries to delete a row in a parent table, but the delete fails because a row in another table depends on that row. Which referential-integrity rule is in effect?

The restrict delete rule

A database administrator tried to delete a row in a parent table, but the delete fails because a row in another table depends on that row. Which referential-integrity rule is in effect?

The restrict rule

What does WHERE identify in a basic SQL SELECT statement?

The rows to be included

CASCADE

propagates primary key changes to foreign keys.

Which syntax is the correct way to use the DROP INDEX command to drop a primary key from a table?

DROP INDEX `PRIMARY` ON tbl_name ;

Which command eliminates a table?

DROP TABLE

POW(x, y)

SQL numeric function that returns x to the power of y

A database manager starts to convert data that has been normalized into a form that conforms to the relational model. A simple primary key has been established and all the repeating groups have been deleted. In which form is this data?

Second normal form

binary relationship

a relationship between two entity types.

Refer to the given SQL syntax. ALTER TABLE tbl_name action [, action ] ... ; What does this syntax allow? Choose 2 answers

Dropping indexes, Changing column data types

COUNT()

function that counts the number of rows retrieved by a SELECT statement.

AVG()

function that finds the arithmetic mean (average) of all the values in a group.

MAX()

function that finds the maximum value in a group.

MIN()

function that finds the minimum value in a group

SUM()

function that sums all the values in a group.

SET NULL

sets invalid foreign keys to NULL.

SET DEFAULT

sets invalid foreign keys to a default primary key value, specified in SQL.

TRUNCATE

statement that deletes all rows from a table and resets the table's auto-increment values back to 1

Refer to the given SQL statement. 1. SELECT SPNAME 2. FROM SALESPERSON 3. WHERE SPNUM= 4. FROM CUSTOMER 5. WHERE CUSTNUM=20900); What is missing from the subquery at the beginning of line 4?

(SELECT SPNUM

Refer to the given SQL Statement. SELECT invoice_id FROM Invoice WHERE customer_id = FROM customer WHERE customer_id = 55547 ); What is missing from the subquery at the beginning of Line 4?

(SELECT customer_id

Refer to the given SQL statement. SELECT PRODNUM, SUM(QUANTITY) FROM SALESPERSON Which line, when added to the end of the statement, returns the total number of each kind of product by product number?

COUNT PRODNUM;

What is the difference between COUNT(*) and COUNT( col_name) for specified column names?

COUNT(*) counts every row selected, and COUNT(col_name) counts only non-null values.

CONCAT(s1, s2, ...)

SQL string function that returns the string that results from concatenating the string arguments

Which two MySQL data types can represent images or sounds?

TINYBLOB BINARY

GROUP BY

A __________ ___________ clause must appear before the ORDER BY clause and after the WHERE clause (if present).

query parser

A ____________ _____________ checks each query for syntax errors and converts valid queries to an internal representation.

query optimizer

A ____________ _____________ reads the internal representation, generates alternative execution plans, estimates execution times, and selects the fastest plan.

NULL

A comparison operator evaluates to ____________ if one or both operands are NULL.

column-level constraint

A constraint that is applied to a single column is called

table-level constraint

A constraint that is applied to multiple columns is called

FOREIGN KEY REFERENCES

A foreign key constraint is added to a CREATE TABLE statement with the ____________ _________ and _______________ keywords.

first normal form

A table is in _________ ___________ ___________ when all non-key columns depend on the primary key. If a table has no duplicate rows, the composite of all columns is unique, so either all columns or some subset comprise a primary key. A table with no duplicate rows is in this form.

second normal form

A table is in ___________ _____________ _____________ when all non-key columns depend on the whole primary key. In other words, a non-key column cannot depend on part of a composite primary key. A table with a simple primary key is automatically in this form.

Which data definition language statement affects databases or objects in them?

ALTER

A database administrator needs to modify an index on the CUSTOMER table called IDX_NAME because there are multiple customers with the same name. It has been determined that it would be desirable to modify the index to also include the telephone number. Which command line will modify the IDX_NAME index on the CUSTOMER table to include both NAME and TELEPHONE as part of the index?

ALTER INDEX IDX_NAME ON CUSTOMER(NAME, TELEPHONE);

The database administrator has been tasked with implementing an index to speed up the retrieval of data based on the city values. The database is using the InnoDB engine. What is the correct syntax to create an index to be built on the city field based on the database information?

ALTER TABLE 'Salesperson' ADD INDEX 'city_index' (`city`)

Which ALTER TABLE statement adds a foreign key constraint to a child table?

ALTER TABLE child ADD FOREIGN KEY (par_id) REFERENCES parent (par_id) ON DELETE CASCADE;

What is the proper command to change a view?

ALTER VIEW

In which two ways can data redundancy affect managing disk storage space and calculation speed?

Additional processing time is needed to update redundant data, and redundant data takes up additional disk space.

SUM, which returns the total of selected values. AVG, which returns the average of selected values. MAX, which returns the largest selected value. MIN, which returns the smallest selected value.

Aggregate functions include:

How does Table 2 affect the returned results from Table 1 in a left join?

All Table 1 results are returned, regardless of whether a match is found in Table 2.

primary keys

All table columns, except ____________ _____________, may contain NULL values by default.

% (Modulo)

Arithmetic operator that returns the integer remainder after dividing one operand by another

Which two SQL data types can represent images or sounds? Choose 2 answers

BINARY, TINYBLOB

Which method creates an empty copy of a table and then populates it from the original table?

CREATE TABLE ... LIKE followed by INSERT INTO ... SELECT :

Refer to the given SQL statement. CREATE TABLE Invoice ( invoice_id INT NOT NULL , date DATE NOT NULL, customer_id INT NOT NULL, PRIMARY KEY (invoice_id), FOREIGN KEY (customer_id) REFERENCES Customer (customer_id) ); In which line of the statement is the table given its name?

CREATE TABLE Invoice (

Refer to the given SQL statement. SELECT product_name, product_number, mfg_city FROM product; Which statement, when added before this statement, generates a view?

CREATE VIEW viewprod AS

Refer to the given SQL statement. CREATE TABLE CUSTOMER ( CustomerID INT NOT NULL AUTO_INCREMENT, LastName VARCHAR(100) NOT NULL, FirstName VARCHAR(100) NOT NULL, PRIMARY KEY ( CustomerID ) ); Which component of the command indicates the table's name?

CUSTOMER

When a patient is deleted from the patient table, all corresponding rows from the patient in the exam table are automatically deleted as well. What is this referential integrity technique called?

Cascaded delete

When a product is deleted from the product table, all corresponding rows for the product in the pricing table are deleted automatically as well. What is this referential integrity technique called?

Cascaded delete

!=

Comparison operator that compares two values for inequality

Column values, when grouped together, must be unique. Ex: The combination (2538, 1) is unique within (ID, Number). Columns may not contain NULL. Composite primary keys must be minimal.

Composite primary keys obey three rules:

EXISTS

Correlated subqueries commonly use the __________ operator, which returns TRUE if a subquery selects at least one row and FALSE if no rows are selected.

Which statement deletes all rows from the invoice table?

DELETE FROM Invoice;

DATEDIFF(expr1, expr2) TIMEDIFF(expr1, expr2)

DQL date/time function that returns expr1 - expr2 in number of days or time values, given expr1 and expr2 are date, time, or datetime values

Refer to the given SQL statement. CREATE TABLE Product ( product_id int NOT NULL, product_name varchar(30) NOT NULL, product_description varchar(255) NOT NULL, product_price float(6,2) NOT NULL, PRIMARY KEY (product_id) ); Which kind of data type is FLOAT in the statement?

Decimal

Refer to the given SQL statement. CREATE TABLE mytbl ( f FLOAT(10,4), c CHAR(15) NOT NULL DEFAULT 'none', i TINYINT UNSIGNED NULL ); Which kind of data type is FLOAT in this statement?

Decimal

What happens in an UPDATE statement if the WHERE clause is omitted?

Every row in the table is updated.

Which statement should be used so that it assigns a foreign key to the customer name?

FOREIGN KEY ( customer_name ) REFERENCES table_name ( customer_name )

Which statement should be used to establish a foreign key on customer_id?

FOREIGN KEY (customer_id) REFERENCES customer (customer_id)

cache manager

For optimal performance, the query processor layer has a ___________ ____________ that stores reusable information in main memory.

Two attributes in two related tables have the exact same domain of values. The attribute is a primary key in one table. Which kind of key is the attribute in the other table?

Foreign

Foreign key values may be repeated. Ex: Sales and Marketing have the same manager. Foreign key values may be NULL. Ex: Technical support currently has no manager. Non-NULL foreign key values must match some primary key value.

Foreign keys do not obey the same rules as primary keys:

With which command can a database administrator allow a user named Mary to query the Patient table by executing SELECT commands on it?

GRANT SELECT ON PATIENT TO 'mary'@ 'localhost';

Refer to the given SQL statement: SELECT item_id, SUM(quantity) FROM invoice Which line, when added to the end of the statement, returns the total number of each kind of item by item_id?

GROUP BY item_id;

Refer to the SQL statement below: CREATE TABLE Invoice ( invoice_id INT NOT NULL AUTO_INCREMENT , date DATE NOT NULL, customer_id INT NOT NULL, PRIMARY KEY (invoice_id), FOREIGN KEY (customer_id) REFERENCES Customer (customer_id), Which clause when added to the end of the statement creates an index on the customer_id field?

INDEX custid_index (customer_id));

Which clause in a CREATE TABLE statement creates an index on a field?

INDEX index_name (index_columns)

CHECK

If the ___________ expression does not evaluate to TRUE or UNKNOWN (for NULL values), the constraint is violated.

third normal form

Informally, a table is in ______________ ________________ ____________ when all non-key columns depend on the key, the whole key, and nothing but the key. A table is in this form if, whenever a non-key column A depends on column B, then B is unique. Columns A and B may be simple or composite. Although B is unique, B is not necessarily minimal and therefore is not necessarily a candidate key.

What does the DELETE statement do?

It removes rows from a table.

Refer to the given SQL statement. SELECT table1.*, table2.* FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

It selects all of the rows in Table 1 (regardless of if there is a match in Table 2) and the matching rows in Table 2.

Refer to the given SQL statement. SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 What does the LEFT JOIN statement do?

It selects all of the rows in Table 1 and the matching rows in Table 2.

Which command functions as a RIGHT JOIN with the roles of the tables reversed?

LEFT JOIN

A customer can purchase many products and a product can be purchased by many customers. Which kind of binary relationship does this scenario describe?

Many-to-many

A salesperson is authorized to sell many products and a product can be sold by many salespersons. Which kind of binary relationship does this scenario describe?

Many-to-many

WHERE

Omitting the __________ clause from an UPDATE statement results in all rows being updated/deleted.

Refer to the given information. CREATE TABLE 'test1' ( contact_id INT(10), name VARCHAR(10), event_id INT(5), Which command should be added to the end to make the event_id attribute the primary key within a CREATE TABLE statement?

PRIMARY KEY (event_id));

Refer to the given information. CREATE TABLE Invoice ( invoice_id INT NOT NULL, date DATE NOT NULL, customer_id INT NOT NULL, Which clause should be added to the end to make the invoice_id attribute the primary key within a CREATE TABLE statement?

PRIMARY KEY (invoice_id));

A primary key is updated. A foreign key is updated. A row containing a primary key is deleted. A row containing a foreign key is inserted.

Referential integrity can be violated in four ways:

What is the name of the special internal database where the query optimizer finds information?

Relational catalog

Which SQL statement retrieves all of the columns from the Owners table?

SELECT * FROM Owners;

Which SQL statement tallies the number of different cities in which record companies have been founded?

SELECT COUNT(DISTINCT city) FROM recordcompany;

Table SALES ___________ STORE_ID SALES DATE SALES AMOUNT Which SQL statement will return the sales amount for each store?

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALE GROUP BY STORE_ID;

A database administrator of a movie rental company needs to compile a list of movies released each year for a marketing campaign. Which SQL command will accomplish this?

SELECT YEAR, TITLE FROM MOVIE GROUP BY YEAR;

LOWER(s)

SQL string function that returns the lowercase s

REPLACE(s, from, to)

SQL string function that returns the string s with all occurrences of from replaced with to

TRIM(s)

SQL string function that returns the string s without leading and trailing spaces

RAND()

SQL numeric function that returns a random number between 0 (inclusive) and 1 (exclusive)

ROUND(n, d)

SQL numeric function that returns n rounded to d decimal places

ABS(n)

SQL numeric function that returns the absolute value of n

LOG(n)

SQL numeric function that returns the natural logarithm of n

SQRT(n)

SQL numeric function that returns the square root of n

Which function determines the total value for a column?

SUM()

NULL

SUM, AVG, MIN, and MAX ignore ______ values.

Which delete rule sets column values in a child table to a missing value when the matching data is deleted from the parent table?

Set-to-Null

Which task does ORDER BY perform by default?

Sorting rows in ascending order

Tables are normalized — exactly one value exists in each cell. No duplicate column names — duplicate column names are not allowed in one table. However, the same column name can appear in different tables. No duplicate rows — no two rows may have identical values in all columns.

Tables obey three structural rules:

BINARY

The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the ______________ keyword.

A database manager plans to create a view of a table and has these two elements: • The CREATE VIEW privilege for the table • A level of privilege for every column selected by the SELECT What else does the manager need to create this view?

The SELECT privilege for every column referred to elsewhere in the statement

SET

The UPDATE statement uses the _____________ clause to specify the new column values.

Which condition must be in effect to use the INSERT INTO ... VALUES syntax for an INSERT statement, assuming there is no auto-increment option?

The VALUES list must contain a value for each column in the table.

data types

The ______ ______ of the foreign and primary keys must be the same, but the names may be different.

AS

The _______ keyword follows a column or table name to create an alias.

NOT EXISTS

The ___________ _____________ operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected.

WHERE

The ___________ clause is used with UPDATE, DELETE, and SELECT statements to specify a condition that must be true for a row to be chosen.

A manager deletes the BUILDING_MANAGEMENT row in a DEPARTMENT table. Deleting the row also deletes the GARDENING_TEAM and CLEANING_TEAM rows from the referenced PERSONNEL_TEAMS table. Which referential-integrity rule is in effect?

The cascade delete rule

Salesperson 361 is related to two customers. The table follows the restrict delete rule. What happens if someone tries to delete Salesperson 361 from the customer records?

The delete is prohibited.

DROP VIEW patient_view; What happens as a result of the execution of this statement to the patient table on which patient_view is based?

The view is discarded

Refer to the given statement. DROP VIEW EMPLOYEE; What happens as a result of the execution of this statement to the HRDB table on which the EMPLOYEE view is based?

The view is discarded.

WITH CHECK OPTION

To prevent inserts or updates that appear to fail, databases that support view updates have an optional __________ ___________ _____________ clause. When it is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause. Instead, the database generates an error message that explains the violation.

Why does MySQL's architecture use concurrency control?

To prevent two users from modifying the same record at the same time.

Why is a view used to give controlled access to data?

To restrict access to persons retrieving and modifying sensitive information

What is the purpose of using a SELECT statement?

To retrieve data

NULL

When a row is inserted into a table, an unspecified value is assigned ___________ by default.

Refer to the given SQL statement. SELECT EMPNUM FROM EMPLOYEE Which line added to the end of the statement returns employee numbers of at least 1000?

WHERE EMPNUM >= 1000;

Refer to the given SQL statement. SELECT PRODNUM, PRODNAME FROM PRODUCT Which line should be added to the end of the statement to return the product numbers and product names for products that cost 20 dollars?

WHERE PRODCOST=20;

Refer to the given SQL statement: SELECT item_number, item_name FROM item Which line should be added to the end of the statement to return the item numbers and item names for items that have an item_price of 10 dollars?

WHERE item_price = 10;

Refer to the given SQL statement. SELECT patient_id FROM patient Which line added to the end of the statement returns patient ids of at least 1000?

WHERE patient_id >=1000;

Which optional clause added to a GRANT statement enables the account to give its own privileges to other uses?

WITH GRANT OPTION

Protect sensitive data. Save complex queries. Save optimized queries.

What are some advantages of creating views?

table

What is the result of a relational operation?

subquery, sometimes called a nested query or inner query

a query within another SQL query.

relation

a named set of tuples, all drawn from the same sequence of domains.

unsigned

a number that cannot be negative.

signed

a number that may be negative.

non-NULL

________-________ foreign key values must match some value of the primary key.

Views

___________ restructure table columns and data types without changes to the underlying database design.

JOIN

a SELECT statement that combines data from two tables, known as the left table and right table, into a single result.

auto-increment column

a column that is assigned an automatically incrementing value.

non-key column

a column that is not contained in a candidate key.

foreign key

a column, or group of columns, that refer to a primary key.

primary key

a column, or group of columns, used to identify a row.

relational model

a database model based on mathematical principles, with three parts: A data structure that prescribes how data is organized. Operations that manipulate data structures. Rules that govern valid relational data.

tuple

a finite sequence of values, each drawn from a fixed domain.

aggregate function

a function that works on a group of values.

domain

a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE.

Unary relationship

a relationship that associates occurrences of an entity type with other occurrences of the same entity type.

artificial key

a single-column primary key created by the database designer when no suitable single-column or composite primary key exists.

alias

a temporary name assigned to a column or table.

CREATE TABLE activity ( activity_id int NOT NULL, activity_name varchar(30) NOT NULL, activity_date date NOT NULL, time varchar(20) NOT NULL, location varchar(30) NOT NULL, coordinator_id varchar(10) NOT NULL, PRIMARY KEY (activity_id), FOREIGN KEY (coordinator_id) REFERENCES coordinator (coordinator_id) ); Which two columns are created as something other than variable-length strings in the statement above?

activity_id, activity_date

OUTER JOIN

any join that selects unmatched rows, including left, right, and full joins.

ADD

clause that adds a column

GROUP BY

clause that groups rows with identical values into a set of summary rows. returns one row for each group.

LIMIT

clause that limits the number of rows returned by a SELECT statement.

CHANGE

clause that modifies a column

INTO

clause that names the table and columns where data is to be added.

ORDER BY

clause that orders selected rows by one or more columns in ascending (alphabetic or increasing) order.

ON DELETE

clause that responds to an invalid primary key deletion.

ON UPDATE

clause that responds to an invalid primary key update.

VALUES

clause that specifies the column values to be added.

ON

clause that specifies the join columns.

WHERE

clause that works like an if statement in a programming language, specifying conditions that must be true for a row to be selected.

DISTINCT

clause used with a SELECT statement to return only unique or 'distinct' values.

HAVING

clause used with the GROUP BY clause to filter group results. The _____________ clause must appear after the GROUP BY clause but before the optional ORDER BY clause.

CHAR(N), VARCHAR(N)

character data types

equijoin

compares columns of two tables with the = operator.

non-equijoin

compares columns with an operator other than =, such as <, >.

PRIMARY KEY

constraint in a CREATE TABLE statement that names the column(s) that uniquely identify each row.

UNIQUE

constraint that ensures all column values are unique.

CHECK

constraint that specifies an expression that limits the range of a column's values.

NOT NULL

constraint used in a CREATE TABLE statement to prevent a column from having a NULL value.

DEFAULT

constraint used in a CREATE TABLE statement to specify a column's default value when no value is provided.

DATE, TIME, DATETIME

date and time data types

DECIMAL(M,D), FLOAT, DOUBLE

decimal data types

TINYINT, SMALLINT, MEDIUMINT, INTEGER or INT, BIGINT

integer data types

FULL JOIN

join that selects all left and right table rows, regardless of match.

LEFT JOIN

join that selects all left table rows, but only matching right table rows.

RIGHT JOIN

join that selects all right table rows, but only matching left table rows.

INNER JOIN

join that selects only matching left and right table rows.

FROM

keyword in a DELETE statement that is followed by the table name whose rows are to be deleted.

AUTO_INCREMENT

keyword that defines an auto-increment column in MySQL.

DESC

keyword with the ORDER BY clause that orders rows in descending order.

Relational rules, also known as integrity rules

logical constraints that ensure data is valid and conforms to business policy.

RESTRICT

rejects an insert, update, or delete that violates referential integrity.

Business rules

relational rules specific to a particular database and application.

Structural rules

relational rules that govern data in every relational database.

ternary relationship

relationship that involves three different entity types.

INSERT

statement that adds rows to a table.

ALTER TABLE

statement that adds, deletes, or modifies columns on an existing table.

CREATE DATABASE

statement that creates a new database.

CREATE TABLE

statement that creates a new table by specifying the table name, column names, and column data types.

CREATE VIEW

statement that creates a view table and specifies the view name, query, and, optionally, column names.

CREATE INDEX

statement that creates an index by specifying the index name and table columns that compose the index.

DROP

statement that deletes a column

DROP INDEX

statement that deletes a table's index.

DROP TABLE

statement that deletes a table, along with all the table's rows, from a database.

DELETE

statement that deletes existing rows in a table.

DROP DATABASE

statement that deletes the database, including all tables in the database

SHOW INDEX

statement that displays a table's index. generates a result table with one row for each column of each index.

EXPLAIN

statement that generates a result table that describes how a statement is executed by the storage engine.

UPDATE

statement that modifies existing rows in a table.

SHOW

statement that provides database users and administrators with information about databases, the database contents (tables, columns, etc.), and server status information.

self-join

when a table is joined to itself. A _________-__________ can compare any columns of a table, as long as the columns have comparable data types.

Optional attribute

when each entity instance can have zero attribute instances.

Required attribute

when each entity instance has at least one attribute instance.