EY SQL Interview Questions

EY stands for Ernst and Young, a global provider of professional services. The location of its headquarters is in London, England. EY’s primary goal, made possible by data and technology, is to improve the working environment. It facilitates the growth and expansion of clients. With more than 750 locations across more than 150 countries, EY employs more than 500,000 people. EY provides businesses with assurance, tax, advising, and consulting services.

 Learn Full Stack Development with expert mentors! Get Free Demo Here!

Introduction to EY

Ernst Young was the seventh-biggest privately held company in the US in 2019. EY is a great place to work since it provides you with chances to advance your career. A company that promotes work-life balance is EY. EY works with a range of technologies that support its service portfolio. Therefore, if one is seeking for a job in IT, this is an excellent location to work.

Among the “Big Four” accounting firms, EY is the world’s largest professional services network in terms of both revenue and workforce. Numerous technical positions in the fields of technology, tax, auditing, and consulting are available at EY. Technical profiles can be difficult to interview for, but you can improve your chances of success by being well-prepared.

EY Recruitment Process

1. Online Assessment:

The following three sections typically comprise EY’s online assessment:

• Aptitude: There are thirty questions in this section.
• Verbal and Logical: There are thirty questions in this part as well. It evaluates the applicant’s capacity for logical thought.
• Coding problems: Two moderately challenging problems. To be eligible for this level, you must possess a solid understanding of data structures and algorithms. Our website offers practice questions. Each section takes 30 minutes to complete in total.

2. Technical Interview:

• Technical interviews are extended to candidates who pass the online test. At EY, this round usually happens on the HIREVIEW platform. Usually, there are two or three interviewers in this round. This round’s questions are primarily project-related. You need to be well-versed in your project job.
• Additionally, you must be well-versed in computer basics, including DBMS, OS, OOPS, and networking. If you have experience, this round includes discusses your prior employment experiences.
• Your performance in the prior rounds determines how many technical interviews you are invited to attend. Typically, this process entails one technical interview for each candidate.

3. HR Interview:

The HR round usually comes following the technical interview round. The employer wants to determine if the applicant fits in with the company’s culture. The interview phase at EY HR is crucial, and candidates shouldn’t undervalue it. Being well-prepared for an HR interview is advised.

Why Join EY?

Choosing EY (Ernst & Young) as a SQL professional offers several compelling reasons:

1. Global Brand and Reputation

• EY is one of the “Big Four” accounting firms with a global presence, recognized for its excellence in audit, consulting, and advisory services. Working at EY adds immense credibility to your resume as a SQL professional.

2. Diverse Industry Exposure

• EY serves a wide range of industries, from finance and healthcare to technology and manufacturing. As a SQL professional, you get the opportunity to work on varied projects, helping different clients optimize data management and analytics solutions.

3. Challenging Projects

• EY handles complex data challenges for large clients. SQL professionals at EY are involved in designing and managing large-scale databases, implementing data migration, and optimizing performance in cloud and on-premises environments, giving you the chance to develop advanced SQL skills.

4. Innovation and Technology Focus

• EY heavily invests in technology and data analytics. SQL professionals working here are exposed to modern data platforms (e.g., Azure, AWS, Google Cloud) and data visualization tools (e.g., Power BI, Tableau), helping you stay up-to-date with cutting-edge technologies.

5. Data Analytics and Automation

• EY leverages SQL in conjunction with automation tools, AI, and machine learning models. As a SQL professional, you may be involved in projects that integrate SQL with Python, R, or other data science tools to deliver advanced analytics solutions.

6. Career Growth Opportunities

• EY offers structured career development programs, mentoring, and certifications. SQL professionals can grow into data engineering, analytics consulting, or leadership roles. They also promote internal learning through platforms like EYU (EY’s learning platform).

7. Collaborative Work Culture

• EY has a collaborative work environment with a focus on team-based projects. As a SQL professional, you’ll be working alongside data scientists, business analysts, and IT consultants, offering you the chance to broaden your skillset beyond SQL.

8. Global Mobility

• With EY’s extensive global network, SQL professionals have the opportunity to work across different regions or on international projects, gaining global exposure and expanding their professional network.

9. Competitive Compensation and Benefits

• EY offers competitive salaries and benefits packages, including health insurance, paid time off, retirement plans, and bonuses. They also provide flexible working arrangements and a strong focus on work-life balance.

10. Social Responsibility and Sustainability Focus

• EY is known for its commitment to corporate social responsibility and sustainability initiatives. By joining EY, you can be part of projects that contribute to positive social and environmental impact.

Which step are you stuck on?

EY SQL Interview Preparation Tips

To prepare for an EY SQL interview, it’s important to focus on both technical skills and problem-solving approaches. Here are some key tips to help you succeed:

1. Understand SQL Fundamentals

• Data Types: Be clear on different SQL data types (e.g., VARCHAR, INT, DATE, etc.).
• Basic Queries: Practice SELECT, INSERT, UPDATE, DELETE commands.
• Filtering and Sorting: Get comfortable using WHERE, ORDER BY, and GROUP BY clauses.

2. Master Joins and Subqueries

• Joins: Learn INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and SELF JOIN. Be prepared to explain when to use each.
• Subqueries: Understand nested queries, correlated subqueries, and their performance impacts.

3. Aggregate Functions and Grouping

• Know how to use functions like COUNT(), SUM(), AVG(), MIN(), and MAX().
• Understand how to group data using GROUP BY and HAVING clauses.

4. Window Functions

• Practice using window functions like ROW_NUMBER(), RANK(), DENSE_RANK(), and LEAD()/LAG() for advanced data analysis.

5. Indexes and Optimization

• Know how indexes work, including clustered and non-clustered indexes, and when to use them for query optimization.
• Understand the concept of query optimization and how to use EXPLAIN plans to analyze query performance.

6. Stored Procedures and Triggers

• Be familiar with creating and using stored procedures, triggers, and views.
• Know when to use them for efficiency and automation within databases.

7. Data Modeling and Normalization

• Understand database design concepts like normalization (1NF, 2NF, 3NF), denormalization, and how they affect performance and scalability.

8. Handling Complex Queries

• Prepare for complex problem-solving with multi-step queries, using CTEs (Common Table Expressions) and recursive queries.

9. ETL and Data Migration

• Be prepared for questions on data extraction, transformation, and loading (ETL) processes, which may be relevant in EY’s data-heavy projects.

10. Real-World Business Scenarios

• Expect SQL questions framed around real-world business scenarios. Practice interpreting business problems and converting them into SQL queries that provide insights.

Top EY SQL Interview Questions and Answers

Basic EY SQL Interview Question and Answers:

1. What is SQL?

Answer: SQL (Structured Query Language) is a standard language used to manage and manipulate relational databases. It is used to perform tasks like querying data, updating records, and managing database schemas.

2. What is a database?

Answer: A database is an organized collection of data that can be easily accessed, managed, and updated. It is structured to allow efficient retrieval, insertion, and deletion of data.

3. What are the different types of SQL statements?

Answer:

• DDL (Data Definition Language): Defines database schema (CREATE, ALTER, DROP).
• DML (Data Manipulation Language): Manages data within schema objects (SELECT, INSERT, UPDATE, DELETE).
• DCL (Data Control Language): Controls access to data (GRANT, REVOKE).
• TCL (Transaction Control Language): Manages transactions (COMMIT, ROLLBACK, SAVEPOINT).

4. What is a primary key?

Answer: A primary key is a column (or set of columns) that uniquely identifies each row in a table. It must contain unique values and cannot contain NULL.

5. What is a foreign key?

Answer: A foreign key is a column that creates a relationship between two tables by referencing the primary key of another table. It ensures referential integrity between the two tables.

6. What is normalization?

Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing a database into two or more tables and defining relationships between them.

7. What are the different types of joins?

Answer:

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table.
• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either table.

8. What is a unique key?

Answer: A unique key ensures that all values in a column are unique. Unlike a primary key, a table can have more than one unique key, and unique keys can accept NULL values.

9. What is an index?

Answer: An index is a database object that improves the speed of data retrieval operations on a table. It works like a book’s index, helping to locate data quickly, but it may slow down INSERT and UPDATE operations.

10. What is the difference between WHERE and HAVING clauses?

Answer:

• The WHERE clause is used to filter rows before the grouping of data.
• The HAVING clause is used to filter rows after the grouping of data using aggregate functions (e.g., COUNT, SUM).

11. What is a subquery?

Answer: A subquery is a query within another query. The inner query is executed first, and its result is used by the outer query. Subqueries can be used with SELECT, INSERT, UPDATE, or DELETE statements.

12. What is the difference between TRUNCATE, DELETE, and DROP?

Answer:

• DELETE: Removes specific rows from a table and can be rolled back.
• TRUNCATE: Removes all rows from a table but cannot be rolled back.
• DROP: Deletes the entire table from the database, including its structure.

13. What is a VIEW in SQL?

Answer: A view is a virtual table based on the result of an SQL query. It does not store data physically but provides a way to look at the data in a table or combination of tables.

14. What are constraints in SQL?

Answer: Constraints are rules applied to table columns to enforce data integrity. Common constraints include PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK.

15. What is a JOIN in SQL?

Answer: A JOIN is an SQL operation used to combine rows from two or more tables based on a related column between them.

16. What is a transaction in SQL?

Answer: A transaction is a sequence of SQL statements that are treated as a single unit of work. It follows the properties of ACID (Atomicity, Consistency, Isolation, Durability) to ensure data integrity.

17. What is the difference between CHAR and VARCHAR in SQL?

Answer:

• CHAR: A fixed-length character data type. It pads extra spaces to match the defined length.
• VARCHAR: A variable-length character data type. It stores only the characters that are provided.

18. What is the use of the GROUP BY clause?

Answer: The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used with aggregate functions like COUNT, SUM, AVG, etc.

19. What is the difference between UNION and UNION ALL?

Answer:

• UNION: Combines the result sets of two queries and removes duplicate records.
• UNION ALL: Combines the result sets of two queries and keeps all records, including duplicates.

20. What is a DEFAULT constraint?

Answer: The DEFAULT constraint is used to provide a default value to a column when no value is inserted into that column.

 Learn Full Stack Development with expert mentors! Get Free Demo Here!

Experienced EY SQL developer Interview Questions and Answers

1. What is normalization? Explain its different forms.

Answer: Normalization is the process of organizing data in a database to minimize redundancy and improve data integrity.

• 1NF: Ensures each column contains atomic (indivisible) values and there are no repeating groups.
• 2NF: Requires the table to be in 1NF and all non-primary attributes must be fully functionally dependent on the primary key.
• 3NF: Requires the table to be in 2NF and that no transitive dependency exists.
• BCNF: A stricter form of 3NF where every determinant is a candidate key.

2. What are the different types of SQL joins?

Answer: SQL joins are used to combine rows from two or more tables based on a related column.

• INNER JOIN: Returns rows that have matching values in both tables.
• LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
• RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
• FULL JOIN: Returns rows when there is a match in one of the tables.

3. Explain the concept of indexing and its types.

Answer: Indexing is a database optimization technique to speed up the retrieval of rows from a table.

• Clustered Index: Determines the physical order of data in the table and can be only one per table.
• Non-Clustered Index: Does not alter the physical order and can be multiple for a table.

4. What is a stored procedure? What are its benefits?

Answer: A stored procedure is a precompiled set of one or more SQL statements stored in the database. Benefits:

• Improves performance by reducing network traffic.
• Promotes code reuse.
• Enhances security by controlling access to the data.

5. What are transactions, and explain ACID properties?

Answer: A transaction is a unit of work that is executed as a single operation.

• Atomicity: All operations must succeed or none.
• Consistency: Data remains in a consistent state before and after the transaction.
• Isolation: Transactions are isolated from each other until they are completed.
• Durability: Once a transaction is committed, it remains so, even in case of system failures.

6. What are subqueries and correlated subqueries?

Answer: A subquery is a query nested within another SQL query.

• Non-correlated subquery: Executes independently of the outer query.
• Correlated subquery: Depends on the outer query for its execution.

7. How can you improve SQL query performance?

Answer:

• Use proper indexing.
• Avoid SELECT * (select only necessary columns).
• Use joins instead of subqueries when possible.
• Optimize the use of WHERE, GROUP BY, and ORDER BY clauses.
• Partition large tables.

8. What is the difference between DELETE, TRUNCATE, and DROP?

Answer:

• DELETE: Removes rows from a table based on a condition and can be rolled back.
• TRUNCATE: Removes all rows from a table without logging individual row deletions and cannot be rolled back.
• DROP: Removes the table or database entirely.

9. What is a view in SQL, and what are its uses?

Answer: A view is a virtual table created by a SQL query that displays data from one or more tables.
Uses:

• Simplifies complex queries.
• Provides security by restricting data access.
• Allows abstraction for users.

10. Explain the difference between UNION and UNION ALL.

Answer:

• UNION: Combines the result sets of two or more SELECT queries and removes duplicates.
• UNION ALL: Combines the result sets of two or more SELECT queries without removing duplicates.

11. What is the purpose of GROUP BY in SQL?

Answer: GROUP BY is used to arrange identical data into groups. It is commonly used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN.

12. How does a LEFT JOIN differ from a FULL JOIN?

Answer:

• LEFT JOIN: Returns all records from the left table and the matched records from the right table.
• FULL JOIN: Returns all records when there is a match in either left or right table.

13. What is a primary key and a foreign key?

Answer:

• Primary Key: A column or set of columns that uniquely identifies each row in a table.
• Foreign Key: A column or set of columns in one table that refers to the primary key in another table, creating a relationship between the two tables.

14. What are SQL triggers?

Answer: Triggers are special types of stored procedures that automatically execute when certain events occur in the database (e.g., INSERT, UPDATE, DELETE).

15. How do you handle SQL exceptions in stored procedures?

Answer: SQL exceptions can be handled using TRY...CATCH blocks in SQL Server. This allows for the capturing of errors and rolling back transactions when necessary.

16. What is the difference between a cross join and an inner join?

Answer:

• Cross Join: Produces a Cartesian product of all rows from both tables, resulting in all possible combinations.
• Inner Join: Returns only the rows where there is a match between both tables.

17. What is a cursor in SQL, and when would you use it?

Answer: A cursor is a database object that retrieves row-by-row data from a result set. It is typically used in scenarios where row-by-row processing is needed (e.g., when performing iterative logic on each row).

18. How would you write a query to find the second highest salary?

Answer:

19. What is the difference between OLTP and OLAP?

Answer:

• OLTP (Online Transaction Processing): Focuses on managing transaction-based applications like order entry and financial transactions.
• OLAP (Online Analytical Processing): Focuses on complex queries and data analysis to support business decision-making.

20. Explain the use of the HAVING clause.

Answer: The HAVING clause is used to filter groups after the GROUP BY operation, often in conjunction with aggregate functions. Unlike the WHERE clause, which filters rows before grouping, HAVING filters rows after the groups have been formed.

 Learn Full Stack Development with expert mentors! Get Free Demo Here!

EY Tehnical SQL Interview Questions and Answers

1. What is the difference between WHERE and HAVING clause?

Answer:

• WHERE is used to filter rows before grouping.
• HAVING filters data after the grouping operation is applied (usually with GROUP BY).

2. Explain the concept of JOIN and its types.

Answer:

• A JOIN is used to combine rows from two or more tables based on a related column.
• Types include:
• INNER JOIN: Returns rows with matching values in both tables.
• LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table. If no match, returns NULL.
• RIGHT JOIN: Similar to LEFT JOIN but starts with the right table.
• FULL OUTER JOIN: Returns all rows when there is a match in one of the tables.
• CROSS JOIN: Returns the Cartesian product of both tables.

3. What is a subquery and where can you use it?

Answer:

• A subquery is a query nested inside another query.
• It can be used in SELECT, FROM, WHERE, or HAVING clauses.

4. What is a correlated subquery?

Answer: A correlated subquery references columns from the outer query. The subquery is executed once for every row processed by the outer query.

5. How can you optimize a query for performance?

Answer:

• Use indexing for frequently used columns.
• Avoid SELECT *, specify the needed columns.
• Use EXISTS instead of IN where possible.
• Avoid using functions on indexed columns.
• Minimize subqueries and derived tables.

6. What is the difference between UNION and UNION ALL?

Answer:

• UNION removes duplicate rows.
• UNION ALL includes all rows, including duplicates, which makes it faster.

7. What is an INDEX? What are the types of indexing?

Answer: An INDEX is used to speed up retrieval of rows from a table.

Types include:

• Clustered Index: Sorts and stores data rows in the table based on key values.
• Non-clustered Index: Does not alter the physical order of rows but creates a separate object within the table that points to the original table rows.

8. What is a CROSS APPLY and OUTER APPLY?

Answer:

• CROSS APPLY works like an INNER JOIN for table-valued functions, returning rows only when the function returns a result.
• OUTER APPLY works like a LEFT JOIN, returning all rows from the left table, even when the function returns NULL.

9. Explain the difference between DELETE and TRUNCATE.

Answer:

• DELETE: Removes rows based on a condition; can be rolled back and can have a WHERE clause.
• TRUNCATE: Removes all rows without logging individual row deletions; faster but cannot be rolled back for specific rows.

10. What is normalization? Explain its different forms.

Answer: Normalization is a process to minimize redundancy and dependency by organizing fields and table relationships.

Forms:

• 1NF: No repeating groups, atomic values.
• 2NF: 1NF + No partial dependencies.
• 3NF: 2NF + No transitive dependencies.
• BCNF: Every determinant is a candidate key.

11. What is a transaction in SQL?

Answer: A transaction is a unit of work performed against the database. It is atomic, meaning either all operations within the transaction succeed, or none of them do.

Properties: ACID (Atomicity, Consistency, Isolation, Durability).

12. What is a stored procedure, and why use it?

Answer: A stored procedure is a precompiled group of one or more SQL statements stored in the database.

Benefits:

• Reduces network traffic.
• Enhances security.
• Can be reused and maintained easily.
• Optimized performance since compiled and cached.

13. What is a view in SQL, and how is it different from a table?

Answer:

• A view is a virtual table based on the result of an SQL query.
• Unlike a physical table, a view does not store data itself but fetches it from underlying tables.

14. Explain RANK(), ROW_NUMBER(), and DENSE_RANK() in SQL.

Answer:

• ROW_NUMBER(): Gives a unique sequential number starting from 1 for each row.
• RANK(): Provides ranking but skips numbers when there are ties.
• DENSE_RANK(): Provides ranking without gaps for ties.

15. What is a CTE (Common Table Expression)?

Answer:

• A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
• It improves readability and organizes complex queries.

16. How do you handle NULL values in SQL?

Answer:

• Use IS NULL or IS NOT NULL to check for nulls.
• Functions like COALESCE() or IFNULL() can replace null values with a default value.

17. What is the purpose of the GROUP BY clause?

Answer:  GROUP BY groups rows that have the same values into summary rows, often used with aggregate functions like COUNT(), SUM(), MAX(), etc.

18. Explain the difference between EXISTS and IN.

Answer:

• EXISTS: Tests for the existence of rows in a subquery. It is generally faster when dealing with large datasets.
• IN: Checks if a value matches any value in a list or subquery. Better for smaller datasets.

19. What is a trigger in SQL?

Answer: A trigger is a set of actions automatically executed when a specified event (like INSERT, UPDATE, or DELETE) occurs on a table.

20. How do you handle deadlocks in SQL?

Answer:

• Deadlocks can be handled by:
• Proper indexing.
• Using SET TRANSACTION ISOLATION LEVEL to reduce lock contention.
• Breaking large transactions into smaller ones.
• Implementing a retry mechanism with backoff for failed transactions.

 Learn Full Stack Development with expert mentors! Get Free Demo Here!