Database Management I: Section 4-6 Test

To eliminate duplicate rows in the result, include the _________ keyword in the SELECT clause.

DISTINCT

When mapping subtypes to tables we can ________ Use one table Use multiple tables Either one or multiple tables are valid options. Neither one or multiple tables are valid options.

Either one or multiple tables are valid options.

T/F: The glossary can be used as the naming standard for your Logical Model.

True

By default column aliases appear _________.

Upper case

Data Manipulation Language includes all of the following except: DELETE COMMIT INSERT UPDATE

COMMIT

With the __________ and ROLLBACK statements, you have control over making changes to the data permanent

COMMIT

To issue a CREATE TABLE statement, you must have the __________ privilege.

CREATE TABLE

The UPDATE statement modifies the values of a specific row or rows if the _______ clause is specified.

MODIFY VALUE WHERE SET Answer key says it's SET, but uh...

Which symbol is the wildcard used for any number of characters in SQL?

%

The ___________ JOIN clause produces the cross-product of two tables.

CROSS

T/F: Constraints cannot be added to a table after its creation.

False

The Data Definition Language performs all of the following except: Add constraints to tables Insert data into tables Alter tables Create tables

Insert data into tables

You can display selected columns of a table by listing them in the __________ clause.

SELECT

A _______ clause creates an equijoin between two tables using one column with the same name, regardless of the data type.

USING

When building a model in Oracle SQL Data Modeler you begin in the ________ tab.

Logical

The ______ operator requires either of the component conditions to be true. AND BETWEEN EITHER OR

OR

_______ consistency guarantees a consistent view of the data at all times.

Read

Which character is used as a substitution variable in APEX?

: (a colon)

Which SQL key word is used to do ranking in top-n-analysis

ROWNUM

T/F: Table names and column names must begin with a number and should be 1-30 characters long.

False

T/F: Users may view data that is in the process of being changed by another user.

False

T/F: When you define a table, you can specify that a column should be given a default value by using the LITERAL option.

False

T/F: In Oracle SQL Developer Data Modeler you can select how you want to map your subtypes.

True

T/F: Literals can be used within a SELECT statement.

True

T/F: The Oracle server ensures data consistency based on transactions.

True

T/F: Top-n-analysis is used when you want to retrieve only the top number of records from a result set.

True

T/F: You can override the default order by using parentheses around the expressions that you want to calculate first.

True

T/F: You can specify multiple columns after the DISTINCT qualifier.

True

A __________ can be found at the intersection of a row and column and contains one value.

field

Relational Databases store data in a two-dimensional matrix known as a _________.

table

T/F: We must use the ON clause to join three tables.

Answer Key: False But uhhh, pretty sure 'tis true.

Which operator is used to display rows based on an ordered range of values?

BETWEEN

The _______ condition tests for nulls.

IS NULL

You use the ______ operator to perform wildcard searches of valid search string values.

LIKE

Oracle Application Express is an application platform used to share and learn _____ and __________.

SQL, PL/SQL

T/F: A foreign key value must match an existing primary key value otherwise, it must be null.

True

You can use _____________ to temporarily store values, while executing a query.

substitution variables

A collection of DML statements that form a logical unit of work is called a ______________.

transaction

An automatic commit occurs when _________ . (Choose 2)(Choose all correct answers) A TCL statement is executed. A DML statement is executed. SELECT statement is executed. A DDL statement is executed.

A TCL statement is executed. A DDL statement is executed.

You can link columns to other columns, arithmetic expressions, or constant values to create a character expression by using the ____________ operator (||).

concatenation

The following statement displays all the rows in the departments table: SELECT ____ FROM departments;

*

T/F: The ORDER BY clause must be placed before the WHERE clause in a SQL statement.

False

Which column alias is invalid? First Name "First Name" "FIRST NAME" First_Name

First Name

________ Engineering is the process of transforming a Logical Data Model to a Relational Model.

Forward

The ________ join clause is based on all the columns in the two tables that have the same name and the same datatype.

NATURAL JOIN

Which statement displays the last name, salary, and annual compensation of employees where the annual compensation is calculated by multiplying the monthly salary with 15, plus a one-time bonus of $200. SELECT last_name, salary, 15*salary+200 FROM employees; SELECT last_name, salary, 15*(salary+200) FROM employees; Either statement will produced the desired result. Neither statement will produce the desired result.

SELECT last_name, salary, 15*salary+200 FROM employees; (first option)

T/F: The TRUNCATE statement is a data definition language (DDL) statement and generates no rollback information.

True

T/F: When you have constraints in place on columns, an error is returned if you try to violate the constraint rule.

True

A literal can be all of the following except: a calculation a character a number a date

a calculation

Which is the correct order of execution for statements in a SELECT query? FROM, SELECT, ORDER BY, WHERE SELECT, ORDER BY, FROM, WHERE FROM, WHERE, SELECT, ORDER BY SELECT, WHERE, FROM, ORDER BY

once again, no correct answers????? [https://classroom.google.com/c/MTc2MTU5MjU3NjVa/m/MzA0OTM1ODEyMDRa/details] Answer Key: SELECT, WHERE, FROM, ORDER BY Internet & Emily: FROM, WHERE, SELECT, ORDER BY

According to the rules of precedence which operator will be evaluated first? AND OR Both are on the same level of precedence.

AND

The command that removes all rows from a table, leaving the table empty and the table structure intact is...

TRUNCATE

What does the following statement do: ALTER TABLE DEPT_TEST ADD CONSTRAINT unq_dept_det UNIQUE (DEPT_ID, DEPARTMENT_NAME) ; It alters the DEPT_TEST table to add a composite unique key comprising of the DEPT_ID and DEPARTMENT_NAME. It alters the DEPT_TEST table to add a composite foreign key comprising of the DEPT_ID and DEPARTMENT_NAME. It alters the DEPT_TEST table to add a the DEPT_ID and DEPARTMENT_NAME columns. It alters the DEPT_TEST table to add a composite primary key comprising of the DEPT_ID and DEPARTMENT_NAME.

It alters the DEPT_TEST table to add a composite unique key comprising of the DEPT_ID and DEPARTMENT_NAME. (the first option)

Which two statements are correct? (Choose all correct answers) Keywords cannot be abbreviated or split across lines. SQL statements are not case sensitive (unless indicated). Keywords can be abbreviated or split across lines. SQL statements are case sensitive.

Keywords cannot be abbreviated or split across lines. SQL statements are not case sensitive (unless indicated).

All of the following are tools to access a relational database except: SQL * Plus Oracle Application Express Oracle SQL Data Modeler Oracle SQL Developer

Oracle SQL Data Modeler

Once you have created a table, you can use the ________________ command to confirm that it was created. (Choose 2) (Choose all correct answers) ORDER BY SELECT LIST DESCRIBE

SELECT DESCRIBE

The steps to convert a Relational model to a Logical model using the Oracle SQL Developer Data Modeler are: Select the Relational model, Accept defaults, Click the Engineer to Logical Model icon, Click Engineer Click the Engineer to Relational Model icon, Select the Logical model, Click Engineer, Accept defaults. Select the Relational model, Click the Engineer to Logical Model icon, Accept defaults, Click Engineer None of the above.

Select the Relational model, Click the Engineer to Logical Model icon, Accept defaults, Click Engineer

The Oracle SQL Data Modeler enables you to do all of the following except: Store metadata information Create process, logical, relational and physical models Capture business rules and information Store application data

Store application data

T/F: A self-join can be used when there are two fields with the same data on a table that have different meanings.

True

T/F: An Entity Relationship model does not highlight the physical and database constraints. It is essential to transform the ER model into a relational model which can serve as the basis for defining the physical implementation of the database.

True

You can logically relate data from multiple tables using ___________.

foreign keys

What is the result of executing the following statement: SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, DEPARTMENT_ID AS "DEPTID" FROM EMPLOYEES WHERE DEPARTMENT_ID = 90 ; -Displays the EMPLOYEE_ID, LAST_NAME, JOB_ID, DEPARTMENT_ID, DEPTID from the EMPLOYEES table where the department id is 90. -Displays the EMPLOYEE_ID, LAST_NAME, JOB_ID, DEPARTMENT_ID from the EMPLOYEES table where the department id is 90. -Prompts the user for additional information. -Throws an error

no correct answers??? [https://classroom.google.com/c/MTc2MTU5MjU3NjVa/m/MzA0OTM1ODEyMDRa/details] I think it's the second one (the one without DEPTID)

The stages of SQL processing are Parsing, Optimization, Row Source Generation and _____________ .

Execution

The steps to create a glossary from an existing Logical model are: Right click Logical model, select Create Glossary from Logical Model Double click the Logical Model Rick click the Relational model, select Create Glossary from Relational Model None of the above.

Right click Logical model, select Create Glossary from Logical Model

You can use Application Express (APEX) for

Developing and expanding your skills with Oracle database, SQL and PL/SQL

Arithmetic expressions containing a null value evaluate to _________.

null

T/F: Substitution variables are used when you want to prompt for different criteria in a condition.

True

T/F: The CARTESIAN or CROSS join gets created when a join condition is omitted.

True

T/F: Top-Down Modeling is the approach taken for designing a new database.

True

Which of the following statements is syntactically correct? SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149 ; SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.manager_id = 149; Both statements are syntactically correct. Neither statement is syntactically correct.

Both statements are syntactically correct.

Any column that is not listed explicitly obtains a null value in the new row unless we have _________ values for the missing columns that are used.

DEFAULT

DDL means:

Data Definition Language

___________________ is used to add, modify and delete data.

Data Manipulation Language

To define naming templates in Oracle SQL Data Modeler: Right click the name of the design in object browser Select properties, select template Edit the template to use abbreviations Do all of these steps.

Do all of these steps.

T/F: In the SQL Commands option of APEX, you can run more than one SQL command at a time.

False

T/F: You can delete a row that contains a primary key that is used as a foreign key in another table.

False

The following statement will result in an error (True or False): SELECT last_name "Name" , salary*12 "Annual Salary", salary +100 FROM employees;

False

___________ constraints can only be created at the column level. Not Null Check Unique Primary Key

Not Null

Joining tables with the NATURAL JOIN, USING, or ON clauses results in an __________ join. (Choose 2) (Choose all correct answers) CROSS OUTER INNER Equi-join

OUTER INNER

Which statement will display those employees who have a job title that contains the string 'JEF' and earn $10,000 or more? SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 AND job_id LIKE '%JEF%' ; SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%JEF%' ; Either statement displays the desired output. Neither statement displays the desired output.

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 AND job_id LIKE '%JEF%' ; (the first option)

A database transaction consists of the following except: One TCL Statement DML statements representing one consistent change to the data SELECT queries One DDL Statement

SELECT queries

T/F: In APEX SQL Workshop and Object Browser options you can view table layouts.

True

Why will the following statement result in an error? SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400; WHERE clause cannot be used in a query with USING. The field in the USING clause cannot have a qualifier. Syntax of the USING clause is incorrect. There is nothing wrong - this will run correctly.

The field in the USING clause cannot have a qualifier.

T/F: In Oracle SQL Data Modeler, the attribute that you assign as primary UID is automatically set to a mandatory attribute and will be engineered to a primary key in the relational model.

True

When altering a table, you can decrease the width of a column if ...(Choose 3)(Choose all correct answers) The table has no rows The decrease in column width is not less than the existing values in that column The column contains only null values The column contains numeric values

The table has no rows The decrease in column width is not less than the existing values in that column The column contains only null values

____________ give you more flexibility and control when changing data, and they ensure data consistency in the event of user process failure or system failure.

Transactions

If a table already contains rows when a column is added, the new column is initially null or takes the default value for all the rows. True or False?

True

T/F: A join between two tables that returns the results of the INNER join as well as the unmatched rows from the left (or right) table is called a left (or right) OUTER join.

True

T/F: Character strings and dates in the WHERE clause must be enclosed with single quotation marks

True

T/F: Columns without the NOT NULL constraint can contain null values by default.

True

T/F: Converting a Relational model to a Logical model is called reverse engineering.

True

To connect to an Oracle database you need to use a client program. True or False?

True