Topic – Basic SQL Questions Asked in Analytics Interviews
Check out all the Blogs to Master 200+ Advanced SQL Interview Questions

Complete List of 200+ SQL Interview Questions –

Get the full list of 200+ interview questions here
200 Tricky and Advanced SQL Interview Questions

Basic SQL Questions Asked in Analytics Interviews

In the world of relational databases, data is often scattered across multiple tables. To weave a complete narrative, we need to bring these tables together. This is where SQL joins come into play, acting as bridges between related information. SQL Questions Asked in Analytics

JOINS in SQL – SQL Questions Asked in Analytics

• INNER JOIN: This acts as a filter, only revealing the shared information between two tables. It’s like finding the common ground, the rows that have a match in both tables based on a defined condition.
• LEFT JOIN: This prioritizes the left table, showing all its rows and supplementing them with matching rows from the right table. Where there’s no match, the right table’s columns are filled with NULL values, ensuring no information from the left table is lost.
• RIGHT JOIN: The counterpart of LEFT JOIN, this prioritizes the right table, displaying all its rows and supplementing them with matching rows from the left table.
• FULL JOIN: This aims for inclusivity, showing all rows from both tables, regardless of whether there’s a match. It’s a comprehensive view, combining the results of both LEFT and RIGHT JOINs.

Filtering Data: The Precision of WHERE and HAVING

Filtering data is crucial for narrowing down results and focusing on relevant information. SQL provides two powerful tools for this purpose: WHERE and HAVING.

• WHERE: This acts as a pre-filter, applied to individual rows before any grouping or aggregation takes place. It’s used to set conditions on the raw data.
• HAVING: This operates on aggregated data, filtering groups created by the GROUP BY clause. It’s used to set conditions on the results of aggregate functions.

Grouping and Aggregating Data: The Power of GROUP BY

To analyze data effectively, we often need to group similar rows and perform aggregate calculations. The GROUP BY clause is the key to this process.

• Purpose: It groups rows based on the values of specified columns, creating subsets of data that can be analyzed collectively. This allows us to calculate summary statistics, such as sums, averages, and counts, for each group.

Counting Rows: COUNT(*) vs. COUNT(column_name)

Counting rows is a fundamental operation in SQL, but there are subtle differences in how we approach it.

• COUNT(*): This provides a comprehensive count of all rows in a table or group, including those with NULL values. It’s a universal counter.
• COUNT(column_name): This counts only the rows where the specified column has non-NULL values. It’s a more selective counter, focusing on the presence of data in a specific column.

Storing Text: CHAR vs. VARCHAR

When storing text data, we need to choose the appropriate data type. SQL offers two common options: CHAR and VARCHAR.

• CHAR: This stores fixed-length character strings. If you define a CHAR(10), it will always occupy 10 characters, regardless of the actual string length.
• VARCHAR: This stores variable-length character strings, using only the space needed for the actual string plus a small overhead. This is more efficient for storing text of varying lengths.

Combining Result Sets: UNION vs. UNION ALL

To combine the results of multiple SELECT statements, we can use the UNION and UNION ALL operators.

• UNION: This combines the result sets and removes duplicate rows, ensuring a unique set of results.
• UNION ALL: This combines the result sets without removing duplicates, preserving all rows from all result sets.

Ensuring Data Integrity: ACID Properties

ACID properties are fundamental principles that guarantee the reliability of database transactions.

• Atomicity: A transaction is treated as a single, indivisible unit of work, ensuring that either all operations are completed or none are.
• Consistency: A transaction brings the database from one valid state to another, preserving database constraints.
• Isolation: Concurrent transactions are executed as if they were executed serially, preventing interference between them.
• Durability: Once a transaction is committed, its changes are permanent and survive system failures.

Removing Data: DELETE, TRUNCATE, and DROP

SQL provides three commands for removing data, each with distinct effects.

• DELETE: This removes specific rows based on a WHERE clause, allowing for selective deletion.
• TRUNCATE: This removes all rows from a table, but retains the table structure.
• DROP: This removes the entire table, including its structure and data.

Enforcing Uniqueness: Primary Key vs. Unique Key

Primary keys and unique keys are used to enforce uniqueness, but they have different roles.

• Primary Key: This uniquely identifies each row in a table, and each table can have only one primary key.
• Unique Key: This also enforces uniqueness, but a table can have multiple unique keys, and it can contain NULL values.

Removing Duplicate Rows: DISTINCT vs. GROUP BY

Both DISTINCT and GROUP BY can be used to remove duplicate rows, but they serve different purposes.

• DISTINCT: Returns only unique values from selected columns.
• GROUP BY: Groups rows based on column values, often used with aggregate functions.

Aggregate Functions and Grouping

Aggregate functions can be used without the Group By clause. In this case the aggregate function will operate on the whole dataset, and return a single row. If a where clause is used, then the aggregate function will operate on the returned data of the where clause, and still return a single row. The Group by clause is required when you want to create groups of data, and then apply the aggregate function to those groups.

The Data Monk Best Seller e-books and services

The Data Monk Youtube Videos and Playlist:

Here is a list of playlists available for FREE on Youtube to make sure your interview preparation is on track