These questions cover a wide range of SQL concepts and can be used to assess different levels of expertise in SQL during an interview.
1. What is SQL?
– SQL (Structured Query Language) is a standard language for managing and manipulating relational databases.
2. What are the different types of SQL commands?
– SQL commands are broadly categorized into DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), and DCL (Data Control Language).
3. Explain the difference between SQL and MySQL.
– SQL is a standard language for managing relational databases, whereas MySQL is an open-source relational database management system (RDBMS) that uses SQL.
4. What is a primary key in SQL?
– A primary key uniquely identifies each record in a table and must contain unique values. It cannot have NULL values.
5. What is a foreign key in SQL?
– A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a link between the two tables.
6. What is a unique key in SQL?
– A unique key ensures that all values in a column are unique and not NULL. Unlike primary keys, a table can have multiple unique keys.
7. Differentiate between CHAR and VARCHAR data types.
– `CHAR` is fixed-length and stores blank spaces up to its defined length, while `VARCHAR` is variable-length and only stores actual characters entered.
8. Explain the ACID properties of a DBMS.
– ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably.
9. What is normalization and why is it important?
– Normalization is the process of organizing data to minimize redundancy and dependency by dividing large tables into smaller tables and defining relationships. It reduces data anomalies and improves database efficiency.
10. What is denormalization?
– Denormalization is the process of adding redundant data back into a normalized database to improve read performance by reducing the number of joins needed.
11. What are the different types of joins in SQL?
– Joins in SQL include INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).
12. Explain INNER JOIN.
– INNER JOIN returns records that have matching values in both tables based on the join condition.
13. Explain LEFT JOIN and RIGHT JOIN.
– LEFT JOIN returns all records from the left table and the matched records from the right table. RIGHT JOIN does the opposite—it returns all records from the right table and the matched records from the left table.
14. What is a full outer join?
– FULL OUTER JOIN returns all records when there is a match in either left or right table records.
15. How do you find duplicate records in a table?
– To find duplicate records, you can use a query like:
SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1;
16. How do you remove duplicate records in a table?
– To remove duplicate records, you can create a new table with distinct records or use the `DELETE` statement with a self-join.
17. What is the difference between UNION and UNION ALL?
– `UNION` removes duplicate records, while `UNION ALL` includes all records, including duplicates.
18. What is the difference between WHERE and HAVING clauses?
– `WHERE` is used to filter records before any groupings are made, while `HAVING` is used to filter records after groupings have been made.
19. What is the purpose of the GROUP BY clause?
– The `GROUP BY` clause is used to group rows that have the same values into summary rows, such as “find the number of customers in each city.”
20. How do you perform a subquery in SQL?
– A subquery is a query nested within another query. For example:
SELECT column1, column2 FROM table1 WHERE column1 IN (SELECT column1 FROM table2);
21. What is a correlated subquery?
– A correlated subquery refers to a subquery that refers to a column from the outer query.
22. Explain the difference between clustered and non-clustered indexes.
– A clustered index determines the order in which rows in a table are physically stored, whereas a non-clustered index is a separate structure that stores a sorted list of references to the table rows.
23. What is the purpose of the INDEX keyword in SQL?
– The `INDEX` keyword is used to create an index on a table, which helps in faster retrieval of records from the table.
24. How do you add a new column to an existing table?
– You can use the `ALTER TABLE` statement:
ALTER TABLE table_name ADD column_name datatype;
25. What is the difference between TRUNCATE and DELETE statements?
– `TRUNCATE` removes all records from a table without logging the individual row deletions, while `DELETE` removes specific rows based on a condition.
26. How do you update data in a table using SQL?
– Use the `UPDATE` statement:
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
27. What is a stored procedure?
– A stored procedure is a prepared SQL code that can be saved and reused. It allows you to group and execute multiple SQL statements.
28. How do you create a stored procedure in SQL?
– Example of creating a stored procedure:
CREATE PROCEDURE procedure_name AS BEGIN SQL statements END;
29. What is a trigger in SQL?
– A trigger is a special type of stored procedure that automatically runs when an event occurs in the database.
30. Explain the difference between a trigger and a stored procedure.
– A stored procedure is called explicitly, whereas a trigger is automatically executed when a specified event occurs.
31. What is a view in SQL and why do we use it?
– A view is a virtual table based on the result set of a SELECT statement. It simplifies complex queries and provides a layer of security by restricting access to certain columns.
32. How do you create a view in SQL?
– Example of creating a view:
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
33. Explain the difference between a function and a stored procedure.
– A function returns a value, whereas a stored procedure does not necessarily return a value but can perform operations.
34. What are aggregate functions in SQL?
– Aggregate functions operate on a set of values and return a single value. Examples include `SUM`, `AVG`, `COUNT`, `MIN`, and `MAX`.
35. List some aggregate functions available in SQL.
– `SUM`, `AVG`, `COUNT`, `MIN`, and `MAX`.
36. How do you handle NULL values in SQL?
– You can use functions like `COALESCE`, `IS NULL`, or `IS NOT NULL` to handle NULL values appropriately.
37. What is the purpose of the COALESCE function?
– The `COALESCE` function returns the first non-NULL value among its arguments.
38. How do you order the results of a query in SQL?
– Use the `ORDER BY` clause:
SELECT column1, column2 FROM table_name ORDER BY column1 ASC; -- or DESC for descending order
39. Explain the concept of data integrity.
– Data integrity ensures the accuracy, consistency, and reliability of data stored in a database.
40. What is a self-join?
– A self-join is a regular join, but the table is joined with itself.
41. How do you perform pagination in SQL?
– Use the `LIMIT` and `OFFSET` clauses in combination with `ORDER BY` to paginate results.
42. Explain the concept of a transaction in SQL.
– A transaction is a sequence of SQL operations that are executed as a single unit. It ensures data consistency by allowing all operations to either complete successfully or fail as a whole.
43. What is the purpose of the COMMIT statement?
– The `COMMIT` statement saves all the changes made by the transaction permanently to the database.
44. What is the purpose of the ROLLBACK statement?
– The `ROLLBACK` statement undoes all the changes made by the transaction since the last `COMMIT` or `ROLLBACK` statement.
45. How do you handle errors in SQL transactions?
– Use `TRY…CATCH` blocks in SQL Server or `BEGIN…EXCEPTION` blocks in Oracle to handle errors in transactions.
46. What is the difference between OLTP and OLAP?
– OLTP (Online Transaction Processing) involves managing and processing transaction-oriented applications, while OLAP (Online Analytical Processing) deals with processing and analyzing large-scale data.
47. Explain the concept of a foreign key constraint.
– A foreign key constraint enforces referential
integrity between two tables by ensuring that the values in one table’s foreign key column match the values in another table’s primary key column.
48. How do you enforce referential integrity in SQL?
– Use foreign key constraints between related tables to enforce referential integrity.
49. What is a common table expression (CTE) in SQL?
– A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
50. Explain the concept of data warehousing in SQL.
– Data warehousing involves collecting and managing data from various sources to provide meaningful business insights through analysis and reporting.
These questions cover a wide range of SQL concepts and can be used to assess different levels of expertise in SQL during an interview.