Interview Q&A

The BETWEEN operator filters the result set within a given range. It is inclusive, meaning it

includes the boundary values.

Example:

SELECT *

FROM employees

WHERE salary BETWEEN 40000 AND 60000;

This query will return employees whose salaries are between 40,000 and 60,000 (inclusive).

The LIKE operator is used to search for a specified pattern in a column.

• % matches any sequence of characters.
• _ matches a single character.

Example:

SELECT *

FROM employees

WHERE name LIKE 'J%n'; -- Names that start with 'J' and end with

'n'

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

Common aggregate functions include:

• COUNT(): Returns the number of rows.
• SUM(): Returns the sum of a column.
• AVG(): Returns the average of a column.
• MAX(): Returns the maximum value.
• MIN(): Returns the minimum value.

Example:

SELECT AVG(salary)

FROM employees;

• COUNT(*): Counts all rows, including rows with NULL values in any column.
• COUNT(column_name): Counts only non-NULL values in the specified column.

Example:

SELECT COUNT(*) FROM employees; -- Counts all rows

SELECT COUNT(salary) FROM employees; -- Counts rows where salary is

NOT NULL

Conditional aggregation allows you to apply aggregate functions to a subset of data that

meets a certain condition. This is usually done with a CASE expression.

Example:

SELECT department,

SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,

SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count

FROM employees

GROUP BY department;

This query counts the number of male and female employees in each department.

Common Table Expression (CTE) is a temporary result set that can be referenced within

SELECT, INSERT, UPDATE, or DELETE statement. CTEs are often used for organizing

complex queries.

Example:

WITH EmployeeCTE AS (

SELECT name, salary

FROM employees

WHERE salary > 50000

SELECT * FROM EmployeeCTE;

The CASE statement is SQL’s way of handling if-else logic in queries. It allows you to return

different values based on certain conditions.

Example:

SELECT Name,

CASE

WHEN Age < 18 THEN 'Minor'

WHEN Age >= 18 AND Age < 60 THEN 'Adult'

ELSE 'Senior'

END AS AgeGroup

FROM Employees;

• LEFT JOIN: Returns all records from the left table, and matched records from the

right table. If there's no match, NULL values are returned for the right table's

columns.

• RIGHT JOIN: Returns all records from the right table, and matched records from the

left table. If there's no match, NULL values are returned for the left table's columns.

The UNION ALL operator combines the result sets of two or more queries, but unlike

UNION, it does not remove duplicate rows.

Example:

SELECT name FROM employees

UNION ALL

SELECT name FROM contractors;

This will return all names from both employees and contractors, including duplicates.

subquery in the FROM clause allows you to treat the result of a query as a temporary table,

which can then be joined or queried further.

Example:

SELECT avg_salary

FROM (SELECT AVG(salary) AS avg_salary FROM employees) AS avg_table;

This query calculates the average salary using a subquery in the FROM clause.

Views

view in SQL is a virtual table that is defined by a SELECT query. It does not store data

itself but rather provides a way to view and work with the results of a query as if it were a

table. Views allow users to access specific, formatted data without modifying the underlying

tables directly.

• Purpose: Simplify complex queries, abstract the database schema, and improve

security by exposing only the necessary data.

Example:

CREATE VIEW employee_view AS

SELECT id, name, department

FROM employees;

You can then query the view like a regular table:

SELECT * FROM employee_view;

• Data Storage:
• Table: Stores actual data in the database.
• View: A virtual table that shows data from one or more tables but does not

store data. It derives its data from the underlying tables each time it is

queried.

• Modifications:
• Table: You can insert, update, or delete data directly.
• View: You cannot modify data in a view unless certain conditions are met.

Some views (those based on a single table) are updatable, while others

(especially those with JOINs, aggregations, or complex logic) are not.

• Performance:
• Table: Generally optimized for performance with indexes, data distribution,

and so on.

• View: May have slower performance due to the query being executed each

time the view is accessed, especially if it's a complex view or involves large

datasets.

ccess to the data without revealing the full database schema.

• Security: By granting access to a view instead of a table, you can restrict access to

sensitive data and only expose the columns or rows that are necessary.

• Consistency: A view can standardize how data is accessed across multiple queries

or applications, ensuring consistent results.

• Reduced Redundancy: Rather than writing the same complex query multiple times,

you can create a view to encapsulate it and reuse the view in different queries.

• Simplifies Complex Queries: Views can encapsulate complex queries, making

them reusable and easier to manage.

• Data Abstraction: Views abstract the underlying table structure and allow for easier

access to the data without revealing the full database schema.

• Security: By granting access to a view instead of a table, you can restrict access to

sensitive data and only expose the columns or rows that are necessary.

• Consistency: A view can standardize how data is accessed across multiple queries

or applications, ensuring consistent results.

• Reduced Redundancy: Rather than writing the same complex query multiple times,

you can create a view to encapsulate it and reuse the view in different queries.

Yes, views can be updated in SQL, but there are conditions for when this is possible:

• Updatable Views: A view is updatable if it:
• Is based on a single table.
• Does not contain aggregation functions like COUNT(), AVG(), or SUM().
• Does not include DISTINCT, GROUP BY, JOIN, or other complex operations

that modify the set of rows.

• Non-Updatable Views: Views that use complex JOINs, aggregations, or GROUP BY

clauses are typically non-updatable.

You can still modify data through these views by using triggers, such as INSTEAD

OF triggers, which allow you to define custom behavior for updating or deleting rows.

Example of an updatable view:

CREATE VIEW simple_view AS

SELECT id, name, salary FROM employees;

• - You can now update 'simple_view' directly:

UPDATE simple_view SET salary = 60000 WHERE id = 101;

Under what conditions?

Yes, views can be updated in SQL, but there are conditions for when this is possible:

• Updatable Views: A view is updatable if it:
• Is based on a single table.
• Does not contain aggregation functions like COUNT(), AVG(), or SUM().
• Does not include DISTINCT, GROUP BY, JOIN, or other complex operations

that modify the set of rows.

• Non-Updatable Views: Views that use complex JOINs, aggregations, or GROUP BY

clauses are typically non-updatable.

You can still modify data through these views by using triggers, such as INSTEAD

OF triggers, which allow you to define custom behavior for updating or deleting rows.

Example of an updatable view:

CREATE VIEW simple_view AS

SELECT id, name, salary FROM employees;

• - You can now update 'simple_view' directly:

UPDATE simple_view SET salary = 60000 WHERE id = 101;

n indexed view (also called a materialized view) in SQL Server is a view that has a

unique clustered index created on it. This results in the view storing the query results

physically in the database, similar to a table. It is useful for improving performance when you

have complex queries that aggregate data, as the results of the view are precomputed and

stored.

• Benefits:
• Faster read performance for complex aggregations or queries.
• Reduces the need for recomputing the result of a complex query each time

the view is accessed.

• Downside:
• Insert, update, or delete operations on the underlying tables will incur

dditional overhead because the indexed view must also be updated.

Example:

CREATE VIEW employee_summary AS

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department;

• - Create a clustered index on the view

CREATE UNIQUE CLUSTERED INDEX idx_employee_summary

ON employee_summary(department);

Answer: re certain DBMS-specific features and optimizations (e.g., indexed views in SQL Server).

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Here’s how you can create a view in each of these DBMS:

SQL Server:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

PostgreSQL:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

MySQL:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

The syntax for creating a view is generally the same across these databases, although there

are certain DBMS-specific features and optimizations (e.g., indexed views in SQL Server).

materialized view is a database object that stores the result of a query physically, similar

to an indexed view but more generalized. Unlike regular views, which generate their result

dynamically when queried, materialized views store the result set and can be periodically

refreshed.

• Advantages:
• Faster query performance, especially for complex queries or aggregations,

since the data is precomputed and stored.

• Refresh: The data in a materialized view may become outdated over time, so it

needs to be refreshed periodically, either manually or automatically, depending on the

DBMS.

Example:

CREATE MATERIALIZED VIEW sales_summary AS

SELECT product_id, SUM(sales) AS total_sales

FROM sales

GROUP BY product_id;

• - Refresh the materialized view when needed:

REFRESH MATERIALIZED VIEW sales_summary;

• Materialized views are supported in systems like PostgreSQL and Oracle, but MySQL does

not have a built-in materialized view feature. You can simulate one using tables and

scheduled jobs.

To delete a view, you use the DROP VIEW statement. This removes the view from the

database entirely.

Example:

DROP VIEW view_name;

Be cautious when dropping a view, as it will no longer be available for use in queries. Ensure

no other objects are dependent on the view before dropping it.

nd query data without modifying the underlying tables directly.

• Views do not support procedural logic, loops, or variables.
• Stored Procedure:
• A stored procedure is a set of SQL statements that can be executed as a

single unit. It can include complex logic like loops, conditionals, and multiple

queries.

• Stored procedures can perform INSERT, UPDATE, DELETE, and other

operations on the database. They can also return results.

Example of a stored procedure:

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = emp_id;

END;

• View:
• A view is a virtual table defined by a SELECT query. It provides a way to view

and query data without modifying the underlying tables directly.

• Views do not support procedural logic, loops, or variables.
• Stored Procedure:
• A stored procedure is a set of SQL statements that can be executed as a

single unit. It can include complex logic like loops, conditionals, and multiple

queries.

• Stored procedures can perform INSERT, UPDATE, DELETE, and other

operations on the database. They can also return results.

Example of a stored procedure:

CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)

BEGIN

SELECT salary FROM employees WHERE id = emp_id;

END;

Optimizing views is important to ensure that your queries are efficient. Here are some

strategies:

• Avoid Complex Views: Avoid creating views with complex logic (e.g., multiple

JOINs, GROUP BY, or subqueries), as they can result in slower performance. Keep

views simple and focused.

• Indexing: Ensure that the underlying tables have proper indexes, especially on the

columns used in JOIN or WHERE conditions.

• Materialized Views: Use materialized views when querying large datasets or

performing heavy aggregations. These views store the results physically and can be

refreshed periodically.

• Limit the Data: Use WHERE clauses in your views to filter unnecessary rows and

columns. Only select the data that is necessary.

• Optimize the Base Tables: Since views reflect the data from base tables, ensure

those tables are optimized (e.g., with appropriate indexing and normalization).

• Avoid Nested Views: A view based on another view can result in poor performance,

as the inner view’s query needs to be executed each time the outer view is queried.

Stored Procedures & Functions

stored procedure is a precompiled collection of SQL statements that can be executed as

single unit. It allows you to encapsulate logic, such as data manipulation, complex queries,

or repetitive tasks, into reusable blocks. Stored procedures can accept parameters, perform

ctions like SELECT, INSERT, UPDATE, and DELETE, and return results.

• Benefits: Improved performance (since the procedure is precompiled), code

reusability, centralized logic, and security (by restricting direct access to tables).

Example:

CREATE PROCEDURE GetEmployeeDetails (IN emp_id INT)

BEGIN

SELECT * FROM employees WHERE id = emp_id;

END;