Optimization is the process the DBMS uses to choose the most efficient execution plan for the query you have written.
Since SQL is a declarative language, you tell SQL what to do, but not how to do it. This means, many of the mechanics and in’s and out’s are lets to the DBMS. It gets to choose whether to use an index or just scan a table.
You can see what choices the DBMS is making by viewing it’s query plan.
The article Query Plans in SQL is a great place to start.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using my guide Getting Started Using SQL Server