SQL Practice Question Tutorial

Basic SQL (SELECT, WHERE, ORDER BY)
• Select All Records with a Specific Condition in SQL
• Find Products with Price Greater Than 100 in SQL
• Retrieve Top 5 Most Expensive Products in SQL
• Sort Customers by Registration Date in Descending Order
• Retrieve All Discontinued Products in SQL
• List Employees Hired After 2018
• Retrieve Orders from the Last 30 Days in SQL
• Retrieve Names and Emails of Customers Whose Name Starts with ‘A’ in SQL
• Retrieve All Orders with a NULL ShipperID in SQL
• Find Out of Stock Products in SQL
Joins (INNER, LEFT, RIGHT, FULL)
• List Orders with Customer Names and Order Date
• Join Products with Suppliers to Retrieve Supplier Names
• Retrieve Orders with Their Shipping Company Names
• Find Customers Who Haven’t Placed Any Orders
• List All Products and Their Categories, Even If the Category Is Null
• Retrieve Orders, Products, and Quantities Using OrderDetails in SQL
• Find Employees Who Have Not Processed Any Orders in SQL
• Get Suppliers with No Products Listed in SQL
• List Orders with Product Details in SQL
• Calculate Total Sales per Customer Using SQL Joins
Group By and Aggregation
• Count Total Customers Per Country
• Find the Average Price of All Products in SQL
• Calculate Total Revenue from All Orders in SQL
• List Number of Orders Per Employee
• Find the Maximum Quantity Sold in Any Order
• Show Total Number of Orders Per Day
• Display Total Payments Received Per Customer
• List Categories with Average Product Price Greater Than 100
• Count the Number of Discontinued Products
• Total Units Sold Per Product
Subqueries & CTEs
• Find Customers Who Placed More Than 5 Orders
• List Products with Prices Higher Than Average
• Get the Most Expensive Product(s) Per Category
• Find Top 3 Customers by Order Count
• List Employees with Higher Than Average Number of Orders
• Retrieve Orders with More Than 3 Different Products
• Use CTE to Find Cumulative Daily Payments
• Running Total of Payments per Customer
• Show Categories with the Lowest Priced Product Less Than 50
• Retrieve Orders Where Total Amount Is Greater Than 500
Stored Procedures & Functions
• Stored Procedure to Get Customer Order History
• Stored Procedure to Insert a New Product
• Scalar Function to Calculate Discount Price
• Inline Table-Valued Function for Orders in a Date Range
• Procedure to Delete All Discontinued Products
• Function to Calculate Total Order Amount
• Write a Procedure That Updates Stock After Order
• Write a Procedure to Log Inventory Change
• Create a Procedure to Get Supplier Details by City
• Create a Function to Return the Employee's Full Name
Triggers
• Trigger to Prevent Deletion from Customers Table
• AFTER INSERT Trigger to Log Order Information
• Trigger to Update Stock After Inserting Order Details
• Write a Trigger That Logs Payment Changes
• Add a Trigger to Check for Negative Stock Levels
Views
• Create a View for Customer Order Summary
• Create a View for Products in Stock with Supplier Name
• View of Order Details with Total Amount Per Row
• Create a View of Top 5 Selling Products
• View to See Payments Per Month
Indexing and Performance
• Create a Non-Clustered Index on Products(ProductName)
• Create an Index on Orders(OrderDate)
• Analyze Execution Plan for a Query with and without Index
• Create a Composite Index on OrderDetails(OrderID, ProductID)
• Discuss Pros and Cons of Indexing PaymentMethod
Transactions & Error Handling
• Place an Order and Update Stock in SQL
• Add TRY...CATCH for Payment Insert Errors
• Create a Transaction That Rolls Back If Stock Is Insufficient
• Handle Error When Inserting a Duplicate Customer Email in SQL
• Add Transaction for Order Cancellation
Window Functions
• Use ROW_NUMBER to Get the Latest Order per Customer
• Use RANK() to Get the Top 3 Employees by Order Count
• Show LEAD() and LAG() for Customer Payments
• Use NTILE(4) to Divide Customers into Revenue Quartiles
• Find Products with Sales Growing Month-over-Month
Data Modelling / Normalization Questions
• Explain 1NF, 2NF, and 3NF Using Your Tables
• Identify if the Products Table is Normalized
• Is it Okay to Store CategoryName in Products?
• Handling Multiple Addresses per Customer in SQL
• Understanding One-to-Many Relationship Between Orders and OrderDetails
Advanced SQL / Miscellaneous
• Write a Query to Pivot Payments by Month
• Write a Query to Unpivot Inventory Change Logs
• Get First and Last Order per Customer
• Find Orders with No Payments
• List Employees Who Processed More Than 2 Orders
• Find Duplicate Products by Name
• Check for Customers with Invalid Emails
• Show Top 3 Suppliers by Product Count
• Get Product Stock History Using InventoryLogs
• Write Dynamic SQL to Filter Customers by Any City
Data Types & Constraints
• Add CHECK Constraint on Quantity > 0
• Add UNIQUE Constraint to Customer Email
• Alter Products Table to Add Default Value for UnitsInStock
• Create a Computed Column for FullName in Employees
• Change Column Data Type of PaymentMethod to VARCHAR(100)
Backup, Restore & Maintenance
• Write a Command to Back Up the Database