SQL is an easy and quick-to-learn language, but it also can be difficult to become advanced at it. Regardless of which position you’re applying for - product analyst, data analyst, data scientist, or data engineer - your SQL knowledge will be tested thoroughly.
I’m a big SQL enthusiast myself. I use it today extensively, not only for data extraction, but also for formatting, casting, and analysis. Therefore, when interviewing candidates, I put a lot of emphasis on a candidate’s SQL knowledge and comfort level.
Today there are various SQL variations and flavors: MySQL, MSSQL or T-SQL, PostgreSQL, pl/SQL, CQL, SQLite. They all are similar but have syntax differences and depend on which application you use: Oracle, SQLServer, or open-source like Redshift, Cassandra, etc. For your interview, you will be tested either MSSQL or MySQL. Small or middle size companies usually use open source applications, so get familiarity with PostgreSQL or MySQL. Enterprises most likely will test MSSQL, as many of their applications are built on top SQLServer and Oracle. RDBMS.
You don’t need to worry about all those SQL variations, because the type of SQL challenges you get on your interview can be solved in any SQL in a similar way. The SQL foundation concepts stay the same.
Tested SQL skills for different levels:
Beginner: At the beginning level, I am primarily concerned that the candidate has an understanding of general SQL concepts like types of JOINS, WHERE, HAVING. Be prepared to explain the differences between LEFT and RIGHT JOIN, OUTER and INNER (a few expert-level candidates got stuck on this one), WHERE and HAVING. Helpful links:
Intermediate: At the intermediate level, I’m looking for the ability to not only extract data but get that data into the right format for analysis. Moreover, I like to see that the candidate is comfortable applying different math operations, like AVG, MIN, %, while maintaining appropriate SQL format. Be prepared to use a subquery, apply aggregations, to explain the difference between UNION and UNION ALL. Helpful links:
Best practices: https://365datascience.com/sql-best-practices/
Common interview questions: https://leetcode.com/problemset/database/
Math operations: https://mode.com/sql-tutorial/intro-to-intermediate-sql/
Advanced: Finally at the advanced level, I want to see a comfort level with data manipulations, feature engineering, and formatting, as well as a deep and thorough understanding of cost and time optimizations. Be prepared to explain the difference between DROP, DELETE, and TRUNCATE, along with INSERT and UPDATE, evaluate the pros and cons of each of these, apply SELF JOIN, and window functions. Helpful links:
Common table expressions - https://mjk.space/advanced-sql-cte/
Troubleshoot schema problem - http://sqlfiddle.com/
You can practice each SQL concept for different levels here.
Also, check this SQL hack about to join two tables that are not related by a foreign key relationship here.
To get more familiarity with SQLite, how to manipulate data and conduct data analysis in SQLite read here.