PLEASE NOTE: Do not post your answers to this gist; email them to [email protected] instead
- Voting
Each voter can vote in zero or more referenda. Each referendum has one or more questions, and each question is a yes/no vote. Write the simplest normalized schema to describe this in generic SQL statements or as an entity-relationship diagram. Point out where the indexes would be if you want to quickly know the results of a given referendum question, but you never expect to query a single voter's voting record.
- Cursors
Explain the difference between explicit and implicit cursors in PL/SQL. Give an example in which an explicit cursor can be used to solve a problem that cannot be solved (or cannot be solved easily) using implicit cursors.
- Someone Did Something Dumb
Some new code hit production and it's performing horribly! The site is all broken.
The engineers have tracked the problem down to this SQL query:
-- Most popular user first names
SELECT first_name, COUNT(*) FROM v_user_locale_info GROUP BY first_name ORDER BY COUNT(*) DESC LIMIT 10
The definition of the v_user_locale_info view is:
SELECT u.first_name
, u.last_name
, a.street_address1
, a.street_address2
, a.city
, a.postalcode
, c.primary_language
, l.ltr
FROM users u
, address a
, country c
, language l
WHERE u.address_id = a.id
AND a.country_code = c.code
AND l.language_code = c.primary_language_code
What's wrong?
This query worked fine on the staging server. Why is it failing now?
- Unix tools
In one Unix command, find all of the files in /usr/local whose contents contain the word "aardvark" (case-insensitive), and list them all in order from most-recently created to least-recently created.
- Remote Development Environments
A client has asked you to solve some problems on their large (1 TB) database located in San Francisco, CA but you live in Indonesia. Your Internet bandwidth is about 1 Mbps, but it is a bit unreliable - especially at night when everyone returns home and starts BitTorrenting their favorite TV shows.
Discuss the pros and cons of interacting with the client's database using:
- An SSH session
- A Remote Desktop/Citrix session
- A local copy of the database
- A slimmed-down copy of the database (which does not currently exist)
Also discuss tools that you would use to mitigate any shortcomings of your setup.
How does the answer change if your bandwidth is 100 Kbps? 100 MBps?
- Javascript Form Handling
Design a simple form that validates a single date input (and includes a submit button). The resulting HTML must be fully-functional, but can reference libraries available on the public Internet.
Discuss how you would integrate such a form into a PHP controller framework. (In fact, your form design may reflect your choice of MVC framework if you would like.)
Is it possible to circumvent the form validation? If so, how? How would you mitigate this vulnerability?