📘 SQL Challenges for Data Analytics – With Explanation 🧠
(Beginner ➡️ Advanced)
1️⃣ Select Specific Columns
This fetches only the
✔️ Used when you don’t want all columns from a table.
2️⃣ Filter Records with WHERE
The
✔️ Used for applying conditions on data.
3️⃣ ORDER BY Clause
Sorts all users based on
✔️ Helpful to get latest data first.
4️⃣ Aggregate Functions (COUNT, AVG)
Explanation:
-
-
✔️ Used for quick stats from tables.
5️⃣ GROUP BY Usage
Groups data by
✔️ Use when you want grouped summaries.
6️⃣ JOIN Tables
Fetches user names along with order amounts by joining
✔️ Essential when combining data from multiple tables.
7️⃣ Use of HAVING
Like
✔️ **Use
8️⃣ Subqueries
Finds users whose salary is above the average. The subquery calculates the average salary first.
✔️ Nested queries for dynamic filtering9️⃣ CASE Statementnt**
Adds a new column that classifies users into categories based on age.
✔️ Powerful for conditional logic.
🔟 Window Functions (Advanced)
Ranks users by score *within each city*.
SQL Learning Series: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1075
(Beginner ➡️ Advanced)
1️⃣ Select Specific Columns
SELECT name, email FROM users;
This fetches only the
name and email columns from the users table. ✔️ Used when you don’t want all columns from a table.
2️⃣ Filter Records with WHERE
SELECT * FROM users WHERE age > 30;
The
WHERE clause filters rows where age is greater than 30. ✔️ Used for applying conditions on data.
3️⃣ ORDER BY Clause
SELECT * FROM users ORDER BY registered_at DESC;
Sorts all users based on
registered_at in descending order. ✔️ Helpful to get latest data first.
4️⃣ Aggregate Functions (COUNT, AVG)
SELECT COUNT(*) AS total_users, AVG(age) AS avg_age FROM users;
Explanation:
-
COUNT(*) counts total rows (users). -
AVG(age) calculates the average age. ✔️ Used for quick stats from tables.
5️⃣ GROUP BY Usage
SELECT city, COUNT(*) AS user_count FROM users GROUP BY city;
Groups data by
city and counts users in each group. ✔️ Use when you want grouped summaries.
6️⃣ JOIN Tables
SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;
Fetches user names along with order amounts by joining
users and orders on matching IDs. ✔️ Essential when combining data from multiple tables.
7️⃣ Use of HAVING
SELECT city, COUNT(*) AS total
FROM users
GROUP BY city
HAVING COUNT(*) > 5;
Like
WHERE, but used with aggregates. This filters cities with more than 5 users. ✔️ **Use
HAVING after GROUP BY.**8️⃣ Subqueries
SELECT * FROM users
WHERE salary > (SELECT AVG(salary) FROM users);
Finds users whose salary is above the average. The subquery calculates the average salary first.
✔️ Nested queries for dynamic filtering9️⃣ CASE Statementnt**
SELECT name,
CASE
WHEN age < 18 THEN 'Teen'
WHEN age <= 40 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM users;
Adds a new column that classifies users into categories based on age.
✔️ Powerful for conditional logic.
🔟 Window Functions (Advanced)
SELECT name, city, score,
RANK() OVER (PARTITION BY city ORDER BY score DESC) AS rank
FROM users;
Ranks users by score *within each city*.
SQL Learning Series: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1075
❤4🥰1
SQL Interview Questions with Answers
1. How to change a table name in SQL?
This is the command to change a table name in SQL:
ALTER TABLE table_name
RENAME TO new_table_name;
We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name.
2. How to use LIKE in SQL?
The LIKE operator checks if an attribute value matches a given string pattern. Here is an example of LIKE operator
SELECT * FROM employees WHERE first_name like ‘Steven’;
With this command, we will be able to extract all the records where the first name is like “Steven”.
3. If we drop a table, does it also drop related objects like constraints, indexes, columns, default, views and sorted procedures?
Yes, SQL server drops all related objects, which exists inside a table like constraints, indexes, columns, defaults etc. But dropping a table will not drop views and sorted procedures as they exist outside the table.
4. Explain SQL Constraints.
SQL Constraints are used to specify the rules of data type in a table. They can be specified while creating and altering the table. The following are the constraints in SQL: NOT NULL CHECK DEFAULT UNIQUE PRIMARY KEY FOREIGN KEY
React ❤️ for more
1. How to change a table name in SQL?
This is the command to change a table name in SQL:
ALTER TABLE table_name
RENAME TO new_table_name;
We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name.
2. How to use LIKE in SQL?
The LIKE operator checks if an attribute value matches a given string pattern. Here is an example of LIKE operator
SELECT * FROM employees WHERE first_name like ‘Steven’;
With this command, we will be able to extract all the records where the first name is like “Steven”.
3. If we drop a table, does it also drop related objects like constraints, indexes, columns, default, views and sorted procedures?
Yes, SQL server drops all related objects, which exists inside a table like constraints, indexes, columns, defaults etc. But dropping a table will not drop views and sorted procedures as they exist outside the table.
4. Explain SQL Constraints.
SQL Constraints are used to specify the rules of data type in a table. They can be specified while creating and altering the table. The following are the constraints in SQL: NOT NULL CHECK DEFAULT UNIQUE PRIMARY KEY FOREIGN KEY
React ❤️ for more
❤4
Data Analytics project ideas to build your portfolio in 2025:
1. Sales Data Analysis Dashboard
Analyze sales trends, seasonal patterns, and product performance.
Use Power BI, Tableau, or Python (Dash/Plotly) for visualization.
2. Customer Segmentation
Use clustering (K-means, hierarchical) on customer data to identify groups.
Provide actionable marketing insights.
3. Social Media Sentiment Analysis
Analyze tweets or reviews using NLP to gauge public sentiment.
Visualize positive, negative, and neutral trends over time.
4. Churn Prediction Model
Analyze customer data to predict who might leave a service.
Use logistic regression, decision trees, or random forest.
5. Financial Data Analysis
Study stock prices, moving averages, and volatility.
Create an interactive dashboard with key metrics.
6. Healthcare Analytics
Analyze patient data for disease trends or hospital resource usage.
Use visualization to highlight key findings.
7. Website Traffic Analysis
Use Google Analytics data to identify user behavior patterns.
Suggest improvements for user engagement and conversion.
8. Employee Attrition Analysis
Analyze HR data to find factors leading to employee turnover.
Use statistical tests and visualization.
React ❤️ for more
1. Sales Data Analysis Dashboard
Analyze sales trends, seasonal patterns, and product performance.
Use Power BI, Tableau, or Python (Dash/Plotly) for visualization.
2. Customer Segmentation
Use clustering (K-means, hierarchical) on customer data to identify groups.
Provide actionable marketing insights.
3. Social Media Sentiment Analysis
Analyze tweets or reviews using NLP to gauge public sentiment.
Visualize positive, negative, and neutral trends over time.
4. Churn Prediction Model
Analyze customer data to predict who might leave a service.
Use logistic regression, decision trees, or random forest.
5. Financial Data Analysis
Study stock prices, moving averages, and volatility.
Create an interactive dashboard with key metrics.
6. Healthcare Analytics
Analyze patient data for disease trends or hospital resource usage.
Use visualization to highlight key findings.
7. Website Traffic Analysis
Use Google Analytics data to identify user behavior patterns.
Suggest improvements for user engagement and conversion.
8. Employee Attrition Analysis
Analyze HR data to find factors leading to employee turnover.
Use statistical tests and visualization.
React ❤️ for more
❤2
Here are some commonly asked SQL interview questions along with brief answers:
1. What is SQL?
- SQL stands for Structured Query Language, used for managing and manipulating relational databases.
2. What are the types of SQL commands?
- SQL commands can be broadly categorized into four types: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).
3. What is the difference between CHAR and VARCHAR data types?
- CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type. CHAR will always occupy the same amount of storage space, while VARCHAR will only use the necessary space to store the actual data.
4. What is a primary key?
- A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures data integrity by enforcing uniqueness and can be used to establish relationships between tables.
5. What is a foreign key?
- A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a relationship between two tables and ensures referential integrity.
6. What is a JOIN in SQL?
- 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.
7. 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 (LEFT, RIGHT, FULL) returns all rows from one or both tables, with NULL values in columns where there is no match.
8. What is the difference between GROUP BY and ORDER BY?
- GROUP BY is used to group rows that have the same values into summary rows, typically used with aggregate functions like SUM, COUNT, AVG, etc., while ORDER BY is used to sort the result set based on one or more columns.
9. What is a subquery?
- A subquery is a query nested within another query, used to return data that will be used in the main query. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.
10. What is normalization in SQL?
- Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing large tables into smaller tables and defining relationships between them to improve data integrity and efficiency.
Around 90% questions will be asked from sql in data analytics interview, so please make sure to practice SQL skills using websites like stratascratch. ☺️💪
1. What is SQL?
- SQL stands for Structured Query Language, used for managing and manipulating relational databases.
2. What are the types of SQL commands?
- SQL commands can be broadly categorized into four types: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).
3. What is the difference between CHAR and VARCHAR data types?
- CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type. CHAR will always occupy the same amount of storage space, while VARCHAR will only use the necessary space to store the actual data.
4. What is a primary key?
- A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures data integrity by enforcing uniqueness and can be used to establish relationships between tables.
5. What is a foreign key?
- A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a relationship between two tables and ensures referential integrity.
6. What is a JOIN in SQL?
- 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.
7. 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 (LEFT, RIGHT, FULL) returns all rows from one or both tables, with NULL values in columns where there is no match.
8. What is the difference between GROUP BY and ORDER BY?
- GROUP BY is used to group rows that have the same values into summary rows, typically used with aggregate functions like SUM, COUNT, AVG, etc., while ORDER BY is used to sort the result set based on one or more columns.
9. What is a subquery?
- A subquery is a query nested within another query, used to return data that will be used in the main query. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.
10. What is normalization in SQL?
- Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing large tables into smaller tables and defining relationships between them to improve data integrity and efficiency.
Around 90% questions will be asked from sql in data analytics interview, so please make sure to practice SQL skills using websites like stratascratch. ☺️💪
❤3
SQL interview questions with answers 😄👇
1. Question: What is SQL?
Answer: SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It is used to query, insert, update, and delete data in databases.
2. Question: Differentiate between SQL and MySQL.
Answer: SQL is a language for managing relational databases, while MySQL is an open-source relational database management system (RDBMS) that uses SQL as its language.
3. Question: Explain the difference between INNER JOIN and LEFT JOIN.
Answer: INNER JOIN returns rows when there is a match in both tables, while LEFT JOIN returns all rows from the left table and the matched rows from the right table, filling in with NULLs for non-matching rows.
4. Question: How do you remove duplicate records from a table?
Answer: Use the
5. Question: What is a subquery in SQL?
Answer: A subquery is a query nested inside another query. It can be used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.
6. Question: Explain the purpose of the GROUP BY clause.
Answer: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, like when using aggregate functions such as COUNT, SUM, AVG, etc.
7. Question: How can you add a new record to a table?
Answer: Use the
8. Question: What is the purpose of the HAVING clause?
Answer: The HAVING clause is used in combination with the GROUP BY clause to filter the results of aggregate functions based on a specified condition.
9. Question: Explain the concept of normalization in databases.
Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves breaking down tables into smaller, related tables.
10. Question: How do you update data in a table in SQL?
Answer: Use the
Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz
Share with credits: https://news.1rj.ru/str/sqlspecialist
Hope it helps :)
1. Question: What is SQL?
Answer: SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It is used to query, insert, update, and delete data in databases.
2. Question: Differentiate between SQL and MySQL.
Answer: SQL is a language for managing relational databases, while MySQL is an open-source relational database management system (RDBMS) that uses SQL as its language.
3. Question: Explain the difference between INNER JOIN and LEFT JOIN.
Answer: INNER JOIN returns rows when there is a match in both tables, while LEFT JOIN returns all rows from the left table and the matched rows from the right table, filling in with NULLs for non-matching rows.
4. Question: How do you remove duplicate records from a table?
Answer: Use the
DISTINCT keyword in a SELECT statement to retrieve unique records. For example: SELECT DISTINCT column1, column2 FROM table;5. Question: What is a subquery in SQL?
Answer: A subquery is a query nested inside another query. It can be used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.
6. Question: Explain the purpose of the GROUP BY clause.
Answer: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, like when using aggregate functions such as COUNT, SUM, AVG, etc.
7. Question: How can you add a new record to a table?
Answer: Use the
INSERT INTO statement. For example: INSERT INTO table_name (column1, column2) VALUES (value1, value2);8. Question: What is the purpose of the HAVING clause?
Answer: The HAVING clause is used in combination with the GROUP BY clause to filter the results of aggregate functions based on a specified condition.
9. Question: Explain the concept of normalization in databases.
Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves breaking down tables into smaller, related tables.
10. Question: How do you update data in a table in SQL?
Answer: Use the
UPDATE statement to modify existing records in a table. For example: UPDATE table_name SET column1 = value1 WHERE condition;Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz
Share with credits: https://news.1rj.ru/str/sqlspecialist
Hope it helps :)
❤3
A step-by-step guide to land a job as a data analyst
Landing your first data analyst job is toughhhhh.
Here are 11 tips to make it easier:
- Master SQL.
- Next, learn a BI tool.
- Drink lots of tea or coffee.
- Tackle relevant data projects.
- Create a relevant data portfolio.
- Focus on actionable data insights.
- Remember imposter syndrome is normal.
- Find ways to prove you’re a problem-solver.
- Develop compelling data visualization stories.
- Engage with LinkedIn posts from fellow analysts.
- Illustrate your analytical impact with metrics & KPIs.
- Share your career story & insights via LinkedIn posts.
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
Landing your first data analyst job is toughhhhh.
Here are 11 tips to make it easier:
- Master SQL.
- Next, learn a BI tool.
- Drink lots of tea or coffee.
- Tackle relevant data projects.
- Create a relevant data portfolio.
- Focus on actionable data insights.
- Remember imposter syndrome is normal.
- Find ways to prove you’re a problem-solver.
- Develop compelling data visualization stories.
- Engage with LinkedIn posts from fellow analysts.
- Illustrate your analytical impact with metrics & KPIs.
- Share your career story & insights via LinkedIn posts.
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
❤1
1. What is the difference between the RANK() and DENSE_RANK() functions?
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.
2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?
One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.
3. What is the shortcut to add a filter to a table in EXCEL?
The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.
4. What is DAX in Power BI?
DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.
5. Define shelves and sets in Tableau?
Shelves: Every worksheet in Tableau will have shelves such as columns, rows, marks, filters, pages, and more. By placing filters on shelves we can build our own visualization structure. We can control the marks by including or excluding data.
Sets: The sets are used to compute a condition on which the dataset will be prepared. Data will be grouped together based on a condition. Fields which is responsible for grouping are known assets. For example – students having grades of more than 70%.
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.
2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?
One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesn’t affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.
3. What is the shortcut to add a filter to a table in EXCEL?
The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.
4. What is DAX in Power BI?
DAX stands for Data Analysis Expressions. It's a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.
5. Define shelves and sets in Tableau?
Shelves: Every worksheet in Tableau will have shelves such as columns, rows, marks, filters, pages, and more. By placing filters on shelves we can build our own visualization structure. We can control the marks by including or excluding data.
Sets: The sets are used to compute a condition on which the dataset will be prepared. Data will be grouped together based on a condition. Fields which is responsible for grouping are known assets. For example – students having grades of more than 70%.
❤2
Roadmap to become a data analyst
1. Foundation Skills:
•Strengthen Mathematics: Focus on statistics relevant to data analysis.
•Excel Basics: Master fundamental Excel functions and formulas.
2. SQL Proficiency:
•Learn SQL Basics: Understand SELECT statements, JOINs, and filtering.
•Practice Database Queries: Work with databases to retrieve and manipulate data.
3. Excel Advanced Techniques:
•Data Cleaning in Excel: Learn to handle missing data and outliers.
•PivotTables and PivotCharts: Master these powerful tools for data summarization.
4. Data Visualization with Excel:
•Create Visualizations: Learn to build charts and graphs in Excel.
•Dashboard Creation: Understand how to design effective dashboards.
5. Power BI Introduction:
•Install and Explore Power BI: Familiarize yourself with the interface.
•Import Data: Learn to import and transform data using Power BI.
6. Power BI Data Modeling:
•Relationships: Understand and establish relationships between tables.
•DAX (Data Analysis Expressions): Learn the basics of DAX for calculations.
7. Advanced Power BI Features:
•Advanced Visualizations: Explore complex visualizations in Power BI.
•Custom Measures and Columns: Utilize DAX for customized data calculations.
8. Integration of Excel, SQL, and Power BI:
•Importing Data from SQL to Power BI: Practice connecting and importing data.
•Excel and Power BI Integration: Learn how to use Excel data in Power BI.
9. Business Intelligence Best Practices:
•Data Storytelling: Develop skills in presenting insights effectively.
•Performance Optimization: Optimize reports and dashboards for efficiency.
10. Build a Portfolio:
•Showcase Excel Projects: Highlight your data analysis skills using Excel.
•Power BI Projects: Feature Power BI dashboards and reports in your portfolio.
11. Continuous Learning and Certification:
•Stay Updated: Keep track of new features in Excel, SQL, and Power BI.
•Consider Certifications: Obtain relevant certifications to validate your skills.
1. Foundation Skills:
•Strengthen Mathematics: Focus on statistics relevant to data analysis.
•Excel Basics: Master fundamental Excel functions and formulas.
2. SQL Proficiency:
•Learn SQL Basics: Understand SELECT statements, JOINs, and filtering.
•Practice Database Queries: Work with databases to retrieve and manipulate data.
3. Excel Advanced Techniques:
•Data Cleaning in Excel: Learn to handle missing data and outliers.
•PivotTables and PivotCharts: Master these powerful tools for data summarization.
4. Data Visualization with Excel:
•Create Visualizations: Learn to build charts and graphs in Excel.
•Dashboard Creation: Understand how to design effective dashboards.
5. Power BI Introduction:
•Install and Explore Power BI: Familiarize yourself with the interface.
•Import Data: Learn to import and transform data using Power BI.
6. Power BI Data Modeling:
•Relationships: Understand and establish relationships between tables.
•DAX (Data Analysis Expressions): Learn the basics of DAX for calculations.
7. Advanced Power BI Features:
•Advanced Visualizations: Explore complex visualizations in Power BI.
•Custom Measures and Columns: Utilize DAX for customized data calculations.
8. Integration of Excel, SQL, and Power BI:
•Importing Data from SQL to Power BI: Practice connecting and importing data.
•Excel and Power BI Integration: Learn how to use Excel data in Power BI.
9. Business Intelligence Best Practices:
•Data Storytelling: Develop skills in presenting insights effectively.
•Performance Optimization: Optimize reports and dashboards for efficiency.
10. Build a Portfolio:
•Showcase Excel Projects: Highlight your data analysis skills using Excel.
•Power BI Projects: Feature Power BI dashboards and reports in your portfolio.
11. Continuous Learning and Certification:
•Stay Updated: Keep track of new features in Excel, SQL, and Power BI.
•Consider Certifications: Obtain relevant certifications to validate your skills.
❤2
SQL interview questions with answers 😄👇
1. Question: What is SQL?
Answer: SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It is used to query, insert, update, and delete data in databases.
2. Question: Differentiate between SQL and MySQL.
Answer: SQL is a language for managing relational databases, while MySQL is an open-source relational database management system (RDBMS) that uses SQL as its language.
3. Question: Explain the difference between INNER JOIN and LEFT JOIN.
Answer: INNER JOIN returns rows when there is a match in both tables, while LEFT JOIN returns all rows from the left table and the matched rows from the right table, filling in with NULLs for non-matching rows.
4. Question: How do you remove duplicate records from a table?
Answer: Use the
5. Question: What is a subquery in SQL?
Answer: A subquery is a query nested inside another query. It can be used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.
6. Question: Explain the purpose of the GROUP BY clause.
Answer: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, like when using aggregate functions such as COUNT, SUM, AVG, etc.
7. Question: How can you add a new record to a table?
Answer: Use the
8. Question: What is the purpose of the HAVING clause?
Answer: The HAVING clause is used in combination with the GROUP BY clause to filter the results of aggregate functions based on a specified condition.
9. Question: Explain the concept of normalization in databases.
Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves breaking down tables into smaller, related tables.
10. Question: How do you update data in a table in SQL?
Answer: Use the
Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz
Share with credits: https://news.1rj.ru/str/sqlspecialist
Hope it helps :)
1. Question: What is SQL?
Answer: SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It is used to query, insert, update, and delete data in databases.
2. Question: Differentiate between SQL and MySQL.
Answer: SQL is a language for managing relational databases, while MySQL is an open-source relational database management system (RDBMS) that uses SQL as its language.
3. Question: Explain the difference between INNER JOIN and LEFT JOIN.
Answer: INNER JOIN returns rows when there is a match in both tables, while LEFT JOIN returns all rows from the left table and the matched rows from the right table, filling in with NULLs for non-matching rows.
4. Question: How do you remove duplicate records from a table?
Answer: Use the
DISTINCT keyword in a SELECT statement to retrieve unique records. For example: SELECT DISTINCT column1, column2 FROM table;5. Question: What is a subquery in SQL?
Answer: A subquery is a query nested inside another query. It can be used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.
6. Question: Explain the purpose of the GROUP BY clause.
Answer: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, like when using aggregate functions such as COUNT, SUM, AVG, etc.
7. Question: How can you add a new record to a table?
Answer: Use the
INSERT INTO statement. For example: INSERT INTO table_name (column1, column2) VALUES (value1, value2);8. Question: What is the purpose of the HAVING clause?
Answer: The HAVING clause is used in combination with the GROUP BY clause to filter the results of aggregate functions based on a specified condition.
9. Question: Explain the concept of normalization in databases.
Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves breaking down tables into smaller, related tables.
10. Question: How do you update data in a table in SQL?
Answer: Use the
UPDATE statement to modify existing records in a table. For example: UPDATE table_name SET column1 = value1 WHERE condition;Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz
Share with credits: https://news.1rj.ru/str/sqlspecialist
Hope it helps :)
❤2
Data Analyst INTERVIEW QUESTIONS AND ANSWERS
👇👇
1.Can you name the wildcards in Excel?
Ans: There are 3 wildcards in Excel that can ve used in formulas.
Asterisk (*) – 0 or more characters. For example, Ex* could mean Excel, Extra, Expertise, etc.
Question mark (?) – Represents any 1 character. For example, R?ain may mean Rain or Ruin.
Tilde (~) – Used to identify a wildcard character (~, *, ?). For example, If you need to find the exact phrase India* in a list. If you use India* as the search string, you may get any word with India at the beginning followed by different characters (such as Indian, Indiana). If you have to look for India” exclusively, use ~.
Hence, the search string will be india~*. ~ is used to ensure that the spreadsheet reads the following character as is, and not as a wildcard.
2.What is cascading filter in tableau?
Ans: Cascading filters can also be understood as giving preference to a particular filter and then applying other filters on previously filtered data source. Right-click on the filter you want to use as a main filter and make sure it is set as all values in dashboard then select the subsequent filter and select only relevant values to cascade the filters. This will improve the performance of the dashboard as you have decreased the time wasted in running all the filters over complete data source.
3.What is the difference between .twb and .twbx extension?
Ans:
A .twb file contains information on all the sheets, dashboards and stories, but it won’t contain any information regarding data source. Whereas .twbx file contains all the sheets, dashboards, stories and also compressed data sources. For saving a .twbx extract needs to be performed on the data source. If we forward .twb file to someone else than they will be able to see the worksheets and dashboards but won’t be able to look into the dataset.
4.What are the various Power BI versions?
Power BI Premium capacity-based license, for example, allows users with a free license to act on content in workspaces with Premium capacity. A user with a free license can only use the Power BI service to connect to data and produce reports and dashboards in My Workspace outside of Premium capacity. They are unable to exchange material or publish it in other workspaces. To process material, a Power BI license with a free or Pro per-user license only uses a shared and restricted capacity. Users with a Power BI Pro license can only work with other Power BI Pro users if the material is stored in that shared capacity. They may consume user-generated information, post material to app workspaces, share dashboards, and subscribe to dashboards and reports. Pro users can share material with users who don’t have a Power BI Pro subnoscription while workspaces are at Premium capacity.
ENJOY LEARNING 👍👍
👇👇
1.Can you name the wildcards in Excel?
Ans: There are 3 wildcards in Excel that can ve used in formulas.
Asterisk (*) – 0 or more characters. For example, Ex* could mean Excel, Extra, Expertise, etc.
Question mark (?) – Represents any 1 character. For example, R?ain may mean Rain or Ruin.
Tilde (~) – Used to identify a wildcard character (~, *, ?). For example, If you need to find the exact phrase India* in a list. If you use India* as the search string, you may get any word with India at the beginning followed by different characters (such as Indian, Indiana). If you have to look for India” exclusively, use ~.
Hence, the search string will be india~*. ~ is used to ensure that the spreadsheet reads the following character as is, and not as a wildcard.
2.What is cascading filter in tableau?
Ans: Cascading filters can also be understood as giving preference to a particular filter and then applying other filters on previously filtered data source. Right-click on the filter you want to use as a main filter and make sure it is set as all values in dashboard then select the subsequent filter and select only relevant values to cascade the filters. This will improve the performance of the dashboard as you have decreased the time wasted in running all the filters over complete data source.
3.What is the difference between .twb and .twbx extension?
Ans:
A .twb file contains information on all the sheets, dashboards and stories, but it won’t contain any information regarding data source. Whereas .twbx file contains all the sheets, dashboards, stories and also compressed data sources. For saving a .twbx extract needs to be performed on the data source. If we forward .twb file to someone else than they will be able to see the worksheets and dashboards but won’t be able to look into the dataset.
4.What are the various Power BI versions?
Power BI Premium capacity-based license, for example, allows users with a free license to act on content in workspaces with Premium capacity. A user with a free license can only use the Power BI service to connect to data and produce reports and dashboards in My Workspace outside of Premium capacity. They are unable to exchange material or publish it in other workspaces. To process material, a Power BI license with a free or Pro per-user license only uses a shared and restricted capacity. Users with a Power BI Pro license can only work with other Power BI Pro users if the material is stored in that shared capacity. They may consume user-generated information, post material to app workspaces, share dashboards, and subscribe to dashboards and reports. Pro users can share material with users who don’t have a Power BI Pro subnoscription while workspaces are at Premium capacity.
ENJOY LEARNING 👍👍
❤1
1. What data sources can Power BI connect to?
Ans: The list of data sources for Power BI is extensive, but it can be grouped into the following:
Files: Data can be imported from Excel (.xlsx, xlxm), Power BI Desktop files (.pbix) and Comma Separated Value (.csv).
Content Packs: It is a collection of related documents or files that are stored as a group. In Power BI, there are two types of content packs, firstly those from services providers like Google Analytics, Marketo, or Salesforce, and secondly those created and shared by other users in your organization.
Connectors to databases and other datasets such as Azure SQL, Database and SQL, Server Analysis Services tabular data, etc.
2. What are the different integrity rules present in the DBMS?
The different integrity rules present in DBMS are as follows:
Entity Integrity: This rule states that the value of the primary key can never be NULL. So, all the tuples in the column identified as the primary key should have a value.
Referential Integrity: This rule states that either the value of the foreign key is NULL or it should be the primary key of any other relation.
3. What are some common clauses used with SELECT query in SQL?
Some common SQL clauses used in conjuction with a SELECT query are as follows:
WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.
ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).
GROUP BY clause in SQL is used to group records with identical data and can be used in conjunction with some aggregation functions to produce summarized results from the database.
HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.
4. What is the difference between count, counta, and countblank in Excel?
The count function is very often used in Excel. Here, let’s look at the difference between count, and it’s variants - counta and countblank.
1. COUNT
It counts the number of cells that contain numeric values only. Cells that have string values, special characters, and blank cells will not be counted.
2. COUNTA
It counts the number of cells that contain any form of content. Cells that have string values, special characters, and numeric values will be counted. However, a blank cell will not be counted.
3. COUNTBLANK
As the name suggests, it counts the number of blank cells only. Cells that have content will not be taken into consideration.
Ans: The list of data sources for Power BI is extensive, but it can be grouped into the following:
Files: Data can be imported from Excel (.xlsx, xlxm), Power BI Desktop files (.pbix) and Comma Separated Value (.csv).
Content Packs: It is a collection of related documents or files that are stored as a group. In Power BI, there are two types of content packs, firstly those from services providers like Google Analytics, Marketo, or Salesforce, and secondly those created and shared by other users in your organization.
Connectors to databases and other datasets such as Azure SQL, Database and SQL, Server Analysis Services tabular data, etc.
2. What are the different integrity rules present in the DBMS?
The different integrity rules present in DBMS are as follows:
Entity Integrity: This rule states that the value of the primary key can never be NULL. So, all the tuples in the column identified as the primary key should have a value.
Referential Integrity: This rule states that either the value of the foreign key is NULL or it should be the primary key of any other relation.
3. What are some common clauses used with SELECT query in SQL?
Some common SQL clauses used in conjuction with a SELECT query are as follows:
WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.
ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).
GROUP BY clause in SQL is used to group records with identical data and can be used in conjunction with some aggregation functions to produce summarized results from the database.
HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.
4. What is the difference between count, counta, and countblank in Excel?
The count function is very often used in Excel. Here, let’s look at the difference between count, and it’s variants - counta and countblank.
1. COUNT
It counts the number of cells that contain numeric values only. Cells that have string values, special characters, and blank cells will not be counted.
2. COUNTA
It counts the number of cells that contain any form of content. Cells that have string values, special characters, and numeric values will be counted. However, a blank cell will not be counted.
3. COUNTBLANK
As the name suggests, it counts the number of blank cells only. Cells that have content will not be taken into consideration.
❤2🎉1
Excel interview questions for both data analysts and business analysts
1) What are the basic functions of Microsoft Excel?
2) Explain the difference between a workbook and a worksheet.
3) How would you freeze panes in Excel?
4) Can you name some common keyboard shortcuts in Excel?
5) What is the purpose of VLOOKUP and HLOOKUP?
7) How do you remove duplicate values in Excel?
8) Explain the steps to filter data in Excel.
9) What is the significance of the "IF" function in Excel, and can you provide an example of its use?
10) How would you create a pivot table in Excel?
11) Explain the use of the CONCATENATE function in Excel.
12) How do you create a chart in Excel?
13) Explain the difference between a line chart and a scatter plot.
14) What is conditional formatting, and how can it be applied in Excel?
15) How would you create a dynamic chart that updates with new data?
16) What is the INDEX-MATCH function, and how is it different from VLOOKUP?
17) Can you explain the concept of "PivotTables" and when you would use them?
18) How do you use the "COUNTIF" and "SUMIF" functions in Excel?
19) Explain the purpose of the "What-If Analysis" tools in Excel.
20) What are array formulas, and can you provide an example of their use?
Business Analysis Specific:
1) How would you analyze a set of sales data to identify trends and insights?
2) Explain how you might use Excel to perform financial modeling.
3) What Excel features would you use for forecasting and budgeting?
4) How do you handle large datasets in Excel, and what tools or techniques do you use for optimization?
5) What are some common techniques for cleaning and validating data in Excel?
6) How do you identify and handle errors in a dataset using Excel?
Scenario-based Questions:
1) Imagine you have a dataset with missing values. How would you approach this problem in Excel?
2) You are given a dataset with multiple sheets. How would you consolidate the data for analysis?
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
1) What are the basic functions of Microsoft Excel?
2) Explain the difference between a workbook and a worksheet.
3) How would you freeze panes in Excel?
4) Can you name some common keyboard shortcuts in Excel?
5) What is the purpose of VLOOKUP and HLOOKUP?
7) How do you remove duplicate values in Excel?
8) Explain the steps to filter data in Excel.
9) What is the significance of the "IF" function in Excel, and can you provide an example of its use?
10) How would you create a pivot table in Excel?
11) Explain the use of the CONCATENATE function in Excel.
12) How do you create a chart in Excel?
13) Explain the difference between a line chart and a scatter plot.
14) What is conditional formatting, and how can it be applied in Excel?
15) How would you create a dynamic chart that updates with new data?
16) What is the INDEX-MATCH function, and how is it different from VLOOKUP?
17) Can you explain the concept of "PivotTables" and when you would use them?
18) How do you use the "COUNTIF" and "SUMIF" functions in Excel?
19) Explain the purpose of the "What-If Analysis" tools in Excel.
20) What are array formulas, and can you provide an example of their use?
Business Analysis Specific:
1) How would you analyze a set of sales data to identify trends and insights?
2) Explain how you might use Excel to perform financial modeling.
3) What Excel features would you use for forecasting and budgeting?
4) How do you handle large datasets in Excel, and what tools or techniques do you use for optimization?
5) What are some common techniques for cleaning and validating data in Excel?
6) How do you identify and handle errors in a dataset using Excel?
Scenario-based Questions:
1) Imagine you have a dataset with missing values. How would you approach this problem in Excel?
2) You are given a dataset with multiple sheets. How would you consolidate the data for analysis?
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
❤2