Top SQL Server Interview Questions And Answers

Question: Explain the difference between HAVING and WHERE in SQL.

Answer:

Both HAVING and WHERE are used to filter records in SQL, but they are used in different contexts and serve different purposes. Here’s a detailed explanation of their differences:

1. WHERE Clause:

   • Purpose: The WHERE clause is used to filter records before any grouping is done (i.e., it operates on individual rows in the table).
   • Used with: It can be used with SELECT, INSERT, UPDATE, and DELETE statements.
   • Applies to: Non-aggregated columns (columns that are not the result of an aggregate function like COUNT(), SUM(), etc.).
   • Execution order: It is executed before the GROUP BY clause (if present).

   Example:

   SELECT name, salary
   FROM Employees
   WHERE salary > 50000;

   • This query filters employees who have a salary greater than 50,000 before any grouping or aggregation happens.

2. HAVING Clause:

   • Purpose: The HAVING clause is used to filter records after the data has been grouped (i.e., it operates on aggregated data, or the result of GROUP BY).
   • Used with: It is used with GROUP BY and is often used in conjunction with aggregate functions like SUM(), COUNT(), AVG(), etc.
   • Applies to: Aggregated data, meaning it filters groups of rows after the grouping has been done.
   • Execution order: It is executed after the GROUP BY clause.

   Example:

   SELECT department, AVG(salary)
   FROM Employees
   GROUP BY department
   HAVING AVG(salary) > 50000;

   • This query first groups employees by their department and then filters the groups where the average salary is greater than 50,000.

Key Differences:

Aspect	WHERE	HAVING
Purpose	Filters rows before grouping	Filters groups after aggregation
Used With	Individual rows (non-aggregated columns)	Aggregated data (using functions like COUNT(), SUM())
Can Be Used In	SELECT, INSERT, UPDATE, DELETE	SELECT with GROUP BY
Execution Order	Executes before GROUP BY	Executes after GROUP BY

Example Showing the Difference:

Using WHERE:

SELECT department, salary
FROM Employees
WHERE salary > 50000;

This query filters employees whose salary is greater than 50,000 before any grouping occurs.

Using HAVING with GROUP BY:

SELECT department, AVG(salary)
FROM Employees
GROUP BY department
HAVING AVG(salary) > 50000;

This query first groups employees by department and then filters the departments where the average salary is greater than 50,000. HAVING is used to filter the aggregated result.

Conclusion:

• Use WHERE to filter individual rows based on column values before aggregation.
• Use HAVING to filter results after aggregation, typically when using GROUP BY and aggregate functions.

Question: What is a view in SQL?

Answer:

A view in SQL is a virtual table that represents the result of a stored query. It does not store data itself but rather provides a way to simplify complex queries by encapsulating them into a reusable structure. Views allow you to present data from one or more tables in a particular format, and they are treated like regular tables in SQL queries.

Key characteristics of a view:

1. Virtual Table: A view is a virtual table that consists of a SELECT query. It doesn’t physically store data but retrieves data from underlying tables each time it’s accessed.

2. Read-Only or Updatable: Views can be either read-only or updatable, depending on how the view is defined and the database’s support. Simple views based on a single table are often updatable, but complex views involving joins, aggregation, or DISTINCT may be read-only.

3. Encapsulation: Views can encapsulate complex logic and simplify queries for end-users or other applications. They help abstract away the complexity of underlying data structures and simplify query writing.

4. Security: Views can be used to limit access to certain columns or rows of a table, improving security. For example, you can create a view that only exposes certain columns, restricting access to sensitive data.

5. Dynamic: The data in a view is dynamic because it is generated based on the underlying tables. If the data in the tables changes, the view reflects the changes immediately when accessed.

Syntax for Creating a View:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

Suppose we have a table Employees:

EmployeeID	Name	Department	Salary
1	John	HR	50000
2	Sarah	IT	60000
3	David	HR	55000

Now, if we want to create a view that shows only the employees in the HR department:

CREATE VIEW HR_Employees AS
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Department = 'HR';

The view HR_Employees will now present:

EmployeeID	Name	Salary
1	John	50000
3	David	55000

This view can be queried just like a regular table:

SELECT * FROM HR_Employees;

Advantages of Using Views:

1. Simplified Queries: Views abstract complex queries and make it easier to retrieve the required data without repeatedly writing complex SQL statements.

2. Data Security: By using views, you can restrict access to specific columns or rows of a table, providing an extra layer of security.

3. Data Integrity: Views help maintain consistency by ensuring users work with the same, pre-defined logic for querying data, rather than writing custom queries that might vary across different applications.

Types of Views:

1. Simple Views: A view based on a single table without complex joins or aggregations.
2. Complex Views: A view based on multiple tables using joins, groupings, or aggregate functions.

Modifying Data Through Views:

While views are primarily used for querying data, in certain cases, they can be used to update underlying data. However, there are restrictions:

• Updatable Views: These are views based on a single table where you can perform INSERT, UPDATE, or DELETE operations.
• Non-Updatable Views: Views based on complex queries like joins, aggregates, or grouped data may not allow direct updates.

Dropping a View:

To remove a view, you can use the DROP VIEW statement:

DROP VIEW view_name;

Conclusion:

• A view is a virtual table in SQL that is defined by a query and does not store data itself.
• It simplifies complex queries, improves security, and can abstract away complex table structures.
• Views can be used to improve maintainability and readability of SQL code by encapsulating repetitive or complex logic.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as SQL interview questions, SQL interview experiences, and details about various SQL job positions. Click here to check it out.