Brainscape's Knowledge GenomeTM

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


See full index

Oracle 11g SQL > Ch. 9 Joining Tables > Flashcards

Ch. 9 Joining Tables Flashcards

1
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
2
Q

Which of the following SQL statements will display the names of all customers who have purchased a copy of E-BUSINESS THE EASY WAY?

SELECT lastname, firstname
FROM customers NATURAL JOIN books
WHERE title LIKE ‘%BUSI%’;

SELECT lastname, firstname
FROM customers c, books b, orders o, orderitems oi
WHERE title LIKE ‘%BUSI%’;

SELECT lastname, firstname
FROM customers c, books b, orders o, orderitems oi
WHERE c.customer# = o.customer# AND o.order# = oi.order# AND
oi.isbn = b.isbn AND title LIKE ‘%BUSI%’;

none of the above

A

SELECT lastname, firstname
FROM customers c, books b, orders o, orderitems oi
WHERE c.customer# = o.customer# AND o.order# = oi.order# AND
oi.isbn = b.isbn AND title LIKE ‘%BUSI%’;

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

Which of the following types of joins is created by matching equivalent values in each table?

Cartesian join

equality join

non-equality join

outer join

A

equality join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
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
5
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
6
Q

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

12

225

255

30

A

30

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
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
9
Q

Which of the following types of joins refers to results consisting of each row from the first table being replicated from every row in the second table?

outer join

non-equality join

self-join

Cartesian join

A

Cartesian join

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

In a Cartesian join, linking a table that contains 10 rows to a table that contains 9 rows will result in ____ rows being displayed in the output.

19

18

90

89

A

90

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
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
12
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
13
Q

The JOIN keyword is included in which of the following clauses?

SELECT

FROM

WHERE

ORDER BY

A

FROM

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

Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled SHORTEST POEMS?

SELECT gift FROM promotion
WHERE gift = minretail;

SELECT gift FROM promotion, books
WHERE retail >= minretail AND title = ‘SHORTEST POEMS’;

SELECT gift FROM promotion, books
WHERE retail BETWEEN minretail AND maxretail
AND title = ‘SHORTEST POEMS’;

none of the above

A

SELECT gift FROM promotion, books
WHERE retail BETWEEN minretail AND maxretail
AND title = ‘SHORTEST POEMS’;

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

Which of the following SQL statements will display the title of all books that have had multiple copies requested in a single order?

SELECT title
FROM books NATURAL JOIN orderitems
WHERE qty > 1;

SELECT title
FROM books JOIN orderitems
WHERE qty > 1;

SELECT title
FROM books JOIN orderitems ON (isbn) JOIN orders ON (order#)
WHERE qty>1;

SELECT title
FROM books JOIN orderitems USING(isbn);

A

SELECT title
FROM books NATURAL JOIN orderitems
WHERE qty > 1;

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

Which of the following SQL statements will display the name of each publisher that publishes a book classified in the COMPUTER category?

SELECT UNIQUE name
FROM books, publisher;

SELECT UNIQUE name
FROM books NATURAL JOIN publisher
WHERE category = ‘COMPUTER’;

SELECT DISTINCT name
FROM books JOIN publisher
WHERE category = ‘COMPUTER’;

both b and c

A

SELECT UNIQUE name
FROM books NATURAL JOIN publisher
WHERE category = ‘COMPUTER’;

17
Q

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

SELECT

FROM

WHERE

both b and c

A

both b and c

18
Q

Which of the following is a valid SQL statement?

SELECT c.customer#, order#, orderdate, shipdate
FROM customers c NATURAL JOIN orders c;

SELECT * FROM customers JOIN orders ON (customer#);

SELECT c.customer#, order#, orderdate, shipdate
FROM customers c, orders o
WHERE c.customer# = o.customer#;

both b and c

A

SELECT c.customer#, order#, orderdate, shipdate
FROM customers c, orders o
WHERE c.customer# = o.customer#;

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

20
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

21
Q

A column qualifier is separated from the column using which symbol?

comma (,)

plus sign (+)

period (.)

percent sign (%)

A

period (.)

22
Q

The outer join operator in the WHERE clause cannot be used with which of the following operators?

IN

OR

AND

both a and b

A

both a and b

23
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

24
Q

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, books;

SELECT title, name
FROM publisher NATURAL JOIN books;

SELECT title, name
FROM publisher, books
WHERE p.pubid = b.pubid;

both b and c

A

SELECT title, name

FROM publisher NATURAL JOIN books;

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

26
Q

To display the name of each customer and the ISBN of each book purchased by the customers would require how many joins in the FROM clause of the SQL statement?

4

3

2

1

A

2

27
Q

How many joining conditions will be required in an SQL statement that is used to determine the gift that corresponds to each book in the BOOKS table?

4

3

2

1

A

1

28
Q

Which of the following keywords is used to create an equality join?

NATURAL JOIN

JOIN…ON

JOIN…USING

all of the above

A

all of the above

29
Q

Which of the following SQL statements will display the gift or gifts that should be sent with order# 1003?

SELECT gift
FROM promotion
WHERE order# = 1003;

SELECT gift
FROM promotion NATURAL JOIN books
WHERE order# = 1003;

SELECT gift
FROM promotion, orderitems oi, books b
WHERE retail BETWEEN minretail AND maxretail
AND oi.isbn = b.isbn AND order# = 1003;

none of the above

A

SELECT gift
FROM promotion, orderitems oi, books b
WHERE retail BETWEEN minretail AND maxretail
AND oi.isbn = b.isbn AND order# = 1003;

30
Q

Which of the following SQL statements will display the title and cost of each book stored in the BOOKS table, as well as the name of the contact person and the phone number to call to reorder the book?

SELECT title, cost, contact, phone
FROM publisher, books;

SELECT title, cost, contact, phone
FROM publisher JOIN books
USING (pubid);

SELECT title, cost, contact, phone
FROM publisher JOIN books
USING (isbn);

SELECT title, cost, contact, phone
FROM publisher JOIN books
USING (contact);

A

SELECT title, cost, contact, phone
FROM publisher JOIN books
USING (pubid);

Oracle 11g SQL (10 decks)

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