IM SUBQUERY MIDTERM QUIZ Flashcards by LUIS KEINE CERTEZA

What is a subquery in SQL?
A. A query that runs on multiple databases
B. A query nested inside another query
C. A query that only returns a single row
D. A query that only modifies data

A query nested inside another query

How well did you know this?

Not at all

Perfectly

In which SQL clauses can a subquery be used?
A. WHERE
B. HAVING
C. FROM
D. All of the above

All of the above

How well did you know this?

Not at all

Perfectly

What is the main purpose of a subquery?
A. To perform multiple operations at once
B. To replace complex joins
C. To retrieve intermediate results for use in another query
D. To improve database performance

To retrieve intermediate results for use in another query

How well did you know this?

Not at all

Perfectly

What is another term used to refer to a subquery?
A. Inner SELECT
B. Outer SELECT
C. Super query
D. Primary query

Inner SELECT

How well did you know this?

Not at all

Perfectly

Which of the following is NOT a type of subquery?
A. Single-row subquery
B. Multiple-row subquery
C. Multi-database subquery
D. Multiple-column subquery

Multi-database subquery

How well did you know this?

Not at all

Perfectly

Which type of subquery returns only one row?
A. Single-row subquery
B. Multiple-row subquery
C. Multiple-column subquery
D. Aggregate subquery

Single-row subquery

How well did you know this?

Not at all

Perfectly

A multiple-row subquery returns how many rows?
A. Only one row
B. One or more rows
C. Exactly two rows
D. No rows

One or more rows

How well did you know this?

Not at all

Perfectly

Which SQL operator is used with multiple-row subqueries?
A. =
B. IN
C. <>
D. LIKE

How well did you know this?

Not at all

Perfectly

Which of the following statements correctly finds employees earning more than Abel’s salary?

A. SELECT last_name FROM employees WHERE salary > Abel;

B. SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = ‘Abel’);

C. SELECT last_name FROM employees WHERE salary < (SELECT salary FROM employees WHERE last_name = ‘Abel’);

D. SELECT last_name FROM employees WHERE salary >= Abel;

SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = ‘Abel’);

How well did you know this?

Not at all

Perfectly

Which comparison operator is used in single-row subqueries?
A. =
B. >
C. >=
D. All of the above

All of the above

How well did you know this?

Not at all

Perfectly

What happens if a single-row subquery returns multiple rows?
A. The query executes successfully
B. The query returns only the first row
C. The query generates an error
D. The query returns null

The query generates an error

How well did you know this?

Not at all

Perfectly

What is the main difference between a single-row and a multiple-row subquery?

A. A single-row subquery can only be used in the FROM clause

B. A multiple-row subquery must always return more than one column

C. A single-row subquery returns exactly one row, while a multiple-row subquery can return more than one

D. There is no difference

A single-row subquery returns exactly one row, while a multiple-row subquery can return more than one

How well did you know this?

Not at all

Perfectly

Which SQL operator is used to compare a value with a set of values returned by a subquery?
A. =
B. IN
C. LIKE
D. EXISTS

How well did you know this?

Not at all

Perfectly

The ANY operator is used when comparing a value with…
A. A single value
B. Each value returned by a subquery
C. The total number of rows in a table
D. The first value returned by a subquery

Each value returned by a subquery

How well did you know this?

Not at all

Perfectly

What does <ALL mean when used in a subquery?
A. Less than the minimum value in the subquery result
B. Less than the average value in the subquery result
C. Less than the maximum value in the subquery result
D. Less than or equal to the highest value in the subquery

Less than the minimum value in the subquery result

How well did you know this?

Not at all

Perfectly

What does =ANY mean?
A. Equals at least one value returned by the subquery
B. Equals the first row of the subquery
C. Equals the last row of the subquery
D. Equals all rows returned by the subquery

Equals at least one value returned by the subquery

How well did you know this?

Not at all

Perfectly

The ALL operator in a subquery means…
A. The value must match at least one row
B. The value must be greater than or less than every row returned by the subquery
C. The subquery must return at least one null value
D. The subquery must return exactly one row

The value must be greater than or less than every row returned by the subquery

How well did you know this?

Not at all

Perfectly

What happens if a subquery returns NULL?
A. The outer query ignores the NULL value
B. The outer query returns NULL if the comparison operator is =
C. The outer query generates an error
D. The subquery is automatically removed from execution

The outer query returns NULL if the comparison operator is =

Which SQL operator should NOT be used when a subquery might return NULL values?
A. IN
B. =
C. NOT IN
D. ANY

NOT IN

How can you ensure that a subquery does not return NULL values?
A. Add a WHERE clause in the subquery to exclude NULLs
B. Use the IS NOT NULL condition in the outer query
C. Both A and B
D. There is no way to avoid NULL values in a subquery

Both A and B

Which of the following subqueries is correctly used in the HAVING clause?
A.
sql
Copy
Edit
SELECT department_id FROM employees
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

B.
sql
Copy
Edit
SELECT department_id FROM employees
WHERE AVG(salary) > (SELECT AVG(salary) FROM employees);

C.
sql
Copy
Edit
SELECT department_id FROM employees
HAVING salary > (SELECT salary FROM employees);

D.
sql
Copy
Edit
SELECT department_id FROM employees
HAVING (SELECT department_id FROM employees);

sql
Copy
Edit
SELECT department_id FROM employees
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

What is the purpose of a subquery in the FROM clause?
A. To create a temporary table
B. To update rows in the main query
C. To replace GROUP BY operations
D. To generate errors

To create a temporary table

What is a subquery?
a) A query inside another query
b) A query that updates a table
c) A query that deletes records
d) A query that only selects one row

A query inside another query

What is the purpose of using subqueries?
a) To update multiple tables at once
b) To retrieve data based on an unknown condition
c) To insert new records into a table
d) To permanently delete duplicate records

To retrieve data based on an unknown condition

A subquery is also known as: a) Outer query b) Aggregated query c) Nested SELECT d) Temporary query

Nested SELECT

What is a single-row subquery? a) A subquery that returns only one column b) A subquery that returns exactly one row c) A subquery that always returns multiple rows d) A subquery that returns multiple columns

A subquery that returns exactly one row

What is a multiple-row subquery? a) A subquery that always returns one row b) A subquery that can return more than one row c) A subquery that cannot use aggregate functions d) A subquery that must be placed in the FROM clause

A subquery that can return more than one row

What type of subquery returns multiple columns? a) Single-row subquery b) Multiple-row subquery c) Multiple-column subquery d) Aggregated subquery

Multiple-column subquery

Where can a subquery be placed in an SQL statement? a) WHERE clause b) HAVING clause c) FROM clause d) All of the above

All of the above

Which SQL clause is most commonly used with subqueries? a) SELECT b) FROM c) WHERE d) GROUP BY

WHERE

Which of the following operators is NOT a single-row operator? a) = b) > c) >= d) IN

Which operator should be used in a multiple-row subquery? a) = b) <> c) IN d) ALL

What does

Less than the maximum value returned by the subquery

What does >ALL mean in a subquery? a) More than the minimum value returned b) More than the maximum value returned c) Equal to all values in the subquery d) None of the above

More than the maximum value returned

Which of the following statements is true regarding =ANY? a) It is equivalent to IN b) It is equivalent to ALL c) It means greater than any value in the subquery d) It means less than the minimum value in the subquery

It is equivalent to IN

What aggregate function is commonly used in subqueries? a) COUNT b) AVG c) MIN d) All of the above

All of the above

Which clause allows the use of subqueries to filter aggregated results? a) WHERE b) HAVING c) GROUP BY d) ORDER BY

HAVING

What will happen if a subquery with an aggregate function (e.g., MIN, MAX) is used with = in a WHERE clause? a) It will return a syntax error b) It will always return a NULL value c) It will compare the value to the aggregate result d) It will fail if the subquery returns multiple rows

It will compare the value to the aggregate result

What happens if a single-row subquery returns multiple rows? a) The query executes successfully b) An error occurs c) The first row is used, and the rest are ignored d) The results are stored in a temporary table

An error occurs

What is the correct way to fix a single-row subquery that returns multiple rows? a) Change = to IN b) Add an ORDER BY clause c) Use a different SELECT statement d) Change HAVING to WHERE

Change = to IN

If a subquery returns NULL, what happens to the main query? a) It returns NULL as the result b) It raises an error c) It ignores the NULL value d) It automatically assigns a default value

It returns NULL as the result

Which of the following should NOT be used with NOT IN when working with subqueries? a) Values b) NULL c) COUNT d) DISTINCT

NULL

Which of the following problems can be solved using subqueries? a) Finding employees earning more than a specific employee b) Finding the department with the highest salary c) Finding products priced lower than the average price d) All of the above

All of the above

Which SQL query is an example of a subquery? a) SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); b) DELETE FROM employees WHERE salary > 5000; c) UPDATE employees SET salary = 6000 WHERE job_id = 'IT_PROG'; d) INSERT INTO employees VALUES (101, 'John', 'Manager', 7000);

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);