CSDT Community

1. What is SQL?

• SQL (Structured Query Language) is a standard language for accessing and manipulating databases.

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 the difference between DELETE, TRUNCATE, and DROP?

• DELETE: Removes rows from a table based on a condition, can be rolled back.
• TRUNCATE: Removes all rows from a table without logging individual row deletions, can't be rolled back.
• DROP: Deletes the table and its structure from the database.

4. What are constraints in SQL?

• Constraints are rules applied to a table's columns to enforce data integrity. Examples: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK.

5. What is the difference between PRIMARY KEY and UNIQUEkey?

• PRIMARY KEY: A unique identifier for a row, cannot contain NULL values.
• UNIQUE: Ensures all values in a column are different but can contain one NULL value.

6. What is a JOIN? Name different types of JOINs.

• A JOIN is used to combine rows from two or more tables based on a related column.
• INNER JOIN: Returns matching rows from both tables.
• LEFT JOIN: Returns all rows from the left table and matched rows from the right.
• RIGHT JOIN: Returns all rows from the right table and matched rows from the left.
• FULL OUTER JOIN: Returns all rows when there is a match in either table.

7. What is an INDEX? Why is it used?

• An INDEX is used to speed up the retrieval of data from a database. It works like a pointer that helps in faster searching and sorting.

8. What is normalization? Explain different normal forms.

• Normalization is the process of organizing data to reduce redundancy and improve data integrity. Key normal forms:
• 1NF: Removes duplicate columns.
• 2NF: Ensures no partial dependency on a composite primary key.
• 3NF: Removes transitive dependencies.

9. What is denormalization?

• Denormalization is the process of adding redundant data to improve query performance by reducing the number of JOINs needed.

10. What is a VIEW in SQL?

• A VIEW is a virtual table that contains a result set of a stored SQL query. It does not store data but provides a simplified representation of the data.

11. What is a stored procedure?

• A stored procedure is a prepared SQL code that can be saved and reused. It allows you to execute a sequence of SQL commands by calling the procedure.

12. What are triggers in SQL?

• A TRIGGER is a stored procedure that is automatically executed in response to certain events on a table, like INSERT, UPDATE, or DELETE.

13. What is a CURSOR in SQL?

• A CURSOR is used to fetch rows one at a time from a result set. It allows row-by-row processing for tasks like updating or deleting data.

14. What is the difference between HAVING and WHEREclauses?

• WHERE is used to filter rows before grouping, whereas HAVING is used to filter groups after aggregation.

15. Explain the use of the GROUP BY clause.

• GROUP BY is used with aggregate functions (like COUNT(), SUM(), etc.) to group rows that have the same values in specified columns.

16. What is the difference between UNION and UNION ALL?

• UNION: Combines the result sets of two queries, removing duplicates.
• UNION ALL: Combines the result sets of two queries without removing duplicates.

17. What are aggregate functions in SQL?

• Aggregate functions perform a calculation on a set of values and return a single value. Examples: SUM(), COUNT(), MAX(), MIN(), AVG().

18. What is the EXISTS operator used for?

• EXISTS is used to check the existence of rows in a subquery. It returns TRUE if the subquery returns one or more rows.

19. What is the difference between CHAR and VARCHAR?

• CHAR: A fixed-length string. It reserves space for the defined length, even if the actual string is shorter.
• VARCHAR: A variable-length string that uses only the space necessary for the actual string.

20. What is a subquery?

• A subquery is a query within another query. It is used to return data that will be used by the outer query.

21. What is the difference between RANK() and DENSE_RANK()?

• RANK(): Provides rank with gaps in ranking when there are ties.
• DENSE_RANK(): Provides rank without gaps, even when there are ties.

These questions cover a broad range of SQL topicstypically encountered in interviews for Database Programmer roles.