SQL Interview Preparation

personHelpBox
August 08, 2024
0

  • What is SQL?

    SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases.

  • What is a primary key?

    A primary key is a column (or combination of columns) that uniquely identifies each row in a table.

    CREATE TABLE Employees (
    EmployeeID int NOT NULL PRIMARY KEY,
    FirstName varchar(255),
    LastName varchar(255)
);

  • What is a foreign key?

    A foreign key is a column that creates a relationship between two tables.

    CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    EmployeeID int,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

  • How do you retrieve all records from a table?

    SELECT * FROM table_name;

  • How do you select unique records from a table?

    SELECT DISTINCT column1, column2 FROM table_name;

  • What is a JOIN? Explain the different types of JOINs.

    A JOIN clause is used to combine rows from two or more tables based on a related column.

    • INNER JOIN: Returns records with matching values in both tables.
    • LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and the matched records from the right table.
    • RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and the matched records from the left table.
    • FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either left or right table.
    SELECT Orders.OrderID, Employees.FirstName
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID;

  • What is a subquery?

    A subquery is a query nested inside another query.

    SELECT * FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Orders WHERE OrderDate = '2023-01-01');

  • What is the difference between WHERE and HAVING clauses?

    • WHERE: Filters records before any groupings are made.
    • HAVING: Filters records after the groupings are made.
    SELECT COUNT(EmployeeID), Department
FROM Employees
GROUP BY Department
HAVING COUNT(EmployeeID) > 5;

  • What is the SQL CASE statement?

    The CASE statement is used to create different outputs based on conditions.

    SELECT EmployeeID,
    CASE
        WHEN Salary > 50000 THEN 'High'
        WHEN Salary > 30000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryCategory
FROM Employees;

  • What are aggregate functions?

    Aggregate functions perform a calculation on a set of values and return a single value.

    • SUM(): Returns the total sum of a numeric column.
    • AVG(): Returns the average value of a numeric column.
    • COUNT(): Returns the number of rows.
    • MIN(): Returns the smallest value.
    • MAX(): Returns the largest value.
    SELECT AVG(Salary) FROM Employees;

  • What is a stored procedure?

    A stored procedure is a set of SQL statements that can be executed on the database.

    CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
    SELECT * FROM Employees;
END;

  • What is a trigger?

    A trigger is a set of SQL statements that automatically execute in response to certain events on a particular table.

    CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    PRINT 'Record inserted into Employees table';
END;

  • How do you create an index?

    An index is used to speed up the performance of queries.

    CREATE INDEX idxLastName
ON Employees (LastName);

  • What is normalization? Explain the different normal forms.

    Normalization is the process of organizing data to reduce redundancy.

    • 1NF: No repeating groups.
    • 2NF: 1NF + no partial dependency.
    • 3NF: 2NF + no transitive dependency.
    • BCNF: 3NF + every determinant is a candidate key.

  • What is denormalization?

    Denormalization is the process of combining normalized tables into larger tables to improve read performance.

  • Retrieve the names of employees who work in the 'Sales' department.

    SELECT FirstName, LastName
FROM Employees
WHERE Department = 'Sales';

  • List the total number of orders placed by each employee.

    SELECT EmployeeID, COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY EmployeeID;

  • Update the salary of an employee with ID 5 to 60000.

    UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 5;

  • Delete all records from the Orders table where OrderDate is before '2023-01-01'.

    DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

  • Create a new table called 'Departments' with columns 'DepartmentID' and 'DepartmentName'.

    CREATE TABLE Departments (
    DepartmentID int NOT NULL PRIMARY KEY,
    DepartmentName varchar(255) NOT NULL
);

  • Retrieve the second highest salary from the Employees table.

    SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

  • Find all employees who have placed more than 10 orders.

    SELECT EmployeeID, COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(OrderID) > 10;

  • Get the names of employees who have not placed any orders.

    SELECT FirstName, LastName
FROM Employees
WHERE EmployeeID NOT IN (SELECT EmployeeID FROM Orders);

  • List the departments along with the number of employees in each department.

    SELECT Department, COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY Department;

  • Find the average salary of employees in each department.

    SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

  • Create a function to calculate the total sales for an employee.

    CREATE FUNCTION GetTotalSales (@EmployeeID int)
RETURNS int
AS
BEGIN
    DECLARE @TotalSales int;
    SELECT @TotalSales = SUM(OrderAmount)
    FROM Orders
    WHERE EmployeeID = @EmployeeID;
    RETURN @TotalSales;
END;

  • Write a query to retrieve the current date and time.

    SELECT GETDATE();

  • What is a view and how do you create one?

    A view is a virtual table based on the result of a SELECT query.

    CREATE VIEW EmployeeOrders AS
SELECT Employees.FirstName, Employees.LastName, Orders.OrderID, Orders.OrderDate
FROM Employees
JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;

  • Explain the use of the COALESCE function.

    COALESCE returns the first non-null value in a list of arguments.

    SELECT COALESCE(NULL, NULL, 'Default Value', 'Another Value');

  • Write a query to find employees whose first name starts with 'A'.

    SELECT * FROM Employees
WHERE FirstName LIKE 'A%';

  • Explain the difference between clustered and non-clustered indexes.

    • Clustered Index: Sorts and stores the data rows in the table based on their key values.
    • Non-Clustered Index: Contains a pointer to the data in a table, which is stored separately from the table.

  • How do you optimize a query?

    • Use proper indexing.
    • Avoid using SELECT *.
    • Use WHERE clauses to filter data.
    • Avoid unnecessary joins.
    • Use appropriate data types.

  • Explain the EXPLAIN statement.

    EXPLAIN provides information about how SQL statements are executed by the database.

    EXPLAIN SELECT * FROM Employees;

  • What is a transaction?

    A transaction is a sequence of one or more SQL operations treated as a single unit of work.

    BEGIN TRANSACTION;
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;
COMMIT;

  • How do you rollback a transaction?

    BEGIN TRANSACTION;
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;
ROLLBACK;

  • What are the different types of constraints in SQL?

    • NOT NULL: Ensures a column cannot have a NULL value.
    • UNIQUE: Ensures all values in a column are unique.
    • PRIMARY KEY: Uniquely identifies each record in a table.
    • FOREIGN KEY: Ensures referential integrity of the data.
    • CHECK: Ensures that all values in a column satisfy a specific condition.
    • DEFAULT: Sets a default value for a column when no value is specified.

  • Explain the difference between DELETE and TRUNCATE.

    • DELETE: Removes rows one at a time and can use WHERE clause. It is slower and logs each row deletion.
    • TRUNCATE: Removes all rows from a table without logging individual row deletions. It is faster and cannot use WHERE clause.

  • What is a UNION operator?

    UNION combines the result set of two or more SELECT statements and removes duplicate rows.

    SELECT FirstName FROM Employees
UNION
SELECT FirstName FROM Managers;

  • What is the difference between UNION and UNION ALL?

    • UNION: Removes duplicate rows.
    • UNION ALL: Includes duplicate rows.

  • How do you create a temporary table?

    CREATE TEMPORARY TABLE TempEmployees (
    EmployeeID int,
    FirstName varchar(255),
    LastName varchar(255)
);

  • What is CTE (Common Table Expression)?

    CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

    WITH EmployeeCTE AS (
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
)
SELECT * FROM EmployeeCTE;

  • Explain the RANK() function.

    RANK() provides a rank for each row within the partition of a result set.

    SELECT EmployeeID, Salary,
       RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

  • What is a cursor in SQL?

    A cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row.

    DECLARE cursor_name CURSOR FOR
SELECT FirstName, LastName FROM Employees;
OPEN cursor_name;
FETCH NEXT FROM cursor_name;
CLOSE cursor_name;
DEALLOCATE cursor_name;

  • How do you handle NULL values in SQL?

    • IS NULL: Checks if a value is NULL.
    • IS NOT NULL: Checks if a value is not NULL.
    • COALESCE(): Returns the first non-null value.

    SELECT * FROM Employees WHERE MiddleName IS NULL;

  • Explain the use of the ISNULL function.

    ISNULL replaces NULL with a specified replacement value.

    SELECT ISNULL(MiddleName, 'N/A') FROM Employees;

  • How do you create a backup of a database?

    BACKUP DATABASE database_name
TO DISK = 'path_to_backup_file';

  • How do you restore a database from a backup?

    RESTORE DATABASE database_name
FROM DISK = 'path_to_backup_file';

  • Write a query to find the employees who joined in the last 30 days.

    SELECT * FROM Employees
WHERE JoinDate >= DATEADD(day, -30, GETDATE());

  • What is partitioning in SQL?

    Partitioning divides a table into smaller, more manageable pieces, but still allows the table to be queried as a whole.

  • Explain database normalization and its advantages.

    Normalization organizes a database into tables and columns to reduce redundancy and improve data integrity. Advantages include:

    • Reduced data redundancy.
    • Improved data integrity.
    • Efficient data organization.
Tags

You Might Like

Show more
interview-question

Post a Comment

0Comments

Post a Comment (0)
Share to other apps
Copy Post Link