ΉΣΛЯƬ々ΉΛᄃ𝐊ΣЯ

Commonly Asked SQL Server scenarios based interview questions!! Scenario 1: Sales Data Analysis •⁠ ⁠Question: Write a query to get the total sales amount for each month in the current year. •⁠ ⁠Answer: SELECT MONTH(sales_date) AS month, SUM(sales_amount) AS total_sales FROM sales_data WHERE YEAR(sales_date) = YEAR(GETDATE()) GROUP BY MONTH(sales_date) Scenario 2: Employee Attendance •⁠ ⁠Question: Write a query to get the employees who have been absent for more than 3 days in the current month. •⁠ ⁠Answer: WITH absences AS ( SELECT employee_id, attendance_date, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY attendance_date) AS absence_count FROM attendance_data WHERE attendance_status = 'Absent' AND YEAR(attendance_date) = YEAR(GETDATE()) AND MONTH(attendance_date) = MONTH(GETDATE()) ) SELECT employee_id FROM absences WHERE absence_count > 3 Scenario 3: Customer Order History •⁠ ⁠Question: Write a query to get the customers who have placed an order in the last 30 days but not in the last 7 days. •⁠ ⁠Answer: WITH recent_orders AS ( SELECT customer_id, order_date FROM order_data WHERE order_date >= DATEADD(DAY, -30, GETDATE()) ), latest_orders AS ( SELECT customer_id, order_date Here you can find essential SQL Interview Resources👇 https://topmate.io/analyst/864764 Like this post if you need more 👍❤️ Hope it helps :)

Here are two SQL queries related to performance, along with explanations: Query 1: Identifying Long-Running Queries SELECT query_text, total_logical_reads, total_logical_writes, avg_cpu_time, avg_duration FROM ( SELECT qt.query_text, qs.total_logical_reads, qs.total_logical_writes, qs.avg_cpu_time, qs.avg_duration, ROW_NUMBER() OVER (ORDER BY qs.avg_duration DESC) AS row_num FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ) AS subquery WHERE row_num <= 10; Explanation: 1.⁠ ⁠This query uses Dynamic Management Views (DMVs) to analyze query performance. 2.⁠ ⁠sys.dm_exec_query_stats provides statistics on query execution. 3.⁠ ⁠sys.dm_exec_sql_text retrieves the query text. 4.⁠ ⁠The subquery ranks queries by average duration in descending order. 5.⁠ ⁠The outer query selects the top 10 longest-running queries. Query 2: Finding Index Usage SELECT object_name(ps.object_id) AS table_name, i.name AS index_name, user_seeks, user_scans, user_lookups, user_updates FROM sys.dm_db_index_usage_stats us INNER JOIN sys.indexes i ON us.object_id = i.object_id AND us.index_id = i.index_id INNER JOIN sys.partition_stats ps ON us.object_id = ps.object_id AND us.index_id = ps.index_id WHERE database_id = DB_ID() AND user_seeks + user_scans + user_lookups > 0; Explanation: 1.⁠ ⁠This query uses DMVs to analyze index usage. 2.⁠ ⁠sys.dm_db_index_usage_stats provides statistics on index usage. 3.⁠ ⁠sys.indexes retrieves index metadata. 4.⁠ ⁠sys.partition_stats provides partition-level statistics. 5.⁠ ⁠The query filters results to the current database and indexes with non-zero usage. 6.⁠ ⁠The output shows tables, indexes, and usage statistics (seeks, scans, lookups, updates). Here you can find essential SQL Interview Resources👇 https://topmate.io/analyst/864764 Like this post if you need more 👍❤️ Hope it helps :)

🔍 Retrieve Order Numbers and Product Names SELECT orders.orderNumber, products.productName FROM orderdetails INNER JOIN orders ON orderdetails.orderNumber = orders.orderNumber INNER JOIN products ON orderdetails.productCode = products.productCode; This query pulls order numbers and product names using INNER JOINs across the relevant tables. 💳 List All Customers and Their Payments SELECT customers.customerName, payments.paymentDate, payments.amount FROM customers INNER JOIN payments ON customers.customerNumber = payments.customerNumber; It joins customers and payments to show customer payments, dates, and amounts. 🛒 Find All Orders and Display Customer Details SELECT customers.customerName, orders.orderNumber, orders.orderDate FROM customers LEFT JOIN orders ON customers.customerNumber = orders.customerNumber; This LEFT JOIN lists customers with or without orders. 👥 List Employees and Customer Counts SELECT employees.firstName, employees.lastName, COUNT(customers.customerNumber) AS customerCount FROM employees LEFT JOIN customers ON employees.employeeNumber = customers.salesRepEmployeeNumber GROUP BY employees.employeeNumber; This counts how many customers each employee manages. Here you can find essential SQL Interview Resources👇 https://topmate.io/analyst/864764 Like this post if you need more 👍❤️ Hope it helps :)