Question

4. Return the card number, first name, last name, and average late fee of each customer. Name this column as "Fee".

5. Return the authors whose first names are shorter than all last names.
6. Return the first 10 borrowing which late fee is unknown.

How do I write these using Oracle SQL sub selects?

Answer 1

To write these queries using Oracle SQL sub selects, the following syntax can be used:

1. Return the card number, first name, last name, and average late fee of each customer. Name this column as "Fee".

SELECT card_number, first_name, last_name, AVG(late_fee) AS "Fee"

FROM customers

GROUP BY card_number, first_name, last_name;

2. Return the authors whose first names are shorter than all last names.

SELECT *

FROM authors

WHERE LENGTH(first_name) < ALL (SELECT LENGTH(last_name) FROM authors);

3. Return the first 10 borrowing which late fee is unknown.

SELECT *

FROM borrowings

WHERE late_fee IS NULL

AND ROWNUM <= 10;

Step-by-step explanation:

In these queries, sub selects are used to select specific data from a table and use it in the main query. For example, in the second query, a sub select is used to select the length of all last names from the authors table, and this data is then used in the main query to filter the authors whose first names are shorter than all last names. In the third query, a sub select is used to filter the borrowings with unknown late fees, and the main query is used to limit the result to the first 10 rows.