Join our community to see how developers are using Workik AI everyday.
Q6: What is a foreign key, and why is it used?
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The purpose of the foreign key is to ensure referential integrity of the data, meaning that the foreign key values in the referencing table must match primary key values in the referenced table.
Uses:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Q7: What is the difference between DELETE, TRUNCATE, and DROP commands?
These commands are used to remove data or database objects but differ in their operations and consequences:
DELETE FROM table_name WHERE condition;
TRUNCATE TABLE table_name;
DROP TABLE table_name;
Q8: What are stored procedures, and what are their benefits?
Stored procedures are precompiled collections of one or more SQL statements stored under a name and processed as a unit. They can accept parameters, perform operations, and return results.
Benefits:
CREATE PROCEDURE GetEmployeeDetails
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;
Q9: Explain ACID properties in the context of a database transaction.
ACID properties ensure reliable processing of database transactions and maintain data integrity even in the event of failures. The properties are:
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
Q10: What is a common table expression (CTE), and how is it used?
A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve readability and simplify complex queries by breaking them into simpler parts.
Usage:
WITH EmployeeCTE AS (
SELECT EmployeeID, FirstName, LastName, ManagerID
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID
FROM Employees e
INNER JOIN EmployeeCTE ecte ON e.ManagerID = ecte.EmployeeID
)
SELECT * FROM EmployeeCTE;
Request question
Please fill in the form below to submit your question.
Q11: What is a view in SQL, and why would you use it?
A view is a virtual table that is based on the result set of an SQL query. It contains rows and columns, just like a real table, and the fields in a view are fields from one or more real tables in the database.
Uses of Views:
CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE Department = 'Sales';
Q12: What is a trigger in SQL, and when would you use it?
A trigger is a special kind of stored procedure that automatically executes in response to certain events on a particular table or view. Triggers can be used to enforce business rules, maintain audit trails, and synchronize tables.
Types of Triggers:
CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
INSERT INTO EmployeeAudit(EmployeeID, ChangeDate)
SELECT EmployeeID, GETDATE()
FROM inserted;
END;
Uses:
Q13: Explain the concept of a subquery in SQL.
A subquery, also known as an inner query or nested query, is a query within another SQL query. The result of the subquery is used by the outer query. Subqueries can be used in various SQL clauses like SELECT, INSERT, UPDATE, DELETE, and WHERE.
Types of Subqueries:
SELECT * FROM Employees
WHERE EmployeeID = (SELECT ManagerID FROM Departments WHERE DepartmentID = 1);
SELECT * FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');
SELECT e1.EmployeeID, e1.FirstName
FROM Employees e1
WHERE e1.Salary > (SELECT AVG(e2.Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);
Q14: What is the difference between UNION and UNION ALL?
UNION: Combines the result sets of two or more SELECT queries into a single result set, excluding duplicate rows. The columns in the SELECT statements must have the same number and compatible data types.
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;
UNION ALL: Combines the result sets of two or more SELECT queries into a single result set, including all duplicate rows.
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;
Key Differences:
Q15: What are aggregate functions in SQL? Provide examples.
Aggregate functions perform a calculation on a set of values and return a single value. They are commonly used with the GROUP BY clause in SQL queries to group rows that have the same values in specified columns.
Common Aggregate Functions:
SELECT COUNT(*) FROM Employees;
SELECT SUM(Salary) FROM Employees;
SELECT AVG(Salary) FROM Employees;
SELECT MIN(Salary) FROM Employees;
SELECT MAX(Salary) FROM Employees;
Request question
Please fill in the form below to submit your question.
Q16: What is a materialized view, and how is it different from a regular view?
A materialized view is a database object that contains the results of a query and stores them physically. Unlike a regular view, which is a virtual table created dynamically upon each access, a materialized view stores data and can be refreshed periodically.
Differences:
Q17: What are user-defined functions (UDFs) in SQL, and how are they different from stored procedures?
User-defined functions (UDFs) are routines that accept parameters, perform an action, and return the result of that action as a value. UDFs can be scalar (return a single value) or table-valued (return a table).
Differences from Stored Procedures:
Q18: What is database sharding, and why is it used?
Database sharding is a method of distributing data across multiple databases or servers to improve performance and scalability. Each shard is a separate database that contains a subset of the data.
Uses:
Example: If a customer database is sharded by region, one shard might contain data for North America, while another shard contains data for Europe.
Q19: What is the difference between HAVING and WHERE clauses in SQL?
The HAVING and WHERE clauses are both used to filter records in SQL, but they operate in different contexts and stages of query execution.
WHERE Clause:
SELECT * FROM Employees WHERE Salary > 50000;
HAVING Clause:
SELECT Department, COUNT(*) as EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;
Key Differences:
Q20: What is a surrogate key in SQL?
A surrogate key is an artificial key that is used as a unique identifier for each row in a table. It is not derived from the data in the table and has no business meaning. Surrogate keys are typically auto-incremented integers.
Uses:
CREATE TABLE Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
Request question
Please fill in the form below to submit your question.
SELECT FirstName, LastName
FROM Employees
WHERE HireDate > '01-01-2023';
The date format is incorrect and should be in the correct SQL date format (YYYY-MM-DD).
SELECT FirstName, LastName
FROM Employees
WHERE HireDate > '2023-01-01';
SELECT *
FROM Orders
WHERE CustomerID = 12345;
Answer: To improve performance, you can create an index on the CustomerID column.
CREATE INDEX idx_customerid ON Orders(CustomerID);
Q3: Write a SQL query to find the third highest salary from the Employees table.
SELECT Salary
FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rank
FROM Employees
) AS RankedSalaries
WHERE rank = 3;
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE EmployeeID IN (SELECT ManagerID FROM Employees);
The output will be a list of employees who are also managers. It includes the EmployeeID, FirstName, and LastName of those employees whose EmployeeID matches any ManagerID in the Employees table.
SELECT E.EmployeeID, E.FirstName, E.LastName, D.DepartmentName
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.DepartmentID;
Ensure that there are indexes on the join columns to optimize performance.
CREATE INDEX idx_employee_departmentid ON Employees(DepartmentID);
CREATE INDEX idx_department_departmentid ON Departments(DepartmentID);
DELETE FROM Employees
WHERE LastLogin < DATEADD(YEAR, -1, GETDATE());
The query is correct. Ensure that the LastLogin column is of date type and contains valid dates. If using SQL Server, the function is correct, but if using another SQL dialect, you might need to adjust the date function. For example, in MySQL, you would use ‘DATE_SUB’.
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
ORDER BY AvgSalary DESC;
Using a Common Table Expression (CTE) named DepartmentSalaries improves readability by breaking the query into two parts: one that calculates the average salary for each department and one that selects and orders the results. This separation makes the query easier to understand and maintain.
WITH DepartmentSalaries AS (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
)
SELECT DepartmentID, AvgSalary
FROM DepartmentSalaries
ORDER BY AvgSalary DESC;
SELECT StoreID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY StoreID
ORDER BY TotalSales DESC;
The query is correct. Ensure that the SalesAmount column does not contain NULL values and that the data types are consistent.
SELECT StoreID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY StoreID
ORDER BY TotalSales DESC;
SELECT MONTH(SaleDate) AS SaleMonth, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY MONTH(SaleDate);
Create an index on the SaleDate column to improve performance.
CREATE INDEX idx_saledate ON Sales(SaleDate);
SELECT E1.EmployeeID, E1.FirstName, E1.LastName
FROM Employees E1
JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID
WHERE E1.LastName = E2.LastName;
Request question
Please fill in the form below to submit your question.