Can you create and optimize complex SQL queries to retrieve data?

Yes, creating and optimizing complex SQL queries is an important skill for effective database work. Optimization includes:

• Using indexes to speed up searches.
• Avoiding redundant JOINs and subqueries.
• Applying aggregate functions and groupings as needed.
• Using EXPLAIN to analyze the execution plan of the query.

Example of a complex query that selects users with more than 5 orders and sorts by the date of the last order:

SELECT u.id, u.name, COUNT(o.id) AS orders_count, MAX(o.order_date) AS last_order
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY last_order DESC;

This query can be optimized by ensuring there are indexes on orders.user_id and orders.order_date.