Simple SQL question (well, not for me)
 

I need to find the number of rows in a mySQL table where FirstName contains the letter "e" and lastName has more than 5 characters.

Say the table is called Employees or whatever.

TIA

Yes, it's simple but what have you tried so far and where are you stuck? And which variant of SQL is this on, MariaDB, PostgresSQL, or SQLite3?

The output should have format kind of like this:

------------------------------------
| count(*) |
------------------------------------
| 2 |
------------------------------------

NOTE: Can't format but it should be maybe like a box with count(*) in the top box and the actual count number below it.

I haven't tried anything yet. I don't even know what SQL command to use because I know nothing about SQL. I don't even have a DB on my CentOS box. Is there a resource online where you can practice SQL commands?

Yes, the quick and very easy way is to install SQLite3. It is the least complicated. If you have a RPM derivative,

Then you can access it in a terminal by pointing the interactive client at a file which will serve as you database:

The manual for SQLite is quite good and there are also lots of good (and bad) tutorials out there.

The first thing to do after that would be to create a table with the appropriate fields (aka columns). Then fill the table with a few sample records (aka rows) with data. Then you can try various searches.

OK so I don't want to come off like a complete helpless case so would it be something like this:

SELECT COUNT(*)
FROM Employees
WHERE FirstName CONTAINS "e" AND LastName > 5

Is my syntax right or will that produce gobbledygook or just bork?

What does the table which you've created look like? And do you have a few rows of fake data yet?

I haven't created a table. This is hypothetical. It's a possible technical interview question. Let's just assume that it's a basic table with a list of people's name, so the will be a FirstName column, a LastName column. There might be a few other columns like Age or HairColour. We are only interested in finding the number of entries where the FirstName contains letter "e" and Lastname has more than 5 characters.


So if we have

Firstname Lastname

eddie pillar
jo spencer
eve potter
joe bloggs




The answer is three.

Turbocapitalist has provided a link that could help you understand SQL and solve this question

I don't want to sound ungrateful but that link has wads of documentation and I don't see any examples, like you get in O'Reilly books.
It'll take me weeks to trawl through that. But thanks anyway.
I don't see anything in the Syntax that allows you to count characters in an entry.

You're getting the sequence wrong. You need to set up a table with few rows of data first, so you have something to search in. e.g. https://www.w3schools.blog/create-table-sqlite

 

If you want to succeed in a technical interview that involves answering SQL questions, you should know basic SQL.

A good way to learn basic SQL is to setup a dummy database and run queries against it.

That way, you don't ask questions like "will this query work" you run it yourself and find out.

OK so I figured out how to find records where FirstName contains letter "e"

SELECT FirstName,LastName FROM Employees
WHERE FirstName LIKE '%e%'


But I have no idea how to work out how to count characters in a field.

I agree, but this isn't a DBA or SQL job. It's a Linux Team job and there's only this one SQL question.
I will get an interview if I can answer this banana skin question on the technical assessment.

All my other skills Linux, SNMP, Networking, bash scripting, virtualisation are fine. It's just this one question that some guy recommend they ask and it's completely irrelevant to the job but they've lost loads of candidates because of it. Just because of pettiness.

This will usually be listed in the documentation under "string functions".

(For Sqlite, it is listed under "scalar functions".)

Anyway, this is a tech forum not an advice or agony aunt column.

And it is completely pointless. You want to say you know the answer? Or what? And why? I can give you the answer but you can't even check if that was correct or not...

OK so I have the second part

Now it's

SELECT FirstName, LastName FROM Employees
WHERE FirstName LIKE '%e%' AND LENGTH(LastName) > 5;


But that satisfies the conditions and returns the firstnames and lastname (I found a test database with a run SQL function)

But I have no clue how to count the number of rows returned. There are 7 in the table that I found.

It is (should be) perfectly ok to say "I don't know" - depending on the question, it may be wise to follow up with "but if I needed to, I would XYZ".

Some interviewers will deliberately ask irrelevant questions to see how candidates respond.

Given your second paragraph... if they're that petty in an interview, maybe it's a sign - are you sure it's a place you want to work at?


Put what you just typed into a search engine, prefixed by sql and you will get the answer!

select count(*) from ... (response #18)

However, I will also be one to say: if you are looking for the answer to a technical-interview question, "asking for the right answer on a forum" is not the right way to go about it. There are an abundance of SQL-tutorial web sites out there, and some of them even allow you to type in queries to "try them out for yourself."

SQL "select" queries are quite simple:

• "SELECT" what? – fields or function results
• "FROM" somewhere – could be a single table or a "JOIN" between multiple tables based on common field values. (There are several types of JOINs available.)
• "WHERE" conditions are met on the rows.
• (Optional) "GROUP BY" fields to produce summary values (count, average, etc.) for all unique combinations of those fields instead of the rows themselves.
• (Optional) "HAVING" if you want to limit which grouped rows are returned
• "ORDER BY" to sort the results.

Unlike other types of databases, SQL will attempt to give you your answer no matter how the database is organized or indexed, always finding on its own the most-efficient way to do that. In one step, it takes your query, figures out the best way to do it, and does it. (IBM's "SEQUEL" system was completely revolutionary, at the time ...)

While SQL is "mostly standardized," there are "dialect" differences between them. Most will support the same basic "select" syntax although there may be function-name differences. Some provide considerably more capability, such as constructing "pivot tables." Some, such as SQLite, do not support certain usually-standard things because of the particular nature of their implementation.

But: "learning to program is like learning to swim – you have to get in the water, shallow end first." You have to "try ... :banghead: ... try again." Yes, it is frustrating, especially at first. Until the :idea: turns on.

If you ask someone else for the answer, you're not learning to swim. You're just asking for a life-ring to be thrown to you by someone who did get into the same pool and "learn how."

The real task of "learning SQL" is not mastering the relatively-simple syntax of the statements. The real task is to take the problem description, and knowledge of the database, to decide what an appropriate SQL query should be. There may be more than one answer.

Or ask an AI: https://you.com/search?q=I+need+to+f...ue&tbm=youchat

SQL systems are endemic these days.
If you want to be a Linux admin (not a network eng), then a basic knowledge is expected.

For those seriously interested, let me please emphasize:

"The real task of "learning SQL" is not mastering the relatively-simple syntax of the statements. The real task is to take the [abstract] problem description, and knowledge of the database, to decide what an appropriate SQL query should be."

Elementary mastery of "SQL" is only the first step – akin to "mastery" of the circular saw and the hammer in the construction trades. You must master these skills before you can move on, but the mere mastery of these skills does not mean that you have moved on.

For example: "the person who you just hired to build a deck" did not gain his qualifications for doing so merely because he knew how to use a [tool]." Instead, he gained his position by knowing how to apprise the situation of "building your deck," and then to successfully execute the task of actually building it – of course using [tools].

Got it

SELECT COUNT(*) FROM Customers
WHERE CustomerName LIKE '%m%'AND LENGTH(Country) < 4;


Number of records where customer has "m" ib the name and country has less than 4 chars.

Ok, that is actually good news. And now?
If you think your problem is solved please mark the thread as solved.

Well, this actually-trivial question has obviously been "solved," but the real point of the thread is that you must learn how to solve these things for yourself. Because, "on the job," you are constantly going to be required to do this. Every company's databases – for historical reasons or otherwise – will be different. In order to be technically competent, you must understand the tools that are available in the [SQL ...] databases that you are using. And, if your software uses an "SQL abstraction layer," you must understand this also.

At the end of every day, your job is to come up with "the right answer."

This requires time, and most of all it requires practice. "Just like swimming," it is actually not that very complicated. But, you must get into the pool. Fortunately for all of us, other people have constructed "practice web sites" which allow you to experiment without first constructing a database of your own or setting up a server.

FYI: "It is a HUGE mistake to 'bluff your way into a job.'" Far better to candidly admit what you do not yet know, and to profess your eagerness to learn it. (After all, "that is how you learn ...") They just might hire you anyway.

Speaking as a "hiring manager" myself, a "hiring manager" above all does not want to be deceived. There are plenty of available resources to "bring a new team member 'up to speed,'" as long as one actually and truthfully realizes what this person does and does not yet(!) know. "Nobody knows everything, even if they think that they do." :) Better to understate what you think that you can do, and let them be pleasantly surprised.

Looking back, I can certainly say that I tended to hire people who were less certain of their abilities, because I felt that they were being honest with me. If the candidate appeared to be "too good to be true," I trusted my gut.

"Never make a promise to anyone that you are not certain that you can keep!" It is perfectly okay to tell someone: "I'm not sure, but I'll try, and I'll be the first one to let you know." "Honesty is the best policy.™"