Databases Junior interview questions and answers

75 questions for a Databases (Junior) interview - with correct answers, explanations and links to the docs.

Take this quiz
  1. 1.What is a relational database?
  2. 2.What is a primary key?
  3. 3.What is a foreign key?
  4. 4.How do you model a many-to-many relationship, such as students and courses?
  5. 5.What does the NOT NULL constraint do?
  6. 6.What is the difference between UNIQUE and PRIMARY KEY?
  7. 7.What does a CHECK constraint do?
  8. 8.How do you get an auto-incrementing ID column in modern PostgreSQL?
  9. 9.What is a common reason to use UUIDs instead of incrementing integers as IDs?
  10. 10.Which column type is best for storing money amounts?
  11. 11.Which query returns the names of users older than 30, sorted by name?
  12. 12.In what logical order does the database evaluate the parts of a query?
  13. 13.What does SELECT DISTINCT city FROM users return?
  14. 14.What does WHERE email = NULL return?
  15. 15.What is the difference between COUNT(*) and COUNT(phone)?
  16. 16.What does WHERE name LIKE 'An%' match?
  17. 17.How do you get the 10 newest orders?
  18. 18.What does WHERE price BETWEEN 10 AND 20 include?
  19. 19.What does WHERE status IN ('new', 'paid') mean?
  20. 20.Why is SELECT * often avoided in application code?
  21. 21.What does an INNER JOIN return?
  22. 22.What does a LEFT JOIN return?
  23. 23.How do you find users who have no orders?
  24. 24.What does a CROSS JOIN of a table with 10 rows and a table with 5 rows return?
  25. 25.A user has 3 orders and 2 addresses. You join users, orders and addresses on user_id. How many rows do you get for that user?
  26. 26.Why are table aliases like u and o used in joins?
  27. 27.What does this query return?
  28. 28.What is the difference between WHERE and HAVING?
  29. 29.Why does SELECT city, name, COUNT(*) FROM users GROUP BY city fail?
  30. 30.Which aggregate functions are standard in SQL?
  31. 31.What is the difference between UNION and UNION ALL?
  32. 32.What happens with UPDATE users SET active = false; without a WHERE?
  33. 33.Which statement adds a row?
  34. 34.What is the difference between DELETE FROM logs and TRUNCATE logs?
  35. 35.What is the difference between DDL and DML?
  36. 36.What is a 'soft delete'?
  37. 37.What is the main goal of database normalization?
  38. 38.Which table violates first normal form (1NF)?
  39. 39.What is denormalization?
  40. 40.Which type should you usually use for timestamps in PostgreSQL?
  41. 41.What is the difference between varchar(255) and text in PostgreSQL?
  42. 42.What is a view?
  43. 43.What is an index for?
  44. 44.Why not add an index on every column?
  45. 45.What is a transaction?
  46. 46.What does ACID stand for?
  47. 47.What does 'durability' guarantee?
  48. 48.What is SQL injection?
  49. 49.Why should an application connect to the database with a user that has limited privileges?
  50. 50.What is a database migration?
  51. 51.What is an ORM?
  52. 52.Why should you regularly test restoring from backups, not just make them?
  53. 53.What is the main idea of a document database like MongoDB?
  54. 54.What is Redis most often used for in web applications?
  55. 55.When is a relational database usually the better default?
  56. 56.What does 'schemaless' mean for a document database?
  57. 57.What does EXPIRE session:42 3600 do in Redis?
  58. 58.What is a subquery?
  59. 59.What does COALESCE(nickname, name) return?
  60. 60.What does CASE do in a query?
  61. 61.What does INSERT ... RETURNING id do in PostgreSQL?
  62. 62.What does OFFSET 20 LIMIT 10 do?
  63. 63.What is a database 'schema' in the sense of PostgreSQL's CREATE SCHEMA?
  64. 64.Why should you avoid storing large files (images, videos) directly in database rows?
  65. 65.What does SQLite fit best?
  66. 66.What is the result of SELECT 10 + NULL?
  67. 67.What is a composite primary key?
  68. 68.What does ON DELETE CASCADE on a foreign key do?
  69. 69.What does created_at timestamptz NOT NULL DEFAULT now() do?
  70. 70.How do you add a column to an existing table?
  71. 71.How do you get the 5 cities with the most users?
  72. 72.Table employees(id, name, manager_id) stores each employee's manager. How do you list employees with their manager's name?
  73. 73.Does PostgreSQL automatically create an index on a foreign key column like orders.user_id?
  74. 74.What does EXISTS check?
  75. 75.Where do NULLs appear with ORDER BY last_login ASC in PostgreSQL?