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. |
Quote:
|
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 Code:
sqlite3 /home/me/Documents/my.sql.database.sqlite3 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?
|
Quote:
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
|
Quote:
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. |
Quote:
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. |
Quote:
(For Sqlite, it is listed under "scalar functions".) |
All times are GMT -5. The time now is 02:53 PM. |