Interview questions

Hey /prog/, this is one of the tests I give in job interviews to check for bullshitting. Too easy, too difficult? What do you think?


SQL
 
You have a table named orders with a structure described by the following statement:
 
create table orders
(
    order_number         varchar(12)  primary key,
    date_required        datetime,
    status               int not null
)
 
1. Write a query which lists all order_numbers where the status is less than 6 and the year of date_required is 2004. Have the list sorted by order_number in ascending order.
 
2. Write a query that returns the number of orders which have a status of 8.
 

There are two tables, structure as follows:
 
create table machines
(
    machine_id          varchar(5)  primary key
        check (machine_id like '[a-z][a-z][a-z][0-9][0-9]'),
    location            varchar(4),
    current_job_id      int
)
 
create table jobs
(
    job_id              int  primary key,
    partnumber          varchar(20),
    quantity            int,
    quantity_completed  int
)
 
Each row in the machines table represents a machine in the factory. Each machine has an ID number with three letters and two numbers. The location column is a four letter code representing different places in the factory. The current_job_id indicates what job the machine is currently running.
 
The jobs table is a list of these jobs. The item that is being made on that job is indicated by the partnumber column. The quantity and quantity_completed fields are how much needs to be made on that job, and how much has been made so far.
 
3. Write an SQL query that lists all machines located in location 'TOOL'
 
4. Write a query which returns all fully completed jobs for partnumbers beginning with 'H'.
 
5. Write another query that returns all machines that are running jobs for the partnumber 'P15/125'.
 

Visual Basic
 
Here is a template for a function that returns the number of occurrences of the character C in the string S:
 
Public Function CharacterCount(S as String, C as String) As Integer
  ...
End Function
 
Some examples of its use:
 
CharacterCount("eggs","e") returns 1
CharacterCount("oranges and lemons"," ") returns 2
CharacterCount("TESTING","t") returns 0
 
6. Write the body of this function, using any method you'd like.

1.
select
  order_number
from
  orders
where
  status < 6
  and year(date_required) = 2004
order by order_number asc;

2.
select
  count(order_number)
from
  orders
where
  status = 8;

3.
select
  machine_id
from
  machines
where
  location = 'TOOL';

4.
select
  job_id
from
  jobs
where
  quantity_completed = quantity
  and partnumber like 'H%';

5.
select
  m.machine_id
from
  machine m,
  jobs j
where
  j.partnumber = 'P15/125'
  and j.quantity_completed = j.quantity;

6.
Public Function CharacterCount(S as String, C as String) as Integer
  Dim current_count as Integer
  current_count = 0

  Dim position as Integer
  For position = 0 to Len(S) - 1
    If Mid(S, position, 1) = C Then
      current_count = current_count + 1
    End If
  Next position

  CharacterCount = current_count
End Function


The reason the SQL is all split up like that is because back when I was writing some apps that used SQL, I found it was a lot easier to add columns, tables and conditions if they were all on separate lines.

And I haven't touched VB in years. I'm assuming Option Explicit because coding without it is a friggin' nightmare.