Answer:
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;
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.
