WELCOME     TO     B.M

SQL Interview Questions and Answers 2024 - Mysql Database


SQL Interview Questions and Answers 2024 - Mysql Database. Explore a comprehensive collection of SQL (Structured Query Language) interview questions designed to test your database management skills. Whether you're a seasoned SQL developer or preparing for a job interview, these questions cover a range of topics, including query optimization, data manipulation, and database design. Boost your SQL knowledge and ace your next interview with our expertly curated set of questions and detailed answers. Dive into this valuable resource to enhance your SQL proficiency and stay ahead in the competitive world of database management.

Most Important Top SQL Database Interview Questions and Answers

1. What is SQL?

SQL stands for Structured Query Language. It is a domain-specific language used for managing and manipulating relational databases.

2. What are the types of SQL statements?

SQL statements can be classified into three main types: DDL (Data Definition Language), DML (Data Manipulation Language), and DQL (Data Query Language).

3. Explain the difference between DELETE and TRUNCATE commands.

DELETE is a DML command used to delete specific rows from a table, whereas TRUNCATE is a DDL command used to remove all rows from a table.

4. What is a primary key?

A primary key is a unique identifier for a record in a database table. It ensures that each record in the table can be uniquely identified and is used to establish relationships between tables.

5. Explain the concept of normalization.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves breaking down large tables into smaller, related tables.

6. Write a SQL query to retrieve all columns from a table named "employees."

SELECT * FROM employees;

7. How can you retrieve unique values from a column?

SELECT DISTINCT column_name FROM table_name;

8. Write a query to find the second highest salary from a table named "salaries."

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

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

INNER JOIN returns only the matching rows from both tables, while LEFT JOIN returns all rows from the left table and the matching rows from the right table.

10. Write a SQL query to count the number of rows in a table named "orders."


11. What is a foreign key?

A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a link between the two tables.

12. Explain the ACID properties of a transaction.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliability of database transactions.

13. What is a subquery?

A subquery is a query nested within another query. It can be used to retrieve data that will be used by the main query as a condition.

14. Write a query to find the third highest salary from a table named "employees."

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 2, 1;

15. Explain the purpose of the GROUP BY clause.

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, like calculating aggregate functions (e.g., COUNT, SUM) on those groups.

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

ALTER TABLE table_name ADD COLUMN new_column datatype;

17. Write a SQL query to update the salary of an employee with ID 101.

UPDATE employees SET salary = 50000 WHERE employee_id = 101;

18. Explain the difference between UNION and UNION ALL.

UNION combines the result sets of two queries, eliminating duplicate rows, while UNION ALL includes all rows, including duplicates.

19. How can you delete all records from a table without deleting the table itself?

DELETE FROM table_name;

20. Write a query to find the employees who earn more than the average salary.

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

21. What is a NOT NULL constraint?

The NOT NULL constraint ensures that a column cannot have a NULL (missing or undefined) value.

22. Explain the purpose of the UNIQUE constraint.

The UNIQUE constraint ensures that all values in a column are unique, with no duplicate values.

23. How can you add a PRIMARY KEY constraint to an existing table?

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

24. What is the purpose of the CHECK constraint?

The CHECK constraint ensures that the values in a column meet a specific condition.

25. Explain the CASCADE option in a foreign key constraint.

CASCADE automatically propagates changes in the parent table's primary key to the child table's foreign key, maintaining referential integrity.

26. What is an index?

An index is a database object that improves the speed of data retrieval operations on a database table.

27. Explain the difference between clustered and non-clustered indexes.

A clustered index determines the order in which data is physically stored in a table, while a non-clustered index does not affect the physical order of rows.

28. What are the advantages and disadvantages of using indexes?

  • Advantages: Faster data retrieval, improved query performance.
  • Disadvantages: Increased storage space, slower data modification operations.

29. Explain the concept of covering indexes.

A covering index includes all the columns required to satisfy a query, eliminating the need to access the actual table for data retrieval.

30. What is a view in SQL?

A view is a virtual table based on the result of a SELECT query. It does not store the data itself but provides a way to present selected data from one or more tables.

31. How do you create a view?

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
WHERE condition;

32. Can you update data in a view?

It depends on the type of view. Updatable views allow data modifications, but complex views with multiple tables or aggregate functions might not be updatable.

33. Write a query to retrieve data from a view named "customer_orders."

SELECT * FROM customer_orders;

34. Explain the purpose of the WITH CHECK OPTION clause when creating a view.

The WITH CHECK OPTION ensures that all data modifications made through the view adhere to the conditions specified in the WHERE clause.

35. What is a stored procedure?

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

36. How do you create a stored procedure?

CREATE PROCEDURE procedure_name
-- SQL statements

37. What is the difference between a stored procedure and a function?

A stored procedure does not return a value, whereas a function returns a value.

38. Explain the concept of parameterized stored procedures.

Parameterized stored procedures accept input parameters, making them more flexible and reusable.

39. Write a SQL query to call a stored procedure named "get_employee_details" with parameter values.

EXEC get_employee_details @employee_id = 101;

40. What is a trigger?

A trigger is a set of instructions that are automatically executed or fired in response to specified events, such as INSERT, UPDATE, or DELETE operations on a table.

41. How do you create a trigger?

CREATE TRIGGER trigger_name
ON table_name
-- Trigger logic

42. Explain the difference between BEFORE and AFTER triggers.

BEFORE triggers are executed before the triggering event, while AFTER triggers are executed after the triggering event.

43. Write a trigger to update a timestamp column when a row is modified.

CREATE TRIGGER update_timestamp
ON table_name
UPDATE table_name SET timestamp_column = GETDATE() WHERE id IN (SELECT id FROM inserted);

44. What is the purpose of the "inserted" and "deleted" tables in triggers?

The "inserted" table contains the new values for rows affected by an INSERT or UPDATE operation, while the "deleted" table contains the old values for rows affected by an UPDATE or DELETE operation.

45. Explain the difference between JOIN and INNER JOIN.

JOIN is a general clause used to combine rows from two or more tables, while INNER JOIN returns only the rows that have matching values in both tables.

46. Write a query to retrieve the names of employees and their corresponding departments.

SELECT, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

47. Explain the concept of a self-join.

A self-join is a regular join, but the table is joined with itself. It is used when a table has a hierarchical structure or when comparing rows within the same table.

48. Write a query to find the employees who do not belong to any department.

FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
WHERE departments.department_id IS NULL;

49. What is a correlated subquery?

A correlated subquery refers to a subquery that depends on the outer query, using values from the outer query in its WHERE clause.

50. Write a query to find the top N records from a table named "products" based on the product price.


51. Explain the purpose of the HAVING clause.

The HAVING clause is used to filter the results of a GROUP BY clause based on specified conditions.

52. Write a query to calculate the total sales for each product category.

SELECT category, SUM(sales) AS total_sales
FROM products
GROUP BY category;

53. How can you pivot data using the SQL PIVOT function?

The PIVOT function is used to transform rows into columns, aggregating data in the process.

54. Write a query to find the nth highest salary without using the LIMIT clause.

SELECT DISTINCT salary FROM employees e1
WHERE n = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e1.salary <= e2.salary);

SQL Performance Tuning:-

55. What is SQL optimization?

SQL optimization is the process of improving the performance of SQL queries by minimizing response time and resource consumption.

56. Explain the use of indexes in SQL optimization.

Indexes improve query performance by providing faster data retrieval. They reduce the number of rows that need to be scanned.

57. How can you optimize a slow-performing query?

Possible optimizations include adding indexes, rewriting the query, and ensuring that statistics are up-to-date.

58. Explain the concept of query execution plans.

A query execution plan is a set of steps chosen by the database engine to execute a SQL query efficiently.

59. Write a query to retrieve the top 10 products by sales, optimizing for performance.


60. What is the difference between CHAR and VARCHAR data types?

CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type.

61. Explain the purpose of the DATE data type.

The DATE data type is used to store date values in the format 'YYYY-MM-DD.'

62. How do you handle NULL values in SQL?

NULL represents an unknown or missing value. You can use the IS NULL or IS NOT NULL conditions to filter or check for NULL values.

63. What is the purpose of the TIMESTAMP data type?

The TIMESTAMP data type is used to store date and time values down to fractions of a second.

64. Write a query to find the employees whose birthdays fall in the current month.

SELECT * FROM employees WHERE MONTH(birthdate) = MONTH(GETDATE());

65. Explain the concept of a database transaction.

A database transaction is a sequence of one or more SQL statements executed as a single unit of work. It ensures the consistency and integrity of a database.

66. What is the purpose of the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements?

BEGIN TRANSACTION marks the beginning of a transaction, COMMIT saves the changes, and ROLLBACK undoes the changes made during the transaction.

67. Explain the concept of isolation levels in database transactions.

Isolation levels define the degree to which one transaction must be isolated from the effects of other concurrent transactions.

68. What is SQL injection, and how can it be prevented?

SQL injection is a technique where an attacker injects malicious SQL code into a query. It can be prevented by using parameterized queries and input validation.

-:

About B.M

Welcome to our online Job, matrimonial bio data maker! Bio-data maker for job - Online marriage bio-data, Biodata format for Job, Cover page design, School, College project front page. Easy to create, easy to use, fully customizable, with elegantly attractive designed.
@biodatamakeronline @BMakerInfo @biodatamakerjobnews @biodatamakeronline - instagram

Copyright design and developed by @ www.BiodataMaker.Info