Deep Dive Into Data Science Interview/

...

/

SQL

SQL

The SQL questions and answers in this lesson will help you understand the types of SQL questions you can expect in data science interviews.

We'll cover the following...

What is wrong with the query below?

MySQL
SELECT UserId, AVG(Total) AS AvgOrderTotal
FROM Invoices
HAVING COUNT(OrderId)>=1

This query will get the average order amount by UserId for the customers with at least 1 order. So, we need to use the GROUP BY function to group the customers by UserId. Therefore, the correct query would be:

MySQL
SELECT UserId, AVG(Total) AS AvgOrderTotal
FROM Invoices
GROUP BY UserId
HAVING COUNT(OrderId)>=1

Consider the below table. Write a query that retrieves all employees not recruited by any recruiter.

Employee

Id

Name

RecruitedBy

1

Ross Taylor

NULL

2

Andy Smith

1

3

Scarlett Berry

NULL

4

Evelyn Depp

3

5

John Lee

3

6

James Dean

NULL

The query that retrieves all employees that are not recruited by any recruiter is:

MySQL
SELECT Employee.Name FROM Employee
JOIN Recruiter ON Employee.RecruitedBY = Recruiter.Id
WHERE RecruitedBy = NULL

What is the difference between DELETE and TRUNCATE?

The DELETE command can be used to remove rows from the table and uses the WHERE clause. We can also perform COMMIT and ROLLBACK after a DELETE command. On the other hand, the TRUNCATE command removes all rows from the table, ...