Simple SQL question (well, not for me)

picklehead · 02-07-2023, 06:56 AM

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

Turbocapitalist · 02-07-2023, 06:58 AM

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?

picklehead · 02-07-2023, 07:00 AM

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.

picklehead · 02-07-2023, 07:17 AM

Quote:

Originally Posted by Turbocapitalist

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?

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?

Turbocapitalist · 02-07-2023, 07:24 AM

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

Code:

sudo yum update
sudo yum install sqlite3

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

Code:

sqlite3 /home/me/Documents/my.sql.database.sqlite3

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.

picklehead · 02-07-2023, 07:27 AM

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?

Turbocapitalist · 02-07-2023, 07:31 AM

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

picklehead · 02-07-2023, 07:42 AM

Quote:

Originally Posted by Turbocapitalist

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.

grail · 02-07-2023, 07:47 AM

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

picklehead · 02-07-2023, 08:13 AM

Quote:

Originally Posted by grail

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.

Turbocapitalist · 02-07-2023, 08:23 AM

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

boughtonp · 02-07-2023, 08:29 AM

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.

picklehead · 02-07-2023, 08:30 AM

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.

picklehead · 02-07-2023, 08:36 AM

Quote:

Originally Posted by boughtonp

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.

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.

boughtonp · 02-07-2023, 08:36 AM

Quote:

Originally Posted by picklehead

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

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

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