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