SELECT * FROM Employees;

Explanation: SELECT * means "select all columns". FROM Employees specifies the table to retrieve data from. This is one of the most fundamental SQL practice questions for beginners.

SELECT FirstName, LastName FROM Employees;

Explanation: Instead of *, you list the specific column names you want to retrieve, separated by commas. This SQL WHERE clause examples technique focuses your query results.

SELECT * FROM Employees WHERE Salary > 50000;

Explanation: The WHERE clause is used to filter rows based on a specified condition in SQL query questions. This is a fundamental concept, much like programming syntax itself.

SELECT DISTINCT Location FROM Departments;

Explanation: The DISTINCT keyword removes duplicate rows from the result set. This is commonly tested in SQL interview questions.

SELECT * FROM Employees WHERE DepartmentID = 1;

Explanation: The = operator is used for equality checks in the WHERE clause. We will cover how to find the ID from the name 'Sales' in the SQL JOIN examples section.

SELECT * FROM Employees WHERE Salary BETWEEN 60000 AND 80000;

Explanation: The BETWEEN operator is inclusive, meaning it selects values that are greater than or equal to the first value AND less than or equal to the second value. This is a popular SQL practice questions topic.

-- For MySQL/PostgreSQL:
SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;

-- For SQL Server:
SELECT * FROM Employees WHERE DATEPART(year, HireDate) = 2023;

-- For SQLite:
SELECT * FROM Employees WHERE STRFTIME('%Y', HireDate) = '2023';

Explanation: Date/time functions are specific to the SQL dialect (e.g., MySQL, PostgreSQL, SQL Server). YEAR() or DATEPART() are common functions to extract parts of a date in SQL query questions.

SELECT * FROM Employees ORDER BY Salary DESC;

Explanation: The ORDER BY clause sorts the result set. DESC specifies descending order. The default is ASC (ascending). This is a foundational concept in SQL interview questions, similar to sorting algorithms in programming.

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

Explanation: The LIKE operator is used for pattern matching in SQL WHERE clause examples. The wildcard % matches any sequence of zero or more characters.

-- Using OR:
SELECT * FROM Employees WHERE DepartmentID = 1 OR DepartmentID = 2;

-- Using IN (more efficient):
SELECT * FROM Employees WHERE DepartmentID IN (1, 2);

Explanation: The IN operator is a shorthand for multiple OR conditions and is often more readable and performant in SQL practice questions.

-- Using NOT EQUAL:
SELECT * FROM Employees WHERE DepartmentID != 3;
-- Or
SELECT * FROM Employees WHERE DepartmentID <> 3;

-- Using NOT IN:
SELECT * FROM Employees WHERE DepartmentID NOT IN (3);

Explanation: Both != and <> are standard operators for "not equal" in SQL query questions. NOT IN is useful for excluding a list of values.

-- For PostgreSQL/Oracle (standard):
SELECT FirstName || ' ' || LastName AS FullName FROM Employees;

-- For MySQL:
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

-- For SQL Server:
SELECT FirstName + ' ' + LastName AS FullName FROM Employees;

Explanation: String concatenation syntax varies by SQL dialect in SQL interview questions. AS is the keyword used to create a column alias (rename a column in the output).

SELECT * FROM Employees WHERE DepartmentID IS NULL;

Explanation: You cannot use = NULL to check for NULL values in SQL practice questions. You must use the IS NULL operator. Conversely, you would use IS NOT NULL to find rows with a value.

-- For MySQL/PostgreSQL:
SELECT * FROM Employees 
ORDER BY Salary DESC 
LIMIT 5;

-- For SQL Server:
SELECT TOP 5 * FROM Employees 
ORDER BY Salary DESC;

-- For Oracle:
SELECT * FROM Employees 
ORDER BY Salary DESC 
FETCH FIRST 5 ROWS ONLY;

Explanation: The syntax for limiting results differs across database systems. This SQL query questions example first sorts all employees by salary (highest to lowest) and then takes only the top 5 rows from that sorted set.

SELECT * FROM Employees 
WHERE DepartmentID = 1 AND Salary > 70000;

Explanation: The AND operator is used to combine multiple conditions in a WHERE clause for SQL interview questions. Both conditions must be true for a row to be returned.

SELECT COUNT(*) FROM Employees;

Explanation: COUNT(*) is an SQL aggregate functions example that returns the total number of rows in a table. COUNT(ColumnName) would count non-NULL values in that specific column.

SELECT AVG(Salary) FROM Employees;

Explanation: AVG() is an aggregate function that calculates the average value of a numeric column. This is frequently tested in SQL practice questions.

SELECT MAX(Salary), MIN(Salary) FROM Employees;

Explanation: MAX() and MIN() are SQL aggregate functions that return the maximum and minimum values of a column, respectively.

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;

Explanation: The GROUP BY clause groups rows that have the same values in specified columns (here, DepartmentID). The COUNT(*) aggregate function then counts the rows within each group. This is a core concept in SQL GROUP BY questions.

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

Explanation: Similar to the previous SQL GROUP BY questions example, GROUP BY creates groups, and AVG(Salary) calculates the average salary for each of those groups.

Explanation:

• WHERE filters rows before any grouping or aggregation happens (it works on individual rows)
• HAVING filters groups after grouping and aggregation happen (it works on the results of SQL aggregate functions like COUNT() or AVG())

This distinction is crucial in SQL interview questions.

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 10;

Explanation: You must use HAVING here, not WHERE, in these SQL GROUP BY questions. WHERE COUNT(*) > 10 would fail because WHERE is processed before the COUNT() is calculated.

Explanation: An INNER JOIN (or just JOIN) returns only the rows that have matching values in both tables. If an employee has a DepartmentID that doesn't exist in the Departments table, they will not be included in the result. This is fundamental to understanding SQL JOIN examples.

SELECT 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM 
    Employees e
INNER JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID;

Explanation: This SQL JOIN examples query joins the two tables ON the column they have in common (DepartmentID). We use aliases (e for Employees, d for Departments) to make the query shorter and clearer. This explores relationships, a key concept in graph algorithms.

Explanation: A LEFT JOIN returns all rows from the left table (the first table listed, e.g., Employees), and the matched rows from the right table. If there is no match, the columns from the right table will be filled with NULL. This is commonly tested in SQL interview questions.

SELECT 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM 
    Employees e
LEFT JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID;

Explanation: If an employee has a DepartmentID of NULL (or one that doesn't exist in Departments), they will still be listed in these SQL JOIN examples, but their DepartmentName will be NULL.

SELECT 
    d.DepartmentName, 
    e.FirstName, 
    e.LastName
FROM 
    Departments d
LEFT JOIN 
    Employees e ON d.DepartmentID = e.DepartmentID;

Explanation: This is the reverse of the previous SQL practice questions example. By putting Departments on the left side of the LEFT JOIN, we guarantee all departments will be listed, even if no employees match (in which case FirstName and LastName would be NULL).

Explanation: A RIGHT JOIN is the mirror image of a LEFT JOIN in SQL JOIN examples. It returns all rows from the right table and matched rows from the left. The previous query (Q27) could be rewritten with a RIGHT JOIN as:

... FROM Employees e RIGHT JOIN Departments d ON ...

Most developers prefer to stick to LEFT JOINs for consistency in SQL query questions.

Explanation: A FULL OUTER JOIN returns all rows when there is a match in either the left or the right table in SQL interview questions. It's a combination of a LEFT JOIN and a RIGHT JOIN. It will show all employees (even those without departments) AND all departments (even those without employees).

SELECT 
    d.DepartmentName
FROM 
    Departments d
LEFT JOIN 
    Employees e ON d.DepartmentID = e.DepartmentID
WHERE 
    e.EmployeeID IS NULL;

Explanation: We perform a LEFT JOIN from Departments (to keep all departments) in this SQL JOIN examples question. For departments with no employees, the e.EmployeeID column (and all other Employees columns) will be NULL. We then use the WHERE clause to filter for only those NULL rows.

Explanation: A subquery is a SQL query nested inside another query. Subqueries can be used in SELECT, FROM, WHERE, or HAVING clauses. They are powerful tools in SQL subquery examples and SQL interview questions.

SELECT * FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Explanation: The subquery (SELECT AVG(Salary) FROM Employees) calculates the average salary first, then the outer query uses that value to filter employees. This is a classic SQL subquery examples question.

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
ORDER BY EmployeeCount DESC
LIMIT 1;

-- Or using a subquery:
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) = (
    SELECT MAX(EmployeeCount)
    FROM (SELECT COUNT(*) AS EmployeeCount FROM Employees GROUP BY DepartmentID) AS DeptCounts
);

Explanation: Both approaches work in SQL practice questions. The first sorts and limits results; the second uses a subquery to find the maximum count.

-- Using a subquery:
SELECT * FROM Employees
WHERE DepartmentID IN (
    SELECT DepartmentID FROM Departments WHERE Location = 'New York'
);

-- Using a JOIN (often more efficient):
SELECT e.* FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.Location = 'New York';

Explanation: This SQL subquery examples question demonstrates two approaches: subqueries and joins. Both are valid, though JOINs are often more performant.

SELECT * FROM Employees
WHERE Salary > (
    SELECT MAX(Salary) FROM Employees WHERE DepartmentID = 1
);

Explanation: The subquery finds the maximum salary in the Sales department (DepartmentID 1), and the outer query finds all employees earning more than that amount. This is an advanced SQL interview questions example.

Explanation: A correlated subquery is a subquery that references columns from the outer query. It is executed once for each row processed by the outer query, making it potentially slower than non-correlated subqueries. This concept appears frequently in advanced SQL subquery examples.

SELECT e1.FirstName, e1.LastName, e1.Salary, e1.DepartmentID
FROM Employees e1
WHERE e1.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.DepartmentID = e1.DepartmentID
);

Explanation: This is a correlated subquery in SQL practice questions. For each employee (e1), the subquery calculates the average salary of their department by referencing e1.DepartmentID.

INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary, HireDate)
VALUES (101, 'John', 'Doe', 1, 60000, '2023-05-15');

Explanation: The INSERT INTO statement adds new rows to a table in SQL query questions. You specify the columns and their corresponding values.

UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 101;

Explanation: The UPDATE statement modifies existing data. Always use a WHERE clause to avoid updating all rows accidentally. This is critical in SQL practice questions.

DELETE FROM Employees
WHERE EmployeeID = 101;

Explanation: The DELETE statement removes rows from a table. Always use a WHERE clause to specify which rows to delete in SQL interview questions, or you'll delete all rows!

UPDATE Employees
SET Salary = Salary * 1.10;

Explanation: You can use expressions in the SET clause of an UPDATE statement. This SQL query questions example increases each salary by multiplying it by 1.10 (adding 10%).

Explanation: Window functions perform calculations across a set of rows related to the current row, without collapsing the result set like SQL aggregate functions do with GROUP BY. They use the OVER() clause. Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), and LAG(). These are advanced SQL interview questions topics.

SELECT 
    FirstName, 
    LastName, 
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

Explanation: ROW_NUMBER() assigns a unique sequential number to each row based on the specified order in SQL practice questions. This is useful for pagination and ranking.

-- Using LIMIT/OFFSET (MySQL/PostgreSQL):
SELECT DISTINCT Salary 
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

-- Using a subquery:
SELECT MAX(Salary) FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

-- Using window functions:
SELECT Salary
FROM (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
) AS RankedSalaries
WHERE SalaryRank = 2;

Explanation: Multiple approaches exist for this common SQL interview questions challenge. Window functions with DENSE_RANK() handle ties gracefully.

Explanation:

• UNION combines the result sets of two or more SELECT statements and removes duplicate rows
• UNION ALL combines result sets but keeps all duplicates

Both require the same number of columns with compatible data types. This is frequently tested in SQL query questions.

SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 1
UNION
SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 2;

Explanation: UNION automatically removes duplicate rows if an employee appears in both departments in these SQL practice questions.

Explanation: An index is a database object that improves the speed of data retrieval operations on a table. It works like a book's index, allowing the database to find rows quickly without scanning the entire table. However, indexes slow down INSERT, UPDATE, and DELETE operations because the index must be updated. This concept is crucial for performance optimization in SQL interview questions.

CREATE INDEX idx_salary ON Employees(Salary);

Explanation: This creates an index named idx_salary on the Salary column of the Employees table, speeding up queries that filter or sort by salary in SQL query questions.

Explanation: A CASE statement is SQL's version of if-then-else logic. It allows you to add conditional logic to queries, returning different values based on specified conditions. This is useful in SQL practice questions for categorizing data.

SELECT 
    FirstName, 
    LastName, 
    Salary,
    CASE 
        WHEN Salary > 80000 THEN 'High'
        WHEN Salary > 50000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryBracket
FROM Employees;

SELECT FirstName, LastName, COUNT(*) AS DuplicateCount
FROM Employees
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1;

Explanation: This SQL GROUP BY questions example groups by the columns you want to check for duplicates (FirstName, LastName) and uses HAVING to filter for groups with more than one row. This is a practical SQL interview questions scenario for data quality checks.