SQL (Structured Query Language) remains one of the most in-demand skills for data analysts, backend developers, and database administrators. It’s the foundation of managing, analyzing, and transforming structured data across relational database systems like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.
This resource, "100+ SQL Coding Interview Questions and Answers," helps recruiters evaluate candidates’ ability to write efficient SQL queries, optimize performance, and handle complex data manipulation tasks.
Whether hiring for Data Engineers, Business Analysts, or Full-Stack Developers, this assessment helps measure:
Using WeCP’s SQL Coding Assessment, recruiters can:
✅ Auto-generate SQL questions based on difficulty, topic, and job role.
✅ Evaluate actual query outputs using built-in SQL execution environments.
✅ Detect plagiarism and cheating with AI-powered proctoring (Sherlock AI).
✅ Benchmark candidates on execution speed, accuracy, and query optimization.
Identify SQL professionals who can write clean, optimized, and scalable database queries—ready to manage real-world data challenges.
SQL stands for Structured Query Language. It is a standard programming language specifically designed for managing and manipulating relational databases. SQL is used to interact with data stored in relational database management systems (RDBMS) like MySQL, PostgreSQL, SQL Server, and Oracle.
SQL allows users to:
Its primary use is to enable applications and users to interact with databases to store and retrieve data in an efficient and consistent manner.
SQL operates using statements like SELECT, INSERT, UPDATE, DELETE, and CREATE. The most common SQL operations involve querying data (SELECT), modifying data (INSERT, UPDATE, DELETE), and defining the structure of the database (CREATE, ALTER, DROP).
A database is an organized collection of data that is stored and managed in a structured way, typically within a database management system (DBMS). The data in a database is usually stored in tables, which are related to each other in some way (for example, through keys).
A database is designed to store, retrieve, and manage data efficiently. It allows users to:
There are two main types of databases:
In SQL, a table is the fundamental unit of data storage. It is a structured collection of data organized into rows and columns. Each table represents a specific entity, such as customers, orders, products, etc. The columns in the table represent attributes or fields of the entity, while the rows represent individual records or instances of the entity.
A table has a fixed structure defined by the columns, which can have specific data types (e.g., integer, string, date). Tables are defined using the CREATE TABLE statement and can have constraints like primary keys, foreign keys, and unique keys to enforce data integrity.
For example, a Customers table might have the following structure:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
DateOfBirth DATE
);For example, in a Customers table:
Example:
DELETE FROM Customers WHERE CustomerID = 1;
Example:
TRUNCATE TABLE Customers;Example:
DROP TABLE Customers;
To retrieve all the records from a table, you use the SELECT statement. The basic syntax is:
SELECT * FROM table_name;
Where:
For example, to retrieve all records from the Customers table:
SELECT * FROM Customers;
This query returns all the rows and columns from the Customers table.
A primary key is a column or a set of columns in a table that uniquely identifies each row in the table. It ensures that:
Primary keys are essential for maintaining data integrity and ensuring that each record can be uniquely identified.
For example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);In this example, EmployeeID is the primary key for the Employees table, ensuring that each employee has a unique identifier.
A foreign key is a column or a set of columns in one table that references the primary key or unique key in another table. It establishes a relationship between two tables, ensuring referential integrity by making sure that the values in the foreign key column(s) exist in the referenced primary key column(s).
Foreign keys help maintain data consistency and prevent orphaned records (i.e., records in a child table that do not have corresponding records in the parent table).
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
In this example, CustomerID in the Orders table is a foreign key that references the CustomerID in the Customers table.
A unique constraint is used to ensure that all values in a column (or a combination of columns) are unique across all rows in the table. Unlike a primary key, a unique constraint allows NULL values (though only one NULL value can exist in a column with a unique constraint, depending on the RDBMS).
Unique constraints are useful for enforcing uniqueness in data that is not a primary key but still needs to be distinct, such as email addresses or phone numbers.
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);
In this example, the Email column has a unique constraint, ensuring that no two employees can have the same email address.
An index in SQL is a database object that improves the speed of data retrieval operations on a table. It works similarly to the index in a book: instead of scanning the entire table for matching rows, an index provides a more efficient way to find data by storing references to the data in a sorted order.
Indexes can be created on one or more columns in a table and are used to speed up the performance of SELECT queries, especially those that involve WHERE, ORDER BY, or JOIN clauses.
However, indexes can slow down write operations (like INSERT, UPDATE, and DELETE), as the index needs to be updated whenever the data changes.
Example:
CREATE INDEX idx_customer_email ON Customers (Email);
In this example, an index is created on the Email column of the Customers table, making searches based on email faster.
A NULL value in SQL represents the absence of a value or unknown data. It is not the same as an empty string ('') or zero (0). The key differences are:
Key Differences:
The WHERE clause in SQL is used to filter records based on a specified condition. It allows you to restrict the rows returned by a SELECT, UPDATE, DELETE, or INSERT statement based on the given criteria.
The WHERE clause evaluates the condition (or conditions) for each row, and only rows that meet the condition(s) will be affected or returned.
Example:
SELECT * FROM Employees WHERE Salary > 50000;
In this example, the WHERE clause filters out employees whose salary is greater than 50,000.
The SELECT statement is used to retrieve data from a database. It allows you to query one or more tables and specify which columns of data to retrieve, and you can apply various filters and operations to narrow down or organize the result set.
The basic syntax of a SELECT query is:
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column;
Example:
SELECT FirstName, LastName FROM Employees WHERE Department = 'Sales';
This query retrieves the first and last names of employees who work in the "Sales" department.
You can filter data using the WHERE clause by specifying a condition that must be met for a row to be included in the result set. The condition can involve comparison operators, logical operators, and other expressions.
Comparison Operators:
Example:
SELECT * FROM Employees WHERE Age > 30 AND Department = 'HR';
This filters the records to show only employees whose age is greater than 30 and who work in the "HR" department.
Aggregate functions in SQL perform a calculation on a set of values and return a single result. They are commonly used in conjunction with the GROUP BY clause to group records based on specific columns.
Some common aggregate functions are:
Example with GROUP BY:
sql
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
This query groups employees by department and counts how many employees are in each department.
The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions like COUNT(), SUM(), AVG(), etc., to perform calculations on each group of data.
Example:
SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;This query calculates the average salary for each department.
The ORDER BY clause is used to sort the results of a query by one or more columns, either in ascending (ASC) or descending (DESC) order.
Syntax:
SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
Example:
SELECT FirstName, LastName, Salary FROM Employees
ORDER BY Salary DESC;
This query retrieves employee names and salaries, ordered by salary in descending order (highest to lowest).
Example (INNER JOIN):
Copy code
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Example (LEFT JOIN):
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;This query returns all orders, even if the customer does not exist in the Customers table.
A subquery is a query within another query. It can be used to return a value that is then used by the outer query. Subqueries are often used in WHERE, FROM, or SELECT clauses and are enclosed in parentheses.
There are two types of subqueries:
Example of a subquery in the WHERE clause:
sql
SELECT FirstName, LastName, Salary
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');
In this example, the inner query returns the DepartmentID of the "Sales" department, and the outer query retrieves employees in that department.
The HAVING clause is used to filter results after grouping them with the GROUP BY clause. While the WHERE clause filters records before grouping, the HAVING clause is used to filter the groups (aggregated data) based on a condition.
Example:
SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;This query groups employees by department and only returns departments where the average salary is greater than 50,000.
A Cartesian Join, also known as a Cross Join, is a type of join where each row from the first table is combined with every row from the second table. The result is a Cartesian product of the two tables, meaning that if table A has m rows and table B has n rows, the result will contain m * n rows.
Important Characteristics:
Example:
SELECT * FROM Employees
CROSS JOIN Departments;
In this example, each employee will be paired with every department, resulting in a Cartesian product of employees and departments.
A self join is a join where a table is joined with itself. It is useful when you have hierarchical data or when you want to compare rows within the same table. You need to give the table two different aliases in order to treat it as two separate entities.
In a self join, one instance of the table represents the "parent" record, and the other represents the "child" record.
Example:
SELECT A.EmployeeID, A.Name AS Employee, B.Name AS Manager
FROM Employees A
JOIN Employees B ON A.ManagerID = B.EmployeeID;
In this example, the Employees table is joined with itself, where A represents the employee and B represents their manager.
The DISTINCT keyword is used in SQL to remove duplicate rows from the result set. When you use DISTINCT, SQL will return only unique values for the columns selected in the query.
Example:
SELECT DISTINCT Department FROM EmployeesThis query will return only the unique departments from the Employees table, eliminating any duplicate entries.
Use cases for DISTINCT:
The UPDATE statement in SQL is used to modify existing records in a table. You can update one or more columns in one or more rows, and you can specify the rows to be updated using the WHERE clause.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;Example
UPDATE Employees
SET Salary = 60000
WHERE Department = 'HR';
This query updates the Salary of all employees in the HR department to 60,000.
The INSERT INTO statement is used to add new rows of data to a table. You can insert data into specific columns or all columns in a table.
Syntax:
INSERT INTO table_name
VALUES (value1, value2, ...);INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);Example
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (101, 'John', 'Doe', 'Sales', 50000);This query inserts a new employee record with the specified EmployeeID, FirstName, LastName, Department, and Salary.
To delete records from a table, you can use the DELETE statement. You can specify which rows to delete using the WHERE clause. If the WHERE clause is omitted, all rows in the table will be deleted.
DELETE FROM table_name
WHERE condition;
Example
DELETE FROM Employees
WHERE EmployeeID = 101;This query deletes the employee record where the EmployeeID is 101.
The LIMIT clause in SQL is used to restrict the number of rows returned by a query. It is typically used in SELECT statements to return a specified number of rows.
Syntax:
SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;Example
SELECT * FROM Employees
LIMIT 5;This query returns the first 5 rows from the Employees table.
Use cases for LIMIT:
Both CHAR and VARCHAR are used to store character string data, but they differ in how they store and handle data:
Example:
CREATE TABLE Users (
Username CHAR(10),
Email VARCHAR(50)
);In this example, the Username will always take up 10 characters, while the Email will take up only as many characters as needed.
The BETWEEN operator is used in SQL to filter the result set within a specified range. It is typically used with numeric, date, or text data types. The BETWEEN operator is inclusive, meaning it includes the boundary values specified in the range.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Example
SELECT * FROM Employees
WHERE Salary BETWEEN 40000 AND 60000;
This query retrieves all employees whose salary is between 40,000 and 60,000, inclusive.
Use cases:
Both IN and EXISTS are used to check for the presence of values in subqueries, but they differ in how they work:
Example:
SELECT FirstName, LastName
FROM Employees
WHERE DepartmentID IN (1, 2, 3);
Example:
SELECT FirstName, LastName
FROM Employees E
WHERE EXISTS (SELECT 1 FROM Departments D WHERE E.DepartmentID = D.DepartmentID);
Key Differences:
To find the number of rows in a table, you can use the COUNT() aggregate function. This function counts the number of rows that match a specified condition. If you want to count all the rows in a table, you can use COUNT(*).
Syntax:
SELECT COUNT(*) FROM table_name;
Example
SELECT COUNT(*) FROM Employees;
This query will return the total number of rows (records) in the Employees table.
Notes:
SQL constraints are rules applied to columns or tables to enforce data integrity, accuracy, and reliability. They restrict the type of data that can be stored in the table, ensuring that the data adheres to certain standards.
Types of SQL Constraints:
The ALTER command in SQL is used to modify the structure of an existing table. It allows you to add, delete, or modify columns and constraints in a table.
Common Uses of ALTER:
Add a new column:
ALTER TABLE table_name ADD column_name datatype;Modify an existing column:
ALTER TABLE table_name MODIFY column_name datatype;Drop a column:
ALTER TABLE table_name DROP COLUMN column_name;
Rename a column:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
Add or drop constraints:
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type;
Example:
ALTER TABLE Employees ADD BirthDate DATE;
This query adds a new column BirthDate to the Employees table.
To create a new table in SQL, you use the CREATE TABLE statement. You define the table name, followed by the columns and their data types.
Syntax:
CREATE TABLE table_name (
column1 datatype [constraint],
column2 datatype [constraint],
...
);Example
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DepartmentID INT,
Salary DECIMAL(10, 2)
);
This query creates an Employees table with columns for EmployeeID, FirstName, LastName, DepartmentID, and Salary.
A view in SQL is a virtual table based on the result of a SELECT query. It does not store data itself but provides a way to look at data from one or more tables in a specific manner. Views simplify complex queries by presenting a simplified or customized result set.
Advantages of Views:
Example:
sql
CREATE VIEW EmployeeSalaryView AS
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;
This query creates a view EmployeeSalaryView that shows employees with salaries greater than 50,000.
A schema in SQL is a collection of database objects, including tables, views, indexes, and other elements, that are logically grouped together. A schema serves as a namespace to organize database objects.
Example:
CREATE SCHEMA Sales;
CREATE TABLE Sales.Orders (OrderID INT, OrderDate DATE);In this example, the Sales schema contains the Orders table.
The CASE statement is a conditional expression in SQL, allowing you to return specific values based on certain conditions. It is similar to an "IF-ELSE" construct in programming.
Syntax:
SELECT column_name,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END AS new_column
FROM table_name;
Example
SELECT FirstName, LastName, Salary,
CASE
WHEN Salary > 100000 THEN 'High'
WHEN Salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'Low'
END AS SalaryCategory
FROM Employees;
This query categorizes employees based on their salary.
Both LEFT JOIN and RIGHT JOIN are used to combine rows from two tables based on a related column. The difference lies in which table’s rows are returned when there is no match.
Example:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
This query retrieves all employees, even if they are not assigned to a department.
Both UNION and UNION ALL are used to combine the result sets of two or more SELECT queries, but they have key differences:
Example (UNION):
SELECT FirstName FROM Employees
UNION
SELECT FirstName FROM Contractors;This query returns unique FirstName values from both Employees and Contractors.
Example (UNION ALL):
SELECT FirstName FROM Employees
UNION ALL
SELECT FirstName FROM Contractors;
This query returns all FirstName values from both Employees and Contractors, including duplicates.
To retrieve the current date and time in SQL, you can use the CURRENT_TIMESTAMP or GETDATE() function (depending on the SQL flavor).
For SQL Server:
SELECT GETDATE();
For MySQL:
SELECT CURRENT_TIMESTAMP;
For PostgreSQL:
SELECT NOW();
Normalization is the process of organizing data in a database to eliminate redundancy and ensure data integrity. It involves dividing large tables into smaller ones and defining relationships between them. The goal is to reduce data anomalies such as insertion, update, and deletion issues.
Normal Forms:
Denormalization is the process of intentionally introducing redundancy into a database by merging tables or duplicating data, effectively reversing some aspects of normalization. It is used to optimize performance, particularly in read-heavy systems.
Why use Denormalization?
Example:
In a normalized design, you might have separate tables for Customers and Orders. Denormalizing would involve storing customer details (like name and address) directly in the Orders table to reduce the need for a JOIN when retrieving orders with customer information.
SQL joins allow you to retrieve data from multiple tables based on a relationship between them. The primary types of joins are:
Example:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;Example:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Example:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Example:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Example:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;An INNER JOIN returns only the rows where there is a match in both tables. It eliminates rows that do not have a match.
Example:
Assume you have two tables: Employees and Departments.
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
This query retrieves the names of employees along with their corresponding department names, but only for employees who belong to a department. If an employee does not belong to a department, that employee will not be included in the result.
Practical Use:
A FULL OUTER JOIN returns all rows when there is a match in one of the tables. If there is no match, NULL values are returned for the columns from the table without a match.
Example:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;This query will return all employees and all departments. If an employee is not assigned to a department, the DepartmentName will be NULL. Similarly, if a department has no employees, the employee-related columns will be NULL.
Practical Use:
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If no match is found in the right table, NULL is returned for the right table's columns.
A RIGHT JOIN (or RIGHT OUTER JOIN) does the opposite: it returns all rows from the right table and the matching rows from the left table. If no match is found in the left table, NULL is returned for the left table's columns.
Difference:
Example (LEFT JOIN):
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;This query returns all employees and their department names. If an employee does not belong to a department, the department name will be NULL.
A CROSS JOIN returns the Cartesian product of two tables. Every row from the first table is combined with every row from the second table, resulting in a potentially very large result set.
Example:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;
If Employees has 5 rows and Departments has 3 rows, the result will contain 15 rows (5 × 3).
Practical Use:
An index is a database object that improves the speed of data retrieval operations on a table. It works similarly to an index in a book, allowing the database to quickly locate the desired rows.
When to Use an Index:
Example:
CREATE INDEX idx_employee_name ON Employees(Name);
This creates an index on the Name column of the Employees table.
Caution:
To find duplicate records, you can use a GROUP BY clause along with the HAVING clause to identify groups that have more than one occurrence.
Example:
SELECT Name, COUNT(*)
FROM Employees
GROUP BY Name
HAVING COUNT(*) > 1;
This query will return all names that appear more than once in the Employees table, showing which names are duplicated.
A SELF JOIN is a join where a table is joined with itself. This is useful when you want to compare rows within the same table, such as identifying hierarchical relationships (e.g., managers and employees).
Example:
Assume an Employees table with columns EmployeeID, EmployeeName, and ManagerID (where ManagerID references EmployeeID).
SELECT E1.EmployeeName AS Employee, E2.EmployeeName AS Manager
FROM Employees E1
LEFT JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;
This query lists all employees and their managers. If an employee has no manager, NULL will appear in the Manager column.
Handling NULL values in SQL requires a few different techniques, as they represent unknown or missing data and are treated differently from other values like an empty string or zero.
Techniques for Handling NULL values:
SELECT * FROM Employees WHERE FirstName IS NULL;
SELECT COALESCE(PhoneNumber, 'Not Provided') FROM Employees;
SELECT IFNULL(PhoneNumber, 'No Phone') FROM Employees;
SELECT
CASE
WHEN PhoneNumber IS NULL THEN 'No Phone'
ELSE PhoneNumber
END AS PhoneStatus
FROM Employees;
Caution:
To calculate the sum of a specific column for rows that meet a condition, you can use the SUM() aggregate function along with the WHERE clause.
Example:
SELECT SUM(Salary) AS TotalSalary
FROM Employees
WHERE DepartmentID = 2;
This query calculates the total salary for all employees in department 2.
Both HAVING and WHERE are used to filter data in SQL, but they are used at different points in a query and for different purposes.
Key Differences:
Example:
-- Using WHERE to filter rows before grouping
SELECT DepartmentID, AVG(Salary)
FROM Employees
WHERE Salary > 50000
GROUP BY DepartmentID;
-- Using HAVING to filter groups after aggregation
SELECT DepartmentID, AVG(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 60000;
The COALESCE() function in SQL is used to return the first non-NULL value from a list of expressions. It’s often used to replace NULL values with a default value or an alternate expression.
Syntax:
COALESCE(expression1, expression2, ..., expressionN);
Example:
SELECT Name, COALESCE(PhoneNumber, 'Not Provided') AS ContactInfo
FROM Employees;
If PhoneNumber is NULL, the query will return 'Not Provided' instead.
The ROW_NUMBER() function assigns a unique number to each row within a result set, based on a specific ordering. It is often used for pagination or to assign a rank to rows.
Syntax:
ROW_NUMBER() OVER (ORDER BY column_name)
Example (Pagination):
SELECT Name, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees
WHERE DepartmentID = 2;
This query assigns a unique row number (RowNum) to each employee in department 2, ordered by salary in descending order.
Example (Top N per Group):
If you want to retrieve the top 3 highest paid employees per department:
WITH RankedEmployees AS (
SELECT Name, Salary, DepartmentID,
ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum
FROM Employees
)
SELECT Name, Salary, DepartmentID
FROM RankedEmployees
WHERE RowNum <= 3;
This query partitions the result set by DepartmentID, orders each department by salary, and then retrieves the top 3 employees per department.
There are several ways to find the second highest salary, but the most common approaches are:
1. Using LIMIT with ORDER BY (in MySQL)
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;This query orders the salaries in descending order and skips the highest one (using OFFSET 1), thus returning the second highest salary.
2. Using Subquery (General SQL)
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
This query finds the maximum salary that is less than the highest salary, which is effectively the second highest.
3. Using ROW_NUMBER() (SQL Server, PostgreSQL, etc.)
WITH RankedSalaries AS (
SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees
)
SELECT Salary
FROM RankedSalaries
WHERE RowNum = 2;
This query ranks salaries in descending order and selects the row where RowNum = 2, which represents the second highest salary.
SQL injection is a code injection technique where an attacker can insert or manipulate SQL queries by injecting malicious SQL code into an input field. This can lead to unauthorized access to data, loss of data, or even full database compromise.
How SQL Injection Happens:
Prevention Techniques:
Example (in Python with psycopg2):
cursor.execute("SELECT * FROM Employees WHERE EmployeeID = %s", (employee_id,))
A TRANSACTION in SQL is a sequence of one or more SQL operations executed as a single unit. A transaction ensures that either all operations succeed, or none of them are applied (atomicity).
Key Properties (ACID):
Example:
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
-- Commit the transaction if everything is successful
COMMIT;
If any error occurs before the COMMIT statement, you can roll back the transaction to ensure no money is transferred.
ROLLBACK;
ACID stands for Atomicity, Consistency, Isolation, and Durability — the four key properties that ensure reliable transaction processing in SQL databases.
The EXISTS keyword is used in SQL to check if a subquery returns any rows. It is commonly used in correlated subqueries.
Example (Checking for Existence):
SELECT Name
FROM Employees e
WHERE EXISTS (SELECT 1 FROM Departments d WHERE e.DepartmentID = d.DepartmentID);
This query checks if there is at least one department corresponding to each employee’s DepartmentID. If there is, the employee’s name is returned.
When to Use:
The CASE statement is used for conditional logic in SQL. It allows you to perform if-else-like conditions within a query to return different values depending on specified conditions.
Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END
The CASE statement can be used in the SELECT, UPDATE, DELETE, and WHERE clauses. It evaluates conditions in the specified order and returns the first result where the condition is true. If no condition matches, the ELSE clause is executed, if provided.
Example:
SELECT EmployeeID, Name, Salary,
CASE
WHEN Salary >= 70000 THEN 'High'
WHEN Salary >= 50000 THEN 'Medium'
ELSE 'Low'
END AS SalaryLevel
FROM Employees;
This query categorizes employees based on their salary: those with salaries of 70,000 or more are categorized as 'High', between 50,000 and 69,999 as 'Medium', and the rest as 'Low'.
To get the number of records grouped by a specific column, you can use the GROUP BY clause combined with the aggregate function COUNT().
Example:
SELECT DepartmentID, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID;
This query counts the number of employees in each department, grouping the result by the DepartmentID. The COUNT(*) function counts all rows within each group.
SQL provides a variety of functions to work with dates and times.
Common Date and Time Functions:
SELECT NOW();
SELECT CURRENT_DATE;
SELECT DATEADD(DAY, 7, '2024-01-01');
SELECT DATEDIFF('2024-01-10', '2024-01-01');
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
SELECT EXTRACT(YEAR FROM CURRENT_DATE);
A temporary table is a table that is created within a session and automatically dropped when the session ends. Temporary tables are often used for intermediate data storage during complex queries or for processing data temporarily.
Creating and Using a Temporary Table:
Create Temporary Table:
CREATE TEMPORARY TABLE TempEmployees (
EmployeeID INT,
Name VARCHAR(100),
Salary DECIMAL(10, 2)
);
Insert Data into Temporary Table:
INSERT INTO TempEmployees (EmployeeID, Name, Salary)
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE DepartmentID = 1;
Query Data from Temporary Table:
SELECT * FROM TempEmployees;
Drop Temporary Table (Optional, but it will be dropped automatically at session end):
DROP TEMPORARY TABLE TempEmployees;
Temporary tables can store intermediate results and are typically used in stored procedures or scripts.
Both UNION and INTERSECT combine results from multiple queries, but they operate differently.
Example:
SELECT Name FROM Employees WHERE DepartmentID = 1
UNION
SELECT Name FROM Employees WHERE DepartmentID = 2;
Example:
SELECT Name FROM Employees WHERE DepartmentID = 1
INTERSECT
SELECT Name FROM Employees WHERE DepartmentID = 2;
The RANK() function is a window function that assigns a unique rank to each row within a result set, but it can assign the same rank to rows with equal values. It is similar to the ROW_NUMBER() function, but RANK() allows for ties.
Key Differences:
Example (Ranking Salaries):
SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;
Example Output:
Name
Salary
Rank
Alice
100000
1
Bob
100000
1
Charlie
95000
3
David
90000
4
To retrieve the last N records, the method depends on the SQL database system being used. Here are examples for different systems:
SELECT * FROM Employees
ORDER BY EmployeeID DESC
LIMIT 5;
SELECT TOP 5 * FROM Employees
ORDER BY EmployeeID DESC;
SELECT * FROM Employees
ORDER BY EmployeeID DESC
LIMIT 5;
In all cases, the query orders the data in descending order by the column (e.g., EmployeeID), and then limits the result to the last N records.
To find the maximum value from a group of records, you use the MAX() aggregate function.
Example:
SELECT MAX(Salary) AS MaxSalary
FROM Employees
WHERE DepartmentID = 1;
This query returns the maximum salary for employees in department 1.
You can also use MAX() in combination with GROUP BY to find the maximum value per group.
SELECT DepartmentID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentID;
A TRIGGER is a database object that automatically executes or fires when a specific event occurs (such as INSERT, UPDATE, or DELETE) on a table or view.
Example:
Suppose you want to track changes to employee salaries, and automatically log any changes.
Create a Trigger:
CREATE TRIGGER SalaryUpdate
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, UpdateDate)
VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary, NOW());
END;
This trigger will automatically insert a record into the SalaryHistory table whenever an employee's salary is updated.
A VIEW is a virtual table in SQL that is based on the result of a SELECT query. Unlike a regular table, a view does not store data itself; it displays data dynamically from the underlying tables based on the query used to define it.
Key Differences Between Views and Tables:
Example:
CREATE VIEW EmployeeSalaries AS
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > 50000;
This view will display all employees with salaries greater than 50,000 whenever queried, but does not store the data itself.
You can perform both a LEFT JOIN and a RIGHT JOIN on the same table to compare and retrieve data from the left and right sides of the joins.
Example:
SELECT A.EmployeeID, A.Name, B.DepartmentID, B.DepartmentName
FROM Employees A
LEFT JOIN Departments B ON A.DepartmentID = B.DepartmentID
RIGHT JOIN Departments C ON A.DepartmentID = C.DepartmentID;
In this query:
You can use the UPDATE statement with a WHERE clause to update multiple rows in a table based on specific conditions. The conditions will determine which rows will be updated.
Example:
UPDATE Employees
SET Salary = Salary * 1.10
WHERE DepartmentID = 3 AND Salary < 50000;
This query increases the salary by 10% for all employees in department 3 who have a salary lower than 50,000.
Both CHAR and VARCHAR are used to store string data, but there are key differences in how they handle storage and performance:
Example:
CREATE TABLE Employees (
Name VARCHAR(50), -- Variable length
PhoneNumber CHAR(10) -- Fixed length
);The GROUP_CONCAT() function (in MySQL) is used to concatenate values from multiple rows into a single string, with an optional separator between the values. It is often used when you want to aggregate values across rows and return them as a single column.
Example:
SELECT DepartmentID, GROUP_CONCAT(Name ORDER BY Name) AS EmployeeNames
FROM Employees
GROUP BY DepartmentID;
This query returns a list of employee names for each department, separated by commas.
Output Example:
DepartmentID
EmployeeNames
1
Alice, Bob, Charlie
2
David, Eve
Pagination is implemented by limiting the number of rows returned by a query and using OFFSET and LIMIT (or ROWNUM in SQL Server) to define the range of rows to retrieve.
Example for MySQL/PostgreSQL:
SELECT * FROM Employees
ORDER BY EmployeeID
LIMIT 10 OFFSET 20;
Example for SQL Server:
SELECT * FROM Employees
ORDER BY EmployeeID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
This retrieves the records from 21 to 30 in the Employees table.
A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. Stored procedures allow you to encapsulate logic (such as querying and updating data) and can accept input parameters and return results.
Example of Creating a Stored Procedure:
CREATE PROCEDURE GetEmployeeSalary (IN emp_id INT)
BEGIN
SELECT Name, Salary
FROM Employees
WHERE EmployeeID = emp_id;
END;
To execute the stored procedure:
CALL GetEmployeeSalary(1);
Stored procedures improve code reusability, reduce client-server traffic, and help in enforcing business logic.
The WITH clause, also known as a Common Table Expression (CTE), is used to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE query. CTEs are often used for simplifying complex queries and improving query readability.
Syntax:
WITH CTEName AS (
SELECT column1, column2
FROM Table
WHERE condition
)
SELECT *
FROM CTEName;
Example:
WITH DeptCTE AS (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
)
SELECT e.Name, e.DepartmentID, d.AvgSalary
FROM Employees e
JOIN DeptCTE d ON e.DepartmentID = d.DepartmentID;
In this example, the CTE (DeptCTE) calculates the average salary by department, and the main query joins this result with the Employees table.
A composite key is a primary key made up of more than one column. It is used when a single column is not sufficient to uniquely identify a record. You can define a composite key by including multiple columns in the PRIMARY KEY constraint.
Example:
CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);
In this example, the combination of OrderID and ProductID forms the composite primary key for the OrderDetails table.
To join three or more tables in SQL, you can use multiple JOIN clauses. The key is to ensure you use the appropriate join condition for each table.
Example:
SELECT e.Name, d.DepartmentName, p.ProjectName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
JOIN Projects p ON e.EmployeeID = p.EmployeeID;
This query retrieves the names of employees, their department names, and the projects they are working on by joining the Employees, Departments, and Projects tables.
Window functions (also known as analytic functions) perform calculations across a set of table rows related to the current row. Unlike regular aggregate functions, window functions do not collapse the result set but instead return a value for each row based on the window frame.
Common Window Functions:
Example:
SELECT EmployeeID, Name, Salary,
RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;
This query ranks employees by salary, with the highest-paid employee having a rank of 1.
Example with PARTITION BY (Dividing the result set into partitions):
SELECT DepartmentID, EmployeeID, Name, Salary,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DepartmentRank
FROM Employees;
In this query, employees are ranked within each department based on their salary, so each department gets its own ranking starting from 1.
Indexing in SQL is a technique used to speed up the retrieval of rows from a table by creating a data structure that allows for fast lookup. An index is a pointer to data in a table that can significantly improve query performance, particularly for SELECT queries that involve searching, sorting, and filtering large datasets.
Types of Indexes:
Single-Column Index: An index on a single column. It’s created on a column that is frequently used in WHERE, JOIN, or ORDER BY clauses.
CREATE INDEX idx_employee_name ON Employees(Name);
Composite Index: An index on two or more columns. It’s helpful when queries filter by multiple columns.
CREATE INDEX idx_employee_dept_salary ON Employees(DepartmentID, Salary);Unique Index: Ensures that all values in the indexed column(s) are unique. This type of index is automatically created when a PRIMARY KEY or UNIQUE constraint is defined.
CREATE UNIQUE INDEX idx_employee_email ON Employees(Email);Full-Text Index: Optimized for searching text in large text fields, often used in applications like search engines or content management systems.
CREATE FULLTEXT INDEX idx_employee_name ON Employees(Name);Example:
CREATE CLUSTERED INDEX idx_employee_id ON Employees(EmployeeID);
Example:
CREATE NONCLUSTERED INDEX idx_employee_name ON Employees(Name);
Key Difference:
Indexes can significantly improve query performance, especially for SELECT statements, by reducing the amount of data scanned during a query. However, there are some trade-offs:
In summary, while indexes improve read performance, they can negatively affect write performance and consume additional resources.
Sharding is a database architecture technique in which data is partitioned across multiple database instances (shards). Each shard holds a subset of the data, typically based on a specific range of values (e.g., customer ID or geographic region). Sharding allows for horizontal scaling by distributing data across multiple servers.
Use Cases:
Sharding is typically used in combination with a distributed database or in large-scale applications where performance and scalability are key concerns.
Partitioning in SQL is the process of dividing a large table into smaller, more manageable pieces (partitions). Each partition is a subset of the data, and partitions are typically based on a column (like a date or region). Partitioning helps improve query performance and management of large datasets.
Types of Partitioning:
Example:
CREATE TABLE Orders (
OrderID INT,
OrderDate DATE,
Amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(OrderDate)) (
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022)
);
This example partitions the Orders table by the year of the OrderDate.
Example:
-- WHERE filters individual rows
SELECT Name, Salary
FROM Employees
WHERE Salary > 50000;
-- HAVING filters groups after aggregation
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 60000;
In the first query, WHERE filters individual employees with a salary greater than 50,000. In the second query, HAVING filters departments where the average salary is greater than 60,000.
Optimizing slow-running queries involves a combination of techniques to improve query execution speed. Some of the best practices include:
EXPLAIN PLAN: Use the EXPLAIN plan to understand how the query is executed and identify bottlenecks.
EXPLAIN SELECT * FROM Employees WHERE Salary > 50000;
Stored procedures and functions encapsulate SQL logic, making it reusable and maintainable. The benefits include:
Example of a stored procedure:
CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)
BEGIN
SELECT Salary FROM Employees WHERE EmployeeID = emp_id;
END;The EXPLAIN plan provides insights into how the database engine executes a query. It shows the execution plan, including which indexes are used, the order of table scans, and the cost of each operation.
Example:
EXPLAIN SELECT * FROM Employees WHERE Salary > 50000;
The output will show how the database is accessing the Employees table and whether it is using an index or performing a full table scan.
Use Cases:
CROSS APPLY and OUTER APPLY are used to join a table with a table-valued function. They allow you to apply a function to each row of a table.
Example of CROSS APPLY:
SELECT e.EmployeeID, e.Name, p.ProjectName
FROM Employees e
CROSS APPLY GetEmployeeProjects(e.EmployeeID) p;
Example of OUTER APPLY:
SELECT e.EmployeeID, e.Name, p.ProjectName
FROM Employees e
OUTER APPLY GetEmployeeProjects(e.EmployeeID) p;
In the CROSS APPLY example, only employees with projects will be returned, while the OUTER APPLY will return all employees, even if they don't have any associated projects.
A database trigger is a set of SQL statements that are automatically executed (or "triggered") by the database in response to certain events on a table or view. Triggers are associated with a specific table and are fired when a particular event (INSERT, UPDATE, DELETE) occurs on that table.
Uses of Triggers:
Example of a Trigger:
CREATE TRIGGER trg_after_insert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO AuditLog (Action, EmployeeID, ActionTime)
VALUES ('INSERT', NEW.EmployeeID, NOW());
END;This trigger inserts a record into the AuditLog table after a new row is added to the Employees table.
A recursive CTE is a CTE that references itself to solve problems that involve hierarchical or recursive relationships, such as organizational structures or bill-of-materials queries. Recursive CTEs are typically used for operations like traversing tree-like structures.
A recursive CTE is divided into two parts:
Example of a Recursive CTE:
WITH RECURSIVE OrgChart AS (
-- Anchor member: select the top-most manager
SELECT EmployeeID, Name, ManagerID
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member: select employees managed by the previous level
SELECT e.EmployeeID, e.Name, e.ManagerID
FROM Employees e
JOIN OrgChart o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgChart;
This query finds all employees in an organizational hierarchy, starting from the top-level manager and traversing down to their subordinates.
ACID stands for Atomicity, Consistency, Isolation, and Durability—four key properties that ensure that SQL transactions are processed reliably and protect the database from inconsistencies.
In SQL, a many-to-many relationship occurs when multiple records in one table are associated with multiple records in another table. This can be implemented using a junction table (also known as a bridge or associative table) that contains foreign keys referencing the primary keys of both related tables.
Example:
Suppose you have two tables: Students and Courses, and a student can enroll in multiple courses, while a course can have multiple students. You need a third table to represent this many-to-many relationship.
-- Students Table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100)
);
-- Courses Table
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100)
);
-- Junction Table to represent many-to-many relationship
CREATE TABLE StudentCourses (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);This design allows each student to enroll in multiple courses and each course to have multiple students.
To find the nth highest salary from a table, you can use a combination of DISTINCT, ORDER BY, and LIMIT (for MySQL/PostgreSQL) or use ROW_NUMBER() (for SQL Server).
Example for MySQL/PostgreSQL (using LIMIT):
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET n-1;In this query, replace n with the desired rank (e.g., n = 3 for the 3rd highest salary). The OFFSET skips the top n-1 salaries and retrieves the nth highest.
Example for SQL Server (using ROW_NUMBER()):
WITH RankedSalaries AS (
SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees
)
SELECT Salary
FROM RankedSalaries
WHERE Rank = n;
In this query, ROW_NUMBER() assigns a unique rank to each salary ordered by descending value, and you can filter to retrieve the nth rank.
Denormalization is the process of intentionally introducing redundancy into a database by combining tables that were previously normalized. It’s done to optimize read-heavy operations where performance is critical and the complexity of multiple joins in normalized tables may be detrimental to performance.
Reasons for Denormalization:
However, denormalization comes at the cost of increased storage space and complexity in maintaining data integrity (i.e., handling updates to redundant data).
Example:
Combining Orders and OrderDetails tables into a single denormalized table for reporting purposes.
Example:
-- GROUP BY (aggregated result)
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;
-- PARTITION BY (window function)
SELECT EmployeeID, DepartmentID, Salary,
AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgSalary
FROM Employees;
In the first query, GROUP BY calculates the average salary per department and returns one row per department. In the second query, PARTITION BY calculates the average salary for each department but retains each employee’s data in the result.
A foreign key is a column (or set of columns) in a table that creates a link between data in two tables. It refers to the primary key in another table, ensuring that the values in the foreign key column correspond to valid entries in the referenced table. Foreign keys help maintain referential integrity, which ensures that relationships between tables remain consistent.
Example:
-- Parent Table (Departments)
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
-- Child Table (Employees) with Foreign Key
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
In this example, the Employees table has a foreign key (DepartmentID) that references the Departments table. This ensures that each employee belongs to a valid department.
Designing a database for a large-scale application requires careful planning and consideration of performance, scalability, and maintainability. Key steps include:
A materialized view is a database object that stores the results of a query physically on disk, unlike a regular view, which is a virtual table that always re-executes the query when accessed.
Differences:
Example of Materialized View:
CREATE MATERIALIZED VIEW TopSalaries AS
SELECT EmployeeID, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 10;
This materialized view stores the top 10 salaries in the Employees table. It can be refreshed periodically to ensure it has up-to-date data.
The MERGE statement, also known as an "upsert" operation, allows you to combine INSERT, UPDATE, and DELETE actions into a single query based on whether a matching record exists in the target table.
It works by comparing rows in a target table to rows in a source table. Depending on whether a match is found, it performs one of the following actions:
Syntax:
MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (source.id, source.name)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Example:
MERGE INTO Employees AS target
USING NewEmployees AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
UPDATE SET target.Name = source.Name, target.Salary = source.Salary
WHEN NOT MATCHED THEN
INSERT (EmployeeID, Name, Salary) VALUES (source.EmployeeID, source.Name, source.Salary)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
This merges data from the NewEmployees table into the Employees table, updating existing records, inserting new ones, and deleting records in Employees that are no longer in NewEmployees.
An e-commerce schema should support the core operations of online shopping, such as product management, customer accounts, order tracking, payments, and inventory management. Below is a basic schema design:
Key tables:
Users Table: Stores customer information (name, email, password, shipping address).
CREATE TABLE Users (
UserID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE,
Password VARCHAR(255),
ShippingAddress VARCHAR(255)
);
Products Table: Stores product details (name, price, description, quantity).
CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(255),
Price DECIMAL(10, 2),
StockQuantity INT,
CategoryID INT
);
Orders Table: Stores order details (order ID, user ID, total amount, order date).
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
UserID INT,
OrderDate DATETIME,
TotalAmount DECIMAL(10, 2),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
Order_Items Table: Stores items within an order (order ID, product ID, quantity, price).
CREATE TABLE Order_Items (
OrderItemID INT PRIMARY KEY AUTO_INCREMENT,
OrderID INT,
ProductID INT,
Quantity INT,
Price DECIMAL(10, 2),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
Payments Table: Stores payment transactions (payment ID, order ID, payment method, status).
CREATE TABLE Payments (
PaymentID INT PRIMARY KEY AUTO_INCREMENT,
OrderID INT,
PaymentMethod VARCHAR(50),
PaymentDate DATETIME,
Status VARCHAR(20),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
Categories Table: Stores product categories (e.g., electronics, clothing).
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY AUTO_INCREMENT,
CategoryName VARCHAR(100)
);
Inventory Table: Keeps track of stock levels for products.
CREATE TABLE Inventory (
ProductID INT,
StockQuantity INT,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
Example:
CREATE TRIGGER log_employee_update
AFTER UPDATE ON Employees
FOR EACH ROW
INSERT INTO AuditLog (Action, EmployeeID, ActionTime)
VALUES ('UPDATE', OLD.EmployeeID, NOW());
Example:
CREATE PROCEDURE CalculateTotalAmount (IN OrderID INT)
BEGIN
SELECT SUM(Price * Quantity) AS TotalAmount
FROM Order_Items
WHERE OrderID = OrderID;
END;
Differences:
An AUTO_INCREMENT field is a column in a table where the database automatically generates a unique value for every new row inserted. This is typically used for primary key columns, ensuring that each row has a unique identifier.
Example:
CREATE TABLE Users (
UserID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100)
);
In this example, the UserID field will automatically increment by 1 each time a new user is added to the table.
Normalization is the process of organizing the data in a database to reduce redundancy and dependency. It involves breaking down large tables into smaller, related tables, and ensuring that the data is logically stored.
Normal Forms:
Example:Before 1NF:
| OrderID | Products |
|---------|-----------------|
| 1 | A, B, C |
| 2 | D, E |
After 1NF:
| OrderID | Product |
|---------|---------|
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | D |
| 2 | E |
A Database Administrator (DBA) is responsible for managing and maintaining a database system. Key responsibilities include:
To ensure data integrity and avoid anomalies:
The FOR XML PATH clause in SQL Server is used to concatenate row values into a single string, typically for aggregation purposes.
Example:
SELECT Name
FROM Employees
FOR XML PATH('');
This will return all employee names concatenated into a single XML string.
Concatenate with a separator (e.g., comma):
SELECT STUFF((SELECT ',' + Name
FROM Employees
FOR XML PATH('')), 1, 1, '') AS EmployeeNames;
SQL Joins are used to combine records from two or more tables based on a related column. There are several types of joins, each with its performance implications:
Example:
SELECT * FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Example:
SELECT * FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Example:
SELECT * FROM Orders
FULL OUTER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Example:
SELECT * FROM Products
CROSS JOIN Categories;Example:
SELECT A.EmployeeID, A.Name, B.Name AS Manager
FROM Employees A
LEFT JOIN Employees B ON A.ManagerID = B.EmployeeID;
You can calculate running totals and moving averages using window functions like SUM(), AVG(), and OVER().
Running Total:A running total sums all previous rows up to the current row.
SELECT OrderID, OrderDate, Amount,
SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;
Moving Average:A moving average calculates the average over a specified window of rows (e.g., the last 3 orders).
SELECT OrderID, OrderDate, Amount,
AVG(Amount) OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Orders;
A temporary table is a table that is created and used for storing intermediate results within a session or a transaction. These tables are automatically dropped at the end of the session or when the connection is closed. Temporary tables are useful for breaking down complex queries into simpler steps.
Example:
CREATE TABLE #TempOrders (
OrderID INT,
OrderDate DATETIME,
Amount DECIMAL(10, 2)
);
INSERT INTO #TempOrders (OrderID, OrderDate, Amount)
SELECT OrderID, OrderDate, Amount FROM Orders WHERE OrderDate > '2023-01-01';
SELECT * FROM #TempOrders;
The #TempOrders table will be dropped automatically at the end of the session.
Here are some notable differences between SQL Server and MySQL:
-- SQL Server
SELECT TOP 5 * FROM Employees;
-- MySQL
SELECT * FROM Employees LIMIT 5;
You can use window functions or subqueries to achieve this. Here’s a method using the ROW_NUMBER() window function:
WITH RankedOrders AS (
SELECT OrderID, CustomerID, OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RowNum
FROM Orders
)
SELECT OrderID, CustomerID, OrderDate
FROM RankedOrders
WHERE RowNum <= 3;
In this example, for each CustomerID, the first 3 OrderID records are selected based on the order date.
A full-text search allows you to search for words or phrases within a text column efficiently. It is available in SQL Server and MySQL with different syntax and features.
In SQL Server:Full-text indexing must be enabled. Use CONTAINS or FREETEXT to perform the search.
SELECT * FROM Articles
WHERE CONTAINS(ArticleText, 'database');
In MySQL:Use FULLTEXT indexes and MATCH...AGAINST to search text.
SELECT * FROM Articles
WHERE MATCH(ArticleText) AGAINST('database');
Database replication is the process of copying data from one database to another to ensure data availability, backup, and fault tolerance.
Types of replication:
Deadlocks occur when two or more queries are waiting for each other to release resources. Here's how to troubleshoot them:
A transaction is a sequence of operations that are executed as a single unit. If one operation fails, the entire transaction is rolled back to maintain data integrity.
Example using SQL Server:
BEGIN TRANSACTION;
BEGIN TRY
-- Step 1: Insert data
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (1, 'C001', '2024-11-01');
-- Step 2: Update inventory
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 'P001';
-- Step 3: Commit the transaction
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- If error occurs, roll back the transaction
ROLLBACK TRANSACTION;
PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;