Data Analytics Competency Exam: MySQL, Python, Tableau, Excel, and Power BI

Welcome to your Data Analytics Competency Exam: MySQL, Python, Tableau, Excel, and Power BI

What does the acronym SQL stand for?

Structured Query Language

Simple Query Language

Structured Question Language

Simple Question Language

None

Which SQL clause is used to filter records?

SELECT

WHERE

FROM

ORDER BY

None

What does the JOIN clause do in SQL?

Deletes records from a table

Combines rows from two or more tables based on a related column

Creates a new table

Selects unique records

None

Which SQL statement is used to remove a table from a database?

DELETE TABLE

DROP TABLE

REMOVE TABLE

ERASE TABLE

None

What is the default port number for MySQL?

1433

3306

8080

1521

None

Which command is used to modify existing records in a table?

MODIFY

UPDATE

INSERT

ALTER

None

In MySQL, what is the purpose of the GROUP BY clause?

To aggregate data based on one or more columns

To sort data in ascending or descending order

To filter data

To remove duplicate records

None

What is the function of the COUNT() in SQL?

Adds values in a column

Returns the number of rows that match a specified condition

Finds the largest value in a column

Finds the smallest value in a column

None

Which of the following is a valid MySQL data type for storing text?

VARCHAR

INT

DATE

FLOAT

None

What is a foreign key in MySQL?

A key that uniquely identifies each record in a table

A key used to reference another table's primary key

A key used to join two tables

A key that contains only unique values

None

Which SQL statement is used to add a new column to an existing table?

ADD COLUMN

INSERT COLUMN

ALTER TABLE ADD

UPDATE TABLE ADD

None

What is the difference between CHAR and VARCHAR in MySQL?

CHAR is fixed-length, VARCHAR is variable-length

CHAR is variable-length, VARCHAR is fixed-length

CHAR is numeric, VARCHAR is string

CHAR can store images, VARCHAR cannot

None

Which MySQL function is used to get the current date?

GETDATE()

NOW()

SYSDATE()

CURDATE()

None

In MySQL, how do you select all records from a table named 'Employees'?

SELECT * FROM Employees;

SELECT ALL FROM Employees;

SELECT FROM Employees;

SELECT EMPLOYEES;

None

What is the use of the DISTINCT keyword in SQL?

To filter rows that meet specific criteria

To select unique records from a table

To combine rows from multiple tables

To change the structure of a table

None

Which MySQL function is used to combine strings?

CONCAT()

MERGE()

JOIN()

CONNECT()

None

How do you delete all records from a table named 'Orders' in MySQL?

DELETE Orders;

DELETE FROM Orders;

DELETE * FROM Orders;

DROP Orders;

None

Which command is used to change the structure of an existing MySQL table?

CHANGE TABLE

ALTER TABLE

MODIFY TABLE

UPDATE TABLE

None

What is the role of the PRIMARY KEY constraint in MySQL?

To ensure all values in a column are unique

To uniquely identify each record in a table

To enforce a foreign key relationship

To create an index on a column

None

Which of the following is true about the MySQL LIMIT clause?

It is used to restrict the number of rows returned by a query

It is used to filter records based on a condition

It is used to join tables

It is used to sort data

None

Which Python library is primarily used for data manipulation and analysis?

NumPy

Matplotlib

Seaborn

Pandas

None

What does the function "df.head()" do in Pandas?

It shows the first five rows of the DataFrame

It displays the last five rows of the DataFrame

It returns the column names of the DataFrame

It summarizes the data in the DataFrame

None

How do you create a Pandas DataFrame from a dictionary in Python?

pd.DataFrame({data})

pd.create_dataframe(data)

pd.new_dataframe(data)

pd.DataFrame(data)

None

Which library in Python is used for data visualization?

NumPy

Matplotlib

Scikit-learn

Requests

None

What does the following code return? df.describe()

A summary of statistics for numerical columns

A list of all column names

A DataFrame with null values

The first five rows of the DataFrame

None

In Python, which method is used to check for missing values in a DataFrame?

df.isnull()

df.missing()

df.isna()

Both A and C

None

Which Python library is commonly used for machine learning?

Pandas

Matplotlib

Scikit-learn

TensorFlow

None

What does the iloc[] function do in Pandas?

Selects rows and columns by integer-location

Selects rows and columns by labels

Deletes rows and columns

Inserts rows and columns

None

Which function is used to merge two DataFrames in Pandas?

pd.merge()

pd.join()

pd.concat()

pd.append()

None

How do you create a pivot table in Pandas?

pd.pivot_table()

pd.pivot()

pd.pivot_df()

pd.create_pivot()

None

What is the purpose of the groupby() function in Pandas?

To group data by a specific column

To filter data by a specific column

To sort data by a specific column

To join data by a specific column

None

Which Python function is used to read a CSV file into a Pandas DataFrame?

pd.read_excel()

pd.read_csv()

pd.read_table()

pd.read_file()

None

How do you remove a column from a Pandas DataFrame?

df.clear(column_name)

df.delete(column_name)

df.remove(column_name)

df.drop(column_name, axis=1)

None

Which of the following is true about NumPy arrays in Python?

They can store elements of different data types

They are faster and more efficient than Python lists

They are immutable like Python tuples

They can only store numerical data

None

What is the use of the matplotlib.pyplot library in Python?

Data manipulation

Data visualization

Data storage

Data analysis

None

Which method is used to create a histogram in Matplotlib?

plt.plot()

plt.hist()

plt.bar()

plt.scatter()

None

In Python, how do you handle missing data in a Pandas DataFrame?

df.fillna()

df.dropna()

Both A and B

None of the above

None

Which of the following is NOT a feature of Python dictionaries?

They are unordered collections

They have keys and values

They can be accessed by an index

They are mutable

None

What is the output of the following code? print(type(np.array([1, 2, 3])))

None

Which of the following methods would you use to combine DataFrames vertically in Pandas?

df.join()

df.concat()

df.append()

df.merge()

None

What is Tableau primarily used for?

Data collection

Data cleaning

Data visualization

Data storage

None

Which of the following is NOT a type of join available in Tableau?

Inner Join

Left Join

Right Join

Down Join

None

Which Tableau feature allows you to create interactive maps?

Data Blending

Geographic Mapping

Pivot Table

Scatter Plot

None

In Tableau, what is a 'calculated field'?

A field that is imported directly from the data source

A field that is created by combining two or more existing fields

A field that is created by applying a formula or calculation

A field that is used only in filters

None

Which of the following charts in Tableau is best for showing the relationship between two variables?

Line Chart

Scatter Plot

Bar Chart

Pie Chart

None

What does a Tableau 'dashboard' allow you to do?

Connect to multiple data sources

Create multiple sheets within a workbook

Combine multiple visualizations into one interactive display

Export data to Excel

None

Which of the following is a data aggregation function in Tableau?

SUM

COUNT

AVG

All of the above

None

How can you create a hierarchy in Tableau?

By dragging one field onto another in the Data pane

By using the calculated field

By creating a new sheet

By applying a filter

None

What is the purpose of data blending in Tableau?

To create calculated fields

To merge data from different sources

To filter data

To group data

None

Which of the following is NOT a file type used in Tableau?

.twb

.tde

.tbx

.twbx

None

What does 'LOD' stand for in Tableau?

Level of Data

Level of Detail

Length of Data

Load of Data

None

In Tableau, how do you display data labels on a chart?

Drag the measure to the Label shelf

Right-click the chart and select 'Show Labels'

Go to the 'Format' menu and choose 'Labels'

It is done automatically when you create a chart

None

Which feature in Tableau allows you to filter all worksheets in a dashboard at once?

Global Filter

Dashboard Filter

Filter Shelf

Cross Filter

None

What is the default data connection in Tableau?

Live Connection

Extract Connection

Temporary Connection

Cached Connection

None

Which Tableau feature allows you to create a snapshot of data at a specific time?

Extract

Data Snapshot

Bookmark

History Table

None

Which of the following can you use to combine data from multiple tables in Tableau?

Joins

Blends

Unions

All of the above

None

In Tableau, how do you group data within a field?

Use a filter

Use the 'Hierarchy' option

Create a calculated field

Use the 'Group' option from the drop-down menu

None

What is a 'story' in Tableau?

A series of visualizations that work together to convey information

A detailed description of the data

A sequence of calculations applied to the data

A specific filter applied to a dashboard

None

How do you sort data in a Tableau visualization?

Drag the field to the top of the Data pane

Click the sort icon in the visualization

Right-click the field and select 'Sort'

Both B and C

None

Which of the following is used to limit the amount of data loaded into Tableau?

Filters

Extracts

Groups

All of the above

None

What is the primary purpose of Excel?

Data storage

Data visualization

Data analysis and manipulation

Data collection

None

Which of the following is a valid Excel function for summing a range of cells?

=SUM()

=ADD()

=TOTAL()

=AGGREGATE()

None

How do you create a chart in Excel?

Use the 'Insert' tab and select 'Chart'

Use the 'Data' tab and select 'Chart'

Use the 'Home' tab and select 'Chart'

Use the 'View' tab and select 'Chart'

None

What does the VLOOKUP function do in Excel?

Looks for a value in a vertical column and returns a corresponding value

Looks for a value in a horizontal row and returns a corresponding value

Counts the number of blank cells in a range

Calculates the sum of a column of numbers

None

Which of the following is the correct formula to calculate the average of a range in Excel?

=AVG(A1:A10)

=MEAN(A1:A10)

=AVERAGE(A1:A10)

=SUM(A1:A10)/10

None

What is the purpose of the 'Freeze Panes' option in Excel?

To lock rows and columns in place while scrolling

To prevent data from being edited

To highlight cells with conditional formatting

To create a new worksheet

None

How do you remove duplicate values from a dataset in Excel?

Use the 'Data' tab and select 'Remove Duplicates'

Use the 'Home' tab and select 'Remove Duplicates'

Use the 'View' tab and select 'Remove Duplicates'

Use the 'Insert' tab and select 'Remove Duplicates'

None

Which Excel feature allows you to summarize large amounts of data with a few clicks?

PivotTable

Data Table

Chart Wizard

Formula Auditing

None

What does the IF function do in Excel?

Checks a condition and returns one value if true and another if false

Checks a condition and returns a value only if true

Checks a condition and returns a value only if false

Joins two or more text strings together

None

How do you insert a new worksheet in an Excel workbook?

Use the 'Insert' tab and select 'Worksheet'

Right-click an existing sheet tab and select 'Insert'

Click the plus icon next to the sheet tabs

Both B and C

None

Which of the following shortcuts allows you to save an Excel workbook?

Ctrl + S

Ctrl + P

Ctrl + O

Ctrl + A

None

In Excel, what is a 'named range'?

A range of cells that has been given a specific name

A range of cells that has conditional formatting

A range of cells that is part of a table

A range of cells that contains formulas

None

How do you create a PivotTable in Excel?

Use the 'Data' tab and select 'PivotTable'

Use the 'Insert' tab and select 'PivotTable'

Use the 'Home' tab and select 'PivotTable'

Use the 'View' tab and select 'PivotTable'

None

What is the function of the 'AutoSum' feature in Excel?

Automatically sums a range of cells

Automatically fills in values based on patterns

Automatically averages a range of cells

Automatically creates a chart from selected data

None

Which of the following formulas would you use to count the number of cells that contain numbers in a range?

=COUNTIF(A1:A10, ">0")

=COUNTA(A1:A10)

=COUNT(A1:A10)

=COUNTBLANK(A1:A10)

None

How do you apply conditional formatting in Excel?

Use the 'Home' tab and select 'Conditional Formatting'

Use the 'Data' tab and select 'Conditional Formatting'

Use the 'View' tab and select 'Conditional Formatting'

Use the 'Insert' tab and select 'Conditional Formatting'

None

What does the CONCATENATE function do in Excel?

Combines multiple text strings into one string

Adds numbers in a range

Finds the average of a range

Returns the highest value in a range

None

How do you insert a formula into a cell in Excel?

Type an equals sign = followed by the formula

Use the 'Insert' tab and select 'Formula'

Use the 'Data' tab and select 'Formula'

Use the 'View' tab and select 'Formula'

None

Which Excel function would you use to round a number to the nearest integer?

=ROUND()

=INT()

=FLOOR()

=CEILING()

None

How do you reference a cell from another worksheet in Excel?

=SheetName!CellAddress

=WorksheetName!CellAddress

=TableName!CellAddress

=SheetNumber!CellAddress

None

What is Power BI primarily used for?

Data storage

Data visualization and business intelligence

Data collection

Data cleaning

None

Which of the following is a key feature of Power BI?

Real-time data access

Data analysis

Custom visualizations

All of the above

None

What does the 'DAX' stand for in Power BI?

Data Analysis Expressions

Data Access Expressions

Data Analysis Executions

Data Access Executions

None

Which Power BI feature allows you to create interactive reports?

Power BI Desktop

Power BI Service

Power BI Mobile

All of the above

None

In Power BI, what is a 'dashboard'?

A collection of visualizations on a single page

A collection of tables and charts

A set of data tables

A sequence of data transformations

None

Which of the following file types can be imported into Power BI?

Excel

CSV

JSON

All of the above

None

What is the purpose of 'Power Query' in Power BI?

To transform and clean data

To create visualizations

To analyze data

To store data

None

Which of the following can you use to create a relationship between tables in Power BI?

Keys

Joins

Relationships pane

Both A and C

None

Which Power BI feature allows you to perform advanced data modeling and calculations?

DAX

Power Query

Dataflow

Visualizations pane

None

What is the purpose of the 'Q&A' feature in Power BI?

To ask questions in natural language and get answers in the form of visuals

To create new reports

To filter data

To add comments to dashboards

None

Which of the following can be used to create custom visualizations in Power BI?

DAX

Power Query

R Scripts

All of the above

None

In Power BI, what is a 'calculated column'?

A column created by applying a DAX formula to existing data

A column imported from an Excel file

A column used to group data

A column that is hidden from view

None

Which of the following data sources can Power BI connect to?

SQL Server

Excel

Google Analytics

All of the above

None

How do you share a Power BI dashboard with others?

By exporting the dashboard to a PDF

By publishing it to the Power BI Service and sharing the link

By emailing the .pbix file

By taking a screenshot and sending it

None

Which of the following is true about Power BI reports?

They can contain multiple pages

They can contain multiple datasets

They can be shared with others

All of the above

None

What is the use of the 'Filter' pane in Power BI?

To filter data displayed in visualizations

To filter data before importing

To filter data within the Power Query editor

To filter data in the DAX editor

None

Which of the following is NOT a type of visualization available in Power BI?

Line Chart

Scatter Plot

Geographic Map

Gantt Chart

None

In Power BI, what does the term 'refresh' refer to?

Reloading the data in the visualizations

Reloading the data from the source

Reapplying the filters

Resetting the report layout

None

What is a 'dataflow' in Power BI?

A process to load and transform data from multiple sources

A visualization of data trends over time

A sequence of steps to create a dashboard

A connection to a data source

None

Time's up

Time is Up!