Another set of SQL interview questions ask in Data Analyst Technical Interviews.

Save it while preparing

for the interview.

1) Retrieve the total number of customers who placed orders from each eatery since January 2021
across all countries. Return the eatery's name, the total number of customers, and their names in one
row.

2) For each eatery, identify the customer who placed the 3rd order. Return the eatery's name and the
customer's name.

3) Identify the Pakistani eatery with the highest cumulative order value since January 2021. Provide the
name of the customer who contributed the most to the total order value for that eatery, along with the
eatery's name and the total value of orders.

4)Determine which customers ordered from "Crunchy Delights" and "Sizzling Bites" in the year 2021 in
Pakistan. Return the names of the customers, the eatery names, the maximum order value placed by the
customer for each eatery, and the maximum order value placed by that customer across both eateries.

5)Find the eatery with the highest number of orders placed by customers from the United States since
January 2021. Return the eatery's name and the total number of orders.

6)Retrieve the names of customers who have placed orders from at least three different eateries in 2021.
Return the names of these customers along with the count of eateries they've ordered from.

In data analyst interviews, 50% of the time hiring manager after sql, start to 𝐚𝐬𝐤 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬 𝐨𝐧
𝐄𝐱𝐜𝐞𝐥, and same goes with my colleagues and friends. After discussing with them, i have prepared
some list of questions for you. Here is the break down:
The interviewer show some datasets and give you scenarios to solve:

📊 Excel Functions Scenario 1:

Imagine you have a dataset containing sales figures for different products across various regions. The
management wants to calculate the total sales for each product category. How would you use the SUMIF
function to accomplish this task efficiently?

🔄 Excel Functions Scenario 2:

You have a large dataset with customer information including names, email addresses, and phone
numbers. Your task is to extract the domain names from the email addresses provided. Explain how you
would use the RIGHT, LEFT, and FIND functions in combination to achieve this.

🔍 Pivot Tables Scenario 1:

You're given a dataset containing sales data for a retail store over the past year. Your manager wants to
analyze the sales performance by month and product category. Describe how you would create a pivot
table to summarize and visualize this information effectively.

📈 Pivot Tables Scenario 2:

Your company has conducted a survey with multiple-choice questions. The survey results are stored in
an Excel sheet with each respondent's choices recorded in separate columns. How would you use pivot
tables to analyze the survey data and present the distribution of responses for each question?

🔗 Power Query Scenario 1:

You receive a CSV file with inconsistent date formats in one of the columns. Some dates are in
"MM/DD/YYYY" format, while others are in "DD/MM/YYYY" format. Explain how you would use Power
Query to standardize the date format across the entire column.
🔄 Power Query Scenario 2:

You're working with a large dataset that contains duplicate rows. Before proceeding with analysis, you
need to remove these duplicate rows to ensure data accuracy. How would you use Power Query to
identify and remove duplicate records from the dataset efficiently?

📊 Excel Functions Question:

What is the difference between the VLOOKUP and INDEX-MATCH functions in Excel? When would you
use one over the other?

🔍 Power Query Question:

What are some advantages of using Power Query over traditional data manipulation techniques in Excel?
Provide examples.

📈 Excel Functions Question:

How would you use the AVERAGEIF and AVERAGEIFS functions in Excel to calculate the average sales for
a specific product category within a given date range?

🔗 Pivot Tables Question:

What are "Slicers" in Excel pivot tables, and how can they enhance data analysis and visualization?"

🔄 Power Query Question:

Explain the process of merging queries in Power Query. How does it help in combining data from
different sources for analysis?

MNC SQL interview questions for data analyst& bussiness analyst ! 💼

1. Explain Order of Execution of SQL query

2. Provide a use case for each of the functions Rank, Dense_Rank & Row_Number ( 💡 majority struggle )

3. Write a query to find the cumulative sum/Running Total

4. Find the Most selling product by sales/ highest Salary of employees

5. Write a query to find the 2nd/nth highest Salary of employees

6. Difference between union vs union all

7. Identify if there any duplicates in a table

8. Scenario based Joins question, understanding of Inner, Left and Outer Joins via simple yet tricky
question

9. LAG, write a query to find all those records where the transaction value is greater then previous
transaction value

10. Rank vs Dense Rank, query to find the 2nd highest Salary of employee

( Ideal soln should handle ties)

11. Write a query to find the Running Difference (Ideal sol'n using windows function)

12. Write a query to display year on year/month on month growth

13. Write a query to find rolling average of daily sign-ups

14. Write a query to find the running difference using self join (helps in understanding the logical
approach, ideally this question is solved via windows function)

15. Write a query to find the cumulative sum using self join

(helps in understanding the logical approach, ideally this question is solved via windows function)

16. Optimize a query to find the latest transaction for each customer

17. Explore the benefits of CTEs (Common Table Expressions) in a real-world scenario

18. Write a query to calculate the percentile rank of a given value in a dataset

19. Implement a pivot table in SQL for dynamic column aggregation

20. Craft a query to identify and remove duplicates based on specific criteria

Some common #SQL interview questions along with their queries:

1. Write a SQL query to find the second highest salary from the 'emp' table. (Columns: id, salary)

SELECT MAX(salary) AS second_highest_salary

FROM emp

WHERE salary < (SELECT MAX(salary) FROM emp);

2. Write a SQL query to find the days when the temperature was higher than its previous dates.
(Columns: Days, Temp)

SELECT Days, Temp

FROM table_name t1

WHERE Temp > (SELECT Temp FROM table_name t2 WHERE t2.Days = t1.Days - 1);

SQL queries that are commonly asked during interviews:

1. Retrieve the first 10 rows from a table:

SELECT * FROM table_name LIMIT 10;

2. Find the second highest salary from an Employee table:

SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);

3. List employees who joined in the last month:

SELECT * FROM Employee WHERE JOIN_DATE >= DATEADD(MONTH, -1, GETDATE());

4. Count the number of employees in each department:

SELECT department, COUNT(*) AS num_employees

FROM Employee

GROUP BY department;

5. Retrieve employees who have the highest salary in each department:

SELECT department, MAX(salary) AS max_salary

FROM Employee

GROUP BY department;

6. Find the nth highest salary from an Employee table:

SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT n-1, 1;

7. List employees who don't have a manager:

SELECT * FROM Employee WHERE manager_id IS NULL;

8. Retrieve employees who earn more than their managers:

SELECT e.*

FROM Employee e

JOIN Employee m ON e.manager_id = m.employee_id

WHERE e.salary > m.salary;

9. Find duplicate records in a table:

SELECT column1, column2, COUNT(*)

FROM table_name

GROUP BY column1, column2

HAVING COUNT(*) > 1;

10. Calculate the total sales for each product:

SELECT product_id, SUM(quantity * price) AS total_sales

FROM Sales

GROUP BY product_id;

Recently a candidate interviewed by India's biggest Airline for Executive Data Analyst.
Questions -

1) What is SQL. What are the different joins we can used ?

2) Gave me the three tables randomly maked by interviewer and told me to apply the joins and give me
the result ?

3)Define where and having clause.? They gave me a table and conditions then we have to apply clause?
Where clause is used with Group by or not ?

4) Rate yourself in Powe BI, SQL, Microsoft Excel out of 10 , and when i answered they told me rate
yourself always high.

5)Define different types of Look up function? Difference between Lookup and index function in excel,
You have to show how these function works with an example?

6)What formulas you know in excel? Define Advance Excel and what's the use of it ?

7)Define Nestedif and Ifs function with an example?

8)Define Power BI and ETL?

9)Define your projects? What type of analysis you did in your Projects?

10)How do you do data cleansing? How to deal with missing values?

11)Define Pandas? What is Statistical Analysis? what types of Statistical Analysis you have done?

12)Define Mean, Median, Mode?

Understanding SQL Joins 🧐

• Ever heard of SQL joins but felt lost in the database labyrinth?

• Let's unravel this mystery with a relatable analogy and simple language!

Scenario:

• Imagine you're hosting a party with two guest lists – one for friends and another for colleagues.

• Now, let's use SQL joins to merge these lists and see who's coming together!

1. Inner Join:

• Think of an inner join as the ultimate networking tool at your party.

• It brings together only the guests who are on both the friends' and colleagues' lists.

• It's like inviting friends who are also colleagues or vice versa.

Syntax:-

SELECT Friends.Name, Colleagues.Name

FROM Friends

INNER JOIN Colleagues ON Friends.Email = Colleagues.Email;

Example:

If "John" is on both lists with the same email, the inner join will include him in the final guest list since
he's both a friend and a colleague.

2. Left Join:

• Now, let's say you're more inclined to prioritize your friends over colleagues.
• A left join fetches all guests from the friends' list and matches them with colleagues if they're present.

• If not, they're still invited!

Syntax:

SELECT Friends.Name, Colleagues.Name

FROM Friends

LEFT JOIN Colleagues ON Friends.Email = Colleagues.Email;

Example:

Even if "Mary" is on the friends' list but not the colleagues' list, the left join ensures she still gets an
invite, leaving the colleague slot empty.

3. Right Join:

• Conversely, imagine you're giving more weight to colleagues this time.

• A right join prioritizes colleagues, ensuring all colleagues get an invite, even if they're not on the
friends' list.

Syntax:

SELECT Friends.Name, Colleagues.Name

FROM Friends

RIGHT JOIN Colleagues ON Friends.Email = Colleagues.Email;

Example:

If "David" is a colleague but not a friend, the right join ensures he's included in the guest list alongside
his colleague companions.

4. Full Outer Join:

• Now, let's consider a scenario where you want to invite everyone from both lists, regardless of their
friendship status. • A full outer join combines all guests from both lists, ensuring no one gets left out.

Syntax:

SELECT Friends.Name, Colleagues.Name

FROM Friends

FULL OUTER JOIN Colleagues ON Friends.Email = Colleagues.Email;

Example:

Whether "Emily" is a friend, a colleague, or both, the full outer join ensures she receives an invite to
your inclusive gathering.

5. Self Join:

• Think of a self join as inviting someone to the party and asking them to bring their clone!

• It's like looking at the same guest list twice and making connections within the same table.

Syntax:

SELECT f.Name AS FriendName, c.Name AS ColleagueName

FROM Guests f

JOIN Guests c ON f.Email = c.Email;

Example:

If "Alice" and "Bob" share the same email on the guest list, a self join will pair them together, creating a
unique networking opportunity within the same table.
SQL queries that are commonly asked during interviews: part 3.O

1. Calculate the total number of orders placed each month:

SELECT EXTRACT(MONTH FROM order_date) AS month, COUNT(*) AS num_orders

FROM Orders

GROUP BY EXTRACT(MONTH FROM order_date);

2. Find the customer who has placed the highest number of orders:

SELECT customer_id, COUNT(*) AS num_orders

FROM Orders

GROUP BY customer_id

ORDER BY num_orders DESC

LIMIT 1;

3. Retrieve the top 10% of highest-paid employees:

SELECT *

FROM Employee

ORDER BY salary DESC

LIMIT (SELECT COUNT(*) * 0.1 FROM Employee);

4. List employees who have the same manager:

SELECT e1.employee_id, e1.employee_name, e1.manager_id

FROM Employee e1

JOIN Employee e2 ON e1.manager_id = e2.manager_id AND e1.employee_id <> e2.employee_id;

5. Calculate the running total of sales for each month:

SELECT order_date, SUM(amount) OVER (ORDER BY order_date) AS running_total

FROM Orders;

6. Retrieve the latest order placed by each customer:

SELECT DISTINCT ON (customer_id) *

FROM Orders

ORDER BY customer_id, order_date DESC;

7. Find customers who have never placed an order:

SELECT *

FROM Customers

WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM Orders);

8. List the products that have never been sold:

SELECT *

FROM Products

WHERE product_id NOT IN (SELECT DISTINCT product_id FROM Sales);

9. Retrieve the average time taken to ship orders for each shipping method:

SELECT shipping_method, AVG(DATEDIFF(DAY, order_date, ship_date)) AS avg_shipping_time

FROM Orders

GROUP BY shipping_method;

10. Find the total number of unique customers who made purchases in each year:
SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(DISTINCT customer_id) AS num_customers

FROM Orders

GROUP BY EXTRACT(YEAR FROM order_date);

TOP CONCEPTS FOR INTERVIEW PREPARATION!!

🚀TOP 10 SQL Concepts for Job Interview

1. Aggregate Functions (SUM/AVG)

2. Group By and Order By

3. JOINs (Inner/Left/Right)

4. Union and Union All

5. Date and Time processing

6. String processing

7. Window Functions (Partition by)

8. Subquery

9. View and Index

10. Common Table Expression (CTE)

🚀TOP 10 Statistics Concepts for Job Interview

1. Sampling

2. Experiments (A/B tests)

3. Descriptive Statistics

4. p-value

5. Probability Distributions

6. t-test

7. ANOVA

8. Correlation

9. Linear Regression

10. Logistics Regression

Why is Excel Often the Starting Point for SQL?

Here's how Excel can help you before you dive into SQL:

🔍 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒

In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to
get data from more than one table.

🧮 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬

Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count
things, which is what you often do in SQL.

🔄 𝐈𝐅 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋

Excel's IF statements let you make choices with your data. This is similar to using WHERE in SQL to pick
specific data.

📅 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭

Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier
when you switch to SQL.

📊 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋

Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL,
which helps you organize and summarize data.

🔗 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬

Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into
how SQL finds and connects information.

You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it
can't be mastered overnight!
SQL queries that are commonly asked during interviews

41. Retrieve the most recent order for each product:

SELECT product_id, MAX(order_date) AS recent_order_date

FROM Orders

GROUP BY product_id;

42. Find the total number of orders and the average order amount for each customer:

SELECT customer_id, COUNT(*) AS total_orders, AVG(amount) AS avg_order_amount

FROM Orders

GROUP BY customer_id;

43. List the products that have been sold more than 100 times:

SELECT product_id

FROM Sales

GROUP BY product_id

HAVING COUNT(*) > 100;

44. Retrieve the email addresses of customers who have not made any purchases:

SELECT email

FROM Customers

WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM Orders);

45. Calculate the total number of days each product has been in stock:
SELECT product_id, DATEDIFF(DAY, MIN(stock_date), MAX(stock_date)) AS total_days_in_stock

FROM Stock

GROUP BY product_id;

46.Find the departments with the highest and lowest average employee salaries:

Highest average

SELECT department, AVG(salary) AS avg_salary

FROM Employee

GROUP BY department

ORDER BY avg_salary DESC

LIMIT 1;

lowest average

SELECT department, AVG(salary) AS avg_salary

FROM Employee

GROUP BY department

ORDER BY avg_salary Asc

LIMIT 1;

47. List the customers who have made purchases in all months of the year:

SELECT customer_id

FROM Orders

GROUP BY customer_id

HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) = 12;

48. Calculate the difference in sales between the current year and the previous year for each product:

SELECT product_id,

SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = EXTRACT(YEAR FROM CURRENT_DATE) THEN

amount ELSE 0 END) -

SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN

amount ELSE 0 END) AS sales_difference

FROM Sales

GROUP BY product_id;

49. Retrieve the employees who have joined in the last quarter:

SELECT *

FROM Employee

WHERE JOIN_DATE >= DATEADD(QUARTER, -1, GETDATE());

50. List the products that have never been out of stock:

SELECT product_id

FROM Products

WHERE product_id NOT IN (SELECT DISTINCT product_id FROM Stock WHERE stock_quantity = 0);

SQL queries that are commonly asked during interviews:

51. Retrieve the average number of days between orders for each customer:

SELECT customer_id, AVG(DATEDIFF(day, LAG(order_date) OVER(PARTITION BY customer_id ORDER BY

order_date), order_date)) AS avg_days_between_orders

FROM Orders

GROUP BY customer_id;

52. Find the customers who have placed orders on consecutive days:

SELECT DISTINCT o1.customer_id

FROM Orders o1

JOIN Orders o2 ON o1.customer_id = o2.customer_id

WHERE DATEDIFF(day, o1.order_date, o2.order_date) = 1;

53. Calculate the total revenue generated from each product category:

SELECT p.category_id, SUM(o.amount) AS total_revenue

FROM Orders o

JOIN Products p ON o.product_id = p.product_id

GROUP BY p.category_id;

54. Retrieve the top 3 most profitable products based on total revenue:

SELECT product_id, SUM(amount) AS total_revenue

FROM Orders

GROUP BY product_id

ORDER BY total_revenue DESCLIMIT 3;

55. Find the number of employees in each salary range (e.g., 0-50000, 50001-100000, etc.):
SELECT CONCAT(FLOOR(salary/50000)50000 + 1, '-', FLOOR(salary/50000)*50000 + 50000) AS
salary_range, COUNT() AS num_employees

FROM Employee

GROUP BY FLOOR(salary/50000);

56. Retrieve the top 5 most frequent words from a text column:

SELECT word, COUNT(*) AS frequency

FROM (

SELECT regexp_split_to_table(text_column, '\s+') AS word

FROM table_name

) AS words

GROUP BY word

ORDER BY frequency DESC

LIMIT 5;

57. Calculate the percentage change in sales amount compared to the previous month for each product:

SELECT product_id,

(SUM(amount) - LAG(SUM(amount)) OVER(PARTITION BY product_id ORDER BY

EXTRACT(YEAR_MONTH FROM sale_date))) / LAG(SUM(amount)) OVER(PARTITION BY product_id ORDER
BY EXTRACT(YEAR_MONTH FROM sale_date)) * 100 AS percentage_change

FROM Sales

GROUP BY product_id;

58. List the customers who have placed orders for all products:
SELECT customer_id

FROM Orders

GROUP BY customer_id

HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM Products);

59. Retrieve the orders placed by customers who have not logged in to the system in the last 30 days:

SELECT *

FROM Orders

WHERE customer_id IN (

SELECT customer_id

FROM Customers

WHERE last_login_date <= DATEADD(day, -30, GETDATE()));

60. Find the average number of products sold per order:

SELECT AVG(num_products) AS avg_products_per_order

FROM (

SELECT order_id, COUNT(*) AS num_products

FROM OrderDetails

GROUP BY order_id

) AS order_products;

Here are some key points for SQL optimization:

Use Indexing: Properly indexing your database tables can significantly speed up query performance by
allowing the database to quickly locate the rows needed for a query.

Optimize Joins: Minimize the number of joins and use appropriate join types (e.g., INNER JOIN, LEFT
JOIN) to ensure efficient data retrieval.

Avoid SELECT * : Instead of selecting all columns using SELECT *, explicitly specify only the columns
needed for the query to reduce unnecessary data transfer and processing overhead.

Use WHERE Clause Wisely: Filter rows early in the query using WHERE clause to reduce the dataset size
before joining or aggregating data.

Avoid Subqueries: Whenever possible, rewrite subqueries as JOINs or use Common Table Expressions
(CTEs) for better performance.

Limit the Use of DISTINCT: Minimize the use of DISTINCT as it requires sorting and duplicate removal,
which can be resource-intensive for large datasets.

Optimize GROUP BY and ORDER BY: Use GROUP BY and ORDER BY clauses judiciously, and ensure that
they are using indexed columns whenever possible to avoid unnecessary sorting.

Consider Partitioning: Partition large tables to distribute data across multiple nodes, which can improve
query performance by reducing I/O operations.

Monitor Query Performance: Regularly monitor query performance using tools like query execution
plans, database profiler, and performance monitoring tools to identify and address bottlenecks.

📊 SQL Topic Wise Functions Guide

1. Aggregation Functions

COUNT: Counts the number of rows in a specified table.

SUM: Calculates the sum of values in a column.

AVG: Computes the average of values in a column.

MIN/MAX: Finds the minimum/maximum value in a column.

2. String Functions

CONCAT: Concatenates two or more strings.

UPPER/LOWER: Converts a string to uppercase/lowercase.

SUBSTRING: Extracts a substring from a string.

LENGTH: Returns the length of a string.

3. Date and Time Functions

NOW/CURRENT_TIMESTAMP: Retrieves the current date and time.

DATEADD/DATEDIFF: Adds or subtracts a specified time interval from a date.

DATEPART: Extracts a specific part (e.g., year, month) from a date.

4. Mathematical Functions

ROUND: Rounds a numeric value to a specified precision.

ABS: Returns the absolute value of a numeric expression.

POWER/SQRT: Calculates the power/square root of a number.

5. Conditional Functions

CASE: Performs conditional logic within a query.

COALESCE: Returns the first non-null expression in a list.

IFNULL/ISNULL: Handles NULL values by substituting a default value.

6. Conversion Functions
CAST/CONVERT: Converts data from one data type to another.

TO_CHAR/TO_DATE/TO_NUMBER: Converts strings to characters, dates, or

numbers.

After few years of experience you will realize that just writing the SQL queries are not sufficient but
optimizing the queries for better performance is important.

Here's some query optimization techniques :

➡ Indexing for columns commonly used in WHERE and ORDER BY.

➡ Using = operator over != operator with WHERE clause.

➡ Using numerical data type and not character data type for storing numerical data.

➡ Using EXISTS in place of IN clause.

➡ Querying specific column then using SELECT *

SQL queries that are commonly asked during interviews:

61. Retrieve the customers who have made purchases on weekdays only:

SELECT customer_id

FROM Orders

GROUP BY customer_id

HAVING COUNT(DISTINCT CASE WHEN EXTRACT(ISODOW FROM order_date) < 6 THEN

EXTRACT(ISODOW FROM order_date) END) = COUNT(DISTINCT EXTRACT(ISODOW FROM order_date));

62. Find the average time taken to ship orders for each product category:

SELECT p.category_id, AVG(DATEDIFF(day, order_date, ship_date)) AS avg_shipping_time

FROM Orders o

JOIN Products p ON o.product_id = p.product_id

GROUP BY p.category_id;

63. Retrieve the customers who have placed orders for more than 10 unique products in a single order:

SELECT customer_id, order_id

FROM (

SELECT customer_id, order_id, COUNT(DISTINCT product_id) AS unique_products

FROM OrderDetails

GROUP BY customer_id, order_id

) AS order_products

WHERE unique_products > 10;

64. Calculate the total sales for each product category in the last quarter:
SELECT p.category_id, SUM(o.amount) AS total_sales_last_quarter

FROM Orders o

JOIN Products p ON o.product_id = p.product_id

WHERE order_date >= DATEADD(QUARTER, -1, GETDATE())

GROUP BY p.category_id;

65. List the employees who have worked in multiple departments:

SELECT employee_id, employee_name

FROM (

SELECT employee_id, employee_name, COUNT(DISTINCT department_id) AS num_departments

FROM Employee_Departments

GROUP BY employee_id, employee_name

) AS multi_department_employees

WHERE num_departments > 1;

66. Retrieve the orders with the highest and lowest order amounts:

SELECT * FROM Orders WHERE amount = (SELECT MAX(amount) FROM Orders)

UNION

SELECT * FROM Orders WHERE amount = (SELECT MIN(amount) FROM Orders);

67. Find the top 3 most common pairs of products bought together:
SELECT product_id1, product_id2, COUNT(*) AS pair_count

FROM (

SELECT od1.product_id AS product_id1, od2.product_id AS product_id2

FROM OrderDetails od1

JOIN OrderDetails od2 ON od1.order_id = od2.order_id AND od1.product_id < od2.product_id

) AS product_pairs

GROUP BY product_id1, product_id2

ORDER BY pair_count DESC

LIMIT 3;

68. Calculate the percentage of total orders for each product category:

SELECT p.category_id, COUNT(o.order_id) * 100.0 / (SELECT COUNT(*) FROM Orders) AS

percentage_total_orders

FROM Orders o

JOIN Products p ON o.product_id = p.product_id

GROUP BY p.category_id;

69. Retrieve the customers who have made purchases on weekends only:

SELECT customer_id

FROM Orders

GROUP BY customer_id

HAVING COUNT(DISTINCT CASE WHEN EXTRACT(ISODOW FROM order_date) > 5 THEN

EXTRACT(ISODOW FROM order_date) END) = COUNT(DISTINCT EXTRACT(ISODOW FROM order_date)));

Technical questions that were asked :

1. Asked me to explain my (Power BI) project worked on.

2. What is Cardinality in Power BI. Cardinality types?

3. Components in Power BI

4. What is Bidirectional cross filtering in Power BI

5. What is Data Modelling ?

6. What is RDBMS ?

7. What are there window functions in SQL. Why do we use it ( explain all )

8. What is difference between Where and Having ?

9. What is difference between Stored procedure and Views ?

10. Movie dataset given ( 3- Columns - title, length, rating

create 4 column based on ranking

( Based on the ranking of the length of the movies in each rating category)

11. Question on JOIN ( Explain all types of JOINS )

table A table B

10 10

10 10

11 14

12 NULL

NULL 12

12 NULL

Find Inner join, Left , right, cross join values.

12. What is VLOOKUP, why do we use it , Syntax of VLOOKUP ( Asked 1 question on VLOOKUP.)

13. Asked question on SUMIF, SUMIFS

SQL queries that are commonly asked during interviews:

71. Retrieve the customers who have made purchases of at least three different products in each
category:

SELECT customer_id

FROM (SELECT customer_id, category_id, COUNT(DISTINCT product_id) AS num_products FROM Orders

o

JOIN Products p ON o.product_id = p.product_id

GROUP BY customer_id, category_id

) AS customer_products_per_category

GROUP BY customer_id

HAVING COUNT() = (SELECT COUNT() FROM Categories);

72.Find the top 3 most common words in a text column excluding common stop words ("and", "the",
"is", etc.):

SELECT word, COUNT(*) AS frequency

FROM (

SELECT regexp_split_to_table(LOWER(text_column), '\s+') AS word

FROM table_name

) AS words

WHERE word NOT IN ('and', 'the', 'is', 'of', 'a', 'to', 'in', 'it')

GROUP BY word

ORDER BY frequency DESC

LIMIT 3;

73. Calculate the average number of orders per month for each customer:
SELECT customer_id, AVG(num_orders) AS avg_orders_per_month

FROM (

SELECT customer_id, EXTRACT(MONTH FROM order_date) AS month, COUNT(*) AS num_orders

FROM Orders

GROUP BY customer_id, EXTRACT(MONTH FROM order_date)

) AS monthly_orders

GROUP BY customer_id;

74. Retrieve the products that have been out of stock for the longest continuous period:

SELECT product_id, MIN(stock_date) AS start_date, MAX(stock_date) AS end_date,

DATEDIFF(DAY, MIN(stock_date), MAX(stock_date)) AS days_out_of_stock

FROM (

SELECT product_id, stock_date,

ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY stock_date) -

ROW_NUMBER() OVER (PARTITION BY product_id, stock_quantity ORDER BY stock_date) AS grp

FROM Stock

) AS stock_groups

WHERE stock_quantity = 0

GROUP BY product_id, grp

ORDER BY days_out_of_stock DESC

LIMIT 1;

75. List the employees who have worked in all departments:

SELECT employee_id

FROM (

SELECT employee_id, COUNT(DISTINCT department_id) AS num_departments

FROM Employee_Departments

GROUP BY employee_id

) AS employee_department_counts

GROUP BY employee_id

HAVING COUNT() = (SELECT COUNT() FROM Departments);

76.Retrieve the products that have experienced a decrease in sales amount for each consecutive month
for the last three months:

SELECT product_id

FROM (

SELECT product_id,

ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS rn,

SUM(amount) AS total_amount

FROM Sales

GROUP BY product_id, EXTRACT(YEAR_MONTH FROM sale_date)

) AS sales_per_month

WHERE rn <= 3

GROUP BY product_id

HAVING COUNT(*) = 3 AND total_amount = MAX(total_amount);

77. Find the average length of time between orders for each customer:
SELECT customer_id, AVG(DATEDIFF(day, LAG(order_date) OVER(PARTITION BY customer_id ORDER BY
order_date), order_date)) AS avg_time_between_orders

FROM Orders

GROUP BY customer_id;

SQL queries that are commonly asked during interviews:

71. Retrieve the customers who have made purchases of at least three different products in each
category:

SELECT customer_id

FROM (SELECT customer_id, category_id, COUNT(DISTINCT product_id) AS num_products FROM Orders

o

JOIN Products p ON o.product_id = p.product_id

GROUP BY customer_id, category_id

) AS customer_products_per_category

GROUP BY customer_id

HAVING COUNT() = (SELECT COUNT() FROM Categories);

72.Find the top 3 most common words in a text column excluding common stop words ("and", "the",
"is", etc.):

SELECT word, COUNT(*) AS frequency

FROM (

SELECT regexp_split_to_table(LOWER(text_column), '\s+') AS word

FROM table_name

) AS words

WHERE word NOT IN ('and', 'the', 'is', 'of', 'a', 'to', 'in', 'it')

GROUP BY word
ORDER BY frequency DESC

LIMIT 3;

73. Calculate the average number of orders per month for each customer:

SELECT customer_id, AVG(num_orders) AS avg_orders_per_month

FROM (

SELECT customer_id, EXTRACT(MONTH FROM order_date) AS month, COUNT(*) AS num_orders

FROM Orders

GROUP BY customer_id, EXTRACT(MONTH FROM order_date)

) AS monthly_orders

GROUP BY customer_id;

74. Retrieve the products that have been out of stock for the longest continuous period:

SELECT product_id, MIN(stock_date) AS start_date, MAX(stock_date) AS end_date,

DATEDIFF(DAY, MIN(stock_date), MAX(stock_date)) AS days_out_of_stock

FROM (

SELECT product_id, stock_date,

ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY stock_date) -

ROW_NUMBER() OVER (PARTITION BY product_id, stock_quantity ORDER BY stock_date) AS grp

FROM Stock

) AS stock_groups

WHERE stock_quantity = 0

GROUP BY product_id, grp

ORDER BY days_out_of_stock DESC

LIMIT 1;
75. List the employees who have worked in all departments:

SELECT employee_id

FROM (

SELECT employee_id, COUNT(DISTINCT department_id) AS num_departments

FROM Employee_Departments

GROUP BY employee_id

) AS employee_department_counts

GROUP BY employee_id

HAVING COUNT() = (SELECT COUNT() FROM Departments);

76.Retrieve the products that have experienced a decrease in sales amount for each consecutive month
for the last three months:

SELECT product_id

FROM (

SELECT product_id,

ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS rn,

SUM(amount) AS total_amount

FROM Sales

GROUP BY product_id, EXTRACT(YEAR_MONTH FROM sale_date)

) AS sales_per_month

WHERE rn <= 3

GROUP BY product_id

HAVING COUNT(*) = 3 AND total_amount = MAX(total_amount);

77. Find the average length of time between orders for each customer:
SELECT customer_id, AVG(DATEDIFF(day, LAG(order_date) OVER(PARTITION BY customer_id ORDER BY
order_date), order_date)) AS avg_time_between_orders

FROM Orders

GROUP BY customer_id;

SQL queries that are commonly asked during interviews:

81. Retrieve the customers who have made purchases of all products within a specific category:

SELECT customer_id

FROM (

SELECT o.customer_id, p.category_id, COUNT(DISTINCT o.product_id) AS num_products

FROM Orders o

JOIN Products p ON o.product_id = p.product_id

GROUP BY o.customer_id, p.category_id

) AS customer_product_count

GROUP BY customer_id HAVING COUNT(*) = (SELECT COUNT(DISTINCT product_id) FROM Products);

82. Find the top 3 most popular product categories based on the total number of orders:

SELECT p.category_id, COUNT(o.order_id) AS num_orders FROM Orders o

JOIN Products p ON o.product_id = p.product_id

GROUP BY p.category_id ORDER BY num_orders DESC LIMIT 3;

83. Retrieve the orders with the highest and lowest total order amounts within each product category:

WITH ranked_orders AS (SELECT *,

RANK() OVER (PARTITION BY category_id ORDER BY order_amount DESC) AS rank_highest,

RANK() OVER (PARTITION BY category_id ORDER BY order_amount ASC) AS rank_lowest

FROM (
 SELECT o.order_id, o.product_id, p.category_id, SUM(o.amount) AS order_amount

FROM Orders o

JOIN Products p ON o.product_id = p.product_id

GROUP BY o.order_id, o.product_id, p.category_id

) AS order_amounts

)SELECT * FROM ranked_orders WHERE rank_highest = 1 OR rank_lowest = 1;

84. List the customers who have made purchases in all product categories:

SELECT customer_id FROM Orders GROUP BY customer_id

HAVING COUNT(DISTINCT category_id) = (SELECT COUNT(DISTINCT category_id) FROM Products);

85. Calculate the percentage of total orders for each customer:

SELECT customer_id, (COUNT(order_id) * 100.0 / (SELECT COUNT(*) FROM Orders)) AS

percentage_of_total_orders

FROM Orders GROUP BY customer_id;

86. Find the employees who have not been assigned to any department:

SELECT employee_id, employee_name FROM Employee

WHERE employee_id NOT IN (SELECT DISTINCT employee_id FROM Employee_Departments);

87. Retrieve the orders with the highest and lowest total order quantities within each product category:

WITH ranked_orders AS (SELECT *,

RANK() OVER (PARTITION BY category_id ORDER BY total_quantity DESC) AS rank_highest,

RANK() OVER (PARTITION BY category_id ORDER BY total_quantity ASC) AS rank_lowest

FROM (SELECT o.order_id, p.category_id, SUM(od.quantity) AS total_quantity

FROM Orders o JOIN OrderDetails od ON o.order_id = od.order_id

 JOIN Products p ON od.product_id = p.product_id

GROUP BY o.order_id, p.category_id

) AS order_quantities

)SELECT * FROM ranked_orders WHERE rank_highest = 1 OR rank_lowest = 1;

88. List the customers who have made purchases on both weekdays and weekends:

SELECT customer_id FROM Orders GROUP BY customer_id

HAVING COUNT(DISTINCT CASE WHEN EXTRACT(ISODOW FROM order_date) <= 5 THEN 'weekday' ELSE
'weekend' END) = 2;

SQL queries that are commonly asked during interviews:

91. Retrieve the top 5 customers with the highest average order amounts:

SELECT customer_id, AVG(amount) AS avg_order_amount

FROM Orders

GROUP BY customer_id

ORDER BY avg_order_amount DESC

LIMIT 5;

92. Find the top 3 most frequent combinations of products bought together (pairs):

SELECT product1, product2, COUNT(*) AS frequency

FROM (

SELECT od1.product_id AS product1, od2.product_id AS product2

FROM OrderDetails od1

JOIN OrderDetails od2 ON od1.order_id = od2.order_id AND od1.product_id < od2.product_id

) AS product_pairs
GROUP BY product1, product2

ORDER BY frequency DESC

LIMIT 3;

93. List the customers who have made purchases in every month of a given year:

SELECT customer_id

FROM Orders

WHERE EXTRACT(YEAR FROM order_date) = 2024

GROUP BY customer_id

HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) = 12;

94. Retrieve the top 5 most profitable months based on total sales amount:

SELECT EXTRACT(MONTH FROM order_date) AS month, SUM(amount) AS total_sales_amount

FROM Orders

GROUP BY EXTRACT(MONTH FROM order_date)

ORDER BY total_sales_amount DESC

LIMIT 5;

95.Find the customers who have placed orders on the same day they registered as customers:

SELECT c.customer_id, c.customer_name

FROM Customers c

JOIN Orders o ON c.customer_id = o.customer_id

WHERE DATE_TRUNC('day', c.registration_date) = DATE_TRUNC('day', o.order_date);

96. Retrieve the orders with the highest and lowest total order amounts for each month:
WITH monthly_orders AS (

SELECT order_id, EXTRACT(MONTH FROM order_date) AS month, SUM(amount) AS total_amount

FROM Orders

GROUP BY order_id, EXTRACT(MONTH FROM order_date)

SELECT *

FROM (

SELECT *, RANK() OVER (PARTITION BY month ORDER BY total_amount DESC) AS rank_highest

FROM monthly_orders

) AS highest_orders

WHERE rank_highest = 1

UNION

SELECT *

FROM (

SELECT *, RANK() OVER (PARTITION BY month ORDER BY total_amount ASC) AS rank_lowest

FROM monthly_orders

) AS lowest_orders

WHERE rank_lowest = 1;

97. List the products that have been sold at least once every month for the past year:

SELECT product_id

FROM (

SELECT product_id, EXTRACT(YEAR_MONTH FROM sale_date) AS sale_month

FROM Sales

GROUP BY product_id, EXTRACT(YEAR_MONTH FROM sale_date)

 HAVING COUNT(DISTINCT EXTRACT(MONTH FROM sale_date)) = 12

) AS monthly_sales

GROUP BY product_id

HAVING COUNT(*) = 12;

98. Retrieve the customers who have made purchases of all products with a specific attribute (e.g., color,
size):

SELECT customer_id

FROM (

SELECT customer_id, attribute

FROM Orders o

JOIN Products p ON o.product_id = p.product_id

WHERE p.attribute = 'color' -- Specify the attribute here

GROUP BY customer_id, attribute

) AS customer_product_count

GROUP BY customer_id

HAVING COUNT() = (SELECT COUNT() FROM Products WHERE attribute = 'color'); -- Specify
the attribute here

#walmart interview questions for Data analyst

SQL:

1. What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN in SQL? When would you use
each type of join?

2. Explain the concept of normalization in database design. Why is it important, and what are the
different normal forms?
3. Discuss the role of indexes in SQL databases. How do indexes improve query performance, and what
are some best practices for index usage?

#Top #SQL Interview Questions frequently asked in Data Analyst Interviews!🤩

❇ Basic Level

1. Explain the difference between drop and truncate.

2. What are Constraints in SQL?

3. What is a primary key in SQL?

4. Explain the difference between CHAR and VARCHAR data types in SQL.

5. How does the WHERE clause work in SQL?

6. What is a foreign key in SQL?

7. How do you use the GROUP BY statement in SQL?

8. What are constraints and types of Constraints?

9. Difference between Group By and Where?

✅Intermediate Level

1. Explain the use of the HAVING clause in SQL.

2. Describe the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

3. Explain the use of the HAVING clause in SQL.

4. What is SQL Query of Execution ?.

5. What are the different types of SQL commands?

6. What is the difference between CHAR and VARCHAR data types?

7. What is an Index ?

8. What is the Difference between UNION & UNION ALL.

✅ Advanced Level

1. Which is faster between CTE and Subquery?

2. What are Views?

3. What are index and their types?

4. What is Normalization ?

5. What is the difference between DELETE and TRUNCATE

commands?

6. What is the difference between a view and a table?

7. Explain Window Functions (All)

8. What is the Difference between Rank & Dense Rank?

9. What is difference between view and stored procedure ?

10. What is Triggers ?

(#CompanyName : Confidential) Important interview questions for Data analyst

Excel:

1. How would you use Excel to clean and preprocess a dataset before analysis? Can you give examples of
common data cleaning techniques and Excel functions you might use?

2. Explain the difference between absolute and relative cell references in Excel formulas. When would
you use each type, and provide examples?

3. Walk me through the process of creating a pivot table in Excel. What are the key steps, and how would
you customize the pivot table to analyze specific aspects of the data?
4. Can you demonstrate how to use Excel's "VLOOKUP" function to perform a lookup operation between
two datasets? What are some alternatives to VLOOKUP, and when would you use them?

5. How can you use Excel's "What-If Analysis" tools such as Scenario Manager or Goal Seek to perform
predictive modeling or sensitivity analysis?

SQL:

1. Write a SQL query to retrieve all orders from a specific customer from a table named "Orders" and join
it with the "Customers" table to include customer details.

2. Explain the difference between the "WHERE" and "HAVING" clauses in SQL. When would you use each
clause, and provide examples?

3. How do you handle NULL values in SQL queries? Can you demonstrate how to filter, replace, or handle
NULL values in a SQL query?

4. Describe the process of creating an index in SQL. When would you create an index, and what are some
factors to consider when designing indexes for a database?

5. What is a subquery in SQL, and how would you use it to perform complex queries or data
transformations? Provide an example of a scenario where a subquery would be useful.

Common hashtag#SQL Mistakes & Quick Fixes: 2.O

⿦ Table Doesn’t Exist:

What it means: The table you're referencing isn't present in the database.

Fix: Double-check your table names for accuracy.

⿧ Connection Issues:

What it means: There's difficulty establishing communication with the database server.

Fix: Verify your internet connection. If it's stable, seek assistance from your technical support team.
⿨ Too Many Rows:

What it means: Your query may return a larger dataset than anticipated.

Fix: Refine your query by adding filters or applying limits to narrow down the results.

⿩ Join Conditions Incorrect:

What it means: Your join conditions are not properly specified, leading to unexpected results.

Fix: Review your join conditions to ensure they accurately connect the related tables.

10 Missing WHERE Clause:

What it means: Your query is missing a WHERE clause, resulting in unintended data retrieval.

Fix: Include a WHERE clause to filter the results based on specific conditions.

⿡⿡ Incorrect Order of Operations:

What it means: The order in which operations are executed in your query is not as intended.

Fix: Use parentheses to explicitly specify the order of operations in complex queries.

⿡⿢ Case Sensitivity Issues:

What it means: SQL is case-sensitive, and mismatches in case can lead to errors.

Fix: Ensure consistency in casing for table names, column names, and keywords throughout your queries.

⿡⿣ Failure to Commit Transactions:

What it means: Changes made within a transaction are not committed properly, leading to data
inconsistency.

Fix: Explicitly commit transactions after making changes to ensure data integrity.

⿡⿤ Indexing Problems:
What it means: Missing or improper indexing can result in slow query performance.

Fix: Analyze query execution plans and consider adding appropriate indexes to optimize performance.

⿡⿥ Incorrect Aggregate Functions:

What it means: Misuse of aggregate functions (e.g., SUM, AVG) can lead to incorrect results.

Fix: Ensure that aggregate functions are applied to the correct columns and properly grouped data.

Common hashtag#SQL Mistakes & Quick Fixes:

⿡ Syntax Error:

What it means: There's a small typo or mistake in your command.

Fix: Review your code carefully. Look for missed commas, brackets, or spelling errors.

⿢ NULL Value Errors:

What it means: You're attempting to insert a blank value where it's not allowed.

Fix: Ensure all required fields are filled with appropriate information.

⿣ Duplicate Entry:

What it means: You're inserting data that already exists in the database.

Fix: Verify the uniqueness of your data before insertion.

⿤ Permission Issues:

What it means: You lack the necessary permissions for the operation you're attempting.

Fix: Confirm you have the appropriate permissions, or seek assistance from your IT team.
⿥ Wrong Data Type:

What it means: You're trying to input data of an incorrect type into a field.

Fix: Check the expected data type for the field and ensure compatibility with your input.

I have listed #SQL interview questions below .Make use of it.

Explain order of execution of SQL.

What is difference between where and having?

What is the use of group by?

Explain all types of joins in SQL?

What are triggers in SQL?

What is stored procedure in SQL

Explain all types of window functions?

(Mainly rank, row_num, dense_rank, lead & lag)

What is difference between Delete and Truncate?

What is difference between DML, DDL and DCL?

What are aggregate function and when do we use them?

explain with few example.

Which is faster between CTE and Subquery?

What are constraints and types of Constraints?

Types of Keys?

Different types of Operators ?

Difference between Group By and Where?

What are Views?

What are different types of constraints?

What is difference between varchar and nvarchar?

Similar for char and nchar?

What are index and their types?

What is an index? Explain its different types.

List the different types of relationships in SQL.

Differentiate between UNION and UNION ALL.

How many types of clauses in SQL ?

What is the difference between UNION and UNION ALL in SQL?

What are the various types of relationships in SQL?

Difference between Primary Key and Secondary Key?

What is the difference between where and having?

Find the second highest salary of an employee?

Hello DataFam!

🔶 Here are the most asked SQL Interview Questions, which are very basic though but one often forgets
to pay attention to basics, but basics are the key to land your dream job.

## Question 1.

Difference between Truncate, Drop, Delete.

## Question 2.

Difference between RANK, DENSE_RANK and ROW_NUMBER window function.

## Question 3.
Difference between Unique, primary keys, foreign keys.

## Question 4.

Difference between “WHERE ” and “HAVING”clause.

## Question 5.

Difference between PARTITION BY and GROUP BY.

## Question 6.

Imagine there is a FULL_NAME column in a table which has values like “Elon Musk“, “Bill Gates“, “Jeff
Bezos“ etc. So each full name has a first name, a space and a last name. Which functions would you use
to fetch only the first name from this FULL_NAME column? Give example.

## Question 7.

How can you convert a text into date format? Consider the given text as “31–01–2021“.

## Question 8.

Why do we use CASE Statement in SQL? Give example

## Question 9.

What is the difference between LEFT, RIGHT, FULL outer join and INNER join?

## Question 10.

Can we use aggregate function as window function? If yes then how do we do it?

#SQL Interview Questions frequently asked in Data Analyst Interviews!

Basic Level 🚀

1. Explain the difference between drop and truncate.

2. What are Constraints in SQL?

3. What is a primary key in SQL?

4. Explain the difference between CHAR and VARCHAR data types in SQL.

5. How does the WHERE clause work in SQL?

6. What is a foreign key in SQL?

7. How do you use the GROUP BY statement in SQL?

8. What are constraints and types of Constraints?

9. Difference between Group By and Where?

Intermediate Level 🚀

1. Explain the use of the HAVING clause in SQL.

2. Describe the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

3. Explain the use of the HAVING clause in SQL.

4. What is SQL Query of Execution ?.

5. What are the different types of SQL commands?

6. What is the difference between CHAR and VARCHAR data types?

7. What is an Index?

8. What is the Difference between UNION & UNION ALL.

Advanced Level 🚀
1. Which is faster between CTE and Subquery?

2. What are Views?

3. What are index and their types?

4. What is Normalization?

5. What is the difference between DELETE and TRUNCATE commands?

6. What is the difference between a view and a table?

7. Explain Window Functions (All)

8. What is the Difference between Rank & Dense Rank?

9. What is difference between view and stored procedure ?

10. What is Triggers ?

Common #SQL Mistakes & Quick Fixes: 2.O

⿡⿦ Unnecessary Cartesian Joins:

What it means: You've unintentionally created a Cartesian join, resulting in a huge, unintended result set.

Fix: Review your join conditions to ensure they properly connect related tables, preventing Cartesian
products.
⿡⿧ Using Reserved Keywords Incorrectly:

What it means: Attempting to use SQL reserved keywords as identifiers (e.g., table or column names).

Fix: Enclose reserved keywords in backticks or double quotes to use them as identifiers, or avoid using
them altogether.

⿡⿨ Inefficient Subqueries:

What it means: Subqueries that are poorly optimized and lead to slow query performance.

Fix: Rewrite subqueries to optimize their execution or consider alternative approaches like JOINs or
derived tables.

⿡⿩ Overlooking Foreign Key Constraints:

What it means: Forgetting to define foreign key constraints can lead to data integrity issues.

Fix: Ensure that appropriate foreign key constraints are defined to maintain referential integrity between
related tables.

⿢⿠ Failure to Normalize Data:

What it means: Storing redundant or duplicate data, leading to data inconsistency and inefficiency.

Fix: Normalize your database schema by breaking down tables into smaller, related tables to reduce
redundancy and improve data integrity.

⿢⿡ Not Using Transactions:

What it means: Making changes to the database without wrapping them in transactions, risking data
inconsistencies.

Fix: Utilize transactions to ensure that a series of database operations either all succeed or all fail,
maintaining data consistency.

⿢⿢ Ignoring Database Security Best Practices:

What it means: Failing to implement proper security measures, leaving the database vulnerable to
attacks.

Fix: Follow best practices such as parameterized queries, input validation, and role-based access control
to enhance database security.

This SQL question is from Amazon's interview for the Data Scientist role. Give it a try!

𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧:

Given a table of transactions and products, write a function to get the month_over_month change in
revenue for the year 2019. Make sure to round month_over_month to 2 decimal places.

Took me around 10- 15 minutes to frame my thoughts, type, and submit. Not to forget the errors, haha.

𝐌𝐲 𝐭𝐡𝐨𝐮𝐠𝐡𝐭 𝐩𝐫𝐨𝐜𝐞𝐬𝐬:

1. Created a 𝐂𝐓𝐄 to calculate total sales for each month in 2019.

2. Calculated month-over-month sales growth using the 𝐥𝐚𝐠() function to retrieve the previous month's
sales.

Important Interview Questions and Answers for Data Analyst (SQL )

1. Explain the ACID properties in the context of database transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability.

Atomicity: Ensures that all operations in a transaction are completed successfully, or none of them are.
 Consistency: Guarantees that the database remains in a consistent state before and after the
transaction.

Isolation: Ensures that concurrent transactions do not interfere with each other and are executed
independently.

Durability: Ensures that once a transaction is committed, its changes are permanently saved and cannot
be lost.

2. What is normalization and denormalization? Why are they important in databases?

Normalization: It is the process of organizing data in a database efficiently by reducing redundancy and
dependency. It involves dividing large tables into smaller ones and defining relationships between them
to minimize redundancy and improve data integrity.

Denormalization: It is the process of adding redundant data to a normalized database to improve

performance by reducing the need for joins and speeding up query execution. While it may sacrifice
some normalization benefits, it can significantly enhance query performance.

3. How do you handle NULL values in SQL?

Use the IS NULL or IS NOT NULL operators to check for NULL values in columns.

Use the COALESCE() function to replace NULL values with a specified default value.

Use the IFNULL() function in MySQL or the ISNULL() function in SQL Server to replace NULL values with a
specified default value.

Handle NULL values appropriately in WHERE clauses, JOIN conditions, and aggregate functions to ensure
accurate query results.

4. Explain the difference between UNION and UNION ALL operators.

UNION: Combines the result sets of two or more SELECT statements into a single result set and removes
duplicate rows.

UNION ALL: Also combines the result sets of two or more SELECT statements into a single result set but
retains all rows, including duplicates.
SQL best practices:

✔ Use EXISTS in place of IN wherever possible

✔ Use table aliases with columns when you are joining multiple tables

✔ Use GROUP BY instead of DISTINCT.

✔ Add useful comments wherever you write complex logic and avoid too many comments.

✔ Use joins instead of subqueries when possible for better performance.

✔ Use WHERE instead of HAVING to define filters on non-aggregate fields

✔ Avoid wildcards at beginning of predicates (something like '%abc' will cause full table scan to get the
results)

✔ Considering cardinality within GROUP BY can make it faster (try to consider unique column first in
group by list)

✔ Write SQL keywords in capital letters.

✔ Never use select *, always mention list of columns in select clause.

✔ Create CTEs instead of multiple sub queries , it will make your query easy to read.

✔ Join tables using JOIN keywords instead of writing join condition in where clause for better readability.

✔ Never use order by in sub queries , It will unnecessary increase runtime.

✔ If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better
performance

✔ Always start WHERE clause with 1 = 1.This has the advantage of easily commenting out conditions
during debugging a query.

✔ Taking care of NULL values before using equality or comparisons operators. Applying window
functions. Filtering the query before joining and having clause.

✔ Make sure the JOIN conditions among two table Join are either keys or Indexed attribute.

Constraints in SQL
In SQL Server, constraints are rules defined on a table column that enforce the integrity and accuracy of
the data stored in the table. Here are some common constraints used in SQL Server:

1. PRIMARY KEY Constraint:

Ensures that a column (or a combination of columns) contains unique values, and no NULL values are
allowed. A table can have only one primary key.

Syntax

CREATE TABLE TableName (

Column1 INT PRIMARY KEY,

Column2 VARCHAR(50)

);

2. FOREIGN KEY Constraint:

Enforces referential integrity between two tables. The foreign key in one table refers to the primary key
in another table.

Syntax

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),

OrderDate DATE

);

3. UNIQUE Constraint:
 Ensures that all values in a column (or a combination of columns) are unique, but it allows NULL values.

Syntax

CREATE TABLE TableName (

Column1 INT UNIQUE,

Column2 VARCHAR(50)

);

4. CHECK Constraint:

Restricts the range of values that a column can have.

Syntax

CREATE TABLE TableName (

Column1 INT CHECK (Column1 > 0),

Column2 VARCHAR(50)

);

5. DEFAULT Constraint:

Provides a default value for a column if no value is specified during the INSERT operation.

Syntax

CREATE TABLE TableName (

Column1 INT DEFAULT 0,

Column2 VARCHAR(50) )

6. NOT NULL Constraint:

 Ensures that a column does not allow NULL values.

Syntax

CREATE TABLE TableName (

Column1 INT NOT NULL,

Column2 VARCHAR(50)

);

These constraints help maintain the integrity and reliability of the data stored in SQL Server tables by
specifying rules and conditions that must be satisfied. They play a crucial role in database design and
data consistency

Complete #SQL (Topic wise) and Python Interview Questions from MNC for entry level position :-

1. Basic SQL Syntax:

SQL keywords

Data types

Operators

SQL statements (SELECT, INSERT, UPDATE, DELETE)

2. Data Definition Language (DDL):

CREATE TABLE

ALTER TABLE

DROP TABLE

Truncate table

3. Data Manipulation Language (DML):

SELECT statement (SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, JOINs)

INSERT statement

UPDATE statement

DELETE statement

4. Aggregate Functions:

SUM, AVG, COUNT, MIN, MAX

GROUP BY clause

HAVING clause

5. Data Constraints:

Primary Key

Foreign Key

Unique

NOT NULL

CHECK

6. Joins:

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

Self Join

Cross Join
7. Subqueries:

Types of subqueries (scalar, column, row, table)

Nested subqueries

Correlated subqueries

8. Advanced SQL Functions:

String functions (CONCAT, LENGTH, SUBSTRING, REPLACE, UPPER, LOWER)

Date and time functions (DATE, TIME, TIMESTAMP, DATEPART, DATEADD)

Numeric functions (ROUND, CEILING, FLOOR, ABS, MOD)

Conditional functions (CASE, COALESCE, NULLIF)

9. Views:

Creating views

Modifying views

Dropping views

10. Indexes:

Creating indexes

Using indexes for query optimization

Basic Concepts for Data Analyst (SQL)

 Subqueries in SQL

In SQL Server, a subquery is a query nested inside another query, and it can be used to retrieve data that
will be used by the main query. Subqueries can appear in various parts of a SQL statement, such as the
SELECT, FROM, WHERE, and HAVING clauses. Here are examples of subqueries in different contexts:

1. Subqueries in SELECT Clause:

Retrieve a single value to be used in the main query's select list.

Syntax:

SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value

FROM table1;

2. Subqueries in FROM Clause:

Create a derived table within the main query.

Syntax:

SELECT column1, column2

FROM (

SELECT column1, column2 FROM table1

) AS subquery;

3. Subqueries in WHERE Clause:

 Filter the result set based on the result of a subquery.

Syntax:

SELECT column1, column2

FROM table1

WHERE column3 = (SELECT MAX(column3) FROM table1);

4. Subqueries in FROM Clause with JOIN:

Use a subquery to generate a result set that is then joined with another table.

Syntax:

SELECT column1, column2

FROM table1

INNER JOIN (

SELECT id, MAX(value) AS max_value FROM table2 GROUP BY id

) AS subquery ON table1.id = subquery.id;

5. Correlated Subqueries:

Reference columns from the outer query within the subquery.

Syntax:

SELECT column1

FROM table1 outer

WHERE column2 > (SELECT AVG(column2) FROM table1 WHERE category = outer.category);
6. Subqueries with EXISTS:

Check for the existence of rows that meet certain conditions.

Syntax:

SELECT column1

FROM table1

WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

7. Subqueries with IN:

Check if a value is present in a set of values returned by a subquery.

Syntax:

SELECT column1

FROM table1

WHERE column2 IN (SELECT distinct_value FROM table2);

𝐒𝐨𝐦𝐞 𝐈𝐦𝐩𝐨𝐫𝐭𝐚𝐧𝐭 𝐭𝐨𝐩𝐢𝐜𝐬 𝐟𝐨𝐫 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐭𝐢𝐜𝐬 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰s:

𝑺𝑸𝑳:

1. Basic

• SELECT statements with WHERE, ORDER BY, GROUP BY, HAVING

• Basic JOINS (INNER, LEFT, RIGHT, FULL)

• Creating and using simple databases and tables

• Understanding database normalization concepts

• Using aliases for table and column names

2. Intermediate

• Aggregate functions (COUNT, SUM, AVG, MAX, MIN)

• Subqueries and nested queries

• Common Table Expressions (WITH clause)

• CASE statements for conditional logic in queries

• Working with date and time functions

3. Advanced

• Advanced JOIN techniques (self-join, non-equi join)

• Window functions (OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, lead, lag)

• Optimization techniques for query performance

• Handling large datasets with partitioning and views

• Using stored procedures and user-defined functions for complex tasks.

String Functions:
It is one of the built in function.Built in function means accepts input performs calculation and returns
value .

1) UPPER(string/colname)

-converts string to uppercase

SELECT UPPER('name') => NAME

2) LOWER(string/colname)

-converts string to lowercase

SELECT LOWER('NAME') => name

3) LEN() :- returns string length i.e. no of chars

LEN(string)

SELECT LEN('NAME') => 4

4) LEFT() :- used to extract characters from left side

LEFT(string,len)

SELECT LEFT('hello world',5) => hello

5) RIGHT(string,len)
 -used to extract characters from right side

SELECT RIGHT('hello world',7) => world

6) REPLICATE() :-

=> used to repeat string for given no of times

REPLICATE(str,no of times)

SELECT REPLICATE('+',5) => +++++

7) REPLACE() :-

=> used to replace one string with another string.

REPLACE(str1,str2,str3)

SELECT REPLACE('hello','ell','abc') => habco

8) TRANSLATE() :-

=> used to translate one char to another char.

TRANSLATE(str1,str2,str3)

SELECT TRANSLATE('hello','elo','abc') => habbc

9) SUBSTRING() :-

=> used to extract part of the string starting from specific position.

SUBSTRING(string,start,length)

SELECT SUBSTRING('Maggienoodles',7,4) => nood

10) CHARINDEX() :-

=> returns position of char in a string

CHARINDEX(char,string,[start])

SELECT CHARINDEX('k','Arunkumar') => 5

"A Candidate failed in Amazon Data Analytics interview - these were the questions he couldn't
answer"

Here are 7 of the questions I was asked (and failed to answer some as a fresher):

1. What SQL statements would you use to get all unique values from a column?

Answer: SELECT DISTINCT(column_name) FROM table_name;

2. How would you select the top 5 items of a list in SQL?

Answer: SELECT column_name_1, column_name_2 FROM table_name ORDER BY column_name_1 DESC
LIMIT 5;

3. Can you explain the difference between a left join and an inner join?

Answer: A left join returns all the values from the left table and matching values from the right table,
while an inner join only returns matching values from both tables.

4. How can you find the second-highest salary from an employee table in SQL?

Answer: SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);

5. Explain the difference between the COUNT() and SUM() functions in SQL?

Answer: COUNT() returns the number of rows in a table, while SUM() returns the sum of values in a
specific column.

6. How can you find duplicate rows in a table?

Answer: SELECT column_name(s) FROM table_name GROUP BY column_name(s) HAVING COUNT(*) > 1;

7. How would you order a table by multiple columns in descending order in SQL?

Answer: SELECT column_name_1, column_name_2 FROM table_name ORDER BY column_name_1 DESC,

column_name_2 DESC;

Amazon Business Analyst Interview Question for Entry-Level Role;-

A. SQL:

1. You have two tables: Product and Supplier.

- Product Table Columns: Product_id, Product_Name, Supplier_id, Price

- Supplier Table Columns: Supplier_id, Supplier_Name, Country

Write an SQL query to find the name of the product with the highest price in each country.

2. You have two tables: Customer and Transaction.

- Customer Table Columns: Customer_id, Customer_Name, Registration_Date

- Transaction Table Columns: Transaction_id, Customer_id, Transaction_Date, Amount

Write an SQL query to calculate the total transaction amount for each customer for the current year. The
output should contain Customer_Name and the total amount.

Here are very commonly asked 5 advanced SQL interview questions.

1- Top N products by sales , Top N products within each category, Ton N employees by salaries etc.

2- Year over year growth, YOY growth for each category , Products with higher sales than previous month
etc.

3- Running sales over months , rolling N months sales , within each category etc.

4- Pivot rows to columns , eg : year wise sales for each category in separate columns etc
5- Number of records after different kinds of joins.

These 5 themes are very common and interviewers can ask variations of it and some
follow up questions.

30 Days SQL Interview Questions series

Day-1

1. *What is SQL?*

- SQL stands for Structured Query Language. It is a standard language used for managing and
manipulating relational databases.

2. *What are the different types of SQL commands?*

- SQL commands are broadly categorized into four types:

- Data Definition Language (DDL)

- Data Manipulation Language (DML)

- Data Control Language (DCL)

- Transaction Control Language (TCL)

3. *What is the difference between DELETE and TRUNCATE commands?*

- DELETE is a DML command used to remove specific rows from a table based on a condition, while
TRUNCATE is a DDL command used to remove all rows from a table without logging individual row
deletions.
4. *What is a primary key?*

- A primary key is a unique identifier for each row in a table. It ensures that each row in a table is
uniquely identifiable and cannot have a NULL value.

5. *What is a foreign key?*

- A foreign key is a column or a combination of columns that establishes a link between data in two
tables. It enforces referential integrity between the two related tables.

6. *What is the difference between WHERE and HAVING clauses?*

- WHERE clause is used to filter records before they are grouped and sorted, while HAVING clause is used
to filter records after they have been grouped.

7. *What is normalization in SQL?*

- Normalization is the process of organizing data in a database to minimize redundancy and dependency.
It involves dividing large tables into smaller tables and defining relationships between them.

8. *What is an index?*

- An index is a database object used to improve the speed of data retrieval operations on a table. It
allows faster retrieval of data by creating an ordered list of key values.

9. *What is a join in SQL?*

- A join is used to combine rows from two or more tables based on a related column between them.
There are different types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

10. *What is the difference between INNER JOIN and OUTER JOIN?*

- INNER JOIN returns only the rows that have matching values in both tables, while OUTER JOIN returns
all rows from both tables and fills in NULL values for columns that do not have a match.
Basic Concepts for Data Analyst (SQL)

Index in SQL

In SQL, an index is a database object that improves the speed of data retrieval operations on a database
table. Indexes can be created on one or more columns of a table, and they work similar to the index in a
book—making it faster to find specific information. Here are some key points about indexes in SQL:

1. Types of Indexes:

Clustered Index: Determines the physical order of data in a table. There can only be one clustered index
per table.

Non-clustered Index: Creates a separate structure for the index, keeping a pointer to the actual data
rows.

2. Creating an Index:

The CREATE INDEX statement is used to create an index. Here's a simple example:

Syntax:

CREATE INDEX idx_column1 ON table_name (column1);

3. Using Indexes:

Indexes are automatically used by the database engine to speed up data retrieval operations, especially
in SELECT statements and WHERE clauses.

However, having too many indexes on a table can impact the performance of data modification
operations (such as INSERT, UPDATE, DELETE), as the indexes need to be maintained.

4. Clustered vs. Non-clustered Index:

 A table can have only one clustered index because the data rows are physically ordered based on the
clustered index.

Non-clustered indexes provide a separate structure for the index, with pointers to the actual data rows.

5. Composite Index:

An index that involves more than one column. It can improve query performance for specific
combinations of columns.

Syntax:

CREATE INDEX idx_columns12 ON table_name (column1, column2);

6. Unique Index:

Ensures that the values in the indexed columns are unique. It is often used to enforce the uniqueness of
a column or combination of columns.

Syntax:

CREATE UNIQUE INDEX idx_unique_column ON table_name (column);

7. Dropping an Index:

The DROP INDEX statement is used to remove an existing index.

Syntax:

DROP INDEX idx_column1 ON table_name;

8. Indexing Best Practices:

 Indexing involves trade-offs, and it's essential to consider the specific needs of your queries.

Regularly analyze and optimize indexes based on the changing workload and data distribution.

Indexes play a crucial role in database performance optimization, helping to speed up query execution.
However, they should be used judiciously, and their maintenance should be considered to ensure
optimal database performance.

SQL Interview Questions Series

*What is a foreign key?*

- A foreign key is a field or a combination of fields in one table that refers to the primary key in another
table. It establishes a relationship between two tables.

*What is the difference between DELETE and TRUNCATE in SQL?*

- DELETE is a DML (Data Manipulation Language) command used to remove rows from a table based on
specified criteria, while TRUNCATE is a DDL (Data Definition Language) command used to remove all
rows from a table, but it does not log individual row deletions.

*Explain the difference between INNER JOIN and LEFT JOIN.*

- INNER JOIN returns only the rows where there is a match in both tables, while LEFT JOIN returns all the
rows from the left table and the matched rows from the right table. If there is no match, NULL values are
returned for the columns from the right table.

. *What is a subquery?*

- A subquery, also known as a nested query or inner query, is a query nested within another query. It can
be used to return data that will be used in the main query's condition.

*Explain the difference between GROUP BY and ORDER BY.*

- GROUP BY is used to group rows that have the same values into summary rows, typically to perform
aggregate functions like COUNT, SUM, AVG, etc., while ORDER BY is used to sort the result set in
ascending or descending order based on specified columns.

*What is a view in SQL?*

- A view is a virtual table based on the result of a SELECT query. It can be used to simplify complex
queries, provide an additional level of security by restricting access to certain columns, or hide the
complexity of the underlying data structure.

. *Explain the ACID properties of transactions in SQL.*

- ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures that either all the
operations within a transaction are completed successfully, or none of them are applied. Consistency
ensures that the database remains in a consistent state before and after the transaction. Isolation
ensures that the execution of multiple transactions concurrently does not result in data inconsistency.
Durability ensures that once a transaction is committed, its changes are permanent and survive
system failures.

𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬 𝐰𝐢𝐭𝐡 𝐀𝐧𝐬𝐰𝐞𝐫𝐬 𝐟𝐨𝐫 𝐝𝐚𝐭𝐚 𝐚𝐧𝐚𝐥𝐲𝐬𝐭 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰

1. What is a primary key and why is it important in a database?

A primary key is a unique identifier for each record in a database table. It is important because it ensures
that each record can be uniquely identified and helps maintain data integrity by preventing duplicate or
null values.

2. Can you explain the difference between INNER JOIN and OUTER JOIN in SQL?

INNER JOIN returns only the rows that have matching values in both tables, while OUTER JOIN returns all
rows from one table and the matched rows from the other table (or null values if there is no match).

3. How do you optimize a SQL query for better performance?

To optimize a SQL query, you can use indexes, avoid using SELECT *, limit the number of columns
selected, use appropriate data types, and avoid using functions in WHERE clauses.

4. What is normalization and why is it important in database design?

Normalization is the process of organizing data in a database to reduce redundancy and dependency. It is
important because it helps improve data integrity, reduce storage space, and make data maintenance
easier.

5. How do you handle missing data in SQL queries?

You can handle missing data in SQL queries by using functions like COALESCE or IFNULL to replace null
values with a default value, or by using the IS NULL or IS NOT NULL operators to filter out records
with missing data.

In the world of SQL interviews, understanding the nuances between the WHERE and HAVING clauses can
be a game-changer!

🔹 WHERE Clause: Think of it as your initial filter when querying a database. This clause is used to extract
records that fulfill a specified condition or set of conditions. It operates on individual rows before the
data is grouped or aggregated.

🔹 HAVING Clause: Here's where things get interesting! The HAVING clause is used in combination with
GROUP BY to filter the results returned by GROUP BY based on specified conditions. It operates on
aggregated data, allowing you to filter groups of rows based on aggregate values.

🔍 Key Difference:

- WHERE is applied to individual rows before grouping.

- HAVING is applied to grouped rows after grouping.

💡 Practical Example:

Let's say we have a table of employees with their respective salaries. If we want to find departments
where the average salary is above a certain threshold:

- We'd use WHERE to filter individual salaries.

- We'd use HAVING to filter groups of departments based on their average salary.

💼 Why It Matters in Interviews:

Understanding these nuances showcases your depth of understanding SQL query optimization and data
manipulation. It demonstrates your ability to efficiently extract the precise data you need, even in
complex scenarios.

💬 What's your take?

Have you encountered any tricky SQL interview questions related to WHERE and HAVING clauses? Share
your experiences and insights below! Let's learn together.

Basic Concepts for Data Analyst (SQL)

Case Statement in SQL:

Imagine you're a chef in a restaurant, and you want to categorize dishes based on their spice level using
SQL:

1. You Have Dishes:

You have a list of dishes and you want to classify them as Mild, Medium, or Spicy based on their spice
level.

2. Using the Spice Level Tool (CASE):

You tell your kitchen staff: "If the dish is not spicy at all, call it Mild. If it's a bit spicy, call it Medium. If it's
really spicy, call it Spicy."
 Example:

SELECT

DishName,

SpiceLevel,

CASE

WHEN SpiceLevel = 'Low' THEN 'Mild'

WHEN SpiceLevel = 'Medium' THEN 'Medium'

ELSE 'Spicy'

END AS SpiceCategory

FROM Menu;

Here, you're using the CASE tool to create a new column (SpiceCategory) based on the spice level of
each dish.

3. Works Anywhere in the Kitchen:

You can use this spice level tool in different parts of your kitchen. Maybe you want to sort the dishes on
the menu based on their spice level, or you want to update the labels of existing dishes.

Example:

UPDATE Menu

SET SpiceCategory =

CASE

WHEN SpiceLevel = 'Low' THEN 'Mild'

WHEN SpiceLevel = 'Medium' THEN

ELSE 'Spicy'
 END;

In this example, you're updating the SpiceCategory of dishes in your menu.

4. It's Like Sorting or Updating with a Spice Guide:

Think of the CASE statement as your spice guide. If it matches a certain spice level, it tells you how to
sort dishes or update their labels.

Example:

SELECT

DishName,

SpiceLevel

FROM Menu

ORDER BY

CASE

WHEN SpiceLevel = 'Low' THEN 1

WHEN SpiceLevel = 'Medium' THEN 2

ELSE 3

END;

Here, you're telling SQL to sort dishes. If it's Low spice, it comes first; if it's Medium, it comes next, and
everything else comes last.

In the kitchen of SQL, the CASE statement is like your spice level guide, helping you classify, sort, or
update dishes based on certain conditions. It's a handy tool for making decisions in your data world!

Tricks to optimize your Naukri portal's profile:-

1. Complete your Naukri portal's profile 100%: Ensure every section of your profile is filled out
completely.

2. Update 0 in the preferred salary part of your profile: Set your preferred salary to 0 to attract more
recruiters. in the interview you can say your expectaion

3. Update your profile daily from 9 AM to 10 AM: Make minor changes and re-upload your resume daily
to stay active.

4. Write a relevant profile headline: Use a headline like "data analyst with 3 years of experience in SQL,
python, tableau, advanced MS excel, and more."

5. Choose "15 days or less" in the notice period section: Indicate your availability as "15 days or less" for
a quicker response from employers.

6. Add relevant skillsets in the key skills section: List skills that match the job roles you are applying for.

7. Showcase your best projects: Highlight projects that are relevant to the job roles you are seeking.

8. Consider a basic premium subscription: A premium subscription can increase your profile's visibility
(optional).

9. Highlight achievements with metrics: Emphasize your successes with quantifiable metrics, like for
example - "increased GMV by 35 CR or 15%."

10. Use keywords effectively: Incorporate industry-specific keywords throughout your profile.
11. Add a professional photo: A professional photo can create a positive impression.

12. Customize your resume for each application: Tailor your resume to highlight the most relevant
experience and skills for each job.

13. Regularly update your profile with new skills and projects: Keep your profile current by adding new
accomplishments and skills as they develop.