close

Interview Questions for SQL Developers: Common SQL Interview Q&A- Part 2

How to delete data from a table using the DELETE statement in SQL

Answer: Use the DELETE statement to remove data from a table

For example:

DELETE FROM Customers WHERE CustomerID = 1;

 

Explain the concept of an Index and the benefits of using Indexes in SQL

Answer: An Index is a data structure that improves the speed of data retrieval in a database. It is created on one or more columns of a table and helps to reduce the time required for searching and sorting data. The benefits of using Indexes include improved query performance and faster data retrieval.

 

How to use the CREATE TABLE statement to create a new table in SQL

Answer: Use the CREATE TABLE statement to create a new table in the database.

For example:

CREATE TABLE Customers( 
    CustomerID INT PRIMARY KEY,  
    CustomerName VARCHAR(50),  
    ContactName VARCHAR(50),  
    Country VARCHAR(50)  
);

 

How to use the ALTER TABLE statement to add a new column to a table in SQL.

Answer: Use the ALTER TABLE statement to add a new column to an existing table.

For example:

ALTER TABLE Customers ADD Email VARCHAR(100);

 

How to use the DROP TABLE statement to delete a table in SQL

Answer: Use the DROP TABLE statement to remove a table from the database.

For example:

DROP TABLE Customers;

 

Explain how to use the UNION and UNION ALL statements in SQL

Answer:

  • UNION: Combines the results of two or more SELECT queries into a single result set and removes duplicates.
  • UNION ALL: Similar to UNION, but retains duplicate rows.

 

How to use the LIKE statement and special characters in search conditions in SQL

Answer: Use the LIKE statement to perform pattern matching for text search. There are two special characters commonly used with LIKE:

  • %: Represents any string of characters, including zero or more characters.
  • _: Represents a single character.
SELECT * FROM Customers WHERE CustomerName LIKE 'A%';

 

Explain the different data retrieval queries: SELECT, SELECT DISTINCT, SELECT TOP in SQL

Answer:

  • SELECT: Retrieves data from one or more tables.
  • SELECT DISTINCT: Retrieves unique data from a column, removing duplicate values.
  • SELECT TOP: Retrieves a specified number of rows from the query result.
SELECT DISTINCT Country FROM Customers;  
SELECT TOP 10 * FROM Orders;

 

How to use the GROUP BY, HAVING, ORDER BY statements together in SQL

Answer: By combining the GROUP BY, HAVING, ORDER BY statements, we can group data, filter groups, and sort the result.

For example:

SELECT Country, COUNT(*) AS TotalCustomers  
FROM Customers  
GROUP BY Country  
HAVING COUNT(*) > 5  
ORDER BY TotalCustomers DESC;

 

Explain the concept of a transaction and how to use the BEGIN TRANSACTION, COMMIT, ROLLBACK statements in SQL.

Answer: A transaction is a sequence of one or more database operations treated as a single unit. If any of the operations within the transaction fail, the entire transaction is rolled back and all changes are undone.

  • BEGIN TRANSACTION: Starts a new transaction.
  • COMMIT: Saves and confirms the changes made in the transaction to the database.
  • ROLLBACK: Cancels the transaction and undoes any changes made in the transaction
BEGIN TRANSACTION;  
UPDATE Accounts SET Balance = Balance- 100 WHERE AccountID = 123;  
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456;  
COMMIT;