SQL Server Tutorial

List products from cheapest to most expensive, breaking ties by name.

Count how many orders each status has.

List categories whose total product value is under 20,000.

Insert a customer named Zoya from Jaipur and return the new CustomerId.

Mark order 103 as Delivered and show its old and new status.

Write a soft delete: add an IsDeleted BIT column to Products and “delete” the Pen.

List students who are NOT enrolled in any course.

List products that have been ordered at least once, using IN.

Use a CTE to find each customer’s number of orders, then return only customers with more than one order.

Return only the most expensive product in each category.

Return the total quantity of items sold across all orders.

Create a view vActiveProducts showing only Name and Price, then query it.

Call dbo.EnrollStudent from C# with ADO.NET and read the output parameter.

Write an inline TVF dbo.fn_OrdersByStatus(@status) returning OrderId and OrderDate.

Explain what inserted and deleted contain during a DELETE statement.

What would the balances be if XACT_ABORT were OFF and there were no TRY…CATCH, and the second UPDATE failed?

Design an index for: SELECT Name, Price FROM Products WHERE Category = @c ORDER BY Price.

A plan shows an Index Seek followed by 50,000 Key Lookups. What is the usual fix?

Rewrite WHERE LEFT(Name, 3) = 'Lap' to be SARGable.

Add a CHECK constraint to Orders allowing only Pending, Delivered or Cancelled statuses.

Recreate the OrderItems → Orders foreign key with ON DELETE CASCADE and explain the effect.

A Students table has columns Course1, Course2, Course3. Which normal form does it break and how would you fix it?

Grant a reporting role read-only access to a reporting schema.

Exercise 2: return each customer’s most recent order (customer name, order id, order date).