What experience do you have in writing and optimizing native SQL queries?

Experience in writing and optimizing native SQL queries includes:

• Formulating queries using indexes to speed up data retrieval.
• Using JOIN to combine tables instead of multiple separate queries.
• Applying WHERE to filter data at the database level to reduce the amount of data transferred.
• Optimizing queries with EXPLAIN to analyze execution plans and identify bottlenecks.
• Minimizing the use of subqueries and replacing them with more efficient constructs.

Example of an optimized query:

SELECT u.id, u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
ORDER BY o.order_date DESC
LIMIT 10;

This query uses JOIN, filtering, and sorting on the database side, which increases the application's efficiency.