An index is a sorted copy of one or more columns with pointers back to the rows, like the index at the back of a book. With an index on orders (customer_id), finding one customer's orders means a short lookup instead of reading the whole table.
CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE UNIQUE INDEX customers_email_idx ON customers (email);
Indexes aren't free: every insert and update must also update them, and they take space. Index the columns you filter or join on often, especially those that pick out few rows (an id, an email). A column with a handful of values, such as status, rarely helps, and small tables don't need indexes at all.
In an index on several columns, order matters: (customer_id, order_date) serves "this customer" and "this customer in this date range", but not "this date range" alone.
Row-store databases such as PostgreSQL, MySQL and SQL Server rely on indexes heavily. Column stores like the one here, BigQuery and Snowflake read columns in large compressed blocks and use indexes far less, but the reasoning above is what interviews ask about.