MCQs on Basic SQL Operations | PostgreSQL Database

Explore essential SQL operations in PostgreSQL, including CRUD operations, filtering with WHERE, sorting and pagination, and mastering basic JOINs for effective database management and query optimization.

MCQs on PostgreSQL Basic SQL Operations

Section 1: CRUD Operations (INSERT, SELECT, UPDATE, DELETE)

1. Which SQL command is used to add data to a table in PostgreSQL?
   • A) INSERT
   • B) SELECT
   • C) UPDATE
   • D) DELETE
2. What is the purpose of the SELECT statement in SQL?
   • A) To remove data from a table
   • B) To retrieve data from one or more tables
   • C) To add a new column to a table
   • D) To modify existing rows
3. Which of the following commands is used to modify data in an existing row?
   • A) UPDATE
   • B) INSERT
   • C) DELETE
   • D) TRUNCATE
4. What is the syntax for deleting all rows from a table in PostgreSQL?
   • A) DELETE FROM table_name WHERE condition;
   • B) DELETE FROM table_name;
   • C) DROP table_name;
   • D) TRUNCATE table_name;
5. How do you retrieve all columns from a table named “employees”?
   • A) SELECT employees FROM *;
   • B) SELECT * FROM employees;
   • C) SELECT ALL FROM employees;
   • D) SELECT rows FROM employees;

Section 2: Filtering with WHERE Clause

6. What is the purpose of the WHERE clause in a SQL query?
   • A) To sort the result set
   • B) To filter rows based on a condition
   • C) To group data by a column
   • D) To specify table joins
7. Which of the following queries retrieves employees with a salary greater than 50,000?
   • A) SELECT * FROM employees WHERE salary < 50000;
   • B) SELECT * FROM employees WHERE salary > 50000;
   • C) SELECT * FROM employees;
   • D) SELECT employees WHERE salary > 50000;
8. What operator is used in the WHERE clause to match a specific pattern in a column?
   • A) LIKE
   • B) IN
   • C) EXISTS
   • D) BETWEEN
9. How would you query for rows where the “status” column is either ‘active’ or ‘pending’?
   • A) SELECT * FROM table WHERE status = ‘active’ AND ‘pending’;
   • B) SELECT * FROM table WHERE status IN (‘active’, ‘pending’);
   • C) SELECT * FROM table WHERE status LIKE ‘active’ OR ‘pending’;
   • D) SELECT * FROM table WHERE status = ‘active’ XOR ‘pending’;
10. Which of the following statements retrieves rows where the “age” column is between 18 and 30 inclusive?
    • A) SELECT * FROM table WHERE age BETWEEN 18 AND 30;
    • B) SELECT * FROM table WHERE age > 18 AND age < 30;
    • C) SELECT * FROM table WHERE age >= 18 AND age <= 30;
    • D) Both A and C

Section 3: Sorting and Pagination (ORDER BY, LIMIT, OFFSET)

11. What does the ORDER BY clause do in a SQL query?
    • A) Limits the number of rows returned
    • B) Filters rows based on conditions
    • C) Sorts the result set in ascending or descending order
    • D) Specifies the columns to be included in the result
12. How can you sort results in descending order by a column named “created_at”?
    • A) ORDER BY created_at;
    • B) ORDER BY created_at ASC;
    • C) ORDER BY created_at DESC;
    • D) ORDER BY DESC created_at;
13. Which SQL keywords limit the number of rows returned in PostgreSQL?
    • A) OFFSET
    • B) LIMIT
    • C) FETCH FIRST
    • D) Both B and C
14. How do you retrieve rows 11 to 20 from a table?
    • A) SELECT * FROM table LIMIT 10 OFFSET 10;
    • B) SELECT * FROM table OFFSET 10 LIMIT 10;
    • C) SELECT * FROM table LIMIT 10 START 10;
    • D) Both A and B
15. What is the default sorting order in SQL when ORDER BY is used without specifying ASC or DESC?
    • A) Descending
    • B) Random
    • C) Ascending
    • D) None

Section 4: Basic Joins (INNER, LEFT, RIGHT)

16. What does an INNER JOIN do in SQL?
    • A) Returns all rows from both tables
    • B) Returns matching rows from both tables
    • C) Returns all rows from the left table
    • D) Returns all rows from the right table
17. Which of the following joins returns all rows from the left table, with matching rows from the right table?
    • A) INNER JOIN
    • B) LEFT JOIN
    • C) RIGHT JOIN
    • D) FULL OUTER JOIN
18. What is the result of a RIGHT JOIN?
    • A) Rows that exist only in the right table
    • B) All rows from the right table and matching rows from the left table
    • C) Rows that exist in both tables
    • D) All rows from both tables
19. Which keyword is used to specify the condition for joining tables?
    • A) WHERE
    • B) ON
    • C) HAVING
    • D) GROUP BY
20. What is the difference between INNER JOIN and LEFT JOIN?
    • A) INNER JOIN returns all rows; LEFT JOIN filters rows
    • B) INNER JOIN returns matching rows; LEFT JOIN includes unmatched rows from the left table
    • C) INNER JOIN combines tables; LEFT JOIN excludes duplicates
    • D) No difference
21. How would you join two tables “employees” and “departments” on the “department_id” column?
    • A) SELECT * FROM employees JOIN departments USING department_id;
    • B) SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;
    • C) SELECT * FROM employees WHERE department_id = departments.department_id;
    • D) SELECT * FROM employees AND departments department_id;
22. What type of JOIN includes rows that do not have matches in the other table?
    • A) CROSS JOIN
    • B) INNER JOIN
    • C) OUTER JOIN
    • D) NATURAL JOIN
23. What is the result of a CROSS JOIN?
    • A) Rows that match based on a condition
    • B) The Cartesian product of two tables
    • C) All rows from the left table
    • D) Rows from one table that meet a specific condition
24. What is the syntax for performing a NATURAL JOIN in SQL?
    • A) SELECT * FROM table1 JOIN table2 USING(column_name);
    • B) SELECT * FROM table1 NATURAL JOIN table2;
    • C) SELECT * FROM table1 CROSS JOIN table2;
    • D) SELECT * FROM table1 JOIN table2 ON column_name;
25. Which JOIN would return unmatched rows from both tables?
    • A) INNER JOIN
    • B) FULL OUTER JOIN
    • C) LEFT JOIN
    • D) RIGHT JOIN
26. What is the default JOIN type in SQL when no type is specified?
    • A) INNER JOIN
    • B) LEFT JOIN
    • C) FULL OUTER JOIN
    • D) CROSS JOIN
27. Can a JOIN clause be used to join more than two tables?
    • A) Yes, but only with INNER JOIN
    • B) Yes, any type of JOIN can be used
    • C) No, only two tables can be joined at a time
    • D) Yes, but only with LEFT JOIN
28. How is an alias for a table defined in a JOIN query?
    • A) USING keyword
    • B) AS keyword
    • C) WITH keyword
    • D) ALIAS keyword
29. Which of the following is a valid LEFT JOIN query?
    • A) SELECT * FROM table1 LEFT table2 ON condition;
    • B) SELECT * FROM table1 LEFT JOIN table2 ON condition;
    • C) SELECT * FROM table1 LEFT JOIN table2 USING column;
    • D) Both B and C
30. What is the purpose of the USING keyword in JOINs?
    • A) To specify the condition for a JOIN
    • B) To specify the columns to use for the JOIN
    • C) To define aliases for columns
    • D) To apply a filter condition

Answer Key