SQL Interview Questions

SQL Interview Questions for Beginners

1. What is SQL?

   SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases. It allows users to create, read, update, and delete data.

2. What is a primary key?

   A primary key is a unique identifier for a record in a table, ensuring that no two rows have the same value in that column.

3. What is a JOIN in SQL?

   A JOIN clause is used to combine rows from two or more tables based on a related column between them.

4. What is the difference between INNER JOIN and LEFT JOIN?

   INNER JOIN returns only the rows where there is a match in both tables.
   
   LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

5. How to create a new table?

   Creates a new table named 'employees' with specified columns.

   CREATE TABLE employees (id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department_id INT);

6. What are the different types of SQL commands?

   SQL commands are classified into several types: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language).

7. How to insert a new record into a table?

   Inserts a new employee record into the employees table.

   INSERT INTO employees (first_name, last_name, department_id) VALUES ('John', 'Doe', 1);

8. How to update an existing record?

   Updates the last name of an employee based on their ID.

   UPDATE employees SET last_name = 'Smith' WHERE id = 1;

9. How to delete a record from a table?

   Deletes an employee record based on their ID.

   DELETE FROM employees WHERE id = 1;

10. How to alter a table to add a new column?

    Adds a new column 'email' to the employees table.

    ALTER TABLE employees ADD email VARCHAR(100);

11. How to change a column's data type?

    Modifies the data type of the 'department_id' column to BIGINT.

    ALTER TABLE employees ALTER COLUMN department_id BIGINT;

12. How to rename a table?

    Renames the employees table to staff.

    ALTER TABLE employees RENAME TO staff;

13. How to drop a table?

    Removes the employees table from the database.

    DROP TABLE employees;

14. How to select all records from a table?

    Retrieves all columns and rows from the employees table.

    SELECT * FROM employees;

15. How to select specific columns?

    Retrieves only the first_name and last_name columns.

    SELECT first_name, last_name FROM employees;

16. How to filter records with WHERE?

    Selects all employees in the Sales department.

    SELECT * FROM employees WHERE department = 'Sales';

17. How to order records?

    Retrieves all employees ordered by hire date in descending order.

    SELECT * FROM employees ORDER BY hire_date DESC;

18. How to count records?

    Returns the total number of records in the employees table.

    SELECT COUNT(*) FROM employees;

19. How to use GROUP BY?

    Counts the number of employees in each department.

    SELECT department, COUNT(*) FROM employees GROUP BY department;

20. How to use HAVING?

    Retrieves departments with more than 10 employees.

    SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

21. How to join tables?

    Joins employees and departments tables to get employee names with their respective department names.

    SELECT e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.id;

22. How to insert records?

    Adds a new employee record to the employees table.

    INSERT INTO employees (first_name, last_name, department_id) VALUES ('John', 'Doe', 1);

23. How to update records?

    Updates the department of John Doe.

    UPDATE employees SET department_id = 2 WHERE first_name = 'John' AND last_name = 'Doe';

24. How to delete records?

    Deletes records of employees with the last name Doe.

    DELETE FROM employees WHERE last_name = 'Doe';

25. How to find duplicates?

    Identifies duplicate employee names.

    SELECT first_name, last_name, COUNT(*) FROM employees GROUP BY first_name, last_name HAVING COUNT(*) > 1;

26. How to use DISTINCT?

    Retrieves unique department names from the employees table.

    SELECT DISTINCT department FROM employees;

27. How to use subqueries?

    Selects employees who belong to the Sales department using a subquery.

    SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales');

28. How to limit results?

    Retrieves the first five records from the employees table.

    SELECT * FROM employees LIMIT 5;

29. How to find the second highest salary using a subquery?

    This query retrieves the second highest salary from the employees table using a subquery.

    SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

30. How to find the second highest salary using the DISTINCT keyword?

    This query retrieves the second highest salary by using DISTINCT to filter unique salary values.

    SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

31. How to find the second highest salary using a Common Table Expression (CTE)?

    This query utilizes a CTE to rank salaries and retrieve the second highest.

    WITH RankedSalaries AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) SELECT salary FROM RankedSalaries WHERE rnk = 2;

32. How to find the second highest salary using GROUP BY?

    This query groups salaries and retrieves the second highest using HAVING.

    SELECT salary FROM employees GROUP BY salary ORDER BY salary DESC LIMIT 1 OFFSET 1;

33. How to find the second highest salary with a subquery and the COUNT function?

    This query finds the second highest salary by counting distinct salaries.

    SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

34. What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?

    INNER JOIN returns only matching rows; LEFT JOIN returns all rows from the left table and matching rows from the right; RIGHT JOIN returns all rows from the right table and matching rows from the left.

    SELECT * FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

35. What is a subquery?

    A subquery is a query nested inside another query, used to retrieve data that will be used in the main query.

    SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales');

36. What is normalization?

    Normalization is the process of organizing data to minimize redundancy and improve data integrity.

37. How do you use the GROUP BY clause?

    GROUP BY aggregates results based on one or more columns.

    SELECT department, COUNT(*) FROM employees GROUP BY department;

38. What are indexes, and why are they used?

    Indexes are data structures that improve the speed of data retrieval operations on a database table at the cost of additional space and maintenance time.

    CREATE INDEX idx_employee_lastname ON employees(last_name);

39. Explain the concept of a stored procedure.

    A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit.

    CREATE PROCEDURE GetEmployeesByDepartment(@dept_id INT) AS BEGIN SELECT * FROM employees WHERE department_id = @dept_id; END;

40. What is a view in SQL?

    A view is a virtual table based on the result set of a SQL query. It can simplify complex queries.

    CREATE VIEW SalesEmployees AS SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales');

41. How can you improve query performance?

    Query performance can be improved by indexing, optimizing queries, avoiding unnecessary columns, and analyzing query execution plans.

    EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

42. What is a CROSS JOIN?

    CROSS JOIN returns the Cartesian product of two tables, combining all rows from the first table with all rows from the second table.

    SELECT * FROM employees CROSS JOIN departments;

43. How to perform a SELF JOIN?

    A SELF JOIN is used to join a table to itself to compare rows within the same table.

    SELECT a.first_name, b.first_name FROM employees a JOIN employees b ON a.manager_id = b.id;

44. What is a NATURAL JOIN?

    NATURAL JOIN automatically joins tables based on columns with the same names and types.

    SELECT * FROM employees NATURAL JOIN departments;

45. How to use FULL OUTER JOIN?

    FULL OUTER JOIN returns all records when there is a match in either left or right table records.

    SELECT * FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id;

46. How to use LEFT JOIN with multiple conditions?

    LEFT JOIN can include multiple conditions using AND.

    SELECT e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id AND e.status = 'active';

47. How to use INNER JOIN with aggregate functions?

    INNER JOIN can be combined with aggregate functions to summarize data.

    SELECT d.department_name, COUNT(e.id) FROM employees e INNER JOIN departments d ON e.department_id = d.id GROUP BY d.department_name;

48. How to use LEFT JOIN with COALESCE?

    LEFT JOIN can use COALESCE to replace NULL values with a default value.

    SELECT e.first_name, COALESCE(d.department_name, 'No Department') FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

49. How to join three tables?

    Joining multiple tables can be done by chaining JOIN statements.

    SELECT e.first_name, d.department_name, p.project_name FROM employees e JOIN departments d ON e.department_id = d.id JOIN projects p ON e.project_id = p.id;

50. How to use the LIKE operator to find names that start with a specific letter?

    This query retrieves all employee names that start with the letter 'A'.

    SELECT * FROM employees WHERE first_name LIKE 'A%';

51. How to use the LIKE operator to find names that contain a specific substring?

    This query finds all employee names that contain the substring 'son'.

    SELECT * FROM employees WHERE last_name LIKE '%son%';

52. How to use the LIKE operator to find names ending with a specific suffix?

    This query retrieves all employee names that end with 'n'.

    SELECT * FROM employees WHERE first_name LIKE '%n';

53. How to use regular expressions to find names that start with a vowel?

    This query retrieves all employee names that start with any vowel using regular expressions.

    SELECT * FROM employees WHERE first_name REGEXP '^[AEIOU]';

54. How to use regular expressions to find names with a specific pattern?

    This query finds all employee names that have 'a' followed by any two characters and then 'e'.

    SELECT * FROM employees WHERE first_name REGEXP 'a..e';

55. How to use regular expressions to find names with specific lengths?

    This query retrieves names that are exactly five characters long.

    SELECT * FROM employees WHERE LENGTH(first_name) = 5;