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

10.2. Database Problems

If you indicate on your résumé that you have some database experience, an interviewer will probably ask you a few questions to determine the depth of your knowledge in this area.

10.2.1. Simple SQL

NOTE

Given a database with the table

Olympics(
     city CHAR(16),
     year INT(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 at all. If you don't really know SQL, you're in trouble. The correct answer is

INSERT INTO Olympics VALUES( 'Montreal', 1976 )

10.2.2. Company and Employee Database

NOTE

You are given a database with the following tables:

Company  (
    companyName  CHAR(30),
    id           INT(4) PRIMARY KEY
)

EmployeesHired  (
    Id             INT(4) PRIMARY KEY,
    numHired       INT(4),
    fiscalQuarter  INT(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 Table 10-3.

Table 10.3. Company and Employee Sample Data

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

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

Write a SQL statement that returns the names of all the companies that hired employees in fiscal quarter 4.

This problem involves retrieving data from two tables. ...