Database 225

SELECT DECODE(ACCTNO, 123456, 'CLOSED', 654321, 'SEIZED', 590395, 'TRANSFER', 'ACTIVE') FROM BANK_ACCT;

ACTIVE

UPDATE BANK_ACCT SET NAME = 'SHAW'; Which records will be updated in that table?

All records

You have just removed 1,700 rows from a table that were no longer needed. In order to save the changes you've made to the database, which of the following statements are used?

COMMIT

What is the TCL statement that lets you make changes in your database permanent

Commit

What is the DDL statement that lets you remove a table, index, or object in your database

DROP

You are cleaning information out of the Oracle database. Which of the following statements will get rid of all views that use a table at the same time you eliminate the table from the database?

DROP VIEW

DML

Data Manipulation Language

DCL

Data control Language

All users have permission to create virtual directories in an Oracle database.

False

MySQL lets you update two or more columns in a single SET statement without comma delimiting them.

False

MySQL supports virtual directories but doesn't support externally managed tables.

False

The REPLACE INTO statement works much like a UNION operator.

False

\?

Help

You would like to reference a table in your PL/SQL block. What special syntactic attribute must you precede the SQL statement with in order to make the PL/SQL block compile?

No special syntax is required.

A NATURAL JOIN can use which of the following subclauses when the columns being joined have the same column name

No sub clause required

You issue a SELECT statement on the BANK_ACCT table containing the ORDER BY clause. Which of the following uses of the ORDER BY clause would produce an error?

ORDER BY sqrt(1);

Which of the following databases restricts you to only one rows in a VALUES clause?

Oracle

Which of the following databases support subqueries in the WHERE clause that work with subqueries that return multiple column values?

Oracle

SELECT * FROM EMP WHERE DEPT IN (SELECT DEPT FROM VALID_DEPTS WHERE DEPT_HEAD = 'SALLY' ORDER BY DEPT);

Oracle returns an error

Cross Join

SQL joins are used to combine rows from two or more tables.

You have a group of values from a column in a table, and you would like to perform a group operation on them. Each of the following functions operate on data from all rows as a group, except for which of the following choices?

SQRT

The results of a query can be thought of as the...

Source table

You execute the query SELECT 5 + 4 FROM DUAL. You have never inserted data into the DUAL table before. Which of the following statements best describes the DUAL table?

Table with one column and one row used in various operations

BETWEEN

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

In

The IN operator allows you to specify multiple values in a WHERE clause.

.currval

The Oracle CURRVAL function is used to access the current value of the specified sequence. Note that CURRVAL can only be used if NEXTVAL has been referenced in the current user session at least once.

Right Join

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

ALTER TABLE contact ADD salutation VARCHAR2(3);

The statement adds a column to the CONTACT table definition and populates a null value for all existing rows in the table.

SELECT c.last_name , a.city FROM contact c JOIN address a ON c.contact_id = a.contact_id;

The statement queries the CONTACT.LAST_NAME column value and ADDRESS.CITY column value from an INNER JOIN based on the intersection between CONTACT.CONTACT_ID and ADDRESS.CONTACT_ID column values.

SELECT c.last_name FROM contact c WHERE c.contact_id IN (SELECT a.contact_id FROM address a);

The statement queries the CONTACT.LAST_NAME column value when the CONTACT.CONTACT_ID column values are found in the list of returned values from the subquery returning the ADDRESS.CONTACT_ID column values from the ADDRESS table.

A FULL JOIN works without a set operator in an Oracle database.

True

The default for the ESCAPED BY clause is:

a backslash

\R

change your mysql prompt

\p

print current command

\#

re-hash

What is the DCL statement that lets you remove privileges in your database

revoke

What is the TCL statement that lets you set a bookmark for undoing changes in your database

savepoint

You can load which types of data in MySQL with the LOAD DATA INFILE statement.

strings

\T

tee. set outfile append everything in given outfile

A LEFT JOIN can use which of the following subclauses when the columns being joined have the same column name (multiple answers possible)?

the ON subclause, Using subclause

TCL

transaction control Language

A surrogate key is a single column generated by a sequence value.

true

Denormalized data sets are data sets that include rows of data that belong in multiple tables.

true

\u

use another database

When writing and executing an INSERT statement in MySQL, you cannot override default signatures of the table by providing a list of at least all mandatory columns like you can with an INSERT statement in Oracle.

False

You are busy creating your tables based on an LDM. Which of the following constraints require the REFERENCES privilege in order to be created?

Foreign-key

You have a script you plan to run using SQL*Plus that contains several SQL statements that manage milk inventory in several different tables based on various bits of information. You want the output to go into a file for review later. Which command would you use?

SPOOL

INSERT INTO contact SELECT contact_s1.nextval , 'Smith' , NULL , 'Joseph' FROM dual;

The statement adds a row to the CONTACT table by incrementing a sequence for a pseudo primary key.

A virtual directory's definition in an Oracle database is independent of its physical directory.

True

In MySQL, a low priority operation means that the INSERT statement waits until no queries are pending against the table.

True

You should use unique keys when using the INSET INTO statement to avoid inserting duplicate records twice.

True

You are creating some tables in your database as part of the logical data model. Which of the following constraints have an index associated with them that is generated automatically by Oracle?

Unique

You wish to join the data from two tables, A and B, into one result set and display that set in your session. Tables A and B have a common column, called C in both tables. Which of the following choices correctly displays the WHERE clause you would use if you wanted to see the data in table A where the value in column C = 5, even when there was no corresponding value in table B?

WHERE A.C = 5 AND A.C = B.C (+);

Which of the following databases lets you put one or more rows in a VALUES clause?

mysql

You create a table but then subsequently realized you needed a few new columns. To add those columns later, you would issue which of the following statements?

ALTER TABLE

You are managing data access for an application with 163 tables and 10,000 users. Which of the following objects would assist in managing access in this application by grouping privileges into an object that can be granted to users at once?

Roles

Full Join

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

The MERGE statement in Oracle merges data from a query into a target table based on criteria evaluated in the ON subclause.

True

You can nest CASE statements within CASE statements in both Oracle and MySQL.

True

\h

help

An INNER JOIN is also an equijoin between which of the following?

- A set column in one table with a set of columns in another table - A set of column in one copy of a table with a set of column in another copy of the same table - A column in one copy of a table with a column in another copy of the same table - A column in one table with a column in another table

Which of the following are aggregation functions

- COUNT - MAX - SUM

CREATE SEQUENCE MY_SEQ START WITH 394 INCREMENT BY 12 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE;

418

You may need to grant a global FILE privilege in MySQL to read external CSV files.

True

You have a script you plan to run using SQL*Plus that contains one SQL statement that INSERTs data into one table. Which of the following options is the easiest way for this script to allow you to specify values for variables once in the script, in a way where there is no user interaction?

Use DEFINE to capture value

You join data from two tables, EXPNS and EMP, into one result set and display that set in your session. The tables have a common column called EMPID. Which of the following choices correctly displays the WHERE clause you would use if you wanted to see the data in table EMP where the values in column EMPID = 39284, but only where there is a corresponding value in table EXPNS?

WHERE EMP.EMPID = 39284 AND EMP.EMPID = EXPNS.EMPID;

\q

exit mysql

A RIGHT JOIN uses ANSI 1989 SQL syntax?

false

A natural key is a single column generated by a sequence value.

false

Both MySQL and Oracle support externally managed tables.

false

In MySQL, the REPLACE INTO statement accomplishes a merge using a natural key comparison.

false

In MySQL, the preferred process for bulk loads is LOAD DATA LOCAL INFILE because bandwidth on network connections is virtually unlimited.

false

In Oracle, the MERGE statement lets you INSERT new records in a table and UPDATE old records when you embed a query that uses an RIGHT JOIN based on the primary key (left operand) to foreign key (right operand) values.

false

MySQL supports external table definitions.

false

MySQL uses \n to terminate lines of a CSV file on the Windows platform.

false

Oracle external tables use sequences.

false

The MERGE statement doesn't differ from the INSERT ALL statement.

false

\s

get status info from server

What is the DDL statement that lets you modify a table, index, or object in your database

alter

\C

charset for processing binlog with multi-byte charsets.

\c

clear

=ALL

comparison operator

=ANY

comparison operator

=SOME

comparison operator

\r

connect

What type of join supports a range non-equijoin

cross

\d

delimiter

What is the DML statement that lets you put data into a table in your database

insert

A LEFT JOIN is also an equijoin between which of the following?

- A column in one copy of a table with a column in another table - A column in one table with a column in another table - A set of columns in one table with a set of columns in another - A set of columns in one copy of a table with a set of columns in another copy of the same table

The REPLACE INTO statement in MySQL uses an ON subclause.

- False

Which of the following result in matching values to glue one set of columns to another set of columns in one or more rows?

- Natural Join - An inner Join - A Join

The result set of a FULL JOIN is which of the following?

- The Left relative compliment of the join between two tables - the intersection between the two sets - The symmetric difference of the join between two tables - the right relative complement of the join between two tables.

A RIGHT JOIN can use which of the following subclauses when the columns being joined have different column name

- The ON subclause

Any transaction can be undone before the second phase of a 2PC process to which of the following points?

- The beginning of the current session - Only the last bookmark set in the current session - the last point where data was made permanent - Any bookmark set in a current session

The result set of a RIGHT JOIN contains which of the following?

- The left relative compliment of the join between two

In MySQL, all import target tables should have which type of key(s)?

- Unique Key - Primary Key

Files that import sources feed corrections and additions into data repositories, and they are known by which of the following names?

- batch import files - flat import files - loader import files

Which of the following are valid subqueries?

- single row - correlated - multiple row - scalar

DDL

...

Union

...

What is the minimum number of virtual directories you must create to use external tables?

1

SELECT ADD_MONTHS ('28-APR-97', 120) FROM DUAL;

28-APR-07

You want to join data from four tables into one result set and display that set in your session. Table A has a column in common with table B, table B with table C, and table C with table D. You want to further restrict data returned from the tables by only returning data where values in the common column shared by A and B equals 5. How many conditions should you have in the WHERE clause of your SELECT statement?

4

INSERT INTO INVENTORY (UPC_CODE, PRODUCT) VALUES (503949353, 'HAZELNUT COFFEE'); INSERT INTO INVENTORY (UPC_CODE, PRODUCT) VALUES (593923506, 'SKIM MILK'); INSERT INTO INVENTORY (UPC_CODE, PRODUCT) VALUES (402392340, 'CANDY BAR); SAVEPOINT INV1; UPDATE INVENTORY SET UPC_CODE = 50393950 WHERE UPC_CODE = 402392340; UPDATE INVENTORY SET UPC_CODE = 4104930504 WHERE UPC_CODE = 402392340; COMMIT; UPDATE INVENTORY SET PRODUCT = (SELECT PRODUCT FROM INVENTORY WHERE UPC_CODE = 50393950) WHERE UPC_CODE = 593923506; ROLLBACK;

4104930504

Which of the following operators acts like an OR logic statement?

=SOME, IN, =ANY

You are testing several SQL statements for accuracy and usefulness. A SQL statement will result in a Cartesian product as the result of which of the following items?

A join statement without a WHERE clause

Each of the following statements is true about referential integrity, except one. Which is it?

All values in the referenced column in the parent table must be present in the referencing column in the child.

What is the result set when you subtract a CROSS JOIN from an INNER JOIN?

An empty set

What key word belongs in the blank of the following ALTER statement for a MySQL database?

Change

Virtual directories only store which kind of data?

Data that maps the virtual directory name to a physical directory

Which permission is necessary in MySQL to LOAD DATA INFILE?

Global file permission

You are entering several dozen rows of data into the BANK_ACCT table. Which of the following statements will enable you to execute the same statement again and again, entering different values for variables at statement runtime?

INSERT INTO BANK_ACCT (ACCTNO, NAME) VALUES (&VAR1, '&VAR2');

You are processing some data changes in your SQL*Plus session as part of one transaction. Which of the following choices does not typically indicate the end of a transaction?

Issuing an UPDATE statement

You need to search for text data in column, but you only remember part of the string. Which of the following SQL operations allow the use of wildcard comparisons?

LIKE

Oracle uses which command to accomplish the insertion of new data and updating of existing data to relational tables during a bulk import of a physical file.

MERGE

Which clause from the following code ensures that duplicate rows are never inserted into the import table? (multiple answers possible) LOAD DATA INFILE 'c:/Data/mysql/kingdom_mysql_import.csv' REPLACE INTO TABLE kingdom_knight_import FIELDS TERMINATED BY ', ' ENCLOSED BY ' " ' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';

REPLACE INTO TABLE clause

What maps the root node of an XML structure.

ROW IDENTIFIED BY clause

SELECT item_title, COUNT(item_title) FROM item; The statement returns how many rows?

Returns one row for each distinct not null item tile

You have a table called TEST_SCORE that stores test results by student personal ID number, test location, and date the test was taken. Tests given in various locations throughout the country are stored in this table. A student is not allowed to take a test for 30 days after failing it the first time, and there is a check in the application preventing the student from taking a test twice in 30 days at the same location. Recently, it has come to everyone's attention that students are able to circumvent the 30-day rule by taking a test in a different location. Which of the following SQL statements would be useful for identifying the students who have done so?

SELECT A.STUDENT_ID, A.LOCATION, B.LOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE STUDENT A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION <> B.LOCATION AND TRUNC(A.TEST_DATE)+30 <= TRUNC(B.TEST_DATE) AND TRUNC(A.TEST_DATE)-30 >= TRUNC(B.TEST_DATE);

You are formulating a SQL statement to retrieve data from Oracle. Which of the following SQL statements are invalid?

SELECT NAME, JERSEY_NO WHERE JERSEY_NO = 6;

Natural Join

SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with same name of associate tables will appear once only.

Inner Join

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

Left Join

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

Union all

The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Cardinality

The numbers of elements in a set

SELECT 'Smith' last_name , ''middle_name , 'Joseph' first_name FROM dual UNION ALL SELECT 'Smith' last_name , 'F' middle_name , 'Joseph' first_name FROM dual;

The statement fabricates a table with three columns and two rows.

SELECT c.last_name FROM contact c WHERE c.contact_id =ALL (SELECT a.contact_id FROM address a);

The statement fails because the CONTACT.CONTACT_ID value cannot match a list of different number values returned by the subquery.

SELECT first_name || DECODE(middle_name,NULL,' ',' '||middle_name||' ') || last_name FROM contact;

The statement queries the CONTACT table and concatenates all FIRST_NAME, a single white space, MIDDLE_NAME, white space, and LAST_NAME column values when the MIDDLE_NAME value is NOT NULL; and all FIRST_NAME, a single white space, and LAST_NAME column values when the MIDDLE_NAME value is NULL.

SELECT c.last_name , a.city FROM contact c FULL JOIN address a ON c.contact_id = a.contact_id;

The statement queries the CONTACT.LAST_NAME column value and ADDRESS.CITY column value from a FULL OUTTER JOIN based on the intersection between CONTACT.CONTACT_ID and ADDRESS.CONTACT_ID column values; all rows in the CONTACT table that are not found in the intersection between CONTACT.CONTACT_ID and ADDRESS.CONTACT_ID column values, they return a value for the CONTACT.LAST_NAME column value only when it is not a null value; and all rows in the ADDRESS table that are not found in the intersection between CONTACT.CONTACT_ID and ADDRESS.CONTACT_ID column values, they return a value for the ADDRESS.CITY column value only when it is not a null value.

SELECT c.last_name FROM contact c WHERE c.contact_id = (SELECT a.contact_id FROM address a WHERE c.contact_id = a.contact_id);

The statement queries the CONTACT.LAST_NAME column value for the intersection between CONTACT.CONTACT_ID and ADDRESS.CONTACT_ID column values because the inner subquery is joined to the containing query.

UPDATE address a SET a.city = (SELECT CASE WHEN c.last_name LIKE 'V%' THEN 'Santa Clara' END FROM contact c WHERE c.contact_id = a.contact_id) WHERE EXISTS (SELECT null FROM contact c WHERE c.contact_id = a.contact_id AND c.last_name LIKE 'V%');

The statement updates the ADDRESS.CITY column value with a string literal 'Santa Clara', provided that the CONTACT.CONTACT_ID and ADDRESS.CONTACT_ID column values match and the CONTACT.LAST_NAME column value for the same row starts with a capital 'V'.

Which of the following uses does NOT describe an appropriate use of the HAVING clause?

To put returned data into sorted order

A self referencing join requires two different columns to join different rows in the same table.

True

\t

notee

In an expense application, you are searching for employee information in the EMPLOYEE table corresponding to an invoice number you have. The INVOICE table contains EMPID, the primary key for EMPLOYEE. Which of the following options is appropriate for obtaining data from EMPLOYEE using your invoice number?

select * from EMPLOYEE where empid = (select empid from invoice where invoice_no = 4399485);

\g

send command to mysql server

\G

send command to mysql server, display results vertically

.nextval

sequence

\.

source run code

The REPLACE INTO statement in MySQL sorts the target data set, determines whether a matching row exists, and inserts source rows when they're new.

true

The import source in MySQL is an internally managed table that mirrors the import source file's structure.

true

What is the DML statement that lets you change data into a table in your database

update