Below are 25 basic SQL interview questions and answers tailored for freshers. These questions cover fundamental SQL concepts and are easy to understand.
1. What is SQL?
Answer:
SQL (Structured Query Language) is a standard language used to communicate with and manipulate databases. It is used to perform tasks like querying, updating, and managing data in relational database systems.
2. What are the different types of SQL commands?
Answer:
SQL commands are categorized into five types:
- DDL (Data Definition Language): Commands like
CREATE,ALTER,DROP(used to define and modify database structures). - DML (Data Manipulation Language): Commands like
SELECT,INSERT,UPDATE,DELETE(used to manipulate data). - DCL (Data Control Language): Commands like
GRANT,REVOKE(used to control access to data). - TCL (Transaction Control Language): Commands like
COMMIT,ROLLBACK(used to manage transactions). - DQL (Data Query Language): The
SELECTcommand (used to query data).
3. What is a database?
Answer:
A database is an organized collection of data stored and accessed electronically. It allows efficient management, retrieval, and manipulation of data.
4. What is a table in SQL?
Answer:
A table is a collection of related data organized in rows (records) and columns (fields). Each table has a unique name and stores data in a structured format.
5. What is a primary key?
Answer:
A primary key is a column (or a set of columns) that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.
Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50)
);6. What is a foreign key?
Answer:
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 relationship between two tables.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
StudentID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);7. What is the difference between DELETE and TRUNCATE?
Answer:
DELETE: Removes specific rows from a table. It can be rolled back and fires triggers.TRUNCATE: Removes all rows from a table. It cannot be rolled back and does not fire triggers.
8. What is the difference between DROP and TRUNCATE?
Answer:
DROP: Deletes the entire table structure and data. The table no longer exists.TRUNCATE: Removes all rows but retains the table structure.
9. What is the difference between WHERE and HAVING?
Answer:
WHERE: Filters rows before grouping (used withSELECT,UPDATE,DELETE).HAVING: Filters groups after grouping (used withGROUP BY).
Example:
SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;10. What is the difference between INNER JOIN and LEFT JOIN?
Answer:
INNER JOIN: Returns only the matching rows from both tables.LEFT JOIN: Returns all rows from the left table and matching rows from the right table. If no match,NULLvalues are returned.
Example:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;11. What is a self-join?
Answer:
A self-join is a join where a table is joined with itself. It is useful for querying hierarchical data or comparing rows within the same table.
Example:
SELECT A.EmployeeName, B.ManagerName
FROM Employees A, Employees B
WHERE A.ManagerID = B.EmployeeID;12. What is a subquery?
Answer:
A subquery is a query nested inside another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements.
Example:
SELECT Name
FROM Students
WHERE StudentID IN (SELECT StudentID FROM Enrollments WHERE CourseID = 101);13. What is normalization?
Answer:
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, related tables.
14. What are the different normal forms?
Answer:
The main normal forms are:
- 1NF (First Normal Form): Each column contains atomic values, and each row is unique.
- 2NF (Second Normal Form): Meets 1NF and removes partial dependencies.
- 3NF (Third Normal Form): Meets 2NF and removes transitive dependencies.
15. What is an index?
Answer:
An index is a database object that improves the speed of data retrieval operations on a table. It works like a book index, allowing faster searches.
Example:
CREATE INDEX idx_name ON Students(Name);16. What is a view?
Answer:
A view is a virtual table created by a query. It does not store data but displays data from one or more tables.
Example:
CREATE VIEW StudentView AS
SELECT Name, Age FROM Students WHERE Age > 18;17. What is a stored procedure?
Answer:
A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. It improves performance and reusability.
Example:
CREATE PROCEDURE GetStudents AS
SELECT * FROM Students;18. What is a trigger?
Answer:
A trigger is a special type of stored procedure that automatically executes in response to specific events (e.g., INSERT, UPDATE, DELETE) on a table.
Example:
CREATE TRIGGER trg_after_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Logs (Message) VALUES ('New student added');
END;19. What is the difference between UNION and UNION ALL?
Answer:
UNION: Combines the results of two queries and removes duplicates.UNION ALL: Combines the results of two queries without removing duplicates.
20. What is the difference between GROUP BY and ORDER BY?
Answer:
GROUP BY: Groups rows with the same values into summary rows (used with aggregate functions likeCOUNT,SUM).ORDER BY: Sorts the result set in ascending or descending order.
21. What is the difference between CHAR and VARCHAR?
Answer:
CHAR: Fixed-length character data (pads with spaces if the data is shorter than the defined length).VARCHAR: Variable-length character data (uses only the required space).
22. What is the difference between IN and BETWEEN?
Answer:
IN: Checks if a value matches any value in a list.BETWEEN: Checks if a value falls within a range (inclusive).
Example:
SELECT * FROM Students WHERE Age BETWEEN 18 AND 25;
SELECT * FROM Students WHERE DepartmentID IN (101, 102);23. What is the difference between IS NULL and IS NOT NULL?
Answer:
IS NULL: Checks if a column containsNULLvalues.IS NOT NULL: Checks if a column does not containNULLvalues.
24. What is a transaction in SQL?
Answer:
A transaction is a sequence of SQL operations performed as a single unit of work. It ensures data integrity using COMMIT (save changes) and ROLLBACK (undo changes).
Example:
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;25. What is the difference between CASCADE and RESTRICT in foreign keys?
Answer:
CASCADE: Automatically updates or deletes related rows in the child table when the parent table is updated or deleted.RESTRICT: Prevents updates or deletions in the parent table if related rows exist in the child table.
These questions and answers cover the fundamental concepts of SQL and are commonly asked in interviews for freshers. Make sure to practice writing queries to reinforce your understanding!
*************** ALL THE BEST *****************
Visit JaganInfo youtube channel for more valuable content https://www.youtube.com/@jaganinfo