Important SQL Interview Questions and Answers (2025)
1. How do you find duplicate records in SQL?
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
This query groups records by a specific column and filters groups that have more than one occurrence.
2. What are the different types of SQL Joins?
- INNER JOIN: Returns records that have matching values in both tables.
- LEFT JOIN: Returns all records from the left table and the matched records from the right table.
- RIGHT JOIN: Returns all records from the right table and the matched records from the left table.
- FULL OUTER JOIN: Returns all records when there is a match in either left or right table.
- SELF JOIN: A regular join but the table is joined with itself.
3. What is a Trigger in SQL? A trigger is a stored procedure that runs automatically in response to specific events on a particular table or view (e.g., INSERT, UPDATE, DELETE).
4. Differentiate between DDL and DML commands in SQL.
DDL (Data Definition Language): Defines database schema (CREATE, ALTER, DROP).
DML (Data Manipulation Language): Manipulates data (INSERT, UPDATE, DELETE).
5. Difference between DELETE, DROP, and TRUNCATE.
DELETE: Removes specific rows, can use WHERE clause, can be rolled back.
TRUNCATE: Removes all rows, faster than DELETE, cannot be rolled back in most DBMS.
DROP: Deletes the entire table structure and data.
6. UNION vs UNION ALL – What’s the difference?
UNION: Removes duplicate records.
UNION ALL: Includes duplicates.
7. Which command gives unique values in SQL?
SELECT DISTINCT column_name FROM table_name;
8. WHERE vs HAVING – Key differences?
WHERE: Filters rows before grouping.
HAVING: Filters groups after aggregation.
9. Explain the execution order of SQL keywords.
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT
10. IN vs BETWEEN operators – When to use what?
IN: For checking values within a list.
BETWEEN: For checking values within a range.
11. Define Primary Key and Foreign Key.
Primary Key: Uniquely identifies each record in a table.
Foreign Key: Links records between two tables.
12. What are Aggregate Functions in SQL?
Functions like COUNT(), SUM(), AVG(), MAX(), MIN() used for calculations on data.
13. Difference between RANK and DENSE_RANK?
RANK(): Leaves gaps in ranking when there are ties.
DENSE_RANK(): No gaps in ranks.
14. List and explain ACID Properties.
Atomicity: All or nothing.
Consistency: Valid state before and after a transaction.
Isolation: Transactions don't affect each other.
Durability: Results are permanent after commit.
15. What is the difference between % and _ in LIKE operator?
%: Matches any number of characters.
_: Matches a single character.
16. What is CTE (Common Table Expression)? A temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
WITH CTE AS (
SELECT column FROM table
)
SELECT * FROM CTE;
17. What is a Database, DBMS, and RDBMS?
Database: Organized data collection.
DBMS: Software to manage databases.
RDBMS: DBMS based on relational model (tables, keys).
18. What is an Alias in SQL?
Temporary name for a table or column.
SELECT column_name AS alias_name FROM table_name;
19. What is Normalization? Explain its types. Process of organizing data to reduce redundancy:
1NF: Eliminate repeating groups.
2NF: Remove partial dependency.
3NF: Remove transitive dependency.
20. How to sort query results in SQL?
Use ORDER BY clause.
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
21. Explain types of Window Functions.
ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), LAG(), etc., work over a partition of data.
22. What is LIMIT and OFFSET in SQL?
LIMIT: Specifies the number of records to return.
OFFSET: Specifies how many rows to skip before starting to return rows.
Credits: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
1. How do you find duplicate records in SQL?
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
This query groups records by a specific column and filters groups that have more than one occurrence.
2. What are the different types of SQL Joins?
- INNER JOIN: Returns records that have matching values in both tables.
- LEFT JOIN: Returns all records from the left table and the matched records from the right table.
- RIGHT JOIN: Returns all records from the right table and the matched records from the left table.
- FULL OUTER JOIN: Returns all records when there is a match in either left or right table.
- SELF JOIN: A regular join but the table is joined with itself.
3. What is a Trigger in SQL? A trigger is a stored procedure that runs automatically in response to specific events on a particular table or view (e.g., INSERT, UPDATE, DELETE).
4. Differentiate between DDL and DML commands in SQL.
DDL (Data Definition Language): Defines database schema (CREATE, ALTER, DROP).
DML (Data Manipulation Language): Manipulates data (INSERT, UPDATE, DELETE).
5. Difference between DELETE, DROP, and TRUNCATE.
DELETE: Removes specific rows, can use WHERE clause, can be rolled back.
TRUNCATE: Removes all rows, faster than DELETE, cannot be rolled back in most DBMS.
DROP: Deletes the entire table structure and data.
6. UNION vs UNION ALL – What’s the difference?
UNION: Removes duplicate records.
UNION ALL: Includes duplicates.
7. Which command gives unique values in SQL?
SELECT DISTINCT column_name FROM table_name;
8. WHERE vs HAVING – Key differences?
WHERE: Filters rows before grouping.
HAVING: Filters groups after aggregation.
9. Explain the execution order of SQL keywords.
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT
10. IN vs BETWEEN operators – When to use what?
IN: For checking values within a list.
BETWEEN: For checking values within a range.
11. Define Primary Key and Foreign Key.
Primary Key: Uniquely identifies each record in a table.
Foreign Key: Links records between two tables.
12. What are Aggregate Functions in SQL?
Functions like COUNT(), SUM(), AVG(), MAX(), MIN() used for calculations on data.
13. Difference between RANK and DENSE_RANK?
RANK(): Leaves gaps in ranking when there are ties.
DENSE_RANK(): No gaps in ranks.
14. List and explain ACID Properties.
Atomicity: All or nothing.
Consistency: Valid state before and after a transaction.
Isolation: Transactions don't affect each other.
Durability: Results are permanent after commit.
15. What is the difference between % and _ in LIKE operator?
%: Matches any number of characters.
_: Matches a single character.
16. What is CTE (Common Table Expression)? A temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
WITH CTE AS (
SELECT column FROM table
)
SELECT * FROM CTE;
17. What is a Database, DBMS, and RDBMS?
Database: Organized data collection.
DBMS: Software to manage databases.
RDBMS: DBMS based on relational model (tables, keys).
18. What is an Alias in SQL?
Temporary name for a table or column.
SELECT column_name AS alias_name FROM table_name;
19. What is Normalization? Explain its types. Process of organizing data to reduce redundancy:
1NF: Eliminate repeating groups.
2NF: Remove partial dependency.
3NF: Remove transitive dependency.
20. How to sort query results in SQL?
Use ORDER BY clause.
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
21. Explain types of Window Functions.
ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), LAG(), etc., work over a partition of data.
22. What is LIMIT and OFFSET in SQL?
LIMIT: Specifies the number of records to return.
OFFSET: Specifies how many rows to skip before starting to return rows.
Credits: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
👍7❤4🎉1
SQL Interview Questions which can be asked in a Data Analyst Interview.
1️⃣ What is difference between Primary key and Unique key?
◼Primary key- A column or set of columns which uniquely identifies each record in a table. It can't contain null values and only one primary key
can exist in a table.
◼Unique key-Similar to primary key it also uniquely identifies each record in a table and can contain null values.Multiple Unique key can exist in a table.
2️⃣ What is a Candidate key?
◼A key or set of keys that uniquely identifies each record in a table.It is a combination of Primary and Alternate key.
3️⃣ What is a Constraint?
◼Specific rule or limit that we define in our table. E.g - NOT NULL,AUTO INCREMENT
4️⃣ Can you differentiate between TRUNCATE and DELETE?
◼TRUNCATE is a DDL command. It deletes the entire data from a table but preserves the structure of table.It doesn't deletes the data row by row hence faster than DELETE command, while DELETE is a DML command and it deletes the entire data based on specified condition else deletes the entire data,also it deletes the data row by row hence slower than TRUNCATE command.
5️⃣ What is difference between 'View' and 'Stored Procedure'?
◼A View is a virtual table that gets data from the base table .It is basically a Select statement,while Stored Procedure is a sql statement or set of sql statement stored on database server.
6️⃣ What is difference between a Common Table Expression and temporary table?
◼CTE is a temporary result set that is defined within execution scope of a single SELECT ,DELETE,UPDATE statement while temporary table is stored in TempDB and gets deleted once the session expires.
7️⃣ Differentiate between a clustered index and a non-clustered index?
◼ A clustered index determines physical ordering of data in a table and a table can have only one clustered index while a non-clustered index is analogous to index of a book where index is stored at one place and data at other place and index will have pointers to storage location of the data,a table can have more than one non-clustered index.
8️⃣ Explain triggers ?
◼They are sql codes which are automatically executed in response to certain events on a table.They are used to maintain integrity of data.
1️⃣ What is difference between Primary key and Unique key?
◼Primary key- A column or set of columns which uniquely identifies each record in a table. It can't contain null values and only one primary key
can exist in a table.
◼Unique key-Similar to primary key it also uniquely identifies each record in a table and can contain null values.Multiple Unique key can exist in a table.
2️⃣ What is a Candidate key?
◼A key or set of keys that uniquely identifies each record in a table.It is a combination of Primary and Alternate key.
3️⃣ What is a Constraint?
◼Specific rule or limit that we define in our table. E.g - NOT NULL,AUTO INCREMENT
4️⃣ Can you differentiate between TRUNCATE and DELETE?
◼TRUNCATE is a DDL command. It deletes the entire data from a table but preserves the structure of table.It doesn't deletes the data row by row hence faster than DELETE command, while DELETE is a DML command and it deletes the entire data based on specified condition else deletes the entire data,also it deletes the data row by row hence slower than TRUNCATE command.
5️⃣ What is difference between 'View' and 'Stored Procedure'?
◼A View is a virtual table that gets data from the base table .It is basically a Select statement,while Stored Procedure is a sql statement or set of sql statement stored on database server.
6️⃣ What is difference between a Common Table Expression and temporary table?
◼CTE is a temporary result set that is defined within execution scope of a single SELECT ,DELETE,UPDATE statement while temporary table is stored in TempDB and gets deleted once the session expires.
7️⃣ Differentiate between a clustered index and a non-clustered index?
◼ A clustered index determines physical ordering of data in a table and a table can have only one clustered index while a non-clustered index is analogous to index of a book where index is stored at one place and data at other place and index will have pointers to storage location of the data,a table can have more than one non-clustered index.
8️⃣ Explain triggers ?
◼They are sql codes which are automatically executed in response to certain events on a table.They are used to maintain integrity of data.
👍4🎉1
Preparing for a SQL interview?
Focus on mastering these essential topics:
1. Joins: Get comfortable with inner, left, right, and outer joins.
Knowing when to use what kind of join is important!
2. Window Functions: Understand when to use
ROW_NUMBER, RANK(), DENSE_RANK(), LAG, and LEAD for complex analytical queries.
3. Query Execution Order: Know the sequence from FROM to
ORDER BY. This is crucial for writing efficient, error-free queries.
4. Common Table Expressions (CTEs): Use CTEs to simplify and structure complex queries for better readability.
5. Aggregations & Window Functions: Combine aggregate functions with window functions for in-depth data analysis.
6. Subqueries: Learn how to use subqueries effectively within main SQL statements for complex data manipulations.
7. Handling NULLs: Be adept at managing NULL values to ensure accurate data processing and avoid potential pitfalls.
8. Indexing: Understand how proper indexing can significantly boost query performance.
9. GROUP BY & HAVING: Master grouping data and filtering groups with HAVING to refine your query results.
10. String Manipulation Functions: Get familiar with string functions like CONCAT, SUBSTRING, and REPLACE to handle text data efficiently.
11. Set Operations: Know how to use UNION, INTERSECT, and EXCEPT to combine or compare result sets.
12. Optimizing Queries: Learn techniques to optimize your queries for performance, especially with large datasets.
If we master/ Practice in these topics we can track any SQL interviews..
Like this post if you need more 👍❤️
Hope it helps :)
Focus on mastering these essential topics:
1. Joins: Get comfortable with inner, left, right, and outer joins.
Knowing when to use what kind of join is important!
2. Window Functions: Understand when to use
ROW_NUMBER, RANK(), DENSE_RANK(), LAG, and LEAD for complex analytical queries.
3. Query Execution Order: Know the sequence from FROM to
ORDER BY. This is crucial for writing efficient, error-free queries.
4. Common Table Expressions (CTEs): Use CTEs to simplify and structure complex queries for better readability.
5. Aggregations & Window Functions: Combine aggregate functions with window functions for in-depth data analysis.
6. Subqueries: Learn how to use subqueries effectively within main SQL statements for complex data manipulations.
7. Handling NULLs: Be adept at managing NULL values to ensure accurate data processing and avoid potential pitfalls.
8. Indexing: Understand how proper indexing can significantly boost query performance.
9. GROUP BY & HAVING: Master grouping data and filtering groups with HAVING to refine your query results.
10. String Manipulation Functions: Get familiar with string functions like CONCAT, SUBSTRING, and REPLACE to handle text data efficiently.
11. Set Operations: Know how to use UNION, INTERSECT, and EXCEPT to combine or compare result sets.
12. Optimizing Queries: Learn techniques to optimize your queries for performance, especially with large datasets.
If we master/ Practice in these topics we can track any SQL interviews..
Like this post if you need more 👍❤️
Hope it helps :)
❤4👍2👏1🎉1
35 Most Common SQL Interview Questions 👇👇
1.) Explain order of execution of SQL.
2.) What is difference between where and having?
3.) What is the use of group by?
4.) Explain all types of joins in SQL?
5.) What are triggers in SQL?
6.) What is stored procedure in SQL
7.) Explain all types of window functions?
(Mainly rank, row_num, dense_rank, lead & lag)
8.) What is difference between Delete and Truncate?
9.) What is difference between DML, DDL and DCL?
10.) What are aggregate function and when do we use them? explain with few example.
11.) Which is faster between CTE and Subquery?
12.) What are constraints and types of Constraints?
13.) Types of Keys?
14.) Different types of Operators ?
15.) Difference between Group By and Where?
16.) What are Views?
17.) What are different types of constraints?
18.) What is difference between varchar and nvarchar?
19.) Similar for char and nchar?
20.) What are index and their types?
21.) What is an index? Explain its different types.
22.) List the different types of relationships in SQL.
23.) Differentiate between UNION and UNION ALL.
24.) How many types of clauses in SQL?
25.) What is the difference between UNION and UNION ALL in SQL?
26.) What are the various types of relationships in SQL?
27.) Difference between Primary Key and Secondary Key?
28.) What is the difference between where and having?
29.) Find the second highest salary of an employee?
30.) Write retention query in SQL?
31.) Write year-on-year growth in SQL?
32.) Write a query for cummulative sum in SQL?
33.) Difference between Function and Store procedure ?
34.) Do we use variable in views?
35.) What are the limitations of views?
React with ❤️ for the detailed answers
1.) Explain order of execution of SQL.
2.) What is difference between where and having?
3.) What is the use of group by?
4.) Explain all types of joins in SQL?
5.) What are triggers in SQL?
6.) What is stored procedure in SQL
7.) Explain all types of window functions?
(Mainly rank, row_num, dense_rank, lead & lag)
8.) What is difference between Delete and Truncate?
9.) What is difference between DML, DDL and DCL?
10.) What are aggregate function and when do we use them? explain with few example.
11.) Which is faster between CTE and Subquery?
12.) What are constraints and types of Constraints?
13.) Types of Keys?
14.) Different types of Operators ?
15.) Difference between Group By and Where?
16.) What are Views?
17.) What are different types of constraints?
18.) What is difference between varchar and nvarchar?
19.) Similar for char and nchar?
20.) What are index and their types?
21.) What is an index? Explain its different types.
22.) List the different types of relationships in SQL.
23.) Differentiate between UNION and UNION ALL.
24.) How many types of clauses in SQL?
25.) What is the difference between UNION and UNION ALL in SQL?
26.) What are the various types of relationships in SQL?
27.) Difference between Primary Key and Secondary Key?
28.) What is the difference between where and having?
29.) Find the second highest salary of an employee?
30.) Write retention query in SQL?
31.) Write year-on-year growth in SQL?
32.) Write a query for cummulative sum in SQL?
33.) Difference between Function and Store procedure ?
34.) Do we use variable in views?
35.) What are the limitations of views?
React with ❤️ for the detailed answers
❤2👍2🎉1
35 Important SQL Interview Questions with Detailed Answers:
1. Explain order of execution of SQL.
Order: FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. SQL queries are processed in this logical sequence, not the way they are written.
2. What is difference between WHERE and HAVING?
WHERE filters rows before aggregation, while HAVING filters groups after aggregation.
3. What is the use of GROUP BY?
GROUP BY aggregates data across rows with the same values in specified columns, commonly used with aggregate functions.
4. Explain all types of joins in SQL?
INNER JOIN: Returns matching rows from both tables.
LEFT JOIN: All rows from the left, matched rows from right.
RIGHT JOIN: All rows from the right, matched rows from left.
FULL JOIN: All rows from both, with NULLs where no match.
SELF JOIN: Joins table to itself.
CROSS JOIN: Cartesian product of both tables.
5. What are triggers in SQL?
Triggers are procedural code executed automatically in response to certain events on a table or view (INSERT, UPDATE, DELETE).
6. What is stored procedure in SQL?
A stored procedure is a set of SQL statements saved and executed on demand, useful for modularizing code.
7. Explain all types of window functions?
RANK(): Gives rank with gaps.
DENSE_RANK(): Ranks without gaps.
ROW_NUMBER(): Unique row index.
LEAD(): Access next row.
LAG(): Access previous row.
8. What is difference between DELETE and TRUNCATE?
DELETE: Row-wise deletion, can have WHERE clause, logs each row.
TRUNCATE: Deletes all rows, faster, minimal logging, cannot rollback easily.
9. What is difference between DML, DDL and DCL?
DML: Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE).
DDL: Data Definition Language (CREATE, ALTER, DROP).
DCL: Data Control Language (GRANT, REVOKE).
10. What are aggregate functions?
Functions that return a single value: SUM(), AVG(), COUNT(), MIN(), MAX().
11. Which is faster: CTE or Subquery?
Performance depends on context, but subqueries are sometimes faster as CTEs may be materialized.
12. What are constraints and types?
Rules to maintain data integrity. Types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
13. Types of Keys?
Primary Key
Foreign Key
Unique Key
Composite Key
Candidate Key
14. Different types of Operators?
Arithmetic: +, -, *, /
Comparison: =, <>, >, <, >=, <=
Logical: AND, OR, NOT
Bitwise, LIKE, IN, BETWEEN
15. Difference between GROUP BY and WHERE?
WHERE filters before aggregation. GROUP BY groups after filtering.
16. What are Views?
Virtual tables based on SQL queries. They store only query definition.
17. What are different types of constraints?
Same as Q12: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
18. What is difference between VARCHAR and NVARCHAR?
VARCHAR: ASCII, 1 byte per char.
NVARCHAR: Unicode, 2 bytes per char, supports multiple languages.
19. Similarity for CHAR and NCHAR?
CHAR: Fixed-length ASCII.
NCHAR: Fixed-length Unicode.
20. What are indexes and their types?
Used for faster retrieval.
Types:
- Clustered
- Non-clustered
- Unique
- Composite
- Full-text
21. What is an index? Explain its types.
Same as above. Indexes speed up queries by creating pointers to data.
22. List different types of relationships in SQL.
One-to-One
One-to-Many
Many-to-Many
23. Differentiate between UNION and UNION ALL.
UNION: Removes duplicates.
UNION ALL: Includes duplicates.
24. How many types of clauses in SQL?
Common clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, JOIN, ON.
25. What is the difference between UNION and UNION ALL in SQL?
Same as Q23.
26. What are various types of relationships in SQL?
Same as Q22.
27. Difference between Primary Key and Secondary Key?
Primary Key: Uniquely identifies rows.
Secondary Key: May not be unique, used for lookup.
Credits: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1000
1. Explain order of execution of SQL.
Order: FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. SQL queries are processed in this logical sequence, not the way they are written.
2. What is difference between WHERE and HAVING?
WHERE filters rows before aggregation, while HAVING filters groups after aggregation.
3. What is the use of GROUP BY?
GROUP BY aggregates data across rows with the same values in specified columns, commonly used with aggregate functions.
4. Explain all types of joins in SQL?
INNER JOIN: Returns matching rows from both tables.
LEFT JOIN: All rows from the left, matched rows from right.
RIGHT JOIN: All rows from the right, matched rows from left.
FULL JOIN: All rows from both, with NULLs where no match.
SELF JOIN: Joins table to itself.
CROSS JOIN: Cartesian product of both tables.
5. What are triggers in SQL?
Triggers are procedural code executed automatically in response to certain events on a table or view (INSERT, UPDATE, DELETE).
6. What is stored procedure in SQL?
A stored procedure is a set of SQL statements saved and executed on demand, useful for modularizing code.
7. Explain all types of window functions?
RANK(): Gives rank with gaps.
DENSE_RANK(): Ranks without gaps.
ROW_NUMBER(): Unique row index.
LEAD(): Access next row.
LAG(): Access previous row.
8. What is difference between DELETE and TRUNCATE?
DELETE: Row-wise deletion, can have WHERE clause, logs each row.
TRUNCATE: Deletes all rows, faster, minimal logging, cannot rollback easily.
9. What is difference between DML, DDL and DCL?
DML: Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE).
DDL: Data Definition Language (CREATE, ALTER, DROP).
DCL: Data Control Language (GRANT, REVOKE).
10. What are aggregate functions?
Functions that return a single value: SUM(), AVG(), COUNT(), MIN(), MAX().
11. Which is faster: CTE or Subquery?
Performance depends on context, but subqueries are sometimes faster as CTEs may be materialized.
12. What are constraints and types?
Rules to maintain data integrity. Types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
13. Types of Keys?
Primary Key
Foreign Key
Unique Key
Composite Key
Candidate Key
14. Different types of Operators?
Arithmetic: +, -, *, /
Comparison: =, <>, >, <, >=, <=
Logical: AND, OR, NOT
Bitwise, LIKE, IN, BETWEEN
15. Difference between GROUP BY and WHERE?
WHERE filters before aggregation. GROUP BY groups after filtering.
16. What are Views?
Virtual tables based on SQL queries. They store only query definition.
17. What are different types of constraints?
Same as Q12: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
18. What is difference between VARCHAR and NVARCHAR?
VARCHAR: ASCII, 1 byte per char.
NVARCHAR: Unicode, 2 bytes per char, supports multiple languages.
19. Similarity for CHAR and NCHAR?
CHAR: Fixed-length ASCII.
NCHAR: Fixed-length Unicode.
20. What are indexes and their types?
Used for faster retrieval.
Types:
- Clustered
- Non-clustered
- Unique
- Composite
- Full-text
21. What is an index? Explain its types.
Same as above. Indexes speed up queries by creating pointers to data.
22. List different types of relationships in SQL.
One-to-One
One-to-Many
Many-to-Many
23. Differentiate between UNION and UNION ALL.
UNION: Removes duplicates.
UNION ALL: Includes duplicates.
24. How many types of clauses in SQL?
Common clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, JOIN, ON.
25. What is the difference between UNION and UNION ALL in SQL?
Same as Q23.
26. What are various types of relationships in SQL?
Same as Q22.
27. Difference between Primary Key and Secondary Key?
Primary Key: Uniquely identifies rows.
Secondary Key: May not be unique, used for lookup.
Credits: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1000
❤8👍5🎉1
Here are some interview questions for both freshers and experienced applying for a data analyst #SQL
Analyst role:
#ForFreshers:
1. What is SQL, and why is it important in data analysis?
2. Explain the difference between a database and a table.
3. What are the basic SQL commands for data retrieval?
4. How do you retrieve all records from a table named "Employees"?
5. What is a primary key, and why is it important in a database?
6. What is a foreign key, and how is it used in SQL?
7. Describe the difference between SQL JOIN and SQL UNION.
8. How do you write a SQL query to find the second-highest salary in a table?
9. What is the purpose of the GROUP BY clause in SQL?
10. Can you explain the concept of normalization in SQL databases?
11. What are the common aggregate functions in SQL, and how are they used?
ForExperiencedCandidates:
1. Describe a scenario where you had to optimize a slow-running SQL query. How did you approach it?
2. Explain the differences between SQL Server, MySQL, and Oracle databases.
3. Can you describe the process of creating an index in a SQL database and its impact on query performance?
4. How do you handle data quality issues when performing data analysis with SQL?
5. What is a subquery, and when would you use it in SQL? Give an example of a complex SQL query you've written to extract specific insights from a database.
6. How do you handle NULL values in SQL, and what are the challenges associated with them?
7. Explain the ACID properties of a database and their importance.
8. What are stored procedures and triggers in SQL, and when would you use them?
9. Describe your experience with ETL (Extract, Transform, Load) processes using SQL.
10. Can you explain the concept of query optimization in SQL, and what techniques have you used for optimization?
Enjoy Learning 👍👍
Analyst role:
#ForFreshers:
1. What is SQL, and why is it important in data analysis?
2. Explain the difference between a database and a table.
3. What are the basic SQL commands for data retrieval?
4. How do you retrieve all records from a table named "Employees"?
5. What is a primary key, and why is it important in a database?
6. What is a foreign key, and how is it used in SQL?
7. Describe the difference between SQL JOIN and SQL UNION.
8. How do you write a SQL query to find the second-highest salary in a table?
9. What is the purpose of the GROUP BY clause in SQL?
10. Can you explain the concept of normalization in SQL databases?
11. What are the common aggregate functions in SQL, and how are they used?
ForExperiencedCandidates:
1. Describe a scenario where you had to optimize a slow-running SQL query. How did you approach it?
2. Explain the differences between SQL Server, MySQL, and Oracle databases.
3. Can you describe the process of creating an index in a SQL database and its impact on query performance?
4. How do you handle data quality issues when performing data analysis with SQL?
5. What is a subquery, and when would you use it in SQL? Give an example of a complex SQL query you've written to extract specific insights from a database.
6. How do you handle NULL values in SQL, and what are the challenges associated with them?
7. Explain the ACID properties of a database and their importance.
8. What are stored procedures and triggers in SQL, and when would you use them?
9. Describe your experience with ETL (Extract, Transform, Load) processes using SQL.
10. Can you explain the concept of query optimization in SQL, and what techniques have you used for optimization?
Enjoy Learning 👍👍
👍7❤1🎉1
𝐒𝐨𝐦𝐞 𝐁𝐞𝐬𝐭 𝐩𝐫𝐚𝐜𝐭𝐢𝐜𝐞𝐬 𝐭𝐨 𝐡𝐞𝐥𝐩 𝐲𝐨𝐮 𝐨𝐩𝐭𝐢𝐦𝐢𝐳𝐞 𝐲𝐨𝐮𝐫 𝐒𝐐𝐋 𝐪𝐮𝐞𝐫𝐢𝐞𝐬:
1. Simplify Joins
• Decompose complex joins into simpler, more manageable queries when possible.
• Index columns that are used as foreign keys in joins to enhance join performance.
2. Query Structure Optimization
• Apply WHERE clauses as early as possible to filter out rows before they are processed further.
• Utilize LIMIT or TOP clauses to restrict the number of rows returned, which can significantly reduce processing time.
3. Partition Large Tables
• Divide large tables into smaller, more manageable partitions.
• Ensure that each partition is properly indexed to maintain query performance.
4. Optimize SELECT Statements
• Limit the columns in your SELECT clause to only those you need. Avoid using SELECT * to prevent unnecessary data retrieval.
• Prefer using EXISTS over IN for subqueries to improve query performance.
5. Use Temporary Tables Wisely
• Temporary Tables: Use temporary tables to save intermediate results when you have a complex query. This helps break down a complicated query into simpler steps, making it easier to manage and faster to run.
6. Optimize Table Design
• Normalize your database schema to eliminate redundant data and improve consistency.
• Consider denormalization for read-heavy systems to reduce the number of joins needed.
7. Avoid Correlated Subqueries
• Replace correlated subqueries with joins or use derived tables to improve performance.
• Correlated subqueries can be very inefficient as they are executed multiple times.
8. Use Stored Procedures:
• Put complicated database tasks into stored procedures. These are pre-written sets of instructions saved in the database. They make your queries run faster because the database doesn’t have to figure out how to execute them each time
Like this post if you need more 👍❤️
Hope it helps :)
1. Simplify Joins
• Decompose complex joins into simpler, more manageable queries when possible.
• Index columns that are used as foreign keys in joins to enhance join performance.
2. Query Structure Optimization
• Apply WHERE clauses as early as possible to filter out rows before they are processed further.
• Utilize LIMIT or TOP clauses to restrict the number of rows returned, which can significantly reduce processing time.
3. Partition Large Tables
• Divide large tables into smaller, more manageable partitions.
• Ensure that each partition is properly indexed to maintain query performance.
4. Optimize SELECT Statements
• Limit the columns in your SELECT clause to only those you need. Avoid using SELECT * to prevent unnecessary data retrieval.
• Prefer using EXISTS over IN for subqueries to improve query performance.
5. Use Temporary Tables Wisely
• Temporary Tables: Use temporary tables to save intermediate results when you have a complex query. This helps break down a complicated query into simpler steps, making it easier to manage and faster to run.
6. Optimize Table Design
• Normalize your database schema to eliminate redundant data and improve consistency.
• Consider denormalization for read-heavy systems to reduce the number of joins needed.
7. Avoid Correlated Subqueries
• Replace correlated subqueries with joins or use derived tables to improve performance.
• Correlated subqueries can be very inefficient as they are executed multiple times.
8. Use Stored Procedures:
• Put complicated database tasks into stored procedures. These are pre-written sets of instructions saved in the database. They make your queries run faster because the database doesn’t have to figure out how to execute them each time
Like this post if you need more 👍❤️
Hope it helps :)
👍7❤3
SQL Interview Questions asked by Urban Company:-
Question 1: Monthly Revenue Trends by Category
Scenario: Analyze monthly revenue trends for each product category.
Table:
1. transactions (Transaction_id, Product_id, Amount_spent, Transaction_date),
2. products (Product_id, Category)
Challenge: Write a SQL query to calculate the total revenue for each category on a monthly basis and identify the top 3 categories with the highest revenue growth month-over-month.
Question 2: Customer Retention Analysis
Scenario: Determine the retention rate of customers.
Table:
1. customer_visits (Customer_id, Visit_date)
Challenge: Write a SQL query to calculate the retention rate of customers month-over-month for the past year, identifying the percentage of customers who return the following month.
Question 3: Product Affinity Analysis
Scenario: Identify products that are frequently bought together.
Table:
1. order_details (Order_id, Product_id, Quantity)
Challenge: Write a SQL query to find pairs of products that are frequently bought together. Include the count of how many times each pair appears in the same order and rank them by frequency.
Question 4: Customer Purchase Segmentation
Scenario: Segment customers based on their purchase behavior.
Table:
1. purchases (Customer_id, Product_id, Amount_spent, Purchase_date)
Challenge: Write a SQL query to segment customers into different groups based on their total spending and purchase frequency in the last year. Classify them into categories like 'High Spenders', 'Medium Spenders', and 'Low Spenders'.
Question 5: Anomaly Detection in Transactions
Scenario: Detect anomalies in transaction amounts.
Table:
1. transactions (Transaction_id, Customer_id, Amount_spent, Transaction_date)
Challenge: Write a SQL query to identify transactions that deviate significantly from the customer's average spending. Flag transactions that are more than three standard deviations away from the mean spending amount for each customer.
Question 1: Monthly Revenue Trends by Category
Scenario: Analyze monthly revenue trends for each product category.
Table:
1. transactions (Transaction_id, Product_id, Amount_spent, Transaction_date),
2. products (Product_id, Category)
Challenge: Write a SQL query to calculate the total revenue for each category on a monthly basis and identify the top 3 categories with the highest revenue growth month-over-month.
Question 2: Customer Retention Analysis
Scenario: Determine the retention rate of customers.
Table:
1. customer_visits (Customer_id, Visit_date)
Challenge: Write a SQL query to calculate the retention rate of customers month-over-month for the past year, identifying the percentage of customers who return the following month.
Question 3: Product Affinity Analysis
Scenario: Identify products that are frequently bought together.
Table:
1. order_details (Order_id, Product_id, Quantity)
Challenge: Write a SQL query to find pairs of products that are frequently bought together. Include the count of how many times each pair appears in the same order and rank them by frequency.
Question 4: Customer Purchase Segmentation
Scenario: Segment customers based on their purchase behavior.
Table:
1. purchases (Customer_id, Product_id, Amount_spent, Purchase_date)
Challenge: Write a SQL query to segment customers into different groups based on their total spending and purchase frequency in the last year. Classify them into categories like 'High Spenders', 'Medium Spenders', and 'Low Spenders'.
Question 5: Anomaly Detection in Transactions
Scenario: Detect anomalies in transaction amounts.
Table:
1. transactions (Transaction_id, Customer_id, Amount_spent, Transaction_date)
Challenge: Write a SQL query to identify transactions that deviate significantly from the customer's average spending. Flag transactions that are more than three standard deviations away from the mean spending amount for each customer.
❤3👍2
SQL Programming Resources
SQL Interview Questions asked by Urban Company:- Question 1: Monthly Revenue Trends by Category Scenario: Analyze monthly revenue trends for each product category. Table: 1. transactions (Transaction_id, Product_id, Amount_spent, Transaction_date), 2.…
You can find detailed answers on our WhatsApp channel: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
❤2👍2
Basics of SQL 👇👇
1. SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases.
2. SQL operates through simple, declarative statements. These statements are used to perform tasks such as querying data, updating data, inserting data, and deleting data from a database.
3. The basic SQL commands include SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP.
4. The SELECT statement is used to retrieve data from a database. It allows you to specify the columns you want to retrieve and filter the results using conditions.
5. The INSERT statement is used to add new records to a table in a database.
6. The UPDATE statement is used to modify existing records in a table.
7. The DELETE statement is used to remove records from a table.
8. The CREATE statement is used to create new tables, indexes, or views in a database.
9. The DROP statement is used to remove tables, indexes, or views from a database.
10. SQL also supports various operators such as AND, OR, NOT, LIKE, IN, BETWEEN, and ORDER BY for filtering and sorting data.
11. SQL also allows for the use of functions and aggregate functions like SUM, AVG, COUNT, MIN, and MAX to perform calculations on data.
12. SQL statements are case-insensitive but conventionally written in uppercase for readability.
13. SQL databases are relational databases that store data in tables with rows and columns. Tables can be related to each other through primary and foreign keys.
14. SQL databases use transactions to ensure data integrity and consistency. Transactions can be committed (saved) or rolled back (undone) based on the success of the operations.
15. SQL databases support indexing for faster data retrieval and performance optimization.
16. SQL databases can be queried using tools like MySQL, PostgreSQL, Oracle Database, SQL Server, SQLite, and others.
Like if you need more similar content
Hope it helps :)
1. SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases.
2. SQL operates through simple, declarative statements. These statements are used to perform tasks such as querying data, updating data, inserting data, and deleting data from a database.
3. The basic SQL commands include SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP.
4. The SELECT statement is used to retrieve data from a database. It allows you to specify the columns you want to retrieve and filter the results using conditions.
5. The INSERT statement is used to add new records to a table in a database.
6. The UPDATE statement is used to modify existing records in a table.
7. The DELETE statement is used to remove records from a table.
8. The CREATE statement is used to create new tables, indexes, or views in a database.
9. The DROP statement is used to remove tables, indexes, or views from a database.
10. SQL also supports various operators such as AND, OR, NOT, LIKE, IN, BETWEEN, and ORDER BY for filtering and sorting data.
11. SQL also allows for the use of functions and aggregate functions like SUM, AVG, COUNT, MIN, and MAX to perform calculations on data.
12. SQL statements are case-insensitive but conventionally written in uppercase for readability.
13. SQL databases are relational databases that store data in tables with rows and columns. Tables can be related to each other through primary and foreign keys.
14. SQL databases use transactions to ensure data integrity and consistency. Transactions can be committed (saved) or rolled back (undone) based on the success of the operations.
15. SQL databases support indexing for faster data retrieval and performance optimization.
16. SQL databases can be queried using tools like MySQL, PostgreSQL, Oracle Database, SQL Server, SQLite, and others.
Like if you need more similar content
Hope it helps :)
👍7🎉1
DATA ANALYST Interview Questions (0-3 yr) (SQL, Power BI)
👉 Power BI:
Q1: Explain step-by-step how you will create a sales dashboard from scratch.
Q2: Explain how you can optimize a slow Power BI report.
Q3: Explain Any 5 Chart Types and Their Uses in Representing Different Aspects of Data.
👉SQL:
Q1: Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() functions using example.
Q2 – Q4 use Table: employee (EmpID, ManagerID, JoinDate, Dept, Salary)
Q2: Find the nth highest salary from the Employee table.
Q3: You have an employee table with employee ID and manager ID. Find all employees under a specific manager, including their subordinates at any level.
Q4: Write a query to find the cumulative salary of employees department-wise, who have joined the company in the last 30 days.
Q5: Find the top 2 customers with the highest order amount for each product category, handling ties appropriately. Table: Customer (CustomerID, ProductCategory, OrderAmount)
👉Behavioral:
Q1: Why do you want to become a data analyst and why did you apply to this company?
Q2: Describe a time when you had to manage a difficult task with tight deadlines. How did you handle it?
I have curated best top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
👉 Power BI:
Q1: Explain step-by-step how you will create a sales dashboard from scratch.
Q2: Explain how you can optimize a slow Power BI report.
Q3: Explain Any 5 Chart Types and Their Uses in Representing Different Aspects of Data.
👉SQL:
Q1: Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() functions using example.
Q2 – Q4 use Table: employee (EmpID, ManagerID, JoinDate, Dept, Salary)
Q2: Find the nth highest salary from the Employee table.
Q3: You have an employee table with employee ID and manager ID. Find all employees under a specific manager, including their subordinates at any level.
Q4: Write a query to find the cumulative salary of employees department-wise, who have joined the company in the last 30 days.
Q5: Find the top 2 customers with the highest order amount for each product category, handling ties appropriately. Table: Customer (CustomerID, ProductCategory, OrderAmount)
👉Behavioral:
Q1: Why do you want to become a data analyst and why did you apply to this company?
Q2: Describe a time when you had to manage a difficult task with tight deadlines. How did you handle it?
I have curated best top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
👍5🎉1
Top 10 Advanced SQL Interview Questions and Answers
1. What is a Common Table Expression (CTE), and when would you use it?
A Common Table Expression (CTE) is a temporary result set that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement.
Example:
2. How do you optimize a query with a large dataset?
- Use proper indexes.
- Avoid SELECT *; only retrieve required columns.
- Break down complex queries using temporary tables or CTEs.
- Analyze query execution plans.
3. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
- RANK(): Skips ranking if there’s a tie (e.g., 1, 2, 2, 4).
- DENSE_RANK(): Does not skip ranks after a tie (e.g., 1, 2, 2, 3).
- ROW_NUMBER(): Assigns unique numbers sequentially, regardless of ties.
4. How do you find duplicate records in a table?
5. What is the difference between INNER JOIN and LEFT JOIN?
- INNER JOIN: Returns records that match in both tables.
- LEFT JOIN: Returns all records from the left table, and matching records from the right table (NULL if no match).
6. Explain window functions and provide an example.
Window functions operate on a set of rows related to the current row, without collapsing them into a single output.
Example:
7. What are the different types of indexes in SQL?
- Clustered Index: Reorders the data physically in the table.
- Non-Clustered Index: Creates a separate structure for data retrieval.
- Unique Index: Ensures no duplicate values in the column.
8. How do you handle NULL values in SQL?
- Use COALESCE() or ISNULL() to replace NULL values.
- Filter with IS NULL or IS NOT NULL in WHERE clauses.
Example:
9. What is the difference between DELETE and TRUNCATE?
- DELETE: Removes specific rows, can use WHERE clause, and logs individual row deletions.
- TRUNCATE: Removes all rows, faster, and resets table identity.
10. How do you use a CASE statement in SQL?
1. What is a Common Table Expression (CTE), and when would you use it?
A Common Table Expression (CTE) is a temporary result set that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement.
Example:
WITH SalesCTE AS (
SELECT SalespersonID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SalespersonID
)
SELECT * FROM SalesCTE WHERE TotalSales > 5000;
2. How do you optimize a query with a large dataset?
- Use proper indexes.
- Avoid SELECT *; only retrieve required columns.
- Break down complex queries using temporary tables or CTEs.
- Analyze query execution plans.
3. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
- RANK(): Skips ranking if there’s a tie (e.g., 1, 2, 2, 4).
- DENSE_RANK(): Does not skip ranks after a tie (e.g., 1, 2, 2, 3).
- ROW_NUMBER(): Assigns unique numbers sequentially, regardless of ties.
4. How do you find duplicate records in a table?
SELECT ColumnName, COUNT(*)
FROM TableName
GROUP BY ColumnName
HAVING COUNT(*) > 1;
5. What is the difference between INNER JOIN and LEFT JOIN?
- INNER JOIN: Returns records that match in both tables.
- LEFT JOIN: Returns all records from the left table, and matching records from the right table (NULL if no match).
6. Explain window functions and provide an example.
Window functions operate on a set of rows related to the current row, without collapsing them into a single output.
Example:
SELECT EmployeeID, Salary,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
FROM Employees;
7. What are the different types of indexes in SQL?
- Clustered Index: Reorders the data physically in the table.
- Non-Clustered Index: Creates a separate structure for data retrieval.
- Unique Index: Ensures no duplicate values in the column.
8. How do you handle NULL values in SQL?
- Use COALESCE() or ISNULL() to replace NULL values.
- Filter with IS NULL or IS NOT NULL in WHERE clauses.
Example:
SELECT COALESCE(PhoneNumber, 'N/A') AS ContactNumber FROM Customers;
9. What is the difference between DELETE and TRUNCATE?
- DELETE: Removes specific rows, can use WHERE clause, and logs individual row deletions.
- TRUNCATE: Removes all rows, faster, and resets table identity.
10. How do you use a CASE statement in SQL?
SELECT ProductName,
CASE
WHEN Quantity > 100 THEN 'High Stock'
WHEN Quantity BETWEEN 50 AND 100 THEN 'Medium Stock'
ELSE 'Low Stock'
END AS StockStatus
FROM Products;
👍5🎉2
🎯 Top 20 SQL Interview Questions You Must Know
SQL is one of the most in-demand skills for Data Analysts.
Here are 20 SQL interview questions that frequently appear in job interviews.
📌 Basic SQL Questions
1️⃣ What is the difference between INNER JOIN and LEFT JOIN?
2️⃣ How does GROUP BY work, and why do we use it?
3️⃣ What is the difference between HAVING and WHERE?
4️⃣ How do you remove duplicate rows from a table?
5️⃣ What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
📌 Intermediate SQL Questions
6️⃣ How do you find the second highest salary from an Employee table?
7️⃣ What is a Common Table Expression (CTE), and when should you use it?
8️⃣ How do you identify missing values in a dataset using SQL?
9️⃣ What is the difference between UNION and UNION ALL?
🔟 How do you calculate a running total in SQL?
📌 Advanced SQL Questions
1️⃣1️⃣ How does a self-join work? Give an example.
1️⃣2️⃣ What is a window function, and how is it different from GROUP BY?
1️⃣3️⃣ How do you detect and remove duplicate records in SQL?
1️⃣4️⃣ Explain the difference between EXISTS and IN.
1️⃣5️⃣ What is the purpose of COALESCE()?
📌 Real-World SQL Scenarios
1️⃣6️⃣ How do you optimize a slow SQL query?
1️⃣7️⃣ What is indexing in SQL, and how does it improve performance?
1️⃣8️⃣ Write an SQL query to find customers who have placed more than 3 orders.
1️⃣9️⃣ How do you calculate the percentage of total sales for each category?
2️⃣0️⃣ What is the use of CASE statements in SQL?
React ♥️ for more
SQL is one of the most in-demand skills for Data Analysts.
Here are 20 SQL interview questions that frequently appear in job interviews.
📌 Basic SQL Questions
1️⃣ What is the difference between INNER JOIN and LEFT JOIN?
2️⃣ How does GROUP BY work, and why do we use it?
3️⃣ What is the difference between HAVING and WHERE?
4️⃣ How do you remove duplicate rows from a table?
5️⃣ What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
📌 Intermediate SQL Questions
6️⃣ How do you find the second highest salary from an Employee table?
7️⃣ What is a Common Table Expression (CTE), and when should you use it?
8️⃣ How do you identify missing values in a dataset using SQL?
9️⃣ What is the difference between UNION and UNION ALL?
🔟 How do you calculate a running total in SQL?
📌 Advanced SQL Questions
1️⃣1️⃣ How does a self-join work? Give an example.
1️⃣2️⃣ What is a window function, and how is it different from GROUP BY?
1️⃣3️⃣ How do you detect and remove duplicate records in SQL?
1️⃣4️⃣ Explain the difference between EXISTS and IN.
1️⃣5️⃣ What is the purpose of COALESCE()?
📌 Real-World SQL Scenarios
1️⃣6️⃣ How do you optimize a slow SQL query?
1️⃣7️⃣ What is indexing in SQL, and how does it improve performance?
1️⃣8️⃣ Write an SQL query to find customers who have placed more than 3 orders.
1️⃣9️⃣ How do you calculate the percentage of total sales for each category?
2️⃣0️⃣ What is the use of CASE statements in SQL?
React ♥️ for more
❤5👍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. ☺️💪
👍6
Scenario based Interview Questions & Answers for Data Analyst
1. Scenario: You are working on a SQL database that stores customer information. The database has a table called "Orders" that contains order details. Your task is to write a SQL query to retrieve the total number of orders placed by each customer.
Question:
- Write a SQL query to find the total number of orders placed by each customer.
Expected Answer:
SELECT CustomerID, COUNT(*) AS TotalOrders
FROM Orders
GROUP BY CustomerID;
2. Scenario: You are working on a SQL database that stores employee information. The database has a table called "Employees" that contains employee details. Your task is to write a SQL query to retrieve the names of all employees who have been with the company for more than 5 years.
Question:
- Write a SQL query to find the names of employees who have been with the company for more than 5 years.
Expected Answer:
SELECT Name
FROM Employees
WHERE DATEDIFF(year, HireDate, GETDATE()) > 5;
Power BI Scenario-Based Questions
1. Scenario: You have been given a dataset in Power BI that contains sales data for a company. Your task is to create a report that shows the total sales by product category and region.
Expected Answer:
- Load the dataset into Power BI.
- Create relationships if necessary.
- Use the "Fields" pane to select the necessary fields (Product Category, Region, Sales).
- Drag these fields into the "Values" area of a new visualization (e.g., a table or bar chart).
- Use the "Filters" pane to filter data as needed.
- Format the visualization to enhance clarity and readability.
2. Scenario: You have been asked to create a Power BI dashboard that displays real-time stock prices for a set of companies. The stock prices are available through an API.
Expected Answer:
- Use Power BI Desktop to connect to the API.
- Go to "Get Data" > "Web" and enter the API URL.
- Configure the data refresh settings to ensure real-time updates (e.g., setting up a scheduled refresh or using DirectQuery if supported).
- Create visualizations using the imported data.
- Publish the report to the Power BI service and set up a data gateway if needed for continuous refresh.
3. Scenario: You have been given a Power BI report that contains multiple visualizations. The report is taking a long time to load and is impacting the performance of the application.
Expected Answer:
- Analyze the current performance using Performance Analyzer.
- Optimize data model by reducing the number of columns and rows, and removing unnecessary calculations.
- Use aggregated tables to pre-compute results.
- Simplify DAX calculations.
- Optimize visualizations by reducing the number of visuals per page and avoiding complex custom visuals.
- Ensure proper indexing on the data source.
Free SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like if you need more similar content
Hope it helps :)
1. Scenario: You are working on a SQL database that stores customer information. The database has a table called "Orders" that contains order details. Your task is to write a SQL query to retrieve the total number of orders placed by each customer.
Question:
- Write a SQL query to find the total number of orders placed by each customer.
Expected Answer:
SELECT CustomerID, COUNT(*) AS TotalOrders
FROM Orders
GROUP BY CustomerID;
2. Scenario: You are working on a SQL database that stores employee information. The database has a table called "Employees" that contains employee details. Your task is to write a SQL query to retrieve the names of all employees who have been with the company for more than 5 years.
Question:
- Write a SQL query to find the names of employees who have been with the company for more than 5 years.
Expected Answer:
SELECT Name
FROM Employees
WHERE DATEDIFF(year, HireDate, GETDATE()) > 5;
Power BI Scenario-Based Questions
1. Scenario: You have been given a dataset in Power BI that contains sales data for a company. Your task is to create a report that shows the total sales by product category and region.
Expected Answer:
- Load the dataset into Power BI.
- Create relationships if necessary.
- Use the "Fields" pane to select the necessary fields (Product Category, Region, Sales).
- Drag these fields into the "Values" area of a new visualization (e.g., a table or bar chart).
- Use the "Filters" pane to filter data as needed.
- Format the visualization to enhance clarity and readability.
2. Scenario: You have been asked to create a Power BI dashboard that displays real-time stock prices for a set of companies. The stock prices are available through an API.
Expected Answer:
- Use Power BI Desktop to connect to the API.
- Go to "Get Data" > "Web" and enter the API URL.
- Configure the data refresh settings to ensure real-time updates (e.g., setting up a scheduled refresh or using DirectQuery if supported).
- Create visualizations using the imported data.
- Publish the report to the Power BI service and set up a data gateway if needed for continuous refresh.
3. Scenario: You have been given a Power BI report that contains multiple visualizations. The report is taking a long time to load and is impacting the performance of the application.
Expected Answer:
- Analyze the current performance using Performance Analyzer.
- Optimize data model by reducing the number of columns and rows, and removing unnecessary calculations.
- Use aggregated tables to pre-compute results.
- Simplify DAX calculations.
- Optimize visualizations by reducing the number of visuals per page and avoiding complex custom visuals.
- Ensure proper indexing on the data source.
Free SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Like if you need more similar content
Hope it helps :)
❤2👍2😍1