Database Foundations Final Practice Test

DML (Data Manipulation Language)

A part of SQL that is used query, insert, update and remove data from a database; includes INSERT, UPDATE, DELETE

TCL (Transaction Control Language)

A part of SQL that is used to manage different transactions occurring within a database; includes COMMIT, SAVEPOINT, ROLLBACK

DCL (Data Control Language)

A part of SQL that is used to manage permissions to objects in a database; includes GRANT, REVOKE

DQL (Data Query Language)

Used for performing queries on the data within schema objects; includes SELECT

DDL (Data Definition Language)

a way of specifying all the attributes and properties of a database: create tables and views with indexes and constraints; includes ALTER, DROP, CREATE, TRUNCATE

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

a. AND

Which operator is used to display rows based on an ordered range of values? a. BETWEEN b. IN c. NOT NULL d. LIKE

a. BETWEEN

What type of join is the following statement?SELECT e.EMPLOYEE_ID, e.LAST_NAME, d.DEPARTMENT_ID, d.DEPARTMENT_NAME, d.LOCATION_IDFROM EMPLOYEES e, DEPARTMENTS d; a. CROSS JOIN b. NATURAL JOIN c. OUTER JOIN d. INNER JOIN

a. CROSS JOIN

___________________ is used to add, modify and delete data. a. Data Manipulation Language b. Data Management Language c. Data Control Language d. Data Definition Language

a. Data Manipulation Language

A join between two tables that returns the results of an INNER join as well as the results of a left and right join is a ______________ join. a. FULL OUTER b. INNER OUTER c. CROSS JOIN d. TOTAL OUTER

a. FULL OUTER

You use the ______ operator to perform wildcard searches of valid search string values. a. LIKE b. MATCH c. STRING d. BETWEEN

a. LIKE

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

a. OR

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

a. Right click Logical model, select Create Glossary from Logical Model

You can display selected columns of a table by listing them in the __________ clause. a. SELECT b. IF c. FROM d. WHERE

a. SELECT

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

a. SELECT c. DESCRIBE

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

a. SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000 AND job_id LIKE '%JEF%' ;

Which column alias will cause an error? a. SELECT last_name AS Last Name FROM employees; b. SELECT last_name "Last Name" FROM employees; c. SELECT last_name lname FROM employees; d. SELECT last_name AS lname FROM employees;

a. SELECT last_name AS Last Name FROM employees;

SQL Commands can be entered and run from the ________________ option within the APEX Application. a. SQL Workshop b. Application Builder c. Packed Apps d. Utilities

a. SQL Workshop

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

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

The command that always removes all rows from a table, leaving the table empty and the table structure intact is ... a. TRUNCATE b. DELETE c. Both d. None

a. TRUNCATE

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

a. The field in the USING clause cannot have a qualifier.

A logical condition combines the result of two component conditions to produce a single result based on those conditions or it inverts the result of a single condition. a. True b. False

a. True

A self-join can be used when there are two fields with the same data on a table that have different meanings. a. True b. False

a. True

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. a. True b. False

a. True

Character strings and dates in the WHERE clause must be enclosed with single quotation marks (' '). a. True b. False

a. True

Columns without the NOT NULL constraint can contain null values by default. a. True b. False

a. True

Constraints ensure the consistency and integrity of the database. a. True b. False

a. True

Constraints should be given meaningful names to make them easier to reference. a. True b. False

a. True

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? a. True b. False

a. True

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. a. True b. False

a. True

In Oracle SQL Developer Data Modeler you can select select how you want to map your subtypes. a. True b. False

a. True

Substitution variables are used when you want to prompt for different criteria in a condition. a. True b. False

a. True

The CARTESIAN or CROSS join gets created when a join condition is omitted. a. True b. False

a. True

The Oracle server ensures data consistency based on transactions. a. True b. False

a. True

The TRUNCATE statement is a data definition language (DDL) statement and generates no rollback information. a. True b. False

a. True

The following statement will execute successfully (true or false):SELECT employee_id, last_name, salary*12 annsalFROM employeesORDER BY annsal ; a. True b. False

a. True

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

a. True

Top-Down Modeling is the approach taken for designing a new database. a. True b. False

a. True

Top-n-analysis is used when you want to retrieve only the top number of records from a result set. a. True b. False

a. True

Will the following statement execute successfully (True or False)?SELECT employee_id, city, department_nameFROM employees e JOIN departments dON d.department_id = e.department_idJOIN locations lON d.location_id = l.location_id; a. True b. False

a. True

You can create a marker in the current transaction by using the SAVEPOINT statement. a. True b. False

a. True

You can display all columns of data in a table by following the SELECT keyword with an asterisk (*). a. True b. False

a. True

You can override the default order by using parentheses around the expressions that you want to calculate first. a. True b. False

a. True

You can specify multiple columns after the DISTINCT qualifier. a. True b. False

a. True

You must first select the Logical model you want to work with to Engineer it to a Relational model. a. True b. False

a. True

By default column aliases appear _________. a. Upper case b. Lower case c. There is no default. d. Mixed case

a. Upper case

If an arithmetic expression contains more than one operator, __________ are evaluated first. a. anything in parenthesis b. exponentiation c. addition and subtraction d. multiplication and division

a. anything in parenthesis

You can use _____________ to temporarily store values, while executing a query. a. substitution variables b. literal values c. database tables d. database fields

a. substitution variables

A collection of DML statements that form a logical unit of work is called a ______________. a. transaction b. command c. statement d. block

a. transaction

Which of the following is the wildcard used for any number of characters in SQL? a. & b. % c. # d. ||

b. %

An automatic commit occurs when _________ . (Choose 2) a. SELECT statement is executed. b. A TCL statement is executed. c. A DDL statement is executed. d. A DML statement is executed.

b. A TCL statement is executed. c. A DDL statement is executed.

The ___________ JOIN clause produces the cross-product of two tables. a. INNER b. CROSS c. OUTER d. CARTESIAN

b. CROSS

When you _______ a table, the database loses all the data in the table. a. ERASE b. DROP c. ALTER d. SELECT

b. DROP

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

b. Displays the EMPLOYEE_ID, LAST_NAME, JOB_ID, DEPARTMENT_ID from the EMPLOYEES table where the department id is 90.

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. a. True b. False

b. False

Table names and column names must begin with a number and should be 1-30 characters long. a. True b. False

b. False

The ORDER BY clause must be placed before the WHERE clause in a SQL statement. a. True b. False

b. False

Users may view data that is in the process of being changed by another user. a. True b. False

b. False

You can delete a row that contains a primary key that is used as a foreign key in another table. a. True b. False

b. False

To create a table you must identify all of the following except : a. Field names b. Field values c. Field data types d. Table name

b. Field values

Which two statements are correct? a. Keywords can be abbreviated or split across lines. b. Keywords cannot be abbreviated or split across lines. c. SQL statements are not case sensitive (unless indicated). d. SQL statements are case sensitive.

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

Which two statements are correct? a. SQL statements are case sensitive. b. Keywords cannot be abbreviated or split across lines. c. Keywords can be abbreviated or split across lines. d. SQL statements are not case sensitive (unless indicated).

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

When building a model in Oracle SQL Data Modeler you begin in the ________ tab. a. Physical b. Logical c. Relational c. Conceptual

b. Logical

The ________ join clause is based on all the columns in the two tables that have the same name and the same datatype. a. USING b. NATURAL JOIN c. CROSS JOIN d. OUTER JOIN

b. NATURAL JOIN

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

b. Not Null

In a SELECT statement the ________ clause can be used to sort the rows. a. WHERE b. ORDER BY c. ORDER d. SORT

b. ORDER BY

Which constratint specifies that the column (or combination of column) must contain unique value AND IS NOT NULL for all rows? a. NOT NULL b. PRIMARY KEY c. UNIQUE KEY d. CHECK

b. PRIMARY KEY

You can define naming standards in Oracle SQL Developer Data Modeler using _______________. a. Glossary b. Naming Templates c. Design Rule Validations d. All of the above can be used to define naming standards in Oracle SQL Data Modeler.

d. All of the above can be used to define naming standards in Oracle SQL Data Modeler.

Data Manipulation Language includes all of the following except : a. INSERT b. DELETE c. UPDATE d. COMMIT

d. COMMIT

Which software is used to manage reading and manipulating a relational database ? a. ERD software b. Flat file software c. Spreadsheet software d. DBMS software

d. DBMS software

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

d. Do all of these steps.

In the SQL Workshop/SQL Commands ___________ tab you can view and retrieve previously run queries. a. Explain b. Saved SQL c. Describe d. History e. Results

d. History

An _______ clause creates an equijoin between two tables using one column from each table regardless of the name or data type. a. CROSS JOIN b. NATURAL JOIN c. USING d. ON

d. ON

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

d. Oracle SQL Data Modeler

_______ Engineering is the process of creating a conceptual or logical model by extracting the information from an existing data source. a. Top-Down b. Target c. Forward d. Reverse

d. Reverse

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

d. SELECT queries

Oracle Application Express is an application platform used to share and learn _____ and __________. a. Java, PL/SQL b. SQL, Java c. SQL, Java Script d. SQL, PL/SQL

d. SQL, PL/SQL

Which of the following cannot be modeled using the Oracle SQL Data Modeler ? a. Entities, attributes, UIDs b. Supertype and subtype entities c. Recursive Relationships d. Arc relationships e. All can be modeled.

e. All can be modeled

All of the following are stages of SQL Processing except: a. Row Source Generation b. Execution c. Parsing d. Optimization e. All of the above options are stages of SQL processing.

e. All of the above options are stages of SQL processing.

Which statements will execute successfully?(Choose 2) a. SELECT employee_id, first_name, last_nameFROM employeesORDER BY employee_id DESCEND; b. SELECT employee_id, last_name, job_id, department_id, hire_dateFROM employeesORDER BY 3; c. SELECT first_name, last_nameFROM employeesORDER BY employee_id DES; d. SELECT first_name, last_name, first_name||last_name fullnameFROM employeesORDER BY fullname;

b. SELECT employee_id, last_name, job_id, department_id, hire_dateFROM employeesORDER BY 3; d. SELECT first_name, last_name, first_name||last_name fullnameFROM employeesORDER BY fullname;

Which column alias will cause an error? a. SELECT last_name AS lname FROM employees; b. SELECT last_name AS Last Name FROM employees; c. SELECT last_name lname FROM employees; d. SELECT last_name "Last Name" FROM employees;

b. SELECT last_name AS Last Name FROM employees;

You can upload and save a SQL Script from the _____________ option. a. Utilities b. SQL Scripts c. RESTful Services d. Object Browser e. SQL Commands

b. SQL Scripts

To use the Oracle SQL Developer Data Modeler to apply naming standards : a. Use a .docx file constaining table names and abbreviations b. Use a .csv file constaining table names and abbreviations c. Use a .accdb file constaining table names and abbreviations d. None of the above.

b. Use a .csv file constaining table names and abbreviations

The _________ clause in a query comes immediately after the SELECT and FROM a. IF b. WHERE c. ORDER BY d. GROUP BY

b. WHERE

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

c. 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. a. BEGINNING b. STANDARD c. DEFAULT d. INSERT

c. DEFAULT

The type of SQL Command used to modify data is : a. DCL b. DDL c. DML d. TCL

c. DML

The DESCRIBE command shows the following about a table except : a. Primary key b. Field names c. Data values d. Data types

c. Data values

A transaction begins when the first DML statement is encountered and ends when one of the following occurs ..... a. A COMMIT or ROLLBACK statement is issued. b. A DDL statement, such as CREATE, is issued. c. Either of the above statements d. None of the above

c. Either of the above statements

When mapping subtypes to tables we can ________? a. Use one table b. Use multiple tables c. Either one or multiple tables are valid options. d. Neither one or multiple tables are valid options.

c. Either one or multiple tables are valid options.

_______ consistency guarantees a consistent view of the data at all times. a. Write b. Data c. Read d. Table

c. Read

To see all the fields and records in the employees table you can run the following query : a. SELECT all FROM employees; b. SELECT columns FROM employees; c. SELECT * FROM employees; d. SELECT # FROM employees; e. None of the above.

c. SELECT * FROM employees;

You use the __________ option to mark one or more columns as unused. a. MARK UNUSED b. DELETE c. SET UNUSED d. TRUNCATE

c. SET UNUSED

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

c. 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: a. Create process, logical, relational and physical models b. Capture business rules and information c. Store application data d. Store metadata information

c. Store application data

____________ give you more flexibility and control when changing data, and they ensure data consistency in the event of user process failure or system failure. a. Functions b. Procedures c. Transactions d. Code blocks

c. Transactions

A literal can be all of the following except : a. a character b. a date c. a calculation d. a number

c. a calculation

Relational Databases store data in a two-dimensional matrix known as a _________. a. flat file b. database c. table d. entity

c. table