Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Oracle 11g SQL > Final Review > Flashcards

Final Review Flashcards

1
Q

To display the name of each customer and the title of each book purchased by the customers would require how many join conditions?

4
3
2
1

A

3

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

NATURAL JOIN
JOIN…ON
JOIN…USING
none of the above

A

JOIN…ON

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

SELECT * FROM orders CROSS JOIN customers;

SELECT * FROM orders, customers
WHERE orders.customer# = customers.customer#;

SELECT * FROM orders, customers
WHERE orders.customer# > customers.customer#;

SELECT * FROM o orders , c customers
WHERE o.customer# = c.customer#;

A

SELECT * FROM orders CROSS JOIN customers;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

In Oracle11g, tables can be linked through which clause(s)?

SELECT
FROM
WHERE
both b and c

A

both b and c

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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?

SELECT title FROM promotion
WHERE retail BETWEEN minretail AND maxretail;

SELECT title FROM promotion NATURAL JOIN books
WHERE retail BETWEEN minretail AND maxretail;

SELECT title FROM promotion JOIN books
USING retail BETWEEN minretail AND maxretail;

none of the above

A

none of the above

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

A(n) ____ is used to combine the results of two queries.

concatenation operator
set operator
comparison operator
logical operator

A

set operator

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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 NATURAL JOIN orders
WHERE orders.customer# IS NOT NULL;

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

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

SELECT lastname, firstname, order#
FROM customers NATURAL JOIN orders
WHERE orders.customer# IS NULL;

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which of the following SQL statements will display the title of the books ordered by customer# 1003?

SELECT title
FROM customers NATURAL JOIN books
WHERE customer# = 1003;

SELECT title
FROM customers JOIN orders USING (customer#)
JOIN orderitems USING (order#) JOIN books USING (isbn)
WHERE customer# = 1003;

SELECT title
FROM customers JOIN orders ON (customer#)
JOIN orderitems ON (order#) JOIN books ON (isbn)
WHERE customer# = 1003;

both a and b

A

SELECT title
FROM customers JOIN orders USING (customer#)
JOIN orderitems USING (order#) JOIN books USING (isbn)
WHERE customer# = 1003;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which of the following set operators can be used to make certain that only the rows returned by both queries are displayed in the results?

UNION
UNION ALL
INTERSECT
MINUS

A

INTERSECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Which of the following set operators will display the results of the combined SQL statements without suppressing duplicate rows?

UNION
UNION ALL
INTERSECT
MINUS

A

UNION ALL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Which of the following SQL statements will display books that have not been ordered recently?

SELECT title
FROM books, orderitems
WHERE books.isbn orderitems.isbn;

SELECT title
FROM books NATURAL JOIN orderitems
WHERE JOIN IS NULL;

SELECT title FROM books NATURAL JOIN orderitems
MINUS
SELECT title FROM books;

SELECT title FROM books
MINUS
SELECT title FROM books NATURAL JOIN orderitems;

A

SELECT title FROM books
MINUS
SELECT title FROM books NATURAL JOIN orderitems;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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
WHERE orderdate = ‘12-APR-03’;

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

SELECT lastname, firstname
FROM customers JOIN orders ON (customer#)
WHERE orderdate = ‘12-APR-03’;

both a and b

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Which of the following queries will display the first and last name of the individual who referred another customer, along with the customer# of the referred customer?

SELECT r.firstname, r.lastname, c.customer#
FROM customers r, customers c
WHERE r.customer# = c.referred;

SELECT r.firstname, r.lastname, c.customer#
FROM customers r JOIN customers c
ON r.customer# = c.referred;

SELECT r.firstname, r.lastname, c.customer#
FROM customers r NATURAL JOIN customers c;

both a and b

A

both a and b

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Which of the following keywords is used to create a Cartesian join?

OUTER JOIN
CROSS JOIN
NATURAL JOIN
JOIN…USING

A

CROSS JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

FULL OUTER JOIN
JOIN…ON
NATURAL JOIN
CROSS JOIN

A

NATURAL JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

SELECT title, pubdate, name
FROM publisher JOIN books ON (pubid)
WHERE category = ‘BUSINESS’;

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

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Which of the following is an example of assigning “o” as a table alias for the ORDERS table in the FROM clause?

FROM o orders, c customers
FROM o.orders, c.customers
FROM orders o, customers c
FROM orders.o, customers.c

A

FROM orders o, customers c

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

SELECT * FROM orders, customers;

SELECT lastname, firstname, order#
FROM orders NATURAL JOIN customers;

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

all of the above

A

all of the above

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

Cartesian join
equality join
self-join
outer join

A

self-join

20
Q

If you are attempting to join two tables that have multiple common columns, which of the following JOIN keywords should be used to specify how the tables should be linked?

OUTER JOIN
CROSS JOIN
JOIN…USING
none of the above

A

JOIN…USING

21
Q

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) ____.

inner view
outer view
inline view
natural view

A

inline view

22
Q

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

IN
ANY
ALL
all of the above

A

all of the above

23
Q

The following SQL statement contains which type of subquery?
SELECT title, retail, (SELECT AVG(retail) FROM books) FROM books;

single-row
multiple-row
multiple-column
inline view

A

single-row

24
Q

The

A

less than the lowest

25
Q

A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.

GROUP BY
HAVING
ORDER BY
WHERE

A

ORDER BY

26
Q

Which of the following can be used in a WHERE clause?

single-row subquery
multiple-column subquery
multiple-row subquery
all of the above

A

all of the above

27
Q

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

ALL
>ANY

A

> ALL

28
Q

The operators =, , =, and are referred to as ____ operators.

multiple-row
single-row
correlated
multiple column

A

single-row

29
Q

Based on the contents of the BOOKS table, which of the following SQL statements will return an error message?

SELECT title FROM books WHERE retail =
(SELECT MAX(retail) FROM books);

SELECT title FROM books WHERE retail IN
(SELECT MAX(retail) FROM books);

SELECT title FROM books WHERE retail >
(SELECT MAX(retail) FROM books);

none of the above

A

none of the above

30
Q

When a multiple-column subquery is included in the WHERE or HAVING clause of the outer query, which operator is used by the outer query to evaluate the results of the subquery?

> BETWEEN
IN
none of the above

A

IN

31
Q

Based upon the contents of the BOOKS table, which of the following will display the retail price of the most expensive book provided by publisher 3?

SELECT MAX(retail)
FROM books
GROUP BY pubid;

SELECT MAXIMUM(retail)
FROM books
WHERE pubid = 3;
SELECT MAX(retail)
FROM books
WHERE pubid = 3;
SELECT MAXIMUM(retail)
FROM books
HAVING pubid = 3;
A 
SELECT MAX(retail)
FROM books
WHERE pubid = 3;
32
Q

The default keyword for group functions is ____.

ALL
DISTINCT
INCLUDENULLS
NONULLS

A

ALL

33
Q

Based on the contents of the BOOKS table, which of the following SQL statements will return the total profit generated by books provided by publisher 4?

SELECT TOTAL(retail-cost) FROM books
WHERE pubid = 4;
SELECT TOTAL(retail-cost) FROM books
GROUP BY pubid;
SELECT SUM(retail-cost) FROM books
WHERE pubid = 4;
SELECT SUM(retail-cost) FROM books
GROUP BY pubid = 4;
A 
SELECT SUM(retail-cost) FROM books
WHERE pubid = 4;
34
Q

Based upon the contents of the BOOKS tables, which of the following SQL statements will return the number of different publishers represented in the table?

SELECT COUNT(DISTINCT pubid) FROM books;
SELECT DISTINCT COUNT(pubid) FROM books;
SELECT DISTINCT (COUNT(pubid)) FROM books;
SELECT (DISTINCT COUNT(pubid)) FROM books;

A

SELECT COUNT(DISTINCT pubid) FROM books;

35
Q

If the DISTINCT keyword is not included in the STDDEV function, the ____ keyword will be assumed.

UNIQUE
ALL
NONULLS
none of the above

A

ALL

36
Q

Which of the following is a valid SQL statement?

SELECT SUM(quantity*retail) "Total Sales"
FROM orders JOIN orderitems ON orders.order# = orderitems.order#
JOIN books ON orderitems.ISBN = books.ISBN
WHERE orderdate = '02-APR-03';
SELECT SUM(quantity*retail) "Total Sales"
FROM orders JOIN orderitems ON orders.order# = orderitems.order#
JOIN books ON orderitems ON orderitems.ISBN = books.ISBN
HAVING orderdate = '02-APR-03';

SELECT customer#, SUM(quantity*retail) “Total Sales”
FROM orders JOIN orderitems ON orders.order# = orderitems.order#
JOIN books ON orderitems ON orderitems.ISBN = books.ISBN
HAVING orderdate = ‘02-APR-03’;

SELECT customer#, SUM(quantity*retail) “Total Sales”
FROM orders JOIN orderitems ON orders.order# = orderitems.order#
JOIN books ON orderitems ON orderitems.ISBN = books.ISBN
HAVING orderdate = ‘02-APR-03’
GROUP BY customer#;

A 
SELECT SUM(quantity*retail) "Total Sales"
FROM orders JOIN orderitems ON orders.order# = orderitems.order#
JOIN books ON orderitems.ISBN = books.ISBN
WHERE orderdate = '02-APR-03';
37
Q

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

SELECT shipstate, COUNT(*)
FROM orders;

SELECT shipstate, COUNT(customer#)
FROM orders;

SELECT shipstate, COUNT()
FROM orders
HAVING COUNT() >0;

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

A

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

38
Q

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 order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;

SELECT order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL
GROUP BY order#;

SELECT COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;

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

A

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

39
Q

Functions that return one result per group of rows are called ____ functions.

group
multiple-row
aggregate
all of the above

A

all of the above

40
Q

The SUM function can only be used with ____ data.

numeric
character
date
alphanumeric

A

numeric

41
Q

Which of the following cannot be used with date columns?

MIN
MAX
AVG
all of the above

A

AVG

42
Q

If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.

HAVING
GROUP BY
WHERE
SELECT

A

HAVING

43
Q

The AVG function can be used with ____ values.

numeric
character
date
all of the above

A

numeric

44
Q

Which of the following can be used with date columns?

MIN
MAX
COUNT
all of the above

A

all of the above

45
Q

Which of the following is a valid statement?

A single-row function can be nested inside a group function.
Group functions can be nested inside other group functions.
Group functions can be nested inside single-row functions.
all of the above

A

all of the above

46
Q

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;

SELECT title
FROM books
WHERE pubdate = MIN(pubdate);

SELECT title
FROM books
WHERE pubdate = MINIMUM(pubdate);

SELECT MINIMUM(pubdate)
FROM books;
A 
SELECT MIN(pubdate)
FROM books;

Oracle 11g SQL (10 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions