How would you securely store database passwords or API keys in AWS?

I am preparing for AWS interviews and want to understand the best practices for securely storing sensitive information such as database passwords, API keys, and application secrets.

Could someone explain:

Which AWS services are recommended for storing secrets securely (such as AWS Secrets Manager or AWS Systems Manager Parameter Store)?
When should I use AWS Secrets Manager instead of Parameter Store?
How can an EC2 instance, Lambda function, or ECS task securely access these secrets without hardcoding them?
What role does AWS IAM play in controlling access to secrets?
How can secret rotation be automated to improve security?
What are the common mistakes developers should avoid when handling credentials in AWS?
Could you provide a simple real-world example or architecture demonstrating secure secret management?