Search⌘ K
AI Features
Home
Courses
Deep Dive Into Data Science Interview

SQL

Explore key SQL concepts crucial for data science interviews including writing queries with GROUP BY, understanding DELETE vs TRUNCATE, and identifying primary vs unique keys. Learn how to work with joins, handle NULL values, and write efficient queries to analyze data. This lesson helps you build foundational SQL skills that are vital for solving common interview questions and working with large datasets in data science roles.

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, ...