Why Choose JLL? JLL Python Interview Questions

JLL Python Test Interview

JLL is a real estate company and soon it will be dealing with new data science tools. So, one of my friend attended the interview of JLL for Python Data scientist. I am discussing here all the Python Interview questions here. But first of all, lets know why JLL.

Want to know: Why Python is the Best Programming Language for Data Science and Machine Learning?

Why Choose JLL?

JLL(Jones Lang LaSalle) is an American professional services and investment management company specializing in real estate. It values the diversity of its employees and provide them good opportunity growth. Soon, it is targeting to become one customer data science company. So, why not join it 🙂

JLL Python Interview Questions

JLL Python Interview Questions and Interview Process

So, there are total four to five rounds if you are going for JLL Python interview. The very first round will be a telephonic one wherein you will be asked about your current company and job role. The interviewer was very supportive in my friends’ case.

The interviewer will explain about the job role and what a JLL python data scientist does in their company. The interviewer will ask simple managerial type questions and you’ll have to provide him/her your scale in a particular skills.

Give Correct ratings for your skills, Interviewer will make a note of it for future rounds.

Further questions will be like what is your notice period and and further date will be intimated to you if the interview finds you a right candidate.

JLL Python Interview Questions and Answers

There will a technical test based on SQL and Python. You”ll find the test is simple if you know your skills :p. The interviewer will also ask you to write on the test how much time did you take to complete the test. It took my friend around 15 mins to complete the SQL test and 25 min to complete the JLL Python Interview questions.

JLL Python Technical Test Question and Answers

The python test questions were based on basic understanding of Python language. The questions include following things:

  1. Python Lists and Subsetting Lists
  2. Lembda function in Python
  3. Self in Python
  4. Truncating in Python
  5. Sum of lists
  6. Sum of arrays
  7. Concatenations
  8. Data type operations
  9. Local variable vs dynamic variance concept

Python test is an MCQ level test with questions are around 25-30 wherein either you have to calculate the output of the code or write the answer on your own. So, strengthen your skills.

JLL SQL Test Questions and Answers

The JLL SQL test was very easy for my friend. According to him, the questions were based on the following things:

  1. Select queries
  2. SQL Joins
  3. How to create views
  4. Selecting a particular output using where clause
  5. Inner queries
  6. Inserting values into table
  7. Trimming values
  8. Round function of SQL

Find out my next post on further JLL Interview rounds, if you want to grab a job in JLL.

Dunnhumby Python Analyst Placement Paper

Hi Friends, today I am sharing with you the Python Test pattern of Dunnhumby. Go through the details to know about Dunnhumby Python Analyst Placement Paper pattern, scoring and further interview process. Also, I would share with you all the details on which you need to focus to get selected as a Dunnhumby Analyst. One of my friend appeared for the Analyst profile for Dunnhumby, Gurgaon location. The interview process started early at 9:00 AM.

Dunnhumby Python

Why Dunnhumby??

Dunnhumby is a customer science company and values the candidates that understand the value that the company adds to its customers. Getting a job at Dunnhumby is like being in your good luck. So, there were many candidates that were either going to appear for an interview on R or Python. My friend appeared for a test on Python. So, Here is the structure of the Python test:

Dunnhumby Placement Paper Pattern

The Dunnhumby Python test contains a total of 15 questions. Out of these 15 questions, 4 were objective questions while 11 of these questions were coding questions where you are supposed to write the answers. Most of these questions were of different scores (2 marks to 5 marks). The questions were typically based on the following things :

Dunnhumby Placement Paper Pattern (Analyst) – Python

  1. Understanding of Python lists
  2. Understanding of operators and float values
  3. For loop applied on lists
  4. Reading the CSV files (few tables were provided and you need to write code to store these files as data frames)
  5. Write the code to change the data type of a column into some other
  6. Write the code to shuffle the list values in a significant way
  7. Sort the table values based on a specific column (ascending or descending order)
  8. Select a particular column based on column joins from two tables
  9. Select column values and calculate min, max, average, count, sum
  10. Re-sampling the data of tables

Dunnhumby Python Objective Test Paper Scoring

In the python technical objective questions you have to choose the output of the applied functions, operators. For this, one should have proper understanding of lists and loops and object referencing. Each objective question contains multiple sub-questions with different score weightage.

The second round will be a case study round and subsequent questions based on it. First of all, do review the already available case studies present on the official website of Dunnhumby. This will help you a lot in forming the logic and giving you an idea of the work Dunnhumby is doing for its clients. As aresult, you will be able to answer the questions asked during this case study interview. Also, wear a smile on your face since nobody likes sad faces :p

That’s all my friends. Best of luck for your interview! 🙂

Please share your interview stories in the comment section. Or you can also share some other company interview experience to me on my contact id.

Thanks

Date Time Functions in SQL and the Use of Date Time Functions

Date Time functions in SQL

There are various Date time functions available in SQL which allow us to create trigger based on it. Date time function allow us to check whether an input string is a date or not. Also, we can check what’s the day or week number on a specific date using date time function. To learn about SQL basics and Stored Procedures function, follow my blog.

Date Time

Let’s check various date time function available with us:

  1. IsDate

The isDate function is used to check whether the input string is a date or not.

Command: Select isDate(“2012-05-30”), it will return 1

If the input string is not a date, the IsDate date time function will return zero.

The isDate function works well on date, time and datetime datatype but not with datetime2 (timestamp with nanoseconds)

2. Day()

This date time function returns the “day number” of the specified input date string.

Command: Select Day(“2012-02-30 02:35:35”) – Returns 30

3. Month()

This sql date time function returns the “month number” of the specified input date string.

Command: Select Month(“2012-02-30 02:35:35”) – Returns 2

4. Day()

This sql date time function returns the “day number” of the specified input date string.

Command: Select Day(“2012-02-30 02:35:35”) – Returns 30

5. DateName()

The DateName() date time function returns a string value while other date time functions return integer value.

There are various attributes that can be used with the DateName() function. let’s me share some examples of it here:

  1. DateTime(Day, “Date value”|”DateTime Value”) – This function returns the day value (exception it is integer)

CommandSelect DateTime(Day, “2013-05-23”) – the output will be 2

2. DateTime(WeekDay, “Date value”|”DateTime Value” – This function returns the week day value

CommandSelect DateTime(WeekDay, “2013-05-23”) – the output will be Saturday.

3. DateTime(Month, “Date value”|”DateTime Value”)  – It returns the month value as a string

CommandSelect DateTime(Month, “2013-05-23”) – the output will be May.

6. Datepart()

The DatePart() function works similar way as DateName function to find out the weekDay. the only difference between DatePart() and DateName() function is Datepart() returns an integer.

e.g. Select DatePart(WeekDay, “2013-05-25”) will return 2 if it is Tuesday

To learn about SQL functions like: Ceiling, Floor, Abs, Round, Trunc, Power, refer the following article which has detailed information: Mathematical Functions in SQL: ABS, Round, Trunc, Power Function

7. DateAdd()

This sql function will add day, month or year according to the specified value.

Command: Select DateAdd(Day|Month|Year, integer value of addition, Date Value)

e.g. Select DateAdd(Day, 10, “2013-05-20”) – This will output “2013-05-30”

Date Diff

8. DateDiff()

This sql function will add day, month or year according to the specified value.

Command: Select DateDiff(Day|Month|Year, Date value 1,Date value 2)

e.g. Select DateAdd(Month, “2013-05-20″,”2013-01-20”) – This will output 4

Cast and Convert Functions in SQL

Cast and convert functions allow you to convert one data type into another.

Syntax of Cast function:

Select cast(Column_name as Data type)

e.g. Select cast(date as nvarchar)

Syntax of Convert function:

Convert function lets you choose style parameter for string values only. The style feature is not available in cast function. By styling I mean a user defined format in which the output is required.

e.g. Select convert(data type, column_name, style)

There are some specific style codes like 103 means : dd/mm/yyyy. Thus the following example:

Select convert(nvarchar, DateOfBirth, 103)

Stored Procedure and String Functions in SQL

Hola friends, let’s understand what are stored procedure and the benefits of using it. I hope you have learnt some basics of SQL before this. Learn basics of SQL in my previous post. Also, if you want to learn about Mathematical Functions in SQL: ABS, Round, Trunc, Power Function

What is a Stored Procedure?

Stored procedures are a set of instructions that need to be instructed again and again. In SQL we can save this frequent instructions as a procedure and call them by just their name.

Stored Procedures

How to create Stored Procedures?

To create stored procedures, simply use the Create Proc/Procedure Procedure_name command.

We can also pass parameter in a stored procedure. The parameters can be of two type:

1. Input (Used to take input)

2. Output (used to print output) – The output keyword must be specified as output in the description.

One example of the stored procedure is shown in the below image:

Stored-Procedure1

Running the Stored Procedures:

The command used to execute a stored procedure is: EXEC|Execute Procedure_name

e.g. Exec HumanResouces.uspFindEmployee “123” (Here 123 is the value for the @BusinessEntityID input parameter.

Stored Procedure with Output Parameter

An example of output parameter is:

Create Proc spGetEmployeeCount

@LastName int

@EmployeeCount Output

As

Begin

Select @EmployeeCount =Count(*) from emp where LastName = @LastName

End

Executing Stored Procedure with Output Parameter:

To execute the stored procedures with output parameters, it is very important to declare the output variable first. To declare the output variable, use the following command:

Declare @OutputVariableName Datatype

Exec Storedprocedurename Input parameter, Output Parameter Out|Output

Print@OutputVariableName

e.g.

Declare @TotalCount Int

Exec spGetEmployeeCount @LastName = “Male”, @TotalCount Out

Print@TotalCount

Benefits of Stored procedures:

  1. The execution plan can be reused

When a normal statement is executed, the path like first few columns are selected, then refined through where clause and then ordered. This is called a kind of execution plan. So, we can save time through stored procedures.

e.g. Select name, gender from Emp

when id IN(12, 14, 15)

order by name

A normal statement if executed with different parameters, use different execution path. However Stored procedures uses the same execution path even with different variable values.

2. Reduced Network Traffic

Since Stored procedures allows code re-usability, the sql instructions takes less time and space while utilizing network bandwidth. Thus network traffic is reduced.

3. Easy Maintainability using Stored Procedures

Maintenance becomes easy. A change in stored procedure is easy rather than finding similar statements at various places and then modifying the code.

4. Code Re-usability

The instruction that will be required again and again need not to be typed again. We just have to create a stored procedure with that statement and this will help us in reusing the code in less time and space.

5. Better Security

The database can be huge and we don’t want everyone to give access to every user in the network. So, build procedures on the specific table to which the user wants access. This way we will be able to provide better security.

6. Avoid SQL Injection Attack

Stored procedures also helps in avoiding SQL injection attacks. To know more about SQL injection attacks, go to the link.

Learn basics of SQL here

String Functions in SQL

  1. Left function:

This string function in sql returns integer values from the left.

command: select left(string, integer count)

e.g. Select left(“abcd”, 3) – Result will be “abc”

2. Right Function

This string function in sql returns integer values from the right of a string/column value.

command: select left(string/column, integer count)

e.g. Select left(“abcd”, 3) – Result will be “bcd”

3. CharIndex

This sql function returns the index of the character value.

command: Select charindex(“a”, “abcd”) – answer would be 1

4. Len

This function the total length of a string or column value of string type.

command e.g. : Select len(“abcd”) – answer would be 4

Note: This sql string function will not count the blank values at the end of a string.

5. Substr

This sql function is used to select a substring value from a string.

Command: select substr(“string value”|column name, position to start, length of fetch character

e.g. Select substr(“abcd”, 1, 3) – answer would be “abc”

We can also choose negative indexing here. A -1 indicates from the right.

6. Replicate

Replicate function replicates a specific string to the specified number of times.

command: Select replicate(“string”, number of times to replicate)

e.g. Select lastname + replicate(“*”,5)

This command will repeat the * five times in lastname. Let’s say the lastname is “John” then the output is: “John*****”

6. Space

This sql function will insert space to the specified number of times between column values.

command: Select Lastname + Space(5) +First name – This command will introduce a 5 character space between first name column value and last name.

7. PatIndex (or Pattern Index)

PatIndex works the same way as charindex by telling the first occurrence. However, PatIndex allows you to use wildcard. You can’t use wildcard with CharIndex.

e.g. Select PatIndex(“aaab”, “abcaaababc”) – The answer would be 4

If it do not find any matched string, the sql string function returns zero.

8. Replace

This string function replaces a string into another one.

Command: Select(String, “value to replace”, replacement value”)

e.g. Select(“abcd.com”, “.com”, “.net”) – The result will be “abcd.net”

9. LTRIM

This string function is used to trim leading blanks from the beginning.

Command e.g. : Select LTRIM(LastName)

10. RTRIM:

This sql function trims the trailing blank values.

e.g. Last Name : “abcd  ”

Select RTRIM(LastName) – the result will be “abcd”

11. ASCII:

This SQL function returns the ASCII code of the string value.

Command: Select ASCII(string value)

e.g. Select ASCII(A) – The answer would be 65

12. CHAR

This SQL function returns the character value from the integer ASCII code.

Command: Select CHAR(Integer value)

e.g. Select CHAR(65) – The answer would be A

13. STUFF

This sql function replaces a specific string value at the specified position by replacing the characters up to length value. It is kind of masking.

Command: Select STUFF(Column, Starting position, length, String to be used as replacement)

e.g. Select STUFF(LastName, 1, 3, “**”)

If the last name is “JohnMarcel”, the output of stuff function will be “***nMarcel”

Post Views: 2,207

User Defined Functions in SQL : Scalar, Inline Table-Valued, Multi Statement

Hola Friends! Today we will learn about various user defined functions present in SQL. So far, we have studied lots of system functions : IsDate, Rand(), round() etc. To gather more information about these system functions, please refer my post Mathematical functions in SQL and Date time functions in SQL.

SQL provides us a feature of programmability wherein it provides system functions as well as lets user create functions of three types:

  1. Scalar functions
  2. InLine Table Valued functions
  3. Multi Statement table valued functions

Each type of sql function is of different use and one should have a proper understanding of all these function types.

If you are new to SQL, please check SQL basics and SQL mathematical

 functions.