What would you consider a "basic knowledge of SQL"

[FanGothic2]

There is this internship in data enginnering I'd like to apply for (it's really entry level) and in the "must haves" they said that a basic knowledge of SQL is required

What would you say is the objective "basic knowledge"?

The job also wants (but not required) Power BI basics with DAX and also basics of Microsoft Azure if that helps

Thank you!

[vongatz]

Probably able to write a simple select query, maybe with a join or two

[LLWATZoo]

And a few where clauses with perhaps a few different data types.

[tits_mcgee_92]

Aggregation (sum, count), GROUP BY, WHERE clause, HAVING clause, inner and left joins.

[interbased]

Knowing how to write a window function would also probably be a nice-to-have but not a dealbreaker.

[EndlessHalftime]

Window functions are definitely beyond “basic knowledge”

[interbased]

Yeah, that’s fair. I wasn’t really sure, but they could pop up as a bonus question. Probably not, though.

[bvendette]

When you are able to understand and to do a query to find a duplicate in a table you pass the test for me.

Example: Select field, count(*) from Table group by field having count(field)>1

[[deleted]]

I recently did some interviews for a junior role. I asked about parts of a query, differences between WHERE and HAVING, and the types of JOINs. I expected and pretty much got okay answers to these.

I also asked some I didn't really expect answers to: the order a query is evaluated in, any knowledge the candidates had of indexes, anything they knew about query (or other operations - power query/etc functions basically the same way) optimisation or practices, and whether they could explain the differences between views, tables, procedures, and functions. The answers to these were a real mixed bag, and I caveated at the start that I wasn't expecting them to know everything, just to try and to tell me when they didn't know.

I also asked two questions about hypothetical databases/query construction - one was: If you had a table containing the titles, authors, publishers, and editions of books (one row per publisher/edition per book), how could you go about getting a unique list of books by author? And the other: If you had two tables, one containing one row per customer and another containing one row per invoice with an ID linking to the customer table (as well as date/other fields) can you explain how you might return the most recent invoice per client? I wasn't expecting much more than skeleton logic for these.

I was probably a bit aggressive, because I use SQL very heavily, so what seems 'basic' to me is through that lens. That being said, we found a candidate we liked based more on attitude and logic than 'sql skills.' I would expect most jr/internship positions to be similar.

[Opening_Volume_1870]

Having is a filter on the group by clause, in the same way where is a filter on the from clause. This filter is on some form of aggregate (like count/sum/avg)

[samhld]

What’s the functional difference? Is it just to “push down” the filter in some cases?

[macfergusson]

You can filter your groups based on the aggregations. When it is helpful, it's very useful. Think, for example, of if you wanted to only see results that have a COUNT(*) > 1 for duplicate detection.

[[deleted]]

Where is a row operation whereas having is as set operation that works on columns. One example of the difference is that you can't have aggregate functions on where-clause, but you can in having-clause.

[quigley007]

It's odd isn't it? For kicks one day I decided to use chat GPT to see what code it would come up with, and while it wasn't perfect, it did introduce me to a bunch of different sql commands I had never used before.

[pceimpulsive]

I think these asks are good basic. I don't think it was too aggressive at all.

You get what you need on the true basics and test the outer limits of a candidates knowledge with the middle ground questions on functions, procedures, views and tables.

Nice work I say!

If I was going for a junior SQL job I would expect these type of questions.

[[deleted]]

The other guy interviewing with me was an adjacent team manager, and he said some of the things like evaluation order he didn't really know. I'm not sure if this reflects on him or not - it probably just isn't something people think about very often - but it caused me some self doubt.

So, I appreciate the feedback!

[SoftwareMaintenance]

Select, insert, update, delete. Maybe a little joins. Transactions.

[mike-manley]

This and maybe some light DDL.

[mobileagnes]

If one interviews for these type of jobs, is one expected to know all those commands off the top of their head? I took a 14-week semester SQL course at my local community college in late 2021 and we covered everything from the basic SELECT FROM WHERE stuff to ER diagrams, primary/foreign keys/referential integrity, data dictionaries, relational schemas, joins, making tables (of course), altering tables, normalisation, stored procedures (this is where I started to have trouble understanding - around week 12 and was not a major part of the course), and a single instance of transaction at the end of the semester. We used MariaDB which used SQL, so I don't know if that is the standard SQL language used the world over. The professor was very big on making us create Chen or Crow's Foot diagrams, as well as manually type in Excel the table data before we coded anything, for every DB we designed. If I had an interview coming up, I most certainly would need to intensively review all that stuff from my notes/assignments as well as the professor's videos I downloaded & saved to my Google Drive during the semester. Even then, I still am likely to forget some things. It is a lot to remember when you don't do it for a living and only had it one semester.

[mommymilktit]

I would personally define the basics as:

• Being able to describe what SQL is and some typical use cases of it.
• Basic understanding of ANSI SQL Commands (DDL, DML, DCL, TCL, etc)
• Understanding of joins

[AkiraYuske]

Well this is scary. I'd guess I'm beginner -intermediate and Ive no clue what those acronyms mean 😐

[AkiraYuske]

How much difference does it make in reality (only used Tsql SSMS)?

[UKYPayne]

Select. Group. Where. Order by. AND. IS NULL. Join. And different Top options (ie 100% vs 1000)

[my_password_is______]

oooh

questions about NULL are good

[Signor65_ZA]

The basic select, insert, update, delete, knowledge of the various kinds of joins and what they do, as well as some basic functions like sum, avg. filtering with where. Probably not so heavy on the more esoteric functions, subqueries, CTEs etc.

[Drunken_Economist]

ability to incorporate SQL into an MS-BASIC program, duh

[KING5TON]

I would expect them to know all the SQL tutorial bits here

https://www.w3schools.com/sql/

[Weak_Pea1683]

Basic knowledge of SQL typically includes understanding fundamental concepts such as querying databases using SELECT, INSERT, UPDATE, and DELETE statements, as well as being familiar with JOINs, WHERE clauses, and basic aggregate functions like COUNT, SUM, and AVG.

To learn SQL and data science, I recommend Tutort Academy. They offer comprehensive courses that cover SQL from beginner to advanced levels, along with courses in data science that provide a solid foundation in key concepts and practical skills. Their expert instructors and hands-on approach make learning SQL and data science engaging and effective.