1. What are Query and Query language?

A query is nothing but a request sent to a database to retrieve data or information. The required data can be retrieved from a table or many tables in the database.

Query languages use various types of queries to retrieve data from databases. SQL, Datalog, and AQL are a few examples of query languages; however, SQL is known to be the widely used query language.



2. What are Superkey and candidate key?

A super key may be a single or a combination of keys that help to identify a record in a table. Know that Super keys can have one or more attributes, even though all the attributes are not necessary to identify the records.

A candidate key is the subset of Superkey, which can have one or more than one attributes to identify records in a table. Unlike Superkey, all the attributes of the candidate key must be helpful to identify the records.


3. What do you mean by buffer pool and mention its benefits?

A buffer pool in SQL is also known as a buffer cache. All the resources can store their cached data pages in a buffer pool. The size of the buffer pool can be defined during the configuration of an instance of SQL Server.
The following are the benefits of a buffer pool:

Increase in I/O performance
Reduction in I/O latency
Increase in transaction throughput
Increase in reading performance


4. What is the difference between Zero and NULL values in SQL?

When a field in a column doesn’t have any value, it is said to be having a NULL value. Simply put, NULL is the blank field in a table. It can cancel be considered as an unassigned, unknown, or unavailable value. On the contrary, zero is a number, and it is an available, assigned, and known value.

1. List the different types of relationships in SQL.

One-to-One - This can be defined as the relationship between two tables where each record in one table is associated with the maximum of one record in the other table.
One-to-Many & Many-to-One - This is the most commonly used relationship where a record in a table is associated with multiple records in the other table.
Many-to-Many - This is used in cases when multiple instances on both sides are needed for defining a relationship.
Self-Referencing Relationships - This is used when a table needs to define a relationship with itself.

2. What are the different views available in Power BI Desktop?

There are three different views in Power BI, each of which serves another purpose:
Report View - In this view, users can add visualizations and additional report pages and publish the same on the portal.
Data View - In this view, data shaping can be performed using Query Editor tools.
Model View - In this view, users can manage relationships between complex datasets.


3. What are macros in Excel?

Excel allows you to automate the tasks you do regularly by recording them into macros. So, a macro is an action or a set of them that you can perform n number of times. For example, if you have to record the sales of each item at the end of the day, you can create a macro that will automatically calculate the sales, profits, loss, etc and use the same for the future instead of manually calculating it every day.

1. What is the Difference Between a Shallow Copy and Deep Copy in python?

Deep copy creates a different object and populates it with the child objects of the original object. Therefore, changes in the original object are not reflected in the copy. copy.deepcopy() creates a Deep Copy. Shallow copy creates a different object and populates it with the references of the child objects within the original object. Therefore, changes in the original object are reflected in the copy. copy.copy creates a Shallow Copy.

2. How can you remove duplicate values in a range of cells?

1. To delete duplicate values in a column, select the highlighted cells, and press the delete button. After deleting the values, go to the ‘Conditional Formatting’ option present in the Home tab. Choose ‘Clear Rules’ to remove the rules from the sheet.

2. You can also delete duplicate values by selecting the ‘Remove Duplicates’ option under Data Tools present in the Data tab.


3. 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%.

4. Define Entity, Entity type, and Entity set.

Entity can be anything, be it a place, class or object which has an independent existence in the real world.

Entity Type represents a set of entities that have similar attributes.

Entity Set in the database represents a collection of entities having a particular entity type.

1. What is the Difference Between a Shallow Copy and Deep Copy in python?

Deepcopy creates a different object and populates it with the child objects of the original object. Therefore, changes in the original object are not reflected in the copy. copy.deepcopy() creates a Deep Copy. Shallow copy creates a different object and populates it with the references of the child objects within the original object. Therefore, changes in the original object are reflected in the copy. copy.copy creates a Shallow Copy.


2. How can you remove duplicate values in a range of cells?

1. To delete duplicate values in a column, select the highlighted cells, and press the delete button. After deleting the values, go to the ‘Conditional Formatting’ option present in the Home tab. Choose ‘Clear Rules’ to remove the rules from the sheet.

2. You can also delete duplicate values by selecting the ‘Remove Duplicates’ option under Data Tools present in the Data tab.


3. 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%.

Data Analyst Interview Questions

1. What do Tableau's sets and groups mean?

Data is grouped using sets and groups according to predefined criteria. The primary distinction between the two is that although a set can have only two options—either in or out—a group can divide the dataset into several groups. A user should decide which group or sets to apply based on the conditions.

2.What in Excel is a macro?

An Excel macro is an algorithm or a group of steps that helps automate an operation by capturing and replaying the steps needed to finish it. Once the steps have been saved, you may construct a Macro that the user can alter and replay as often as they like.

Macro is excellent for routine work because it also gets rid of mistakes. Consider the scenario when an account manager needs to share reports about staff members who owe the company money. If so, it can be automated by utilising a macro and making small adjustments each month as necessary.


3.Gantt chart in Tableau

A Tableau Gantt chart illustrates the duration of events as well as the progression of value across the period. Along with the time axis, it has bars. The Gantt chart is primarily used as a project management tool, with each bar representing a project job.

4.In Microsoft Excel, how do you create a drop-down list?

Start by selecting the Data tab from the ribbon.
Select Data Validation from the Data Tools group.
Go to Settings > Allow > List next.
Choose the source you want to offer in the form of a list array.

Data Analytics Interview Preparation Series Part-1
[Questions with Answers]

Why did you want your job?

I was originally studying physics but didn't want to do a PhD. So, after my master’s I decided I would try a job working with data. I noticed that it was quite common for people studying science to go into data after. I had several friends who went on to become data scientists directly after their undergrad.
I noticed that given my background in maths and some noscripting in Python (thanks to computational physics classes), it wouldn't be too hard to make the jump.
I went into data science because I wanted a more mathematical role with a research component (model design, experimentation, metric design etc.)
This was instead of a more practical role like data analysis or data engineering.
It turned out to be a cool choice and I'm enjoying my time as a data scientist right now!

Why did you choose the industry that you work in?

I work in a music-tech start up. I love it because I make music on the side. Being able to work in
music and be surrounded by people who are also passionate about music is very cool!
The company organizes concerts with artists that we work with etc. It's really cool! This makes the job more interesting for me, given that it's so tightly related to what I love to do.

Like if want me to continue the series 😄❤️

Time to test your data analytics knowledge
👇👇
https://www.instagram.com/dataanalyticsinterview?utm_source=qr&igsh=MXNkbXM3dmN2Nmhibw==

Let's see who gives the first correct answer 😁

Q. Explain the data preprocessing steps in data analysis.

Ans. Data preprocessing transforms the data into a format that is more easily and effectively processed in data mining, machine learning and other data science tasks.
1. Data profiling.
2. Data cleansing.
3. Data reduction.
4. Data transformation.
5. Data enrichment.
6. Data validation.

Q. What Are the Three Stages of Building a Model in Machine Learning?

Ans. The three stages of building a machine learning model are:

Model Building: Choosing a suitable algorithm for the model and train it according to the requirement

Model Testing: Checking the accuracy of the model through the test data

Applying the Model: Making the required changes after testing and use the final model for real-time projects


Q. What are the subsets of SQL?

Ans. The following are the four significant subsets of the SQL:

Data definition language (DDL): It defines the data structure that consists of commands like CREATE, ALTER, DROP, etc.

Data manipulation language (DML): It is used to manipulate existing data in the database. The commands in this category are SELECT, UPDATE, INSERT, etc.

Data control language (DCL): It controls access to the data stored in the database. The commands in this category include GRANT and REVOKE.

Transaction Control Language (TCL): It is used to deal with the transaction operations in the database. The commands in this category are COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.


Q. What is a Parameter in Tableau? Give an Example.

Ans. A parameter is a dynamic value that a customer could select, and you can use it to replace constant values in calculations, filters, and reference lines.
For example, when creating a filter to show the top 10 products based on total profit instead of the fixed value, you can update the filter to show the top 10, 20, or 30 products using a parameter.

Here are some resources to prepare for your data analyst interview 👇👇
https://www.instagram.com/reel/C5aEthbIhE7/?igsh=MXM3eTJvYmN4YnBocw==

Data Analyst Interview Questions

1. Is indentation required in python?

Indentation is necessary for Python. It specifies a block of code. All code within loops, classes, functions, etc is specified within an indented block. It is usually done using four space characters. If your code is not indented necessarily, it will not execute accurately and will throw errors as well.

2. What are Entities and Relationships?

Entity: An entity can be a real-world object that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities.

Relationships: Relations or links between entities that have something to do with each other. For example – The employee’s table in a company’s database can be associated with the salary table in the same database.

3. What is a stored procedure?

Stored Procedure is a function consists of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

4. What is Auto Increment?

Auto increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.
Mostly this keyword can be used whenever PRIMARY KEY is used.

5. Which operator is used in query for pattern matching?

LIKE operator is used for pattern matching, and it can be used as -.
1. % – Matches zero or more characters.
2. _(Underscore) – Matching exactly one character.

1. Define the term 'Data Wrangling.

Data Wrangling is the process wherein raw data is cleaned, structured, and enriched into a desired usable format for better decision making. It involves discovering, structuring, cleaning, enriching, validating, and analyzing data. This process can turn and map out large amounts of data extracted from various sources into a more useful format.

2. What are the best methods for data cleaning?

Create a data cleaning plan by understanding where the common errors take place and keep all the communications open. Before working with the data, identify and remove the duplicates. This will lead to an easy and effective data analysis process.Focus on the accuracy of the data. Set cross-field validation, maintain the value types of data, and provide mandatory constraints.Normalize the data at the entry point so that it is less chaotic. You will be able to ensure that all information is standardized, leading to fewer errors on entry.


3. Explain the Type I and Type II errors in Statistics?

In Hypothesis testing, a Type I error occurs when the null hypothesis is rejected even if it is true. It is also known as a false positive.

A Type II error occurs when the null hypothesis is not rejected, even if it is false. It is also known as a false negative.

4. How do you make a dropdown list in MS Excel?

First, click on the Data tab that is present in the ribbon.Under the Data Tools group, select Data Validation.Then navigate to Settings > Allow > List.Select the source you want to provide as a list array.

5. State some ways to improve the performance of Tableau?

Use an Extract to make workbooks run faster.
Reduce the scope of data to decrease the volume of data.
Reduce the number of marks on the view to avoid information overload.
Hide unused fields.
Use Context filters.
Use indexing in tables and use the same fields for filtering.
Remove unnecessary calculations and sheets.

Follow our instgram page for data analytics quiz 👇👇
https://www.instagram.com/dataanalyticsinterview?igsh=MXNkbXM3dmN2Nmhibw==

✅ Basic Level (Focuses on fundamental concepts and operations in SQL, including syntax, basic commands, and the definitions of key terms.)

1. Explain the difference between drop and truncate.
2. What are Constraints in SQL?
3. Describe the use of the SELECT statement in SQL.
4. What is a primary key in SQL?
5. Explain the difference between CHAR and VARCHAR data types in SQL.
6. What is a foreign key in SQL?
7. How do you use the GROUP BY statement in SQL?
8. What is a JOIN in SQL, and can you describe a scenario where you would use it?
9. How does the WHERE clause work in SQL?
10. Explain the use of the INSERT statement in SQL.

✅Intermediate Level ( Involves more complex queries, including the use of sub-queries, joins, and functions. It requires a deeper understanding of SQL for data manipulation and analysis.)

1. Describe the Difference Between Window Functions and Aggregate Functions in SQL.
2. Write a SQL query to find the top three products with the highest revenue in the last quarter from a sales database.
3. What do you understand by sub-queries in SQL?
4. What is CTE in SQL?
5. Explain the use of the HAVING clause in SQL.
6. How do you implement pagination in SQL queries?
7. Describe the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
8. Explain the concept of indexing in SQL and its benefits.
9. How can you prevent SQL injection in your queries?
10. Write a SQL query to find the second highest salary in a given table.

✅ Advanced Level (Covers topics related to database optimization, advanced data manipulation techniques, and understanding SQL's impact on database performance and design.)

1. Describe a SQL query challenge you faced related to optimizing database performance.
2. What is a Recursive Stored Procedure in SQL?
3. What are the subsets of SQL?
4. How do you use window functions for running totals and moving averages?
5. Explain the process and considerations for denormalizing a database.
6. Discuss the implications and solutions for dealing with NULL values in SQL operations.
7. How do you handle large datasets and optimize queries for big data in SQL?
8. Describe how to implement transaction control in SQL and its importance.
9. Explain the concept of materialized views in SQL and their use cases.
10. Discuss strategies for database sharding and partitioning in SQL and their impact on performance.

Excel Interview Questions with Answers
👇👇
https://www.linkedin.com/posts/sql-analysts_top-excel-interview-questions-with-answers-activity-7185644525871058944-9wtm?utm_source=share&utm_medium=member_android

Here are the questions With Answers ✨

1. Write a query to get the EmpFname from the EmployeeInfo table in the upper case using the alias name as EmpName.

[
SELECT UPPER(EmpFname) AS EmpName FROM EmployeeInfo;
]

2. Write a query to get the number of employees working in the department ‘HR’.

[
SELECT COUNT(*) FROM EmployeeInfo WHERE Department = 'HR';
]

3. What query will you write to fetch the current date?

[
-- For SQL Server:
SELECT GETDATE();

-- For MySQL:
SELECT SYSDATE();
]

4. Write a query to fetch only the place name (string before brackets) from the Address column of the EmployeeInfo table.

[
-- Using MID function in MySQL:
SELECT MID(Address, 1, LOCATE('(', Address) - 1) FROM EmployeeInfo;

-- Using SUBSTRING function:
SELECT SUBSTRING(Address, 1, CHARINDEX('(', Address) - 1) FROM EmployeeInfo;
]

5. Write a query to create a new table whose data and structure are copied from another table.

[
-- Using SELECT INTO in SQL Server:
SELECT * INTO NewTable FROM EmployeeInfo WHERE 1 = 0;

-- Using CREATE TABLE AS in MySQL:
CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo;
]

6. Write a query to display the names of employees that begin with ‘S’.

[
SELECT * FROM EmployeeInfo WHERE EmpFname LIKE 'S%';
]

7. Write a query to retrieve the top N records.

[
-- Using TOP in SQL Server:
SELECT TOP N * FROM EmployeePosition ORDER BY Salary DESC;

-- Using LIMIT in MySQL:
SELECT * FROM EmployeePosition ORDER BY Salary DESC LIMIT N;
]

8. Write a query to obtain relevant records from the EmployeeInfo table ordered by Department in ascending order and EmpLname in descending order.

[
SELECT * FROM EmployeeInfo ORDER BY Department ASC, EmpLname DESC;
]

9. Write a query to get the details of employees whose EmpFname ends with ‘A’.

[
SELECT * FROM EmployeeInfo WHERE EmpFname LIKE '%A';
]

10. Create a query to fetch details of employees having “DELHI” as their address.

[
SELECT * FROM EmployeeInfo WHERE Address LIKE '%DELHI%';
]

11. Write a query to fetch all employees who also hold the managerial position.

[
SELECT E.EmpFname, E.EmpLname, P.EmpPosition
FROM EmployeeInfo E
INNER JOIN EmployeePosition P ON E.EmpID = P.EmpID
WHERE P.EmpPosition = 'Manager';
]

12. Create a query to generate the first and last records from the EmployeeInfo table.

[
-- First record:
SELECT * FROM EmployeeInfo WHERE EmpID = (SELECT MIN(EmpID) FROM EmployeeInfo);

-- Last record:
SELECT * FROM EmployeeInfo WHERE EmpID = (SELECT MAX(EmpID) FROM EmployeeInfo);
]

13. Create a query to check if the passed value to the query follows the EmployeeInfo and EmployeePosition tables’ date format.

[
SELECT ISDATE('01/04/2020') AS "MM/DD/YY";
]

14. Create a query to obtain display employees having salaries equal to or greater than 150000.

[
SELECT EmpName FROM EmployeePosition WHERE Salary >= 150000;
]

15. Write a query to fetch the year using a date.

[
SELECT YEAR(GETDATE()) AS "Year";
]

16. Create an SQL query to fetch EmpPosition and the total salary paid for each employee position.

[
SELECT EmpPosition, SUM(Salary) FROM EmployeePosition GROUP BY EmpPosition;
]

17. Write a query to find duplicate records from a table.

[
SELECT EmpID, EmpFname, Department, COUNT(*)
FROM EmployeeInfo
GROUP BY EmpID, EmpFname, Department
HAVING COUNT(*) > 1;
]

18. Create a query to fetch the third-highest salary from the EmpPosition table.

[
SELECT TOP 1 Salary
FROM (
SELECT TOP 3 Salary
FROM EmpPosition
ORDER BY Salary DESC
) AS ThirdHighestSalary
ORDER BY Salary ASC;
]

19. Write an SQL query to find even and odd records in the EmployeeInfo table.

[
-- Even records:
SELECT EmpID FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmpID) AS rowno, EmpID FROM EmployeeInfo) AS T1 WHERE MOD(rowno, 2) = 0;

-- Odd records:
SELECT EmpID FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmpID) AS rowno, EmpID FROM EmployeeInfo) AS T1 WHERE MOD(rowno, 2) = 1;
]

20. Create a query to fetch the list of employees of the same department.

[
SELECT DISTINCT E1.EmpID, E1.EmpFname, E1.Department
FROM EmployeeInfo E1
INNER JOIN EmployeeInfo E2 ON E1.Department = E2.
]

Most asked SQL interview questions for Data Analyst/Data Engineer role-

1 - What is SQL and what are its main features?
2 - Order of writing SQL query?
3- Order of execution of SQL query?
4- What are some of the most common SQL commands?
5- What’s a primary key & foreign key?
6 - All types of joins and questions on their outputs?
7 - Explain all window functions and difference between them?
8 - What is stored procedure?
9 - Difference between stored procedure & Functions in SQL?
10 - What is trigger in SQL?
11 - Difference between where and having?

Answers to above SQL interview questions:

1. SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. Its main features include querying and managing data, defining and modifying database structures, and controlling access to data.

2. The order of writing an SQL query typically starts with the SELECT clause to specify columns, followed by the FROM clause to specify tables, then optional clauses like WHERE (for filtering), GROUP BY (for grouping), HAVING (for filtering after grouping), ORDER BY (for sorting), and finally, LIMIT/OFFSET (for pagination).

3. The order of execution of an SQL query is generally: FROM (specify data sources), WHERE (apply conditions), GROUP BY (perform grouping), HAVING (filter grouped data), SELECT (retrieve columns), DISTINCT (remove duplicates), ORDER BY (sort results), and finally, LIMIT/OFFSET (apply result limits).

4. Common SQL commands include SELECT (retrieve data), INSERT (add new records), UPDATE (modify existing records), DELETE (remove records), CREATE TABLE (create a new table), ALTER TABLE (modify existing table structure), and DROP TABLE (delete a table).

5. A primary key uniquely identifies each record in a table and ensures no duplicate values.
A foreign key establishes a link between two tables, referencing the primary key of another table to maintain referential integrity.

6. SQL joins include INNER JOIN (returns rows where there is a match in both tables), LEFT JOIN (returns all rows from the left table and matching rows from the right table), RIGHT JOIN (returns all rows from the right table and matching rows from the left table), and FULL JOIN (returns all rows when there is a match in either table).

7. Window functions (like ROW_NUMBER, RANK, DENSE_RANK, etc.) operate over a window of rows and can perform calculations across rows related to the current row. Differences lie in how they assign ranks or sequence numbers based on specified criteria within the window.

8. A stored procedure is a precompiled collection of SQL statements and procedural logic stored in the database and executed as a unit. It can accept input parameters, perform operations, and return results.

9. The main difference between stored procedures and functions in SQL is that stored procedures can perform DML (Data Manipulation Language) operations, such as INSERT, UPDATE, and DELETE, whereas functions are primarily used to compute values and cannot change data.

10. A trigger in SQL is a special type of stored procedure that automatically executes when a specific event (like INSERT, UPDATE, or DELETE) occurs on a table. Triggers are used to enforce business rules, maintain data integrity, or automate tasks.

11. The WHERE clause is used to filter rows before any groupings are made
(typically used in SELECT, UPDATE, or DELETE statements).

The HAVING clause is used to filter rows after the grouping has been done, based on aggregate values
(typically used in SELECT statements with GROUP BY).

Like ❤️ this post if you need more data analytics interview Questions with Answers

Here's a list of commonly asked data analyst interview questions:

1. Tell me about yourself : This is often the opener, allowing you to summarize your background, skills, and experiences.

2. What is the difference between data analytics and data science?: Be ready to explain these terms and how they differ.

3. Describe a typical data analysis process you follow: Walk through steps like data collection, cleaning, analysis, and interpretation.

4. What programming languages are you proficient in?: Typically SQL, Python, R are common; mention any others you're familiar with.

5. How do you handle missing or incomplete data?: Discuss methods like imputation or excluding records based on criteria.

6. Explain a time when you used data to solve a problem: Provide a detailed example showcasing your analytical skills.

7. What data visualization tools have you used?: Tableau, Power BI, or others; discuss your experience.

8. How do you ensure the quality and accuracy of your analytical work?: Mention techniques like validation, peer reviews, or data audits.

9. What is your approach to presenting complex data findings to non-technical stakeholders?: Highlight your communication skills and ability to simplify complex information.

10. Describe a challenging data project you've worked on: Explain the project, challenges faced, and how you overcame them.

11. How do you stay updated with the latest trends in data analytics?: Talk about blogs, courses, or communities you follow.

12. What statistical techniques are you familiar with?: Regression, clustering, hypothesis testing, etc.; explain when you've used them.

13. How would you assess the effectiveness of a new data model?: Discuss metrics like accuracy, precision, recall, etc.

14. Give an example of a time when you dealt with a large dataset: Explain how you managed and processed the data efficiently.

15. Why do you want to work for this company?: Tailor your response to highlight why their industry or culture appeals to you

1. What do you understand about the E-R model?
Answer: E-R model is an Entity-Relationship model which defines the conceptual view of the database.
The E-R model basically shows the real-world entities and their association/relations. Entities here represent the set of attributes in the database.

2. Explain the terms ‘Attribute’ and ‘Relations’
Answer:
Attribute is described as the properties or characteristics of an entity. For Example, Employee ID, Employee Name, Age, etc., can be attributes of the entity Employee.
Relation is a two-dimensional table containing a number of rows and columns where every row represents a record of the relation. Here, rows are also known as ‘Tuples’ and columns are known as ‘Attributes’.

3. What is the Database transaction?
Answer: Sequence of operation performed which changes the consistent state of the database to another is known as the database transaction. After the completion of the transaction, either the successful completion is reflected in the system or the transaction fails and no change is reflected.

4. What do you understand about ‘Atomicity’ and ‘Aggregation’?
Answer: Atomicity is the condition where either all the actions of the transaction are performed or none. This means, when there is an incomplete transaction, the database management system itself will undo the effects done by the incomplete transaction.
Aggregation is the concept of expressing the relationship with the collection of entities and their relationships.