SQL Questions

1. What is a foreign key?
2. What is a join? List the different types of joins and explain them
3. What is normalization?
   1. Various forms or normalization?
   2. What De-normalization?
   3. When should one or the other be applied?
4. What’s the difference between DELETE and TRUNCATE?
5. What are the ACID properties (Atomicity, Consistency, Isolation, Durability)
   • Atomicity: This property ensures that the transaction is completed in all-or-nothing way.
   • Consistency: This ensures that updates made to the database is valid and follows rules and restrictions.
   • Isolation: This property ensures integrity of transaction that are visible to all other transactions.
   • Durability: This property ensures that the committed transactions are stored permanently in the database.
6. What are transactions?
   1. How do transactions work?
   2. Can you explain the locks that the database might use to ensure ACID constraints?

Problem Solving Questions

Metabase Interview Credentials

1. Write a query that breaks down Orders by product category
   1. Order.Total
   2. Group By
   3. Modifier → Add month (this is challenging syntax)
      1. https://www.postgresqltutorial.com/postgresql-extract/
      2. `SELECT EXTRACT(MONTH FROM TIMESTAMP '2016-12-31 13:30:15');``
2. Write a query that breaks users down by the month they were created and the sum of order value
3. How do reviews correlate with order volume?
   1. Broken down by geography?