ISM Final Exam Chapters 8-13

Which of the following format elements will display insignificant leading zeros?

0009999

A table alias can consist of a maximum of ____ characters.

30

Which operator will instruct Oracle12c to list all records with a value that is more than the highest value returned by the subquery?

>ALL

If the DISTINCT keyword is not included in the AVG function, the keyword will be assumed

ALL

The ___ function calculates the average of the numeric values in a specified column.

AVG

Which of the following statements about complex views is incorrect?

All DML operations can be performed on complex views, just like simple views

Which of the following format argument elements indicates that the name of the day of the week for the specified date should be displayed?

DAY

DML operations are not allowed on a view that is created with the ____ keyword.

DISTINCT

With a MERGE statement, a series of ___ actions can occur with a single SQL statement

DML

Which command will delete a view?

DROP VIEW

The ___ operator is used to determine whether a condition is present in a subquery

EXISTS

If you want to create a view based upon a table or tables that do not yet exist, or are currently unavailable (e.g., off-line), what keyword can you use to avoid receiving an error message?

FORCE

The ___ clause is used to indicate that groups should be created.

GROUP BY

Which of the following can only be used to link tables that have a common column?

NATURAL JOIN

When sorting the results in ascending order, which of the following values will be presented last in the output?

NULL

Which of the following functions can be used to substitute another value for a NULL value during calculations?

NVL

Which of the following clauses is used to indicate a particular sort sequence for presenting query results?

ORDER BY

Which of the following can be used to replace a specific set of characters with another set of characters?

REPLACE

Contents of BOOKS table Based upon the contents of the BOOKS table in the accompanying figures, which of the following queries will return all books that cost at least $25.00?

SELECT * FROM books WHERE cost >= 25.00;

Contents of the BOOKS table Based upon the contents of the BOOKS table in the accompanying figures, which of the following SQL statements will retrieve all books published by the publisher assigned Pubid 1?

SELECT * FROM books WHERE pubid=1;

Contents of BOOKS table Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will list all books stored in the BOOKS table with a retail price of more than $38 sorted by retail price?

SELECT * FROM books WHERE retail > 38 ORDER BY retail;

Which SQL statement will return the same results as the following SQL statement? SELECT * FROM orders, customers;

SELECT * FROM orders CROSS JOIN customers;

Contents of the ORDERS table Based upon the contents of the ORDERS table in the accompanying figure, which of the following SQL statements will display all orders contained in the ORDERS table that have been shipped to the customers?

SELECT * FROM orders WHERE shipdate IS NOT NULL;

Contents of the ORDERS table Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will return all orders shipped within three days after the order was received?

SELECT * FROM orders WHERE shipdate-orderdate <= 3;

Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?

SELECT COUNT(*) FROM ORDERS WHERE shipdate IS NULL;

Based upon the contents of the BOOKS tables, which of the following will determine the number of books in the computer category?

SELECT COUNT(category) FROM books WHERE category = 'COMPUTER';

Based on the contents of the BOOKS table, which of the following will display the date of the book with the earliest publication date?

SELECT MIN(pubdate) FROM books;

Based on the contents of the BOOKS table, which of the following SQL statements will return the total profit generated by the books in the COOKING category?

SELECT SUM(retail-cost) FROM books WHERE category = 'COOKING';

Which of the following SQL statements will display all customers who have not recently placed an order?

SELECT customer# FROM customers MINUS SELECT customer# FROM orders;

Which of the following statements about views is incorrect?

Views are database objects that actually store data

Which of the following statements is incorrect?

Views can be modified by using the ALTER VIEW...MODIFY command

Which constraint ensures that the data in a view cannot be changed?

WITH READ ONLY

When functions are nested, the ___ function is solved last

inner

What is the definition of an inline view?

it is a temporary data source that exists only while a command is being executed

When the WHERE clause contains multiple types of operators, which of the following is resolved last?

logical operators

The type of view that actually replicates data is called a(n) ____ view.

materialized

Contents of the ORDERS table Based upon the contents of the ORDERS table in the accompanying figure, which of the following SELECT statements will retrieve all orders contained in the ORDERS table that have not yet been shipped to the customer?

none of the above

The AVG function can be used with ___ values

numeric

The SUM function can only be used with ___ data

numeric

When sorting the results in ascending order, which of the following values will be presented first in the output?

numeric

The TO_CHAR function can be used to format what types of data?

numeric and date

If a view was created with the WITH READ ONLY constraint, to remove the constraint you will need to ____.

re-create the view without the option

If there is an exact match between the data used for a primary sort, a ___ can be used to determine the sequence used to display the data

secondary sort

The process of retrieving only certain rows based upon a specified condition is known as

selection

Which of the following type of joins refers to joining a table to itself?

self-join

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

Which type of view is created with the following command? CREATE VIEW outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH CHECK OPTION;

simple

Which of the following types of views cannot include an arithmetic expression?

simple view

The <= operator is referred to as a(n) ___ operator

single-row

The <> operator is referred to as a(n) ___ operator

single-row

The > operator is referred to as a ___ operator

single-row

A complete query nested inside another query is called a(n) ____

subquery

The outer query receives its input from the ___

subquery

What is the maximum number of columns or character strings that can be combined through a single CONCAT function

two

A ___ stores a query and is used to access data in the underlying tables

view

Which of the following functions is used to determine the number of months between two date values?

MONTHS_BETWEEN

Which clause is used when the group results of a subquery need to be restricted, based on some condition?

HAVING

Which of the following format elements will display 1:00 p.m. as 13?

HH24

Which comparison operator allows you to search for NULL values in a subquery?

IS NULL

Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column?

JOIN...ON

The ___ function returns the smallest value in a specified column.

MIN

Based on the contents of the CUSTOMERS table, which of the following SQL statements will display the customer# of all customers who were referred by the same individual that referred customer# 1003?

SELECT customer# FROM customers WHERE NVL(referred, 0) = (SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);

Based on the contents of the CUSTOMERS table, which SQL statement will display the customers residing in the same state as customer#1013?

SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE customer#=1013);

Based on the contents of the ORDERS table, which of the following SELECT statements will determine the number of orders placed by each customer?

SELECT customer#, COUNT(customer#) FROM orders GROUP BY customer#;

Which of the following SQL statements will return the names of all customers who placed an order on April 12, 2003?

SELECT lastname, firstname FROM customers NATURAL JOIN orders WHERE orderdate = '12-APR-03';

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? SECOND ONE

SELECT lastname, firstname, order# FROM customers, orders WHERE orders.customer# (+) = customers.customer#;

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#;

Which of the following SQL statements will display the publisher name, book title, and retail price of all books that cost more than $35.95?

SELECT name, title, retailFROM books NATURAL JOIN publisherWHERE cost > 35.95;

Based upon the contents of the ORDERS table, which of the following SQL statements will display only those orders shipped to the zip code zone that begins with 323?

SELECT order# FROM orders WHERE SUBSTR(shipzip, 1, 3) = 323;

Based upon the contents of the ORDERS table, which of the following SQL statements will display the ship date for order 1006 as April 2, 2002?

SELECT order#, TO_CHAR(shipdate, 'Month DD, YYYY') FROM orders;

Which of the following SQL statements will display the number of days between the date an order was placed and the date it was shipped?

SELECT order#, TO_CHAR(shipdate-orderdate, '99') FROM orders WHERE shipdate IS NOT NULL;

Contents of the ORDERS table Based upon the contents of the ORDERS table in the accompanying figure, which of the following SQL statements will display how long it took to ship order # 1007 (based upon when the order was originally placed)?

SELECT order#, shipdate-orderdate FROM orders WHERE order# = 1007;

Based upon the contents of the ORDERS table, which of the following will display how many orders were shipped to each state?

SELECT shipdate, COUNT(*) FROM orders GROUP BY shipdate;

Contents of the BOOKS table ​ 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 SQL statements will display the title of each book in the BOOKS table and the name of its publisher?

SELECT title, name FROM publisher NATURAL JOIN books;

Which of the following will display the title, publication date, and publisher name of each book in the BUSINESS category?

SELECT title, pubdate, name FROM publisher JOIN books USING (pubid) WHERE category = 'BUSINESS';

The phonetic representation of a character string can be determined using the ___ function

SOUNDEX

Which of the following format argument elements will display the number of seconds past midnight?

SSSS

The ___ function calculates the standard deviation for a specific set of data.

STDDEV

Which of the following functions will truncate a numeric value to a specific position?

TRUNC

A join based upon a column from each table containing equivalent data is known as an

equality join

Which of the following queries will display data from both the ORDERS and CUSTOMERS tables?

all of the above

Which of the following operators is used with a multiple-row subquery?

all of the above (IN, ANY, ALL)

The MAX function can be used with which type of columns?

all of the above (numeric, date, character)

The MIN function can only be used with ___ columns

all of the above (numeric, date, character)

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 (primary key, with check option, unique)

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

A subquery must include a(n) ___ clause

both a and b (SELECT and FROM)

A ___ is used to indicate how data should relate to a given search condition

comparison operator

Which of the following terms refers to a type of subquery that is processed, or executed, once for each row in the outer query?

correlated subquery