Programming Interviews Exposed: Secrets to Landing Your Next Job, 3rd Edition

Database Problems

If you indicate on your résumé that you have some database experience or the job you apply for involves working with databases, interviewers will probably ask you database questions to determine the depth of your knowledge.

Simple SQL

PROBLEM Given a database with the table

Olympics(
    city CHAR(16),
    year INTEGER(4)
);

write a SQL statement to insert Montreal and 1976 into the database.

This is an extremely easy problem that an interviewer might use to determine whether you have ever used SQL before or whether you were padding your résumé when you mentioned it. If you know SQL, you’re all set. It’s a straightforward SQL INSERT statement; no tricks. If you don’t know SQL, you’re in trouble. The correct answer is

INSERT INTO Olympics VALUES( 'Montreal', 1976 );

Company and Employee Database

PROBLEM You are given a database with the following tables:

Company (
    companyName CHAR(30),
    id          INTEGER(4) PRIMARY KEY
);
EmployeesHired (
    id            INTEGER(4),
    numHired      INTEGER(4),
    fiscalQuarter INTEGER(4),
    FOREIGN KEY (id) REFERENCES Company
);

You may make the assumption that the only possible fiscal quarters are 1 through 4. Sample data for this schema is presented in Tables 12-3 and 12-4.

Table 12-3: Company Sample Data

companyName	id
Hillary Plumbing	6
John Lawn Company	9
Dave Cookie Company	19
Jane Electricity	3

Table 12-4: Employees Hired Sample Data

id	numHired	fiscalQuarter
3	3	2
3	2	4
19	4	1
6	2	1

Write a SQL statement that returns the names of all the companies ...