Basis Questions:
Q1. What is SQL?
SQL (Structured Query Language) is a standard language used to communicate with and manipulate databases. It is used to perform tasks such as querying data, updating data, and managing database structures.
SELECT * FROM Employees;
Q2. What is a primary key?
A primary key is a column (or a set of columns) that uniquely identifies each row in a table. Primary keys must contain unique values and cannot contain NULLs.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100)
);Q3. What is a foreign key?
A foreign key is a column (or a set of columns) that establishes a relationship between two tables. It is a reference to the primary key in another table, ensuring referential integrity.
img src: https://cloud.google.com/spanner/docs/foreign-keys/how-to
There are two foreign key relationships between these tables:
- A foreign key relationship is defined between the
Orderstable and theCustomerstable to ensure that an order can’t be created unless there is a corresponding customer. - A foreign key relationship between the
Orderstable and theProductstable ensures that an order can’t be created for a product that doesn’t exist.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
EmployeeID INT,
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
Q4. What are the different types of SQL commands?
- The main types are:
- 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.
-- DDL Example
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
-- DML Example
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (1, 'HR');
-- DCL Example
GRANT SELECT ON Departments TO user1;
-- TCL Example
COMMIT;
Q5. What is the difference between DELETE and TRUNCATE?
DELETE removes rows one at a time and can include a WHERE clause to filter rows. It logs individual row deletions and can be rolled back. TRUNCATE removes all rows from a table, is faster, and cannot be rolled back in most databases.
-- DELETE Example
DELETE FROM Employees WHERE EmployeeID = 1;
-- TRUNCATE Example
TRUNCATE TABLE Employees;
Intermediate Questions
Q1. What is a join? Name its types.
A join is an operation that combines rows from two or more tables based on a related column. Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.
-- INNER JOIN Example
SELECT Employees.Name, Orders.OrderID
FROM Employees
INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;
Q2. What is an index?
An index is a database object that improves the speed of data retrieval operations on a table by providing quick access to rows.
CREATE INDEX idx_employee_name ON Employees(Name);
Q3. What is normalization?
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity by dividing large tables into smaller, related tables.
-- 1NF Example
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
ContactNumber VARCHAR(15)
);
-- 2NF Example (Assuming 'Department' is another table)
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
ALTER TABLE Employees
ADD DepartmentID INT,
ADD FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);
Q4. What is a view?
A view is a virtual table based on the result set of an SQL query. It provides a way to simplify complex queries, encapsulate logic, and enhance security by restricting access to specific data.
CREATE VIEW EmployeeOrders AS
SELECT Employees.Name, Orders.OrderID
FROM Employees
JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;
Q5. What is a subquery?
A subquery is a query nested within another query. It is used to perform operations that require multiple steps or complex criteria.
SELECT Name
FROM Employees
WHERE EmployeeID IN (
SELECT EmployeeID
FROM Orders
WHERE OrderDate > '2024-01-01'
);Q6. Explain the different normal forms.
- The normal forms include:
- 1NF: Ensures each column contains atomic values.
- 2NF: Achieves 1NF and ensures all non-key attributes are fully dependent on the primary key.
- 3NF: Achieves 2NF and ensures all non-key attributes are not transitively dependent on the primary key.
-- 1NF Example
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
Course VARCHAR(100)
);
-- 2NF Example
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100)
);
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
-- 3NF Example
-- Additional table for Student Address
CREATE TABLE Addresses (
AddressID INT PRIMARY KEY,
StudentID INT,
Address VARCHAR(255),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
Q7. What is denormalization?
Denormalization is the process of combining normalized tables into larger tables to improve read performance, at the cost of write performance and storage efficiency.
-- Combining tables for faster read access
CREATE TABLE StudentInfo (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
CourseID INT,
CourseName VARCHAR(100),
Address VARCHAR(255)
);
Q8. What is a stored procedure?
A stored procedure is a precompiled collection of SQL statements stored in the database that can be executed as a single unit to perform a specific task.
CREATE PROCEDURE GetEmployeeOrders
AS
BEGIN
SELECT Employees.Name, Orders.OrderID
FROM Employees
JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;
END;
Q9. What is a trigger?
A trigger is a set of SQL statements that automatically execute in response to certain events on a particular table or view, such as INSERT, UPDATE, or DELETE.
CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
PRINT 'A new employee record has been inserted.';
END;
Advanced Questions:
Q1. What is a CTE (Common Table Expression)?
A CTE is a temporary result set defined within the execution scope of a single SQL statement, often used to simplify complex queries and improve readability.
WITH EmployeeCTE AS (
SELECT EmployeeID, Name
FROM Employees
WHERE DepartmentID = 1
)
SELECT * FROM EmployeeCTE;
Q2. Explain window functions and their uses.
Window functions perform calculations across a set of table rows related to the current row without collapsing the rows into a single output. Examples include ROW_NUMBER(), RANK(), and SUM() OVER().
SELECT Name, Salary,
RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;Q3. What is the ACID property in databases?
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable processing of database transactions.
-- Example illustrating transaction properties
BEGIN TRANSACTION;
INSERT INTO Accounts (AccountID, Balance) VALUES (1, 1000);
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
COMMIT;
Q4. How do you optimize SQL queries?
SQL queries can be optimized by:
- Using indexes appropriately.
- Avoiding SELECT *.
- Using joins instead of subqueries where possible.
- Analyzing execution plans.
- Avoiding unnecessary calculations or data conversions.
Conclusion
Mastering SQL is a fundamental skill for any aspiring data analyst. It enables you to efficiently manage and manipulate data, which is crucial for making data-driven decisions. The questions covered in this blog should give you a solid foundation for what to expect in SQL interviews and help you prepare effectively.
If you’re eager to deepen your knowledge and gain hands-on experience with SQL, consider enrolling in a comprehensive Data Science Course in Hyderabad. This course covers SQL and other essential tools and techniques, equipping you with the skills needed to excel in the field of data science.
Happy learning and best of luck with your interviews!