Skip to content

beginner-exercises.md

Latest commit

 

History

History
278 lines (215 loc) · 4.75 KB

beginner-exercises.md

File metadata and controls

278 lines (215 loc) · 4.75 KB

PostgreSQL Beginner Interview Questions and Answers

This document contains a set of 30 beginner-friendly PostgreSQL questions along with detailed answers. These questions are suitable for interview preparation.

1. What is PostgreSQL?

Answer: PostgreSQL is an open-source, object-relational database management system (ORDBMS) that supports both SQL (relational) and JSON (non-relational) querying. It is highly extensible, reliable, and supports advanced data types.

2. How do you create a new database in PostgreSQL?

Answer:

CREATE DATABASE my_database;

3. How do you list all databases in PostgreSQL?

Answer:

\l
-- or using SQL
SELECT datname FROM pg_database;

4. How do you connect to a specific database?

Answer:

\c my_database

5. How do you create a table in PostgreSQL?

Answer:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age > 0),
    department VARCHAR(50)
);
  • SERIAL automatically generates a unique number for id.
  • CHECK enforces constraints on data integrity.

6. How do you insert data into a table?

Answer:

INSERT INTO employees (name, age, department)
VALUES ('John Doe', 30, 'IT');

7. How do you retrieve all data from a table?

Answer:

SELECT * FROM employees;

8. How do you retrieve specific columns from a table?

Answer:

SELECT name, department FROM employees;

9. How do you update data in a table?

Answer:

UPDATE employees
SET department = 'HR'
WHERE name = 'John Doe';
  • WHERE clause ensures only the intended row(s) are updated.

10. How do you delete data from a table?

Answer:

DELETE FROM employees
WHERE name = 'John Doe';

11. How do you add a new column to an existing table?

Answer:

ALTER TABLE employees
ADD COLUMN salary NUMERIC(10,2);
  • NUMERIC(10,2) stores numbers with 10 digits total and 2 decimal places.

12. How do you remove a column from a table?

Answer:

ALTER TABLE employees
DROP COLUMN salary;

13. How do you rename a column?

Answer:

ALTER TABLE employees
RENAME COLUMN department TO dept;

14. How do you rename a table?

Answer:

ALTER TABLE employees
RENAME TO staff;

15. How do you create an index on a column?

Answer:

CREATE INDEX idx_name ON employees(name);
  • Indexes improve query performance on frequently searched columns.

16. What is the difference between CHAR, VARCHAR, and TEXT?

Answer:

  • CHAR(n): Fixed-length string, pads with spaces if less than n characters.
  • VARCHAR(n): Variable-length string with a maximum limit of n characters.
  • TEXT: Variable-length string with no maximum limit.

17. How do you find all employees older than 25?

Answer:

SELECT * FROM employees
WHERE age > 25;

18. How do you sort query results by a column in ascending order?

Answer:

SELECT * FROM employees
ORDER BY age ASC;

19. How do you sort query results in descending order?

Answer:

SELECT * FROM employees
ORDER BY age DESC;

20. How do you count the number of rows in a table?

Answer:

SELECT COUNT(*) FROM employees;

21. How do you find the maximum and minimum age?

Answer:

SELECT MAX(age) AS max_age, MIN(age) AS min_age
FROM employees;

22. How do you calculate the average age?

Answer:

SELECT AVG(age) AS average_age FROM employees;

23. How do you find unique departments?

Answer:

SELECT DISTINCT department FROM employees;

24. How do you filter records using multiple conditions?

Answer:

SELECT * FROM employees
WHERE age > 25 AND department = 'IT';

25. How do you use the LIKE operator?

Answer:

SELECT * FROM employees
WHERE name LIKE 'J%'; -- Names starting with J

26. How do you use IN to filter specific values?

Answer:

SELECT * FROM employees
WHERE department IN ('IT', 'HR');

27. How do you use BETWEEN to filter a range?

Answer:

SELECT * FROM employees
WHERE age BETWEEN 25 AND 35;

28. How do you join two tables?

Answer:

SELECT e.name, d.name AS dept_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
  • JOIN combines rows from two tables based on a related column.

29. How do you group data?

Answer:

SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;
  • GROUP BY aggregates rows with the same column value.

30. How do you delete a table?

Answer:

DROP TABLE employees;