Fundamentals

1. What is PostgreSQL?

   • PostgreSQL is a powerful, open-source, object-relational database system known for its robustness, advanced features, and active community.

2. What are the key features of PostgreSQL?

   • Open-source: Free to use and distribute.
   • Object-relational: Supports advanced features like inheritance, user-defined types, and functions.
   • Robustness: Highly reliable and stable.
   • Extensible: Supports extensions for adding new features and functionality.
   • Active community: Large and active community with extensive documentation and support.

3. Explain the concept of ACID properties in PostgreSQL.

   • Atomicity: All-or-nothing execution of transactions.
   • Consistency: Ensures data integrity and adheres to database constraints.
   • Isolation: Concurrent transactions do not interfere with each other.
   • Durability: Once a transaction is committed, it will persist even in case of system failures.

4. What are the different data types in PostgreSQL?

   • integer, varchar, text, date, timestamp, numeric, boolean, json, array, etc.

5. What is a primary key in PostgreSQL?

   • A unique identifier for each row in a table.

SQL Basics

6. Write a SQL query to select all columns from a table named 'users'.

   • SELECT * FROM users;

7. Write a SQL query to select specific columns (id, name) from a table named 'customers'.

   • SELECT id, name FROM customers;

8. Write a SQL query to insert a new row into a table.

   • INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

9. Write a SQL query to update a row in a table.

   • UPDATE customers SET email = 'updated@example.com' WHERE id = 1;

10. Write a SQL query to delete a row from a table.

    • DELETE FROM customers WHERE id = 1;

Joins

11. What are different types of joins in SQL?

    • INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN

12. Explain the difference between INNER JOIN and LEFT JOIN.

    • INNER JOIN: Returns rows where there is a match in both tables.
    • LEFT JOIN: Returns all rows from the left table and matching rows from the right table.

13. Write a SQL query to join two tables (customers and orders).

    • SELECT customers.name, orders.order_date FROM customers INNER JOIN orders ON customers.id = orders.customer_id;

Subqueries

14. What are subqueries in SQL?

    • Nested SELECT statements within another SQL statement.

15. Write a SQL query to find customers who have placed an order.

    • SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

Aggregation Functions

16. What are some common aggregation functions in SQL?

    • COUNT(), SUM(), AVG(), MIN(), MAX()

17. Write a SQL query to find the average order amount.

    • SELECT AVG(amount) FROM orders;

18. Write a SQL query to count the number of customers in a city.

    • SELECT COUNT(*) FROM customers WHERE city = 'New York';

Indexing

19. What is an index in PostgreSQL?

    • A data structure that improves the speed of data retrieval.

20. What are the different types of indexes in PostgreSQL?

    • B-tree index, hash index, GiST index, GIN index.

21. When should you create an index?

    • When frequently querying on a specific column or combination of columns.

Constraints

22. What are constraints in PostgreSQL?

    • Rules that enforce data integrity.

23. What are some common constraints in PostgreSQL?

    • NOT NULL, UNIQUE, FOREIGN KEY, CHECK

Transactions

24. What is a transaction in PostgreSQL?

    • A logical unit of work that consists of one or more SQL statements.

25. How do you start and commit a transaction in PostgreSQL?

    • BEGIN or START TRANSACTION to start, COMMIT to commit.

Views

26. What is a view in PostgreSQL?
    • A virtual table based on the result of a SQL query.

Functions

27. What are functions in PostgreSQL?

    • User-defined functions that can be used to encapsulate complex logic.

28. What are stored procedures in PostgreSQL?

    • Similar to functions, but can modify data in the database.

Triggers

29. What are triggers in PostgreSQL?
    • Special type of stored procedure that automatically executes when an event (INSERT, UPDATE, DELETE) occurs on a table.

Security

30. How do you secure a PostgreSQL database?

    • Strong passwords, user access control, firewalls, regular backups.

31. Explain the concept of SQL injection and how to prevent it.

    • SQL injection is a security vulnerability where malicious SQL code is injected into an application.
    • Prevent it using prepared statements and parameterized queries.

Performance Optimization

32. How can you improve the performance of PostgreSQL queries?

    • Create appropriate indexes, optimize data modeling, use efficient query patterns.

33. What is query planning in PostgreSQL?

    • The process of determining the most efficient way to execute a query.

Advanced Topics

34. What is partitioning in PostgreSQL?

    • Dividing a large table into smaller, more manageable parts.

35. What is replication in PostgreSQL?

    • Creating multiple copies of a database on different servers for high availability and load balancing.

36. What is logical replication in PostgreSQL?

    • Replicates changes to specific tables or subsets of data.

37. What is physical replication in PostgreSQL?

    • Replicates entire databases.

38. What is a cluster in PostgreSQL?

    • A group of interconnected PostgreSQL servers that work together to provide high availability, scalability, and fault tolerance.

39. What is PostgreSQL's role-based access control (RBAC)?

    • A mechanism to control user access to databases, tables, and operations.

40. What are some common PostgreSQL administration tools?

    • pgAdmin, psql.

41. How do you monitor PostgreSQL server performance?

    • Use PostgreSQL's built-in monitoring tools and performance counters.

42. How do you back up and restore a PostgreSQL database?

    • Use the pg_dump and psql commands.

43. What are some common use cases for PostgreSQL?

    • Web applications, e-commerce, data warehousing, geospatial applications.

44. What are some of the challenges of using PostgreSQL?

    • Can have a steeper learning curve compared to some other databases.

45. What is the purpose of the EXPLAIN statement in PostgreSQL?

    • Provides information about how PostgreSQL will execute a query, helping to identify potential performance bottlenecks.

46. What is the difference between SERIAL and BIGSERIAL data types?

    • SERIAL generates integers within a specific sequence, while BIGSERIAL generates 64-bit integers.

47. What is the purpose of the WHERE clause in a SQL query?

    • Specifies the conditions that must be met for a row to be included in the result set.

48. What is the purpose of the ORDER BY clause in a SQL query?

    • Sorts the result set based on one or more columns.

49. What is the purpose of the GROUP BY clause in a SQL query?

    • Groups rows that have the same values for specified columns.

50. What are some advanced PostgreSQL features?

    • Full-text search, JSON support, geospatial extensions, window functions.

I hope these questions are helpful for your PostgreSQL interview preparation!