iche diye gaye content ko clean kar diya gaya hai.
Saare # tags aur formatting symbols hata
N
diye gaye hain, lekin content aur sawalon ki sankhya (50) bilkul wahi hai.
1. What is a stored procedure? Write a simple example.
nswer:A stored procedure is a pre‑compiled SQL codeblock that can accept parameters
A
and perform complex operations. It improves reusability and security.
SQL
CREATEPROCEDUREGetEmployee
EmpIdINT
@
AS
BEGIN
SELECT*FROMEmployeesWHEREId=@EmpId;
END;
2. How do you pass parameters to a stored procedure?
nswer:Parameters are defined after the procedurename and can be given default values.
A
Use NULLIF to handle empty strings.
SQL
CREATEPROCEDUREGetOrders
@FromDateDATE=NULL
S
A
BEGIN
SET@FromDate=NULLIF(@FromDate,'');
SELECT*FROMOrdersWHEREOrderDate>=@FromDateOR@FromDateISNULL;
END;
3. Explain LEFT JOIN with an example.
nswer:LEFT JOIN returns all rows from the left tableand matching rows from the right table.
A
If no match, right side columns are NULL.
SQL
SELECT[Link], [Link]
FROMEmployees E
LEFTJOINOrders OON[Link]=[Link];
. What is the difference between INNER JOIN and
4
LEFT JOIN?
nswer:INNER JOIN returns only rows with matchesin both tables. LEFT JOIN returns all left
A
table rows even if no match on the right.
. How do you remove duplicate rows from a result
5
set?
Answer:Use DISTINCT or GROUP BY with appropriateaggregation.
SQL
SELECTDISTINCTEmpId, ClientIdFROMFeedLog;
6. Explain GROUP BY with an example.
nswer:GROUP BY groups rows that have the same valuesin specified columns, allowing
A
aggregation on other columns.
SQL
SELECTEmpId,COUNT(*)ASVisitCount
FROMFeedLog
GROUPBYEmpId;
7. What is the error Msg 8120 and how to fix it?
nswer:It occurs when a column in SELECT is not partof GROUP BY and not aggregated. Fix
A
by adding the column to GROUP BY or using an aggregate function.
SQL
- - Wrong
SELECTEmpId, ActivityDate,COUNT(*)
FROMFeedLog
GROUPBYEmpId;
- - Fix: add ActivityDate to GROUP BY
SELECTEmpId, ActivityDate,COUNT(*)
FROMFeedLog
GROUPBYEmpId, ActivityDate;
. How do you calculate the week number of the
8
month (not from January)?
Answer:Use (DAY(date) - 1) / 7 + 1. This resets everymonth.
SQL
SELECT((DAY(OrderDate)-1)/7+1)ASWeekNo
FROMOrders;
. Write a query to get the last visit date for each
9
outlet.
Answer:Use MAX(ActivityDate) grouped by outlet andemployee.
SQL
SELECTEmpId, ClientId,MAX(ActivityDate)ASLastVisit
FROMFeedLog
WHEREFeedTypeNOTIN('Leave')
GROUPBYEmpId, ClientId;
10. What is a subquery? Give an example.
nswer:A subquery is a query inside another query.It can be used in SELECT, FROM, or
A
WHERE clauses.
SQL
SELECTNameFROMEmployees
WHEREEmpIdIN(SELECTEmpIdFROMOrdersWHEREAmount>1000);
1 1. How do you convert PCS to Units when 5 PCS = 1
Unit?
Answer:Use a CASE expression.
SQL
SELECTQty,
CASEWHENUOM='PCS'THENQty/5.0ELSEQtyENDASUnits
FROMOrderDetails;
12. What is a window function? Show an example.
nswer:Window functions perform calculations acrossa set of rows related to the current
A
row without collapsing them. Example: SUM(...) OVER (PARTITION BY ...).
SQL
SELECTOrderNo, ProductName, Qty,
SUM(Qty)OVER(PARTITIONBYOrderNo)ASTotalQtyPerOrder
FROMOrderDetails;
13. How do you calculate Month‑to‑Date (MTD) filter?
Answer:Use DATEFROMPARTS to get the first day ofthe month of the end date.
SQL
WHEREActivityDate>=DATEFROMPARTS(YEAR(@ToDate),MONTH(@ToDate),1)
ANDActivityDate<DATEADD(DAY,1,@ToDate);
14. What is the purpose of NULLIF? Give an example.
nswer:NULLIF returns NULL if the two expressionsare equal; otherwise returns the first
A
expression. Useful to treat empty strings as NULL.
SQL
SET@FromDate=NULLIF(@FromDate,'');
15. How do you handle division by zero in SQL?
Answer:Use NULLIF on the denominator.
SQL
SELECTOrderQty*1.0/NULLIF(PC,0)ASAvgQty
FROMPerformance;
1 6. Write a query to show only rows where an outlet
had at least one visit (TC > 0) and show order metrics,
otherwise show 0.
Answer:Use CASE WHEN.
SQL
SELECTOutletId,
CASEWHENTotalVisits>0THENOrderQtyELSE0ENDASQty
FROM...
1 7. How do you join a subquery that aggregates
employee performance?
Answer:Write the subquery, give it an alias, andjoin on EmpId.
SQL
LEFTJOIN(
SELECTEmpId,MAX(OrderQty)ASOrderQty
FROMEmployeeWorkPerformance
GROUPBYEmpId
) EWPON[Link]=[Link]
1 8. What is the difference between COUNT(*) and
COUNT(column)?
nswer:COUNT(*) counts all rows including NULLs.COUNT(column) counts only non‑NULL
A
values in that column.
1 9. How do you get the latest CheckOut date per
outlet (only within current month)?
Answer:Use MAX with a CASE inside the aggregate andMTD date filter.
SQL
SELECTEmpId, ClientId,
MAX(CASEWHENFeedType='CheckOut'THENActivityDateEND)ASLastProductiveVisit
FROMFeedLog
WHEREActivityDate>=DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1)
GROUPBYEmpId, ClientId;
20. Explain the OVER(PARTITION BY ...) clause.
nswer:It divides the result set into partitionsand performs a calculation within each partition,
A
without grouping the rows.
SQL
SELECTOrderNo, ProductId, Qty,
SUM(Qty)OVER(PARTITIONBYOrderNo)ASOrderTotalQty
FROMOrderDetails;
1. How would you avoid duplicate rows when joining
2
a table that has multiple rows per key?
nswer:Either aggregate the table before joining,or use DISTINCT on the join condition, or use
A
a window function with ROW_NUMBER() to pick one row.
SQL
LEFTJOIN(
SELECTEmpId, ClientId,MAX(ActivityDate)ASLastVisit
FROMFeedLog
GROUPBYEmpId, ClientId
) LVON...
22. Write a query to count the number of CheckOut
and Unit type items separately per order.
Answer:Use conditional aggregation.
SQL
SELECTOrderId,
SUM(CASEWHENUOM='PCS'THENQtyEND)ASTotalPCS,
SUM(CASEWHENUOM='Unit'THENQtyEND)ASTotalUnits
FROMOrderDetails
GROUPBYOrderId;
3. What is the purpose of SET NOCOUNT ON in a
2
stored procedure?
nswer:It prevents SQL Server from sending the “(Xrows affected)” message after each DML
A
statement, which improves performance and reduces network traffic.
4. How do you handle an optional @EmpId parameter
2
that can be a manager (show subordinates)?
Answer:Use an EXISTS subquery on a subordinate table.
SQL
WHERE(@EmpIdISNULLOREmpId=@EmpId
OREXISTS(SELECT1FROMSubordinate SWHERE[Link]=EmpIdAND[Link]=
@EmpId))
25. Write a query to find the week number of a date
using ISO standard.
Answer:Use DATEPART(ISO_WEEK, date).
SQL
SELECTDATEPART(ISO_WEEK, OrderDate)ASIsoWeekNo
FROMOrders;
6. How do you convert an empty string to NULL in a
2
parameter?
Answer:Use SET @Param = NULLIF(@Param, '').
27. Explain the difference between MAX and SUM.
nswer:MAX returns the highest value in a group;SUM adds all values. Use MAX for a single
A
value (like last visit date) and SUM for totals.
8. Write a query that shows total units (5 PCS = 1
2
Unit) per order without using GROUP BY.
Answer:Use a window function.
SQL
SELECTOrderId, ProductId, Qty, UOM,
SUM(CASEWHENUOM='PCS'THENQty/5.0ELSEQtyEND)OVER(PARTITIONBYOrderId)AS
OrderUnits
FROMOrderDetails;
29. What is a derived table? Give an example.
Answer:A derived table is a subquery used in theFROM clause. It must have an alias.
SQL
SELECT*FROM(SELECTEmpId,COUNT(*)ASVisitsFROMFeedLogGROUPBYEmpId)ASDerived
WHEREVisits>10;
0. How do you prevent a “division by zero” error
3
when calculating average?
Answer:Use NULLIF(PC, 0).
SQL
SELECTOrderQty*1.0/NULLIF(PC,0)ASAvgQty
FROMPerformance;
1. Write a query that returns the latest activity for
3
each outlet, but only if it’s a CheckOut.
Answer:Use conditional aggregation.
SQL
SELECTEmpId, ClientId,
MAX(CASEWHENFeedType='CheckOut'THENActivityDateEND)ASLastCheckOut
FROMFeedLog
GROUPBYEmpId, ClientId;
32. Explain the use of CAST in date filtering.
Answer:CAST converts a datetime to date for comparison.
SQL
WHERECAST(CreatedDateASDATE)>=@FromDate
33. What is the purpose of CONCAT in SQL?
Answer:It joins two or more strings together.
SQL
SELECTCONCAT(Lat,',', Lng)ASLocationFROMOutlets;
4. How do you create a stored procedure that
3
accepts a date range and returns orders in that
period?
Answer:
SQL
CREATEPROCEDUREGetOrdersByDate
@FromDateDATE,@ToDateDATE
S
A
BEGIN
SELECT*FROMOrders
WHEREOrderDateBETWEEN@FromDateAND@ToDate;
END;
5. Write a query to find the total number of distinct
3
products sold per employee.
Answer:Use SUM(UniqueProducts) from the performancetable grouped by employee.
SQL
SELECTEmpId,SUM(UniqueProducts)ASTotalDistinctProducts
FROMEmployeeWorkPerformance
GROUPBYEmpId;
6. What is the difference between WHERE and
3
HAVING?
Answer:WHERE filters rows before aggregation; HAVINGfilters groups after aggregation.
7. How do you write a case‑insensitive search in SQL
3
Server?
nswer:By default SQL Server is case‑insensitiveunless using a binary collation. But you can
A
use UPPER() or LOWER().
SQL
SELECT*FROMProductsWHEREUPPER(Name)='T-SHIRT';
8. What is the purpose of the _deleted column seen
3
in many tables?
nswer:It is a soft‑delete flag. _deleted = 0 meansactive; _deleted = 1 means deleted. Queries
A
usually filter WHERE _deleted = 0.
9. Write a query that shows orders and their line
3
items, but only for orders that have at least one PCS
item.
Answer:Use EXISTS.
SQL
SELECT*FROMOrders O
WHEREEXISTS(SELECT1FROMOrderDetails ODWHERE[Link]=[Link]AND[Link]='PCS');
0. How do you calculate the total time an employee
4
spent in the market? (If stored in minutes)
Answer:Use SUM(TotalTimeSpentInMarket) grouped byemployee.
41. What is a self‑join? Give an example.
Answer:A self‑join is when a table is joined withitself. Example: finding employees under a
manager.
SQL
SELECT[Link]ASEmployee, [Link]ASManager
FROMEmployees E1
LEFTJOINEmployees E2ON[Link]=[Link];
2. How would you modify a stored procedure to
4
include a new column without breaking existing code?
nswer:Use ALTER PROCEDURE and add the new columnat the end of the SELECT list. Avoid
A
reordering existing columns.
3. Write a query to get the second latest CheckOut
4
date for each outlet.
Answer:Use a window function ROW_NUMBER().
SQL
WITHRankedAS(
SELECTEmpId, ClientId, ActivityDate,
ROW_NUMBER()OVER(PARTITIONBYEmpId,ClientIdORDERBYActivityDateDESC)ASrn
FROMFeedLogWHEREFeedType='CheckOut'
)
SELECT*FROMRankedWHERErn=2;
44. Explain the difference between VARCHAR and
NVARCHAR.
nswer:VARCHAR stores non‑Unicode (1 byte per character);NVARCHAR stores Unicode (2
A
bytes per character) and supports all languages.
45. What is an index? Why is it important for joins?
nswer:An index speeds up data retrieval. For joins,indexes on join columns (like EmpId,
A
ClientId) can dramatically improve performance.
6. Write a query that shows each employee and the
4
number of outlets they visited, even if zero.
Answer:Left join the outlet master with feed logcounts.
SQL
SELECT[Link],COUNT(DISTINCT[Link])ASOutletsVisited
FROMEmployees E
LEFTJOINFeedLog FON[Link]=[Link]AND[Link]NOTIN('Leave')
GROUPBY[Link];
47. How do you debug a stored procedure?
nswer:Use PRINT statements, run it with sample parameters,check intermediate results by
A
selecting from subqueries, and use SQL Server Profiler.
8. Write a query to find orders where the total
4
quantity in units (5 PCS = 1 Unit) is greater than 10.
Answer:
SQL
SELECTOrderId,SUM(CASEWHENUOM='PCS'THENQty/5.0ELSEQtyEND)ASTotalUnits
FROMOrderDetails
GROUPBYOrderId
HAVINGSUM(CASEWHENUOM='PCS'THENQty/5.0ELSEQtyEND)>10;
9. What is the purpose of BEGIN TRANSACTION and
4
COMMIT?
nswer:They define a transaction – a group of SQLstatements that are executed as a single
A
unit. If an error occurs, you can ROLLBACK to undo all changes.
0. How would you explain the entire
5
USP_GetEmployeeOutletEfficiency stored procedure
to a non‑technical manager?
Answer:
“ This procedure creates a report that shows, for each outlet, how many times an employee
visited, how many of those visits were productive (ended with a CheckOut), the last time they
visited, and the last time they had a CheckOut. It also shows order‑related totals (quantity,
value, unique products) – but those numbers are for the whole employee, not per outlet. If an
outlet had no visits, we show zero for order metrics. The report can be run for a single
employee or a manager to see their whole team.”