C170 Practice Test

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

(SELECT customer_id

If one department chair—a professor—can chair only one department, and one department can have only one department chair. The entities PROFESSOR and DEPARTMENT exhibit a(n) _____ relationship.

1:1

A table that is in 2NF and contains no transitive dependencies is said to be in _____.

3NF

In the diagram, which type of relationship is depicted?

A Painter paints at least one painting and could paint many paintings. A painting is painted by one and only one painter.

In the diagram, which type of relationship is depicted?

A professor teaches zero to many sections. A section is taught by one and only one professor.

In the relationship depicted below, which of the following is true?

A specific course is taught by one and only one instructor.

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

ALTER

All changes to a table structure are made using the _____ command, followed by a keyword that produces the specific changes a user wants to make.

ALTER TABLE

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

ALTER TABLE 'Vendor' ADD INDEX 'state_index ('V_STATE');

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

ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent (parent_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? Choose 2 answers:

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

What is a characteristic of a database that is normalized to the third normal form? (Choose 2)

All redundancy has been removed. There are no transitive dependencies.

In the diagram, which type of relationship is depicted?

An employee learns a minimum of one and a maximum of many skills. A skill is learned by one to many employees.

In the diagram, which type of relationship is depicted?

An employee manages one and only one store and a store is manages by one and only one employee.

How is this portion of the entity-relationship diagram read, from left to right?

At least one appointment is scheduled by a patient.

Which two MySQL data types can represent images or sounds?

BINARY, TINYBLOB

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

CREATE DATABASE IF NOT EXISTS db_name;

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 (

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

CREATE TABLE new_tbl_name LIKE tbl_name; INSERT INTO new_tbl_name SELECT * FROM tbl_name;

Refer to the given SQL statement. SELECT customer_id, customer_last_name, customer_first_name FROM customer; Which statement, when added before this statement, generates a view?

CREATE VIEW custview AS

A _____ key can be described as a minimal Super Key,. A Super Key without any unnecessary attributes.

Candidate

A primary key is a(n) _____ key chosen to be the primary means by which rows of a table are uniquely identified.

Candidate

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

You are creating a relational database to store information about instructors and the courses that each instructor teaches. Each course is taught by a single instructor. You have created an Instructor table and a Course table as shown above. You need to create a relationship between the Instructor table and the Course table. You need to keep duplicate data to a minimum. What should you do?

Create a new column in the Course table.

The _____ constraint assigns a value to an attribute when a new row is added to a table.

DEFAULT

Which statement deletes all rows from the invoice table?

DELETE FROM Invoice;

The _____ command would be used to delete the table row where the P_CODE is 'BRT-345'.

DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';

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;

A table can be deleted from the database by using the ___ command.

DROP TABLE

Which command eliminates a table?

DROP TABLE

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 MySQL syntax. ALTER TABLE tbl_name action [, action ] ... ; What does this syntax allow? Choose two answers:

Dropping Indexes, Changing column data types

In the diagram, which type of relationship is depicted?

Each instructor teaches one or more courses.

The CUSTOMER table's primary key is CUS_CODE. The CUSTOMER primary key column has no null entries, and all entries are unique. This is an example of _____ integrity.

Entity

A(n) _____ links tables on the basis of an equality condition that compares specified columns of each table.

Equijoin

Which statement should be used to establish a foreignkey on customer_id?

FOREIGN KEY (customer_id) REFERENCES customer (customer_id)

Dependencies that are based on only a part of a composite primary key are called transitive dependencies.

False

Only one column may be listed in a PRIMARY KEY constraint.

False

SQL requires the use of the ADD command to enter data into a table.

False

When joining three or more tables, you need to specify a join condition for one pair of tables.

False

The cluster key is normally the primary key of both tables in a cluster.

False The cluster key is often the primary key of one table and the corresponding foreign key of another. This optimizes primary key - foreign key join queries.

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

Foreign

A _____ is the primary key of one table that has been placed into another table to create a common attribute and establish a relationship.

Foreign Key

With which command can a database administrato rallowa usernamed 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;

The _____ clause of the GROUP BY statement operates very much like the WHERE clause in the SELECT statement.

HAVING

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 statement correctly inserts an unnamed department with no manager?

INSERT INTO Department (Name, ManagerID) VALUES (' ', 'NULL');

Referential _____ dictates that the foreign key must contain values that match the primary key in the related table or must contain null.

Intergrity

In this entity-relationship diagram, many instructors are shown as working at a minimum of one campus. How is the relationship between instructors and campuses represented in the diagram?

It is linked through the campus_id.

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.

_____ relationships can be implemented by creating a new entity in 1:M relationships with the original entities.

M:N

Which alteration to the CREATE TABLE statement prevents ManagerID from being NULL?

ManagerID SMALLINT NOT NULL,

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

Which kind of relationship is displayed in this entity-relationship diagram?

Many-to-many unary

The database table above has 7 different physician IDs in the first column and physician_id is the primary key of the table. There are 7 different physician names in the second column. The third column indicates which site a physician is located at. There are 3 different sites. Two of the site names are repeated 3 times. Which action should be used to translate this data into third normal form?

Move the data from the third column into its own table.

The _____ constraint can be placed on a column to ensure that every row in the table has a value for that column.

NOT NULL

Which rule would you use in your CREATE TABLE statement for your Category table if you would like to have all products related to a specific category deleted if they are linked to a category that is deleted?

ON DELETE CASCADE

Which rule would you use in your CREATE TABLE statement for your Category table if you needed to prevent deletion of a category if there were associated products in the Product table?

ON DELETE RESTRICT

You are creating a database which will have the 2 tables shown above. You need to ensure that if a category is removed from the Category table, all related product rows would have their CategoryID set to a null value. What should you use?

ON DELETE SET TO NULL

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));

Dependencies based on only a part of a composite primary key are known as _____ dependencies.

Partial

The SQL data manipulation command HAVING:

Restricts the selection of grouped rows based on a condition.

Which of the following queries is used to list a unique value for V_CODE, where the list will produce only a list of those values that are different from one another?

SELECT DISTINCT V_CODE FROM PRODUCT;

The SQL query to output the contents of the EMPLOYEE table sorted by last name, first name, and initial is _____.

SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the values of V_CODE match is _____.

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

Update Customer******WHERE Customer_ID = 'CUS7878'; The _____ command replaces the ***** in the syntax of the UPDATE command, shown above.

SET columnname = expression

A database designer is working through the normalization process. A simple primary key has been established. All of the repeating groups and multi-valued fields have been removed.

Second Normal Form

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

Set-to-Null

A _____ is any key that uniquely identifies each row.

Super Key

What is a characteristic of a database that is normalized to the first normal form?

Tables do not contain multi-valued or repeating fields.

A patient is related to two exam records. The table follows the restrict delete rule. What happens if someone tried to delete the patient?

The delete is not allowed

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

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 row values for two individual columns

What does WHERE identify in a basic SQL SELECT statement?

The rows to be included in the result set

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

What is a characteristic of a database that is normalized to the second normal form?

There are no values that are dependent on only part of a composite primary key.

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

A determinant is any attribute whose value determines other values within a column.

True

Entity integrity is enforced automatically when the primary key is specified in the CREATE TABLE command sequence.

True

If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A).

True

Since a partial dependency can exist only if a table's primary key is composed of two or more attributes, if a table in 1NF has a single-attribute primary key, then the table is automatically in 2NF.

True

Cluster keys in a table cluster are similar to sort columns in a sorted table.

True Cluster keys are similar to sort columns, in that both determine the physical order of rows on storage media.

Which kind of relationship is displayed in this entity-relationship diagram?

Unary one-to-one

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

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? Choose 2 answers

activity_id, activity_date

When a user issues the DELETE FROM tablename command without specifying a WHERE condition, _____.

all rows will be deleted

A(n) _____ only returns matched records from the tables that are being joined.

inner join

A(n) _____ join links tables by selecting only the rows with common values in their common attribute(s).

natural

What are the differences between primary index, clustering index and secondary index?

primary index is an index on a unique sort column. clustering index is an index on a non-unique sort column. secondary index is an index that is not on the sort column.

A(n) _____ is a query that is embedded (or nested) inside another query.

subquery

A(n) _____ exists when there are functional dependencies such that Y is functionally dependent on X, Z is functionally dependent on Y, and X is the primary key.

transitive dependency

When you define a table's primary key, the DBMS automatically creates a(n) _____ index on the primary key column(s) you declared.

unique