Question 1

Suppose you have an ADDRESS_BOOK table containing the first and last names, addresses, and birthdays of many people you know. How can you write a query to display the names of pairs of your acquaintances who have the same birthday?
A) create a new set using ADDRESS_BOOK
B) perform an outer join on ADDRESS_BOOK
C) self-join ADDRESS_BOOK to itself
D) create a second BIRTHDAY table

Accepted Answer

A self-join on the ADDRESS_BOOK table allows you to compare rows within the same table to find pairs of acquaintances who share the same birthday.

Question 2

There are three set operations: union, intersection, and difference. Define each of these operations. Which are supported by Oracle?

Accepted Answer

The answer of There are three set operations: union, intersection,...

Question 3

Describe the types of outer joins.

Accepted Answer

The answer of Describe the types of outer joins....

Question 4

One use case for an alias is a query that joins a table to itself, known as a(n) _____.
A) inner join
B) outer join
C) union
D) self-join

Accepted Answer

Aliases are particularly useful in self-joins, where the same table is joined to itself, necessitating different identifiers for each instance of the table in the query.

Question 5

When tables are listed in the FROM clause of an SQL query, you can give them alternate names, also known as _____.
A) sets
B) qualifiers
C) groups
D) aliases

Accepted Answer

Aliases are used in SQL to give a table, or a column in a table, a temporary name. This is particularly useful in making queries more readable or when joining tables.

Question 6

When a query includes the clause FROM CLIENT A, CLIENT B, SQL will _____.
A) perform an inner join
B) perform an outer join
C) treat it as a query of two tables, A and B
D) treat it as a union of two tables, A and B

Accepted Answer

SQL interprets the syntax "FROM CLIENT A, CLIENT B" as a request to query two tables, A and B, typically resulting in a Cartesian product unless further specified by a WHERE clause or join condition.

Question 7

Which element belongs in the blank if you want to display data from all rows with a credit limit greater than the credit limit of one or more category B cardholders? WHERE (CREDIT_LIMIT > _____ (SELECT CREDIT_LIMIT FROM CARDHOLDER WHERE CATEGORY = 'B'))
A) ANY
B) ALL
C) UNION
D) INTERSECT

Accepted Answer

The correct SQL keyword to use in this context is "ANY". This is because "ANY" compares a value to each value in a list or returned by a subquery, and if the comparison is true for any one of those values, the overall expression is true. In this case, using "ANY" allows the query to return rows where the credit limit is greater than the credit limit of any one or more category B cardholders.

Question 8

Which statement about table joins in SQL is true?
A) Self-joins usually do not include WHERE or ORDER BY clauses.
B) You cannot perform a self-join on a table's primary key column.
C) Joins must be performed between at least two different tables.
D) SQL has no problem treating one table with two aliases as two tables.

Accepted Answer

Self-joins can include WHERE or ORDER BY clauses, and you can perform a self-join on a table's primary key column. Joins do not have to be between two different tables; a table can be joined with itself using aliases, which SQL treats as if they were two separate tables. This is why option D is correct.

Question 9

What steps should you take to construct the SQL command to join (relate) tables?

Accepted Answer

The answer of What steps should you take to construct...

Question 10

What steps should you take to construct a detailed query in a step-by-step fashion?

Accepted Answer

The answer of What steps should you take to construct...

Question 11

What is the term for the letter S or the letter D in the following query? ​
SELECT S.SERVER_ID, S.LAST_NAME, D.DINER_ID, D.LAST_NAME
FROM SERVER S, DINER D
WHERE (S.SERVER_ID = D.SERVER_ID)
A) alias
B) operator
C) qualifier
D) set

Accepted Answer

The terms "S" and "D" are used as aliases for the tables SERVER and DINER, respectively, to simplify the query syntax and reference specific columns in those tables.

Question 12

Which statement about nested queries in SQL is FALSE?
A) In small databases, there is not a significant time difference between nested subqueries and table joins.
B) Typically, nested subqueries perform faster than queries that join tables.
C) A nested query is a subquery within a subquery.
D) Executing each subquery in a nested query produces a temporary table.

Accepted Answer

Nested subqueries often perform slower than queries that join tables because the database engine may need to execute the inner query multiple times for each row processed by the outer query, leading to potentially less efficient execution plans compared to joins.

Question 13

What is the benefit of using an alias in MySQL?
A) It improves performance.
B) It simplifies complex queries.
C) It prevents confusion of columns from different tables.
D) It is required for calculations.

Accepted Answer

The answer of What is the benefit of using an...

Question 14

Why do you use the ANY and ALL operators? What is the difference between the two operators?

Accepted Answer

The answer of Why do you use the ANY and...

Quiz 5: Multiple Table Queries

Suppose you have an ADDRESS_BOOK table containing the first and last names, addresses, and birthdays of many people you know. How can you write a query to display the names of pairs of your acquaintances who have the same birthday?

There are three set operations: union, intersection, and difference. Define each of these operations. Which are supported by Oracle?

Describe the types of outer joins.

One use case for an alias is a query that joins a table to itself, known as a(n) _____.

When tables are listed in the FROM clause of an SQL query, you can give them alternate names, also known as _____.

When a query includes the clause FROM CLIENT A, CLIENT B, SQL will _____.

Which element belongs in the blank if you want to display data from all rows with a credit limit greater than the credit limit of one or more category B cardholders? WHERE (CREDIT_LIMIT > _____ (SELECT CREDIT_LIMIT FROM CARDHOLDER WHERE CATEGORY = 'B') )

Which statement about table joins in SQL is true?

What steps should you take to construct the SQL command to join (relate) tables?

What steps should you take to construct a detailed query in a step-by-step fashion?

What is the term for the letter S or the letter D in the following query? SELECT S.SERVER_ID, S.LAST_NAME, D.DINER_ID, D.LAST_NAME FROM SERVER S, DINER D WHERE (S.SERVER_ID = D.SERVER_ID)

Which statement about nested queries in SQL is FALSE?

What is the benefit of using an alias in MySQL?

Why do you use the ANY and ALL operators? What is the difference between the two operators?

Quiz 5: Multiple Table Queries

Suppose you have an ADDRESS_BOOK table containing the first and last names, addresses, and birthdays of many people you know. How can you write a query to display the names of pairs of your acquaintances who have the same birthday?

There are three set operations: union, intersection, and difference. Define each of these operations. Which are supported by Oracle?

Describe the types of outer joins.

One use case for an alias is a query that joins a table to itself, known as a(n) _____.

When tables are listed in the FROM clause of an SQL query, you can give them alternate names, also known as _____.

When a query includes the clause FROM CLIENT A, CLIENT B, SQL will _____.

Which element belongs in the blank if you want to display data from all rows with a credit limit greater than the credit limit of one or more category B cardholders? WHERE (CREDIT_LIMIT > _____ (SELECT CREDIT_LIMIT FROM CARDHOLDER WHERE CATEGORY = 'B') )

Which statement about table joins in SQL is true?

What steps should you take to construct the SQL command to join (relate) tables?

What steps should you take to construct a detailed query in a step-by-step fashion?

What is the term for the letter S or the letter D in the following query? ​ SELECT S.SERVER_ID, S.LAST_NAME, D.DINER_ID, D.LAST_NAME FROM SERVER S, DINER D WHERE (S.SERVER_ID = D.SERVER_ID)

Which statement about nested queries in SQL is FALSE?

What is the benefit of using an alias in MySQL?

Why do you use the ANY and ALL operators? What is the difference between the two operators?

What is the term for the letter S or the letter D in the following query? SELECT S.SERVER_ID, S.LAST_NAME, D.DINER_ID, D.LAST_NAME FROM SERVER S, DINER D WHERE (S.SERVER_ID = D.SERVER_ID)