10+ AIESEC Interview Questions and Answers

Window functions are used in SQL to perform calculations across a set of table rows related to the current row.

• Some common window functions include ROW_NUMBER, RANK, DENSE_RANK, and NTILE.

• They are used to calculate running totals, moving averages, and cumulative sums.

• Window functions require an OVER clause to define the window of rows that the function will operate on.

There are approximately 1,000 traffic lights in New Delhi.

• New Delhi is a large metropolitan city with heavy traffic, so there are likely many traffic lights.

• The number of traffic lights can vary based on the size of the city and the traffic flow.

• An estimate of around 1,000 traffic lights is reasonable for a city like New Delhi.

Managing conflicts in a team involves effective communication, active listening, finding common ground, and seeking a win-win solution.

• Encourage open communication to address the issue

• Listen to all parties involved to understand their perspectives

• Identify common goals and areas of agreement

• Work together to find a mutually beneficial solution

• Seek mediation or involve a neutral third party if necessary

To find the 3rd highest score in each subject

• Sort the scores in each subject in descending order

• Get the 3rd element in the sorted array for each subject

• Repeat for each subject

A stored procedure logic to insert or update data in a table.

• Use MERGE statement to perform upsert operation in SQL.

• Check if the record exists in the table based on a unique key.

• If record exists, update it. If not, insert a new record.

Use SELECT INTO statement to copy contents of a table in SQL.

• Use SELECT INTO statement to copy contents of a table into a new table.

• Specify the columns you want to copy in the SELECT statement.

• Ensure the new table has the same structure as the original table.

I would estimate there are around 20 urinals in the ZS office.

• Consider the size of the office building and the typical ratio of urinals to toilets in commercial spaces.

• Take into account the number of employees and visitors who would use the restroom facilities.

• If possible, try to gather information on the floor plan or layout of the office to make a more accurate estimate.

Turncoat refers to a traitor or someone who switches sides, while delete means to remove or erase something.

• Turncoat is a person who changes allegiance or loyalty, often seen as a traitor.

• Delete is a command used to remove or erase data or information.

• Example: A turncoat politician switches parties to gain personal benefits. Deleting a file from a computer removes it permanently.

List, tuple, and dictionary are data structures in Python.

• List: ordered, mutable, allows duplicate elements (e.g. [1, 2, 3])

• Tuple: ordered, immutable, allows duplicate elements (e.g. (1, 2, 3))

• Dictionary: unordered, mutable, key-value pairs (e.g. {'key1': 'value1', 'key2': 'value2'})

Different types of SQL joins used to combine data from two or more 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.

• Outer join: Returns all records when there is a match in either left or right table.

• Inner join: Returns records that have matching values in both tables.

To copy a table in SQL, you can use the CREATE TABLE AS SELECT statement.

• Use the CREATE TABLE AS SELECT statement to copy the structure and data of a table.

• Specify the new table name after CREATE TABLE and the existing table name after SELECT.

• Example: CREATE TABLE new_table AS SELECT * FROM existing_table;

Different types of joins in SQL include inner join, left join, right join, and full outer join.

• Inner join: Returns rows when there is a match in both tables

• Left join: Returns all rows from the left table and the matched rows from the right table

• Right join: Returns all rows from the right table and the matched rows from the left table

• Full outer join: Returns rows when there is a match in either table

Dense rank assigns consecutive integers to rows with the same value, while rank leaves gaps in the ranking sequence.

• Dense rank assigns the same rank to rows with the same value, with no gaps in the ranking sequence.

• Rank leaves gaps in the ranking sequence for rows with the same value.

• ROW_NUMBER() assigns a unique sequential integer to each row in the result set.

Joins in SQL are used to combine rows from two or more tables based on a related column between them.

• Joins are used to retrieve data from multiple tables based on a related column

• Types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column