SQL
A string literal must be enclosed in ____.
""
The default keyword for group functions is ____.
ALL
Which of the following commands will drop any columns marked as unused from the TABLEA table?
ALTER TABLE tablea DROP UNUSED COLUMNS;
Which of the following keywords is used to mark a column for deletion at a later time?
ALTER TABLE...SET UNUSED
To instruct Oracle12c to sort data in ascending order, enter ____ after the column name in the ORDER BY clause
ASC
Which of the following is a correct statement
An asterisk can be used as the argument for the COUNT function to include NULL values in the results.
Explain the difference between an implicit and an explicit COMMIT.
An explicit COMMIT occurs when you enter a COMMIT statement, an implicit COMMIT occurs when you exit client tools.
Explain the similarities and differences between the UNIQUE and PRIMARY KEY constraints.
Both validate the uniquness of a value, but the UNIQUE constraint allows NULL values.
Which of the following symbols can be used in a column name?
#
Which of the following symbols cannot be used in a table name?
%
How is a "TOP-N" analysis performed?
A TOP-N analysis is performed by determining if the rows ROWNUM value is less or equal to N.
Explain the difference between a WHERE and a HAVING clause.
A WHERE clause is used for filtering rows, and a HAVING clause is used to filter groups.
What is the purpose of an E-R Model?
A logical representation of the data for an organization or for a business area, using entities for categories of data and relationships for associations between entities
How does a materialized view differ from the other types of views?
A materialized view replicates data
What special consideration needs to be given to search conditions when they are being compared to character or date columns?
A primary sort is the first column a table is ordered by.and a secondary sort is a second field to sort a table by.
What is the difference between a shared and exclusive lock?
A shared lock allows other users to view information on a table but not alter it. An exlusive lock is put on a table so no other user can alter or update the table's contents.
How do the following three types of views differ in terms of the data they can contain: simple view, complex view and inline view?
A simple view references only one table and doesn't include a group function. A complex view can retrieve data from more than one table and can contain group functions. An inline view exists only while the command is being executed.
Which of the following is a valid SQL statement
CREATE TABLE newname (colA DATE, colB VARCHAR2(3);
Which of the following keywords is used to create a Cartesian join?
CROSS JOIN
Which of the following rules does not apply to column names in Oracle 12c?
Column names can contain an ampersand (&).
Which of the following keywords is used to remove a database table in Oracle 12c?
DROP TABLE
Which command will delete a view
DROP VIEW
The ____ function is used to calculate the total amount stored in a numeric field.
SUM
What is the difference between dropping a column with the DROP COLUMN clause and setting a column as unused with the SET UNUSED clause?
Setting it as unused will allow you to mark the column to be deleted at a later time.
What is the difference between how a NOT NULL constraint is added to an existing column and how other types of constraints are added?
The NOT NULL constraint can only be added to an existing column using the MODIFY clause of the ALTER TABLE command, while other types of constraints can be added using the ADD clause of the ALTER TABLE command.
What is the difference between a standard SELECT statement and a SELECT...FOR UPDATE statement?
The SELECT .. FOR UPDATE command places a shared lock on records to be changed.
Which of the following statements about the FOREIGN KEY constraint is incorrect?
The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
A CHAR column cannot be resized to a width that is smaller than the data it already contains.
True
A FOREIGN KEY constraint can be added to the column of a table to ensure that the referenced data value actually exists in the other table
True
Concatenation refers to combining the results of several SELECT statements into one result
True?
Data mining refers to analyzing historical data stored in a database.
True?
Explain the purpose of a set operator.
UNION = Returns the results of both queries and removes duplicates UNION ALL = Returns the results of both queries and includes duplicates INTERSECT = Returns only the rows included in the results of both queries
Describe how the COUNT function processes NULL values when the function is executed.
Use an asterisk, so when an asterisk is encountered, the entire record is counted, so a NULL in one column doesn't cause the row to be ignored.
When does a COMMIT command implicitly occur
When the user issues a DDL command such as CREATE or ALTER TABLE.
A DBMS includes which of the following capabilities?
all of the above
A user can perform a DML operation (add, modify, delete) on a simple view if it does not violate which type of existing constraint on the underlying base table?
all of the above
Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all books published in 2001?
all of the above
Functions that return one result per group of rows are called ____ functions.
all of the above
Which of the following is a valid statement?
all of the above
Which of the following is displayed by the DESCRIBE command?
all of the above
Which of the following is not a step in the Systems Development Life Cycle (SDLC)?
all of the above
The outer join operator in the WHERE clause cannot be used with which of the following operators
both a and b
Which of the following queries will return the same results as the following SQL statement? SELECT c.customer#, lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer#;
both a and b
Which of the following SQL statements will list the name of each customer stored in the customers table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?bith
both a and c
A(n) ____ is used to indicate how data should relate to a given search condition.
comparison operator
A view based on the contents of one table that uses an expression for one of the columns is considered a(n) ____ view.
complex
Combining the contents of two or more columns is known as ____.
concatenation
Commands used to modify data are called ____ commands
data manipulation language (DML)
In an E-R Model a person, place, or thing with characteristics to be stored in the database are referred to as?
entity
A(n) ____ lock will prevent any DDL operations from being performed on the locked table
exclusive
A COMMIT is explicitly issued when the user exits SQL*Plus. _________________________
false
A FOREIGN KEY constraint will not allow a row containing a NULL value in the foreign key column to be added to the table.
false
A NULL value is the same as a blank space.
false
A column alias that has been defined in the SELECT clause of a SELECT statement cannot be referenced in an ORDER BY clause
false
A complex view cannot contain any group functions
false
A correlated subquery is one in which the inner query is executed first, and then the outer query is executed
false
A group function cannot be included in the SELECT clause of a single-row subquery
false
A multiple-column subquery cannot be nested in a WHERE clause
false
A shared lock prevents another user from performing DDL or DML operations on the table
false
Comparison operators are used to combine search conditions
false
DML commands are used to create or modify database tables.
false
Data manipulation language commands are used to create or modify database tables.
false
Group functions return a group of results per row processed
false
TOP-N" analysis can be used to find the highest values in a column by sorting the data in ascending order
false
The DELETE TABLE command can be used to delete all the data stored in a table and release the storage space, yet retain the structure of the table. _________________________
false
The JOIN keyword is used in the WHERE clause to indicate the tables that should be joined or linked.
false
The data being inserted into a table are listed in the ADD clause of the INSERT command.
false
The only group function that includes NULL values by default is the MIN function
false
The results of the outer query are passed to the inner query.
false
To find rows containing a NULL value in a specified column, you must use the search condition of = NULL.
false
When a SELECT statement includes WHERE, HAVING, and GROUP BY clauses, the GROUP BY clause is always evaluated first
false
When combining the results of two SELECT statements with the UNION keyword, duplicate rows are suppressed in the results
false
A single-row subquery can return several columns, but only one row, of results to the outer query.
false
Which of the following does not contain repeating groups, but has a primary key and possibly partial dependencies?
firt normal form
A temporary table that is created when a multiple-column subquery is used in the FROM clause of an outer query is called a(n) ____.
inline view
When the WHERE clause contains multiple types of operators, which of the following is resolved last?
logical operators
A foreign key is usually found on which side of a relationship?
many
Which of the following is the standard abbreviation for the constraint NOT NULL?
nn
Based on the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT isbn, title 2 FROM books 3 WHERE pubid = 4 (SELECT pubid 5 FROM books 6 WHERE title = 'SHORTEST POEMS') 7 AND retail-cost > 8 (SELECT AVG(retail-cost) 9 FROM books);
none of the above
Based on the contents of the BOOKS table, which of the following SQL statements will display the title of all books published by the publisher of SHORTEST POEMS?
none of the above
Based on the contents of the BOOKS table, which of the following SQL statements will return an error message?
none of the above
Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled THE WOK WAY TO COOK?
none of the above
The results of a subquery are passed back as input to the ____ query.
outer
Data in first normal form (1NF) does not contain which of the following?
repeating groups
A(n) ____ is used to combine the results of two queries.
set operator
Which type of view is created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY;
simple
The following SQL statement contains what type of subqueries? SELECT isbn, title FROM books WHERE pubid = (SELECT pubid FROM books WHERE title = 'SHORTEST POEMS') AND retail-cost > (SELECT AVG(retail-cost) FROM books);
single row
The > operator is referred to as a(n) ____ operator
single-row
A(n) ____ in a SQL command instructs Oracle 12c to use a substituted value in place of the variable at the time the command is actually executed.
substitution variable
In which step of the Systems Development Life Cycle (SDLC) is the solution to the identified problem determined and understood?
systems analysis
In which step of the Systems Development Life Cycle (SDLC) is the system actually used by the end-user on a regular basis?
systems deployment
In which step of the Systems Development Life Cycle (SDLC) are the logical and physical components defined?
systems design
Data mining refers to ____.
the extraction of hidden predictive information from large databases.
If the data has no partial dependencies, repeating groups, or transitive dependencies, and has a composite primary key, the data is in which form?
third normal
A user who is issuing DML commands can save modified data or undo uncommitted changes by issuing ____ statements.
transaction control
A CHECK constraint requires that a data value meet a certain condition before the record is added to the database table.
true
A NULL value can be included in the data being added to a table by explicitly entering the word NULL.
true
A UNIQUE constraint is the same as a PRIMARY KEY constraint, except that it will accept NULL values.
true
A bridging table can be used to eliminate a many-to-many relationship in a relational database.
true
A column name can consist of up to 225 characters.
true
A column represents a field in the physical database table.
true
A deadlock occurs when two users hold shared locks on portions of a table that are needed to complete the transaction of the other user.
true
A group function can be used in a(n) inline view
true
A lock is automatically released when the user issues a transaction control statement such as COMMIT or ROLLBACK.
true
A subquery can be used with the INSERT command to enter data from an existing table into the destination table.
true
A subquery is required when the condition for the outer query is based upon an unknown
true
A table that has been dropped without the PURGE option can be retrieved using the FLASHBACK TABLE command.
true
A view can be created to simplify issuing complex SQL queries.
true
AVG, COUNT, and STDDEV are all considered group functions
true
By default, the SUM function assumes the ALL keyword and the result will include multiple occurrences of numeric values
true
DDL commands are used to create or modify database objects
true
Data in second normal form (2NF) may contain which of the following?
true
Group functions are also known as aggregate functions
true
If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison operator
true
If you don't inform users of the name change it could prevent them from finishing their work or accessing the table
true
The ALL option can be used in the SELECT clause to indicate that all columns should be retrieved.
true
The most common type of join is an equijoin, which joins two or more tables together based upon the tables having equivalent data values in a common column.
true
The only required clauses for a SELECT statement are the WHERE and FROM clauses. _________________________
true
When a self-join is created, each copy of the table must be assigned a table alias.
true
a complex view can retrieve data from more than one table
true
table can be locked in SHARE MODE or EXCLUSIVE MODE. _________________________
true
Which of the following symbols can be used to combine data from different columns into one column of output?
||
Which of the following types of constraints is used to enforce referential integrity?
FOREIGN KEY
A column heading that is longer than the width of a column will not be truncated for a column that is defined to store character data. _________________________
False
Data is in second normal form (2NF) if it contains no repeating groups and has a primary key to uniquely identify each record.
False
The ON clause can be used only if the tables being joined have a common column with the same name.
False
The outer join operator is used to combine the results of multiple SELECT statements
False
A(n) WHERE statement allows a user to retrieve data from a database table. _________________________
From
Which clause is used when the group results of a subquery need to be restricted, based on some condition?
HAVING
Which comparison operator allows you to search for NULL values in a subquery?
IS NULL
What is the problem associated with changing the name of a table?
If you don't inform users of the name change it could prevent them from finishing their work or accessing the table
Which of the following statements about the DELETE command is incorrect?
If you omit the mandatory WHERE clause, an error message will be issued
Explain the different processing methods used by correlated and uncorrelated subqueries
In an uncorrelated subquery, the subquery is exectued first, the results are passed to the outer query, then the outer query is executed. A correlated subquery refrences an outer query, then the exists operator is used to test if a link is present.
Explain how nested group functions are processed
In nested group functions the inner function is resolved first.
What is the purpose of the ORDER BY clause?
It is used to display query results in a sorted order.
Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column
JOIN...USING
Based on the contents of the BOOKS table, which line in the following SQL statement contains an error? 1 SELECT title 2 FROM books 3 WHERE pubid EXISTS IN 4 (SELECT pubid 5 FROM books 6 WHERE retail > 41.95);
Line 3
Explain the difference between multiple-row and multiple-column subqueries.
Multiple-row subqueries return more than row of results using WHERE and HAVING clauses. Multiple-column returns columns to an outer query using FROM, WHERE, or HAVING.
Which of the following can only be used to link tables that have a common column?
NATURAL JOIN
Give the appropriate situation in which to use each of the following join keywords: NATURAL JOIN, JOIN...USING, and JOIN...ON.
NATURAL JOIN = used in the FROM clause to join table containing a common column with the same name and definition JOUN... USING =identifies the common column used to join the tables JOIN ... ON = identifies the column used to join tables
Which of the following is a valid column name?
NEW_COLUMN
Which of the following constraints cannot be added to an existing table with the ADD clause of the ALTER TABLE command
NOT NULL
What is the relationship between a NOT NULL constraint and a CHECK constraint?
NOT NULL is a special CHECK where the condition is not a NULL value.
If it is possible for a subquery to return a NULL value to the outer query for comparison, the ____ function should be used to substitute an actual value for the NULL.
NVL
Which of the following rules apply to table names in Oracle 12c?
Names can contain a number sign (#).
Which of the following keywords must have been included during the creation of a FOREIGN KEY constraint to allow a row from the parent table to be deleted, even if it is referenced by a row in the child table?
ON DELETE CASCADE
A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.
ORDER BY
Which of the following clauses is used to indicate a particular sort sequence for presenting query results?
ORDER BY
Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will return all books that cost at least $25.00?
SELECT * FROM books WHERE cost >= 25.00;
Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all books stored in the BOOKS table with Pubid 1 or 2 or that have a retail price of at least $42.00?
SELECT * FROM books WHERE pubid = 1 OR pubid = 2 OR retail >= 42;
Based upon the contents of the BOOKS table in the accompanying figure, which of the following SQL statements will retrieve all books published by the publisher assigned Pubid 1?
SELECT * FROM books WHERE pubid = 1;
Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will return the category and title of all books stored in the BOOKS table presented in order of their category and, for the books in the same category, sort the title of the books in descending order?
SELECT category, title FROM books ORDER BY 1 ASC, 2 DESC;
Which of the following SQL statements will list the name of each customer stored in the CUSTOMERS table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?
SELECT lastname, firstname, order# FROM customers c LEFT OUTER JOIN orders o ON c.customer# = o.customer#;
Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all book titles that are in the Business or Computer category and have a retail price of more than $35.00?
SELECT title FROM books WHERE (category = 'BUSINESS' OR category ='COMPUTER' ) AND retail >35;
Which of the following keywords can be included in a SELECT statement to suppress duplicate data?
DISTINCT
Which of the following is not considered a group function?
TRUNC
Which of the following is not a valid comparison operator?
=>
Explain the difference between an inner join and an outer join
An inner join gives an intersection of the row in common, an outer join combines all the rows.
he ____ operator is used to determine whether a condition is present in a subquery.
EXISTS
Which of the following datatypes refers to variable-length character data, where n represents the maximum length of the column?
VARCHAR2(n)
Which of the following statements about a PRIMARY KEY is incorrect
Which of the following statements about a PRIMARY KEY is incorrect
A lock arising from a SELECT...FOR UPDATE command will be released when ____.
a COMMIT command is executed
Partial dependency exists if what conditions exist?
a column is dependent only on a portion of a composite