Write and run a sql query, with no starter code to answer this question: What Step are Job Codes 0110-0400?

Other SQL Server Topics
1

I am doing 'SQL for data science' course on Coursera, and I came across a question in solving the practice quiz.It has a tabled shown in the picture.

ss
ss1158×660 249 KB

The question is "Write and run a sql query, with no starter code to answer this question: What Step are Job Codes 0110-0400?"
For the earlier questions, I had to convert the string data into numerical to satisfy the given conditions. But it is not working in this situation.

My attempt for this question was :
select * from salary_range_by_job_classification
WHERE Job_code between CAST(0110) AS INT AND CAST(0400) AS INT

Please correct me if I am wrong.

3

-- this is what missed
--cast (Job_Code as INT )

SELECT 
   step 
FROM    
  salary_range_by_job_classification
WHERE 
  cast (Job_Code  as INT ) between   CAST(0110) AS INT AND CAST(0400) AS INT
1 Like
4

I'd be careful casting Job_code as an int. It is clearly stored as a character string. There may be alpha characters in there that would cause it to error out. Try this

declare @t table (job_code char(4))
insert into @t 
values
('0099'),
('0100'),
('0110'),
('0310'),
('0401'),
('0400'),
('1')

select * from @t where job_code between '0110' and '0400'
1 Like
5

Oh thank you for the solution ! I'll definitely try it .

6

SELECT step FROM salary_range_by_job_classification

where job_code between '0110' and '0400'

7

Ok, I know this is an old post but here's a couple of real life gotchas...

First, look at the casing of the column names (you don't show the actual table name but you need to look at that, as well). If the server is setup with a case sensitive collation, the code won't work because all lower case was used in the code.

Second, with the understanding that I don't know what the course work was that led up to this question in the "practice quiz", let's forget that for a moment...

This ISa data science course and the question asked was "What Step are Job Codes 0110-0400?". So far, no one has answered that question, IMHO. All they've done is list ALL the rows with 1 or more columns and no actual code to actually identify the "Step". What if there were a million rows? Are you going to print them on the screen and look to be sure that they're all 1?

Again, forget the course for a minute and think like a data scientist in real life... there's more than 1 question implied here...

  1. How can you PROVE there's only one Step number?
  2. What would happen if there were just one or two rows with different Step numbers other than 1?
  3. As a data scientist, do you suppose that if there were more than one, that someone might ask you how many of each or even what percentage of the sample those rows constitute?

IMHO opinion, a "data scientist" is really a Forensic Scientist that is trying to determine the actual condition of the data and to extract as many facts as possible because people are going to look to you not only for questions that they have, but also for questions they don't know they have. If you can provide answers to questions that they should have asked, you will be hailed as a "Data Scientist".

Otherwise, you're "just" another SQL Programmer.

8

I know this is an old post, but I'm taking the same course as you -- I was having issues, but I think I simply left the apostrophes out when querying between the 0110 and 0400 values. I used this below and it seems to have gotten me the right values:

Select

Job_Code

,Step

From salary_range_by_job_classification

Where Job_Code BETWEEN '0110' and '0400'

ORDER BY Step ASC;

1 Like