1. Difference between SQL, MySQL & PostgreSQL?
SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. MySQL and PostgreSQL are two different types of SQL databases.
– SQL: Language for database management.
– MySQL: An open-source relational database management system (RDBMS).
– PostgreSQL: An advanced open-source RDBMS with a focus on extensibility and standards compliance.

2. What are the different types of SQL commands?
– DDL (Data Definition Language): CREATE, ALTER, DROP
– DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
– DCL (Data Control Language): GRANT, REVOKE
– TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3. What is a primary key?
A primary key is a unique identifier for each record in a table. It must contain unique values and cannot be NULL.

4. What is a foreign key?
A foreign key is a field in one table that uniquely identifies a row in another table. It establishes a relationship between the two tables.

5. Explain the difference between INNER JOIN and LEFT JOIN.
– INNER JOIN: Returns only matching rows from both tables.
– LEFT JOIN: Returns all rows from the left table and matched rows from the right table, with NULL for no matches.

6. How do you use the GROUP BY clause?
The GROUP BY clause is used to arrange identical data into groups, often with aggregate functions like COUNT, SUM, AVG.

7. What is the Basic difference between WHERE and HAVING clauses?
– WHERE: Filters records before grouping.
– HAVING: Filters groups after grouping.

8. What is an index and why is it used?
An index is a database object that improves the speed of data retrieval operations on a table.

9. What is a subquery and when would you use one?
A subquery is a query nested inside another query. It is used to perform operations that need data from multiple queries.

10. How can you improve the performance of a SQL query?
– Use indexes
– Limit columns in SELECT
– Use WHERE clauses early
– Avoid complex joins/subqueries

11. What is a view in SQL?
A view is a virtual table based on the result set of a SQL query. It simplifies complex queries and improves security.

12. Explain the difference between DELETE and TRUNCATE.
– DELETE: Removes rows based on a condition.
– TRUNCATE: Removes all rows.

13. What is normalization and why is it important?
Normalization is the process of organizing data to reduce redundancy and improve data integrity.

14. What are constraints in SQL?
Constraints are rules applied to columns to enforce data integrity.
– NOT NULL: Ensures a column cannot have a NULL value.
– UNIQUE: Ensures all values in a column are unique.
– PRIMARY KEY: Uniquely identifies each record in a table.
– FOREIGN KEY: Uniquely identifies a row in another table.
– CHECK: Ensures all values in a column satisfy a specific condition.
– DEFAULT: Sets a default value for a column when no value is specified.

15. How do you handle NULL values in SQL?
Use functions like IS NULL, IS NOT NULL, COALESCE, and NVL to handle NULL values in queries.

16. What is a UNION operator?
The UNION operator combines the result sets of two SELECT statements and removes duplicates.

17. Explain the difference between UNION and UNION ALL.
– UNION: Removes duplicates.
– UNION ALL: Includes duplicates.

18. What is a trigger in SQL?
A trigger is a set of actions automatically executed in response to certain events on a table/view (INSERT, UPDATE, DELETE).

19. How do you join three or more tables in SQL?
Use multiple JOIN clauses in a single query.

20. What is the difference between CHAR and VARCHAR?
– CHAR: Fixed-length character data type.
– VARCHAR: Variable-length character data type.

21. What is the use of the DISTINCT keyword?
The DISTINCT keyword removes duplicate rows from a result set.

22. What is a composite key?
A composite key is a primary key made up of two or more columns.

23. What is the purpose of the CASE statement?
The CASE statement implements conditional logic in SQL queries.

24. What is an alias in SQL?
An alias is a temporary name for a table or column.

25. How do you fetch the top N records from a table?
Use the LIMIT clause (MySQL, PostgreSQL) or the TOP clause (SQL Server).

26. What is the difference between an INNER JOIN and an OUTER JOIN?
– INNER JOIN: Returns matching rows from both tables.
– OUTER JOIN: Returns matching rows and non-matching rows from one or both tables (LEFT, RIGHT, FULL).

27. Explain the use of the LIKE operator in SQL.
The LIKE operator is used for pattern matching in WHERE clauses.

28. What is the purpose of the ORDER BY clause?
The ORDER BY clause sorts the result set by specified columns.

29. How can you delete duplicate rows in SQL?
Use the ROW_NUMBER() function with a CTE (Common Table Expression) to identify and delete duplicates.

30. What is a self-join and how is it used?
A self-join is a join where a table is joined with itself. It is useful for querying hierarchical data.