Additional SQL Query Constructs

SQL provides many sophisticated capabilities:

Selection of distinct records
SELECT DISTINCT title, price
FROM Books
WHERE price < 50 AND price > 10
Aggregate calculations (COUNT, SUM, AVG, MIN, MAX)
SELECT MIN(price), MAX(price)
FROM Books
Operations on groups
SELECT discount, AVG(price)
FROM Books
WHERE price > 10
GROUP BY discount
HAVING MAX(price) - MIN(price) > 10
String pattern matching
SELECT title, price
FROM Books
WHERE title LIKE 'TIMELESS%'
Ordering
SELECT title, price
FROM Books
ORDER BY price
Set union, intersection and difference (UNION, INTERSECT, EXCEPT)
SELECT title, price
FROM Books
UNION
SELECT title, price
FROM magazines
Nested queries (IN, EXISTS, operator ANY, operator ALL)
SELECT title, price
FROM Books
WHERE price > (SELECT AVG(price) FROM Books)

SELECT title, price
FROM Books
WHERE price < ALL
        (SELECT price
         FROM Books
         WHERE title LIKE 'Harry Potter%')