SQL Server Interview Topic 4: Functions and Reusable SQL

This topic checks whether you can write SQL that is useful in real applications, not just basic queries. Full stack developers often use functions, views, stored procedures, and CTEs for reports and backend APIs.

🎯 Interview Goal

You should be able to explain reusable SQL in simple words and say when to use a function, CTE, view, or stored procedure.

Q29. What are string functions in SQL Server?

Quick interview answer:

String functions work with text values. Examples include LEN, UPPER, LOWER, LTRIM, RTRIM, CONCAT, and SUBSTRING. We use them to clean or format text like student names, codes, and mobile numbers.

Study in detail: String Functions - This article explains common text functions with examples.

Q30. What are date functions in SQL Server?

Quick interview answer:

Date functions work with date and time values. Examples include GETDATE, DATEADD, DATEDIFF, YEAR, MONTH, and DAY. We use them for reports like today's attendance or students admitted in the last 30 days.

Study in detail: Date Functions - This lesson shows how to calculate and filter dates.

Q31. What is CASE in SQL Server?

Quick interview answer:

CASE adds if-else style logic inside a SQL query. It can convert values into readable labels. For example, marks can be shown as Pass or Fail based on a condition.

Study in detail: Math Functions and CASE - This article explains CASE with simple report examples.

Q32. What are window functions?

Quick interview answer:

Window functions calculate values across related rows without collapsing the result into one row per group. Examples include ROW_NUMBER, RANK, and running totals. They are useful for ranking students or numbering rows in reports.

Study in detail: Window Functions - This lesson explains ranking and row numbering with examples.

Q33. What is a CTE?

Quick interview answer:

A CTE, or Common Table Expression, is a temporary named result used inside one query. It makes complex SQL easier to read. It is useful when a report query has multiple steps.

Study in detail: CTEs - This article explains how CTEs simplify longer SQL queries.

Q34. What is a stored procedure?

Quick interview answer:

A stored procedure is a saved SQL program inside the database. It can accept parameters and run multiple SQL statements. APIs often call stored procedures for reusable operations like getting student reports.

Study in detail: Stored Procedures and Functions - This lesson explains stored procedures with practical examples.

Q35. What is a SQL function?

Quick interview answer:

A SQL function returns a value or table result. It is usually used for reusable calculations. For example, a function can calculate grade based on marks, but it should not be used for heavy business logic.

Study in detail: Stored Procedures and Functions - This article compares procedures and functions.

Q36. What is a view?

Quick interview answer:

A view is a saved SELECT query that can be used like a table. It helps simplify complex joins and can hide columns that users should not see. A view does not normally store data by itself.

Study in detail: Views - This article explains views using report-style examples.

Q37. Stored procedure vs function vs view: when do you use each?

Quick interview answer:

Use a stored procedure for an operation or report that may need parameters and multiple statements. Use a function for reusable calculations. Use a view to simplify a reusable SELECT query.

Study in detail: Stored Procedures and Functions and Views - These lessons explain the difference in detail.

Practice Before Next Topic

Write queries for these tasks:

1. Use CASE to show Pass or Fail based on marks.
2. Use ROW_NUMBER to number students by marks.
3. Create a CTE for students above average marks.
4. Create a stored procedure to get students by class.
5. Create a view for active students.

💡 Beginner Interview Tip

Do not say stored procedures, functions, and views are the same. Explain the purpose of each one with a small example.